Open Oracle 10g Data Guard managed Physical Standby Database in Read-Write Mode

A trio of Oracle features - Oracle Data Guard, Database Flashback and Guaranteed Restore Point - equips Oracle DBA's arsenal with few potent techniques that were previously not available.

Without Oracle Flashback and Guaranteed Restore Point, Oracle 10g Data Guard managed Physical Standby Database can be opened in read-only mode for reporting purposes.

With Guaranteed Restore Point, Oracle Data Guard managed Physical Standby Database can be opened in read-write mode for variety of purposes.

With Oracle Flashback, Oracle Data Guard managed physical standby database can be flashed back to previous point-in-time, and then opened for read-only and/or read-write modes.

This blog describes the procedure to open a physical standby database in read-write mode using guaranteed restore point. This technique is quite simple, but can be very useful and powerful. Be sure to leverage it.

My test environment is on Windows platform with Oracle 10g R2 10.2.0.4. Physical standby database is operating in real-time redo apply under maximum performance mode.

Quick Reference Guide

Let me start with a quick reference guide that Oracle DBAs with substantial expertise in Data Guard technology can refer to. Many assumptions have been made in this section. You are responsible for verifying that Oracle Data Guard configuration is functioning properly. The primary objective is to give you SQL*Plus commands in proper sequence that you need to execute to open the physical standby database in read-write mode.

As shown below, there are only few commands to execute on Primary and Standby.

On Standby: Create Guaranteed Restore Point

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

CREATE RESTORE POINT flashback_standby_test GUARANTEE FLASHBACK DATABASE;

On Primary: Defer Redo Propagation to Standby

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

On Standby: Open DB in Read-write mode

ALTER DATABASE ACTIVATE STANDBY DATABASE;

STARTUP MOUNT FORCE;

ALTER DATABASE OPEN;

Use Physical Standby Database in Read-write mode

On Standby: Flashback Database

FLASHBACK DATABASE TO RESTORE POINT FLASHBACK_STANDBY_TEST ;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

STARTUP MOUNT FORCE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

On Primary: Restart Redo Shipping

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Flashback Physical Standby Database 101

Step 1: Create a test table and a job on Primary

As described in my previous blog, create a test table that will help you understand the concepts. This table will also allow you to verify that physical standby database in it new read-write mode is functioning properly, and then the physical standby database has been properly flashed back.

Step 2: Verify that Standby DB is in synch with primary

There are couple of checks that you need to perform to verify that the physical standby database is operating properly and is in synch with the primary database.

• Make sure that media recovery process is running on the standby database.

SQL> SELECT PROCESS

  2  FROM V$MANAGED_STANDBY

  3  WHERE PROCESS LIKE 'MRP%';

PROCESS                                                                        

---------                                                                      

MRP0    

• Perform a log switch on primary database and note down the last archive log sequence.

SQL> SELECT THREAD#, MAX(SEQUENCE#)

  2  FROM V$ARCHIVED_LOGED

  3  WHERE ARCHIVED = 'YES'

  4  AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

  5                           FROM V$DATABASE_INCARNATION

  6                           WHERE STATUS = 'CURRENT')

  7  GROUP BY THREAD#;

   THREAD# MAX(SEQUENCE#)                                                      

---------- --------------                                                      

         1             13                                                      

Verify that the archive log generated on the primary has been transported and applied to the physical standby database.

SQL> SELECT THREAD#, MAX(SEQUENCE#)

  2  FROM V$ARCHIVED_LOG

  3  WHERE ARCHIVED = 'YES'

  4  AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

  5                           FROM V$DATABASE_INCARNATION

  6                           WHERE STATUS = 'CURRENT')

  7  GROUP BY THREAD#;

   THREAD# MAX(SEQUENCE#)                                                      

---------- --------------                                                      

         1             13                                                      

SQL> SELECT THREAD#, MAX(SEQUENCE#)

  2  FROM V$ARCHIVED_LOG

  3  WHERE APPLIED = 'YES'

  4  AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

  5                           FROM V$DATABASE_INCARNATION

  6                           WHERE STATUS = 'CURRENT')

  7  GROUP BY THREAD#;

   THREAD# MAX(SEQUENCE#)                                                      

---------- --------------                                                      

         1             13                                                      

Step 3: Create Restore Point on Physical Standby Database

Make sure that Flash Recovery area has been setup on the physical standby database with sufficient space.

SQL> show parameter db_recov

NAME                                 TYPE        VALUE                         

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      e:\oraflash                   

db_recovery_file_dest_size           big integer 19531250K                     

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> CREATE RESTORE POINT flashback_standby_test GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> select NAME,SCN,TIME from v$restore_point;

NAME                                                                           

---------------------------------------------------------------------FLASHBACK_STANDBY_TEST                                                         

115431141                                                                     

07-MAR-11 12.55.35.000000000 PM                                                

                                                                               

EXAMPLE_NORMAL_RESTORE_POINT                                                   

 114646847                                                                     

13-FEB-11 12.32.19.000000000 PM                                                

Step 4: Defer Standby Archive Log Destination on Primary

Don't forget to switch the log file on primary database as it forces a log switch on the primary and secondary database, and hence restore point information is archived as well on the standby database.

DGS > ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

DGS > SELECT THREAD#, MAX(SEQUENCE#)

  2  FROM V$ARCHIVED_LOG

  3  WHERE ARCHIVED = 'YES'

  4  AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

  5                           FROM V$DATABASE_INCARNATION

  6                           WHERE STATUS = 'CURRENT')

  7  GROUP BY THREAD#;

   THREAD# MAX(SEQUENCE#)

---------- --------------

         1             14

DGS > ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

System altered.

Step 5: Open Physical Standby in Read-write mode

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

During standby database activation process, Oracle archives the current standby redo log file. If you query V$ARCHIVED_LOG, you will see an entry for the most recent archived redo log sequence. Please note that this archive log switch in independent of the primary database.

In addition, Oracle also clears redo log files in preparation for opening the database in read/write mode.

SQL> ALTER DATABASE MOUNT ;

ALTER DATABASE MOUNT

*

ERROR at line 1:

ORA-00750: database has been previously mounted and dismounted

SQL> SHUTDOWN IMMEDIATE ;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area  629145600 bytes                                      

Fixed Size                  1298288 bytes                                      

Variable Size             171966608 bytes                                      

Database Buffers          452984832 bytes                                      

Redo Buffers                2895872 bytes                                      

Database mounted.

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL                                                                        

-------                                                                        

CURRENT 

SQL> select database_role from v$database ;

DATABASE_ROLE

----------------

PRIMARY

                                                                     

SQL> ALTER DATABASE OPEN;

Database altered.

Oracle assigns a new INCARNATION# to the database and opens the database with RESETLOGS. You will also notice that log sequence starts with 1.

Since this database was physical standby database in its previous incarnation, you may see the following error in alert log which is completely normal as we have configured log archive destinations parameters in the event role switch occurs.

ORA-16009: remote archive log destination must be a STANDBY database

You will see Please note that you can use MOUNT FORCE option while mounting the database.

Step 6: Test Read-write mode operations

Remember that we had created a test table and a job that inserts DB_UNIQUE_NAME and TIMESTAMP into a table.

SQL> SELECT DB_UNIQUE_NAME, TSTAMP FROM JMEHTA.DG_SYNC_STATUS WHERE TSTAMP > SYSDATE - 1/24  ;

DB_UNIQUE_NA TSTAMP                                                            

------------ ----------------------------------------                          

DGS          07-MAR-11 12.40.54.680000 PM                                      

DGS          07-MAR-11 12.41.54.682000 PM                                      

DGS          07-MAR-11 12.42.54.683000 PM                                      

DGS          07-MAR-11 12.43.54.685000 PM                                      

DGS          07-MAR-11 12.44.54.686000 PM <- Last redo received from primary                                     

DGP          07-MAR-11 01.07.09.199000 PM <- Old Physical Standby now open in read-write mode began to execute the job to insert data into the database that is open on read-write mode.                                    

DGP          07-MAR-11 01.08.08.309000 PM                                      

DGP          07-MAR-11 01.09.08.400000 PM                                      

DGP          07-MAR-11 01.10.08.491000 PM                                      

9 rows selected.

Step 7: Revert back to Physical Standby Database

SQL> SHUTDOWN IMMEDIATE ;

SQL> STARTUP MOUNT ;

ORACLE instance started.

Total System Global Area  629145600 bytes                                      

Fixed Size                  1298288 bytes                                      

Variable Size             171966608 bytes                                      

Database Buffers          452984832 bytes                                      

Redo Buffers                2895872 bytes                                      

Database mounted.

SQL> FLASHBACK DATABASE TO RESTORE POINT FLASHBACK_STANDBY_TEST ;

Flashback complete.

SQL> select controlfile_type from v$database;

CONTROL                                                                        

-------                                                                        

BACKUP                                                                         

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> SHUTDOWN IMMEDIATE ;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP MOUNT ;

ORACLE instance started.

Total System Global Area  629145600 bytes                                      

Fixed Size                  1298288 bytes                                      

Variable Size             171966608 bytes                                      

Database Buffers          452984832 bytes                                      

Redo Buffers                2895872 bytes                                      

Database mounted.

SQL> SELECT SWITCHOVER_STATUS,open_mode FROM V$DATABASE;

SWITCHOVER_STATUS    OPEN_MODE                                                 

-------------------- ----------                                                

RECOVERY NEEDED      MOUNTED                                                   

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

Step 8 - Enable Physical Standby Log Destination on Primary

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE ;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;

System altered.

Step 8 - Verify that Physical Standby DB is back in its original recovery mode

Allow physical standby database to catch up, and then open on read-only mode to verify that the database has been flashed back and was applying redo from primary database,.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ;

Database altered.

SQL> ALTER DATABASE OPEN ;

Database altered.

SQL> SELECT DB_UNIQUE_NAME, TSTAMP FROM JMEHTA.DG_SYNC_STATUS WHERE TSTAMP > SYSDATE - 1/24 ;

DB_UNIQUE_NA TSTAMP                                                            

------------ ----------------------------------------                          

DGS          07-MAR-11 12.40.54.680000 PM                                      

DGS          07-MAR-11 12.41.54.682000 PM                                      

DGS          07-MAR-11 12.42.54.683000 PM                                      

DGS          07-MAR-11 12.43.54.685000 PM                                      

DGS          07-MAR-11 12.44.54.686000 PM                                      

DGS          07-MAR-11 12.45.54.687000 PM                                      

DGS          07-MAR-11 12.46.54.689000 PM                                      

DGS          07-MAR-11 12.47.54.690000 PM                                      

DGS          07-MAR-11 12.48.54.692000 PM                                      

DGS          07-MAR-11 12.49.54.694000 PM                                      

DGS          07-MAR-11 12.50.54.711000 PM                                      

DGS          07-MAR-11 12.51.54.696000 PM                                      

DGS          07-MAR-11 12.52.54.697000 PM                                      

DGS          07-MAR-11 12.53.54.699000 PM                                      

DGS          07-MAR-11 12.54.54.700000 PM                                      

DGS          07-MAR-11 12.55.54.703000 PM                                      

DGS          07-MAR-11 12.56.54.704000 PM                                      

DGS          07-MAR-11 12.57.54.705000 PM                                      

DGS          07-MAR-11 12.58.54.706000 PM                                      

DGS          07-MAR-11 12.59.54.709000 PM                                      

DGS          07-MAR-11 01.00.54.711000 PM                                      

DGS          07-MAR-11 01.01.54.711000 PM                                      

DGS          07-MAR-11 01.02.54.713000 PM                                      

DGS          07-MAR-11 01.03.54.714000 PM                                      

DGS          07-MAR-11 01.04.54.716000 PM                                      

DGS          07-MAR-11 01.05.54.718000 PM                                      

DGS          07-MAR-11 01.06.54.720000 PM                                      

DGS          07-MAR-11 01.07.54.721000 PM                                      

DGS          07-MAR-11 01.08.54.786000 PM                                      

DGS          07-MAR-11 01.09.54.789000 PM                                      

DGS          07-MAR-11 01.10.54.796000 PM                                      

DGS          07-MAR-11 01.11.54.807000 PM                                      

DGS          07-MAR-11 01.12.54.820000 PM                                      

DGS          07-MAR-11 01.13.54.835000 PM                                      

DGS          07-MAR-11 01.14.54.854000 PM                                      

DGS          07-MAR-11 01.15.54.871000 PM                                      

DGS          07-MAR-11 01.16.54.889000 PM                                      

DGS          07-MAR-11 01.17.54.906000 PM                                      

DGS          07-MAR-11 01.18.54.923000 PM                                      

DGS          07-MAR-11 01.19.55.026000 PM                                      

Note that while physical standby database was open in read-write mode, the primary database was executing the job that we had created in Step 1. Note that we don't have any gaps in data as well data that was inserted on physical standby database when it was open in read-write mode has been flashed back.

Oracle10g/Oracle11g Data Guard - Switchover and Switchback

Switchover and Switchback are very important mechanisms to validate the integrity of Oracle Data Guard configuration. Before you commit to Switchover/Switchback tests, don't forget to make sure that primary and secondary databases are in synch. Switchover and switchback are planned events. Oracle ships End Of Redo marker to the standby database to inform the standby about impending switchover event. Below is the log of such a test that I conducted. 

Switchover to Physical Standby
 
Step 0 - Make sure that primary and standby databases are in sync

Primary and standby database must be in synch before primary database can be switched over to the physical standby.

 

Step 1 - Verify that the Primary Database can be switched over to the Physical Standby

Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database.For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS 
 ----------------- 
 TO STANDBY

1 row selected 

A value of TO STANDBY or SESSIONS ACTIVE 
indicates that the primary database can be switched to the standby role. 

If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly. 

Step 2 - On Primary (DGP) - Make it a Physical Standby Database

DGP> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS                                                              

--------------------                                                           

SESSIONS ACTIVE                                                                

DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN ;

Database altered.

DGP> shutdown immediate ;

ORA-01507: database not mounted

ORACLE instance shut down.

DGP> startup mount ;

ORACLE instance started.

Total System Global Area  629145600 bytes                                      

Fixed Size                  1298288 bytes                                      

Variable Size             171966608 bytes                                      

Database Buffers          452984832 bytes                                      

Redo Buffers                2895872 bytes                                      

Database mounted.

DGP> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS                                                              

--------------------                                                           

TO PRIMARY                                                                     

Step 2 - On Secondary (DGS) - Make it a New Primary Database

DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS                                                              

--------------------                                                           

TO PRIMARY                                                                     

DGS> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

DGS> shutdown immediate ;

ORA-01507: database not mounted

 

ORACLE instance shut down.

DGS> startup ;

ORACLE instance started.

Total System Global Area  603979776 bytes                                      

Fixed Size                  1298144 bytes                                      

Variable Size             163578144 bytes                                      

Database Buffers          436207616 bytes                                      

Redo Buffers                2895872 bytes                                      

Database mounted.

Database opened.

Step 3 - On Old Primary (DGP) - Make it a new Standby DB

DGP> alter database recover managed standby database using current logfile disconnect ;

alter database recover managed standby database using current logfile disconnect

*

ERROR at line 1:

ORA-38500: USING CURRENT LOGFILE option not available without standby redo log files

 

DGP> alter database recover managed standby database disconnect ;

Database altered.

Step 4 - On New Primary (DGS) - Perform Switch Logs

DGS> alter system switch logfile ;

System altered.

DGS> alter system switch logfile ;

System altered.

DGS> select max(tstamp) from jmehta.dg_sync_status ;

MAX(TSTAMP)                                                                    

---------------------------------------------------------------------------    

02-MAR-11 03.39.37.096000 PM                                                   

Switchback to the old Primary 

Step 1 - On New Primary (DGS) : Switchback to Old Primary (DGP)

DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS                                                              

--------------------                                                           

SESSIONS ACTIVE                                                                

DGS> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

*

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

 

DGS>

DGS> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN ;

Database altered.

DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SELECT SWITCHOVER_STATUS FROM V$DATABASE

                              *

ERROR at line 1:

ORA-01507: database not mounted

DGS> shutdown immediate ;

ORA-01507: database not mounted

 

ORACLE instance shut down.

DGS> startup mount ;

ORACLE instance started.

Total System Global Area  603979776 bytes                                      

Fixed Size                  1298144 bytes                                      

Variable Size             163578144 bytes                                      

Database Buffers          436207616 bytes                                      

Redo Buffers                2895872 bytes                                      

Database mounted.

DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS                                                              

--------------------                                                           

TO PRIMARY                                                                     

Step 2 - On Old Primary (DGP) - Make it Primary

DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

ORA-06512: at line 8

ORA-16139: media recovery required

DGP> alter database close ;

Database altered.

DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

*

ERROR at line 1:

ORA-16139: media recovery required

DGP> select switchover_status from v$database ;

SWITCHOVER_STATUS                                                              

--------------------                                                           

SWITCHOVER PENDING                                                             

DGP> select switchover_status from v$database ;

DGP> alter database recover managed standby database finish ;

Database altered.

DGP> select switchover_status from v$database ;

SWITCHOVER_STATUS                                                              

--------------------                                                           

TO PRIMARY                                                                     

DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

DGP> shutdown immediate ;

ORA-01507: database not mounted

ORACLE instance shut down.

DGP> startup

ORACLE instance started.

Total System Global Area  629145600 bytes                                      

Fixed Size                  1298288 bytes                                      

Variable Size             171966608 bytes                                      

Database Buffers          452984832 bytes                                      

Redo Buffers                2895872 bytes                                      

Database mounted.

Database opened.

DGP>

Step 3 - On Secondary (DGS) - Activate Recovery

DGS> alter database recover managed standby database using current logfile disconnect  ;