A-A+
BBED跳过归档日志恢复数据
操作系统信息
[oracle@rhl6 lib]$ uname -a Linux rhl6.0x64 2.6.32-71.el6.x86_64 #1 SMP Wed Sep 1 01:33:01 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
数据库版本
SQL> set line 400
SQL> select * from gv$version;
INST_ID BANNER
---------- --------------------------------------------------------------------------------
1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1 PL/SQL Release 11.2.0.3.0 - Production
1 CORE 11.2.0.3.0 Production
1 TNS for Linux: Version 11.2.0.3.0 - Production
1 NLSRTL Version 11.2.0.3.0 - Production
测试数据
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 66
Next log sequence to archive 68
Current log sequence 68
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4977M
SQL> conn yallonking/yallonking
Connected.
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select rowid,t.* from yallonking t;
ROWID ID NAME MY_DATE
------------------ ---------- -------------------- -------------------
AAARciAAFAAAACHAAA 1 yallonking 2013/07/22 09:44:53
AAARciAAFAAAACHAAB 2 oraking 2013/07/22 09:45:07
AAARciAAFAAAACHAAC 3 xiaolong 2013/07/22 09:50:09
备份数据库
RMAN> backup database; Starting backup at 24-JUL-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=30 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=00001 name=/u01/app/oracle/oradata/ora11gr2/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ora11gr2/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ora11gr2/users01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf channel ORA_DISK_1: starting piece 1 at 24-JUL-13 channel ORA_DISK_1: finished piece 1 at 24-JUL-13 piece handle=/u01/app/oracle/fast_recovery_area/ORA11GR2/backupset/2013_07_24/o1_mf_nnndf_TAG20130724T112657_8yyld1x7_.bkp tag=TAG20130724T112657 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:03:56 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 24-JUL-13 channel ORA_DISK_1: finished piece 1 at 24-JUL-13 piece handle=/u01/app/oracle/fast_recovery_area/ORA11GR2/backupset/2013_07_24/o1_mf_ncsnf_TAG20130724T112657_8yylmk9m_.bkp tag=TAG20130724T112657 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 24-JUL-13 RMAN> exit Recovery Manager complete.
下边插入3条新记录并让其置于3个不同的归档日志中
SQL> insert into yallonking values(4,'archive_test1',sysdate); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered. SQL> insert into yallonking values(5,'archive_test2',sysdate); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered. SQL> insert into yallonking values(6,'archive_test3',sysdate); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered. SQL> select rowid,t.* from yallonking t; ROWID ID NAME MY_DATE ------------------ ---------- -------------------- ------------------- AAARciAAFAAAACHAAA 1 yallonking 2013/07/22 09:44:53 AAARciAAFAAAACHAAB 2 oraking 2013/07/22 09:45:07 AAARciAAFAAAACHAAC 3 xiaolong 2013/07/22 09:50:09 AAARciAAFAAAACHAAD 4 archive_test1 2013/07/24 11:31:48 AAARciAAFAAAACHAAE 5 archive_test2 2013/07/24 11:32:36 AAARciAAFAAAACHAAF 6 archive_test3 2013/07/24 11:32:51 6 rows selected.
清空redo记录，避免记录在redo中的存在影响测试
SQL> select a.GROUP#,MEMBER,a.STATUS from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------
1 /u01/app/oracle/oradata/ora11gr2/redo01.log ACTIVE
2 /u01/app/oracle/oradata/ora11gr2/redo02.log CURRENT
3 /u01/app/oracle/oradata/ora11gr2/redo03.log ACTIVE
SQL> alter database clear unarchived logfile '/u01/app/oracle/oradata/ora11gr2/redo01.log';
Database altered.
SQL> alter database clear unarchived logfile '/u01/app/oracle/oradata/ora11gr2/redo02.log';
alter database clear unarchived logfile '/u01/app/oracle/oradata/ora11gr2/redo02.log'
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance ora11gr2 (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora11gr2/redo02.log'
SQL> alter database clear unarchived logfile '/u01/app/oracle/oradata/ora11gr2/redo03.log';
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database clear unarchived logfile '/u01/app/oracle/oradata/ora11gr2/redo02.log';
Database altered.
下边确认，每个记录都只在对应的归档日志中存在
[root@rhl6 ~]# cd /u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24 [root@rhl6 2013_07_24]# ls -ltr total 15528 -rw-r-----. 1 oracle oinstall 13705216 Jul 24 11:31 o1_mf_1_68_8yyloh5b_.arc -rw-r-----. 1 oracle oinstall 3072 Jul 24 11:32 o1_mf_1_69_8yylps9r_.arc -rw-r-----. 1 oracle oinstall 3584 Jul 24 11:32 o1_mf_1_70_8yylq9on_.arc -rw-r-----. 1 oracle oinstall 2186240 Jul 24 11:41 o1_mf_1_71_8yym6j68_.arc [root@rhl6 2013_07_24]# string * | grep 'archive_test1' -bash: string: command not found [root@rhl6 2013_07_24]# [root@rhl6 2013_07_24]# [root@rhl6 2013_07_24]# strings * | grep 'archive_test1' archive_test1 [root@rhl6 2013_07_24]# strings * | grep 'archive_test2' archive_test2 [root@rhl6 2013_07_24]# strings * | grep 'archive_test3' archive_test3 [root@rhl6 2013_07_24]# strings o1_mf_1_68_8yyloh5b_.arc | grep 'archive_test1' archive_test1 [root@rhl6 2013_07_24]# strings o1_mf_1_69_8yylps9r_.arc | grep 'archive_test2' archive_test2 [root@rhl6 2013_07_24]# strings o1_mf_1_70_8yylq9on_.arc | grep 'archive_test3' archive_test3 [root@rhl6 2013_07_24]# strings o1_mf_1_71_8yym6j68_.arc | grep 'archive_tes' [root@rhl6 ora11gr2]# cd /u01/app/oracle/oradata/ora11gr2/ [root@rhl6 ora11gr2]# strings *.log | grep 'archive_tes'
人为删除数据库数据文件和部分中间的归档日志，并进行从备份恢复数据库
[root@rhl6 2013_07_24]# rm -rf o1_mf_1_69_8yylps9r_.arc
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11gr2/system01.dbf
/u01/app/oracle/oradata/ora11gr2/sysaux01.dbf
/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
/u01/app/oracle/oradata/ora11gr2/users01.dbf
/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
SQL> !mv /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf /tmp/yallonking01.dbf_bak
SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11gr2/yallonking01.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.
SQL> startup
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2233960 bytes
Variable Size 545261976 bytes
Database Buffers 385875968 bytes
Redo Buffers 6123520 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf'
RMAN> restore datafile 5;
Starting restore at 24-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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/ora11gr2/yallonking01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11GR2/backupset/2013_07_24/o1_mf_nnndf_TAG20130724T112657_8yyld1x7_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11GR2/backupset/2013_07_24/o1_mf_nnndf_TAG20130724T112657_8yyld1x7_.bkp tag=TAG20130724T112657
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-JUL-13
SQL> recover datafile 5;
ORA-00279: change 761058 generated at 07/24/2013 11:26:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24/o1_mf_1_68_8yy
loh5b_.arc
ORA-00280: change 761058 for thread 1 is in sequence #68
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 761181 generated at 07/24/2013 11:31:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24/o1_mf_1_69_8yy
lps9r_.arc
ORA-00280: change 761181 for thread 1 is in sequence #69
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24/o1_mf_1_69_8y
ylps9r_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
注：此处发现缺失的正是我们之前删除的那个中间归档日志69
查看数据库一致性
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
761498
SQL> select file#,checkpoint_change#,status from v$datafile;
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
1 761498 SYSTEM
2 761498 ONLINE
3 761498 ONLINE
4 761498 ONLINE
5 761498 ONLINE
SQL> select file#,change# from v$recover_file;
FILE# CHANGE#
---------- ----------
5 761181
下边使用BBED进行处理
BBED> d /v dba 1,1 offset 484 count 4 File: /u01/app/oracle/oradata/ora11gr2/system01.dbf (1) Block: 1 Offsets: 484 to 487 Dba:0x00400001 ------------------------------------------------------- 9a9e0b00 l .... <16 bytes per line> BBED> d /v dba 5,1 offset 484 count 4 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 1 Offsets: 484 to 487 Dba:0x01400001 ------------------------------------------------------- 5d9d0b00 l ]... <16 bytes per line>
注：此处明显因为5号文件从备份恢复而来，故其scn滞后于文件1
凡是修改文件5的scn和1号文件一致，那么将会失去丢失归档之后的所有归档数据
下边我们只修改 kcrbaseq 为下一个日志序列，跳过缺失的日志
BBED> d /v dba 1,1 offset 484 count 4 File: /u01/app/oracle/oradata/ora11gr2/system01.dbf (1) Block: 1 Offsets: 484 to 487 Dba:0x00400001 ------------------------------------------------------- 9a9e0b00 l .... <16 bytes per line> BBED> d /v dba 5,1 offset 484 count 4 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 1 Offsets: 484 to 487 Dba:0x01400001 ------------------------------------------------------- 5d9d0b00 l ]... <16 bytes per line> BBED> d /v dba 5,1 offset 500 count 4 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 1 Offsets: 500 to 503 Dba:0x01400001 ------------------------------------------------------- 45000000 l E... <16 bytes per line>
查看发现当前的日志序列是 0x45也就是69号，我们将其改成0x46也就是70号日志文件
BBED> m /x 46 dba 5,1 offset 500 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 1 Offsets: 500 to 503 Dba:0x01400001 ------------------------------------------------------------------------ 46000000 <32 bytes per line> BBED> sum apply; Check value for File 5, Block 1: current = 0xcd85, required = 0xcd85
再次恢复
SQL> recover datafile 5;
ORA-00279: change 761181 generated at 07/24/2013 11:31:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24/o1_mf_1_70_8yy
lq9on_.arc
ORA-00280: change 761181 for thread 1 is in sequence #70
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00326: log begins at change 761200, need earlier change 761181 <<也就是当前的scn 000b9d5d
ORA-00334: archived log:
'/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24/o1_mf_1_70_8y
ylq9on_.arc'
注：此处发现该文件的scn太久
我们继续修改5号文件的scn为 761200 也就是十六进制的 000b9d70,如下计算
SQL> select to_char('761200','xxxxxxxx') from dual;
TO_CHAR('
---------
b9d70
SQL> select to_number('000b9d70','xxxxxxxx') from dual;
TO_NUMBER('00B9D70','XXXXXXXX')
-------------------------------
761200
BBED> d /v dba 1,1 offset 484 count 4
File: /u01/app/oracle/oradata/ora11gr2/system01.dbf (1)
Block: 1 Offsets: 484 to 487 Dba:0x00400001
-------------------------------------------------------
9a9e0b00 l ....
<16 bytes per line>
BBED> d /v dba 5,1 offset 484 count 4
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 1 Offsets: 484 to 487 Dba:0x01400001
-------------------------------------------------------
5d9d0b00 l ]...
<16 bytes per line>
BBED> m /x 709d0b00 dba 5,1 offset 484 <<注意字节序的反转
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 1 Offsets: 484 to 487 Dba:0x01400001
------------------------------------------------------------------------
709d0b00
<32 bytes per line>
BBED> sum apply;
Check value for File 5, Block 1:
current = 0xd326, required = 0xd326
下边继续尝试恢复数据文件
SQL> recover datafile 5;
ORA-00279: change 761200 generated at 07/24/2013 11:31:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24/o1_mf_1_70_8yy
lq9on_.arc
ORA-00280: change 761200 for thread 1 is in sequence #70
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 761212 generated at 07/24/2013 11:32:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_24/o1_mf_1_71_8yy
m6j68_.arc
ORA-00280: change 761212 for thread 1 is in sequence #71
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
注：此处已经跳过缺失的归档日志，并成功的应用了之后的归档进行了数据文件的恢复
检查测试数据
SQL> alter database open;
Database altered.
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select * from yallonking.yallonking;
ID NAME MY_DATE
---------- -------------------- -------------------
1 yallonking 2013/07/22 09:44:53
2 oraking 2013/07/22 09:45:07
3 xiaolong 2013/07/22 09:50:09
4 archive_test1 2013/07/24 11:31:48
6 archive_test3 2013/07/24 11:32:51
注：此处果真丢失了5号记录。
小结：
只要文件的scn和控制文件中的scn一致，则库便可以完成一致性检查打开，如果文件的scn滞后，那么就需要从该scn应用之后的日志（归档日志）追平scn。
在此例中，我们要跳过缺失的归档日志，我们需要设置该文件的scn为接下来归档开始的scn，以及设置日志序列号为接下来的归档日志序列号，这样才可跳过确实的归档日志进行恢复。
