Feeds:
Posts
Comments

Posts Tagged ‘same Database’

Hi guy’s, hope all of you doing well.Yesterday we face new issue, I would like to share same with you. In one of our development server scheduled cold backup use to happened on weekly basis.In this database while going with health check I found that two instances are running with same name & user of single database.hope you understood the issues.

bash-3.00$ ps -ef |grep pmon
  orasam 28994     1   0   Feb 18 ?         155:24 ora_pmon_sam
  orasam  1654     1   0   Apr 27 ?           1:10 ora_pmon_sam
  orad21  8486  8427   0 23:26:44 pts/2       0:00 grep pmon

Reason: In this server schedule cold backup use to happen on weekly backup. But while shutting down it was not clean shutdown so showing the two background process for each.

bash-3.00$ ps -ef |grep pmon
  orasam 28994     1   0   Feb 18 ?         155:24 ora_pmon_sam
  orasam  1654     1   0   Apr 27 ?           1:10 ora_pmon_sam
  orasam  8500  8427   0 23:29:08 pts/2       0:00 grep pmon

Now see the background process as well.You may find two different background processes are running with same name but with different date & time.

bash-3.00$ ps -ef |grep ora_
  orasam  1670     1   0   Apr 27 ?           0:03 ora_smon_sam
  orasam  1662     1   0   Apr 27 ?           0:07 ora_dbw2_sam
  orasam 29000     1   0   Feb 18 ?          21:31 ora_dbw0_sam
  orasam 29002     1   0   Feb 18 ?          19:49 ora_dbw1_sam
  orasam 29020     1   0   Feb 18 ?         143:50 ora_mmnl_sam
  orasam 29025     1   0   Feb 18 ?          11:35 ora_arc1_sam
  orasam 28994     1   0   Feb 18 ?         155:24 ora_pmon_sam
  orasam  1672     1   0   Apr 27 ?           0:01 ora_reco_sam
  orasam 29029     1   0   Feb 18 ?           0:54 ora_qmnc_sam
  orasam  1656     1   0   Apr 27 ?           0:06 ora_mman_sam
  orasam 29035     1   0   Feb 18 ?           0:01 ora_q000_sam
  orasam 29012     1   0   Feb 18 ?          36:35 ora_smon_sam
  orasam  1668     1   0   Apr 27 ?           0:08 ora_ckpt_sam
  orasam 29010     1   0   Feb 18 ?          79:27 ora_ckpt_sam
  orasam 29018     1   0   Feb 18 ?           7:22 ora_mmon_sam
  orasam 28998     1   0   Feb 18 ?           4:21 ora_mman_sam
  orasam  1658     1   0   Apr 27 ?           0:08 ora_dbw0_sam
  orasam 29016     1   0   Feb 18 ?         119:51 ora_cjq0_sam
  orasam 29023     1   0   Feb 18 ?          14:19 ora_arc0_sam
  orasam 29006     1   0   Feb 18 ?          20:45 ora_dbw3_sam
  orasam 28996     1   0   Feb 18 ?           3:52 ora_psp0_sam
  orasam  1654     1   0   Apr 27 ?           1:10 ora_pmon_sam
  orasam  1674     1   0   Apr 27 ?           0:05 ora_cjq0_sam
  orasam  1660     1   0   Apr 27 ?           0:07 ora_dbw1_sam
  orasam 29014     1   0   Feb 18 ?           0:04 ora_reco_sam
  orasam 29004     1   0   Feb 18 ?          20:12 ora_dbw2_sam
  orasam 29008     1   0   Feb 18 ?          63:42 ora_lgwr_sam
  orasam  1664     1   0   Apr 27 ?           0:07 ora_dbw3_sam
  orasam  8226     1   3 23:14:21 ?           2:51 ora_m000_sam
  orasam  8506  8427   0 23:29:29 pts/2       0:00 grep ora_
  orasam  6996     1   0   Apr 19 ?           0:04 ora_q002_sam
  orasam  1666     1   0   Apr 27 ?           0:06 ora_lgwr_sam
 
Action: Find out the current background pid of database, please find the query for same.
 
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type = ‘BACKGROUND’
                and p.spid= ‘1662’;
 

Excluding these pid kill rest all pid’s
Except these all background pid was killed.(It was for Apr 27).So you can killed
Please find the current status & difference as well.
 
bash-3.00$ ps -ef |grep ora_
  orasam 29000     1   0   Feb 18 ?          21:33 ora_dbw0_sam
  orasam 29002     1   0   Feb 18 ?          19:51 ora_dbw1_sam
  orasam 29020     1   0   Feb 18 ?         144:07 ora_mmnl_sam
  orasam 29025     1   0   Feb 18 ?          11:35 ora_arc1_sam
  orasam 28994     1   0   Feb 18 ?         155:43 ora_pmon_sam
  orasam 29029     1   0   Feb 18 ?           0:55 ora_qmnc_sam
  orasam 29035     1   0   Feb 18 ?           0:01 ora_q000_sam
  orasam 29012     1   0   Feb 18 ?          36:42 ora_smon_sam
  orasam  9030  8427   0 02:52:13 pts/2       0:00 grep ora_
  orasam 29010     1   0   Feb 18 ?          79:36 ora_ckpt_sam
  orasam 29018     1   0   Feb 18 ?           7:23 ora_mmon_sam
  orasam 28998     1   0   Feb 18 ?           4:21 ora_mman_sam
  orasam 29016     1   0   Feb 18 ?         120:06 ora_cjq0_sam
  orasam 29023     1   0   Feb 18 ?          14:20 ora_arc0_sam
  orasam 29006     1   0   Feb 18 ?          20:47 ora_dbw3_sam
  orasam 28996     1   0   Feb 18 ?           3:52 ora_psp0_sam
  orasam 29014     1   0   Feb 18 ?           0:04 ora_reco_sam
  orasam 29004     1   0   Feb 18 ?          20:14 ora_dbw2_sam
  orasam 29008     1   0   Feb 18 ?          63:46 ora_lgwr_sam
  orasam  8226     1   3 23:14:21 ?         205:37 ora_m000_sam
  orasam  6996     1   0   Apr 19 ?           0:04 ora_q002_sam
bash-3.00$

Experts are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »