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

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介:
节前帮网友打开了一套断电后无法正常启动的10.2.0.1数据库,该库使用非归档模式且没有任何备份。 主机断电后,该网友尝试重启数据库实例,但是遇到了ORA-00600:[kccpb_sanity_check_2]内部错误:  
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  1220432 bytes
Variable Size             486539440 bytes
Database Buffers         1644167168 bytes
Redo Buffers               15556608 bytes
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [8198],
[8175], [0x0], [], [], [], []
  该kccpb_sanity_check_2内部错误常由不正确的control file中seq#记录引起,MOS note <ORA-00600: [kccpb_sanity_check_2] During Instance Startup>简单描述了该问题:  
ORA-00600: [kccpb_sanity_check_2] During Instance Startup

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
Symptoms
The database is getting the following errors on Startup:

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [3621501], [3621462], [0x000000000]

Changes
In this case, the customer moved the box from one data center to another.
Cause
ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is
higher than the seq# of the control file header block. This is indication of
the lost write of the header block during commit of the previous cf
transaction.

Solution

1) restore a backup of a controlfile and recover

OR

2) recreate the controlfile

OR

3) restore the database from last good backup and recover

NOTE:  If you do not have any special backup of control file to restore and you are
using Multiple Control File copies in your pfile/init.ora/spfile you can attempt to mount the
database using each control file one by one.
If you are able to mount the database with any of these control file copies
you can then issue 'alter database backup controlfile to trace' to recreate controlfile.
针对该由control file corruption引起的ORA-00600: [kccpb_sanity_check_2] ,可以尝试一下三种解决方法:
  1. restore controlfile from backup
  2. 若有多路复用controlfile,则尝试使用每个单独的control file来mount实例,若能成功则使用该可用controlfile复制到其余位置
  3. 使用controlfile backup trace来重建控制文件,若没有trace那么也可以手动写create controlfile语句来重建
  接手这个问题后尝试第二种方法失败,只好到udump/bdump目录下搜索可用的controlfile backup trace,所幸找到了,重建控制文件并成功:
Create controlfile reuse set database "kmcdb"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/oracle/oradata/kmcdb/system01.dbf',
'/u01/oracle/oradata/kmcdb/undotbs01.dbf',
'/u01/oracle/oradata/kmcdb/sysaux01.dbf',
'/u01/oracle/oradata/kmcdb/users01.dbf',
'/u01/oracle/oradata/kmcdb/example01.dbf'
LOGFILE GROUP 1 ('/u01/oracle/oradata/kmcdb/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/oracle/oradata/kmcdb/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/oracle/oradata/kmcdb/redo03.log') SIZE 51200K RESETLOGS
Wed Sep 28 16:57:47 2011
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Setting recovery target incarnation to 1
Wed Sep 28 16:57:48 2011
Successful mount of redo thread 1, with mount id 523482251
Wed Sep 28 16:57:48 2011
Completed: Create controlfile reuse set database "kmcdb"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/oracle/oradata/kmcdb/system01.dbf',
'/u01/oracle/oradata/kmcdb/undotbs01.dbf',
'/u01/oracle/oradata/kmcdb/sysaux01.dbf',
'/u01/oracle/oradata/kmcdb/users01.dbf',
'/u01/oracle/oradata/kmcdb/example01.dbf'
LOGFILE GROUP 1 ('/u01/oracle/oradata/kmcdb/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/oracle/oradata/kmcdb/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/oracle/oradata/kmcdb/redo03.log') SIZE 51200K RESETLOGS
Wed Sep 28 16:57:57 2011
alter database mount
  成功mount实例后,尝试recover database 并 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的,那么请善待数据。 善待数据的第一条准则是 数据备份重于一切,如果没有备份 那么请不要奢望数据是持久的!



本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1278106

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
|
15天前
|
存储 Oracle 关系型数据库
数据库数据恢复—ORACLE常见故障的数据恢复方案
Oracle数据库常见故障表现: 1、ORACLE数据库无法启动或无法正常工作。 2、ORACLE ASM存储破坏。 3、ORACLE数据文件丢失。 4、ORACLE数据文件部分损坏。 5、ORACLE DUMP文件损坏。
58 11
|
28天前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
一台Oracle数据库打开报错,报错信息: “system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。管理员联系我们数据恢复中心寻求帮助,并提供了Oracle_Home目录的所有文件。用户方要求恢复zxfg用户下的数据。 由于数据库没有备份,无法通过备份去恢复数据库。
|
1月前
|
存储 Oracle 关系型数据库
oracle数据恢复—Oracle数据库文件大小变为0kb的数据恢复案例
存储掉盘超过上限,lun无法识别。管理员重组存储的位图信息并导出lun,发现linux操作系统上部署的oracle数据库中有上百个数据文件的大小变为0kb。数据库的大小缩水了80%以上。 取出&并分析oracle数据库的控制文件。重组存储位图信息,重新导出控制文件中记录的数据文件,发现这些文件的大小依然为0kb。
|
20天前
|
存储 Oracle 关系型数据库
服务器数据恢复—华为S5300存储Oracle数据库恢复案例
服务器存储数据恢复环境: 华为S5300存储中有12块FC硬盘,其中11块硬盘作为数据盘组建了一组RAID5阵列,剩下的1块硬盘作为热备盘使用。基于RAID的LUN分配给linux操作系统使用,存放的数据主要是Oracle数据库。 服务器存储故障: RAID5阵列中1块硬盘出现故障离线,热备盘自动激活开始同步数据,在同步数据的过程中又一块硬盘离线,RAID5阵列瘫痪,上层LUN无法使用。
|
2月前
|
存储 Oracle 关系型数据库
数据库数据恢复—Oracle ASM磁盘组故障数据恢复案例
Oracle数据库数据恢复环境&故障: Oracle ASM磁盘组由4块磁盘组成。Oracle ASM磁盘组掉线 ,ASM实例不能mount。 Oracle数据库故障分析&恢复方案: 数据库数据恢复工程师对组成ASM磁盘组的磁盘进行分析。对ASM元数据进行分析发现ASM存储元数据损坏,导致磁盘组无法挂载。
|
3月前
|
Oracle 关系型数据库 数据库
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
打开oracle数据库报错“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。 数据库没有备份,无法通过备份去恢复数据库。用户方联系北亚企安数据恢复中心并提供Oracle_Home目录中的所有文件,急需恢复zxfg用户下的数据。 出现“system01.dbf需要更多的恢复来保持一致性”这个报错的原因可能是控制文件损坏、数据文件损坏,数据文件与控制文件的SCN不一致等。数据库恢复工程师对数据库文件进一步检测、分析后,发现sysaux01.dbf文件损坏,有坏块。 修复并启动数据库后仍然有许多查询报错,export和data pump工具使用报错。从数据库层面无法修复数据库。
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
|
3月前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—异常断电导致Oracle数据库数据丢失的数据恢复案例
Oracle数据库故障: 机房异常断电后,Oracle数据库启库报错:“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。数据库没有备份,归档日志不连续。用户方提供了Oracle数据库的在线文件,需要恢复zxfg用户的数据。 Oracle数据库恢复方案: 检测数据库故障;尝试挂起并修复数据库;解析数据文件。
|
2月前
|
Oracle 关系型数据库 数据库
oracle数据恢复—Oracle数据库文件损坏导致数据库打不开的数据恢复案例
打开oracle数据库时报错,报错信息:“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。急需恢复zxfg用户下的数据。 出现上述报错的原因有:控制文件损坏、数据文件损坏、数据文件与控制文件的SCN不一致等。数据恢复工程师对数据库文件做进一步检测分析后发现sysaux01.dbf文件有坏块。修复sysaux01.dbf文件,启动数据库依然有许多查询报错。export和data pump工具无法使用,查询告警日志并分析报错,确认发生上述错误的原因就是sysaux01.dbf文件损坏。由于该文件损坏,从数据库层面无法修复数据库。由于system和用户表空间的数据文件是正常的,
|
3月前
|
Oracle 安全 关系型数据库
Oracle数据恢复—Oracle数据库误删除的数据恢复方法探讨
删除Oracle数据库数据一般有以下2种方式:delete、drop或truncate。下面针对这2种删除oracle数据库数据的方式探讨一下oracle数据库数据恢复方法(不考虑全库备份和利用归档日志)。
|
5月前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库误删除的数据恢复方法
相信有很多oracle数据库用户都遇到过在操作Oracle数据库时误删除某些重要数据的情况,这个时候如果数据库没有备份且数据十分重要的,怎么才能恢复误删除的数据呢?北亚企安数据恢复工程师下面简单介绍几个误删除Oracle数据库数据的恢复方法。