Gaining maximum benefits from Oracle Data Guard

When Oracle initially introduced the standby database concepts, the solo intention was to deliver the solutions to protect enterprise critical business data from any sort of natural and unnatural disasters. Since its innovation, Oracle has incorporated huge amount of new functionality/features and made several enhancements to ensure the standby database deliver and meet various user demands. This article is not

When Oracle initially introduced the standby database concepts, the solo intention was to deliver the solutions to protect enterprise critical business data from any sort of natural and unnatural disasters. Since its innovation, Oracle has incorporated huge amount of new functionality/features and made several enhancements to ensure the standby database deliver and meet various user demands.

This article is not intended to talk about Data Guard technologies and how to implement it. The goal of this article is to discuss the ideas and provide you the glimpse of those Data Guard features using which one can use to gain the maximum benefits, apart from the disaster recovery, from their Oracle data guard setup. In a nutshell, the following topics are explained:

  • Offload RMAN backups to standby database
  • Testing application existing/new functionality on standby
  • Real-time query over active standby database

Offload RMAN backups to standby database

Offloading any sort of workload to reduce the burden over a critical business production database is always a welcome decision. When you have an active standby database in place, you can take the advantages by scheduling/running all RMAN backups of the primary database on its standby database. The backups performed over the standby database are fully interchangeable, and requires no changes in the RMAN script or commands.

Keep in mind the following best practices when offloading RMAN backups over standby database:

  • Create and Maintain a catalog database
  • Configure ARCHIVE LOG deletion policy on PRIMARY-STNADBY databases:
    • PRIMARY-DB_RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
    • STANDBY-DB_RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
  • Turn on CONTROLFILE AUTOBACKUP and BACKUP OPTIMION on the standby database
  • Before running the RMAN backup scripts on the standby database, ensure you resync the catalog using the RESYNC CATALOG FROM db_unique_name ALL command
  • Configure Block Change Tracking (BCT) on a physical standby database to perform faster incremental backups

On the flipside, if the primary and standby databases are not in sync, then, you will be in a bad situation to perform backups. Therefore, you will have to ensure that the primary and standby are in sync before you perform RMAN backups on the standby database. Although the Maximum Availability Architecture (MAA) encourage taking backups from both the primary and standby, you can just have backups from standby database.

RMAN backups performed on standby database are fully interchangeable. The backups can be used to perform all sorts of restore, recover operations on primary as well standby databases. There is no change in the restore, recovery commands, the syntax are remains the same.

Testing application existing/new functionality

In general, whenever an application team wanted to test a certain functionality of an existing application or incorporating a new module, they typically request the DBAs to provide them a snapshot of the current database. Also, before configuring and deploying the application in DR environment, application might require a database in a fully updatable mode. As you might know, a standby database can’t be opened in a fully updateable mode, therefore, as a DBA it is going to be a challenging task for you to support the demands coming from the application owners/team members. Thankfully, the snapshot standby database feature can fulfill the demands.

An Oracle snapshot standby database feature allows you open the database in a fully updatable (read write) mode and the ability to roll back the changes to resume the standby synchronization from the point where it was stopped. When a standby database is converted to a snapshot standby database mode, the redo from the production will be shipped to the standby location; however, the redo will be not applied over the standby database. When the snapshot database revert back to standby database mode after all the testing, Oracle will resume the recovery from the point it was stopped and all the changes made temporary will be removed.

The following step-by-step procedure demonstrates how to convert a standby database to a snapshot database and vice versa:

  • STDBY_SQL> ALTER DATABASE CONVERT TO STANDBY DATABASE;
  • STDBY_SQL> SHUTDOWN IMMEDIATE
  • STDBY_SQL> STARTUP
  • STDBY_SQL> SELECT database_role,open_mode FROM v$database;

At the point, your physical standby database is converted to a full READ WRITE mode. You can ask the developers, application teams to test the database. Once the testing is been done and wanted to revert back the standby mode, do the following:

  • STDBY_SQL> SHUTDOWN IMMEDIATE
  • STDBY_SQL> STARTUP MOUNT
  • STDBY_SQL> ALTER DATABASE TO PHYSICAL STANDBY;
  • STDBY_SQL> SHUTDWON IMMEDIATE
  • STDBY_SQL> STARTUP MOUNT
  • STDBY_SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Review the standby database alert.log file to verify/confirm the recovering is taking place.

Active Standby database & Real time query

Pre Oracle 11g, a physical standby database either can be opened in a read only mode or recovery mode. If the standby is opened in a read only mode, despite the users can connect to the standby database and run their reports and queries, the data on the standby database will not be up to date with its primary database as during the course of database open in read only mode, recovery won’t be taking place. An Oracle active standby database a new feature introduced in Oracle 11g can open the database in READ ONLY mode whilst apply the recovering in the background. This way, the primary and the standby databases will be in sync and the users can get the exact copy of their production data.

Going further deeper with regards to the Active standby database and transaction consistency results over the standby database, you will have to enable the new real time query feature on the active standby database in order to ensure you get the transaction consistent results.

The procedure below demonstrates how to enable real-time query feature on active standby database:

  • Ensure the active standby database opened in a read only mode
  • STDBY_SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABSAE USING CURRENT LOGFILE;
  • STDBY_SQL> SELECT open_mode FROM v$database;
  • Set the maximum data delay parameter on the primary database
  • PRIMARY_SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=1;
  • Use the following to confirm the real-time query is enabled:
    • PRIMARY_SQL> SELECT recover_mode FROM v$archive_dest_status WHERE dest_id = 2;
      DEST_ID RECOVERY_MODE
      ------- ------------------------
      2 MANAGED REAL TIME APPLY

These are a few yet very power features of active standby database which can be used to get the maximum benefits from the standby database setup. These features will work on >=Oracle 11g version.