プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

  友達のために、電源が切れたからまともに起動できなくなった10.2.0.1データベースを起動した。そのリポジトリはアーカイブモードではない上に、何のバックアップもない。 電源が切れた後に、データベースインスタンスを再起動したいが、ORA-00600:[kccpb_sanity_check_2]内部エラが起こった: SQL> select status from v$instance; STATUS ------------ STARTED SQL> SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 1220432 bytes Variable Size 486539440 bytes Database Buffers 1644167168 bytes Redo Buffers 15556608 bytes ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [8198], [8175], [0x0], [], [], [], [] そのkccpb_sanity_check_2内部エラは間違えたcontrol fileのseq#記録に引き起こされた。MOS note <ORA-00600: [kccpb_sanity_check_2] During Instance Startup>にこのようなトラブルを説明した: ORA-00600: [kccpb_sanity_check_2] During Instance Startup Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later [Release: 10.2 and later ] Information in this document applies to any platform. Symptoms The database is getting the following errors on Startup: ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [3621501], [3621462], [0x000000000] Changes In this case, the customer moved the box from one data center to another. Cause ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is higher than the seq# of the control file header block. This is indication of the lost write of the header block during commit of the previous cf transaction. Solution 1) restore a backup of a controlfile and recover OR 2) recreate the controlfile OR 3) restore the database from last good backup and recover NOTE: If you do not have any special backup of control file to restore and you are using Multiple Control File copies in your pfile/init.ora/spfile you can attempt to mount the database using each control file one by one. If you are able to mount the database with any of these control file copies you can then issue 'alter database backup controlfile to trace' to recreate controlfile.   control file corruptionが引き起こしたORA-00600: [kccpb_sanity_check_2] トラブルに対して、以下の三つの解決策を試してください: 1. restore controlfile from backup 2. 複数のcontrolfileがある場合に、各control fileでmountできたインスタンスを参考してください。成功できれば、使用可能なcontrolfileをほかの場所へコピできる 3. controlfile backup traceでコントロールファイルを再構造する、traceがなくとも人工的にcreate controlfile文で再構造できる このようなトラブルが起こったら、二つ目の方法を試したが、失敗した。udump/bdumpディレクトリの下で、使用可能なcontrolfile backup traceを検索するしかない。幸いにそれを見つけた。そしてコントロールファイルを再構造することが成功した。: Create controlfile reuse set database "kmcdb" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 Datafile '/u01/oracle/oradata/kmcdb/system01.dbf', '/u01/oracle/oradata/kmcdb/undotbs01.dbf', '/u01/oracle/oradata/kmcdb/sysaux01.dbf', '/u01/oracle/oradata/kmcdb/users01.dbf', '/u01/oracle/oradata/kmcdb/example01.dbf' LOGFILE GROUP 1 ('/u01/oracle/oradata/kmcdb/redo01.log') SIZE 51200K, GROUP 2 ('/u01/oracle/oradata/kmcdb/redo02.log') SIZE 51200K, GROUP 3 ('/u01/oracle/oradata/kmcdb/redo03.log') SIZE 51200K RESETLOGS Wed Sep 28 16:57:47 2011 WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command Default Temporary Tablespace will be necessary for a locally managed database in future release Setting recovery target incarnation to 1 Wed Sep 28 16:57:48 2011 Successful mount of redo thread 1, with mount id 523482251 Wed Sep 28 16:57:48 2011 Completed: Create controlfile reuse set database "kmcdb" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 Datafile '/u01/oracle/oradata/kmcdb/system01.dbf', '/u01/oracle/oradata/kmcdb/undotbs01.dbf', '/u01/oracle/oradata/kmcdb/sysaux01.dbf', '/u01/oracle/oradata/kmcdb/users01.dbf', '/u01/oracle/oradata/kmcdb/example01.dbf' LOGFILE GROUP 1 ('/u01/oracle/oradata/kmcdb/redo01.log') SIZE 51200K, GROUP 2 ('/u01/oracle/oradata/kmcdb/redo02.log') SIZE 51200K, GROUP 3 ('/u01/oracle/oradata/kmcdb/redo03.log') SIZE 51200K RESETLOGS Wed Sep 28 16:57:57 2011 alter database mount インスタンスを成功にmountできたら、データベースをリカバリして、再起動してください : recover database using backup controlfile until cancel; controlfileが再構造されたから、今のcontrolfileはどれのオンラインログがcurrentだと知らなかったから、人工的に指定してください。 ALTER DATABASE RECOVER database using backup controlfile until cancel Wed Sep 28 17:27:09 2011 Media Recovery Start WARNING! Recovering data file 1 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 3 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 4 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 5 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. parallel recovery started with 7 processes ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ... Wed Sep 28 17:27:20 2011 ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/kmcdb/redo02.log' Wed Sep 28 17:27:20 2011 Media Recovery Log /u01/oracle/oradata/kmcdb/redo02.log Errors with log /u01/oracle/oradata/kmcdb/redo02.log ORA-339 signalled during: ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/kmcdb/redo02.log' ... Wed Sep 28 17:27:20 2011 ALTER DATABASE RECOVER CANCEL ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ... Wed Sep 28 17:27:37 2011 ALTER DATABASE RECOVER database using backup controlfile until cancel Wed Sep 28 17:27:37 2011 Media Recovery Start WARNING! Recovering data file 1 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 3 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 4 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 5 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. parallel recovery started with 7 processes ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ... Wed Sep 28 17:27:52 2011 ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/kmcdb/redo03.log' Wed Sep 28 17:27:52 2011 Media Recovery Log /u01/oracle/oradata/kmcdb/redo03.log Wed Sep 28 17:27:52 2011 Incomplete recovery applied all redo ever generated. Recovery completed through change 16228646 Wed Sep 28 17:27:52 2011 Media Recovery Complete (kmcdb) Completed: ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/kmcdb/redo03.log' Wed Sep 28 17:28:12 2011 何度も試した後に、redo03.logは必要とされているcurrent group memberだから、alter database open resetlogsしてください。けど、またORA-00600: internal error code, arguments: [4000], [7], [], [], [], [], [], []エラになる: alter database open resetlogs Wed Sep 28 17:28:12 2011 RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 16228646 Resetting resetlogs activation ID 523468297 (0x1f337e09) Online log /u01/oracle/oradata/kmcdb/redo01.log: Thread 1 Group 1 was previously cleared Online log /u01/oracle/oradata/kmcdb/redo02.log: Thread 1 Group 2 was previously cleared Wed Sep 28 17:28:12 2011 Setting recovery target incarnation to 5 Wed Sep 28 17:28:12 2011 Assigning activation ID 523485789 (0x1f33c25d) Thread 1 opened at log sequence 1 Current log# 3 seq# 1 mem# 0: /u01/oracle/oradata/kmcdb/redo03.log Successful open of redo thread 1 Wed Sep 28 17:28:12 2011 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Sep 28 17:28:12 2011 SMON: enabling cache recovery Wed Sep 28 17:28:12 2011 Errors in file /u01/oracle/admin/kmcdb/udump/kmcdb_ora_31798.trc: ORA-00600: internal error code, arguments: [4000], [7], [], [], [], [], [], [] Wed Sep 28 17:28:14 2011 Errors in file /u01/oracle/admin/kmcdb/udump/kmcdb_ora_31798.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [7], [], [], [], [], [], [] 以上のORA-00600: [4000], [7],はusn=のロールバックセグメント時にみつけられる。ブロックをロールバックする時に、ロールバックセグメントにトラブルが起こった。それに、ORA-00704: bootstrap process failureエラも同時に起こった。　これはロールバックする必要があるデータブロックはbootstrapにとって、大切なブートストラップ・オブジェクトである。 一般的にbootstrap objectはデータをrollback、cleanupあるいはapply undoするときに、_corrupted_rollback_segments,_offline_rollback_segmentsあるいは10513トランザクションでORA-00600: [4000]を阻止できない。 まだ試したいなら、_corrupted_rollback_segments,_offline_rollback_segmentsとeventバラメタを修正して、試してください: alter system set event='10513 trace name context forever,level 2 : 10512 trace name context forever,level 1: 10511 trace name context forever,level 2: 10510 trace name context forever,level 1' scope=spfile; ALTER SYSTEM SET _offline_rollback_segments='(_SYSSMU7$)' SCOPE=SPFILE; ALTER SYSTEM SET _corrupted_rollback_segments='(_SYSSMU7$)' SCOPE=SPFILE; 再びopen database alter database open resetlogs Wed Sep 28 17:47:05 2011 RESETLOGS after complete recovery through change 16228668 Resetting resetlogs activation ID 523471415 (0x1f338a37) Online log /u01/oracle/oradata/kmcdb/redo01.log: Thread 1 Group 1 was previously cleared Online log /u01/oracle/oradata/kmcdb/redo02.log: Thread 1 Group 2 was previously cleared Wed Sep 28 17:47:05 2011 Setting recovery target incarnation to 11 Wed Sep 28 17:47:05 2011 Assigning activation ID 523466708 (0x1f3377d4) Thread 1 opened at log sequence 1 Current log# 3 seq# 1 mem# 0: /u01/oracle/oradata/kmcdb/redo03.log Successful open of redo thread 1 Wed Sep 28 17:47:05 2011 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Sep 28 17:47:05 2011 SMON: enabling cache recovery Wed Sep 28 17:47:05 2011 Errors in file /u01/oracle/admin/kmcdb/udump/kmcdb_ora_32261.trc: ORA-00600: internal error code, arguments: [4000], [7], [], [], [], [], [], [] Wed Sep 28 17:47:06 2011 Errors in file /u01/oracle/admin/kmcdb/udump/kmcdb_ora_32261.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [7], [], [], [], [], [], [] Wed Sep 28 17:47:06 2011 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 32261 ORA-1092 signalled during: alter database open resetlogs... BootstrapがORA-00600:[4000]エラになることを避けられない。これはデータベースを起動することに対して、致命的である。このデータベースをリカバリするにはbootstrapのトラブルを解決する必要がある。 どうしよう？ まずはORA-00600:[4000]内部的なエラtraceログを読んでください: *** SERVICE NAME:() 2011-09-28 17:46:09.022 *** SESSION ID:(160.3) 2011-09-28 17:46:09.022 Recovery target incarnation = 10, activation ID = 0 Influx buffer limit = 98098 (50% x 196196) Successfully allocated 7 recovery slaves Using 156 overflow buffers per recovery slave Start recovery at thread 1 ckpt scn 16228666 logseq 1 block 2 *** 2011-09-28 17:46:09.236 Media Recovery add redo thread 1 *** 2011-09-28 17:46:14.752 Media Recovery drop redo thread 1 *** 2011-09-28 17:46:41.788 Recovery target incarnation = 10, activation ID = 0 Influx buffer limit = 98098 (50% x 196196) Successfully allocated 7 recovery slaves Using 156 overflow buffers per recovery slave Start recovery at thread 1 ckpt scn 16228666 logseq 1 block 2 *** 2011-09-28 17:46:41.842 Media Recovery add redo thread 1 *** 2011-09-28 17:46:52.799 Media Recovery Log /u01/oracle/oradata/kmcdb/redo03.log ----- Redo read statistics for thread 1 ----- Read rate (ASYNC): 0Kb in 10.99s => 0.00 Mb/sec Total physical reads: 4096Kb Longest record: 0Kb, moves: 0/1 (0%) Change moves: 0/1 (0%), moved: 0Mb Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb Last redo scn: 0x0000.00f7a13b (16228667) ---------------------------------------------- *** 2011-09-28 17:46:52.835 Media Recovery drop redo thread 1 File 1 (stop scn 16228668) completed recovery at checkpoint scn 16228668 File 2 (stop scn 16228668) completed recovery at checkpoint scn 16228668 File 3 (stop scn 16228668) completed recovery at checkpoint scn 16228668 File 4 (stop scn 16228668) completed recovery at checkpoint scn 16228668 File 5 (stop scn 16228668) completed recovery at checkpoint scn 16228668 ARCH: Connecting to console port... *** 2011-09-28 17:47:05.351 Prior to RESETLOGS processing... ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE start Database is not in archivelog mode ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE complete *** 2011-09-28 17:47:05.353 *** 2011-09-28 17:47:05.707 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [4000], [7], [], [], [], [], [], [] Current SQL statement for this session: select ctime, mtime, stime from obj$ where obj# = :1 ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst()+27 call ksedst1() 0 ? 1 ? ksedmp()+557 call ksedst() 0 ? 9EF2E6D0 ? 0 ? 2A ? 9CCB06F4 ? 70000 ? ksfdmp()+19 call ksedmp() 3 ? BFAA18D0 ? AC152A0 ? CBD2D40 ? 3 ? CB843B8 ? kgeriv()+188 call 00000000 CBD2D40 ? 3 ? kgeasi()+113 call kgeriv() CBD2D40 ? B7EB0020 ? FA0 ? 1 ? BFAA190C ? ktudba()+264 call kgeasi() CBD2D40 ? B7EB0020 ? FA0 ? 2 ? 1 ? 0 ? 7 ? 0 ? ktrgcm()+6207 call ktudba() 7 ? BFAA1DEC ? 0 ? 0 ? ktrgtc()+941 call ktrgcm() B7EFF4F4 ? 0 ? B7EFE054 ? 96F1A0B4 ? 96F10CE8 ? 198 ? kdsgrp()+107 call ktrgtc() B7EFF4F4 ? B7EFF49C ? 9C22142 ? BFAA1F08 ? 240 ? 9C24DC4 ? 9C21D7C ? kdsfbrcb()+513 call kdsgrp() B7EFF4F0 ? 0 ? B7EFF4F0 ? qertbFetchByRowID() call kdsfbrcb() B7EFF4F0 ? B7EFDFEC ? 0 ? 1 ? +2052 0 ? 0 ? opifch2()+5157 call 00000000 96F10A8C ? A11CDE4 ? BFAA2534 ? 1 ? 引发ORA-00600:[4000]错误的数据块头信息: Block header dump: 0x0040007a Object id on Block? Y seg/obj: 0x12 csc: 0x00.f84ef0 itc: 1 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.01f.000011cf 0x00800057.0faf.07 --U- 1 fsc 0x0000.00f84ef1 data_block_dump,data header at 0x82bba044 以上的traceファイルの情報量は極多すぎて、一つ一つで見極めて文件信息量极大，我们来抽丝剥茧的罗列这些信息量: 1. File 1 (stop scn 16228668) completed recovery at checkpoint scn 16228668，、このログは1号データファイルが最後のcheckpoint scnにリカバリできると意味している。データベースを起動してみるときに、今のscnが16228668からはじまる。 2. ORA-00600: internal error code, arguments: [4000], [7], [], [], [], [], [], [] Current SQL statement for this session: select ctime, mtime, stime from obj$ where obj# = :1 ORA-00600:[4000], [7]エラを引き起こした文は”select ctime, mtime, stime from obj$ where obj# = :1″で、これはよく使われるアーカイブSQL文。 3. ORA-00600:[4000] raisedを引き起こしたのはktudba関数である。そのstack callはkdsgrp-> ktrgtc -> ktrgcm -> ktudba -> kgeasi(エラ処理関数) 4. ORA-00600:[4000], [7]エラを引き起こしたデータブロックは1号データファイルの122ブロックで、seg/objは0x12ブロックタイプはDataである。それに、一つのITL entryが存在している: Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.01f.000011cf 0x00800057.0faf.07 –U- 1 fsc 0x0000.00f84ef1 そのITLのScnは00f84ef1= 16273137 > current_scn=16228668 且lck=1 ここでは一つのトラブルが起こった: なぜこの”select ctime, mtime, stime from obj$ where obj# = :1″クエリ文がそのOBJ$のデータブロックのに対して、ITLに関するロールバックセグメントをアクセスするか ここでOracleにクエリ文でCR一致にブロックを読み取るブロックとブロックディレイをクリンアップするブロックを構造することに関する知識を紹介する： 1. 既存するbuffer blockがクエリのSCN要求に満たせないときに、クエリプロセスは既存するbufferとundo情報でCRブロックを構造できる。例えばcacheで既存するブロックのSCNは10だが、クエリ文が実にScn=1の時に始まったから。このクエリ文は１である。ではこのクエリ文のSnap_scnは１で、SCNの要求を満たすために、関するrollback segmentをアクセスして、その buffer blockにロールバックする必要がある。 2. 一方、クエリ文がブロックをアクセスするときに、ブロックをcleanupしてください。つまり、ディレイブロックを削除するということ(deferred block cleanout)。このようなブロックをクリンアウトするときに、クエリプロセスもそれについてのITLに関するrollback segmentをアクセスする必要がある。では、どこのブロックにアクセスするときにクリンアップする必要があるか？次の内容に紹介する。 また、ORA-00600:[4000]の話に戻ろう。そのエラに関わる対象は大事なBootstrapテーブルOBJ$で伝統的な方法を使えない：_corrupted_rollback_segments,_offline_rollback_segmentsあるいは10513トランザクションでORA-00600: [4000]を阻止できる。ブロック修正ツールBBEDでトラブルがあったデータブロックのITLトランザクションのFLAGからUからCへ(Commit)。 TRANSACTION_COMMITED = 0x08; TRANSACTION_UPBOUND = 0x02; TRANSACTION_ACTIVE = 0x01; Flag= –U- つまりTRANSACTION_UPBOUNDの時にflagが占めているバイトが0x02でそのバイトをTRANSACTION_COMMITED = 0x08に修正してください; あいにく、QQでモートアシスタンスでBBEDでデータブロックを起動したが、Terminalツールにトラブルがあるから。ログは不完全で、とくにBBEDの部分： [oracle@DBserver1 lib]$ cd /u01/oracle/oradata/kmcdb/ [oracle@DBserver1 kmcdb]$ ls control01.ctl control02.ctl.bak ctl_backup redo02.log system01.dbf users01.dbf control01.ctl.bak control03.ctl example01.dbf redo03.log temp01.dbf control02.ctl control03.ctl.bak redo01.log sysaux01.dbf undotbs01.dbf [oracle@DBserver1 kmcdb]$ cp system01.dbf ctl_backup/ [oracle@DBserver1 kmcdb]$ cp system01.dbf system01.dbf.bak 正式にBBEDする前に、すべてのデータファイルをバックアップしてください。 oracle@DBserver1 kmcdb]$ bbed filename=system01.dbf password=blockedit mode=edit BBED: Release 2.0.0.0.0 - Limited Production on Wed Sep 28 21:22:41 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. BBED> set blocksize 8192 BLOCKSIZE 8192 BBED> set block 123 BLOCK# 123 BBED> map /v File: system01.dbf (0) Block: 123 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktbbh, 48 bytes @20 ub1 ktbbhtyp @20 union ktbbhsid, 4 bytes @24 struct ktbbhcsc, 8 bytes @28 b2 ktbbhict @36 ub1 ktbbhflg @38 ub1 ktbbhfsl @39 ub4 ktbbhfnx @40 struct ktbbhitl[1], 24 bytes @44 struct kdbh, 14 bytes @68 ub1 kdbhflag @68 b1 kdbhntab @69 b2 kdbhnrow @70 sb2 kdbhfrre @72 sb2 kdbhfsbo @74 sb2 kdbhfseo @76 b2 kdbhavsp @78 b2 kdbhtosp @80 struct kdbt[1], 4 bytes @82 b2 kdbtoffs @82 b2 kdbtnrow @84 sb2 kdbr[108] @86 ub1 freespace[873] @302 ub1 rowdata[7013] @1175 ub4 tailchk @8188 ITL记录从 struct ktbbhitl[1], 24 bytes @44 开始 BBED> set offset 61 OFFSET 61 BBED> d File: system01.dbf (0) Block: 123 Offsets: 61 to 572 Dba:0x00000000 ------------------------------------------------------------------------ 200000f1 4ef80000 016c00ff ffea0053 04690369 0300006c 007b1f3a 1ffe1ec1 1e801e40 1ef81db8 1d771d29 1dec1cb0 1c681c27 1ce71ba7 1b681b27 1beb1aaf 1a701a2f 1af319b4 19731937 19f118ae 18671827 18e717a7 1766172b 17e316a2 1661161f 16e315a3 15661529 15e914a8 146c142c 14ec13b0 13731332 13f112b0 126f122f 12ee11a0 11601121 11de109b 105f1023 10e60fa3 0f620f1d 0fd80e9c 0e5a0e14 0ed30d94 0d540d13 0dd30c93 0c570c17 0cd90b98 0b580b15 0bd00a8b 0a460a01 0ac10981 09410901 09c0087d 083e0801 08bc077e 074207fc 06bb0674 063206f0 05aa0564 051e05d8 04950453 04000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 BBED> modify /x 0x80 #modify /x 20 filename 'system01.dbf' block 123. offset 61. sum apply verify そのITLトランザクションのlck=1で、lb: 0x1のrow piece記録をlb: 0x0に修正してください。 Traceでその記録は tab 0, row 26, @0x18f1 tl: 70 fb: --H-FL-- lb: 0x1 cc: 17 col 0: [ 2] c1 02 col 1: [ 4] c3 08 61 2c col 2: [ 1] 80 col 3: [12] 5f 4e 45 58 54 5f 4f 42 4a 45 43 54 col 4: [ 2] c1 02 col 5: *NULL* set offset 6454 modify /x 0x00 #modify /x 01 filename 'system01.dbf' block 123. offset 6454. sum apply verify 以上はOBJ$に対するデータブロックの修正が完成した。 データベースを再起動したが、いろんなブロックを見つけた: alter database open resetlogs Wed Sep 28 21:42:53 2011 RESETLOGS after complete recovery through change 16228672 Resetting resetlogs activation ID 523466708 (0x1f3377d4) Online log /u01/oracle/oradata/kmcdb/redo01.log: Thread 1 Group 1 was previously cleared Online log /u01/oracle/oradata/kmcdb/redo02.log: Thread 1 Group 2 was previously cleared Wed Sep 28 21:42:54 2011 Setting recovery target incarnation to 12 Wed Sep 28 21:42:54 2011 Assigning activation ID 523426694 (0x1f32db86) Thread 1 opened at log sequence 1 Current log# 3 seq# 1 mem# 0: /u01/oracle/oradata/kmcdb/redo03.log Successful open of redo thread 1 Wed Sep 28 21:42:54 2011 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Sep 28 21:42:54 2011 SMON: enabling cache recovery Wed Sep 28 21:42:54 2011 Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. Corrected file 5 plugged in read-only status in control file Dictionary check complete Wed Sep 28 21:42:54 2011 SMON: enabling tx recovery Wed Sep 28 21:42:54 2011 ********************************************************************* WARNING: The following temporary tablespaces contain no files. This condition can occur when a backup controlfile has been restored. It may be necessary to add files to these tablespaces. That can be done using the SQL statement: ALTER TABLESPACE ADD TEMPFILE Alternatively, if these temporary tablespaces are no longer needed, then they can be dropped. Empty temporary tablespace: TEMP ********************************************************************* Updating character set in controlfile to ZHS16GBK Wed Sep 28 21:42:54 2011 Errors in file /u01/oracle/admin/kmcdb/bdump/kmcdb_smon_32531.trc: ORA-00600: internal error code, arguments: [2662], [0], [16273152], [0], [16273158], [4220957], [], [] ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected. Wed Sep 28 21:42:55 2011 LOGSTDBY: Validating controlfile with logical metadata Wed Sep 28 21:42:55 2011 LOGSTDBY: Validation complete ORA-01555 caused by SQL statement below (SQL ID: 96g93hntrzjtr, SCN: 0x0000.00f84f0a): Wed Sep 28 21:42:55 2011 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 Global Name changed to KMCDB Wed Sep 28 21:42:55 2011 Non-fatal internal error happenned while SMON was doing non-existent object cleanup. SMON encountered 1 out of maximum 100 non-fatal internal errors. Wed Sep 28 21:42:56 2011 Completed: alter database open resetlogs Wed Sep 28 21:42:57 2011 Hex dump of (file 1, block 61479) in trace file /u01/oracle/admin/kmcdb/bdump/kmcdb_smon_32531.trc Corrupt block relative dba: 0x0040f027 (file 1, block 61479) Fractured block found during buffer read Data in bad block: type: 6 format: 2 rdba: 0x0040f027 last change scn: 0x0000.00f80906 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x36a70601 check value in block header: 0xe5d2 computed block checksum: 0x3fa1 Reread of rdba: 0x0040f027 (file 1, block 61479) found same corrupted data Wed Sep 28 21:42:57 2011 Errors in file /u01/oracle/admin/kmcdb/bdump/kmcdb_smon_32531.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted (file # 1, block # 61479) ORA-01110: data file 1: '/u01/oracle/oradata/kmcdb/system01.dbf' Wed Sep 28 21:42:58 2011 Starting background process CJQ0 CJQ0 started with pid=24, OS id=724 Wed Sep 28 21:43:01 2011 Errors in file /u01/oracle/admin/kmcdb/bdump/kmcdb_m000_720.trc: ORA-00600: internal error code, arguments: [2662], [0], [16273222], [0], [16273348], [12624044], [], [] Wed Sep 28 21:43:02 2011 Errors in file /u01/oracle/admin/kmcdb/bdump/kmcdb_m000_720.trc: ORA-00600: internal error code, arguments: [2662], [0], [16273288], [0], [16273348], [12624044], [], [] ORA-00600: internal error code, arguments: [2662], [0], [16273222], [0], [16273348], [12624044], [], [] Wed Sep 28 21:44:35 2011 create temporary tablespace temp02 tempfile '/u01/oracle/oradata/temp02.dbf' size 400M Wed Sep 28 21:44:35 2011 Completed: create temporary tablespace temp02 tempfile '/u01/oracle/oradata/temp02.dbf' size 400M Wed Sep 28 21:45:52 2011 create undo tablespace undo03 datafile '/u01/oracle/oradata/undo03.dbf' size 500M Hex dump of (file 3, block 3839) in trace file /u01/oracle/admin/kmcdb/udump/kmcdb_ora_749.trc Corrupt block relative dba: 0x00c00eff (file 3, block 3839) Fractured block found during buffer read Data in bad block: type: 6 format: 2 rdba: 0x00c00eff last change scn: 0x0000.00f7be10 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x49b20601 check value in block header: 0xe0a3 computed block checksum: 0xf7a2 Reread of rdba: 0x00c00eff (file 3, block 3839) found same corrupted data Completed: create undo tablespace undo03 datafile '/u01/oracle/oradata/undo03.dbf' size 500M Wed Sep 28 21:45:58 2011 Errors in file /u01/oracle/admin/kmcdb/bdump/kmcdb_smon_32531.trc: ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], [] Wed Sep 28 21:45:58 2011 Non-fatal internal error happenned while SMON was doing Corrupt Block Seg Info. SMON encountered 2 out of maximum 100 non-fatal internal errors. Wed Sep 28 21:47:15 2011 ALTER SYSTEM SET undo_management='AUTO' SCOPE=SPFILE; Wed Sep 28 21:47:19 2011 Starting background process EMN0 EMN0 started with pid=22, OS id=757 Wed Sep 28 21:47:19 2011 Shutting down instance: further logons disabled Wed Sep 28 21:47:19 2011 Stopping background process CJQ0 Wed Sep 28 21:47:19 2011 新しいUndoテーブルスペースを作成して、切り替えて、持続的現れるORA-600トラブルを避けられる。 以上のalert.logで2662と4097内部的なエラはデータベースをcrashさせないが、非常手段で起動したデータベースもう安定していないから、早速にデータをバックアップして、データベースを再構造してください: run{ set maxcorrupt for datafile 1,2,3,4,5 to 100; backup as compressed backupset incremental level 0 database; } exp system/oracle full=y file=full.dmp 友達はデータベースのデータがとっても大切だと何度も言ったが、その行為がそのデータが実にどうでもいいと示している。 データがすごく大事だと思っているなら、データを大事にしてください。何よりも大事なことはデータバックアップである。バックアップなしにデータが永遠に保てると思わないでください！