Oracle BBED单个数据文件跳过丢失的归档继续恢复(一)

最新推荐文章于 2023-03-25 11:59:55 发布

DBhanG

最新推荐文章于 2023-03-25 11:59:55 发布

阅读量422

点赞数

文章标签：数据库 oracle bbed linux

本文链接：https://blog.csdn.net/baoyuhang0/article/details/108980465

版权

场景描述：

数据库利用备份进行恢复时，需要执行restore+recover，restore利用备份片恢复数据文件，recover是进行归档日志应用到最新状态，当我们归档日志断档丢失时，可以利用bbed跳过丢失的归档日志，继续恢复下去，在生产环境中bbed是极其危险的操作，谨慎使用，本次测试是单个数据文件测试。

环境构建：

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/app/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         25 INACTIVE
         2         26 CURRENT    //当前日志组为26
         3         24 INACTIVE


创建测试表空间：
SQL> create tablespace test_bbed datafile '/oracle/app/oradata/prod/bbed01.dbf' size 50M;
Tablespace created.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.
SQL> /
System altered.

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         28 INACTIVE
         2         29 CURRENT  //当前日志组
         3         27 INACTIVE

备份数据文件bbed01.dbf
RMAN> backup datafile 8;
Starting backup at 06-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/oracle/app/oradata/prod/bbed01.dbf
channel ORA_DISK_1: starting piece 1 at 06-OCT-20
channel ORA_DISK_1: finished piece 1 at 06-OCT-20
piece handle=/oracle/app/product/11.2.0/db_1/dbs/01vcapgi_1_1 tag=TAG20201006T222834 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-20


SQL> create table t1 tablespace test_bbed as select * from dba_objects;
Table created.

SQL> insert into t1 select * from t1;

86957 rows created.

SQL> commit;

Commit complete.

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         28 INACTIVE
         2         29 CURRENT   t1表创建记录存在29号日志中
         3         27 INACTIVE
         
SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint;
System altered.

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         28 INACTIVE
         2         29 INACTIVE
         3         30 CURRENT

创建t2测试表：
SQL> create table t2 tablespace test_bbed as select * from dba_objects;
Table created.

SQL> insert into t2 select * from t2;

86957 rows created.

SQL> commit;

Commit complete.

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         28 INACTIVE
         2         29 INACTIVE
         3         30 CURRENT  t2记录存在30号日志组中
         
多次进行日志切换：
SQL> alter system switch logfile;

删除29号归档日志
将数据文件8号删除
使用旧的备份片进行恢复数据文件8

删除29号归档日志：
SQL> select name from v$archived_log where sequence#=29;
NAME
--------------------------------------------------------------------------------
/oracle/app/product/11.2.0/db_1/dbs/arch1_29_1046156085.dbf

[oracle@server1 dbs]$ mv arch1_29_1046156085.dbf /tmp/

删除8号数据文件：
[oracle@server1 prod]$ mv bbed01.dbf /tmp/


SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from t1;
select count(*) from t1
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/oracle/app/oradata/prod/bbed01.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.

执行restore恢复

[oracle@server1 prod]$ rman target /
RMAN> startup mount;
RMAN> restore datafile 8;
Starting restore at 06-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /oracle/app/oradata/prod/bbed01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/01vcapgi_1_1
channel ORA_DISK_1: piece handle=/oracle/app/product/11.2.0/db_1/dbs/01vcapgi_1_1 tag=TAG20201006T222834
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-OCT-20

执行recover：
RMAN> recover datafile  8;
Starting recover at 06-OCT-20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 30 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_30_1046156085.dbf
archived log for thread 1 with sequence 31 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_31_1046156085.dbf
archived log for thread 1 with sequence 32 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_32_1046156085.dbf
archived log for thread 1 with sequence 33 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_33_1046156085.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/06/2020 22:35:37
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 29 and starting SCN of 1140299 found to restore
//找不到29号归档日志

查看scn记录信息：
SQL> select file#,checkpoint_change# from v$datafile_header; 

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1140640
         2            1140640
         3            1140640
         4            1140640
         5            1140640
         6            1140640
         7            1140640
         8            1140337


SQL> select file#,checkpoint_change#,last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1140640
         2            1140640
         3            1140640
         4            1140640
         5            1140640
         6            1140640
         7            1140640
         8            1140640



SQL> select FIRST_CHANGE#,NEXT_CHANGE#,sequence# from v$archived_log where sequence#=29 or sequence#=30;
FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
------------- ------------ ----------
      1140299      1140440         29
      1140440      1140551         30

将8号数据文件头部的SCN修改为1140440跳过29号归档日志，让Oracle以为29号归档日志已经应用完成。
继续恢复30号归档日志以及之后的归档日志

BBED修改484偏移量：

BBED> set filename '/oracle/app/oradata/prod/bbed01.dbf'
        FILENAME        /oracle/app/oradata/prod/bbed01.dbf

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x00116671
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x3ec56612
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x0000001d
         ub4 kcrbabno                       @504      0x00000004
         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查看当前SCN：
SQL> select to_number('00116671','xxxxxxxxxxx') from dual;
TO_NUMBER('00116671','XXXXXXXXXXX')
-----------------------------------
                            1140337

需要将其修改为1140440
转换16进制：
SQL> select to_char('1140440','xxxxxxxxxxx') from dual;
TO_CHAR('114
------------
      1166d8

对于little-endian的format，他存储是先存储低位的，因此实际block 存储的是：d8661100

这里我们只修改484
查看484：
BBED> dump /v offset 484 count 32
 File: /oracle/app/oradata/prod/bbed01.dbf (0)
 Block: 1       Offsets:  484 to  515  Dba:0x00000000
-------------------------------------------------------
 71661100 00000000 1266c53e 01000000 l qf.......fÅ>....
 1d000000 04000000 10000000 02000000 l ................

 我们需要将71661100修改为d8661100，这里只修改前2位即可
BBED> set mode edit
        MODE            Edit

BBED> modify /x d8
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oracle/app/oradata/prod/bbed01.dbf (0)
 Block: 1                Offsets:  484 to  515           Dba:0x00000000
------------------------------------------------------------------------
 d8661100 00000000 1266c53e 01000000 1d000000 04000000 10000000 02000000 
 <32 bytes per line>

应用保存：
BBED> sum apply
Check value for File 0, Block 1:
current = 0x797e, required = 0x797e

再次查看数据文件头部SCN信息：
SQL> select file#,checkpoint_change# from v$datafile_header; 
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1140640
         2            1140640
         3            1140640
         4            1140640
         5            1140640
         6            1140640
         7            1140640
         8            1140440   //已经修改成功

执行recover：

RMAN> recover database;  
Starting recover at 07-OCT-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
starting media recovery
archived log for thread 1 with sequence 30 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_30_1046156085.dbf
archived log for thread 1 with sequence 31 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_31_1046156085.dbf
archived log for thread 1 with sequence 32 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_32_1046156085.dbf
archived log for thread 1 with sequence 33 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_33_1046156085.dbf
unable to find archived log
archived log thread=1 sequence=29
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2020 23:25:47
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29 and starting SCN of 1140440

//这里还是需要寻找29号归档日志。
然后我们修改offset 500  RBA为30，这里认为数据库应用归档是根据RBA作为起始应用的归档日志号

BBED修改500偏移量：

尝试将500改为30：
SQL> select to_char('30','xxxxxxxxx') from dual;
TO_CHAR('3
----------
        1e
实际修改为1e000000

BBED> set filename '/oracle/app/oradata/prod/bbed01.dbf'
        FILENAME        /oracle/app/oradata/prod/bbed01.dbf

BBED> dump /v offset 500 count 32  
 File: /oracle/app/oradata/prod/bbed01.dbf (0)
 Block: 1       Offsets:  500 to  531  Dba:0x00000000
-------------------------------------------------------
 22000000 04000000 10000000 02000000 l "...............
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

BBED> set mode edit
        MODE            Edit

BBED> modify /x 1e
 File: /oracle/app/oradata/prod/bbed01.dbf (0)
 Block: 1                Offsets:  500 to  531           Dba:0x00000000
------------------------------------------------------------------------
 1e000000 04000000 10000000 02000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

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

再次尝试执行recover：

RMAN> recover datafile 8;
Starting recover at 07-OCT-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
starting media recovery
archived log for thread 1 with sequence 30 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_30_1046156085.dbf
archived log for thread 1 with sequence 31 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_31_1046156085.dbf
archived log for thread 1 with sequence 32 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_32_1046156085.dbf
archived log for thread 1 with sequence 33 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_33_1046156085.dbf
archived log file name=/oracle/app/product/11.2.0/db_1/dbs/arch1_30_1046156085.dbf thread=1 sequence=30
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2020 23:53:56
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oracle/app/product/11.2.0/db_1/dbs/arch1_30_1046156085.dbf'
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 4 change 6601364733960 time 10/06/2020 22:28:34
ORA-00334: archived log: '/oracle/app/product/11.2.0/db_1/dbs/arch1_30_1046156085.dbf'
//目前确实在寻找30号归档日志，但是出现未知错误

这里我们尝试先将该数据文件离线，把库拉起来。

SQL> alter database datafile 8 offline;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter database datafile 8 online;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/oracle/app/oradata/prod/bbed01.dbf'


SQL> recover datafile 8;
ORA-00279: change 1140440 generated at 10/06/2020 22:28:34 needed for thread 1
ORA-00289: suggestion :
/oracle/app/product/11.2.0/db_1/dbs/arch1_30_1046156085.dbf
ORA-00280: change 1140440 for thread 1 is in sequence #30

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
//不知道为什么只应用了30号归档。 31 ，32 ，33 却没有应用

SQL> alter database datafile 8 online;
Database altered.

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-08103: object no longer exists


SQL> select count(*) from t2;
select count(*) from t2
                     *
ERROR at line 1:
ORA-08103: object no longer exists

结论：
在我自己的测试中，本次只修改了offset 484 以及 offset500
库是拉起来了可以看出我们跳过了29号归档日志，应用了30号日志，但是即使我们应用了30号归档，但是数据还是没了，而且30号以后的归档日志并没有应用。
看网上的恢复有些修改了504 508，导致后续归档能够成功应用。、

尝试同时修改484 500 504 508，还是存在问题
Oracle BBED单个数据文件跳过丢失的归档继续恢复(二)

其他有关bbed的案例：

Oracle BBED工具介绍与安装

Oracle BBED利用copy命令恢复已删除的记录

Oracle BBED修改数据块进而修改数据

Oracle BBED全库跳过丢失的归档继续恢复

Oracle BBED单个数据文件跳过丢失的归档继续恢复

Oracle BBED单个数据文件跳过所有归档恢复

Oracle BBED将offline的数据文件改为online案例

DBhanG

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle BBED单个数据文件跳过丢失的归档继续恢复(一)

场景描述：      数据库利用备份进行恢复时，需要执行restore+recover，restore利用备份片恢复数据文件，recover是进行归档日志应用到最新状态，当我们归档日志断档丢失时，可以利用bbed跳过丢失的归档日志，继续恢复下去，在生产环境中bbed是极其危险的操作，谨慎使用，本次测试是单个数据文件测试。环境构建：SQL> archive log list;Database log mode
复制链接

扫一扫