Physical dataguard 如何自动解决archive log gap

1 下文提到的第一种方式测试（此种方式是针对Primary的日志根本没有在standby control file里注册的情况）：

 关闭standby database，在primary 上做几次switch logfile后，再开启standby。

观察standby启动的process,最开始就只有arch

select process from v$managed_standby;

ARCH

ARCH

此时观察 standby 的alert 日志，发现并没有从primary收到日志的记录。

等了一下，RFS启动了。

SQL> select process from v$managed_standby;


PROCESS
---------
ARCH
ARCH
RFS

RFS启动后，alert日志里面发现RFS开始接收日志，虽然这个时候MRP进程没有开启来。

RFS[1]: Assigned to RFS process 3232
RFS[1]: Identified database type as 'physical standby'
Mon May 26 20:33:40 China Standard Time 2014
RFS LogMiner: Client disabled from further notification
RFS[1]: Archived Log: 'E:\TESTDGP\ARCHIVE\1_577_847819895.ARC'
RFS[1]: Archived Log: 'E:\TESTDGP\ARCHIVE\1_578_847819895.ARC'
RFS[1]: Archived Log: 'E:\TESTDGP\ARCHIVE\1_579_847819895.ARC'
RFS[1]: Archived Log: 'E:\TESTDGP\ARCHIVE\1_580_847819895.ARC'
Mon May 26 20:33:46 China Standard Time 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 2452
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: No standby redo logfiles selected (reason:7)
Mon May 26 20:33:47 China Standard Time 2014
Errors in file c:\oracle\product\10.2.0\admin\testdgp\udump\testdgp_rfs_2452.trc:
ORA-16086: standby database does not contain available standby log files

2 日志已经在standby controlfiel注册了，但是standby的日志在被应用之前就被删除或corrupt了

此时是MPR进程利用参数FAL_SERVER，到FAL_SERVER里指定的service name去取日志（前提是日志还在service name指定位置的服务器上，硬盘及对应在control file 的记录都要存在）

接上一个实验，此时在开启MRP进程之前将还未应用的日志删除。此时第一种的自动侦测功能是不会去解决这个 gap的。

只有开启了MRP进程，即alter database recover managed standby database disconnect;

观察MRP进程的状态：

SQL> select process,status from v$managed_standby;


PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
RFS       IDLE
MRP0      WAIT_FOR_GAP

alert日志里面：

Media Recovery Log E:\TESTDGP\ARCHIVE\1_577_847819895.ARC
Media Recovery Log E:\TESTDGP\ARCHIVE\1_578_847819895.ARC
Media Recovery Log E:\TESTDGP\ARCHIVE\1_579_847819895.ARC
Media Recovery Log E:\TESTDGP\ARCHIVE\1_580_847819895.ARC
Media Recovery Log E:\TESTDGP\ARCHIVE\1_581_847819895.ARC
Media Recovery Log E:\TESTDGP\ARCHIVE\1_582_847819895.ARC
Media Recovery Log E:\TESTDGP\ARCHIVE\1_583_847819895.ARC
Error opening E:\TESTDGP\ARCHIVE\1_583_847819895.ARC
Attempting refetch
Media Recovery Waiting for thread 1 sequence 583
Fetching gap sequence in thread 1, gap sequence 583-583
Mon May 26 21:14:41 China Standard Time 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 3936
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Allowing overwrite of partial archivelog for thread 1 sequence 583
RFS[3]: Archived Log: 'E:\TESTDGP\ARCHIVE\1_583_847819895.ARC'
Mon May 26 21:15:09 China Standard Time 2014
Media Recovery Log E:\TESTDGP\ARCHIVE\1_583_847819895.ARC
Media Recovery Log E:\TESTDGP\ARCHIVE\1_584_847819895.ARC
Error opening E:\TESTDGP\ARCHIVE\1_584_847819895.ARC
Attempting refetch
Media Recovery Waiting for thread 1 sequence 584
Fetching gap sequence in thread 1, gap sequence 584-584
Mon May 26 21:15:11 China Standard Time 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid

参考资料：

Data Guard Gap Detection and Resolution Possibilities (文档 ID 1537316.1)

In this Document
Purpose
Details
  What is an Archive Gap ??
  Methods of Gap Resolution
  Automatic Gap Resolution
  FAL (Fetch Archive Log) Gap Resolution
  Manual Gap Resolution
  Roll forward using Incremental Backup (Physical Standby only)
APPLIES TO:


Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
PURPOSE


 This Document show the various Possibilities to detect and resolve a Redo Gap on a Standby Database.


 


Would you like to explore this Topic further with other Oracle Customers, Oracle Employees and Industry Experts ??


You can discuss this Note, show your Experiences or ask Questions about it directly right at the Bottom of this Note in the Discussion Thread about this Document.


If you want to discover Discussions about other Articles and Subjects or even post new Discussions you can access the My Oracle Support Community Page for High Availability Data Guard


 


DETAILS


 


What is an Archive Gap ??


 


An Archive Gap is a Range of missing Redo on the Standby Site that prevents Log Apply Services to proceed. This typically happens when the Standby Site is unable to receive Redo from the Primary Database or the Redo Information is not available on the Standby Database. Possible and common Causes for Archive Gaps are:


Network Disconnects or stop of Log Transport Services
Outages of the Standby Database
Misconfigurations of Log Transport Services
I/O-Issues on the Standby Site
Manual Deletion of ArchiveLogs before they are applied to the Standby
Insufficient Bandwith in the Network between the Primary and Standby Site
Once there is an Archive Gap on the Standby Database the Log Apply Services will get stuck until the Gap is resolved, ie. the missing Redo in Form of ArchiveLogs is fetched and made available on the Standby Site. Log Apply Services then can pick it up and proceed.


 


Methods of Gap Resolution


 


There are 4 Possibilities to resolve an Archive Gap on a Standby Database. Those are discussed below.


Automatic Gap Resolution


Automatic Gap Resolution is performed automatically by the Log Transport Services. Basically the currently transferred Redo is compared with the last received. If there is a Mismatch, it is detected by the receiving RFS-Process on the Standby Database, which will automatically request the missing Log Sequence from the Primary Database again via the ARCH-RFS Heartbeat Ping. This Type of Gap Resolution is using the Service defined in log_archive_dest_n on the Primary Database serving this Standby Database. In Addition the ARCH-RFS Heartbeat Ping performs a Poll of the current Sequence to detect an Archive Gap. If there is one detected it will get resolved the same Way. Once a Gap is resolved the Transport Process (ARCH or LGWR) is notified about the Resolution of the Gap. For Automatic Gap Resolution there is no special Setting or monitoring required.


 


FAL (Fetch Archive Log) Gap Resolution


Once an ArchiveLog is received or archived from a Standby RedoLog on the Standby Database, it is registered in the Standby Controlfile (you can query the Registration by v$archived_log on a Physical Standby Database and dba_logstdby_log on a Logical Standby Database). If such a File is missing or corrupted for any Reason (eg. it got deleted by Fault), FAL is called to perform a Gap Resolution. This is the Case because such missing Logfiles are typically detected by the Log Apply Services on the Standby Database. Those are working independent from the Log Transport Services and do not have a direct Link to the Primary Database. To use FAL, there must be one or two (prior Oracle 11.2.0) Initialization Parameters setup on the Standby Database:


FAL_SERVER: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points to the Database from where the missing ArchiveLog(s) should be requested. This can either be the Primary Database, but also another Standby-, ArchiveLog Repository- or Far Sync Standby (> Oracle 12.1.0) Database inside the Data Guard Configuration. It is possible to specify multiple Service Names (Comma separated). FAL will then sequentially attempt those Databases to resolve the Gap.


FAL_CLIENT (< Oracle 11.2.0): Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points from the FAL_SERVER Database(s) back to the Standby Database (ie. that’s the Destination where the FAL_SERVER Database should send the Redo to). Ensure this TNS-Alias exists in the TNSNAMES.ORA of your FAL_SERVER Database(s). This Parameter is not required any more since Oracle 11.2.0. However you have to ensure there exists a corresponding log_archive_dest_n on your FAL_SERVER Database(s) which is pointing to the Standby Database requesting the Gap Resolution.


Once the Log Apply Services detect an Archive Gap it sends a FAL Request to the FAL_SERVER handing over the FAL_CLIENT (or db_unique_name for Version > 11.1.0). An ARCH-Process on the FAL_SERVER tries to pick up the request Sequence(s) from that Database and sends it back to the FAL_CLIENT (or uses the Destination valid for this db_unique_name). If the first FAL_SERVER is not able to resolve the Gap, the next FAL_SERVER in the List will be attempted. If it cannot be resolved by all FAL_SERVERs the FAL-Request fails and a corresponding Message will be put in the ALERT.LOG of the Standby Database.


In order to successfully complete a Gap Request the requested ArchiveLog Sequence(s) must be available on the FAL_SERVER Database (on Disk and the corresponding Entry in the Controlfile).


FAL is available since Oracle 9.2.0 for Physical Standby Database and Oracle 10.1.0 for Logical Standby Databases.


 


Manual Gap Resolution


If an Archive Gap cannot be resolved automatically by any of the previously mentioned Methods, you can still try to manually resolve an Archive Gap.


You can query v$archive_gap on a Physical Standby Database or dba_logstdby_log on the Logical Standby Database to determine a current Archive Gap, eg.


 


SQL> select * from v$archive_gap;


SQL> select thread#, sequence# from dba_logstdby_log l where next_change# not in


     (select first_change# from dba_logstdby_log where l.thread#=thread#)


     order by thread#, sequence#;


 


Now copy the returned Sequences to the Standby Database manually to the desired Location. If the missing are not yet registered on the Standby Database, you have to register them before the Log Apply Services are able to read those Logfiles. You can register ArchiveLogs using


Physical Standby:


SQL> alter database register logfile ‘<File-Specification>’;
 Logical Standby:


SQL> alter database register logical logfile ‘<File-Specification>’;
 


Once they are registered Log Apply Services will pick up the ArchiveLogs and proceed.


 


Roll forward using Incremental Backup (Physical Standby only)


If a Gap cannot be resolved with any of the previous Methods, the Gap is quite large and may take a long Time to resolve or the Gap cannot be resolved because the missing ArchiveLogs do not exist on any Database any more, you can still roll forward a Physical Standby Database using an incremental Backup from SCN. This Feature is available since Oracle 10.2.0. The Idea is to record the latest SCN applied to the Standby Database, then create an incremental Backup from that SCN on Primary Database using RMAN together with a Backup of the current Controlfile (as Standby Controlfile).


Then we first replace the old Standby Controlfile with the Standby Controlfile from the incremental Backup and apply the incremental Backup on the Standby Database. This is a real fast and easy Way to bring a Standby Database back close to the current Status of the Primary Database. Since the Steps to take can be different in the various Releases, please look into Chapter


“Using RMAN Incremental Backups to Roll Forward a Physical Standby Database”


in the


“Oracle Data Guard Concepts and Administration”


Documentation for the Version you are using. There is a detailed Step-by-Step Instruction for each Version.


 


You can directly participate in the Discussion about this Note below. The Frame is the interactive live Discussion - not a Screenshot ;-)