【Oracle】startup hung 遇到 ORA-00443,ORA-00445

楊奇龍發表於2011-10-27
Oracle
上午接近11點，一同事說測試環境的資料庫起不來了，可以透過sqlplus連線資料庫，但是執行startup命令時，會出現hung住的情況。檢視日誌只有早上8：23之前的，之後對資料庫進行啟動的操作是沒有記錄的
日誌大致內容如下：
ORA-00445: background process "J000" did not start after 120 seconds
Thu Oct 27 07:20:21 2011
Errors in file /apsarapangu/disk1/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_smco_654.trc  (incident=10194):
ORA-00445: background process "W000" did not start after 120 seconds
Thu Oct 27 07:25:04 2011
Dumping diagnostic data in directory=[cdmp_20111027072504], requested by (instance=1, sid=654 (SMCO)), summary=[incident=10194].
Thu Oct 27 07:25:40 2011
kkjcre1p: unable to spawn jobq slave process 
Errors in file /apsarapangu/disk1/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_400.trc:
Thu Oct 27 07:32:59 2011
Process q000 died, see its trace file
Process 0x0xab06fe628 appears to be hung while dumping
Attempting to kill process 0x0xab06fe628 with OS pid = 32572
OSD kill succeeded for process 0xab06fe628
Process 0x0xab0707aa8 appears to be hung while dumping
Attempting to kill process 0x0xab0707aa8 with OS pid = 400
OSD kill succeeded for process 0xab0707aa8
Process 0x0xac8713678 appears to be hung while dumping
Attempting to kill process 0x0xac8713678 with OS pid = 654
OSD kill succeeded for process 0xac8713678
Thu Oct 27 07:59:20 2011
Restarting dead background process CJQ0
Thu Oct 27 07:59:38 2011
Restarting dead background process MMON
Restarting dead background process SMCO
Thu Oct 27 07:59:50 2011
Starting background process CJQ0
Starting background process MMON
Thu Oct 27 08:00:59 2011
Process q000 died, see its trace file
Thu Oct 27 08:01:02 2011
Starting background process SMCO
Thu Oct 27 08:02:21 2011
Process SMCO died, see its trace file
Errors in file /apsarapangu/disk1/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_32498.trc:
ORA-00443: background process "SMCO" did not start
Starting background process CJQ0
Starting background process MMON
Thu Oct 27 08:02:39 2011
Process q000 died, see its trace file
Thu Oct 27 08:02:39 2011
Restarting dead background process SMCO
Thu Oct 27 08:03:55 2011
Process SMCO died, see its trace file
Errors in file /apsarapangu/disk1/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_32498.trc:
ORA-00443: background process "SMCO" did not start
出現ORA-00443,ORA-00445 
ORA-00443: background process "SMCO" did not start
ORA-00445: background process "W000" did not start after 120 seconds
對於ORA-00443 官方的介紹如下：
Cause:  The specified process did not start.
Action: Check that the executable image is in the correct place with the 
        correct protections and that there is enough memory.
對於ORA-00445 官方的介紹如下：
Cause:  The specified process did not start.
Action: Check and, if necessary, correct problems indicated by one or more of the following:
        the size of the SGA
        the operating system-specific initialization parameters
        accompanying messages
        the background trace file
        the executable image is not in the right location with the correct  protections
兩個錯誤都指示和記憶體是否足夠相關，因此檢視oracle 的記憶體設定情況。由於資料庫沒有啟動且使用的spfile，所以透過如下方式：
SQL> create pfile from spfile;
File created.
檢視 pfile中的關於記憶體配置的情況：
orcl.__db_cache_size=21877489664        ~20.375G
orcl.__java_pool_size=134217728         ~128M
orcl.__large_pool_size=134217728        ~128M
orcl.__pga_aggregate_target=17179869184 ~16G
orcl.__sga_target=25769803776           ~24G
orcl.__shared_io_pool_size=0 
orcl.__shared_pool_size=3221225472      ~307.2M
orcl.__streams_pool_size=134217728      ~128 M
*.memory_target=44445899345920  ---約41393.46G.而整個PC server 的記憶體不過141G！！
上面的memory_target配置完全超過了系統的記憶體大小，因此造成了alert 報警日誌產生的錯誤
修改 memory_target的值為45G。重新啟動：
SQL> startup nomount pfile='/apsarapangu/disk1/opt/oracle/products/11.2.0/dbs/initorcl.ora';
ORACLE instance started.
Total System Global Area 4.5698E+10 bytes
Fixed Size                  2236784 bytes
Variable Size            2.3757E+10 bytes
Database Buffers         2.1877E+10 bytes
Redo Buffers               61263872 bytes
利用pfile 建立新的spfile（將老的備份）
SQL> create spfile from  pfile='/apsarapangu/disk1/opt/oracle/products/11.2.0/dbs/initorcl.ora';
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
使用spfile檔案啟動，成功
SQL> startup nomount
ORACLE instance started.
Total System Global Area 4.5698E+10 bytes
Fixed Size                  2236784 bytes
Variable Size            2.3757E+10 bytes
Database Buffers         2.1877E+10 bytes
Redo Buffers               61263872 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit

成功啟動。。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-709794/，如需轉載，請註明出處，否則將追究法律責任。

相關文章