5 使用BBED跳过归档
在归档模式下，缺失了一部分的归档日志文件，对数据文件进行恢复
1 开启归档
--shutdown immediate --startup mount --alter database archivelog --alter database open SYS@ orcl >archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 258 Next log sequence to archive 260 Current log sequence 260
2 插入测试数据
conn test/test create table t1 (id int,name varchar2(10),date1 date default sysdate); insert into t1(id,name) values(1,'AAAAA'); insert into t1(id,name) values(2,'bbbbb'); commit; SYS@ orcl >/ ID NAME DATE1 ---------- -------------------------------------------------- --------- 1 AAAAA 22-MAY-19 2 bbbbb 22-MAY-19 SYS@ orcl >select * from v$dbfile; FILE# NAME ---------- -------------------------------------------------- 5 /u01/app/oracle/oradata/orcl/test01.dbf 4 /u01/app/oracle/oradata/orcl/users01.dbf 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 1 /u01/app/oracle/oradata/orcl/system01.dbf
3 备份数据文件5
[oracle@DSI ~]$ rman target/ RMAN> backup datafile 5 format '/home/oracle/backup/datafile5_%U';
查看归档
SYS@ orcl >select sequence#,status from v$archived_log order by 1 desc; SEQUENCE# S ---------- - 259 A 258 A 257 A SYS@ orcl >alter system switch logfile; SYS@ orcl >select sequence#,status from v$archived_log order by 1 desc; SEQUENCE# S ---------- - 267 A 266 A 265 A 264 A 263 A 262 A 261 A 260 A 259 A 258 A 257 A
4 删除一部分归档日志
[oracle@DSI arch]$ rm 1_264_1006250831.dbf 1_265_1006250831.dbf SYS@ orcl >select * from v$dbfile; FILE# NAME ---------- -------------------------------------------------- 5 /u01/app/oracle/oradata/orcl/test01.dbf 4 /u01/app/oracle/oradata/orcl/users01.dbf 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 1 /u01/app/oracle/oradata/orcl/system01.dbf SYS@ orcl >select file#,CREATION_CHANGE#,CHECKPOINT_CHANGE# ,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#,STATUS from v$datafile order by 1; FILE# CREATION_CHANGE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS ---------- ---------------- ------------------ --------------------- ------------ --------------- ------- 1 7 9293148 0 0 SYSTEM 2 1799 9293148 0 0 ONLINE 3 2821 9293148 0 0 ONLINE 4 15940 9293148 0 0 ONLINE 5 929531 9293148 0 0 ONLINE archivelog模式下,当数据文件offline时，其对应的数据文件头stop scn会更新，同时controlfile中该datafile 的stop scn信息也会更新.此时也会更新offline scn，并且offline scn等于stop scn.
SYS@ orcl >alter database datafile 5 offline; Database altered. SYS@ orcl >select file#,CREATION_CHANGE#,CHECKPOINT_CHANGE# ,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#,STATUS from v$datafile order by 1; FILE# CREATION_CHANGE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS ---------- ---------------- ------------------ --------------------- ------------ --------------- ------- 1 7 9293148 0 0 SYSTEM 2 1799 9293148 0 0 ONLINE 3 2821 9293148 0 0 ONLINE 4 15940 9293148 0 0 ONLINE 5 929531 9293148 0 9293224 0 RECOVER
对5号文件进行还原
RMAN> restore datafile 5; Starting restore at 22-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=143 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 00005 to /u01/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/datafile5_0iu26hqq_1_1 channel ORA_DISK_1: piece handle=/home/oracle/backup/datafile5_0iu26hqq_1_1 tag=TAG20190522T142946 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 22-MAY-19
5号数据文件无法被Online,提示需要介质恢复
SYS@ orcl >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/orcl/test01.dbf'
对5号文件进行恢复，提示缺少归档日志文件
RMAN> recover datafile 5; Starting recover at 22-MAY-19 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 260 is already on disk as file /u01/app/oracle/arch/1_260_1006250831.dbf archived log for thread 1 with sequence 261 is already on disk as file /u01/app/oracle/arch/1_261_1006250831.dbf archived log for thread 1 with sequence 262 is already on disk as file /u01/app/oracle/arch/1_262_1006250831.dbf archived log for thread 1 with sequence 263 is already on disk as file /u01/app/oracle/arch/1_263_1006250831.dbf archived log for thread 1 with sequence 266 is already on disk as file /u01/app/oracle/arch/1_266_1006250831.dbf archived log for thread 1 with sequence 267 is already on disk as file /u01/app/oracle/arch/1_267_1006250831.dbf RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/22/2019 14:39:05 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of archived log for thread 1 with sequence 265 and starting SCN of 9293139 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 264 and starting SCN of 9293136 found to restore 恢复数据文件时，发现归档丢失了:265，264
Data File Header Dump
SYS@ orcl >alter session set events 'immediate trace name file_hdrs level 10'; Session altered. SYS@ orcl >select * from v$diag_info; DATA FILE #5: name #4: /u01/app/oracle/oradata/orcl/test01.dbf creation size=0 block size=8192 status=0x1c head=4 tail=4 dup=1 tablespace 5, index=5 krfil=5 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:222 scn: 0x0000.008dcd5c 05/22/2019 14:33:12 Stop scn: 0x0000.008dcda8 05/22/2019 14:36:29 ---正常的应该是Stop scn: 0xffff.ffffffff 05/22/2019 11:22:17 Creation Checkpointed at scn: 0x0000.000e2efb 04/22/2019 17:12:12 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.00000000 prev_range: 0 Online Checkpointed at scn: 0x0000.00000000 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED Plugged readony: NO Plugin scnscn: 0x0000.00000000 Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00 Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00 Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00 Online move state: 0 V10 STYLE FILE HEADER: Compatibility Vsn = 186647552=0xb200400 Db ID=1534031567=0x5b6f76cf, Db Name='ORCL' Activation ID=0=0x0 Control Seq=8283=0x205b, File size=64000=0xfa00 File Number=5, Blksiz=8192, File Type=3 DATA Tablespace #5 - TEST rel_fn:5 Creation at scn: 0x0000.000e2efb 04/22/2019 17:12:12 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 reset logs count:0x3bfa2b4f scn: 0x0000.00000001 prev reset logs count:0x0 scn: 0x0000.00000000 recovered at 05/22/2019 14:39:05 status:0x0 root dba:0x00400208 chkpt cnt: 217 ctl cnt:216 begin-hot-backup file size: 0 Checkpointed at scn: 0x0000.008dccf8 05/22/2019 14:29:46 thread:1 rba:(0x104.99bb.10) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
内部介质恢复机制
正常情况下，Oracle不允许跳过归档加载文件
SYS@ orcl >select to_char(SEQUENCE#,'xxxxxxxxxxxxxxxxxx'),to_char(FIRST_CHANGE#,'xxxxxxxxxxxxxxxxxxxxxxxx') from v$archived_log where sequence#=266; TO_CHAR(SEQUENCE#,' TO_CHAR(FIRST_CHANGE#,'XX ------------------- ------------------------- 10a 8dcd56
更改到5号文件的文件头
BBED修改文件头检查点SCN BBED> set file 5 block 1 FILE# 5 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x008dccf8 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x3c23475a ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000104 ub4 kcrbabno @504 0x000099bb ub2 kcrbabof @508 0x0010 BBED> modify /x 56cd8d00 offset 484 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/test01.dbf (5) Block: 1 Offsets: 484 to 995 Dba:0x01400001 ------------------------------------------------------------------------ 56cd8d00 00000000 5a47233c 01000000 04010000 bb990000 10000000 02000000 BBED修改文件头检查点RBA SYS@ orcl >select * from v$lock; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 000000008DD8ECC8 000000008DD8ED20 5 MR 202 0 4 0 3763 0 000000008DD8E3F0 000000008DD8E448 5 MR 4 0 4 0 3763 0 000000008DD8E690 000000008DD8E6E8 5 MR 3 0 4 0 3763 0 000000008DD8E930 000000008DD8E988 5 MR 1 0 4 0 3763 0 000000008DD8EA10 000000008DD8EA68 5 MR 2 0 4 0 3763 0 000000008DD8EBD0 000000008DD8EC28 5 MR 201 0 4 0 3763 0 000000008DD8F048 000000008DD8F0A0 5 PW 1 0 3 0 3762 0 BBED> dump /v offset 484 count 32 File: /u01/app/oracle/oradata/orcl/test01.dbf (5) Block: 1 Offsets: 484 to 515 Dba:0x01400001 ------------------------------------------------------- 56cd8d00 00000000 5a47233c 01000000 l 04010000 bb990000 10000000 02000000 l BBED> dump /v offset 500 File: /u01/app/oracle/oradata/orcl/test01.dbf (5) Block: 1 Offsets: 500 to 531 Dba:0x01400001 ------------------------------------------------------- 04010000 bb990000 10000000 02000000 l BBED> modify /x 0a01 offset 500 File: /u01/app/oracle/oradata/orcl/test01.dbf (5) Block: 1 Offsets: 500 to 531 Dba:0x01400001 ------------------------------------------------------------------------ 0a010000 bb990000 10000000 02000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 5, Block 1: current = 0x50fb, required = 0x50fb
数据恢复
SYS@ orcl >recover datafile 5; Media recovery complete. SYS@ orcl >alter database datafile 5 online; Database altered. SYS@ orcl >select * from test.t1; ID NAME DATE1 ---------- -------------------------------------------------- --------- 1 AAAAA 22-MAY-19 2 bbbbb 22-MAY-19 BBED> exit BBED> set file 5 block 1 BBED> map /v BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x008dd509 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x3c2355c6 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000010c ub4 kcrbabno @504 0x00000d2a ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02
介质恢复总结
备份数据 --冷备，热备，cp，rman等
归档日志 --scn，rba
数据文件
检查点
介质恢复
这种方法是不得已采取的非常规手段，通常Oracle官方不被支持。跳过的归档日志中
涵盖了大量的事务，打开库后那么数据将不可避免出现不一致的问题！！！
------- --v$recover_file select file#,online_status "STATUS",to_char(change#,'9999999999999999') "SCN", To_char(time,'yyyy-mm-dd hh24:mi:ss')"TIME" from v$recover_file; --v$datafile set lines 160 col name for a50 select ts#,file#,BYTES/1024/1024/1024,status,enabled, to_char(checkpoint_change#,'999999999999999') "SCN", to_char(last_change#,'999999999999999')"STOP_SCN", name from v$datafile; --v$datafile_header set linesize 150 select ts#,file#,TABLESPACE_NAME,status, to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME, to_char(checkpoint_change#,'9999999999999999') "SCN", to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY from v$datafile_header; select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh; --v$backup begin backup select file#,to_char(CHANGE#,'9999999999999999') "SCN", to_char(TIME,'yyyy-mm-dd hh24:mi:ss') "TIME" from v$backup; ---------