Oracle Data Guard Protection Modes for non-DBAs

Oracle Data Guard offers three different types of data protection modes – Maximum Performance, Maximum Availability and Maximum Protection. The names that Oracle has chosen to describe the protection modes may be confusing. If you need to explain the protection modes to the technical management team, then the following terminology may help you.

Asynchronous

• Primary database transactions (redo information) are replicated asynchronously to the standby database with respect to the primary database transactions.
• Transactions are confirmed as committed as soon as they are written to the disks on primary server.
• Commit Performance on the primary is solely dependent upon the performance of the primary database and server.
• This protection mode has minimal impact on the performance of the primary database.
• This protection may incur minimal data loss.
• Default Protection mode

Synchronous, Best Efforts

• Primary database transactions (redo information) are replicated synchronously to the standby database with respect to the primary database transactions.
• If standby database becomes unavailable, then Oracle falls back to asynchronous protection mode.
• Transactions are confirmed as committed as soon as they are written to the disks on primary server and secondary server.
• Commit Performance on the primary is dependent upon the performance of the primary database and standby database. Commit Performance also depends upon the network latency between the primary and standby databases.
• This protection mode has impact on primary database performance as COMMITs are completed only when transactions are confirmed as written on the primary and standby servers.
• This protection mode offers no data loss as long as standby database is available.

Synchronous

• Primary database transactions (redo information) are replicated synchronously to the standby database with respect to the primary database transactions.
• If standby database becomes unavailable, then the primary database hangs.
• Transactions are committed as soon as writes to the disks on primary and secondary are completed.
• Commit Performance on the primary is dependent upon the performance of the primary database and standby database. Commit Performance also depends upon the network latency between the primary and standby databases.
• This protection mode has impact on primary database performance as COMMITs are completed only when transactions are confirmed as written on the primary and standby servers. In addition, the primary database may hang if the standby database becomes unavailable.
• This protection mode offers no data loss

Recover a Failed Primary Database in Oracle10g Data Guard Configuration

When you failover a primary database to a physical standby database, the standby database assumes the role of the primary database. Oracle records the SCN at which the standby database became new primary in V$DATABASE.STANDBY_BECAME_PRIMARY_SCN. From this point onwards, SCN chain is advanced in the new primary database by all subsequent transactions. Now the former primary database can no longer participate in the Data Guard configuration unless and until it is recovered upto SCN stored in V$DATABASE.STANDBY_BECAME_PRIMARY_SCN.

After resolving failover issues, you may be able to restart the former primary database, but this database is out of SCN alignment and you must recover it. Once you recover the former primary database, it can be joined in Data Guard configuration.

If enabled, Flashback Backup Logs may be the quickest and easiest option to perform the recovery. Otherwise, RMAN backups with archived logs would get you the desired result.

Perform the following query to obtain the SCN at which the physical standby database became the primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

To recover database using RMAN, run the following RMAN block:

RMAN> RUN

    { SET UNTIL SCN <standby_became_primary_scn + 1>;

      RESTORE DATABASE; 

      RECOVER DATABASE;

    }

With Flashback option enabled, run the following:

SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

Please note a subtle difference in the above-mentioned two recovery options. RMAN sets SCN at standby_became_primary_scn + 1, but Flashback database option uses standby_became_primary_scn. If you are wondering why, then here is the explanation:

SCN in RMAN block specifies an upper, non-inclusive limit. RMAN selects only files that can be used to restore or recover up to but not including the specified SCN. Flashback Database specifies an inclusive SCN value. Flashback database recovers upto, including standby_became_primary_scn.