Oracle 之利用BBED跳过归档日志实现恢复（二）

电玩女神 2022-07-12 11:28 275阅读 0赞

本次实验和之前做的实验：[Oracle 之利用BBED修改数据块SCN----极端环境下的数据恢复（一）][Oracle _BBED_SCN----]差不多，本次实验将删除归档进行恢复

http://blog.csdn.net/shiyu1157758655/article/details/56286018

使用BBED跳过丢失的归档，在recover datafile的过程当中如果丢失了需要的归档将使得recover无法进行，使用bbed工具可以跳过丢失的归档进行recover datafile。

**1.测试环境**

OS:RedHat 5.9

Oracle:12.1.0.2

**2.模拟实验环境**

**a）物理备份**

ocrl:/data/oradata/ocrl/archivelog@oracle1>cd /data/oradata/ocrl/datafile/  
ocrl:/data/oradata/ocrl/datafile@oracle1>ls  
OCRL  sysaux01.dbf  system01.dbf  system01.dbf.bak  test.dbf  tstest.dbf  undotbs01.dbf  users01.dbf  
ocrl:/data/oradata/ocrl/datafile@oracle1>cp test.dbf test.dbf.bak

SQL> alter system switch logfile;//切日志产生归档  
  
  
System altered.  
  
  
SQL> /  
  
  
System altered.  
  
  
SQL> /  
  
  
System altered.  
  
  
SQL> /  
  
  
System altered.  
  
  
SQL> /  
  
  
System altered.  
  
  
SQL> /  
  
  
System altered.

SQL> select \* from v$log;//查看序列号  
  
  
    GROUP\#    THREAD\#  SEQUENCE\#      BYTES  BLOCKSIZE  MEMBERS ARC  
\---------- ---------- ---------- ---------- ---------- ---------- ---  
STATUS FIRST\_CHANGE\# FIRST\_TIM NEXT\_CHANGE\# NEXT\_TIME     CON\_ID  
\---------------- ------------- --------- ------------ --------- ----------  
 1    1     226   52428800   5121 NO  
CURRENT       13300023 22-FEB-17   2.8147E+14 0  
  
  
 2    1     224   52428800   5121 YES  
INACTIVE      13296997 22-FEB-17     13297004 22-FEB-17  0  
  
  
 3    1     225   52428800   5121 YES  
INACTIVE      13297004 22-FEB-17     13300023 22-FEB-17  0  
  
![Image 1][]  
  
SQL> archive log list;  
Database log mode      Archive Mode  
Automatic archival      Enabled  
Archive destination      /data/oradata/ocrl/archivelog  
Oldest online log sequence     231  
Next log sequence to archive   233  
Current log sequence      233  
SQL> exit

**b）删除数据文件和归档文件**

ocrl:/data/oradata/ocrl/archivelog@oracle1>ll -tr  
total 24120  
\-rw-r----- 1 oracle dba 14556672 Feb 22 11:04 1\_223\_936091272.arc  
\-rw-r----- 1 oracle dba     4096 Feb 22 11:04 1\_224\_936091272.arc  
\-rw-r----- 1 oracle dba  1226752 Feb 22 11:23 1\_225\_936091272.arc  
\-rw-r----- 1 oracle dba  8521216 Feb 22 11:42 1\_226\_936091272.arc  
\-rw-r----- 1 oracle dba     2048 Feb 22 11:42 1\_227\_936091272.arc  
\-rw-r----- 1 oracle dba     3072 Feb 22 11:42 1\_228\_936091272.arc  
\-rw-r----- 1 oracle dba     2048 Feb 22 11:42 1\_229\_936091272.arc  
\-rw-r----- 1 oracle dba     1536 Feb 22 11:42 1\_230\_936091272.arc  
\-rw-r----- 1 oracle dba     1536 Feb 22 11:42 1\_232\_936091272.arc  
\-rw-r----- 1 oracle dba     1536 Feb 22 11:42 1\_231\_936091272.arc  
\-rw-r----- 1 oracle dba   278016 Feb 22 11:45 1\_233\_936091272.arc  
\-rw-r----- 1 oracle dba     1536 Feb 22 11:45 1\_234\_936091272.arc  
\-rw-r----- 1 oracle dba     1024 Feb 22 11:46 1\_236\_936091272.arc  
\-rw-r----- 1 oracle dba     4096 Feb 22 11:46 1\_235\_936091272.arc  
\-rw-r----- 1 oracle dba    13824 Feb 22 11:46 1\_237\_936091272.arc  
\-rw-r----- 1 oracle dba     4608 Feb 22 11:46 1\_238\_936091272.arc  
ocrl:/data/oradata/ocrl/archivelog@oracle1>rm -rf \*  //删除所有归档  
ocrl:/data/oradata/ocrl/archivelog@oracle1>cd /data/oradata/ocrl/datafile/  
ocrl:/data/oradata/ocrl/datafile@oracle1>ls  
OCRL  sysaux01.dbf  system01.dbf  system01.dbf.bak  test.dbf  test.dbf.bak  tstest.dbf  undotbs01.dbf  users01.dbf  
ocrl:/data/oradata/ocrl/datafile@oracle1>rm -rf test.dbf  //删除test数据文件  
ocrl:/data/oradata/ocrl/datafile@oracle1>ls  
OCRL  sysaux01.dbf  system01.dbf  system01.dbf.bak  test.dbf.bak  tstest.dbf  undotbs01.dbf  users01.dbf

**查询test上的table**

SQL> conn test/test  
Connected.  
  
SQL> select table\_name from user\_tables;  
  
  
TABLE\_NAME  
\--------------------------------------------------------------------------------  
T\_BITMAP  
T\_BTREE  
GROUP\_TEST  
FT\_1  
JUST  
AISHU1  
  
  
6 rows selected.  
  
SQL> select \* from just;  
select \* from just  
              \*  
ERROR at line 1:  
ORA-01116: error in opening database file 5  
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'  
ORA-27041: unable to open file  
Linux-x86\_64 Error: 2: No such file or directory  
Additional information: 3

这里已经发现test的数据文件已经损坏

SQL> shutdown immediate   //这里正常关闭报错  
ORA-01116: error in opening database file 5  
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'  
ORA-27041: unable to open file  
Linux-x86\_64 Error: 2: No such file or directory  
Additional information: 3  
  
SQL> shutdown abort; //强制关闭  
ORACLE instance shut down.  
SQL> startup  //打开数据库  
ORACLE instance started.  
  
  
Total System Global Area  754974720 bytes  
Fixed Size   2928968 bytes  
Variable Size 524291768 bytes  
Database Buffers 222298112 bytes  
Redo Buffers   5455872 bytes  
Database mounted.  
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file  
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'     //这里缺少test数据文件  
  
  
SQL> select status from v$instance;  
  
  
STATUS  
\------------  
MOUNTED  
  
  
SQL> select \* from v$recover\_file;//查看需要恢复的文件  
  
  
FILE\# ONLINE  ONLINE\_STATUS ERROR    CHANGE\#  TIME  CON\_ID    
\------------------------------------------------------------------------------------------------  
5     ONLINE  ONLINE        FILE NOT FOUND           0                    0  
  
**把原来的数据文件备份回来**  
ocrl:/data/oradata/ocrl/datafile@oracle1>mv test.dbf.bak test.dbf

SQL> alter database open;  
alter database open  
\*  
ERROR at line 1:  
ORA-01113: file 5 needs media recovery  
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'  
  
  
SQL> recover datafile 5; //恢复  
ORA-00279: change 13303047 generated at 02/22/2017 11:42:56 needed for thread 1  
ORA-00289: suggestion : /data/oradata/ocrl/archivelog/1\_233\_936091272.arc  
ORA-00280: change 13303047 for thread 1 is in sequence \#233  
  
  
Specify log: \{<RET>=suggested | filename | AUTO | CANCEL\}  
auto   \--自动模式让他去选择  
ORA-00308: cannot open archived log  
'/data/oradata/ocrl/archivelog/1\_233\_936091272.arc'   
ORA-27037: unable to obtain file status  
Linux-x86\_64 Error: 2: No such file or directory  
Additional information: 3  
  
  
ORA-00308: cannot open archived log  
'/data/oradata/ocrl/archivelog/1\_233\_936091272.arc'  //需要seq为233的归档 此文件已经被我们删除  
ORA-27037: unable to obtain file status  
Linux-x86\_64 Error: 2: No such file or directory  
Additional information: 3

**3.利用BBED跳过归档进行恢复**

**a）查看数据头文件scn**

SQL> select status from v$instance;  
  
  
STATUS  
\------------  
MOUNTED  
  
SQL> col name for a50   
SQL>  select name,checkpoint\_change\# from v$datafile\_header;//查看数据文件头scn  
  
  
NAME  CHECKPOINT\_CHANGE\#  
\-------------------------------------------------- ------------------  
/data/oradata/ocrl/datafile/system01.dbf    13304100  
/data/oradata/ocrl/datafile/sysaux01.dbf    13304100  
/data/oradata/ocrl/datafile/undotbs01.dbf    13304100  
/data/oradata/ocrl/datafile/users01.dbf              13304100  
/data/oradata/ocrl/datafile/test.dbf             13303047  
/data/oradata/ocrl/datafile/tstest.dbf    13304100  
  
  
6 rows selected.  
  
  
SQL> select to\_char('13303047','XXXXXXXX') from dual;//将5号文件scn 转成16进制值  
  
  
TO\_CHAR('  
\---------  
   CAFD07  
  
**b）BBED修改**

**用BBED查询test数据文件结构信息**  
BBED> set dba 5,1  
DBA            0x01400001 (20971521 5,1)  
  
  
BBED> map  
 File: /data/oradata/ocrl/datafile/test.dbf (5)  
 Block: 1                                     Dba:0x01400001  
\------------------------------------------------------------  
 Data File Header  
  
  
 struct kcvfh, 1112 bytes                   @0         
  
  
 ub4 tailchk                                @8188      
  
  
**BBED查看kcvfh信息**  
  
BBED> p kcvfhckp  
struct kcvfhckp, 36 bytes                   @484       
   struct kcvcpscn, 8 bytes                 @484       
      ub4 kscnbas                           @484      0x00cafd07  
      ub2 kscnwrp                           @488      0x0000  
   ub4 kcvcptim                             @492      0x37d3a8c0  
   ub2 kcvcpthr                             @496      0x0001  
   union u, 12 bytes                        @500       
      struct kcvcprba, 12 bytes             @500       
         ub4 kcrbaseq                       @500      0x000000e9  
         ub4 kcrbabno                       @504      0x00000002  
         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> d /v dba 5,1 offset 484     / /查看5号数据头文件的scn  
 File: /data/oradata/ocrl/datafile/test.dbf (5)  
 Block: 1       Offsets:  484 to  995  Dba:0x01400001  
\-------------------------------------------------------  
 07fdca00 00000000 c0a8d337 01001e82 l .?.....括?....  
 e9000000 02000000 10000000 02000000 l ?..............  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 0d000d00 0d000100 00000000 00000000 l ................  
 00000000 02004001 d9e63e00 00000000 l ......@.冁>.....  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
  
  
 <16 bytes per line>

**查看正常数据文件头**

BBED> d /v dba 1,1 offset 484    //1号数据文件  
 File: /data/oradata/ocrl/datafile/system01.dbf (1)  
 Block: 1       Offsets:  484 to  995  Dba:0x00400001  
\-------------------------------------------------------  
 2401cb00 00000000 39aad337 01000000 l $.?....9?7....  
 ef000000 b5000000 100072f0 02000000 l ?..?....r?...  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 0d000d00 0d000100 00000000 00000000 l ................  
 00000000 02004000 6c3d9200 00000000 l ......@.l=......  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 0215e11b l ..............?  
 fb1ef7c6 d7131af0 7f44f1f4 59000000 l ?髌?.?D耵Y...  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 0013b9e3 l ..............广  
 13aac34f aebfb3af e5f7d22b 03000600 l .?O?朝鬻?....  
 f3b89100 00000000 00000000 00000000 l 蟾..............  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
  
  
 <16 bytes per line>  
  
  
BBED> d /v dba 2,1 offset 484  //2号数据文件  
 File: /data/oradata/ocrl/datafile/sysaux01.dbf (2)  
 Block: 1       Offsets:  484 to  995  Dba:0x00800001  
\-------------------------------------------------------  
 2401cb00 00000000 39aad337 01000000 l $.?....9?7....  
 ef000000 b5000000 100072f0 02000000 l ?..?....r?...  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 0d000d00 0d000100 00000000 00000000 l ................  
 00000000 02008000 75ba9700 00000000 l ........u?.....  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 0215e11b l ..............?  
 fb1ef7c6 d7131af0 7f44f1f4 59000000 l ?髌?.?D耵Y...  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 0013b9e3 l ..............广  
 13aac34f aebfb3af e5f7d22b 03000600 l .?O?朝鬻?....  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
  
  
 <16 bytes per line>

BBED> set mode edit  
MODE           Edit  
  
  
BBED> modify /x 2401cb dba 5,1 offset 484   //修改5号文件的数据文件头scn一致  
 File: /data/oradata/ocrl/datafile/test.dbf (5)  
 Block: 1                Offsets:  484 to  995           Dba:0x01400001  
\------------------------------------------------------------------------  
 2401cb00 00000000 c0a8d337 01001e82 e9000000 02000000 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 02004001 d9e63e00 00000000   
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   
 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> sum apply  
Check value for File 5, Block 1:  
current = 0xe558, required = 0xe558  
  
  
BBED> d /v  dba 5,1 offset 484   //查看修改后的test的头文件  
 File: /data/oradata/ocrl/datafile/test.dbf (5)  
 Block: 1       Offsets:  484 to  995  Dba:0x01400001  
\-------------------------------------------------------  
 2401cb00 00000000 c0a8d337 01001e82 l $.?....括?....  
 e9000000 02000000 10000000 02000000 l ?..............  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 0d000d00 0d000100 00000000 00000000 l ................  
 00000000 02004001 d9e63e00 00000000 l ......@.冁>.....  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
 00000000 00000000 00000000 00000000 l ................  
  
  
 <16 bytes per line>

**c）打开数据库**

SQL> startup force  
ORACLE instance started.  
  
  
Total System Global Area  754974720 bytes  
Fixed Size   2928968 bytes  
Variable Size 524291768 bytes  
Database Buffers 222298112 bytes  
Redo Buffers   5455872 bytes  
Database mounted.  
ORA-01113: file 5 needs media recovery  
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'  
  
SQL> recover database;  
Media recovery complete.  
SQL> alter database open;  
  
  
Database altered**.**

[Oracle _BBED_SCN----]: http://blog.csdn.net/shiyu1157758655/article/details/56286018
[Image 1]: