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的,那么请善待数据。 善待数据的第一条准则是 数据备份重于一切,如果没有备份 那么请不要奢望数据是持久的!

Script:收集Oracle备份恢复信息

我们在诊断Oracle backup restore问题时总是希望能获得足够的诊断信息,一般来说RDA会是一个最好的诊断信息收集工具,但是有时候客户会很反感使用RDA(不信任感),这里我们提供一段专门用来收集oracle备份恢复信息的脚本。

运行以下脚本需要设置合理的”ORACLE_HOME、ORACLE_SID”环境变量,并设置NLS_DATE_FORMAT环境变量,如

NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export NLS_DATE_FORMAT

以”rman target /”登陆并运行:

spool log to rman_report.log
set echo on
show all;
report schema;
list incarnation;
list backup summary;
list backup;
list copy;
report need backup;
report obsolete;
restore database preview;
spool log off

以下脚本在sqlplus中以sysdba身份执行,执行要求数据库至少处于mounted已加载状态下;注意该原始脚本是只读readonly的,它仅仅是读取数据字典,不会造成危害,当然请确保你的脚本来源!!

spool results01.txt
set echo on feedback on time on timing on pagesize 100 linesize 80 numwidth 13
show user
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from v$version;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;
column name format a30
column value format a49
select name, value from v$parameter where isdefault='FALSE' order by 1;
column parameter format a30
column value format a49
select * from v$nls_parameters order by parameter;
column name format a10
select dbid, name,
to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode, log_mode,
to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
to_char(controlfile_change#, '999999999999999') as controlfile_change#,
to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
to_char(resetlogs_change#, '999999999999999') as resetlogs_change#,
to_char(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
from v$database;
select * from v$instance;
archive log list;
select * from v$thread order by thread#;
select * from v$log order by first_change#;
column member format a45
select * from v$logfile;
column name format a79
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
'#' || df.name || '#' as filename, df.file#, df.status, df.enabled, df.creation_change#,
to_char(df.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
to_char(df.checkpoint_change#, '999999999999999') as checkpoint_change#,
to_char(df.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
to_char(df.offline_change#, '999999999999999') as offline_change#,
to_char(df.online_change#, '999999999999999') as online_change#,
to_char(df.online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
to_char(df.unrecoverable_change#, '999999999999999') as online_change#,
to_char(df.unrecoverable_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
to_char(df.bytes, '9,999,999,999,990') as bytes, block_size
from v$datafile df, v$tablespace ts
where ts.ts# = df.ts#
and ( df.status <> 'ONLINE'
or    df.checkpoint_change# <> (select checkpoint_change# from v$database) );
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
'#' || dh.name || '#' as filename, dh.file#, dh.status, dh.error, dh.
fuzzy, dh.creation_change#,
to_char(dh.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
to_char(dh.checkpoint_change#, '999999999999999') as checkpoint_change#,
to_char(dh.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
to_char(dh.resetlogs_change#, '999999999999999') as resetlogs_change#,
to_char(dh.resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
to_char(dh.bytes, '9,999,999,999,990') as bytes
from v$datafile_header dh, v$tablespace ts
where ts.ts# = dh.ts#
and ( dh.status <> 'ONLINE'
or    dh.checkpoint_change# <> (select checkpoint_change# from v$database) );
select * from v$tempfile;
select HXFIL File_num,substr(HXFNM,1,60) file_name, FHTNM tablespace_name,
FHTYP type, HXERR validity,
FHSCN SCN, FHTIM SCN_Time, FHSTA status,
FHTHR Thread, FHRBA_SEQ Sequence
from X$KCVFH
--where HXERR > 0
order by HXERR, FHSTA, FHSCN, HXFIL;
column error format a15
select error, fuzzy, status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by error, fuzzy, status, checkpoint_change#, checkpoint_time
order by checkpoint_change#, checkpoint_time;
select * from V$INSTANCE_RECOVERY;
select * from v$recover_file order by change#;
select * from dba_tablespaces where status <> 'ONLINE';
SELECT * FROM database_properties order by property_name;
select *
from X$KCCLH, (select min(checkpoint_change#) df_min_scn,
min(checkpoint_change#) df_max_scn
from v$datafile_header
where status='ONLINE') df
where LHLOS in (select first_change# from v$log)
or df.df_min_scn between LHLOS and LHNXS
or df.df_max_scn between LHLOS and LHNXS;
select * from v$backup where status <> 'NOT ACTIVE';
select ADDR, XIDUSN, XIDSLOT, XIDSQN,
UBAFIL, UBABLK, UBASQN,
START_UBAFIL, START_UBABLK, START_UBASQN,
USED_UBLK, STATUS
from   v$transaction;
select * from v$archive_gap;
select * from v$archive_dest_status where recovery_mode <> 'IDLE';
column USED_GB format 999,990.999
column USED% format 990.99
column RECLAIM_GB format 999,990.999
column RECLAIMABLE% format 990.99
column LIMIT_GB format 999,990.999
select frau.file_type as type,
frau.percent_space_used/100 * rfd.space_limit /1024/1024/1024 "USED_GB",
frau.percent_space_used "USED%",
frau.percent_space_reclaimable "RECLAIMABLE%",
frau.percent_space_reclaimable/100 * rfd.space_limit /1024/1024/1024 "RECLAIM_GB",
frau.number_of_files "FILES#"
from   v$flash_recovery_area_usage frau,
v$recovery_file_dest rfd
order by file_type;
select name,
space_limit/1024/1024/1024 "LIMIT_GB",
space_used/1024/1024/1024 "USED_GB",
space_used/space_limit*100 "USED%",
space_reclaimable/1024/1024/1024 "RECLAIM_GB",
number_of_files "FILE#"
from   v$recovery_file_dest;
select * from v$backup_corruption;
select * from v$copy_corruption order by file#, block#;
select * from v$database_block_corruption order by file#, block#;
SELECT f.file#, f.name,
e.tablespace_name, e.segment_type, e.owner, e.segment_name,
c.file#, c.block#, c.blocks, c.corruption_change#, c.corruption_type
FROM dba_extents e, V$database_block_corruption c, v$datafile f
WHERE c.file# = f.file#
and   e.file_id = c.file#
and   c.block# between e.block_id AND e.block_id + e.blocks - 1;
select * from v$database_incarnation;
select * from v$rman_configuration;
select s.recid as bs_key, p.recid as bp_key, p.status, p.tag, p.device_type,
p.handle, p.media, p.completion_time, p.bytes
from   v$backup_piece p, v$backup_set s
where  p.set_stamp = s.set_stamp
and    s.controlfile_included='YES'
order by p.completion_time;
select s.recid as bs_key, p.recid as bp_key, p.status, p.tag, p.device_type,
p.handle, p.media, p.completion_time, f.absolute_fuzzy_change#, p.bytes
from   v$backup_datafile f, v$backup_piece p, v$backup_set s
where  p.set_stamp = s.set_stamp
and    f.set_stamp = s.set_stamp
and    p.handle is not null
and    f.file# = 1
order by p.completion_time;
SELECT
session_recid,
input_bytes_per_sec_display,
output_bytes_per_sec_display,
time_taken_display,
end_time
FROM v$rman_backup_job_details
ORDER BY end_time;
select * from v$filestat;
column EBS_MB format 9,990.99
column TOTAL_MB format 999,990.99
select SID, SERIAL, FILENAME, EFFECTIVE_BYTES_PER_SECOND/1024/1024 as EBS_MB,
OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, TOTAL_BYTES/1024/1024 as TOTAL_MB,
STATUS, MAXOPENFILES, buffer_size, buffer_count
from v$backup_async_io
where close_time >= sysdate-3
order by close_time;
select SID, SERIAL, FILENAME, EFFECTIVE_BYTES_PER_SECOND/1024/1024 as EBS_MB,
OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, TOTAL_BYTES/1024/1024 as TOTAL_MB,
STATUS, MAXOPENFILES, buffer_size, buffer_count
from v$backup_sync_io
where close_time >= sysdate-3;
select * from v$controlfile_record_section order by type;
select to_char(rownum) || '. ' || output rman_output from v$rman_output;
select * from v$rman_status where trunc(end_time) > trunc(sysdate)-3;
select protection_mode, protection_level from v$database;
select * from v$recovery_progress;
select s.client_info,
sl.message,
sl.sid, sl.serial#, p.spid,
round(sl.sofar/sl.totalwork*100,2) "% Complete"
from   v$session_longops sl, v$session s, v$process p
where  p.addr = s.paddr
and    sl.sid=s.sid
and    sl.serial#=s.serial#
and    opname LIKE 'RMAN%'
and    opname NOT LIKE '%aggregate%'
and    totalwork != 0
and    sofar <> totalwork;
select AL.*,
DF.min_checkpoint_change#, DF.min_checkpoint_time
from v$archived_log AL,
(select min(checkpoint_change#) min_checkpoint_change#,
min(checkpoint_time) min_checkpoint_time
from v$datafile_header
where status='ONLINE') DF
where DF.min_checkpoint_change# between AL.first_change# and AL.next_change#
order by AL.first_change#;
select * from v$asm_diskgroup;
select * from v$asm_disk;
select * from v$flashback_database_log;
select * from v$flashback_database_logfile order by first_change# desc;
select * from v$flashback_database_stat order by begin_time desc;
select * from v$restore_point;
select * from v$rollname;
select * from v$undostat;
select * from dba_rollback_segs;
spool off

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了。

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

 

如果自己搞不定可以找诗檀软件专业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之前必须执行必要的备份,并在专业人士的指导下操作!

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

一套Linux上的10.2.0.4系统在异常恢复后(使用_allow_resetlogs_corruption隐藏参数打开后遭遇ORA-00600:[40xx]相关的内部错误,创建并切换到了新的撤销表空间上)出现ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []内部错误,当该非内部错误(non-fatal)出现100次以上时会在告警日志alert.log中出现记录。
并有可能导致实例crash,具体日志如下:

 

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

 

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

 

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

 

 

Errors in file /s01/10gdb/admin/clinica/bdump/clinica_smon_21463.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Tue Jan  4 23:13:19 2011
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
clinica_smon_21463.trc:
Dump of buffer cache at level 4 for tsn=1, rdba=8388633
BH (0x91fdf428) file#: 2 rdba: 0x00800019 (2/25) class: 19 ba: 0x91c62000
set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 0
dbwrid: 0 obj: -1 objn: 0 tsn: 1 afn: 2
hash: [fcf7dd68,fcf7dd68] lru: [91fdf5b8,91fdf398]
ckptq: [NULL] fileq: [NULL] objq: [f5b53d60,f5b53d60]
use: [fa694970,fa694970] wait: [NULL]
st: XCURRENT md: SHR tch: 0
flags: gotten_in_current_mode
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 1 rdba: 0x00800019 (2/25)
scn: 0x0000.0352d07c seq: 0x01 flg: 0x00 tail: 0xd07c2601
frmt: 0x02 chkval: 0x0000 type: 0x26=KTU SMU HEADER BLOCK
/* 这里dump了一个tsn=1,file#=2的数据块,
可以看到它的类型是KTU SMU HEADER BLOCK即某个回滚段头
*/
Hex dump of block: st=0, typ_found=1
........................
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Current SQL statement for this session:
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas,  num_mappings, tim_scn_map) 
values (0, :1, :2, :3, :4, :5, :6, :7)
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
7FFFF53BC160 ? 7FFFF53BC1C0 ?
7FFFF53BC100 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
7FFFF53BC160 ? 7FFFF53BC1C0 ?
7FFFF53BC100 ? 000000000 ?
ksfdmp()+21          call     ksedmp()             000000003 ? 000000001 ?
7FFFF53BC160 ? 7FFFF53BC1C0 ?
7FFFF53BC100 ? 000000000 ?
kgeriv()+176         call     ksfdmp()             000000003 ? 000000001 ?
7FFFF53BC160 ? 7FFFF53BC1C0 ?
7FFFF53BC100 ? 000000000 ?
kgesiv()+119         call     kgeriv()             0068C97C0 ? 2ABDF1D42BF0 ?
000000000 ? 0F4A33EA0 ?
7FFFF53BC100 ? 000000000 ?
ksesic0()+209        call     kgesiv()             0068C97C0 ? 2ABDF1D42BF0 ?
000001001 ? 000000000 ?
7FFFF53BCEE0 ? 000000000 ?
ktugti()+3200        call     ksesic0()            000001001 ? 0068C9940 ?
000000000 ? 00000009A ?
000000010 ? 101010101010101 ?
ktsftcmove()+4149    call     ktugti()             0B73F111C ? 7FFFF53BD278 ?
7FFFF53BD280 ? 000000000 ?
7FFFF53BD27C ? 7FFFF53BD270 ?
ktsf_gsp()+1937      call     ktsftcmove()         00000000A ? 000000000 ?
000000000 ? 000000000 ?
7FFFF53BD27C ? 7FFFF53BD270 ?
kdtgsp()+512         call     ktsf_gsp()           000000000 ? 7FFFF53BF460 ?
000000024 ? 000000002 ?
7FFFF53BF460 ? 000000000 ?
kdccak()+111         call     kdtgsp()             2ABDF1D6A2D8 ? 7FFF00000000 ?
2ABDF1D68530 ? 000000002 ?
7FFFF53BF460 ? 000000000 ?
kdcgcs()+5419        call     kdccak()             2ABDF1D6A2D8 ? 000000001 ?
0F4A3BBA8 ? 000000000 ?
2ABDF1D6A370 ? 000000000 ?
kdcgsp()+1372        call     kdcgcs()             2ABDF1D6A2D8 ? 000000001 ?
0F4A3BBA8 ? 000000000 ?
2ABDF1D6A370 ? 000000000 ?
kdtInsRow()+1808     call     kdcgsp()             2ABDF1D6A2D8 ? 000000001 ?
0F4A3BBA8 ? 000000000 ?
2ABDF1D6A370 ? 000000000 ?
insrow()+342         call     kdtInsRow()          2ABDF1D6A2D8 ? 000000001 ?
0F4A3BBA8 ? 000000000 ?
2ABDF1D6A370 ? 000000000 ?
insdrv()+594         call     insrow()             2ABDF1D6A2D8 ? 7FFFF53BFCC8 ?
000000000 ? 0F4A33DE0 ?
2ABDF1D6A370 ? 000000000 ?
inscovexe()+404      call     insdrv()             2ABDF1D6A2D8 ? 7FFFF53BFCC8 ?
000000000 ? 2ABDF1D6D908 ?
2ABDF1D6A370 ? 000000000 ?
insExecStmtExecIniE  call     inscovexe()          0F4A33DE0 ? 0F4A3C230 ?
ngine()+85                                         7FFFF53C0EF0 ? 2ABDF1D69F20 ?
2ABDF1D6A370 ? 000000000 ?
insexe()+386         call     insExecStmtExecIniE  0F4A33DE0 ? 0F4A3C230 ?
ngine()              2ABDF1D69F20 ? 2ABDF1D69F20 ?
2ABDF1D6A370 ? 000000000 ?
opiexe()+9182        call     insexe()             0F4A333A8 ? 7FFFF53C0EF0 ?
0F4A33DE0 ? 2ABDF1D69F20 ?
2ABDF1D6A370 ? 2ABDF1D69F20 ?
opiall0()+1842       call     opiexe()             000000049 ? 000000003 ?
7FFFF53C12F8 ? 000000001 ?
..............

针对该ORA-00600:[4097]内部错误,metalink上Note [ID 1030620.6]介绍了一种workaround的方法:

An ORA-600 [4097] can be encountered through various activities that use 
rollback segments.
Solution Description: 
===================== 
The most likely cause of this is BUG 427389.  This BUG is fixed in
version 7.3.3.3.  The BUG is caused when Rollback Segments are dropped and 
recreated after a shutdown abort.  It is encountered through a very specific 
set of circumstances: 
When an instance has a rollback segment offline and the instance crashes, or 
the user does a shutdown abort, the rollback segment wrap number does not get 
updated.  If that segment is then dropped and recreated immediately after the 
instance is restarted, the wrap number could be lower than existing wrap 
numbers.  This will cause the ORA-600[4097] to occur in subsequent 
transactions using Rollback. 
To avoid encountering this bug, rollback segments should only be dropped and 
recreated after the instance has been shutdown normal and restarted.  If you 
have already encountered the bug, use the following workaround:  
Select segment_name, segment_id from dba_rollback_segs; 
Drop all Rollback Segments except for SYSTEM.  
Recreate dummy (small) rollback segments with the same names in their place. 
Then, recreate additional rollback segments you want to keep with their 
permanent storage parameters.   
Now drop the dummy ones. This should ensure that the segment_ids are not 
reused. 
If you ever want to add a rollback segment you have to use the workaround steps
again.  If you do not fill the dummy slots you may see the problem re-appear.

我们可以尝试drop异常恢复前已有的可能存在问题的rollback segment来规避这个问题,虽然在10g下使用AMU(automatic managed undo)但仍可以做到这一点:

SQL> alter system set "_smu_debug_mode"=4;
System altered.
/* 设置SMU debug模式为4以便能够手动管理回滚段 */
SQL> set heading off 
SQL> select 'drop rollback segment "'||segment_name||'";' from dba_rollback_segs where segment_name!='SYSTEM';
drop rollback segment "_SYSSMU1$";
drop rollback segment "_SYSSMU2$";
drop rollback segment "_SYSSMU3$";
drop rollback segment "_SYSSMU4$";
drop rollback segment "_SYSSMU5$";
drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU7$";
drop rollback segment "_SYSSMU8$";
drop rollback segment "_SYSSMU9$";
drop rollback segment "_SYSSMU10$";
drop rollback segment "_SYSSMU11$";
drop rollback segment "_SYSSMU12$";
drop rollback segment "_SYSSMU13$";
drop rollback segment "_SYSSMU14$";
drop rollback segment "_SYSSMU15$";
drop rollback segment "_SYSSMU16$";
drop rollback segment "_SYSSMU17$";
drop rollback segment "_SYSSMU18$";
drop rollback segment "_SYSSMU19$";
drop rollback segment "_SYSSMU20$";
drop rollback segment "_SYSSMU21$";
drop rollback segment "_SYSSMU22$";
drop rollback segment "_SYSSMU23$";
drop rollback segment "_SYSSMU24$";
drop rollback segment "_SYSSMU25$";
drop rollback segment "_SYSSMU26$";
drop rollback segment "_SYSSMU27$";
drop rollback segment "_SYSSMU28$";
drop rollback segment "_SYSSMU29$";
drop rollback segment "_SYSSMU30$";
30 rows selected.
/* 依次执行以上的drop rollback segment回滚段的命令
注意当前撤销表空间上的回滚段仅能offline而无法drop掉,
实际上我们需要做的也仅仅是把之前undo表空间上有问题的回滚段drop掉
*/
SQL> alter rollback segment "_SYSSMU30$" offline;
Rollback segment altered.
SQL> drop rollback segment "_SYSSMU30$";
drop rollback segment "_SYSSMU30$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU30$' (in undo tablespace) not allowed
SQL> alter rollback segment "_SYSSMU30$" online;
Rollback segment altered.

经过以上drop问题回滚段rollback segment后,系统不再出现ORA-00600:[4097]内部错误,实例恢复正常。在系统正常后,我们有必要重置之前所设的”_smu_debug_mode”UNDO管理debug模式的隐藏参数。

DBMS_REPAIR example


PURPOSE
This document provides an example of DBMS_REPAIR as introduced in Oracle 8i.
Oracle provides different methods for detecting and correcting data block
corruption - DBMS_REPAIR is one option.
WARNING: Any corruption that involves the loss of data requires analysis to
understand how that data fits into the overall database system. Depending on
the nature of the repair, you may lose data and logical inconsistencies can
be introduced; therefore you need to carefully weigh the gains and losses
associated with using DBMS_REPAIR.
SCOPE & APPLICATION
This article is intended to assist an experienced DBA working with an Oracle
Worldwide Support analyst only.  This article does not contain general
information regarding the DBMS_REPAIR package, rather it is designed to provide
sample code that can be customized by the user (with the assistance of
an Oracle support analyst) to address database corruption.  The
"Detecting and Repairing Data Block Corruption" Chapter of the Oracle8i
Administrator's  Guide should be read and risk assessment analyzed prior to
proceeding.
RELATED DOCUMENTS
Oracle 8i Administrator's Guide,  DBMS_REPAIR Chapter
Introduction
=============
Note: The DBMS_REPAIR package is used to work with corruption in the
transaction layer and the data layer only (software corrupt blocks).
Blocks with physical corruption (ex. fractured block) are marked as
the block is read into the buffer cache and DBMS_REPAIR ignores all
blocks marked corrupt.
The only block repair in the initial release of DBMS_REPAIR is to
*** mark the block software corrupt ***.
DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM must both be set to FALSE.
A backup of the file(s) with corruption should be made before using package.
Database Summary
===============
A corrupt block exists in table T1.
SQL> desc t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                      NOT NULL NUMBER(38)
COL2                                               CHAR(512)
SQL> analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
---> Note: In the trace file produced from the ANALYZE, it can be determined
---        that the corrupt block contains 3 rows of data (nrows = 3).
---        The leading lines of the trace file follows:
Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_2835.trc
Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta
With the Partitioning option
*** 1998.12.16.15.53.02.000
*** SESSION ID:(7.6) 1998.12.16.15.53.02.000
kdbchk: row locked by non-existent transaction
table=0   slot=0
lockid=32   ktbbhitc=1
Block header dump:  0x01800003
Object id on Block? Y
seg/obj: 0xb6d  csc: 0x00.1cf5f  itc: 1  flg: -  typ: 1 - DATA
fsl: 0  fnx: 0x0 ver: 0x01
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0002.011.00000121    uba: 0x008018fb.0345.0d  --U-    3  fsc
0x0000.0001cf60
data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x18
pbl: 0x28088044
bdba: 0x01800003
flag=-----------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x19d
avsp=0x185
tosp=0x185
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x5ff
0x14:pri[1]     offs=0x3a6
0x16:pri[2]     offs=0x19d
block_row_dump:
[... remainder of file not included]
end_of_block_dump
DBMS_REPAIR.ADMIN_TABLES (repair and orphan key
================================================
ADMIN_TABLES provides administrative functions for repair and orphan key tables.
SQL> @adminCreate
SQL> connect sys/change_on_install
Connected.
SQL>
SQL> -- Repair Table
SQL>
SQL> declare
2  begin
3  -- Create repair table
4  dbms_repair.admin_tables (
5  --    table_name => 'REPAIR_TABLE',
6      table_type => dbms_repair.repair_table,
7      action => dbms_repair.create_action,
8      tablespace => 'USERS');          -- default TS of SYS if not specified
9  end;
10  /
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type
2  from dba_objects
3  where object_name like '%REPAIR_TABLE';
OWNER                 OBJECT_NAME                      OBJECT_TYPE
------------------------------------------------------------------
SYS                   DBA_REPAIR_TABLE                 VIEW
SYS                   REPAIR_TABLE                     TABLE
SQL>
SQL> -- Orphan Key Table
SQL>
SQL> declare
2  begin
3  -- Create orphan key table
4  dbms_repair.admin_tables (
5      table_type => dbms_repair.orphan_table,
6      action => dbms_repair.create_action,
7      tablespace => 'USERS');          -- default TS of SYS if not specified
8  end;
9  /
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type
2  from dba_objects
3  where object_name like '%ORPHAN_KEY_TABLE';
OWNER                 OBJECT_NAME                      OBJECT_TYPE
------------------------------------------------------------------
SYS                   DBA_ORPHAN_KEY_TABLE             VIEW
SYS                   ORPHAN_KEY_TABLE                 TABLE
DBMS_REPAIR.CHECK_OBJECT
=========================
CHECK_OBJECT procedure checks the specified object and populates the repair
table with information about corruption and repair directive(s).  Validation
consists of block checking all blocks in the object.  All blocks previously
marked corrupt will be skipped.
Note: In the initial release of DBMS_REPAIR the only repair is to mark the
block as software corrupt.
SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
2     rpr_count int;
3  begin
4     rpr_count := 0;
5  dbms_repair.check_object (
6     schema_name => 'SYSTEM',
7     object_name => 'T1',
8     repair_table_name => 'REPAIR_TABLE',
9     corrupt_count => rpr_count);
10     dbms_output.put_line('repair count: ' || to_char(rpr_count));
11  end;
12  /
repair count: 1
PL/SQL procedure successfully completed.
SQL> desc repair_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_ID                                 NOT NULL NUMBER
TABLESPACE_ID                             NOT NULL NUMBER
RELATIVE_FILE_ID                          NOT NULL NUMBER
BLOCK_ID                                  NOT NULL NUMBER
CORRUPT_TYPE                              NOT NULL NUMBER
SCHEMA_NAME                               NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
BASEOBJECT_NAME                                    VARCHAR2(30)
PARTITION_NAME                                     VARCHAR2(30)
CORRUPT_DESCRIPTION                                VARCHAR2(2000)
REPAIR_DESCRIPTION                                 VARCHAR2(200)
MARKED_CORRUPT                            NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP                           NOT NULL DATE
FIX_TIMESTAMP                                      DATE
REFORMAT_TIMESTAMP                                 DATE
SQL> select object_name, block_id, corrupt_type, marked_corrupt,
2  corrupt_description, repair_description
3  from repair_table;
OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
T1                                      3            1 FALSE
kdbchk: row locked by non-existent transaction
table=0   slot=0
lockid=32   ktbbhitc=1
mark block software corrupt
Data Extraction
===============
The repair table indicates that block 3 of file 6 is corrupt - but remember
that this block has not yet been marked as corrupt, therefore now is the
time to extract any meaningful data.  After the block is marked corrupt,
the entire block must be skipped.
1. Determine the number of rows in the block from ALTER SYSTEM DUMP (nrows = 3).
2. Query the corrupt object and extract as much information as possible.
SQL> -- The following query can be used to salvage data from a corrupt block.
SQL> -- Creating a temporary table facilitates data insertion.
SQL> create table temp_t1 as
2  select * from system.t1
3  where dbms_rowid.rowid_block_number(rowid) = 3
4  and dbms_rowid.rowid_to_absolute_fno (rowid, 'SYSTEM','T1') = 6;
Table created.
SQL> select col1 from temp_t1;
COL1
----------
2
3
DBMS_REPAIR.FIX_CORRUPT_BLOCKS  (ORA-1578)
============================================
FIX_CORRUPT_BLOCKS procedure fixes the corrupt blocks in the specified objects
based on information in the repair table.  After the block has been marked as
corrupt,  an ORA-1578 results when a full table scan is performed.
SQL> declare
2     fix_count int;
3  begin
4     fix_count := 0;
5  dbms_repair.fix_corrupt_blocks (
6     schema_name => 'SYSTEM',
7     object_name => 'T1',
8     object_type => dbms_repair.table_object,
9     repair_table_name => 'REPAIR_TABLE',
10     fix_count => fix_count);
11     dbms_output.put_line('fix count: ' || to_char(fix_count));
12  end;
13  /
fix count: 1
PL/SQL procedure successfully completed.
SQL> select object_name, block_id, marked_corrupt
2  from repair_table;
OBJECT_NAME                      BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
T1                                      3 TRUE
SQL> select * from system.t1;
select * from system.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 3)
ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'
DBMS_REPAIR.DUMP_ORPHAN_KEYS
==============================
DUMP_ORPHAN_KEYS reports on index entries that point to rows in corrupt data
blocks.
SQL> select index_name from dba_indexes
2  where table_name in (select distinct object_name from repair_table);
INDEX_NAME
------------------------------
T1_PK
SQL> @dumpOrphanKeys
SQL> set serveroutput on
SQL>
SQL> declare
2     key_count int;
3  begin
4     key_count := 0;
5  dbms_repair.dump_orphan_keys (
6     schema_name => 'SYSTEM',
7     object_name => 'T1_PK',
8     object_type => dbms_repair.index_object,
9     repair_table_name => 'REPAIR_TABLE',
10     orphan_table_name => 'ORPHAN_KEY_TABLE',
11     key_count => key_count);
12     dbms_output.put_line('orphan key count: ' || to_char(key_count));
13  end;
14  /
orphan key count: 3
PL/SQL procedure successfully completed.
SQL> desc orphan_key_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME                               NOT NULL VARCHAR2(30)
INDEX_NAME                                NOT NULL VARCHAR2(30)
IPART_NAME                                         VARCHAR2(30)
INDEX_ID                                  NOT NULL NUMBER
TABLE_NAME                                NOT NULL VARCHAR2(30)
PART_NAME                                          VARCHAR2(30)
TABLE_ID                                  NOT NULL NUMBER
KEYROWID                                  NOT NULL ROWID
KEY                                       NOT NULL ROWID
DUMP_TIMESTAMP                            NOT NULL DATE
SQL> select index_name, count(*) from orphan_key_table
2  group by index_name;
INDEX_NAME                       COUNT(*)
------------------------------ ----------
T1_PK                                   3
Note: Index entry in the orphan key table implies that the index should be
rebuilt to guarantee the a table probe and an index probe return the same
result set.
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
===============================
SKIP_CORRUPT_BLOCKS enables/disables the skipping of corrupt blocks during
index and table scans of a specified object.
Note: If an index and table are out of sync, then a SET TRANSACTION READ ONLY
transaction may be inconsistent in situations where one query probes only
the index and then a subsequent query probes both the index and the table.
If the table block is marked corrupt, then the two queries will return
different results.
Suggestion: If SKIP_CORRUPT_BLOCKS is enabled, then rebuild any indexes
identified in the orphan key table (or all index associated with object
if DUMP_ORPHAN_KEYS was omitted).
SQL> @skipCorruptBlocks
SQL> declare
2  begin
3  dbms_repair.skip_corrupt_blocks (
4     schema_name => 'SYSTEM',
5     object_name => 'T1',
6     object_type => dbms_repair.table_object,
7     flags => dbms_repair.skip_flag);
8  end;
9  /
PL/SQL procedure successfully completed.
SQL> select table_name, skip_corrupt from dba_tables
2  where table_name = 'T1';
TABLE_NAME                     SKIP_COR
------------------------------ --------
T1                             ENABLED
SQL> -- rows in corrupt block skipped, no errors on full table scan
SQL> select * from system.t1;
COL1              COL2
--------------------------------------------------------------------------------
4                 dddd
5                 eeee
--> Notice the pk index has not yet been corrected.
SQL> insert into system.t1 values (1,'aaaa');
insert into system.t1 values (1,'aaaa')
*
SQL> select * from system.t1 where col1 = 1;
no rows selected
DBMS_REPAIR.REBUILD_FREELISTS
===============================
REBUILD_FREELISTS rebuilds freelists for the specified object.
SQL> declare
2  begin
3  dbms_repair.rebuild_freelists (
4     schema_name => 'SYSTEM',
5     object_name => 'T1',
6     object_type => dbms_repair.table_object);
7  end;
8  /
PL/SQL procedure successfully completed.
Rebuild Index
=============
Note:  Every index identified in the orphan key table should be rebuilt to
ensure consistent results.
SQL> alter index system.t1_pk rebuild online;
Index altered.
SQL> insert into system.t1 values (1, 'aaaa');
1 row created.
SQL> select * from system.t1;
COL1              COL2
--------------------------------------------------------------------------------
4                 dddd
5                 eeee
1                 aaaa
Note - The above insert statement was used to provide a simple example.
This is the perfect world - we know the data that was lost.  The temporary
table (temp_t1) should also be used to include all rows extracted from
the corrupt block.
Conclusion
==========
At this point the table T1 is available but data loss was incurred.  In general,
data loss must be seriously considered before using the DBMS_REPAIR package for
mining the index segment and/or table block dumps is very complicated and
logical inconsistencies may be introduced.  In the initial release, the only
repair affected by DBMS_REPAIR is to mark the block as software corrupt.
<<End of Article>

沪ICP备14014813号

沪公网安备 31010802001379号