Home
 | 
Articles
 | 
High Availability
 | 
Using a Physical Standby Database for Read/Write Testing
 | 
Physical Standby Database
       for Read/Write Testing
 
Last modified: March 2017
Recent Article
All Archives
Topics
Comments 
by ShanNura
»»
Consider a scenario your application/QA team wants to test the DR application using the most recent production data. Let us assume your standby database is already running in the DR site and is constantly in sync with the primary database.
Using Flashback Database feature, we can temporarily convert the standby database to act as a production database and ask the application team to carry on with their testing. Once the testing is completed, we can put the database back to a point in the past (to the restore point) and act as standby database once again and continue to apply the log from the primary site. After the physical standby is flashed back, Dataguard will automatically synchronize it with the primary. This eliminates the need to recreate the physical standby from a backup of the primary database.
This is an efficient method and quite useful to briefly take your standby database to behave as a production database without having to disturb the primary site. Meaning, the primary database can remain online without the need to logoff users or shut it down.
Converting standby as primary
Logon to the standby database and check the role and mode:
SQL>
SQL> select database_role, open_mode from v$database ;
Ensure, the standby is almost in sync with the primary. Perform a couple of archive log switches on the primary and followed by defer LOG_ARCHIVE_DEST_STATE_2 from all RAC nodes.
$
SQL> alter system archive log current;
SQL> /
SQL> /

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both sid='*';
SQL> show parameter LOG_ARCHIVE_DEST_STATE_2 ;
Logon to the standby again, take the standby out of recovery mode.
SQL>
SQL> alter database recover managed standby database cancel ;
Create a guaranteed flashback restore point. Remember, this is a very important step. Check if there exists any previous restore point. If exists, you can use 'SQL> drop restore point <restore_point_name>;'
SQL>
SQL> select name,guarantee_flashback_database,time,restore_point_time from v$restore_point;
SQL> create restore point DR_20170301_1 guarantee flashback database ;
SQL> select name,guarantee_flashback_database,time,restore_point_time from v$restore_point;
Your alert log would indicate, for example, as below:

Wed Mar 01 09:50:03 2017
Created guaranteed restore point DR_20170301_1
Activate standby database and then open it.
SQL>
SQL> alter database activate physical standby database;
SQL> alter database set standby database to maximize performance;
Then, we need to bounce the database.
$
[oracle@ol-alpha-dr ~]$ srvctl stop database -database unicorn
[oracle@ol-alpha-dr ~]$ srvctl start database -database unicorn
Note: In case, the database is in MOUNTED state, you can manually open it. Also check the other instances whether or not they are OPEN or MOUNTED.
SQL>
SQL> select name, status from v$database, v$instance ;

NAME      STATUS
--------- ------------
PHOENIX   MOUNTED

SQL> alter database open;
Database altered.

SQL> select database_role,open_mode from v$database ;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE
We can confirm if all the instances are up and running.
$
[oracle@ol-alpha-dr ~]$ srvctl status database -database unicorn
Instance PHOENIX1 is running on node ol-alpha-dr
Instance PHOENIX2 is running on node ol-beta-dr
Now with the standby database acting as a primary database from the DR site, we can ask the team to proceed with their DR application testing.
Remember, longer the testing window, more the number of archives have to be applied later when we put this primary database back as standby. Keep an eye on the usage of FRA.
Reverse the primary to act as Standby
Shutdown the database.
$<>  
[oracle@ol-alpha-dr ~]$ srvctl stop database -database unicorn
[oracle@ol-alpha-dr ~]$ srvctl start instance -database unicorn -node ol-alpha-dr -startoption mount
Note: I am starting only the first instance as I don't want the other instances. Generally one instance is enough to receive and apply the archive from the primary site.
Logon to the standby database (mounted instance).
$
[oracle@ol-alpha-dr ~]$ sqlplus / as sysdba
SQL>
SQL> select name, status from v$database, v$instance ;

NAME      STATUS
--------- ------------
UNICORN   MOUNTED
Check the restore point and use FLASHBACK DATABASE
SQL>
SQL> select name,guarantee_flashback_database,time,restore_point_time from v$restore_point;
SQL> flashback database to restore point DR_20170301_1 ;
Your alert log would indicate as:

Completed: flashback database to restore point DR_20170301_1
Then, convert to physical standby.
SQL>
SQL> alter database convert to physical standby ;

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
Put the database into recovery mode.
$
SQL> alter database recover managed standby database disconnect from session ;
Logon to the primary and re-enable the archiving.
SQL>
SQL> alter system set log_archive_dest_state_2=ENABLE scope=both sid='*' ;
Check the sync. If there are too many archives to be applied and then it will surely take sometime before it catches up with the primary.
Please write your comment if this article was useful.

ShanNura

/
You might want to read this:
Snapshot Standby (11g feature)