【Oracle】基于SCN的增量备份修复DataGuard GAP

1. 首先来模拟 Gap 的产生 1.1. 备库关闭： SYS@dgtest_sshutdown immediate; 1.2. 主库切换日志 SYS@dgtestselect SEQUENCE#,ARCHIVED,STATUS from v$log; SEQUENCE# ARC STATUS ---------- --- ---------------- 61 YES ACTIVE 62 YES ACTIVE 63 NO CURREN

1. 首先来模拟Gap的产生

1.1. 备库关闭：

SYS@dgtest_s>shutdown immediate;

1.2. 主库切换日志

SYS@dgtest>select SEQUENCE#,ARCHIVED,STATUS from v$log;

 

 SEQUENCE# ARC STATUS

---------- --- ----------------

        61 YES ACTIVE

        62 YES ACTIVE

        63 NO  CURRENT

 

SYS@dgtest>alter system archive log current;

 

System altered.

 

SYS@dgtest>select SEQUENCE#,ARCHIVED,STATUS from v$log;

 

 SEQUENCE# ARC STATUS

---------- --- ----------------

        64 NO  CURRENT

        62 YES ACTIVE

        63 YES ACTIVE

刚才current的日志已经归档

1.3. 删除归档，产生UNRESOLVABLE GAP

现在删除63号归档

[oracle@primary arch]$ mv 1_63_909786801.dbf 1_63_909786801.dbf.bak

 

2. 查看报错

2.1. 启动备库

SYS@dgtest_s>startup

2.2. 查看备库的alert

Media Recovery Log /u01/app/oracle/arch/1_62_909786801.dbf

Media Recovery Waiting for thread 1 sequence 63

Fetching gap sequence in thread 1, gap sequence 63-63

Fri May 06 05:28:09 2016

FAL[client]: Failed to request gap sequence

 GAP - thread 1 sequence 63-63

 DBID 3866310445 branch 909786801

FAL[client]: All defined FAL servers have been attempted.

------------------------------------------------------------

Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization

parameter is defined to a value that's sufficiently large

enough to maintain adequate log switch information to resolve

archivelog gaps.

------------------------------------------------------------

2.3. 主库查询SWITCHOVER_STATUS

SYS@dgtest>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

--------------------

UNRESOLVABLE GAP

为UNRESOLVABLE GAP，说明此时的GAP需要我们自己手工去修复，无法自动修复，可自动修复的GAP显示为RESOLVABLE GAP

 

3. 基于SCM的增量备份修复GAP

3.1. 在备库上查询current scn号

SYS@dgtest_s>select current_scn from v$database;

 

CURRENT_SCN

-----------

    2567388

3.2. 到主库去进行基于此SCN的增量备份

RMAN> BACKUP INCREMENTAL FROM SCN 2567388 DATABASE FORMAT '/u01/app/oracle/oradata/tmp/ora11_scn_%U' tag 'For Standby Gap';

3.3. 传输到备库：

[oracle@primary tmp]$ scp * standby:/u01/app/oracle/oradata/tmp

oracle@standby's password:

ora11_scn_0kr54hvk_1_1                                                                                                               100%  125MB 125.2MB/s   00:01    

ora11_scn_0lr54l99_1_1                                                                                                               100% 9664KB   9.4MB/s   00:00  

3.4. 备库重新启动到mount，并取消日志应用

SYS@dgtest_s>shutdown immediate;

SYS@dgtest_s>startup mount;

SYS@dgtest_s>alter database recover managed standby database cancel;

3.5. 注册刚才传输过来的备份集

RMAN> CATALOG START WITH '/u01/app/oracle/oradata/tmp';

3.6. recover备库

RMAN> recover database noredo;

恢复完毕，这时我们可以观察备库的alert日志：

Incremental restore complete of datafile 4 /u01/app/oracle/oradata/dgtest_s/users01.dbf

  checkpoint is 2893208

  last deallocation scn is 3

Incremental restore complete of datafile 3 /u01/app/oracle/oradata/dgtest_s/undotbs01.dbf

  checkpoint is 2893208

  last deallocation scn is 973300

Incremental restore complete of datafile 5 /u01/app/oracle/oradata/dgtest_s/example01.dbf

  checkpoint is 2893208

  last deallocation scn is 942056

Mon May 09 05:20:25 2016

Incremental restore complete of datafile 2 /u01/app/oracle/oradata/dgtest_s/sysaux01.dbf

  checkpoint is 2893208

  last deallocation scn is 956093

Incremental restore complete of datafile 1 /u01/app/oracle/oradata/dgtest_s/system01.dbf

  checkpoint is 2893208

  last deallocation scn is 955346

发现数据文件的scn号都已经重新刷新，但是此时还不能重新起库，需要重新从主库生成一个standby controlfile。

3.7. 主库备份控制文件

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/app/oracle/oradata/tmp/ctl.bak';

3.8. 传输standby控制文件到备库

oracle@standby's password:

ctl.bak                                                                                                                              100% 9664KB   9.4MB/s   00:00

3.9. 备库恢复standby控制文件

备库库起到nomount阶段：

SYS@dgtest_s>shutdown immediate

SYS@dgtest_s>startup nomount;

rman恢复控制文件

RMAN> RESTORE STANDBY CONTROLFILE FROM '/u01/app/oracle/oradata/tmp/ctl.bak';

3.10. mount备库，并取消日志应用

SYS@dgtest_s> alter database mount;

SYS@dgtest_s>alter database recover managed standby database cancel;

3.11. 清空备库日志组

SYS@dgtest_s>ALTER DATABASE CLEAR LOGFILE GROUP 1;

 

Database altered.

注：如果采用了standby log模式，不需要清空，如果清空会出现

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

ALTER DATABASE CLEAR LOGFILE GROUP 1

*

ERROR at line 1:

ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 1 thread 1: '/u01/oradata/badly9/redo01.log'

说明：如果没有采用standby log模式，有几组需要清空几组

 

3.12. 备库重设flashback

SYS@dgtest_s>ALTER DATABASE FLASHBACK OFF;

SYS@dgtest_s>ALTER DATABASE FLASHBACK ON;

3.13. 备库开始应用日志

SYS@dgtest_s>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

 

4. 确认修复成功

在主库中执行

SYS@dgtest>alter system switch logfile;

分别主备库中执行select max(sequence#) from v$archived_log;如果一致标示修复成功

SYS@dgtest>select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

            81

 

SYS@dgtest_s>select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

            81

至此GAP修复完毕。