Monday, June 6, 2011

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.

No comments:

Post a Comment