oracle startup 之後 如果執行SQL 那麼立馬shutdown 原因分析

事故情境    當我資料庫使用的時候，突然斷電了。然後就出現了下邊的癥狀。

oracle startup  之後  如果執行SQL 那麼立馬shutdown 原因分析

排查步驟 ： 

1    根據 alter_orcl.log  尋找錯誤。然後在trc裡面找明細的錯誤。

2    發現時 ora-00600錯誤。 然後引用了一遍其他人的文章。具體如下。

Ora-00600 4193一般來說是undo資料表空間的問題。

我們來看看一個執行個體 
在trc檔案裡出現 

Fri Dec 16 22:37:27 2005 Errors in file /opt/oracle/admin/orcl/bdump/orcl_smon_22817.trc:ORA-00604: error occurred at recursive SQL level 1ORA-00607: Internal error occurred while making a change to a data blockORA-00600: internal error code, arguments: [4193],
[1171], [1187], [], [], [], [], []Fri Dec 16 23:28:40 2005Errors in file /opt/oracle/admin/orcl/bdump/conner_smon_22817.trc:ORA-00600: internal error code, arguments: [4193], [1171], [1187], [], [], [], [], [] 

4193錯誤通常是因為恢複時redo與undo不一致所導致。 

Oracle的解釋如下: 

引用:

While backing out an undo record (i.e. at the time of rollback) we found a transaction id mis-match indicating either a corruption in the rollback segment or corruption in an object which the rollback segment is trying to apply undo records on. 

This would indicate a corrupted rollback segment. 

檢查具體的Trace檔案，可以發現類似如下錯誤： 

*** 2005-12-16 20:54:53.496 
ksedmp: internal or fatal error 
ORA-00600: internal error code, arguments: [4193], [1171], [1187], [], [], [], [], [] 
Current SQL statement for this session: 
UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIME_DP=:4 
WHERE TIME_MP = :5 AND THREAD = :6 AND ROWNUM <= 1 

由於是UNDO存在不一致，可以通過重建UNDO資料表空間來解決: 

SQL> create undo tablespace undotbs2 
2 datafile '/opt/oracle/oradata/orcl/undotbs2.dbf' size 10m; 

Tablespace created. 

SQL> alter system set undo_tablespace=undotbs2 scope=both; 
System altered. 

SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 

SQL> startup 
ORACLE instance started. 
Total System Global Area 114365800 bytes 
Fixed Size 451944 bytes 
Variable Size 50331648 bytes 
Database Buffers 62914560 bytes 
Redo Buffers 667648 bytes 
Database mounted. 
Database opened. 

SQL> show parameter undo 
NAME TYPE value 
------------------------------------ 
undo_management string AUTO 
undo_retention integer 10800 
undo_suppress_errors boolean FALSE 
undo_tablespace 

SQL> drop tablespace undotbs1 including contents and datafiles; 

Tablespace dropped. 

至此，Ora-600 4193錯誤不再出現。 

從alert檔案中，可以看到自動控制檔案備份生效： 

Sun Dec 18 22:37:59 2005 
drop tablespace undotbs1 including contents and datafiles 
Sun Dec 18 22:37:59 2005 
Deleted file /opt/oracle/oradata/orcl/undotbs01.dbf 
Starting control autobackup 
Control autobackup written to DISK device handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20051218-01' 

Completed: drop tablespace undotbs1 including contents and datafiles.

================*** 2013-07-04 11:04:55.691
SMON: following errors trapped and ignored:
ORA-01595: error freeing extent (2) of rollback segment (5))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [18], [5], [], [], [], [], []
*** 2013-07-04 11:06:38.859
error 472 detected in background process
ORA-00472: PMON  process terminated with error

http://blog.csdn.net/kingsonl/article/details/8033588

Thu Jul 04 13:55:05 2013
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_1668.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [600], [888], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [600], [888], [], [], [], [], []

http://wenku.baidu.com/view/7de1f1f8700abb68a982fbed.html

ORA-00604: 
error occurred at recursive SQL level 1

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [4193], [600], [888], [], [], [], [], []

http://blog.csdn.net/inthirties/article/details/4630889

create undo tablespace undotbs2 datafile 'C:/oracle/product/10.2.0/oradata/orcl/undotbs2.dbf' size 10m;