Oracle DG同步失败问题处理(一)

IT小Chen 2021-04-14

7315

环境：

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

---警告日志：

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

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-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

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

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

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

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;

----------

10 rows selected.

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle DG同步失败问题处理(一)

评论