1.实验环境
OS:Centos7.9
DB:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
2.实验说明
在Oracle数据库的恢复过程中，如果遇到归档日志丢失的情况，可以使用BBED来跳过这些缺失的归档日志，从而继续恢复过程。这个过程涉及到修改数据库的SCN(系统更改号)和RBA(重做日志文件中的位置)值，使其指向缺失归档日志之后的下一个有效归档日志中的记录，从而让数据库忽略丢失的归档日志，继续恢复到最新的状态。
虽然通过BBED跳过缺失的归档日志可以让数据库继续进行恢复，但这种方法并不是没有风险，测试结果显示，即使跳过了缺失的归档日志，数据库仍然可能存在数据丢失的风险，因此在实际生产环境中一定要定期做好备份工作。
4.实验内容
4.1数据库开启归档模式
--配置归档目录
SYS@EVA>alter system set log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/eva';
--关闭数据库
SYS@EVA>shut immediate
--打开数据库到mount状态
SYS@EVA>startup mount
--开启归档模式
SYS@EVA>alter database archivelog;
--打开数据库
SYS@EVA>alter database open;
--查看归档模式
SYS@EVA>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/fast_recovery_area/eva
Oldest online log sequence 44
Next log sequence to archive 46
Current log sequence 46
4.2创建测试数据
--为了方便试验我们新建一个表空间进行测试
SYS@EVA>create tablespace tbs1 datafile '/u01/app/oracle/oradata/EVA/tbs1.dbf' size 10m;
--修改scott默认表空间
SYS@EVA>alter user scott default tablespace tbs1;
--创建测试表
SCOTT@EVA>create table tb01 (id int,value varchar2(10));
--随便插入一些数据
SCOTT@EVA>insert into tb01 values(1,'ABCDE');
SCOTT@EVA>insert into tb01 values(2,'abcde');
SCOTT@EVA>commit;
--切换日志
SCOTT@EVA>alter system switch logfile;
--数据自增一次
SCOTT@EVA>insert into tb01 select * from tb01;
--切换日志
SCOTT@EVA>alter system switch logfile;
--数据自增一次
SCOTT@EVA>insert into tb01 select * from tb01;
--切换日志
SCOTT@EVA>alter system switch logfile;
--数据自增一次
SCOTT@EVA>insert into tb01 select * from tb01;
--切换日志
SCOTT@EVA>alter system switch logfile;
--当前表中数据
SCOTT@EVA>select count(*) from tb01;
COUNT(*)
----------
16
4.3RMAN备份数据文件
--查看数据位于几号文件
SCOTT@EVA>select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/EVA/system01.dbf
2 /u01/app/oracle/oradata/EVA/sysaux01.dbf
3 /u01/app/oracle/oradata/EVA/undotbs01.dbf
4 /u01/app/oracle/oradata/EVA/users01.dbf
5 /u01/app/oracle/oradata/EVA/tbs1.dbf
--RMAN备份5号文件
[oracle@oracledb ~]$ rman target /
RMAN> backup datafile 5;
Starting backup at 12-JUL-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/EVA/tbs1.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-24
channel ORA_DISK_1: finished piece 1 at 12-JUL-24
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/0a2vnqr5_1_1 tag=TAG20240712T132140 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-JUL-24
RMAN> list backup of datafile 5;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 1.11M DISK 00:00:00 12-JUL-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240712T132140
Piece Name: /u01/app/oracle/product/12.2.0/dbhome_1/dbs/0a2vnqr5_1_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2115040 12-JUL-24 NO /u01/app/oracle/oradata/EVA/tbs1.dbf
4.4RMAN备份后再插入一些数据
--当前的归档日志序列号
SCOTT@EVA>select SEQUENCE# from v$archived_log;
SEQUENCE#
----------
60
61
62
63
--插入数据切换日志
SCOTT@EVA>insert into tb01 select * from tb01;
SCOTT@EVA>commit;
SCOTT@EVA>alter system switch logfile;
SCOTT@EVA>insert into tb01 select * from tb01;
SCOTT@EVA>commit;
SCOTT@EVA>alter system switch logfile;
SCOTT@EVA>insert into tb01 select * from tb01;
SCOTT@EVA>commit;
SCOTT@EVA>alter system switch logfile;
SCOTT@EVA>select count(*) from tb01;
COUNT(*)
----------
128
--新增数据后的归档日志序列号
SCOTT@EVA>select SEQUENCE# from v$archived_log;
SEQUENCE#
----------
60
61
62
63
64
65
66
4.5模拟归档日志丢失
[oracle@oracledb eva]$ ll -rht
total 47M
-rw-r----- 1 oracle oinstall 47M Jul 12 13:17 1_60_1173349032.dbf
-rw-r----- 1 oracle oinstall 5.5K Jul 12 13:17 1_61_1173349032.dbf
-rw-r----- 1 oracle oinstall 4.5K Jul 12 13:18 1_62_1173349032.dbf
-rw-r----- 1 oracle oinstall 3.0K Jul 12 13:18 1_63_1173349032.dbf
-rw-r----- 1 oracle oinstall 119K Jul 12 13:26 1_64_1173349032.dbf
-rw-r----- 1 oracle oinstall 3.5K Jul 12 13:26 1_65_1173349032.dbf
-rw-r----- 1 oracle oinstall 6.5K Jul 12 13:28 1_66_1173349032.dbf
--删除63 64号归档
[oracle@oracledb eva]$ rm -fr 1_63_1173349032.dbf
[oracle@oracledb eva]$ rm -fr 1_64_1173349032.dbf
[oracle@oracledb eva]$ ll -rht
total 47M
-rw-r----- 1 oracle oinstall 47M Jul 12 13:17 1_60_1173349032.dbf
-rw-r----- 1 oracle oinstall 5.5K Jul 12 13:17 1_61_1173349032.dbf
-rw-r----- 1 oracle oinstall 4.5K Jul 12 13:18 1_62_1173349032.dbf
-rw-r----- 1 oracle oinstall 3.5K Jul 12 13:26 1_65_1173349032.dbf
-rw-r----- 1 oracle oinstall 6.5K Jul 12 13:28 1_66_1173349032.dbf
4.6将数据文件offline并对其rman restore
--离线数据文件
SCOTT@EVA>alter database datafile 5 offline;
SCOTT@EVA>select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/EVA/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE
5 /u01/app/oracle/oradata/EVA/tbs1.dbf RECOVER
--删除物理文件
[oracle@oracledb EVA]$ rm -fr /u01/app/oracle/oradata/EVA/tbs1.dbf
--restore数据文件
RMAN> restore datafile 5;
Starting restore at 12-JUL-24
using channel ORA_DISK_1
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 00005 to /u01/app/oracle/oradata/EVA/tbs1.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/0a2vnqr5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/0a2vnqr5_1_1 tag=TAG20240712T132140
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 12-JUL-24
4.7将数据文件online
--online数据文件时提示需要介质恢复
SCOTT@EVA>alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/EVA/tbs1.dbf'
--recover 数据文件,提示64号归档不存在(没有提示63号归档,可见恢复时备份之前的归档是不需要用来recover的)
RMAN> recover datafile 5;
Starting recover at 12-JUL-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 65 is already on disk as file /u01/app/oracle/fast_recovery_area/eva/1_65_1173349032.dbf
archived log for thread 1 with sequence 66 is already on disk as file /u01/app/oracle/fast_recovery_area/eva/1_66_1173349032.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/12/2024 13:32:30
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 64 and starting SCN of 2114778 found to restore
4.8使用BBED修改文件头检查点信息
--从51号归档日志查出介质恢复的检查点并转换为16进制
SYS@EVA>select SEQUENCE# seq#,
to_char(SEQUENCE#,'xxxxxxxxxx') seq16,
FIRST_CHANGE# ckpt#,
to_char(FIRST_CHANGE#,'xxxxxxxxx') ckpt16
from v$archived_log
where SEQUENCE#=65;
SEQ# SEQ16 CKPT# CKPT16
---------- ----------- ---------- ----------
65 41 2115195 20467b
--使用bbed查看5号文件头kcvfhckp信息
[oracle@oracledb ~]$ bbed password=blockedit listfile=/home/oracle/tbs1.list blocksize=8192 mode=edit
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/EVA/tbs1.dbf 1281
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x002045e0 --需要修改的检查点SCN
ub2 kscnwrp @488 0x8000
ub2 kscnwrp2 @490 0x0000
ub4 kcvcptim @492 0x45fbeb65
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000040 --需要修改的日志序列号
ub4 kcrbabno @504 0x000000b9
ub2 kcrbabof @508 0x0010
--修改检查点SCN即kscnbas
BBED> m /x 7b4620 offset 484 --7b4620为前面查询的FIRST_CHANGE#,linux大小端存储,需要两位两位调换
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/EVA/tbs1.dbf (1)
Block: 1 Offsets: 484 to 995 Dba:0x00400001
------------------------------------------------------------------------
7b462000 00800000 65ebfb45 01000000 40000000 b9000000 1000520d 02000000
--修改检查点RBA即kcrbaseq
BBED> m /x 41 offset 500 --41为前面查询的SEQUENCE#日志序列号
File: /u01/app/oracle/oradata/EVA/tbs1.dbf (1)
Block: 1 Offsets: 500 to 1011 Dba:0x00400001
------------------------------------------------------------------------
41000000 b9000000 1000520d 02000000 00000000 00000000 00000000 00000000
--验证修改是否正确
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0020467b --已修改
ub2 kscnwrp @488 0x8000
ub2 kscnwrp2 @490 0x0000
ub4 kcvcptim @492 0x45fbeb65
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000041 --已修改
ub4 kcrbabno @504 0x000000b9
ub2 kcrbabof @508 0x0010
BBED> sum apply
BBED> verify -校验通过
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/EVA/tbs1.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
4.9重新recover数据文件，online数据文件
--recover数据文件
RMAN> recover datafile 5;
Starting recover at 12-JUL-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-JUL-24
--online数据文件
SCOTT@EVA>alter database datafile 5 online;
Database altered.
--查看数据,由于缺失归档日志导致数据有所丢失
SCOTT@EVA>select count(*) from tb01;
COUNT(*)
----------
80
至此，实验完毕，数据存在丢失的情况，因此再次说明下，生产环境一定要做好备份！
最后修改时间：2024-07-12 15:27:22
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
