ORACLEデータベース によくあるエラ の解決策
プロの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 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したあと、databaseをリカバリして、データベースを起動する :
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=7のロールバックセグメントを使っている時にrollback segmentにエラがあることを気づいた。(ora600 [2662]エラと間違えないようにしてください]。それにORA-00600: [4000]はORA-00704: bootstrap process failureエラも同時に起きている。ロールバックが必要としているデータブロックはbootstrapの大切なオブジェクトと意味している。
一般的に、bootstrap objectはロールバックあるいはクリンアップが必要としているが、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,このログは一つ目のデータファイルが最後の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文である。クエリのオブジェクトはcriticalのbootstrapオブジェクトOBJ$ベーステーブルである。
3. ORA-00600:[4000] raisedを引き起こしたのはktudba関数で、これがOracleエラ構成を引き起こした。その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関係するrollback segmentをアクセスする必要があるか?
ここで、OracleにクエリでCR一致性ブロックリドとブロックディレイ削除の意味を紹介する必要がある:
1. 今のbuffer blockがクエリのSCN要求に満足できなければ、クエリプロセスは既存するbufferとundo情報でCRブロックを構造する。例えば、cacheに既存するblockのSCNは10だが、わたしのクエリ文はScn=1の時から始まった、ではこのクエリ文の的Snap_scnは1でそのSCNの要求を満たすために、関連するrollback segmentをアクセスして、 buffer blockにロールバックする必要がある。
2. 一方、クエリがブロックをアクセスしている時に、ブロックをクリンアップする必要があるかもしれない。つまり、ディレイブロック削除である (deferred block cleanout)。このような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$でデータブロックの修正が終わった
以上のような人工的にBBEDオペレーションを修正出来たら、データベースを成功に起動できたが、いろんなベッドブロックが見つけた:
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
そのデータがどれほど大切か、彼に何度も聞いたが、その行為はあんなデータがどうでもいいと言っている。
本当に大切なデータだと思っていれば、バックアップを用意してください。
Comment