Oracle_dg归档丢失问题处理

Oracle_dg归档丢失问题处理

环境：
OS：RedHat 6.3 
DB: Oracle Database 11g Enterprise Edition Release 12.1.0.2.0

故障： 主,备库归档文件只保留7天，超过7天的归档会自动删除；
由于备库服务器停机过一段时间，导致主库部分归档还没有传到备库，就已经被删除了；

解决方案：
(1)如果数据量很小，可以考虑重建备库；
(2)如果数据量很大，可以使用Rman基于SCN的增量备份来修复GAP问题；

本文主要讲解第二种解决方案：

Rman基于SCN的增量备份来修复GAP
(1)问题现象
(2)查找主库中是否存在产生GAP的归档文件
(3)确定增量恢复的起始SCN号
(4)主库:使用Rman基于SCN的增量备份
(5)备库:恢复
(6)测试

(1)问题现象
---备库无法open，报错无法获取sequence# 43968
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-16043: Redo apply has been canceled.
ORA-16016: archived log for thread 1 sequence# 43968 unavailable
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave

---警告日志：
Wed Nov 15 16:04:56 2017
Errors in file /u01/app/oracle/diag/rdbms/standby_orcl/orcl/trace/orcl_pr00_13628.trc:
ORA-16043: Redo apply has been canceled.
ORA-16016: archived log for thread 1 sequence# 43968 unavailable
Wed Nov 15 16:04:56 2017
Standby Crash Recovery aborted due to error 10877.
Wed Nov 15 16:04:56 2017
Errors in file /u01/app/oracle/diag/rdbms/standby_orcl/orcl/trace/orcl_ora_12435.trc:
ORA-10877: error signaled in parallel recovery slave 
ORA-10877: error signaled in parallel recovery slave 
Wed Nov 15 16:04:56 2017
Completed Standby Crash Recovery.
Wed Nov 15 16:04:56 2017
Errors in file /u01/app/oracle/diag/rdbms/standby_orcl/orcl/trace/orcl_ora_12435.trc:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-16043: Redo apply has been canceled.
ORA-16016: archived log for thread 1 sequence# 43968 unavailable
ORA-10877: error signaled in parallel recovery slave 
ORA-10877: error signaled in parallel recovery slave 
ORA-10877: error signaled in parallel recovery slave 
ORA-10877: error signaled in parallel recovery slave 
ORA-10877: error signaled in parallel recovery slave 
ORA-10877: error signaled in parallel recovery slave 
ORA-10877: error signaled in parallel recovery slave 
ORA-10458 signalled during: alter database open...

(2)查找主库中是否存在产生GAP的归档文件
---备库gap
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1         43968          50948

---主库
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 43968 AND 50948;
NAME
--------------------------------------------------------------------------------
......
/u01/ora_arch/1_50943_910299442.dbf
/u01/ora_arch/1_50944_910299442.dbf
/u01/ora_arch/1_50945_910299442.dbf
/u01/ora_arch/1_50946_910299442.dbf
/u01/ora_arch/1_50947_910299442.dbf
/u01/ora_arch/1_50948_910299442.dbf

3966 rows selected.

---发现主备部分归档文件已经自动删除，无法直接恢复
[root@CHENorcdb1 ~]# cd /u01/ora_arch/
[root@CHENorcdb1 ora_arch]# ll -rth > 1116.txt
[root@CHENorcdb1 ora_arch]# vi 1116.txt
......

(3)确定增量恢复的起始SCN号
---备库
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         7            1812569
         5            1812569
         4         1879904814
         6         1879904814
        14         1879904814
         9         1879904814
        10         1879904814
        11         1879904814
        12         1879904814
        13         1879904814
         3         1879904814
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1         1879904814
         8         1879904814

13 rows selected.

主库：
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         7            1812569
         5            1812569
         4         2205980530
         6         2205980530
        14         2205980530
         9         2205980530
        10         2205980530
        11         2205980530
        12         2205980530
        13         2205980530
         3         2205980530
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1         2205980530
         8         2205980530

13 rows selected.

(4)主库:使用Rman基于SCN的增量备份
[oracle@CHENorcdb1 ~]$ rman target / 
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Nov 16 13:18:45 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1438080174)
RMAN> 
run
{
allocate channel c3 device type disk; 
backup as compressed backupset incremental from scn 1879904814 database format '/home/oracle/rman1116/%U';
release channel c3;
}

---将备份传到备库
[oracle@CHENorcdb1 oracle]# tar -zcvf rman1116.tar.gz rman1116/
rman1116/
rman1116/0vsjna6b_1_1
rman1116/10sjna9v_1_1
rman1116/12sjnabm_1_1

[oracle@CHENorcdb1 ~]$ scp -P 49622 rman1116.tar.gz 192.168.5.50:/home/oracle/
oracle@192.168.5.50's password: 
rman1116.tar.gz                               100%  496MB  99.1MB/s   00:05

(5)备库:恢复
[oracle@CHENorcdb2 ~]$ tar -zxvf rman1116.tar.gz 

---1 nomount备库
SQL> shutdown immediate.
SQL> startup nomount

---2 通过备份恢复控制文件
RMAN>  restore standby controlfile from '/home/oracle/rman1116/12sjnabm_1_1';
Starting restore at 16-NOV-17
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/orcl/standby_control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/standby_control02.ctl
Finished restore at 16-NOV-17

---3 mount备库
SQL> alter database mount standby database;
Database altered.

---4 恢复备库
RMAN> catalog start with '/home/oracle/rman1116';
RMAN> recover database;
......
archived log file name=/u01/ora_arch/1_51211_910299442.dbf thread=1 sequence=51211
media recovery complete, elapsed time: 00:00:05
Finished recover at 16-NOV-17

(6)验证
---1 open备库
SQL> alter database open;
---3 启动应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
---4 查看归档GAP
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
---5 查看当前序列号
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         7            1812569
         5            1812569
         4         2206259949
         6         2206259949
        14         2206259949
         9         2206259949
        10         2206259949
        11         2206259949
        12         2206259949
        13         2206259949
         3         2206259949
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1         2206259949
         8         2206259949

13 rows selected.

---主库:创建测试数据
SQL> create table test1116 as select level as id from dual connect by level <=10;
---备库:查询测试数据
SQL> select * from test1116;
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2147373/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2147373/