Oracle: Activation and DR Testing

Owned by Vijayganesh Sivaprakasam
Last updated: Feb 09, 2024 by Jan Klinke
9 min read

When disaster strikes and the primary database is no longer available the standby database must be activated to become the new primary database to continue operation.  This is also called failover to the standby database.

The steps to activate the standby database are:

  1. Stop the scheduling of Dbvisit Standby.

  2. Change the network configuration (or DNS) so that users will connect to the standby database (or server) instead of the primary database (or server).

  3. Activate and open the standby database for normal operation as per the instructions below.  Note that as soon as the standby database is activated and becomes the new primary database that is open read/write.  The link to the original primary database is lost and it is no longer possible to apply new logs to the original standby database.

To activate and open the standby database for continued operation in the event of a disaster the following procedure discussed in this section must be used.

1. Disaster Recovery Actions

In case of disaster strikes, you will want to activate (failover) to the standby database.  During this process, a resetlogs is performed and the standby database is opened read/write.

The standby database is then converted to a primary database and cannot be used as a standby database anymore. In Multiplatform, there are a number of options introduced.

For a selected configuration there are three main options for Disaster Recovery Actions

( 1 ) Activate Standby: Select this option if you want to activate the standby database now - this is usually in the case when the disaster happened and you need to activate (failover) to the standby database.

( 2 ) Test Standby Activation:

  • It is always recommended that sites perform DR testing, activate a standby database and make use of a disaster did happen everything work as expected.  This option will allow you to perform a backup of the standby database prior to activating it. 

  • Two options will be available:

  • Create a backup to a local disk using RMAN Backup sets

    • Create a backup to a local disk using RMAN Image Copies

      • This option can be useful to quickly switch back to your image copy instead of running a restore process.

( 3 ) Test Opening Standby Read-Only: This option allows you to quickly test if your standby database can be opened read-only.  This is an excellent test to see if the standby database is in a consistent state.  If a standby database is not consistent, you will not be able to open it read-only or activate it.  In this case, you will have to send and apply more logs before you will be able to activate or open the standby read-only.

2. Test Standby Activation

The below screen capture will show how to perform Test standby activation. The major steps involved during this operation are.

  • Take a backup or image copy of the database before activation

  • Activate the standby database and perform testing (pointing DR application to the database, running queries, allowing users to perform changes). During this step, the actual production database is not affected and the main application can continue to operate as it is and moreover any operations(data changes) performed on the activated DR database is not backported to the actual production.

  • After the activity is completed, restore and recover the database prior to the activation using the backup taken.

  • Continue to send and apply archivelogs as before

Ensure the archivelogs are not deleted from production during the DR testing operation. Once the test is completed the archivelogs required by standby has to be sent from primary. This should be from the point where backup of the database was taken prior to activation.

Steps Involved:

( 1 ) Choose the configuration for which you are testing and then click on Test Standby Activation. This will open up the options that need to be filled in.

( 2 ) The backup type can be either Image copy or Backupset. In this example, we are choosing backupset

( 3 ) The backup format is chosen by default. It is recommended to leave it as is but can be changed to the user’s requirements.

( 4 ) Backup location, If you are using Image copy ensure the backup location or the datafile location has enough free space available. For backupset backups , Dbvisit uses compressed backupsets ,so we need atleast 25 - 30% space as the database size. Provided backup location must not be under FRA/datafile location and also avoid using ARCHDEST. In this example, we are choosing the location /u01/app/oracle/backup.

Ensure the archivelogs are not deleted from production during the DR testing operation. Once the test is completed the archivelogs required by standby has to be sent from primary. This should be from the point where backup of the database was taken prior to activation.

( 5 ) The DR actions for database activation can be stopped once the backup is completed or you could automatically activate as soon as the backup is complete. The default option is yes and the process waits after the backup is completed.

( 6 ) After choosing the options press Start to start the process.

 

 

The process completes successfully and waits for the user to activate the standby database and the backups are taken in /u01/app/oracle/backup

After the backup completes, choose the configuration again and click on Resume Standby Activation Test ( 1 ).

This will show when the backup was taken ( 1 ) and then clicking on the start button ( 2 ) will activate the standby database and open the database in read-write mode.

The tasks show that the standby database is activated and the database is currently in read-write mode.

The configuration also clearly states that the standby database is activated for DR testing.

Once the DR testing activity is completed, we need to restore and recover the database from the database backup taken prior to activation. Choose the configuration and you can click on the option Reinstate Standby Database ( 1 ) option.

After choosing the Reinstate Standby Database option we can see the details on when the backup was taken ( 1 ) and also the list of backup pieces that will be restored back to the database ( 2 ). Click Start ( 3 ) button to start the process of restore and recovery of the standby database.

The database will be restored and recovered from the backup taken previously.

The standby database is now in recovery mode and the archivelogs are getting applied from primary to standby ( 1 ) and we can also see all the tasks related to the standby DR test.

 

3. Standby Read-Only Test

This option will help in determining if the database is standby is in a consistent state. This would open the database in read-only mode and restart the database to mount mode so that the archivelogs can be applied.

The standby database is put to Read-only mode to check if its in a consistent state ( 1 ) and also check if it's ready for activation and then its restarted and put back to recovery mode ( 2 )

4. Activate Standby

Select this option if you want to activate the standby database now - this is usually in the case when the disaster happened and you need to activate (failover) to the standby database. If you have enabled Standby Update Delay where the standby database archivelogs are available on the standby but not applied. This would give you option to apply all the logs and then activate the standby database.

After the standby database is activated. The Observer and the Automated standby update as disabled. The observer is disabled to ensures that the unnecessary alerts are not sent out. Since, there is no standby database after activation the automated standby update is disabled as well. This options would be prompted when the new standby database is created.

 

Clicking on Activate will open the database in read-write mode with resetlogs and once this operation is done , you have to either recreate the standby database after dropping the database , If you are doing this during a true DR scenario where the primary is unavailable then you might have to recreate the primary database as standby and then do a switchover to revert back to original configuration.

 

Once the standby database is activated the roles of the configuration are switched from primary to standby.

The configuration will now be shown as IN FAILOVER STATE with the configuration getting switched between primary and standby.

After activating standby database and making it new primary, it is necessary to verify that everything is working correctly from DBA perspective. The most important checks include (but are not limited to):

  • checking all temporary datafiles are created

  • observing alert log of new primary database

  • cycle through all redo log groups by running “alter system switch logfile” and verify all archivelogs are correctly created

  • cross check backups and archivelogs in RMAN repository

  • verify that listener lists all necessary database services

5. Intelligent Activation

This is a new feature introduced in the standby multiplatform. In the earlier versions when apply delay lag is set in standby, the archivelogs are sent from primary to standby but are not applied due to the delay lag set. When the standby database is activated during such scenarios the archivelogs that are available in standby are not applied but the database is activated with a delay lag. But, in standby multiplatform options are provided for the user to activate the standby database with the latest archivelogs or the users can choose what timestamp to activate the standby.

 

In the below example, the standby update delay is set to 20minutes and the Automated standby update is set to 300sec(5mins), when we try to activate the database, below options and timestamps are provided for the activation.

If you choose option ( 1 ) then the archivelogs are applied based on the apply delay lag and the database is Activated for that timestamp. But if you choose option ( 2 ) then all the archivelogs that are generated between option ( 1 ) and including ( 2 ) are applied. Option ( 2 ) is just 5 minutes behind the primary so when the database is activated there is just an RPO of 5 minutes to the database.

 

 

Once the Activate is clicked the archivelogs are applied until 5minutes from the Primary and then database is activated and opened with resetlogs with