Data Gaurd Gap Detection and resolved gap

When we using Dataguard, physical standby can go out of sync with the primary database. Before doing anything we need to verify why standby is not in sync state with primary database. We can use of v$archived_log and gv$archived_log views for gap detection. In above views we can check status of APPLIED filed for last sequence number that it has YES or NO value. Therefore If APPLIED=YES then archivelog file applied to standby and If APPLIED=NO this means that due to the missing log MRP is not applying the logs on standby database.

1-ON primary and Standby Database:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

Or

SQL> SELECT SEQUENCE#, APPLIED FROM gV$ARCHIVED_LOG;

SEQUENCE#                      APPLIED

-------------------    ---------------------

      658                         YES

      659                          NO

      660                          NO

As you see we have gap between primary and standby  this means from sequence number 659 doesn’t apply on the standby.

2-Now we can take an incremental backup of primary from SCN where standby is lagging and apply on standby.

On Standby database query v$database iew and record current SCN of the standby database:

SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)

----------------------------------------
15720350562

Stop Redo apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Connect to the primary database as the RMAN target and create an incremental backup from the current SCN of the standby database that was record.

RMAN>BACKUP INCREMENTAL FROM SCN 15720350562 DATABASE FORMAT '/backup/BackupForStandby_%U' ;

3-Copy taken backup from primary to standby database and do a recovery of standby database using the incremental backup.at first you should catalog your backup piece.

$ rman nocatalog target /

RMAN> CATALOG BACKUPPIECE '/test/BackupForStandby _1re6yu5_1_1';

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 2015-12-14 09:23:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
....
..
..
.
channel ORA_DISK_1: reading from backup piece /backup/BackupForStandby_%U
channel ORA_DISK_1: restored backup piece 1
piece handle/backup/BackupForStandby_%U channel ORA_DISK_1: restore complete, elapsed time: 01:53:08
Finished recover at 2008-07-25 05:20:3

After that start the managed recover :

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Now you can check value of APPLIED for last sequence number on standby and primary database:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

Or

SQL> SELECT SEQUENCE#, APPLIED FROM gV$ARCHIVED_LOG;

SEQUENCE#                   APPLIED

-------------------    ---------------------

      658                               YES

      659                               YES

      660                               YES

The most important Data Guard Background processes

• Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence. 

• Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database. the Remote File Server (RFS) process receives the redo data and writes it to a Standby Redo Log (SRL). RFS sends an acknowledgement back to the production database that the redo has been received and written to disk. When the production database receives this acknowledgement, it acknowledges the commit to the client application and processes the next transaction. 

• Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).

• Managed Recovery Process (MRP) - Applies archive redo log information to the standby database.

• Logical Standby Process (LSP) - The LSP applies the redo records from archived redo logs to the logical standby database. The Oracle database log miner engine is used by the logical standby process for the SQL apply operations. Using the log miner engine, the LSP process recreates the SQL statements from redo logs that have been executed on the primary database. These statements are then applied to the standby database to keep it current with the primary database.

Change Data guard Protection Mode

Change data guard protection mode from Maximum Performance to Maximum Availability.

The default is Maximum Performance.

SYNC | ASYNC = Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM = Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT = Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN = Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

Use LGWR SYNC if you use either Maximum Protection OR Maximum Availability. 
so here LGWR is directly connected with RFS,henc there will be no LNS process. 

Check Primary database:

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;



 PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

---On Primary Database

 SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;


  STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
  ------ ------------- ------------- --------------------

  OPEN    orcl          PRIMARY       MAXIMUM PERFORMANCE

 SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTBY LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstby' scope=both;

 SQL> alter database set standby database to maximize availability;

---On Standby Database

 SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

 SQL> alter database recover managed standby database cancel;

 SQL>  alter system set log_archive_dest_2='SERVICE=ORCL LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;

 SQL> alter database recover managed standby database using current logfile disconnect from session;

 stby->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

 PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


 prod-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

 PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------

 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Apply logs on standby system with time delay or lag

We would like the standby database running with a delay or lag, for example 4 hours. 

The primary database will transport all archive-information to the standby-Server at once = normaly. But the standby database applies the changes always 4 hours later. So, if we recognize a problem like a bad Update/delete,... we will open the standby in read only mode and read the old, correct data.

Variable: APPLY_DELAY_LAG_MINUTES

Default value: 0

Description: This creates a lag time between the archiving of a redo log at the primary database and the application of the log on the standby database.

Note that the archive logs are still transferred to the standby server as normal without delay.

To turn off APPLY_DELAY_LAG_MINUTES, set to 0.

set in spfile :

APPLY_DELAY_LAG_MINUTES=240

Summary of Physical Data Guard Concepts

Data Guard is unique among Oracle replication solutions in supporting both synchronous
(zero ata loss) and asynchronous (near-zero data loss) configurations.supporting ad-hoc queries, reporting, backups, or test activity.

Types of standby databases:

- physical standby : uses Redo Apply.Physical standby databases provide the best disaster recovery    (DR) protection for the Oracle Database.(block-for-block basis).

- Logical  standby : uses SQL Apply.

Standby roles :

- physical standby : not read and not write.

        - Active Data Guard : enables a physical standby database to be used for read-only applications.

-Snapshot Standby   : enables a physical standby database to be open read-write for testing.
A snapshot standby database receives and archives, but does not apply, redo data from its primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A standby database is initially created from a backup copy of the primary database.

Data Guard Services:

- Log Transport Services : Data Guard transport services transmit the redo directly from the primary database log buffer to the standby database(s) where it is written to a standby redo log file.

- Log Apply Services : on the standby database read redo records from a standby redo log file, perform continuous Oracle validation to insure
that the redo is not corrupt, and then applies redo changes to the standby database.

- Role Management Services : Change the role of a database from a standby database to a primary database,
or from a primary database to a standby database using either a switchover or a
failover operation.


Data Guard  two types of replication : Data guard is similar repilication without capturing

- synchronous : primary database wait for confirmation from a standby database that redo has been received and written to disk (a standby redo log file).Data Guard Maximum Protection mode.

- asynchronous : Primary without waiting for acknowledgment that redo has been received by the standby database.(Maximum Performance)

Protection Modes :

- Maximum Protection : Zero data loss Double failure protection - SYNC -Signal commit success to the application only after acknowledgement is received from a standby database that redo for that transaction is hardened to disk.

- Maximum Availability : Zero data loss Single failure protection - SYNC -Signal commit success to the application only after acknowledgement is received from a standby database or after NET_TIMEOUT threshold period expires – whichever occurs first.

- Maximum Performance : Potential for minimal data loss - ASYNC - Primary never waits for standby acknowledgment to signal commit success to the application.

Managed Recovery Process (MRP) : A physical standby database applies redo received from the primary using MRP.