场景描述:
数据库利用备份进行恢复时,需要执行restore+recover,restore利用备份片恢复数据文件,recover是进行归档日志应用到最新状态,当我们所有归档日志丢失时,可以利用bbed跳过所有的归档日志,将数据库open起来,在生产环境中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 8
Next log sequence to archive 10
Current log sequence 10
SQL> create tablespace test_bbed datafile '/oracle/app/oradata/prod/bbed01.dbf' size 50M;
Tablespace created.
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.
RMAN> backup datafile 6 format '/home/oracle/backup/datafile6_%U';
Starting backup at 31-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 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 31-JUL-20
channel ORA_DISK_1: finished piece 1 at 31-JUL-20
piece handle=/home/oracle/backup/datafile8_01v6n1e7_1_1 tag=TAG20200731T181951 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JUL-20
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 8
Next log sequence to archive 10
Current log sequence 10
多次切换日志:
再次查看
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 15
Next log sequence to archive 17
Current log sequence 17
删除归档14 15:(也可以全部删除,因为我们是跳过所有归档进行恢复)
rm -rf arch1_14_1046156085.dbf arch1_15_1046156085.dbf
删除数据文件
[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
开始恢复:
RMAN> restore datafile 6;
RMAN> recover datafile 6;
Starting recover at 08-OCT-20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_10_1046156085.dbf
archived log for thread 1 with sequence 11 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_11_1046156085.dbf
archived log for thread 1 with sequence 12 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_12_1046156085.dbf
archived log for thread 1 with sequence 13 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_13_1046156085.dbf
archived log for thread 1 with sequence 16 is already on disk as file /oracle/app/product/11.2.0/db_1/dbs/arch1_16_1046156085.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/08/2020 23:40:44
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 1115761 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 14 and starting SCN of 1115758 found to restore
这里只是归档断档,我不想后续继续应用16号归档,跳过所有归档日志。
查看SCN信息:
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1115769
2 1115769
3 1115769
4 1115769
5 1115769
6 1115732
6 rows selected.
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1115769
2 1115769
3 1115769
4 1115769
5 1115769
6 1115769
6号数据文件 SCN1115732 -> 1115769 直接推进到与其他数据文件一致
bbed有关一致性的偏移量介绍:
(1)kscnbas (at offset 484) - SCN of last change to the datafile.
(2)kcvcptim (at offset 492) -Time of the last change to the datafile.
(3)kcvfhcpc (at offset 140) - Checkpoint count.
(4)kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致,如果都一致,可以正常操作,如果不一致,那么会报ORA-01113错误。
所以这里我们不仅修改484,我们还要修改492 140 148.
我们将6号文件的484 492 140 148全部修改与1号文件一致
bbed连接1号文件进行查看1号文件信息:
BBED> set filename '/oracle/app/oradata/prod/system01.dbf'
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00110679
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3ec81979
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000011
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> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000074
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000073
1号文件实际存储的值为:
484 0x00110679 -> 79061100
492 0x3ec81979-> 7919c83e
140 0x00000074-> 74000000
148 0x00000073 -> 73000000
bbed连接目标6号文件,进行修改:
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 0x00110654
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3ec8194c
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000a
ub4 kcrbabno @504 0x0000a894
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
现将6号数据文件偏移量484 492 500 140 148全部修改与1号文件一致
BBED> modify /x 79061100 offset 484
BBED> modify /x 7919c83e offset 492
BBED> modify /x 74000000 offset 140
BBED> modify /x 73000000 offset 148
修改后:
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00110679
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3ec81979
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000a
ub4 kcrbabno @504 0x0000a894
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> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000074
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000073
BBED> sum apply
Check value for File 0, Block 1:
current = 0xab75, required = 0xab75
再次查看SCN信息:
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1115769
2 1115769
3 1115769
4 1115769
5 1115769
6 1115769
6 rows selected.
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1115769
2 1115769
3 1115769
4 1115769
5 1115769
6 1115769
尝试open打开数据库:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/oracle/app/oradata/prod/bbed01.dbf'
ORA-01207: file is more recent than control file - old control file
重建控制文件:
SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt';
Database altered.
[oracle@server1 ~]$ vim ctl.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/app/oradata/prod/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oracle/app/oradata/prod/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/app/oradata/prod/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/app/oradata/prod/system01.dbf',
'/oracle/app/oradata/prod/sysaux01.dbf',
'/oracle/app/oradata/prod/undotbs01.dbf',
'/oracle/app/oradata/prod/users01.dbf',
'/oracle/app/oradata/prod/example01.dbf',
'/oracle/app/oradata/prod/bbed01.dbf'
CHARACTER SET ZHS16GBK
SQL> shutdown immediate;
SQL> @ctl.sql
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/app/oradata/prod/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
数据库成功启动。
SQL> select count(*) from t1;
COUNT(*)
----------
173914
恢复成功
本次修改了offset 484 492 140 148
其他有关bbed的案例: