A-A+

BBED跳过归档日志恢复数据

2013年07月24日 Backup&Recovery 暂无评论 阅读 1,722 次

操作系统信息

[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,以及设置日志序列号为接下来的归档日志序列号,这样才可跳过确实的归档日志进行恢复。

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: