分类目录归档：数据库

v$datafile.enabled相关值说明

发表于 2012 年 8 月 6 日 由 惜分飞

自认为对v$datafile视图算比较了解,但是今天看到一网友的v$datafile.enabled=DISABLED,我一时没有想出来是为什么,这里通过试验对v$datafile.enabled各种情况进行了试验并简单说明scn的变化情况
模拟环境(READ WRITE)

SQL> alter tablespace users add datafile '/u01/oracle/oradata/XFF/users02.dbf' size 10m;

Tablespace altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             456727 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

QL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              456727
         2 ONLINE              456727
         3 ONLINE              456727
         4 ONLINE              456727
         5 ONLINE              458322

加入数据文件scn不一样是因为:加入数据文件的时候,使用是当前scn,而数据库未做checkpoint,所以出现数据文件scn不一致现象

datafile offline(READ WRITE)

SQL> alter database datafile 5 offline;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 OFFLINE             458322

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ----------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE RECOVER /u01/oracle/oradata/XFF/users02.dbf

这里可以看出来数据文件offline之后,v$datafile.enabled依然是READ WRITE,但是该数据文件的scn不再变化

tablespace offline(DISABLED)

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.

SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458430 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 ONLINE              458430

SQL> alter system checkpoint;

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             458443 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458443
         2 ONLINE              458443
         3 ONLINE              458443
         4 ONLINE              458443
         5 ONLINE              458443

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458497 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458497
         2 ONLINE              458497
         3 ONLINE              458497
         4 OFFLINE                  0
         5 OFFLINE                  0

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf
 
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 OFFLINE                  0
         5 OFFLINE                  0

SQL> alter tablespace users online;

Tablespace altered.

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 ONLINE              458526
         5 ONLINE              458526

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> alter system checkpoint;

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458551
         5 ONLINE              458551

以上部分证明：
1.online datafile也不触发database checkpoint
2.tablespace offline后v$datafile.enabled为DISABLED
3.控制文件对应表空间scn不再变化,datafile header scn变为0
4.tablespace online不需要recover

tablespace read only(READ ONLY)

SQL> alter tablespace users read only;

Tablespace altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458567
         5 ONLINE              458567

SQL> alter system checkpoint;

System altered.

SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458567
         5 ONLINE              458567

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458635
         5 ONLINE              458635

SQL> alter system checkpoint;

System altered.

SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458649 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458649
         2 ONLINE              458649
         3 ONLINE              458649
         4 ONLINE              458649
         5 ONLINE              458649

以上部分证明：
1.tablespace read only 对应的v$datafile.enabled为READ ONLY
2.tablespace read only与tablespace read write之间的转换也不会触发database checkpoint

补充说明
官方文档给出来的v$datafile.enabled有以下几种
DISABLED – No SQL access allowed
READ ONLY – No SQL updates allowed
READ WRITE – Full access allowed
UNKNOWN – should not occur unless the control file is corrupted
但是对于UNKNOWN我通过模拟重建控制文件,缺少数据文件(为READ WRITE),通过在线删除数据文件(为READ WRITE),都不能出现UNKNOWN状态,如果知道的朋友请告知我.

发表在 Oracle | 评论关闭

通过bbed解决ORA-00600[4000]案例

发表于 2012 年 8 月 5 日 由 惜分飞

数据库启动出现ORA-00600[4000]错误

Fri Nov  4 06:50:38 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7046.trc:
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Fri Nov  4 06:50:40 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7046.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Fri Nov  4 06:50:40 2011
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 7046
ORA-1092 signalled during: ALTER DATABASE OPEN...

查看trace文件

*** 2011-11-04 06:50:38.942
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1


Block header dump:  0x0040007a
 Object id on Block? Y
 seg/obj: 0x12  csc: 0x00.1020770d  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.029.0000029a  0x00802381.01f9.03  --U-    1  fsc 0x0000.1020770e

查询trace相关数据对应值

SQL> select DBMS_UTILITY.data_block_address_file (TO_NUMBER ('0040007a','xxxxxxxx')) file_no,
  2  DBMS_UTILITY.data_block_address_block (TO_NUMBER ('0040007a','xxxxxxxx')) block_no
  3  from dual;

   FILE_NO   BLOCK_NO
---------- ----------
         1        122


SQL> select to_number('1020770e','xxxxxxxxxxx') itl_commit from dual;

ITL_COMMIT
----------
 270563086

SQL> select to_number('1020770d','xxxxxxxxxxxx') csc from dual;

       CSC
----------
 270563085

通过这里的分析,我们可以得出file 1 block 122的内容未提交,可能是导致错误ORA-00600[4000],尝试这手工提交该事务

BBED> set file 1 block 122
        FILE#           1
        BLOCK#          122

BBED> map
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 122                                   Dba:0x0040007a
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 48 bytes                     @20      

 struct kdbh, 14 bytes                      @68      

 struct kdbt[1], 4 bytes                    @82      

 sb2 kdbr[108]                              @86      

 ub1 freespace[873]                         @302     

 ub1 rowdata[7013]                          @1175    

 ub4 tailchk                                @8188    


BBED> p ktbbh
struct ktbbh, 48 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00000012
      ub4 ktbbhod1                          @24       0x00000012
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x1020770d
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       1
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0005
         ub2 kxidslt                        @46       0x0029
         ub4 kxidsqn                        @48       0x0000029a
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00802381
         ub2 kubaseq                        @56       0x01f9
         ub1 kubarec                        @58       0x03
      ub2 ktbitflg                          @60       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x1020770e

BBED> set count 16
        COUNT           16

BBED> m /x 0180 offset 60 
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 122              Offsets:   60 to   75           Dba:0x0040007a
------------------------------------------------------------------------
 01800000 0e772010 00016c00 ffffea00 

 <32 bytes per line>

BBED> p ktbbh
struct ktbbh, 48 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00000012
      ub4 ktbbhod1                          @24       0x00000012
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x1020770d
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       1
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0005
         ub2 kxidslt                        @46       0x0029
         ub4 kxidsqn                        @48       0x0000029a
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00802381
         ub2 kubaseq                        @56       0x01f9
         ub1 kubarec                        @58       0x03
      ub2 ktbitflg                          @60       0x8001 (KTBFCOM)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x1020770e

BBED> sum apply
Check value for File 1, Block 122:
current = 0x6902, required = 0x6902

尝试重启库

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size              92276136 bytes
Database Buffers          218103808 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

查看alert日志

Fri Nov  4 07:42:46 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7702.trc:
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], []
Fri Nov  4 07:42:46 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7702.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], []
Fri Nov  4 07:42:46 2011
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 7702
ORA-1092 signalled during: ALTER DATABASE OPEN...

分析trace文件

*** 2011-11-04 07:42:46.273
Recovery of Online Redo Log: Thread 1 Group 1 Seq 40 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
[ktbdchk] -- readers_dsz -- bad dscn
scn: 0x0000.1020770escn: 0x0000.0021fa09
*** 2011-11-04 07:42:46.530
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1

Block header dump:  0x0040007a
 Object id on Block? Y
 seg/obj: 0x12  csc: 0x00.1020770d  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.029.0000029a  0x00802381.01f9.03  C---    0  scn 0x0000.1020770e

根据这个错误提示ktbdchk–>bad dscn,猜测ktbdchk是header scn中的ktbdchk,查找1020770e发现是itl comomit scn,通过bbed查看

BBED>  p kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes                    @484     
   ub4 kscnbas                              @484      0x0021fa09
   ub2 kscnwrp                              @488      0x0000

通过这里可以知道ORA-00600[ktbdchk1: bad dscn]是因为itl comomit scn远大于datafile header scn,从而oracle认为datafile header scn是错误的,从而提示ktbdchk1: bad dscn.尝试bbed修改itl comomit scn

BBED> p ktbbh
struct ktbbh, 48 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00000012
      ub4 ktbbhod1                          @24       0x00000012
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x1020770d
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       1
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0005
         ub2 kxidslt                        @46       0x0029
         ub4 kxidsqn                        @48       0x0000029a
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00802381
         ub2 kubaseq                        @56       0x01f9
         ub1 kubarec                        @58       0x03
      ub2 ktbitflg                          @60       0x8001 (KTBFCOM)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x1020770e

BBED> m /x 09fa2100 offset 64
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 122              Offsets:   64 to   79           Dba:0x0040007a
------------------------------------------------------------------------
 09fa2100 00016c00 ffffea00 53046903 

 <32 bytes per line>

BBED> sum apply
Check value for File 1, Block 122:
current = 0xf404, required = 0xf404

启动数据库

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size              92276136 bytes
Database Buffers          218103808 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
发表在 非常规恢复 | 标签为 | 评论关闭

重现ORA-600 4000异常

发表于 2012 年 8 月 5 日 由 惜分飞

对于数据库恢复感兴趣的人,一定对于ORA-600[4000]这个著名的错误记忆犹新,这里通过试验重现ORA-600[4000]
查询数据库obj$.con$记录

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 4 06:32:36 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size              92276136 bytes
Database Buffers          218103808 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> select OBJ#,dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  2  dbms_rowid.rowid_block_number(rowid) block_num from obj$ 
  3  where name='CON$';

      OBJ#    REL_FNO  BLOCK_NUM
---------- ---------- ----------
        28          1        122

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

bbed修改数据块
根据催华的研究,当OBJ$中部分数据块中的csc和itl的scn都大于header scn的时候,数据库启动就会报ORA-600[4000]

[oracle@xifenfei ~]$ bbed listfile=list mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Fri Nov 4 06:47:09 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/XFF/system01.dbf                                 0
     2  /u01/oracle/oradata/XFF/undotbs01.dbf                                0
     3  /u01/oracle/oradata/XFF/sysaux01.dbf                                 0
     4  /u01/oracle/oradata/XFF/users01.dbf                                  0
     5  /u01/oracle/oradata/XFF/datfttuser.dbf                               0

BBED> set block 1     
        BLOCK#          1

BBED> set file 1 block 1
        FILE#           1
        BLOCK#          1

BBED>  p kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes                    @484     
   ub4 kscnbas                              @484      0x00210f97
   ub2 kscnwrp                              @488      0x0000

BBED> set block 122
        BLOCK#          122

BBED> p ktbbh
struct ktbbh, 48 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00000012
      ub4 ktbbhod1                          @24       0x00000012
   struct ktbbhcsc, 8 bytes                 @28         <==csc(SCN of the last block cleanout)
      ub4 kscnbas                           @28       0x0020770d
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       1
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0005  <==回滚段序号
         ub2 kxidslt                        @46       0x0029
         ub4 kxidsqn                        @48       0x0000029a
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00802381
         ub2 kubaseq                        @56       0x01f9
         ub1 kubarec                        @58       0x03
      ub2 ktbitflg                          @60       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x0020770e <==itl commit scn

BBED> set count 16
        COUNT           16

BBED> m /x 0d772010 offset 28
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 122              Offsets:   28 to  60           Dba:0x0040007a
------------------------------------------------------------------------
 0d772010 00000000 01000200 00000000 

 <32 bytes per line>

BBED> m /x 0e772010 offset 64
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 122              Offsets:   64 to  96           Dba:0x0040007a
------------------------------------------------------------------------
 0e772010 00016c00 ffffea00 53046903 

 <32 bytes per line>

BBED> p ktbbh
struct ktbbh, 48 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00000012
      ub4 ktbbhod1                          @24       0x00000012
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x1020770d
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       1
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0005
         ub2 kxidslt                        @46       0x0029
         ub4 kxidsqn                        @48       0x0000029a
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00802381
         ub2 kubaseq                        @56       0x01f9
         ub1 kubarec                        @58       0x03
      ub2 ktbitflg                          @60       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x1020770e

BBED> sum apply
Check value for File 1, Block 122:
current = 0xc902, required = 0xc902

启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size              92276136 bytes
Database Buffers          218103808 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

查看日志

Fri Nov  4 06:50:38 2011
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Fri Nov  4 06:50:38 2011
ALTER DATABASE OPEN
Fri Nov  4 06:50:38 2011
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=7048
Fri Nov  4 06:50:38 2011
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=7050
ARC1: STARTING ARCH PROCESSES
Fri Nov  4 06:50:38 2011
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri Nov  4 06:50:38 2011
Thread 1 opened at log sequence 38
  Current log# 3 seq# 38 mem# 0: /u01/oracle/oradata/XFF/redo03.log
Successful open of redo thread 1
Fri Nov  4 06:50:38 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Nov  4 06:50:38 2011
SMON: enabling cache recovery
Fri Nov  4 06:50:38 2011
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=7052
Fri Nov  4 06:50:38 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7046.trc:
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Fri Nov  4 06:50:40 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7046.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Fri Nov  4 06:50:40 2011
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 7046
ORA-1092 signalled during: ALTER DATABASE OPEN...

在ORACL 11.2的版本中,同样的方法无法重新该错误,数据库依然能够正常打开,所以如果要测试的朋友请选择11G以下版本进行.

发表在 ORA-xxxxx | 标签为 , , | 评论关闭