事故场景 当我数据库使用的时候,突然断电了。然后就出现了下边的症状。
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;
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;