Oracle数据恢复:解决ORA-00600:[4000] ORA-00704: bootstrap process failure错误一例

节前帮网友打开了一套断电后无法正常启动的10.2.0.1数据库,该库使用非归档模式且没有任何备份。

主机断电后,该网友尝试重启数据库实例,但是遇到了ORA-00600:[kccpb_sanity_check_2]内部错误:

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

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 并 open 数据库 :

 

 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数据块时发现rollback segment存在讹误(注意不要和ora600 [2662]错误混淆],且该ORA-00600: [4000]伴随有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,当尝试open database时数据库当前的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$基表,这说明需要cleanup的块时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做”rollback”。

2. 另一方面当查询语句访问块时,可能需要对块做cleanup,也就是我们说的延迟块清除(deferred block cleanout),当这种Block Cleanout发生时查询进程也需要去访问块上ITL相关的 rollback segment ,那么具体当访问到哪些块时需要做cleanout呢?这个将会在今后的文章中详述。

回归到上面的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

 

虽然该网友和我一再强调他的数据库中的数据十分重要,但是他的行为是在无声的诉说这套数据库里的数据是无关紧要的。

如果你觉得数据是important的,那么请善待数据。 善待数据的第一条准则是 数据备份重于一切,如果没有备份 那么请不要奢望数据是持久的!

手工模拟Oracle数据块逻辑讹误引发,ORA-00600:[13013] [5001]一例

上周在客户那里遇到了一例由Oracle Bug引发的表数据块逻辑讹误触发ORA-00600:[13013], [5001]的问题,这里为了更好地说明该问题,于是萌发了手工模拟该数据块逻辑讹误的想法。

基础知识

Oracle中表的数据块由块头、事务槽、行字典、行数据等多种结构组成。 行数据(rowdata)实际是由许多row piece 行片组成的,每一条row piece的头部都有flag、locks、cols(cc)三个标志位。

其中flag标记了该row piece的类型,该flag位占用一个字节,其不同的bit位代表不同的含义,见下表:

ROW_CLUSTER_KEY = 0x80;              KDRHFK
ROW_CTABLE_NUMBER = 0x40;            KDRHFC
ROW_HEAD_PIECE = 0x20;               KDRHFH
ROW_DELETED_ROW = 0x10;              KDRHFD
ROW_FIRST_PIECE = 0x08;              KDRHFF
ROW_LAST_PIECE = 0x04;               KDRHFL
ROW_FROM_PREVIOUS = 0x02;            KDRHFP
ROW_CONTINUE_NEXT = 0x01;            KDRHFN

一般来说最普通的一条row piece是普通堆表(heap table)的未被删除的且无行迁移/链接的,其flag位应为

普通row的flag一般为
Single Row =
ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE= 0x20 + 0x08 + 0x04= 0x2c
===================================================================================
cluster key的flag一般为
Cluster Key =
ROW_CLUSTER_KEY + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE=
KDRHFL, KDRHFF, KDRHFH, KDRHFK =0x80 + 0x2c =  0xac
BBED> x /rn
rowdata[68]                                 @8166
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    1
mref@8171:    1
hrid@8173:0x01800014.0
nrid@8179:0x01800014.0
col    0[2] @8185: 10 
===================================================================================
Cluster Row =
ROW_CTABLE_NUMBER + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE =
(KDRHFL, KDRHFF, KDRHFH, KDRHFC) = 0x6c 
BBED> x /rncc
rowdata[0]                                  @8098
----------
flag@8098: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8099: 0x00
cols@8100:   10
col    0[2] @8102: 200
col    1[8] @8105: Jennifer
col    2[6] @8114: Whalen
col    3[7] @8121: JWHALEN
col   4[12] @8129: 515.123.4444
col    5[7] @8142: w....
col    6[7] @8150: AD_ASST
col    7[2] @8158: 
col    8[0] @8161: *NULL*
col    9[3] @8162: .
出现ORA-00600:[13013], [5001]且Arg [f] Code =3 代表这一row piece的flag >0xc0,
也就是该行片同时被标记为key和clustered(row is marked as both a Key and Clustered), 其检验代码为check code 6251。
当flag >= 0xc0 时 会出现kdrchk: row is marked as both a Key and Clustered Block 12 failed with check code 6251
当 0xac >flag >= 0xa0 时 会 kdrchk: row is Key and Not only piece of key Block 12 failed with check code 6255
当 flag = 0x43 是 会出现 kdrchk: C and neither of H or F Block 12 failed with check code 6263
当 flag = 0x83 时 会出现 kdrchk: row is marked both as a Key and being continued Block 12 failed with check code 6254

 

当Oracle进程访问数据块时首先会校验block的sum值并与block中的CHECKSUM值进行对比,若一致则说明该block没有物理讹误。但是光这一项检查是不够的,不足以保证block无误。所以Oracle引入了一些列的逻辑检验,每一种逻辑检验对应一个检测代码(check code),这些检测包括row piece的flag、cols(cc)状态是否正确等。

实际负责这类逻辑检验的函数包括:kdbchk、kddummy_blkchk、kco_blkchk、kdBlkCheckError、kdrchk等等。

这里当服务进程访问到问题数据块,检测代码发现其flag为0xff(KCHDFLPN),该flag从逻辑上讲是冲突的,所以检测代码认为该row piece存在异常,进而会引发update的ORA-00600:[13013], [5001]或查询的ORA-600 [qertbFetchByRowID]内部错误。

这里需要说明一下的是,很多人认为dbv工具时无法检测出逻辑讹误的,实际上dbv、rman、validate structure和bbed-verify均可以检测出一定程度的逻辑讹误,但是最可靠的还是db_block_checksum=true情况下的validate structure [online]验证命令。从另一个角度来说,普通的dbv只能做单一的检测,而无法做到交叉地检验,从而了解表和索引上的不一致问题,但是validate structure online却可以做到。


正式模拟

以上我们了解了ORA-00600:[13013], [5001]内部错误是如何被引发的,那么下面手工模拟该错误也就不困难了,当然这里需要用到bbed工具。

以下我们会创建实验用的tablespace,table,index:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
/* 创建实验用的表空间  */
SQL> create tablespace maclean datafile '/home/oracle/maclean.dbf' size 20M;
Tablespace created.
SQL> create table tv tablespace maclean as select rownum t1,'find me' t2 from
dba_tables where rownumcreate index ind_tv on tv(t1) tablespace users;
Index created.
SQL> update tv set t2='corrption here' where t1=200;
update tv set t2='corrption here' where t1=200
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TV"."T2" (actual: 14, maximum: 7)
SQL> alter table tv modify t2 varchar2(200);
Table altered.
SQL> update tv set t2='corruption here' where t1=200;
1 row updated.
SQL> commit;
Commit complete.
/* 以上创建了示例用表,其中t1=200的记录是之后将会
手动修改为存在讹误的行             */
SQL> select dump(200,16) from dual;
DUMP(200,16)
-----------------
Typ=2 Len=2: c2,3
/* 通过16进制码可以方便找出该t1=200的记录行 */ 
SQL> alter system checkpoint;
System altered.
SQL> alter tablespace maclean read only;
Tablespace altered.
SQL> select dbms_rowid.rowid_block_number(rowid) bno ,dbms_rowid.rowid_relative_fno(rowid) fno from tv;
BNO FNO
---------- ----------
12 6
[oracle@rh2 ~]$ cp maclean.dbf maclean.dbf.bak

 

接着使用BBED工具找到目标行并实施手工修改:

 

[oracle@rh2 ~]$ bbed filename=maclean.dbf mode=edit
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun Sep 18 22:14:59 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set block 13
BLOCK# 13
BBED> map /v
File: maclean.dbf (0)
Block: 13 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, 96 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[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
b1 kdbhntab @125
b2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
b2 kdbhavsp @134
b2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
b2 kdbtoffs @138
b2 kdbtnrow @140
sb2 kdbr[200] @142
ub1 freespace[4725] @542
ub1 rowdata[2921] @5267
ub4 tailchk @8188
BBED> find /x c203
File: maclean.dbf (0)
Block: 13 Offsets: 5271 to 5782 Dba:0x00000000
------------------------------------------------------------------------
c2030f63 6f727275 7074696f 6e206865 72652c00 0202c203 0766696e 64206d65
2c000203 c2026407 66696e64 206d652c 000203c2 02630766 696e6420 6d652c00
0203c202 62076669 6e64206d 652c0002 03c20261 0766696e 64206d65 2c000203
c2026007 66696e64 206d652c 000203c2 025f0766 696e6420 6d652c00 0203c202
5e076669 6e64206d 652c0002 03c2025d 0766696e 64206d65 2c000203 c2025c07
66696e64 206d652c 000203c2 025b0766 696e6420 6d652c00 0203c202 5a076669
6e64206d 652c0002 03c20259 0766696e 64206d65 2c000203 c2025807 66696e64
206d652c 000203c2 02570766 696e6420 6d652c00 0203c202 56076669 6e64206d
652c0002 03c20255 0766696e 64206d65 2c000203 c2025407 66696e64 206d652c
000203c2 02530766 696e6420 6d652c00 0203c202 52076669 6e64206d 652c0002
03c20251 0766696e 64206d65 2c000203 c2025007 66696e64 206d652c 000203c2
024f0766 696e6420 6d652c00 0203c202 4e076669 6e64206d 652c0002 03c2024d
0766696e 64206d65 2c000203 c2024c07 66696e64 206d652c 000203c2 024b0766
696e6420 6d652c00 0203c202 4a076669 6e64206d 652c0002 03c20249 0766696e
64206d65 2c000203 c2024807 66696e64 206d652c 000203c2 02470766 696e6420
6d652c00 0203c202 46076669 6e64206d 652c0002 03c20245 0766696e 64206d65
找到了t1=200的偏移值为5271
则其fb的偏移值为5271 -4 = 5267
BBED> set offset 5267
OFFSET 5267
BBED> d
File: maclean.dbf (0)
Block: 13 Offsets: 5267 to 5778 Dba:0x00000000
------------------------------------------------------------------------
2c020202 c2030f63 6f727275 7074696f 6e206865 72652c00 0202c203 0766696e
64206d65 2c000203 c2026407 66696e64 206d652c 000203c2 02630766 696e6420
6d652c00 0203c202 62076669 6e64206d 652c0002 03c20261 0766696e 64206d65
2c000203 c2026007 66696e64 206d652c 000203c2 025f0766 696e6420 6d652c00
0203c202 5e076669 6e64206d 652c0002 03c2025d 0766696e 64206d65 2c000203
c2025c07 66696e64 206d652c 000203c2 025b0766 696e6420 6d652c00 0203c202
5a076669 6e64206d 652c0002 03c20259 0766696e 64206d65 2c000203 c2025807
66696e64 206d652c 000203c2 02570766 696e6420 6d652c00 0203c202 56076669
6e64206d 652c0002 03c20255 0766696e 64206d65 2c000203 c2025407 66696e64
206d652c 000203c2 02530766 696e6420 6d652c00 0203c202 52076669 6e64206d
652c0002 03c20251 0766696e 64206d65 2c000203 c2025007 66696e64 206d652c
000203c2 024f0766 696e6420 6d652c00 0203c202 4e076669 6e64206d 652c0002
03c2024d 0766696e 64206d65 2c000203 c2024c07 66696e64 206d652c 000203c2
024b0766 696e6420 6d652c00 0203c202 4a076669 6e64206d 652c0002 03c20249
0766696e 64206d65 2c000203 c2024807 66696e64 206d652c 000203c2 02470766
696e6420 6d652c00 0203c202 46076669 6e64206d 652c0002 03c20245 0766696e
/* 找到指定行的地址为5267,其当前flag为正常的0x2c  */
BBED> x /rnc
rowdata[0] @5267
----------
flag@5267: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5268: 0x02
cols@5269: 2
col 0[2] @5270: 200
col 1[15] @5273: corruption here
修改该flag 为 0xff BBED> modify /x 0xff
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: maclean.dbf (0)
Block: 13 Offsets: 5267 to 5778 Dba:0x00000000
------------------------------------------------------------------------
ff020202 c2030f63 6f727275 7074696f 6e206865 72652c00 0202c203 0766696e
64206d65 2c000203 c2026407 66696e64 206d652c 000203c2 02630766 696e6420
6d652c00 0203c202 62076669 6e64206d 652c0002 03c20261 0766696e 64206d65
2c000203 c2026007 66696e64 206d652c 000203c2 025f0766 696e6420 6d652c00
0203c202 5e076669 6e64206d 652c0002 03c2025d 0766696e 64206d65 2c000203
c2025c07 66696e64 206d652c 000203c2 025b0766 696e6420 6d652c00 0203c202
5a076669 6e64206d 652c0002 03c20259 0766696e 64206d65 2c000203 c2025807
66696e64 206d652c 000203c2 02570766 696e6420 6d652c00 0203c202 56076669
6e64206d 652c0002 03c20255 0766696e 64206d65 2c000203 c2025407 66696e64
206d652c 000203c2 02530766 696e6420 6d652c00 0203c202 52076669 6e64206d
652c0002 03c20251 0766696e 64206d65 2c000203 c2025007 66696e64 206d652c
000203c2 024f0766 696e6420 6d652c00 0203c202 4e076669 6e64206d 652c0002
03c2024d 0766696e 64206d65 2c000203 c2024c07 66696e64 206d652c 000203c2
024b0766 696e6420 6d652c00 0203c202 4a076669 6e64206d 652c0002 03c20249
0766696e 64206d65 2c000203 c2024807 66696e64 206d652c 000203c2 02470766
696e6420 6d652c00 0203c202 46076669 6e64206d 652c0002 03c20245 0766696e
BBED> x /rnc
rowdata[0] @5267
----------
flag@5267: 0xff (KDRHFN, KDRHFP, KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC, KDRHFK)
lock@5268: 0x02
cols@5269: 0
ckix@5270: 2
BBED> sum apply
Check value for File 0, Block 13:
current = 0x0000, required = 0x0000
我们使用bbed的verify命令验证数据块会发现问题flag
BBED> verify
DBVERIFY - Verification starting
FILE = maclean.dbf
BLOCK = 12
kdrchk: row is marked as both a Key and Clustered
prow=0x7f5335f05693 flag=0xff
Block Checking: DBA = 25165836, Block Type = KTB-managed data block
data header at 0x7f5335f0427c
kdbchk: bad row tab 0, slot 199
Block 12 failed with check code 6251
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
使用dbv工具是也可以验证这种逻辑讹误的
[oracle@rh2 ~]$ dbv file=maclean.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Sun Sep 18 22:27:49 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = maclean.dbf
kdrchk: row is marked as both a Key and Clustered
prow=0x7f9ef25f7693 flag=0xff
Block Checking: DBA = 25165836, Block Type = KTB-managed data block
data header at 0x7f9ef25f627c
kdbchk: bad row tab 0, slot 199
Page 12 failed with check code 6251
DBVERIFY - Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 1
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2548
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 691691 (0.691691)

 

回到sqlplus中访问之前修改的数据行,触发ORA-600[13013] [5001]错误:

 

SQL> alter system flush buffer_cache;
System altered.
SQL> update tv set t2='correct here' where t1=200;
update tv set t2='correct here' where t1=200
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [13013], [5001], [52937],
[25165836], [199], [25165836], [3], []
PLAN_TABLE_OUTPUT
---------------------------------------------------------
Plan hash value: 568795662
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |        |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  UPDATE           | TV     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_TV |     1 |   115 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=200)
SQL> select * from tv where t1=200;
select * from tv where t1=200
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qertbFetchByRowID], [], [], [], [],
[], [], []
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1015724781
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TV     |     1 |   115 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TV |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=200)

可以看到当正好update到问题行记录时如预料出现了ORA-00600:[13013], [5001]错误,而ACCESS BY INDEX ROWID时出现了ORA-00600:[qertbFetchByRowID]。

解决方案

1.在有备份的情况下可以通过blockrecovery在线修复该问题数据块:

RMAN> blockrecover datafile 6 block 12;

Starting blockrecover at 18-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 18-SEP-11

但是请注意如果该逻辑讹误确实是由Oracle Bug引起的话,那么很有可能blockrecover也无能为力,那么可以借鉴第二种方法。

 

2. 第二种方法针对没有备份可用的数据库或者recover数据块不管用的场景,可以设置10231事件并ctas复制该表,但是这种方法可能会丢失有问题的行记录:

SQL> alter session set events ‘10231 trace name context forever, level 10’

SQL> Create table.TABLE_COPY as select * from TABLE;

 

了解更多关于kdrchk函数的信息:

Add check for continued row piece pointing to itself with
corruption description:
"kdrchk: Row piece pointing to itself"
DB_BLOCK_CHECKING = MEDIUM will check for row pieces where the
next rowid (nrid) points to itself (chained row points to itself).
It produces error ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError]
with check code [6266] (3rd ORA-600 argument).
DBVERIFY reports the same corruption description if the block is corrupt on disk.
RMAN when run with the CHECK LOGICAL option reports it as
corruption_type=CORRUPT/LOGICAL in v$database_block_corruption.
"ANALYZE TABLE  VALIDATE STRUCTURE" produces error ORA-1498 and trace file
shows the same corruption description.
With this fix in place DBMS_REPAIR can be used to identify and mark the affected
block as Soft Corrupt producing error ORA-1578 and it can be skipped it for DML's
using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS.
[CM][SG][event 1][domain Q423][mem 0] Joining shared group
kdrchk: column length 0 but not null
prow=0x2a97f4d9d6 flag=0x2c column=57
Block Checking: DBA = 29635651, Block Type = KTB-managed data block
data header at 0x2a97f4be7c
kdbchk: bad row tab 0, slot 2
data_block_dump,data header at 0x2a97d113d8
data_block_dump,data header at 0x2a97d113d8
kdrchk: found invalid symbol reference 48
reference to delete symbol
valid symbol range [0,78)
Block Checking: DBA = 411055291, Block Type = KTB-managed data block
data header at 0x68a3f4
kdbchk: bad row tab 0, slot 4
Page 13499 failed with check code 6265
kdrchk: C and neither of H or F
prow=0x4282803ae flag=0x41
Block Checking: DBA = 322963095, Block Type = KTB-managed data block
data header at 0x42828007c
kdrchk: column length 0 but not null
prow=0x10021035e flag=0x2c column=40
Block Checking: DBA = 25189259, Block Type = KTB-managed data block
data header at 0x10020fe7c
kdbchk: bad row tab 0, slot 0
Page 23435 failed with check code 6264
kdrchk: column length 0 but not null
prow=0x1002122e5 flag=0x2c column=40
Block Checking: DBA = 25189260, Block Type = KTB-managed data block
kdrchk:  row is marked as both a Key and Clustered
prow=0xd2bfa981 flag=0xff
File#67, Block#74754
kdbchk: bad row tab 0, slot 0
kdrchk:  no columns, but has one of P or N
prow=0x934fbffa flag=0x31
DIAGNOSTIC ANALYSIS:
====================
A look at the block dump in the analyze trace file revealed two very
suspicious looking rows:
tab 0, row 0, @0x1ede
tl: 2 fb: --HD---N lb: 0x0
tab 0, row 1, @0x1edc
tl: 2 fb: --HD---N lb: 0x0
The flag bytes in these rows look incorrect.

待修订!

Oracle内部错误:ORA-00600[2608]一例

一套10.2.0.4的单节点数据库在恢复数据文件时出现了ORA-00600: internal error code, arguments: [2608], [1], [0], [690423], [0], [690425], [], []内部错误,其日志如下:

 

如果自己搞不定可以找ASKMACLEAN专业ORACLE数据库修复团队成员帮您恢复!

 

 

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /s01/db_1
System name:    Linux
Node name:      rh2.oracle.com
Release:        2.6.18-194.el5
Version:        #1 SMP Mon Mar 29 22:10:29 EDT 2010
Machine:        x86_64
Instance name: G10R2
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 21360, image: oracle@rh2.oracle.com (TNS V1-V3)
*** 2011-04-27 21:20:40.979
*** ACTION NAME:() 2011-04-27 21:20:40.979
*** MODULE NAME:(sqlplus@rh2.oracle.com (TNS V1-V3)) 2011-04-27 21:20:40.979
*** SERVICE NAME:(SYS$USERS) 2011-04-27 21:20:40.979
*** SESSION ID:(159.3) 2011-04-27 21:20:40.979
kwqmnich: current time:: 13: 20: 40
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
Recovery target incarnation = 2, activation ID = 0
Influx buffer limit = 52443 (50% x 104887)
Successfully allocated 2 recovery slaves
Using 550 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 690423 logseq 1 block 3183
*** 2011-04-27 21:20:46.165
Media Recovery add redo thread 1
*** 2011-04-27 21:20:46.172
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [2608], [1], [0], [690423], [0], [690425], [], []
Current SQL statement for this session:
ALTER DATABASE RECOVER  datafile 9
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
7FFF6F1795E0 ? 7FFF6F179640 ?
7FFF6F179580 ? 000000000 ?
ksfdmp()+21          call     ksedmp()             000000003 ? 000000001 ?
7FFF6F1795E0 ? 7FFF6F179640 ?
7FFF6F179580 ? 000000000 ?
kgeriv()+176         call     ksfdmp()             000000003 ? 000000001 ?
7FFF6F1795E0 ? 7FFF6F179640 ?
7FFF6F179580 ? 000000000 ?
kgesiv()+119         call     kgeriv()             0068966E0 ? 00A81E650 ?
000000000 ? 7FFF6F178F08 ?
7FFF6F179580 ? 000000000 ?
ksesic5()+215        call     kgesiv()             0068966E0 ? 00A81E650 ?
000000A30 ? 000000005 ?
7FFF6F17A360 ? 000000000 ?
kcrfro()+6796        call     ksesic5()            000000A30 ? 000000000 ?
000000001 ? 000000000 ?
000000000 ? 000000000 ?
kcramr()+7872        call     kcrfro()             2B9DB2D29400 ? 000000000 ?
000000001 ? 000000000 ?
000000000 ? 000000000 ?
krddmr()+1290        call     kcramr()             2B9DB2CF70E0 ? 00A7F8280 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
adbdrv()+10248       call     krddmr()             00A7F8280 ? 000000000 ?
7FFF6F182FC4 ? 000000000 ?
2B9DB2CF70E0 ?
A7F828000000001 ?
opiexe()+13505       call     adbdrv()             00A7F8280 ? 000000000 ?
0A2806BD8 ? 000000000 ?
2B9DB2CF70E0 ?
A7F828000000001 ?
opiosq0()+3316       call     opiexe()             000000004 ? 000000000 ?
7FFF6F184238 ? 000000012 ?
2B9DB2CF70E0 ?
A7F828000000001 ?
kpooprx()+315        call     opiosq0()            000000003 ? 00000000E ?
7FFF6F1843A8 ? 0000000A4 ?
2B9DB2CF70E0 ?
A7F828000000001 ?
kpoal8()+799         call     kpooprx()            7FFF6F187554 ? 7FFF6F185530 ?
000000024 ? 000000001 ?
000000000 ? A7F828000000001 ?
opiodr()+984         call     kpoal8()             00000005E ? 000000017 ?
7FFF6F187550 ? 000000001 ?
000000001 ? A7F828000000001 ?
ttcpip()+1012        call     opiodr()             00000005E ? 000000017 ?
7FFF6F187550 ? 000000000 ?
0059C0990 ? A7F828000000001 ?
opitsk()+1322        call     ttcpip()             00689E3B0 ? 000000001 ?
7FFF6F187550 ? 000000000 ?
7FFF6F187048 ? 7FFF6F1876B8 ?
opiino()+1026        call     opitsk()             000000003 ? 000000000 ?
7FFF6F187550 ? 000000001 ?
=========================================================

该ORA-00600[2608]可能由数据文件头中记录的checkpoint scn过小造成,Oracle会将该checkpoint scn与块中的resetlogs scn以及控制文件中记录的日志文件的Low scn相比较,若文件头中的checkpoint scn远小于对比值,那么就会出现ORA-00600[2608]内部错误。

下面我们通过修改数据文件头中kcvfhckp结构中记录的checkpoint scn到一个较小值,来模拟出发ORA-00600[2608]内部错误:

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump file_hdrs 8;
Statement processed.
SQL> oradebug tracefile_name;
DATA FILE #11:
(name #17) /u01/data02.dbf
creation size=6400 block size=8192 status=0x1c head=17 tail=17 dup=1
tablespace 12, index=12 krfil=11 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:4 scn: 0x0000.000b01c2 04/27/2011 22:52:31
Stop scn: 0x0000.000b01c5 04/27/2011 22:52:39
Creation Checkpointed at scn:  0x0000.000b01a8 04/27/2011 22:52:24
thread:1 rba:(0xb.e.10)
....................................................
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
V10 STYLE FILE HEADER:
Compatibility Vsn = 169870080=0xa200300
Db ID=2894437650=0xac859d12, Db Name='G10R2'
Activation ID=0=0x0
Control Seq=740=0x2e4, File size=6400=0x1900
File Number=11, Blksiz=8192, File Type=3 DATA
Tablespace #12 - DATA02  rel_fn:11
Creation   at   scn: 0x0000.000b01a8 04/27/2011 22:52:24
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 01/01/1988 00:00:00
status:0x4 root dba:0x00000000 chkpt cnt: 4 ctl cnt:3
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000b01c2 04/27/2011 22:52:31
/* 可以看到以上11号数据文件头的Checkpoint scn为0x0000.000b01c2 ,
而resetlogs scn为0x0000.000a88f9                                        */
/* 我们将Checkpoint scn修改为0x0000.000a88f7                      */
[oracle@rh2 u01]$ bbed filename=data02.dbf blocksize=8192 password=blockedit mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Wed Apr 27 22:55:30 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set blocksize 8192
BLOCKSIZE       8192
BBED> set block 2
BLOCK#          2
BBED> map
File: data02.dbf (0)
Block: 2                                     Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes                    @0       
ub4 tailchk                                @8188    
BBED> p kcvfh
struct kcvfh, 676 bytes                     @0       
struct kcvfhbfh, 20 bytes                @0       
ub1 type_kcbh                         @0        0x0b
ub1 frmt_kcbh                         @1        0xa2
ub1 spare1_kcbh                       @2        0x00
ub1 spare2_kcbh                       @3        0x00
ub4 rdba_kcbh                         @4        0x02c00001
ub4 bas_kcbh                          @8        0x00000000
ub2 wrp_kcbh                          @12       0x0000
ub1 seq_kcbh                          @14       0x01
ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
ub2 chkval_kcbh                       @16       0xb4a1
ub2 spare3_kcbh                       @18       0x0000
struct kcvfhhdr, 76 bytes                @20      
ub4 kccfhswv                          @20       0x00000000
ub4 kccfhcvn                          @24       0x0a200300
ub4 kccfhdbi                          @28       0xac859d12
text kccfhdbn[0]                      @32      G
text kccfhdbn[1]                      @33      1
text kccfhdbn[2]                      @34      0
text kccfhdbn[3]                      @35      R
text kccfhdbn[4]                      @36      2
text kccfhdbn[5]                      @37       
text kccfhdbn[6]                      @38       
text kccfhdbn[7]                      @39       
ub4 kccfhcsq                          @40       0x000002e4
ub4 kccfhfsz                          @44       0x00001900
s_blkz kccfhbsz                       @48       0x00
ub2 kccfhfno                          @52       0x000b
ub2 kccfhtyp                          @54       0x0003
ub4 kccfhacid                         @56       0x00000000
ub4 kccfhcks                          @60       0x00000000
text kccfhtag[0]                      @64       
text kccfhtag[1]                      @65       
text kccfhtag[2]                      @66       
text kccfhtag[3]                      @67       
text kccfhtag[4]                      @68       
text kccfhtag[5]                      @69       
text kccfhtag[6]                      @70       
text kccfhtag[7]                      @71       
text kccfhtag[8]                      @72       
text kccfhtag[9]                      @73       
text kccfhtag[10]                     @74       
text kccfhtag[11]                     @75       
text kccfhtag[12]                     @76       
text kccfhtag[13]                     @77       
text kccfhtag[14]                     @78       
text kccfhtag[15]                     @79       
text kccfhtag[16]                     @80       
text kccfhtag[17]                     @81       
text kccfhtag[18]                     @82       
text kccfhtag[19]                     @83       
text kccfhtag[20]                     @84       
text kccfhtag[21]                     @85       
text kccfhtag[22]                     @86       
text kccfhtag[23]                     @87       
text kccfhtag[24]                     @88       
text kccfhtag[25]                     @89       
text kccfhtag[26]                     @90       
text kccfhtag[27]                     @91       
text kccfhtag[28]                     @92       
text kccfhtag[29]                     @93       
text kccfhtag[30]                     @94       
text kccfhtag[31]                     @95       
ub4 kcvfhrdb                             @96       0x00000000
struct kcvfhcrs, 8 bytes                 @100     
ub4 kscnbas                           @100      0x000b01a8
ub2 kscnwrp                           @104      0x0000
ub4 kcvfhcrt                             @108      0x2cae0628
ub4 kcvfhrlc                             @112      0x2cade887
   struct kcvfhrls, 8 bytes                 @116                             resetlogs scn
ub4 kscnbas                           @116      0x000a88f9
ub2 kscnwrp                           @120      0x0000
ub4 kcvfhbti                             @124      0x00000000
struct kcvfhbsc, 8 bytes                 @128     
ub4 kscnbas                           @128      0x00000000
ub2 kscnwrp                           @132      0x0000
ub2 kcvfhbth                             @136      0x0000
ub2 kcvfhsta                             @138      0x0004 (KCVFHOFZ)
   struct kcvfhckp, 36 bytes                @484     
struct kcvcpscn, 8 bytes              @484                               checkpoint scn 
ub4 kscnbas                        @484      0x000b01c2
ub2 kscnwrp                        @488      0x0000
ub4 kcvcptim                          @492      0x2cae062f
ub2 kcvcpthr                          @496      0x0001
union u, 12 bytes                     @500     
struct kcvcprba, 12 bytes          @500     
ub4 kcrbaseq                    @500      0x0000000b
ub4 kcrbabno                    @504      0x0000001b
ub2 kcrbabof                    @508      0x0010
ub1 kcvcpetb[0]                       @512      0x02
ub1 kcvcpetb[1]                       @513      0x00
ub1 kcvcpetb[2]                       @514      0x00
ub1 kcvcpetb[3]                       @515      0x00
ub1 kcvcpetb[4]                       @516      0x00
ub1 kcvcpetb[5]                       @517      0x00
ub1 kcvcpetb[6]                       @518      0x00
ub1 kcvcpetb[7]                       @519      0x00
ub4 kcvfhcpc                             @140      0x00000004
ub4 kcvfhrts                             @144      0x00000000
ub4 kcvfhccc                             @148      0x00000003
struct kcvfhbcp, 36 bytes                @152     
struct kcvcpscn, 8 bytes              @152     
ub4 kscnbas                        @152      0x00000000
ub2 kscnwrp                        @156      0x0000
ub4 kcvcptim                          @160      0x00000000
ub2 kcvcpthr                          @164      0x0000
union u, 12 bytes                     @168     
struct kcvcprba, 12 bytes          @168     
ub4 kcrbaseq                    @168      0x00000000
ub4 kcrbabno                    @172      0x00000000
ub2 kcrbabof                    @176      0x0000
ub1 kcvcpetb[0]                       @180      0x00
ub1 kcvcpetb[1]                       @181      0x00
ub1 kcvcpetb[2]                       @182      0x00
ub1 kcvcpetb[3]                       @183      0x00
ub1 kcvcpetb[4]                       @184      0x00
ub1 kcvcpetb[5]                       @185      0x00
ub1 kcvcpetb[6]                       @186      0x00
ub1 kcvcpetb[7]                       @187      0x00
ub4 kcvfhbhz                             @312      0x00000000
struct kcvfhxcd, 16 bytes                @316     
ub4 space_kcvmxcd[0]                  @316      0x00000000
ub4 space_kcvmxcd[1]                  @320      0x00000000
ub4 space_kcvmxcd[2]                  @324      0x00000000
ub4 space_kcvmxcd[3]                  @328      0x00000000
word kcvfhtsn                            @332      12
ub2 kcvfhtln                             @336      0x0006
text kcvfhtnm[0]                         @338     D
text kcvfhtnm[1]                         @339     A
text kcvfhtnm[2]                         @340     T
text kcvfhtnm[3]                         @341     A
text kcvfhtnm[4]                         @342     0
text kcvfhtnm[5]                         @343     2
text kcvfhtnm[6]                         @344      
text kcvfhtnm[7]                         @345      
text kcvfhtnm[8]                         @346      
text kcvfhtnm[9]                         @347      
text kcvfhtnm[10]                        @348      
text kcvfhtnm[11]                        @349      
text kcvfhtnm[12]                        @350      
text kcvfhtnm[13]                        @351      
text kcvfhtnm[14]                        @352      
text kcvfhtnm[15]                        @353      
text kcvfhtnm[16]                        @354      
text kcvfhtnm[17]                        @355      
text kcvfhtnm[18]                        @356      
text kcvfhtnm[19]                        @357      
text kcvfhtnm[20]                        @358      
text kcvfhtnm[21]                        @359      
text kcvfhtnm[22]                        @360      
text kcvfhtnm[23]                        @361      
text kcvfhtnm[24]                        @362      
text kcvfhtnm[25]                        @363      
text kcvfhtnm[26]                        @364      
text kcvfhtnm[27]                        @365      
text kcvfhtnm[28]                        @366      
text kcvfhtnm[29]                        @367      
ub4 kcvfhrfn                             @368      0x0000000b
struct kcvfhrfs, 8 bytes                 @372     
ub4 kscnbas                           @372      0x00000000
ub2 kscnwrp                           @376      0x0000
ub4 kcvfhrft                             @380      0x00000000
struct kcvfhafs, 8 bytes                 @384     
ub4 kscnbas                           @384      0x00000000
ub2 kscnwrp                           @388      0x0000
ub4 kcvfhbbc                             @392      0x00000000
ub4 kcvfhncb                             @396      0x00000000
ub4 kcvfhmcb                             @400      0x00000000
ub4 kcvfhlcb                             @404      0x00000000
ub4 kcvfhbcs                             @408      0x00000000
ub2 kcvfhofb                             @412      0x0000
ub2 kcvfhnfb                             @414      0x0000
ub4 kcvfhprc                             @416      0x2cadd4e7
struct kcvfhprs, 8 bytes                 @420     
ub4 kscnbas                           @420      0x000a7f86
ub2 kscnwrp                           @424      0x0000
struct kcvfhprfs, 8 bytes                @428     
ub4 kscnbas                           @428      0x00000000
ub2 kscnwrp                           @432      0x0000
ub4 kcvfhtrt                             @444      0x00000000
BBED> set offset 484
OFFSET          484
BBED> p                 
pad
---
ub1 pad                                     @484      0xc2
BBED> modify /x f788
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: data02.dbf (0)
Block: 2                Offsets:  484 to  995           Dba:0x00000000
------------------------------------------------------------------------
f7880b00 00000000 2f06ae2c 01000000 0b000000 1b000000 1000e880 02000000 
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 
0d000d00 0d000100 00000000 00000000 00000000 0200c002 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 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> set offset 486
OFFSET          486
BBED> p
pad
---
ub1 pad                                     @486      0x00
BBED> modify /x 0x0a00
File: data02.dbf (0)
Block: 2                Offsets:  486 to  997           Dba:0x00000000
------------------------------------------------------------------------
0a000000 00002f06 ae2c0100 00000b00 00001b00 00001000 e8800200 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 00000d00 
0d000d00 01000000 00000000 00000000 00000200 c0020000 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 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484     
ub4 kscnbas                           @484      0x000a88f7
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x2cae062f
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
struct kcvcprba, 12 bytes             @500     
ub4 kcrbaseq                       @500      0x0000000b
ub4 kcrbabno                       @504      0x0000001b
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> sum
Check value for File 0, Block 2:
current = 0xb4a1, required = 0x3d95
BBED> sum apply
Check value for File 0, Block 2:
current = 0x3d95, required = 0x3d95
/* 如我们所期待地出现了ORA-00600[2608]内部错误 */
SQL> recover datafile 11;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [2608], [2], [0], [690423], [0],
[721306], [], []
这里的690423也就是16进制的0x000a88f7,是我们之前所修改的checkpoint scn,
而721306等于0xb019a,为当前日志文件的Low scn:
LOG FILE #1:
(name #5) /flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log
(name #6) /s01/flash_recovery_area/G10R2/onlinelog/o1_mf_1_6v34jnst_.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x0000000a hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000b0193
Low scn: 0x0000.000b0196 04/27/2011 22:52:05
Next scn: 0x0000.000b019a 04/27/2011 22:52:10
LOG FILE #2:
(name #3) /flashcard/oradata/G10R2/onlinelog/o1_mf_2_6v34jokt_.log
(name #4) /s01/flash_recovery_area/G10R2/onlinelog/o1_mf_2_6v34jotq_.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x0000000b hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000b0196
 Low scn: 0x0000.000b019a 04/27/2011 22:52:10
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
(name #1) /flashcard/oradata/G10R2/onlinelog/o1_mf_3_6v34jpmp_.log
(name #2) /s01/flash_recovery_area/G10R2/onlinelog/o1_mf_3_6v34jpyn_.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000009 hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000b0190
Low scn: 0x0000.000b0193 04/27/2011 22:52:04
Next scn: 0x0000.000b0196 04/27/2011 22:52:05

记以录之!

使用bbed解决ORA-01189错误

ORA-01189错误常见于使用Create Controlfile命令时发现OFFLINE的数据文件或者备份文件生成于上一次的RESETLOGS之前,一般来说如果是在RESETLOGS前offline的数据文件,可以在CREATE CONTROLFILE命令中省略该offline datafile,在打开数据库后使用rename file命令修复其丢失的数据文件名,之后再将数据文件online;若不是offline datafile所造成的ORA-01189错误,就必须要找出与其他数据文件版本一致的数据文件或其备份才能解决该问题。

设想存在这样一个场景:数据库处于非归档无备份的状态,在周日使用shutdown immediate命令干净地关闭了数据库,到周一发现因为磁盘故障丢失了所有的控制文件,不得不使用Create Controlfile RESETLOGS重建控制文件,而在重建过程中又漏输了某条数据文件的记录,之后又使用alter database open resetlogs重置日志文件并打开数据库,此时发现丢失了一个数据文件,尝试使用针对offline datafile的处理方法将丢失的数据文件重命名(rename),并尝试online该数据文件,毫无疑问我们会遇到ORA-01190 “control file or data file %s is from before the last RESETLOGS”错误,这时虽然我们手上有该”干净”的数据文件,却也没有办法使之online了。

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

 

诗檀软件专业数据库修复团队

 

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

这里我介绍一种使用bbed修改数据文件头的方法来解决该ORA-01189错误:

 

 

[oracle@rh2 controlfile]$ oerr ora 1189
01189, 00000, "file is from a different RESETLOGS than previous files"
// *Cause:  In a CREATE CONTROLFILE command either this file or all previous
//          files were backups from before the last RESETLOGS. This may also
//          occur if this is a file that is offline and has been offline since
//          before the last RESETLOGS.
// *Action: If the file was taken offline normal before the last RESETLOGS,
//          and is still offline, omit it from the CREATE CONTROLFILE command.
//          Rename and online the file after the database is open. Otherwise
//          find the version of the mentioned file consistent with the rest
//          of the datafiles and resubmit the command.
[oracle@rh2 controlfile]$ oerr ora 1190
01190, 00000, "control file or data file %s is from before the last RESETLOGS"
// *Cause: Attempting to use a data file when the log reset information in
//          the file does not match the control file. Either the data file
//          or the control file is a backup that was made before the most
//          recent ALTER DATABASE OPEN RESETLOGS.
// *Action: Restore file from a more recent backup.
/* 在我们的场景中wwli.dbf充当这样一个被"忽视"的数据文件 */
SQL> create tablespace we_will_lost_it datafile '/flashcard/wwli.dbf' size 20M;
Tablespace created.
SQL> create table lost_data tablespace we_will_lost_it as select * from obj$;
Table created.
SQL> alter database backup controlfile to trace ;
Database altered.
SQL> shutdown immediate;
/* 删除所有的控制文件,切勿用于生产环境! */
[oracle@rh2 ~]$ rm /flashcard/oradata/G10R2/controlfile/1.ctl 
[oracle@rh2 ~]$ sqlplus / as sysdba                           
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 27 20:42:31 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size                  2083560 bytes
Variable Size             318768408 bytes
Database Buffers          889192448 bytes
Redo Buffers               14692352 bytes
ORA-00205: error in identifying control file, check alert log for more info
/* 使用create controlfile resetlogs重建控制文件,这里我们"无意"中丢失了一个数据文件  */
SQL> CREATE CONTROLFILE REUSE DATABASE "G10R2" RESETLOGS  NOARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
5      MAXINSTANCES 8
6      MAXLOGHISTORY 292
7  LOGFILE
8    GROUP 1 (
9      '/flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log',
10      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_1_6v34jnst_.log'
11    ) SIZE 50M,
12    GROUP 2 (
13      '/flashcard/oradata/G10R2/onlinelog/o1_mf_2_6v34jokt_.log',
14      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_2_6v34jotq_.log'
15    ) SIZE 50M,
16    GROUP 3 (
17      '/flashcard/oradata/G10R2/onlinelog/o1_mf_3_6v34jpmp_.log',
18      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_3_6v34jpyn_.log'
19    ) SIZE 50M
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/flashcard/oradata/G10R2/datafile/o1_mf_system_6v34hhyw_.dbf',
23    '/flashcard/oradata/G10R2/datafile/o1_mf_undotbs1_6v34hj0l_.dbf',
24    '/flashcard/oradata/G10R2/datafile/o1_mf_sysaux_6v34hhyx_.dbf',
25    '/flashcard/oradata/G10R2/datafile/o1_mf_users_6v34hj0p_.dbf',
26    '/flashcard/oradata/G10R2/datafile/o1_mf_example_6v34jthj_.dbf',
27    '/flashcard/oradata/G10R2/datafile/o1_mf_test_6vhlm3mv_.dbf',
28    '/flashcard/oradata/G10R2/datafile/o1_mf_lostfile_6vhtgo7w_.dbf'
 29    -- we lost datafile here!!! '/flashcard/wwli.dbf'
30  CHARACTER SET UTF8
31  ;
Control file created.
SQL> recover database using backup controlfile  ;
ORA-00279: change 690423 generated at 04/27/2011 20:40:36 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R2/archivelog/2011_04_27/o1_mf_1_1_%u_.arc
ORA-00280: change 690423 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
9 OFFLINE OFFLINE FILE MISSING                                                               0
SQL> select name from v$datafile where file#=9;
NAME
--------------------------------------------------
/s01/db_1/dbs/MISSING00009
SQL> alter database rename file '/s01/db_1/dbs/MISSING00009' to '/flashcard/wwli.dbf';
Database altered.
SQL> alter database datafile 9 online;
alter database datafile 9 online
*
ERROR at line 1:
ORA-01190: control file or data file 9 is from
before the last RESETLOGS
ORA-01110: data file 9: '/flashcard/wwli.dbf'
/* 此时遭遇了我们之前描述的问题!  */
SQL> oradebug setmypid;
Statement processed.
/* 使用dump file_hdrs命令转储数据文件头,我们转储数据文件头的目的是为了获取resetlogs count,resetlogs scn */
SQL> oradebug dump file_hdrs 8;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_20029.trc
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000005 03/12/2008 00:39:08
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 04/27/2011 20:45:34
status:0x2004 root dba:0x00400179 chkpt cnt: 85 ctl cnt:84
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000a88fa 04/27/2011 20:46:00
Tablespace #1 - UNDOTBS1  rel_fn:2
Creation   at   scn: 0x0000.00092a47 03/12/2008 01:19:05
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 04/27/2011 20:45:34
status:0x4 root dba:0x00000000 chkpt cnt: 45 ctl cnt:44
begin-hot-backup file size: 0
Tablespace #10 - WE_WILL_LOST_IT  rel_fn:9
Creation   at   scn: 0x0000.000a8849 04/27/2011 20:39:27
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2cadcee3 scn: 0x0000.000a2af7 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 01/01/1988 00:00:00
status:0x0 root dba:0x00000000 chkpt cnt: 3 ctl cnt:2
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000a88f7 04/27/2011 20:40:36
thread:1 rba:(0x1.c6f.10)

以上可以看到数据文件1和2的resetlogs count(0x2cade887),scn(000a88f9)都是一样的值,而我们的问题数据文件头中的对应信息则与之不同!

接下来我们使用bbed命令去修改问题数据文件头中的resetlogs count,scn信息,关于bbed的编译和使用可以参考这里。

[oracle@rh2 ~]$ bbed filename=/flashcard/wwli.dbf blocksize=8192 password=blockedit mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Wed Apr 27 20:59:14 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 1
BLOCK#          1
BBED> map
File: /flashcard/wwli.dbf (0)
Block: 1                                     Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes                    @0       
ub4 tailchk                                @8188    
BBED> p kcvfh
struct kcvfh, 676 bytes                     @0
struct kcvfhbfh, 20 bytes                @0
ub1 type_kcbh                         @0        0x0b
ub1 frmt_kcbh                         @1        0xa2                     format 10.2
ub1 spare1_kcbh                       @2        0x00
ub1 spare2_kcbh                       @3        0x00
ub4 rdba_kcbh                         @4        0x02400001           RDBA
ub4 bas_kcbh                          @8        0x00000000
ub2 wrp_kcbh                          @12       0x0000
ub1 seq_kcbh                          @14       0x01
ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
ub2 chkval_kcbh                       @16       0xafa1                checksum
ub2 spare3_kcbh                       @18       0x0000
struct kcvfhhdr, 76 bytes                @20
ub4 kccfhswv                          @20       0x00000000
ub4 kccfhcvn                          @24       0x0a200300          compatible
ub4 kccfhdbi                          @28       0xac859d12
text kccfhdbn[0]                      @32      G                         DB_NAME
text kccfhdbn[1]                      @33      1
text kccfhdbn[2]                      @34      0
text kccfhdbn[3]                      @35      R
text kccfhdbn[4]                      @36      2
text kccfhdbn[5]                      @37
text kccfhdbn[6]                      @38
text kccfhdbn[7]                      @39
ub4 kccfhcsq                          @40       0x00000245
ub4 kccfhfsz                          @44       0x00000a00
s_blkz kccfhbsz                       @48       0x00
ub2 kccfhfno                          @52       0x0009                   FILE NUM
ub2 kccfhtyp                          @54       0x0003                   FILE TYPE
ub4 kccfhacid                         @56       0x00000000
ub4 kccfhcks                          @60       0x00000000
text kccfhtag[0]                      @64
text kccfhtag[1]                      @65
text kccfhtag[2]                      @66
text kccfhtag[3]                      @67
text kccfhtag[4]                      @68
text kccfhtag[5]                      @69
text kccfhtag[6]                      @70
text kccfhtag[7]                      @71
text kccfhtag[8]                      @72
text kccfhtag[9]                      @73
text kccfhtag[10]                     @74
text kccfhtag[11]                     @75
text kccfhtag[12]                     @76
text kccfhtag[13]                     @77
text kccfhtag[14]                     @78
text kccfhtag[15]                     @79
text kccfhtag[16]                     @80
text kccfhtag[17]                     @81
text kccfhtag[18]                     @82
text kccfhtag[19]                     @83
text kccfhtag[20]                     @84
text kccfhtag[21]                     @85
text kccfhtag[22]                     @86
text kccfhtag[23]                     @87
text kccfhtag[24]                     @88
text kccfhtag[25]                     @89
text kccfhtag[26]                     @90
text kccfhtag[27]                     @91
text kccfhtag[28]                     @92
text kccfhtag[29]                     @93
text kccfhtag[30]                     @94
text kccfhtag[31]                     @95
ub4 kcvfhrdb                             @96       0x00000000
struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
ub4 kscnbas                           @100      0x000a8849
ub2 kscnwrp                           @104      0x0000
ub4 kcvfhcrt                             @108      0x2cade6ff           
ub4 kcvfhrlc                             @112      0x2cadd4e7               resetlogs count
struct kcvfhrls, 8 bytes                 @116
ub4 kscnbas                           @116      0x000a7f86              resetlogs scn
ub2 kscnwrp                           @120      0x0000
ub4 kcvfhbti                             @124      0x00000000
struct kcvfhbsc, 8 bytes                 @128
ub4 kscnbas                           @128      0x00000000
ub2 kscnwrp                           @132      0x0000
ub2 kcvfhbth                             @136      0x0000
ub2 kcvfhsta                             @138      0x0000 (NONE)
struct kcvfhckp, 36 bytes                @484
struct kcvcpscn, 8 bytes              @484                                  Checkpoint scn
ub4 kscnbas                        @484      0x000a88f7
ub2 kscnwrp                        @488      0x0000
ub4 kcvcptim                          @492      0x2cade744
ub2 kcvcpthr                          @496      0x0001
union u, 12 bytes                     @500
struct kcvcprba, 12 bytes          @500
ub4 kcrbaseq                    @500      0x00000001
ub4 kcrbabno                    @504      0x00000c6f
ub2 kcrbabof                    @508      0x0010
ub1 kcvcpetb[0]                       @512      0x02
ub1 kcvcpetb[1]                       @513      0x00
ub1 kcvcpetb[2]                       @514      0x00
ub1 kcvcpetb[3]                       @515      0x00
ub1 kcvcpetb[4]                       @516      0x00
ub1 kcvcpetb[5]                       @517      0x00
ub1 kcvcpetb[6]                       @518      0x00
ub1 kcvcpetb[7]                       @519      0x00
ub4 kcvfhcpc                             @140      0x00000003
ub4 kcvfhrts                             @144      0x00000000
ub4 kcvfhccc                             @148      0x00000002
struct kcvfhbcp, 36 bytes                @152
struct kcvcpscn, 8 bytes              @152
ub4 kscnbas                        @152      0x00000000
ub2 kscnwrp                        @156      0x0000
ub4 kcvcptim                          @160      0x00000000
ub2 kcvcpthr                          @164      0x0000
union u, 12 bytes                     @168
struct kcvcprba, 12 bytes          @168
ub4 kcrbaseq                    @168      0x00000000
ub4 kcrbabno                    @172      0x00000000
ub2 kcrbabof                    @176      0x0000
ub1 kcvcpetb[0]                       @180      0x00
ub1 kcvcpetb[1]                       @181      0x00
ub1 kcvcpetb[2]                       @182      0x00
ub1 kcvcpetb[3]                       @183      0x00
ub1 kcvcpetb[4]                       @184      0x00
ub1 kcvcpetb[5]                       @185      0x00
ub1 kcvcpetb[6]                       @186      0x00
ub1 kcvcpetb[7]                       @187      0x00
ub4 kcvfhbhz                             @312      0x00000000
struct kcvfhxcd, 16 bytes                @316
ub4 space_kcvmxcd[0]                  @316      0x00000000
ub4 space_kcvmxcd[1]                  @320      0x00000000
ub4 space_kcvmxcd[2]                  @324      0x00000000
ub4 space_kcvmxcd[3]                  @328      0x00000000
word kcvfhtsn                            @332      10                         Tablespace#
ub2 kcvfhtln                             @336      0x000f
text kcvfhtnm[0]                         @338     W                          TABLESPACE_NAME
text kcvfhtnm[1]                         @339     E
text kcvfhtnm[2]                         @340     _
text kcvfhtnm[3]                         @341     W
text kcvfhtnm[4]                         @342     I
text kcvfhtnm[5]                         @343     L
text kcvfhtnm[6]                         @344     L
text kcvfhtnm[7]                         @345     _
text kcvfhtnm[8]                         @346     L
text kcvfhtnm[9]                         @347     O
text kcvfhtnm[10]                        @348     S
text kcvfhtnm[11]                        @349     T
text kcvfhtnm[12]                        @350     _
text kcvfhtnm[13]                        @351     I
text kcvfhtnm[14]                        @352     T
text kcvfhtnm[15]                        @353
text kcvfhtnm[16]                        @354
text kcvfhtnm[17]                        @355
text kcvfhtnm[18]                        @356
text kcvfhtnm[19]                        @357
text kcvfhtnm[20]                        @358
text kcvfhtnm[21]                        @359
text kcvfhtnm[22]                        @360
text kcvfhtnm[23]                        @361
text kcvfhtnm[24]                        @362
text kcvfhtnm[25]                        @363
text kcvfhtnm[26]                        @364
text kcvfhtnm[27]                        @365
text kcvfhtnm[28]                        @366
text kcvfhtnm[29]                        @367
ub4 kcvfhrfn                             @368      0x00000009
struct kcvfhrfs, 8 bytes                 @372
ub4 kscnbas                           @372      0x00000000
ub2 kscnwrp                           @376      0x0000
ub4 kcvfhrft                             @380      0x00000000
struct kcvfhafs, 8 bytes                 @384
ub4 kscnbas                           @384      0x00000000
ub2 kscnwrp                           @388      0x0000
ub4 kcvfhbbc                             @392      0x00000000
ub4 kcvfhncb                             @396      0x00000000
ub4 kcvfhmcb                             @400      0x00000000
ub4 kcvfhlcb                             @404      0x00000000
ub4 kcvfhbcs                             @408      0x00000000
ub2 kcvfhofb                             @412      0x0000
ub2 kcvfhnfb                             @414      0x0000
ub4 kcvfhprc                             @416      0x2cadcee3     prev reset logs count
struct kcvfhprs, 8 bytes                 @420                        prev reset scn
ub4 kscnbas                           @420      0x000a2af7
ub2 kscnwrp                           @424      0x0000
struct kcvfhprfs, 8 bytes                @428
ub4 kscnbas                           @428      0x00000000
ub2 kscnwrp                           @432      0x0000
ub4 kcvfhtrt                             @444      0x00000000
/* 以上kcvfh结构是数据文件头的主要信息,其中ub4 kcvfhrlc(offset 112)记录了resetlogs count,
而 struct kcvfhrls 8 bytes(offset 116)记录了resetlogs scn */
/* 接下来将问题数据文件头上的kcvfhrlc和kcvfhrls信息修改成和1号文件中的一样,
以欺骗Oracle让其以为该数据文件参与了上一次的RESETLOGS                 */
BBED> set offset 112
OFFSET          112
/* 在使用bbed modify的时候需要注意所在平台的endian,Linux上使用Little Endian */
BBED> modify /x 87e8
File: /flashcard/wwli.dbf (0)
Block: 1                Offsets:  112 to  623           Dba:0x00000000
------------------------------------------------------------------------
87e8ad2c 867f0a00 00000000 00000000 00000000 00000000 00000000 03000000
00000000 02000000 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 0a000000
0f005745 5f57494c 4c5f4c4f 53545f49 54000000 00000000 00000000 00000000
09000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 e3cead2c f72a0a00 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 f7880a00 00000000 44e7ad2c
01000160 01000000 6f0c0000 10000000 02000000 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 
<32 bytes per line>
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2cade887
BBED> set offset 116
OFFSET          116
BBED> modify /x 0xf988
File: /flashcard/wwli.dbf (0)
Block: 1                Offsets:  116 to  627           Dba:0x00000000
------------------------------------------------------------------------
f9880a00 00000000 00000000 00000000 00000000 00000000 03000000 00000000
02000000 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 0a000000 0f005745
5f57494c 4c5f4c4f 53545f49 54000000 00000000 00000000 00000000 09000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 e3cead2c f72a0a00 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 f7880a00 00000000 44e7ad2c 01000160
01000000 6f0c0000 10000000 02000000 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 
<32 bytes per line>
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
ub4 kscnbas                              @116      0x000a88f9
ub2 kscnwrp                              @120      0x0000
/* 还需要将kcvfhckp结构中的kcvcpscn也修改成与1号文件一样的resetlogs scn ,
否则在recover数据文件的时候可能出现
ORA-00600: internal error code, arguments: [2608], [1], [0], [690423], [0], [690425], [], []错误  */
BBED> set offset 484
OFFSET          484
BBED> modify /x 0xf9
File: /flashcard/wwli.dbf (0)
Block: 1                Offsets:  484 to  995           Dba:0x00000000
------------------------------------------------------------------------
f9880a00 00000000 44e7ad2c 01000160 01000000 6f0c0000 10000000 02000000
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
0d000d00 0d000100 00000000 00000000 00000000 02004002 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 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
struct kcvcpscn, 8 bytes                 @484
ub4 kscnbas                           @484      0x000a88f9
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x2cade744
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500
struct kcvcprba, 12 bytes             @500
ub4 kcrbaseq                       @500      0x00000001
ub4 kcrbabno                       @504      0x00000c6f
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> sum
Check value for File 0, Block 1:
current = 0xb897, required = 0xb899
/* 使用sum apply命令修改该数据块的checksum值  */
BBED> sum apply
Check value for File 0, Block 1:
current = 0xb899, required = 0xb899
SQL>  alter database datafile '/flashcard/wwli.dbf' online;
alter database datafile '/flashcard/wwli.dbf' online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/flashcard/wwli.dbf'
/* 完美地recover datafile 9,因为该数据文件中的数据本身就是"干净"的,
仅仅是datafile header中的resetlogs信息与其他datafile不一致而已  */
SQL> recover datafile 9;
Media recovery complete.
SQL> select * from v$recover_file where file#=9;
no rows selected
SQL> select count(*) from lost_data ;
COUNT(*)
----------
51791
/* That's great!

虽然我们也可以利用adjust_scn和设置隐藏参数”_allow_resetlogs_corruption”来尝试解决ORA-01189错误,但使用bbed可以避免用到”_allow_resetlogs_corruption”这个危险参数,且在之后更完美地recover恢复数据文件。

需要注意的是使用bbed修复问题数据文件并不能保证必然都成功,在使用bbed之前必须执行必要的备份,并在专业人士的指导下操作!

How to make BBED(Oracle Block Brower and EDitor Tool) on Unix/Linux/Windows

“BBED(Oracle Block Brower and EDitor Tool),用来直接查看和修改数据文件数据的一个工具,是Oracle一款内部工具,可以直接修改Oracle数据文件块的内容,简单来说就是一个针对 Oracle的二进制编辑工具。该工具不受Oracle支持,所以默认是没有生成可执行文件的,在使用前需要重新编译。”

 

在10g中编译该工具显得较简单:

 

[maclean@rh2 ~]$ cd $ORACLE_HOME/rdbms/lib
[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
make: `/s01/10gdb/rdbms/lib/bbed' is up to date.
[maclean@rh2 lib]$ rm bbed
[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
Linking BBED utility (bbed)
rm -f /s01/10gdb/rdbms/lib/bbed
gcc -o /s01/10gdb/rdbms/lib/bbed -L/s01/10gdb/rdbms/lib/ -L/s01/10gdb/lib/ -L/s01/10gdb/lib/stubs/  /s01/10gdb/lib/s0main.o /s01/10gdb/rdbms/lib/ssbbded.o /s01/10gdb/rdbms/lib/sbbdpt.o `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 /s01/10gdb/rdbms/lib/defopt.o -ldbtools10 -lclntsh  `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10  -lvsn10 -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10  -lvsn10 -lcommon10 -lgeneric10   -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lclient10 -lnnetd10  -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10   `cat /s01/10gdb/lib/sysliblist` -Wl,-rpath,/s01/10gdb/lib -lm    `cat /s01/10gdb/lib/sysliblist` -ldl -lm   -L/s01/10gdb/lib
[maclean@rh2 lib]$ cp bbed $ORACLE_HOME/bin
[maclean@rh2 lib]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 2 14:18:27 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
BBED>
/* 你可能要问密码是什么? 呵呵 .. 🙂 */

11.2.0.1中编译bbed可执行文件所需要的ssbbded.o和sbbdpt.o对象文件被移除了,所幸我们可以使用10g下的这2个对象文件在11.2.0.1中编译。

[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
Linking BBED utility (bbed)
rm -f /s01/11gdb/rdbms/lib/bbed
gcc -o /s01/11gdb/rdbms/lib/bbed -m64 -L/s01/11gdb/rdbms/lib/ -L/s01/11gdb/lib/ -L/s01/11gdb/lib/stubs/  /s01/11gdb/lib/s0main.o /s01/11gdb/rdbms/lib/ssbbded.o /s01/11gdb/rdbms/lib/sbbdpt.o `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -ldbtools11 -lclntsh  `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /s01/11gdb/lib/sysliblist` -Wl,-rpath,/s01/11gdb/lib -lm    `cat /s01/11gdb/lib/sysliblist` -ldl -lm   -L/s01/11gdb/lib
gcc: /s01/11gdb/rdbms/lib/ssbbded.o: No such file or directory
gcc: /s01/11gdb/rdbms/lib/sbbdpt.o: No such file or directory
[maclean@rh2 ~]$ cp /s01/10gdb/rdbms/lib/ssbbded.o /s01/11gdb/rdbms/lib
[maclean@rh2 ~]$ cp /s01/10gdb/rdbms/lib/sbbdpt.o  /s01/11gdb/rdbms/lib
[maclean@rh2 ~]$ cp /s01/10gdb/rdbms/mesg/bbedus.ms* /s01/11gdb/rdbms/mesg/
/* bbed 需要用到bbedus.msg和bbedus.msb 2个信息文件 */
[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
Linking BBED utility (bbed)
rm -f /s01/11gdb/rdbms/lib/bbed
gcc -o /s01/11gdb/rdbms/lib/bbed -m64 -L/s01/11gdb/rdbms/lib/ -L/s01/11gdb/lib/ -L/s01/11gdb/lib/stubs/  /s01/11gdb/lib/s0main.o /s01/11gdb/rdbms/lib/ssbbded.o /s01/11gdb/rdbms/lib/sbbdpt.o `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -ldbtools11 -lclntsh  `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /s01/11gdb/lib/sysliblist` -Wl,-rpath,/s01/11gdb/lib -lm    `cat /s01/11gdb/lib/sysliblist` -ldl -lm   -L/s01/11gdb/lib
[maclean@rh2 lib]$ file bbed
bbed: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
[maclean@rh2 lib]$ size bbed
text    data     bss     dec     hex filename
154473   43448      32  197953   30541 bbed
[maclean@rh2 lib]$ ldd bbed
libclntsh.so.11.1 => /s01/11gdb/lib/libclntsh.so.11.1 (0x00002b042b883000)
libnnz11.so => /s01/11gdb/lib/libnnz11.so (0x00002b042dead000)
libdl.so.2 => /lib64/libdl.so.2 (0x00000039f2400000)
libm.so.6 => /lib64/libm.so.6 (0x00000039f2000000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039f2800000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00000039f5c00000)
libc.so.6 => /lib64/libc.so.6 (0x00000039f1c00000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002b042e293000)
/lib64/ld-linux-x86-64.so.2 (0x00000039f1800000)
[maclean@rh2 lib]$ cp bbed $ORACLE_HOME/bin
[maclean@rh2 lib]$ which bbed
/s01/11gdb/bin/bbed
[maclean@rh2 lib]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 2 15:18:37 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
BBED>

 

如图:

 

 

 

 

沪ICP备14014813号

沪公网安备 31010802001379号