dataguard 中standby有大量gap解决方法
当前遇到的问题Oracle dataguard环境,Standby很长一段时间没有启用,再次启用standby时,产生大量的归档gap。环境介绍Oracle dataguard环境,主库和备库均为单实例,并且软件和硬件环境都相同操作系统Red Hat Enterprise Linux Server release 5.5 (Tikanga)Linux ncdb 2.6
当前遇到的问题
Oracle dataguard环境,Standby 很长一段时间没有启用,再次启用standby时,产生大量的归档gap。
环境介绍
Oracle dataguard环境,主库和备库均为单实例,并且软件和硬件环境都相同
操作系统
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Linux ncdb 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64x86_64 x86_64 GNU/Linux
数据库
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 – Production
判断依据
经过仔细检查,primary和standby的其他都没有问题,问题定位在standby的gap
Standby查询v$archive_gap
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE#HIGH_SEQUENCE#
---------- ------------- --------------
1 482 3713
Standby的告警日志信息中没有error和ORA,
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 482
Fetching gap sequence in thread 1, gap sequence482-581
解决方法:
解决gap的方法有两种,方法虽然略有不同,但是原理是相同的
一、gap较少,可以直接将缺少的归档scp到standby,在standby手工注册下即可
二、gap较多,在primary 做基于scn的backup,同时创建一个新的standbycontrolfile
,将备份好的backupset ,standbycontrolfile 拷贝的备库的相应目录下,进行restore、recover的操作即可
因为这个案例中,standby丢失的归档太多,推荐用第二种方法,同时,下面的操作也是使用的第二种方法
操作流程提纲:
(1) standby 取消recover
(2) 查询gap中LOW_SEQUENCE#-1对应的scn
(3) 在primary做基于该scn的增量备份
(4) 在primary创建新的standby controlfile
(5) 将增量的备份集和创建好的standby controlfile 拷贝的备库
(6) 备库shutdown
(7) 使用新的standby controlfile 启动备库到mount
(8) Standby 做recover
(9) 验证结果
具体操作步骤
(1) standby取消recover
SQL> alter database recover managed standby databasecancel;
(2) 查询gap中LOW_SEQUENCE#-1对应的scn
SQL>select THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#from v$archived_log
2 where SEQUENCE#=481;
THREAD# SEQUENCE# FIRST_CHANGE#NEXT_CHANGE#
---------- ---------- ------------- ------------
1 481 542543 551725
(3) 在primary做基于该scn的增量备份
RMAN> run {
2> allocate channel c1device type disk;
3> allocate channel c2device type disk;
4> backup incremental from scn 542543 databaseformat '/oradata/bak/ora_scn_%U.bak'; #incremental单词不要写错
5> release channel c1;
6> }
(4) 在primary创建standby controlfile
SQL> alterdatabase create standby controlfile as '/oradata/bak/control.ctl';
(5) 将增量备份集和创建好的standby controlfile 一起拷贝备库相应目录下。
(6) 备库shutdown
SQL> shutdownimmediate
(7) 使用新的standby controlfile 启动备库到mount
SQL> startup nomount;
ORACLE instance started.
Total System Global Area1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 385876536 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14692352 bytes
SQL> alterdatabase mount;
(8) Standby 做recover(需要注意的步骤)
RMAN> catalogstart with '/oradata/bak/ora_scn_05ohoqvu_1_1'; #放在standby的增量备份的备份集
using target databasecontrol file instead of recovery catalog
searching for all filesthat match the pattern /oradata/bak/ora_scn_05ohoqvu_1_1
List of Files Unknown tothe Database
=====================================
File Name:/oradata/bak/ora_scn_05ohoqvu_1_1
Do you really want to catalog theabove files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:/oradata/bak/ora_scn_05ohoqvu_1_1
RMAN> recover database noredo;
Starting recover at 20-8月 -13
allocated channel:ORA_DISK_1
channel ORA_DISK_1: sid=540devtype=DISK
channel ORA_DISK_1:starting incremental datafile backupset restore
channel ORA_DISK_1:specifying datafile(s) to restore from backup set
destination for restore ofdatafile 00001: /oradata/NCDB/system01.dbf
destination for restore ofdatafile 00002: /oradata/NCDB/undotbs01.dbf
destination for restore ofdatafile 00003: /oradata/NCDB/sysaux01.dbf
destination for restore ofdatafile 00004: /oradata/NCDB/users01.dbf
destination for restore ofdatafile 00005: /oradata/NCDB/nnc_data01.dbf
destination for restore ofdatafile 00006: /oradata/NCDB/nnc_data02.dbf
destination for restore ofdatafile 00007: /oradata/NCDB/nnc_data03.dbf
destination for restore ofdatafile 00008: /oradata/NCDB/nnc_index01.dbf
destination for restore ofdatafile 00009: /oradata/NCDB/nnc_index02.dbf
destination for restore ofdatafile 00010: /oradata/NCDB/nnc_index03.dbf
destination for restore ofdatafile 00011: /oradata/NCDB/nnc_iufo01.dbf
channel ORA_DISK_1: reading frombackup piece /oradata/bak/ora_scn_05ohoqvu_1_1
channel ORA_DISK_1:restored backup piece 1
piecehandle=/oradata/bak/ora_scn_05ohoqvu_1_1 tag=TAG20130820T095718
channel ORA_DISK_1: restore complete, elapsed time: 00:02:56
Finished recoverat 20-8月 -13
此时的告警日志信息:
...
Tue Aug 20 11:24:46 2013
alter database recovercancel
Tue Aug 20 11:24:47 2013
Media Recovery Canceled
Completed: alter database recovercancel
Tue Aug 20 11:26:01 2013
Redo Shipping ClientConnected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFSprocess 22461
RFS[1]: Identified databasetype as 'physical standby'
Tue Aug 20 11:26:01 2013
RFS LogMiner: Client disabled from further notification
Tue Aug 20 11:30:18 2013
Incremental restore complete of datafile 3 /oradata/NCDB/sysaux01.dbf
checkpoint is 107026909
last deallocation scn is 612835
Tue Aug 20 11:30:48 2013
Incremental restore complete of datafile 1 /oradata/NCDB/system01.dbf
checkpoint is 107026909
last deallocation scn is 615547
Tue Aug 20 11:31:11 2013
Incremental restore complete of datafile 2 /oradata/NCDB/undotbs01.dbf
checkpoint is 107026909
last deallocation scn is 662518
Incremental restore complete of datafile 4 /oradata/NCDB/users01.dbf
checkpoint is 107026909
Incremental restore complete of datafile 6 /oradata/NCDB/nnc_data02.dbf
checkpoint is 107026909
Incremental restore complete of datafile 7 /oradata/NCDB/nnc_data03.dbf
checkpoint is 107026909
Incremental restore complete of datafile 8 /oradata/NCDB/nnc_index01.dbf
checkpoint is 107026909
Incremental restore complete of datafile 9 /oradata/NCDB/nnc_index02.dbf
checkpoint is 107026909
Incremental restore complete of datafile 10/oradata/NCDB/nnc_index03.dbf
checkpoint is 107026909
Incremental restore complete of datafile 11 /oradata/NCDB/nnc_iufo01.dbf
checkpoint is 107026909
Tue Aug 20 11:31:31 2013
Incremental restorecomplete ofdatafile 5 /oradata/NCDB/nnc_data01.dbf
checkpoint is 107026909
Tue Aug 20 11:34:16 2013
(9) 验证结果
Standby 执行接收并恢复日志操作
SQL> alterdatabase recover managed standby database disconnect from session;
SQL> select * fromv$archive_gap;
no rows selected
SQL> select THREAD#,max(SEQUENCE#) from v$archived_log
2 group by THREAD#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 3729
Primary端验证结果
SQL> select THREAD# ,max(SEQUENCE#) from v$archived_log
2 group by THREAD#;
THREAD# MAX(SEQUENCE#)
--------- --------------
1 3729
Primary进行日志切换,查看standby告警日志:
Fetching gapsequence in thread 1, gap sequence 3727-3727
Tue Aug 2011:36:57 2013
RFS[1]: ArchivedLog: '/oradata/arch/1_3727_753900804.arc'
Tue Aug 2011:37:27 2013
Media RecoveryLog /oradata/arch/1_3727_753900804.arc
Media RecoveryLog /oradata/arch/1_3728_753900804.arc
Media RecoveryWaiting for thread 1 sequence 3729
Tue Aug 2011:37:57 2013
RFS[1]: ArchivedLog: '/oradata/arch/1_3729_753900804.arc'
Tue Aug 20 11:37:582013
Media RecoveryLog /oradata/arch/1_3729_753900804.arc
Media RecoveryWaiting for thread 1 sequence 3730
更多推荐
所有评论(0)