11.2 Data Guard 使用增量备份基于最小数据文件头scn的GAP恢复 on AIX 6.1

此文当适合重新初始化或者在某一时间点且针对该时间点，出现gap时重新追平数据使用
1.数据库版本：oracle 11.2.0.1 on aix 6.1
2.实施流程

1) 物理备库目前应用到653主库归档文件，存在归档GAP
SQL> select thread#, sequence#, status from v$managed_standby where process='MRP0';
   THREAD#  SEQUENCE# STATUS
---------- ---------- ------------
         1        6499 WAIT_FOR_GAP

        

2) 物理备库发现归档GAP 6499-6556
Using STANDBY_ARCHIVE_DEST parameter default value as /oradata/cnapsarchive/
RFS[1]: Assigned to RFS process 15532236
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 4194402
RFS[1]: Opened log for thread 1 sequence 6501 dbid -1450528129 branch 798650689
Sat Sep 10 04:46:13 2016
Fetching gap sequence in thread 1, gap sequence 6499-6556
Sat Sep 10 04:46:15 2016
RFS[2]: Assigned to RFS process 10748000
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 42729576
RFS[2]: Opened log for thread 1 sequence 6499 dbid -1450528129 branch 798650689
Sat Sep 10 04:46:18 2016
RFS[3]: Assigned to RFS process 15663328
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 9240610
RFS[3]: Opened log for thread 1 sequence 6500 dbid -1450528129 branch 798650689
Sat Sep 10 04:46:18 2016
RFS[4]: Assigned to RFS process 8454330
RFS[4]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 4522162
Primary database is in MAXIMUM PERFORMANCE mode

3) 物理备库可以接受GAP之后的源自主库的归档文件，但不应用(此为第二种当出现网络问题导致gap出现时需要查看)
SQL> select SEQUENCE#,name from v$archived_log where SEQUENCE#>=6557;
 SEQUENCE# NAME
---------- --------------------------------------------------
       6557 /oradata/cnapsarchive//1_657_853777039.dbf
       6558 /oradata/cnapsarchive//1_658_853777039.dbf
       6559 /oradata/cnapsarchive//1_659_853777039.dbf
       6560 /oradata/cnapsarchive//1_660_853777039.dbf
       6561 /oradata/cnapsarchive//1_661_853777039.dbf
       6562 /oradata/cnapsarchive//1_662_853777039.dbf
       6563 /oradata/cnapsarchive//1_663_853777039.dbf
       6564 /oradata/cnapsarchive//1_664_853777039.dbf
       6565 /oradata/cnapsarchive//1_665_853777039.dbf
       6566 /oradata/cnapsarchive//1_666_853777039.dbf
       6567 /oradata/cnapsarchive//1_667_853777039.dbf
  
4) 准备采用基于SCN方式增量RMAN备份重新自主库同步物理备库

5) 停止物理备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6) 查询目前物理备库数据库SCN
 （A） SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------

        149533296  

注意：备库检查datafile是否有 scn不一致的情况，找出最小scn的datafile

（B） SELECT file#,checkpoint_change#  from  v$datafile_header；

file#    CHECKPOINT_CHANGE#
--------   -----------------------------------

1         149533296

2              149533296

3 149533296

4 149533296

5 149533296

6 149533290

（C）主库检查从备库最小scn号后是否添加过数据文件，若有则手工在备库添加。（注意；备库添加datafile，路径需要符合db_create_file_dest）
select FILE#,name from v$datafile where CREATION_CHANGE#>=149533290;

          
7) 基于上述备库查询的SCN在主库进行一个增量RMAN备份（注意取最小的scn号）  
RMAN>run{
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL FROM SCN 149533290 database format '/oradata/forstandby_%d_%T_%s_%p.bkp';
release channel d1 ;
release channel d2 ;
release channel d3 ;
release channel d4 ;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%U_%F';
RMAN configuration parameters are successfully reset to default value
released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=1 device type=DISK
allocated channel: d2
channel d2: SID=31 device type=DISK
allocated channel: d3
channel d3: SID=30 device type=DISK
allocated channel: d4
channel d4: SID=34 device type=DISK
Starting backup at 02-FEB-15
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/cnapsdg/system01.dbf
input datafile file number=00004 name=/oradata/cnapsdg/sysaux01.dbf
channel d1: starting piece 1 at 02-FEB-15
channel d2: starting full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/cnapsdg/undotbs01.dbf
input datafile file number=00006 name=/oradata/cnapsdg/users01.dbf
channel d2: starting piece 1 at 02-FEB-15
channel d3: starting full datafile backup set
channel d3: specifying datafile(s) in backup set
input datafile file number=00002 name=/oradata/cnapsdg/edzf01.dbf
input datafile file number=00005 name=/oradata/cnapsdg/shex.dbf
channel d3: starting piece 1 at 02-FEB-15
channel d4: starting full datafile backup set
channel d4: specifying datafile(s) in backup set
input datafile file number=00007 name=/oradata/cnapsdg/webt.dbf
channel d4: starting piece 1 at 02-FEB-15
channel d4: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_cnaps_20160202_1_1.bkp tag=TAG20160202T214936 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:47
channel d4: starting full datafile backup set
channel d4: specifying datafile(s) in backup set
including current control file in backup set
channel d4: starting piece 1 at 02-FEB-15
channel d4: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_cnaps_20160202_2_1.bkp tag=TAG20160202T214936 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:01
channel d4: starting full datafile backup set
channel d4: specifying datafile(s) in backup set
channel d2: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_cnaps_20160202_3_1.bkp tag=TAG20160202T214936 comment=NONE
channel d2: backup set complete, elapsed time: 00:01:49
Finished backup at 02-FEB-15
released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN>

8) 在主库创建基于物理备库的控制文件（此步骤也可放在备库还原好增量备份后进行）
SQL> alter database create standby controlfile as '/oradata/dg_recover/20160910/standby_201609100354.ctl';
Database altered.       

并将standby_201609100354.ctl传输到备库cnaps chuli_standby_gap目录。 

9) 在主库通过SCP上述RMAN备份至备库
[oracle@cnaps]$ pwd
/oradata/dg_recover/20160910
[oracle@cnaps chuli_standby_gap]$ ls -l
total 883484
-rw-r--r--    1 oracle   oinstall 1524875264 Sep 10 01:11 forstandby_CNAPS_20160910_11_1.bkp
-rw-r--r--    1 oracle   oinstall    8159232 Sep 10 00:57 forstandby_CNAPS_20160910_12_1.bkp
-rw-r--r--    1 oracle   oinstall    8110080 Sep 10 00:57 standby_20160910.ctl
[oracle@cnaps chuli_standby_gap]$ scp * oracle@10.x.x.x:/oradata/20160910

10) 确认备库在mount模式下。
select open_mode from v$database; 

如果是 READ ONLY WITH APPLY 则 ALTER DATABASE recover managed standby DATABASE cancel; 

注意：如果是READ ONLY，需要再关闭重新 mount 打开

11）物理备库停止实时apply

SQL> ALTER DATABASE recover managed standby DATABASE cancel;

12) 恢复物理备库

12.1)注册所有来自主库的备份集

RMAN> catalog backuppiece '/cnaps chuli_standby_gap/*';

RMAN>recover database noredo;

略

12.2)备库恢复控制文件  --若不恢复control则查询备库的current_scn 发现还是原来recover前的CURRENT_SCN（虽然recover是成功的）
 注意：可查询 v$datafile_header
RMAN> shutdown immediate;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/cnaps chuli_standby_gap/standby_20160910.ctl';
RMAN> alter database mount;

13) 物理备库启用日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

14) 确认主备库数据是否同步
主库
SQL> select group#,sequence#,status from v$log where status='CURRENT';
GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         2        6559 CURRENT
        
物理备库
SQL> select process,status,sequence# from v$managed_standby where process='MRP0';
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG        6559

15) 手工在主库切换日志确认是否可以同步到备库
SQL> alter system switch logfile;

System altered.
      
16) 关闭物理备库日志应用   
SQL> alter database recover managed standby database cancel;

Database altered.

17) 打开物理备库    
SQL> alter database open;
Database altered.

18) 打开物理备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.