你的位置:首页 > 数据库

[数据库]无归档情况下使用BBED处理ORA


在丢失归档情况下,恢复时常会遇到ora-01113错误,以下实验模拟表空间offline,然后在丢失归档文件的情况下使用BBED修改文件头信息,最后恢复数据文件;
数据库版本:

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE  11.2.0.1.0   ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production

数据文件信息:

SQL> select file#,name, CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss')checkpoint_time,status,BLOCK_SIZE from v$datafile;   FILE# NAME                CHECKPOINT_CHANGE# CHECKPOINT_TIME   STATUS BLOCK_SIZE---------- ----------------------------------- ------------------ ------------------- ------- ----------     1 /u01/app/oradata/sydb/system01.dbf       3161898 2015-04-13 20:46:37 SYSTEM    8192     2 /u01/app/oradata/sydb/sysaux01.dbf       3161898 2015-04-13 20:46:37 ONLINE    8192     3 /u01/app/oradata/sydb/undotbs01.dbf      3161898 2015-04-13 20:46:37 ONLINE    8192     4 /u01/app/oradata/sydb/users01.dbf       3161898 2015-04-13 20:46:37 ONLINE    8192     5 /disk2/oradata/sydb/tbs01.dbf         3161898 2015-04-13 20:46:37 ONLINE    8192     6 /disk2/oradata/sydb/tbs02.dbf         3161898 2015-04-13 20:46:37 ONLINE    8192     7 /disk2/oradata/sydb/tbs03.dbf         3161898 2015-04-13 20:46:37 ONLINE    8192     8 /tmp/tbs_tmp.dbf                3161898 2015-04-13 20:46:37 ONLINE    8192

offline数据文件7并删除归档模拟ora-01113:

SQL> alter database datafile 7 offline;Database altered.SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.SQL> alter database datafile 7 online;alter database datafile 7 online*ERROR at line 1:ORA-01113: file 7 needs media recoveryORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf'SQL> select file#,change#,time from v$recover_file;   FILE#  CHANGE# TIME---------- ---------- ---------     7  3161898 13-APR-15

使用BBED需要修改的内容有:
ub4kscnbas @484    #最后检查的SCN
ub4kcvcptim @492    #检查时间
ub4kcvfhcpc @140   #检查点发生次数
ub4kcvfhccc @148   #未知,但值一直小于kcvfhcpc1

BBED> info File# Name                            Size(blks) ----- ----                            ----------   1 /u01/app/oradata/sydb/system01.dbf                92160   2 /u01/app/oradata/sydb/sysaux01.dbf                71680   3 /u01/app/oradata/sydb/undotbs01.dbf               52480   4 /u01/app/oradata/sydb/users01.dbf                 1600   5 /disk2/oradata/sydb/tbs01.dbf                  221696   6 /disk2/oradata/sydb/tbs02.dbf                  14592   7 /disk2/oradata/sydb/tbs03.dbf                   2560   8 /tmp/tbs_tmp.dbf                         1280

 先确认好system表空间文件信息:

BBED> set dba 1,1 block 1    DBA       0x00400001 (4194305 1,1)    BLOCK#     1BBED> print kcvfhckpstruct kcvfhckp, 36 bytes          @484    struct kcvcpscn, 8 bytes         @484      ub4 kscnbas              @484   0x003043a9   ub2 kscnwrp              @488   0x0000  ub4 kcvcptim               @492   0x34453174BBED> dump/v dba 1,1 offset 484 count 30 File: /u01/app/oradata/sydb/system01.dbf (1) Block: 1    Offsets: 484 to 513 Dba:0x00400001------------------------------------------------------- a9433000 00000000 74314534 01000000 l ....t1E4.... ab000000 2e080000 1000866b 0200   l .......k..BBED> dump/v dba 1,1 offset 492 count 30 File: /u01/app/oradata/sydb/system01.dbf (1) Block: 1    Offsets: 492 to 521 Dba:0x00400001------------------------------------------------------- 74314534 01000000 ab000000 2e080000 l t1E4........ 1000866b 02000000 00000000 0000   l ...k.......…BBED> print kcvfhcpcub4 kcvfhcpc                @140   0x00000237BBED> dump/v dba 1,1 offset 140 count 30 File: /u01/app/oradata/sydb/system01.dbf (1) Block: 1    Offsets: 140 to 169 Dba:0x00400001------------------------------------------------------- 37020000 fe522034 36020000 00000000 l 7...6....... 00000000 00000000 00000000 0000   l ...........…BBED> print kcvfhcccub4 kcvfhccc                @148   0x00000236BBED> dump/v dba 1,1 offset 148 count 30 File: /u01/app/oradata/sydb/system01.dbf (1) Block: 1    Offsets: 148 to 177 Dba:0x00400001------------------------------------------------------- 36020000 00000000 00000000 00000000 l 6............... 00000000 00000000 00000000 0000   l ..............

再确认数据文件7信息:

BBED> print kcvfhckpstruct kcvfhckp, 36 bytes          @484    struct kcvcpscn, 8 bytes         @484      ub4 kscnbas              @484   0x00303f2a   ub2 kscnwrp              @488   0x0000  ub4 kcvcptim               @492   0x34452bad  ub2 kcvcpthr               @496   0x0001BBED> dump/v dba 7,1 offset 484 count 30 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1    Offsets: 484 to 513 Dba:0x01c00001------------------------------------------------------- 2a3f3000 00000000 ad2b4534 01000000 l *?0......... a8000000 65000000 10000000 0200   l e......... <16 bytes per line>BBED> dump/v dba 7,1 offset 492 count 30 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1    Offsets: 492 to 521 Dba:0x01c00001------------------------------------------------------- ad2b4534 01000000 a8000000 65000000 l ....e... 10000000 02000000 00000000 0000   l ...........…BBED> print kcvfhcpcub4 kcvfhcpc                @140   0x00000164BBED> dump/v dba 7,1 offset 140 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1    Offsets: 140 to 169 Dba:0x01c00001------------------------------------------------------- 64010000 fe522034 63010000 00000000 l d...c....... 00000000 00000000 00000000 0000   l ...........…BBED> print kcvfhcccub4 kcvfhccc                @148   0x00000163BBED> dump/v dba 7,1 offset 148 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1    Offsets: 148 to 177 Dba:0x01c00001------------------------------------------------------- 63010000 00000000 00000000 00000000 l c............... 00000000 00000000 00000000 0000   l ..............

00303f2a 刚好是v$recover_file 中查询到的CHANGE#值

SQL> select to_number('00303f2a','xxxxxxxxx') from dual;TO_NUMBER('00303F2A','XXXXXXXXX')---------------------------------             3161898

修改值:

BBED> modify /x a943 dba 7,1 offset 484BBED> set offset +2    OFFSET     486BBED> modify /x 3000BBED> dump /v dba 7,1 offset 484 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1    Offsets: 484 to 513 Dba:0x01c00001------------------------------------------------------- a9433000 00000000 ad2b4534 01000000 l ........ a8000000 65000000 10000000 0200   l e......…BBED> modify /x 74314534 dba 7,1 offset 492 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1        Offsets: 492 to 521      Dba:0x01c00001------------------------------------------------------------------------ 74314534 01000000 a8000000 65000000 10000000 02000000 00000000 0000 BBED> modify /x 37020000 dba 7,1 offset 140 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1        Offsets: 140 to 169      Dba:0x01c00001------------------------------------------------------------------------ 37020000 fe522034 63010000 00000000 00000000 00000000 00000000 0000 BBED> modify /x 36020000 dba 7,1 offset 148 File: /disk2/oradata/sydb/tbs03.dbf (7) Block: 1        Offsets: 148 to 177      Dba:0x01c00001------------------------------------------------------------------------ 36020000 00000000 00000000 00000000 00000000 00000000 00000000 0000BBED> sum applyCheck value for File 7, Block 1:current = 0x7b4d, required = 0x7b4d

确认修改:

SQL> select * from v$recover_file;   FILE# ONLINE ONLINE_ ERROR                                CHANGE# TIME---------- ------- ------- ----------------------------------------------------------------- ---------- ---------     7 OFFLINE OFFLINE UNKNOWN ERROR                      3163049 13-APR-15SQL> column name format a35SQL> select file#,name, CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss')checkpoint_time,status,BLOCK_SIZE from v$datafile;   FILE# NAME                CHECKPOINT_CHANGE# CHECKPOINT_TIME   STATUS BLOCK_SIZE---------- ----------------------------------- ------------------ ------------------- ------- ----------     1 /u01/app/oradata/sydb/system01.dbf       3163049 2015-04-13 21:11:16 SYSTEM    8192     2 /u01/app/oradata/sydb/sysaux01.dbf       3163049 2015-04-13 21:11:16 ONLINE    8192     3 /u01/app/oradata/sydb/undotbs01.dbf      3163049 2015-04-13 21:11:16 ONLINE    8192     4 /u01/app/oradata/sydb/users01.dbf       3163049 2015-04-13 21:11:16 ONLINE    8192     5 /disk2/oradata/sydb/tbs01.dbf         3163049 2015-04-13 21:11:16 ONLINE    8192     6 /disk2/oradata/sydb/tbs02.dbf         3163049 2015-04-13 21:11:16 ONLINE    8192     7 /disk2/oradata/sydb/tbs03.dbf         3161898 2015-04-13 20:46:37 RECOVER    8192     8 /tmp/tbs_tmp.dbf                3163049 2015-04-13 21:11:16 ONLINE    8192

控制文件的信息没有被修改也无法通过BBED修改,所以此时无法恢复data file 7;

SQL> alter database datafile 7 online;alter database datafile 7 online*ERROR at line 1:ORA-01113: file 7 needs media recoveryORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf'SQL> recover datafile 7;ORA-00283: recovery session canceled due to errorsORA-01122: database file 7 failed verification checkORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf'ORA-01207: file is more recent than control file - old control file

通过重建控制文件恢复数据文件7:

SQL> alter database backup controlfile to trace;SQL> shutdown immediateSQL>STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "SYDB" NORESETLOGS ARCHIVELOG  MAXLOGFILES 16  MAXLOGMEMBERS 3  MAXDATAFILES 100  MAXINSTANCES 8  MAXLOGHISTORY 292LOGFILE GROUP 1 '/u01/app/oradata/sydb/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oradata/sydb/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oradata/sydb/redo03.log' SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE '/u01/app/oradata/sydb/system01.dbf', '/u01/app/oradata/sydb/sysaux01.dbf', '/u01/app/oradata/sydb/undotbs01.dbf', '/u01/app/oradata/sydb/users01.dbf', '/disk2/oradata/sydb/tbs01.dbf', '/disk2/oradata/sydb/tbs02.dbf', '/disk2/oradata/sydb/tbs03.dbf', '/tmp/tbs_tmp.dbf'CHARACTER SET UTF8;Control file created.SQL> alter database open;alter database open*ERROR at line 1:ORA-01113: file 7 needs media recoveryORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf''SQL> recover database;Media recovery complete.SQL> alter database open;Database altered.SQL> column name format a35SQL> select file#,name, CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss')checkpoint_time,status,BLOCK_SIZE from v$datafile;   FILE# NAME                CHECKPOINT_CHANGE# CHECKPOINT_TIME   STATUS BLOCK_SIZE---------- ----------------------------------- ------------------ ------------------- ------- ----------     1 /u01/app/oradata/sydb/system01.dbf       3183058 2015-04-13 22:11:12 SYSTEM    8192     2 /u01/app/oradata/sydb/sysaux01.dbf       3183058 2015-04-13 22:11:12 ONLINE    8192     3 /u01/app/oradata/sydb/undotbs01.dbf      3183058 2015-04-13 22:11:12 ONLINE    8192     4 /u01/app/oradata/sydb/users01.dbf       3183058 2015-04-13 22:11:12 ONLINE    8192     5 /disk2/oradata/sydb/tbs01.dbf         3183058 2015-04-13 22:11:12 ONLINE    8192     6 /disk2/oradata/sydb/tbs02.dbf         3183058 2015-04-13 22:11:12 ONLINE    8192     7 /disk2/oradata/sydb/tbs03.dbf         3183058 2015-04-13 22:11:12 ONLINE    8192     8 /tmp/tbs_tmp.dbf                3183058 2015-04-13 22:11:12 ONLINE    8192

注:不同情况,有可能还是无法打开数据库,比如报(ORA-01113: file 1 needs media recovery),尝试使用NORESETLOGS方式重建控制文件,然后在执行Media Recovery 。

--Then end (2015-04-13)