Testing a Physical Standby Database in Oracle 10g

Jan 21, 2009 / By Marcelo Lopes

Tags:

If you don’t want to deal with missteps, I recommend that you test your standby database to facilitate the failover or switchover process.

This procedure is very useful when you have physical standby databases for testing and other purposes that require read-write access to the standby database. Also, it improves your checklist in the event of an error or disaster.

By using Snapshot standby databases, redo data is not applied until you convert the snapshot standby database back into a physical standby database, and after all local updates to the snapshot standby database are discarded.

Requirements

The following requirements need to be met in order to create a snapshot standby.

  1. The Data Guard environment (a primary and a physical standby) needs to be Oracle 10.2.0.1 version or higher.
  2. The Primary and Standby databases need to be in archivelog mode (this is a default requirement for Data Guard).
  3. Force logging should be set to TRUE (to avoid no-logging operations).
  4. Flash Recovery Area (FRA) is required on the standby database to implement a Flashback database.
  5. The Primary and standby are in sync at the time of the test, or the gap between primary and physical standby is nominal.

Activation of the standby

On the standby database

  1. Stop dataguard brokers on standby:
    SQL> alter system set dg_broker_start=FALSE;
  2. Get the SCN
    SQL> select current_scn from v$database;
  3. Cancel the managed recovery:
    SQL> alter database recover managed standby database cancel;
  4. Create the restore point.A restore point can be specified such that it guarantees the database can be recovered to a particular point-in-time and eliminates the need to manually record an SCN or timestamp to use with the Flashback database and Flashback table operations:
    SQL> create restore point TEST_NEW_FEATURE guarantee flashback database;
  5. Now confirm the scn from restore point:
    SQL> col name form a40;
    SQL> select scn, time, name from v$restore_point where name = 'TEST_NEW_FEATURE';

Prepare the primary

  1. Archive logsWhen using the standby redo logs, this step is essential to ensure that the database can be properly flashed back to the restore point.
    SQL> alter system archive log current;
    SQL> alter system switch logfile;
    SQL> alter system switch logfile;
  2. Stop shipment of logs
    SQL> alter system set log_archive_dest_state_2=DEFER

Activate the Physical Standby

  1. Activation of the standby:
    SQL> alter database activate standby database;
    SQL> startup mount force;
    SQL> alter database set standby database to maximize performance; 
    (This is used in case you have not set it before)
    	SQL> alter database open;
  2. disable log_archive_dest_2 (this will prevent archive logs being sent to primary):
    SQL> alter system set log_archive_dest_state_2=DISABLE;

Time for testing

Once the standby database has been activated, it is a full-blown production system. You may run reports, test new code, or create objects. Remember that any results stored in the activated database will be lost when the database is flashed back to before activation time.. If you need to save the results, they must be copied or exported out of the activated database before flashing it back.

Revert the snapshot database to physical standby

After testing is completed, you need to resynchronize the activated database with the primary database.

  1. Flashback to the restore point:
    SQL> STARTUP MOUNT FORCE;
    SQL> FLASHBACK DATABASE TO RESTORE POINT TEST_NEW_FEATURE;
    SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
    SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
    SQL> STARTUP MOUNT FORCE;
  2. Re-enable log shipping on the primaryEnable shipment:
    SQL> alter system set log_archive_dest_state_2=ENABLE
  3. To re-enable log shipping on the standby, enable this parameter:
    SQL> alter system set log_archive_dest_state_2=ENABLE
  4. Check dgmgrl configurationDGMGRL> show configuration verbose;
    Current status for “xxx”:
    SUCCESS
    DGMGRL> enable configuration (if not success)

A status of data guard configuration successful indicates the success of the procedures.

On Oracle Database 11g, you can use the Data Guard command-line interface (DGMGRL), Oracle Active Data Guard, and the OEM interface. In my next blog I’ll describe the equivalent procedures on 11g.

5 Responses to “Testing a Physical Standby Database in Oracle 10g”

  • Don Seiler says:

    Don’t forget to drop the restore point at the end, or your FRA will fill up as it continues flashback logging.

    SQL> DROP RESTORE POINT TEST_NEW_FEATURE;
    
    Restore point dropped.
    
  • Marcelo Lopes says:

    Thank you Don for the remind. It has to be done on the following step:

    1. Flashback to the restore point:

    SQL> STARTUP MOUNT FORCE;
    SQL> FLASHBACK DATABASE TO RESTORE POINT TEST_NEW_FEATURE;
    SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
    SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
    SQL> STARTUP MOUNT FORCE;
    SQL> DROP RESTORE POINT TEST_NEW_FEATURE;

  • Sanhay says:

    Are you able to create the configuration as above in 11g with dgmgrl and can share

    Great Work and excellent presentation to utilize the feature which otherwise people like me don’t realize it

    • Martin Bach says:

      @Sanhay:

      In 11.1 and later you can use the snapshot standby to achieve the same without knowing about the details.

      It has the added advantage that additional standby databases don’t start receiving archived logs from the newly activated standby.

      Cheers,

      Martin

  • Marcelo Lopes says:

    Hi Sanhay and Martin.

    Both options are valid for 11G and later. Just keep in mind that they require Enterprise Edition version.

    “A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database.

    A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.

    Using a single command changes made while read-write mode can be discarded and quickly resynchronize the standby with the primary database.” Met.note [ID 443720.1]

    I haven’t tested the snapshot standby. Will do and consider creating a blog about it.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>