Oracle Flashback Physical Standby Database

This third instalment covering some of the flashback features in Oracle 10g takes a look at combining flashback with using a physical standby. I will take a look at two usage scenarios in which I think could offer significant benefit to anyone using Oracle Database Flashback with a physical standby. This post will cover the use of the physical standby as a read/write copy of a production database.


Download my FREE Oracle Performance Diagnostic process to pinpoint your performance issues.

Using a physical standby as part of a Disaster Recovery solution can offer a comprehensive level of protection for a number of failures.

Implementing a physical standby can however require significant investment in hardware and software licenses. Using the physical standby database to support non-production development and testing can provide greater return on investment.

When reviewing Oracle Database physical standby options a common question is whether the infrastructure hosting the standby database can be “used”. Combining Oracle Database Flashback and Oracle Data Guard a very robust solution can be provisioned that enables the “use” of the physical standby.

Enabling the Physical Standby Oracle Database

The steps to do this are as follows (for those using the Data Guard Broker there are a number of slightly different steps to do that I will cover in a later post):

  1.  Prepare the physical standby:
  1. Cancel the media recovery process.
  2. Create a restore point at the point in time the standby needs to be restored to.
  1. Prepare the primary:
  1. Archive the current redo log.
  2. Disable redo transport.
  1. Activate the physical standby:
  1. Convert standby database to primary database. This allows the physical standby to actually be opened for read/write access.
  2. Mount the database.
  3. Set the protection level to maximum performance.
  4. Open the database.

Running through the above this is what we see on the physical standby (assuming the physical standby is already running):

Physical Standby Database Preparation Tasks

The database restore point is created and changes are no longer being applied to the standby.

Primary Database Preparation Tasks

Physical Standby Open Tasks

Once the database is open it will be current as of the time the last changes were applied. Running the following query against the physical standby will show how current the database is:

Physical Standby Database Use Cases

The following types of processes could make very good use of this “copy” of the production database and remove expensive workload from the production infrastructure:

Database Statistics Collection

If the workload on the primary database prevents the resources required for statistics collection then the physical standby could be used to run the dbms_stats (fnd_stats for those in an e-Business Suite environment) programs required to collect statistics. The workload required on the primary database to update the statistics would be limited to running the dbms_stats package calls to import the updated statistics.

Interface Processing

Large interface jobs and ETL processing can consume a significant amount of resources on a database. Using the physical standby to process ETL and/or interface jobs could remove a significant amount of workload from the primary database to the physical standby infrastructure. Using a combination of partitioning, transportable tablespaces and data pump the “processed” data could easily be moved back to the primary database and made available to the application with very little overhead.

Reporting

Application reports that view data over a historical window prior to the time of switchover could be run against the physical standby.

Application Testing

Having an exact copy of the production database for tuning efforts can be of immense value. This particularly applies in cases where it is not feasible to have a clone taken of the production database quickly.

Disaster Recovery

This technique allows the database to be opened for DR testing and then reverted back to its original state on completion of testing. For DR tests that involve “dummy” transactions this is an ideal way to thoroughly test the DR process without having to worry about failing over to the DR site.

Physical Standby Database Rollback

Once the jobs that use the physical standby have completed the standby needs to be re-synchronised with the production database:

  1. Startup mount (this puts the database in a position to allow the flashback to begin).
  2. Flashback to restore point created prior to the opening of the standby database.
  3. Convert the physical standby control file from a primary mount back to a standby mount.
  4. Startup mount the physical standby.
  5. Begin log apply the start the re-synchronisation.
  6. Enable log shipping on the primary.

Verify the physical standby database has been used since it was opened:

  1. Startup mount the database to enable the database to be flashed back to the restore point:

  1. Check that the restore point is available and perform the flashback:

  1. Convert the physical standby control file from primary mount to standby mount and restart the database with the standby control file mounted:

  1. Enable log apply on the physical standby:

alert log from physical standby:

  1. Enable log transport on the primary database:

alert log from primary database:

Taking a look at the status of log apply on the physical standby we see:

This exercise demonstrates that it is relatively straightforward to use a physical standby for a number of useful activities to reduce workload on the primary server.

This example is a very simple case and it must be stated that whilst the physical standby is being used it is not available immediately for switchover or failover should a disaster happen on the primary site.

Download my FREE Oracle Performance Diagnostic process to pinpoint your performance issues.

Mark Burgess has been helping organisations obtain the maximum value from their data management platforms for over 20 years. Mark is passionate about enabling secure, fast and reliable access to organisations data assets.

About the Author

Mark Burgess has been helping organisations obtain the maximum value from their data management platforms for over 20 years. Mark is passionate about enabling secure, fast and reliable access to organisations data assets.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.