ORA-00600 [4194]的解决

今天遇到一个standby 拷贝出来的库，要建成测试库！

重建控制文件，用resetlogs起库后，数据库直接down掉了。

报了一堆ora-600 [4194]的问题出来

 

Fri May 09 11:38:24 2014opidrv aborting process M000 ospid (20091) as a result of ORA-603Fri May 09 11:38:27 2014Flush retried for xcb 0x44053b268, pmd 0x43f7e9628Errors in file /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/trace/t2fls_pmon_16902.trc  (incident=17):ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []Incident details in: /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/incident/incdir_17/t2fls_pmon_16902_i17.trcUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Fri May 09 11:38:29 2014Dumping diagnostic data in directory=[cdmp_20140509113829], requested by (instance=1, osid=16902 (PMON)), summary=[incident=17].Errors in file /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/trace/t2fls_pmon_16902.trc:ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []PMON (ospid: 16902): terminating the instance due to error 472System state dump requested by (instance=1, osid=16902 (PMON)), summary=[abnormal instance termination].System State dumped to trace file /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/trace/t2fls_diag_16973.trcDumping diagnostic data in directory=[cdmp_20140509113834], requested by (instance=1, osid=16902 (PMON)), summary=[abnormal instance termination].Instance terminated by PMON, pid = 16902

这个问题看起来很熟悉和ora-600 [4193] 是一样都是undo的问题。

metlink上的信息如下：

GoalThis note will give an Brief overview of the steps to resolve ORA-600 [4194]/[4193]:-SolutionShort Description of ORA-00600[4194]---------------------------------------A mismatch has been detected between Redo records and rollback (Undo) records.ARGUMENTS:Arg [a] Maximum Undo record number in Undo blockArg [b] Undo record number from Redo block Refer to Note:39283.1 for more details on the description of this error.Basic Steps that Need to be Followed to Solve an ORA-00600[4194] ErrorThere are two Options to resolve this issue.Options along with their solutions are given below. Option 1:- Support Method(Drop the undo tablespace).    Option 1 :- Supported Method ======================Drop the undo tablespace.   Single instanceThis error normally happens for a new transaction. The trace file actually shows an active transaction for the undo segment because this is the transaction created by the same process.If the undo segment happens to have an active transaction , then Oraclemay recover it later with no problems .Normally if the header is dumped after the error, the active transactin is gone.So a Simpler option to resolve this issue is.Step 1--------SQL> Startup nomount ;    --> using spfileSQL> Create pfile='/tmp/corrupt.ora' from spfile ; SQL> Shutdown immediate; Step 2-------Modify the corrupt.ora and set Undo_management=Manual SQL> Startup mount pfile='/tmp/corrupt.ora' SQL> Show parameter undo    it should show manual SQL> Alter database open ; If it comes up SQL> Create rollback segment r01 ; SQL> Alter rollback segment r01 online ; Create a new undo tablespace SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ; Please note  :-   You can delay the drop of the Old undo tablespace this is just to allow the block cleanout to happen for dead transaction.                               So the below step can be issued after database has been  up and running with new undo tablespace for couple of hours.                               Also note if your database has been forced open(datafiles are not in sync and archivelogs missing ) using any unsupported method then please donot drop the Old undo.Drop the Old undo tablespaceSQL> Drop tablespace <undo tablespace name> including contents and datafiles Step 3-------SQL> Shutdown immediate; SQL> Startup nomount ; ---> Using spfile SQL>Alter system set undo_tablespace=<new Undo tablespace created> scope=spfile; SQL> Shutdown immediate ; SQL> Startup Check if error is reported For Rac Instance(If one instance is down and other is up and running)------------------------If one node is up and running and other node is failing with ORA-00600[4194]/[4193] thenFrom the instance which is up and running create a new undo tablespace and make it the default one for the other instance which is down with the error.Startup the failing instance withthe new undo tablespace.  From Instance which is up and running Create undo tablespace undo_new datafile '<filename>' size <> m ;Alter system set undo_tablespace=<New undo tablespace name> sid=<instance which has corrupt undo tablespace and is down> scope=spfile ;Now Startup the Instance which is downSQL>Startup mount SQL>Show parameter undoShould show the new undo tablespace created aboveSQL>Alter database open ;SQL>Drop tablespace <Old undo tablespace of the failing instance> including contents and datafiles If all the Instance is down in the Rac due to this error then following the instruction given for Single instance and  create new undo tablespace.For 8i database and Below SQL>Startup restrict Drop the Manual rollback segments and recreate it 

看着很长其实就是把undo改成手动，然后重建一个undo即可

原本以为offline undo数据文件即可，起库，但是事实不行！

修改undo_management为manual，然后起库，创建新的undo表空间。

NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      MANUALundo_retention                       integer     3600undo_tablespace                      string      UNDOTBS02

SQL> alter database datafile '/paic/t2fls/data01/oradata/t2fls/SFLS/datafile/o1_mf_undotbs1_8hx69sbf_.dbf' online;Database altered.SQL> alter database open;alter database open*ERROR at line 1:ORA-01113: file 3 needs media recoveryORA-01110: data file 3:'/paic/t2fls/data01/oradata/t2fls/SFLS/datafile/o1_mf_undotbs1_8hx69sbf_.dbf'SQL> recover datafile 3;Media recovery complete.SQL> alter database open;Database altered.SQL> SQL> SQL> SQL> create undo tablespace undotbs02 datafile '/paic/t2fls/data01/oradata/t2fls/SFLS/datafile/undotbs02.dbf' size 2000M;Tablespace created.SQL> alter database drop tablespace undotbs01;alter database drop tablespace undotbs01                    *ERROR at line 1:ORA-01900: LOGFILE keyword expectedSQL> drop tablespace undotbs01;drop tablespace undotbs01*ERROR at line 1:ORA-00959: tablespace 'UNDOTBS01' does not existSQL> drop tablespace undotbs1;Tablespace dropped.