使用BBED跳过归档的恢复
模拟场景:在做恢复时发现丢失部分归档
开启归档
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 44 Next log sequence to archive 46 Current log sequence 46 SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 10G recovery_parallelism integer 0 |
创建表空间、用户、表并插入数据
create tablespace skip_arch datafile '/u01/app/oracle/oradata/orcl/skip_arch01.dbf' size 50m; create user lyj identified by lyj default tablespace skip_arch; grant dba to lyj; conn lyj/lyj create table t1 (id int,name varchar2(10)); insert into t1 values (1,'AAAAAA'); commit; |
对6号文件做备份
conn / as sysdba col FILE_NAME for a60 select FILE_ID,FILE_NAME from dba_data_files order by 1; FILE_ID FILE_NAME ---------- ------------------------------------------------------------ 1 /u01/app/oracle/oradata/orcl/system01.dbf 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 4 /u01/app/oracle/oradata/orcl/users01.dbf 5 /u01/app/oracle/oradata/orcl/dsi01.dbf 6 /u01/app/oracle/oradata/orcl/skip_arch01.dbf rman target / backup datafile 6 format '/oradata/rmanbackup/datafile5_%U'; |
切换归档日志
select sequence#,status from v$archived_log order by 1 desc; SEQUENCE# S ---------- - 45 A 44 A 43 A 42 A 41 A 40 A # 多次切换 alter system switch logfile; / select sequence#,status from v$archived_log order by 1 desc; SEQUENCE# S ---------- - 51 A 50 A 49 A 48 A 47 A 46 A # 查看归档文件 cd /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_03_19 ll total 56544 -rw-r----- 1 oracle oinstall 36331008 Mar 19 00:01 o1_mf_1_45_fbx39wby_.arc -rw-r----- 1 oracle oinstall 21547008 Mar 19 15:05 o1_mf_1_46_fbyrbkbz_.arc -rw-r----- 1 oracle oinstall 2048 Mar 19 15:05 o1_mf_1_47_fbyrbmnn_.arc -rw-r----- 1 oracle oinstall 1536 Mar 19 15:05 o1_mf_1_48_fbyrbqww_.arc -rw-r----- 1 oracle oinstall 1024 Mar 19 15:06 o1_mf_1_49_fbyrbs1o_.arc -rw-r----- 1 oracle oinstall 1024 Mar 19 15:06 o1_mf_1_50_fbyrbt9w_.arc -rw-r----- 1 oracle oinstall 1024 Mar 19 15:06 o1_mf_1_51_fbyrbv64_.arc # 删除48、49号归档 rm o1_mf_1_48_fbyrbqww_.arc o1_mf_1_49_fbyrbs1o_.arc ll total 56536 -rw-r----- 1 oracle oinstall 36331008 Mar 19 00:01 o1_mf_1_45_fbx39wby_.arc -rw-r----- 1 oracle oinstall 21547008 Mar 19 15:05 o1_mf_1_46_fbyrbkbz_.arc -rw-r----- 1 oracle oinstall 2048 Mar 19 15:05 o1_mf_1_47_fbyrbmnn_.arc -rw-r----- 1 oracle oinstall 1024 Mar 19 15:06 o1_mf_1_50_fbyrbt9w_.arc -rw-r----- 1 oracle oinstall 1024 Mar 19 15:06 o1_mf_1_51_fbyrbv64_.arc |
离线6号文件
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 3571679 0 2985399 SYSTEM 2 1834 3571679 0 2985399 ONLINE 3 923328 3571679 0 2985399 ONLINE 4 16143 3571679 0 2985399 ONLINE 5 2959979 3571679 0 2985399 ONLINE 6 3570623 3571679 0 0 ONLINE SQL> alter database datafile 6 offline; Database altered. 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 3571679 0 2985399 SYSTEM 2 1834 3571679 0 2985399 ONLINE 3 923328 3571679 0 2985399 ONLINE 4 16143 3571679 0 2985399 ONLINE 5 2959979 3571679 0 2985399 ONLINE 6 3570623 3571679 0 3572789 0 RECOVER |
archivelog模式下,当数据文件offline时,其对应的数据文件头stop scn会更新,同时controlfile中该datafile的stop scn信息也会更新,此时也会更新offline scn,并且offline scn等于stop scn。
对6号文件进行还原
rman target / RMAN> restore datafile 6; Starting restore at 2018-03-19 15:49:54 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 00006 to /u01/app/oracle/oradata/orcl/skip_arch01.dbf channel ORA_DISK_1: reading from backup piece /oradata/rmanbackup/datafile5_05su6blm_1_1 channel ORA_DISK_1: piece handle=/oradata/rmanbackup/datafile5_05su6blm_1_1 tag=TAG20180319T145901 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 2018-03-19 15:49:57 |
6号数据文件无法被online
SQL> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/skip_arch01.dbf' |
对6号文件进行恢复时因归档丢失报错
RMAN> recover datafile 6; Starting recover at 2018-03-19 15:53:03 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 46 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_03_19/o1_mf_1_46_fbyrbkbz_.arc archived log for thread 1 with sequence 47 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_03_19/o1_mf_1_47_fbyrbmnn_.arc archived log for thread 1 with sequence 50 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_03_19/o1_mf_1_50_fbyrbt9w_.arc archived log for thread 1 with sequence 51 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_03_19/o1_mf_1_51_fbyrbv64_.arc RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/19/2018 15:53:03 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of archived log for thread 1 with sequence 49 and starting SCN of 3571670 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 48 and starting SCN of 3571667 found to restore |
跳归档恢复
# 归档序列48、49已丢失,从50开始恢复 select to_char(SEQUENCE#,'xxxxxxxxxxx') seq, to_char(FIRST_CHANGE#,'xxxxxxxxxxx') scn from v$archived_log where SEQUENCE#=50; SEQ SCN ------------ ------------ 32 367fd9 # d97f3600 # 把上面的值更新到6号文件头 BBED> info File# Name Size(blks) ----- ---- ---------- 1 /u01/app/oracle/oradata/orcl/system01.dbf 97281 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 96001 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 21121 4 /u01/app/oracle/oradata/orcl/users01.dbf 641 5 /u01/app/oracle/oradata/orcl/dsi01.dbf 64001 6 /u01/app/oracle/oradata/orcl/skip_arch01.dbf 6400 BBED> set file 6 block 1 FILE# 6 BLOCK# 1 BBED> map /v File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Dba:0x01800001 ------------------------------------------------------------ Data File Header struct kcvfh, 860 bytes @0 struct kcvfhbfh, 20 bytes @0 struct kcvfhhdr, 76 bytes @20 ub4 kcvfhrdb @96 struct kcvfhcrs, 8 bytes @100 ub4 kcvfhcrt @108 ub4 kcvfhrlc @112 struct kcvfhrls, 8 bytes @116 ub4 kcvfhbti @124 struct kcvfhbsc, 8 bytes @128 ub2 kcvfhbth @136 ub2 kcvfhsta @138 struct kcvfhckp, 36 bytes @484 ## BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ## SCN ub4 kscnbas @484 0x00367eb2 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x39e32eb6 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000002e ## SEQ ub4 kcrbabno @504 0x00009623 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 # 修改SCN BBED> dump /v offset 484 count 32 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Offsets: 484 to 515 Dba:0x01800001 ------------------------------------------------------- b27e3600 00000000 b62ee339 01000000 l .~6........9.... 2e000000 23960000 1000774a 02000000 l ....#.....wJ.... <16 bytes per line> BBED> modify /x d9 offset 484 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Offsets: 484 to 515 Dba:0x01800001 ------------------------------------------------------------------------ d97e3600 00000000 b62ee339 01000000 2e000000 23960000 1000774a 02000000 <32 bytes per line> BBED> modify /x 7f3600 offset 485 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Offsets: 485 to 516 Dba:0x01800001 ------------------------------------------------------------------------ 7f360000 000000b6 2ee33901 0000002e 00000023 96000010 00774a02 00000000 <32 bytes per line> BBED> sum apply Check value for File 6, Block 1: current = 0xc392, required = 0xc392 # 修改SEQ SQL> select to_number('2e','xxxxxxxxxxxxxxxxx') from dual; TO_NUMBER('2E','XXXXXXXXXXXXXXXXX') ----------------------------------- 46 SQL> select to_char(50,'xxxxxxxxx') from dual; TO_CHAR(50 ---------- 32 BBED> dump /v offset 500 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Offsets: 500 to 531 Dba:0x01800001 ------------------------------------------------------- 2e000000 23960000 1000774a 02000000 l ....#.....wJ.... 00000000 00000000 00000000 00000000 l ................ <16 bytes per line> BBED> modify /x 32 offset 500 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Offsets: 500 to 531 Dba:0x01800001 ------------------------------------------------------------------------ 32000000 23960000 1000774a 02000000 00000000 00000000 00000000 00000000 <32 bytes per line> # 修改块号 BBED> dump /v offset 504 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Offsets: 504 to 535 Dba:0x01800001 ------------------------------------------------------- 23960000 1000774a 02000000 00000000 l #.....wJ........ 00000000 00000000 00000000 00000000 l ................ <16 bytes per line> BBED> modify /x 0100 offset 504 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 1 Offsets: 504 to 535 Dba:0x01800001 ------------------------------------------------------------------------ 01000000 1000774a 02000000 00000000 00000000 00000000 00000000 00000000 BBED> sum apply Check value for File 6, Block 1: current = 0x55ac, required = 0x55ac # 之后6号数据文件在recover后就可以online SQL> recover datafile 6; Media recovery complete. SQL> alter database datafile 6 online; Database altered. |
datafile的status有哪些
datafile的status:
KCCFEFDB 0x0001 /* file read-only, plugged from foreign DB */ KCCFEONL 0x0002 /* file is ONLine */ KCCFERDE 0x0004 /* ReaDing is Enabled */ KCCFECGE 0x0008 /* ChanGing is Enabled */ KCCFEMRR 0x0010 /* Media Recovery Required */ KCCFEGEM 0x0020 /* Generate End hot backup Marker at next open */ KCCFECKD 0x0040 /* File record generated by check dictionary */ KCCFESOR 0x0080 /* Save Offline scn Range at next checkpoint */ KCCFERMF 0x0100 /* Renamed Missing File */ KCCFEGOI 0x0200 /* Generate Off-line Immediate marker */ KCCFECUV 0x0400 /* Checkpoint by instance where UnVerified */ KCCFEDRP 0x0800 /* offline to be DRoPped */ KCCFEODC 0x2000 /* Online at Dictionary Check if read/only tblspc */ KCCFEDBR 0x4000 /* entry created by DBMS_BACKUP_RESTORE */ KCCFETRO 0x8000 /* Transition Read Only */define KCCFEWCC 0x1000 /* |
示例
DATA FILE #1: name #8: /u01/app/oracle/oradata/orcl/system01.dbf creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1 0xe = 0x8 + 0x4 + 0x2 Change/Write + Read + Online => Normal status of open files |
详解检查点的结构
通过Data File Header Dump,可以从dump出的trace文件看到检查点最核心的结构,
Checkpointed at scn: 0x0000.0036b8dd 03/19/2018 22:00:37 thread:1 rba:(0x35.2.10) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 SCN:0x0000.0036b8dd 03/19/2018 22:00:37 时间 RBA:日志的地址(log sequence number + block number + 偏移量) THREAD:线程,1单实例 |
其他内容
Data File Header Dump
alter session set events 'immediate trace name file_hdrs level <n>'; 如: alter session set events 'immediate trace name file_hdrs level 10'; |
level
level 1: control file's data file entry level 2 & 4 : level 1 + generic file header level 3 or higher: level 2 + data file header level 10: Most commonly used, It provides the same output as level 3. |
File Type
KCCTYPCF 1 /* control file */ KCCTYPRL 2 /* redo log file */ KCCTYPDF 3 /* vanilla db file */ KCCTYPBC 4 /* backup control file */ KCCTYPBP 5 /* backup piece */ KCCTYPTF 6 /* temporary db file */ KCCTYPCT 7 /* change tracking file */ KCCTYPFL 8 /* flashback database log file */ KCCTYPAL 9 /* archivelog file */ KCCTYPDC 10 /* datafile copy file */ KCCTYPIR 11 /* incompletely restored db file */ KCCTYPEL 12 /* foreign archivelog file */ KCCTYPLB 13 /* LOB */ |