oracle dataguard 基于scn备份解决dg归档丢失

概述：本文摘录网上分享案例实验做个记录；官方文档可参看：Steps to perform for Rolling Forward aPhysical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)

实验：

查看主备库状态：

DGMGRL>  show configuration;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Databases:
    CUBE  - Primary database
    JAKKI - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

主备库测试同步状态：

主库新建用户看备库是否能够应用成功：

SQL> create user cube_six identified by cube_six;
User created.
SQL> conn cube_six/cube_six
ERROR:
ORA-01045: user CUBE_SIX lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant dba to cube_six;
Grant succeeded.
SQL> conn cube_six/cube_six
Connected.
备库：

SQL> conn cube_six/cube_six 
Connected.

主库删除用户：

SQL> conn / as sysdba
Connected.
SQL> drop user cube_six cascade;
User dropped.

备库再次连接看看：

SQL>  conn cube_six/cube_six 
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
确定主备库同步是正常的。

好开始测试基于scn的恢复：

步骤：

1、备库停止日志应用（注意使用dgbroker进行管理备库最好是在mount状态）

2、主库切换归档日志

3、删除中间未应用的一个归档日志

4、备库开启日志应用发现一个归档日志丢失无法继续应用日志

5、使用基于scn的增量备份进行恢复

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/fast_recovery_area
Oldest online log sequence     43
Next log sequence to archive   45
Current log sequence           45

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/fast_recovery_area
Oldest online log sequence     50
Next log sequence to archive   52
Current log sequence           52

备库：

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/fast_recovery_area
Oldest online log sequence     44
Next log sequence to archive   0
Current log sequence           45
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/fast_recovery_area
Oldest online log sequence     51
Next log sequence to archive   0
Current log sequence           52

删除归档日志：

我们将49号归档在主备两边都删除了

[oracle@jakki fast_recovery_area]$   mv 1_49_964541157.dbf 1_49_964541157.dbf.bak

备库开启日志应用：

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

备库告警日志显示：

All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/fast_recovery_area/1_45_964541157.dbf
Media Recovery Log /u01/app/oracle/fast_recovery_area/1_46_964541157.dbf
Media Recovery Log /u01/app/oracle/fast_recovery_area/1_47_964541157.dbf
Media Recovery Log /u01/app/oracle/fast_recovery_area/1_48_964541157.dbf
Media Recovery Log /u01/app/oracle/fast_recovery_area/1_49_964541157.dbf
Error opening /u01/app/oracle/fast_recovery_area/1_49_964541157.dbf
Attempting refetch
Media Recovery Waiting for thread 1 sequence 49
Fetching gap sequence in thread 1, gap sequence 49-49
Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect

找到备库最小的scn号：

SQL>  select CHECKPOINT_CHANGE#  from v$datafile_header  order by 1;


CHECKPOINT_CHANGE#
------------------
           1219486
           1219486
           1219486
           1219486


SQL> select CHECKPOINT_CHANGE#  from v$database  order by 1;


CHECKPOINT_CHANGE#
------------------
           1218615

在主库中按照scn进行增量备份并传输至备库：

[oracle@cube inc]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 4 17:18:50 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CUBE (DBID=3147674261)

RMAN> run
 {configure controlfile autobackup on;
 sql 'alter system switch logfile'; 
BACKUP INCREMENTAL FROM SCN  1218615 DATABASE FORMAT '/home/oracle/inc/IC_%d_%u' tag 'FORSTANDBY';
 }

scp IC_CUBE_0* 192.168.1.68:/home/oracle/inc/

备库重新恢复：

[oracle@jakki inc]$ rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 4 17:39:24 2018


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: CUBE (DBID=3147674261)


RMAN> shutdown immediate;


using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down


RMAN> startup nomount;


connected to target database (not started)
Oracle instance started


Total System Global Area     839282688 bytes


Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes

RMAN> restore standby controlfile from '/home/oracle/inc/IC_CUBE_0rsnrk36';


Starting restore at 04-JAN-18
using channel ORA_DISK_1


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/JAKKI/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/JAKKI/control02.ctl
Finished restore at 04-JAN-18


RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1


RMAN> recover database;


Starting recover at 04-JAN-18
Starting implicit crosscheck backup at 04-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=34 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 04-JAN-18


Starting implicit crosscheck copy at 04-JAN-18
using channel ORA_DISK_1
using channel ORA_DISK_2
Crosschecked 2 objects
Finished implicit crosscheck copy at 04-JAN-18


searching for all files in the recovery area
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/JAKKI/autobackup/2018_01_04/o1_mf_s_964546315_f4vxh36s_.bkp
File Name: /u01/app/oracle/fast_recovery_area/JAKKI/autobackup/2018_01_04/o1_mf_s_964543737_f4vv3jsm_.bkp


using channel ORA_DISK_1
using channel ORA_DISK_2


starting media recovery


archived log for thread 1 with sequence 54 is already on disk as file /u01/app/oracle/fast_recovery_area/1_54_964541157.dbf
archived log for thread 1 with sequence 55 is already on disk as file /u01/app/oracle/fast_recovery_area/1_55_964541157.dbf
unable to find archived log
archived log thread=1 sequence=49
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/04/2018 17:44:21
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 49 and starting SCN of 1219486

RMAN> catalog start with '/home/oracle/inc';


searching for all files that match the pattern /home/oracle/inc


List of Files Unknown to the Database
=====================================
File Name: /home/oracle/inc/IC_CUBE_0qsnrk2m
File Name: /home/oracle/inc/IC_CUBE_0rsnrk36
File Name: /home/oracle/inc/IC_CUBE_0psnrk2m


Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /home/oracle/inc/IC_CUBE_0qsnrk2m
File Name: /home/oracle/inc/IC_CUBE_0rsnrk36
File Name: /home/oracle/inc/IC_CUBE_0psnrk2m


RMAN> recover database;


Starting recover at 04-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=35 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/JAKKI/system01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/JAKKI/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/inc/IC_CUBE_0psnrk2m
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u01/app/oracle/oradata/JAKKI/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/JAKKI/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/inc/IC_CUBE_0qsnrk2m
channel ORA_DISK_1: piece handle=/home/oracle/inc/IC_CUBE_0psnrk2m tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=/home/oracle/inc/IC_CUBE_0qsnrk2m tag=FORSTANDBY
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01


starting media recovery


archived log for thread 1 with sequence 54 is already on disk as file /u01/app/oracle/fast_recovery_area/1_54_964541157.dbf
archived log for thread 1 with sequence 55 is already on disk as file /u01/app/oracle/fast_recovery_area/1_55_964541157.dbf
archived log for thread 1 with sequence 56 is already on disk as file /u01/app/oracle/fast_recovery_area/1_56_964541157.dbf
archived log for thread 1 with sequence 57 is already on disk as file /u01/app/oracle/fast_recovery_area/1_57_964541157.dbf
archived log for thread 1 with sequence 58 is already on disk as file /u01/app/oracle/fast_recovery_area/1_58_964541157.dbf
archived log for thread 1 with sequence 59 is already on disk as file /u01/app/oracle/fast_recovery_area/1_59_964541157.dbf
archived log for thread 1 with sequence 60 is already on disk as file /u01/app/oracle/fast_recovery_area/1_60_964541157.dbf
archived log for thread 1 with sequence 61 is already on disk as file /u01/app/oracle/fast_recovery_area/1_61_964541157.dbf
archived log file name=/u01/app/oracle/fast_recovery_area/1_54_964541157.dbf thread=1 sequence=54
archived log file name=/u01/app/oracle/fast_recovery_area/1_55_964541157.dbf thread=1 sequence=55
archived log file name=/u01/app/oracle/fast_recovery_area/1_56_964541157.dbf thread=1 sequence=56
archived log file name=/u01/app/oracle/fast_recovery_area/1_57_964541157.dbf thread=1 sequence=57
archived log file name=/u01/app/oracle/fast_recovery_area/1_58_964541157.dbf thread=1 sequence=58
archived log file name=/u01/app/oracle/fast_recovery_area/1_59_964541157.dbf thread=1 sequence=59
archived log file name=/u01/app/oracle/fast_recovery_area/1_60_964541157.dbf thread=1 sequence=60
archived log file name=/u01/app/oracle/fast_recovery_area/1_61_964541157.dbf thread=1 sequence=61
unable to find archived log
archived log thread=1 sequence=62
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/04/2018 17:55:12
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 62 and starting SCN of 1221076

SQL> alter database open;

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select open_mode,database_role,switchover_status from v$database;


OPEN_MODE                                DATABASE_ROLE                    SWITCHOVER_STATUS
---------------------------------------- -------------------------------- ----------------------------------------
READ ONLY WITH APPLY                     PHYSICAL STANDBY                 NOT ALLOWED


至此基于scn的dataguard 追加恢复完成。

总结：此方法可应用与数据量大只是丢失了部分的归档日志导致需要重新搭建将耗费很大的力气，此方法可节省时间提高效率。