Oracle 数据保护物理备用数据库重做应用滞后时间约一个月

SYS@standby28> select database_role,open_mode from v$database;

DATABASE_ROLE                    OPEN_MODE
-------------------------------- ----------------------------------------
PHYSICAL STANDBY                 READ ONLY WITH APPLY

SYS@standby28> desc v$dataguard_stats;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(32)
 VALUE                                              VARCHAR2(64)
 UNIT                                               VARCHAR2(30)
 TIME_COMPUTED                                      VARCHAR2(30)
 DATUM_TIME                                         VARCHAR2(30)

SYS@standby28> set lines 200
SYS@standby28> col name for a25
SYS@standby28> col value for a30
SYS@standby28> col unit for a40
SYS@standby28> select name,value,unit from v$dataguard_stats;

NAME                      VALUE                          UNIT
------------------------- ------------------------------ ----------------------------------------
transport lag             +00 00:00:00                   day(2) to second(0) interval
apply lag                 +34 22:18:34                   day(2) to second(0) interval
apply finish time         +00 01:05:29.177               day(2) to second(3) interval
estimated startup time    30                             second

SYS@standby28> set pages 500
SYS@standby28> select t.sequence#,t.applied,t.first_time,t.next_time,t.completion_time
  2  from v$archived_log t
  3  where t.applied='NO'
  4  order by t.sequence# asc;

 SEQUENCE# APPLIED            FIRST_TIME          NEXT_TIME           COMPLETION_TIME
---------- ------------------ ------------------- ------------------- -------------------
......
     19900 NO                 2017-06-12 21:26:32 2017-06-12 21:46:03 2017-06-12 21:46:03
     19901 NO                 2017-06-12 21:46:03 2017-06-12 22:03:36 2017-06-12 22:03:37
     19902 NO                 2017-06-12 22:03:36 2017-06-12 22:17:38 2017-06-12 22:17:39
     19903 NO                 2017-06-12 22:17:38 2017-06-12 22:30:04 2017-06-12 22:30:04
     19904 NO                 2017-06-12 22:30:04 2017-06-12 22:34:29 2017-06-12 22:34:29
     19905 NO                 2017-06-12 22:34:29 2017-06-12 23:10:48 2017-06-12 23:10:48
     19906 NO                 2017-06-12 23:10:48 2017-06-13 00:58:12 2017-06-13 00:58:13
     19907 NO                 2017-06-13 00:58:12 2017-06-13 03:00:31 2017-06-13 03:00:32
     19908 NO                 2017-06-13 03:00:31 2017-06-13 07:21:10 2017-06-13 07:21:10
     19909 NO                 2017-06-13 07:21:10 2017-06-13 07:56:48 2017-06-13 07:56:49
     19910 NO                 2017-06-13 07:56:48 2017-06-13 08:12:56 2017-06-13 08:12:56
     19911 NO                 2017-06-13 08:12:56 2017-06-13 08:24:56 2017-06-13 08:24:56
     19912 NO                 2017-06-13 08:24:56 2017-06-13 08:35:26 2017-06-13 08:35:26
     19913 NO                 2017-06-13 08:35:26 2017-06-13 08:45:01 2017-06-13 08:45:01
     19914 NO                 2017-06-13 08:45:01 2017-06-13 08:53:56 2017-06-13 08:53:56
     19915 NO                 2017-06-13 08:53:56 2017-06-13 09:02:28 2017-06-13 09:02:29
     19916 NO                 2017-06-13 09:02:28 2017-06-13 09:10:51 2017-06-13 09:10:52
     19917 NO                 2017-06-13 09:10:51 2017-06-13 09:19:16 2017-06-13 09:19:16
     19918 NO                 2017-06-13 09:19:16 2017-06-13 09:27:27 2017-06-13 09:27:28
     19919 NO                 2017-06-13 09:27:27 2017-06-13 09:35:27 2017-06-13 09:35:28

1862 rows selected.

dg primary:
[oracle@orcl28 arch]$ ls -lrth
total 2.7G
-rw-r----- 1 oracle oinstall 160M Jun 12 23:10 1_19905_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 00:58 1_19906_910276848.dbf
-rw-r----- 1 oracle oinstall 176M Jun 13 03:00 1_19907_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 07:21 1_19908_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 07:56 1_19909_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:12 1_19910_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:24 1_19911_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:35 1_19912_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:45 1_19913_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:53 1_19914_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:02 1_19915_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:10 1_19916_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:19 1_19917_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:27 1_19918_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:35 1_19919_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:43 1_19920_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:52 1_19921_910276848.dbf

dg standby:
-rw-r----- 1 oracle oinstall 160M Jun 12 23:10 1_19905_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 00:58 1_19906_910276848.dbf
-rw-r----- 1 oracle oinstall 176M Jun 13 03:00 1_19907_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 07:21 1_19908_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 07:56 1_19909_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:12 1_19910_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:24 1_19911_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:35 1_19912_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:45 1_19913_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 08:53 1_19914_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:02 1_19915_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:10 1_19916_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:19 1_19917_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:27 1_19918_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:35 1_19919_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:43 1_19920_910276848.dbf
-rw-r----- 1 oracle oinstall 160M Jun 13 09:52 1_19921_910276848.dbf

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.

 

On Physical standby

SQL> select * from v$archive_gap;

On Logical standby

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 ‘’;
 Logical Standby:

SQL> alter database register logical logfile ‘’;
 

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