Opening Physical Standby for Read Write using Flashback

We are planning to open the standby database in the read/write mode for testing. Please find the step by step method performing it.

It is RAC standby database with two node setup.

Status on the standby database

à STATUS OF THE STANDBY DATABASE

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE

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

BHU       MOUNTED

BHU       MOUNTED

à CHECKING STATUS OF THE CONTROL FILE IN THE STANDBY DATABASE

SQL> select CONTROLFILE_TYPE from gv$database;

CONTROL

-------

STANDBY

STANDBY

à CHECKING RECOVERY AREA AND ALLOCATE SIZE

SQL> show parameter db_recover

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +BHU_RECO

db_recovery_file_dest_size           big integer 4G

à CHECKING WHETHER FLASHBACK IS ENABLED OR NOT. TO OPEN A STANDBY DATABASE IN THE READ/WRITE MODE, WE NEED TO HAVE THE FLASHBACK WITH ENOUGH SIZE.

SQL> select flashback_on from gv$database;

FLASHBACK_ON

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

YES

YES

à CHECKING THE STATUS OF RECOVERY PROCESS, IF IT IS ENABLED THEN WE HAVE TO STOP THE RECOVERY.

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              1         45        714

RFS       LGWR              2         40        630

MRP0      N/A               2         40          0

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

à AFTER CANCELING THE RECOVERY PROCESS, CHECK THE STATUS

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or           client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              1         45       1146

RFS       LGWR              2         40       1051

à CREATING A RESTORE POINT TO FLASHBACK THE DB TO THE OLD STAGE & THIS WILL HELP US TO BRING BACK AS A STANDBY DATABASE

SQL> CREATE RESTORE POINT stby_fb_test GUARANTEE FLASHBACK DATABASE;

Restore point created.

à CHECKING THE SCN DETAILS OF THE FLASHBACK

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

NAME                        SCN TIME

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

STBY_FB_TEST           19370290 16-NOV-11 05.43.54.000000000 PM

Note: Above commands are issue only in the standby database

In Primary DB

Defer log archive destinations pointing to the standby that will be activated. I have given the example of modifying the log shipping through the SQLPLUS (or) using the DG BROKER UTILITY. YOU HAVE TO PERFORM EITHER ONE ONLY.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

(Or)

DGMGRL> edit database 'BHU_A' set PROPERTY 'LogShipping'='OFF';

Property "LogShipping" updated

After modifying, checking the status of the logshipping through the DG broker

DGMGRL> show database verbose 'BHU_A';

Database - BHU_A

  Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s):

    BHU_1

    BHU_2

  Properties:

    LogShipping                     = 'OFF'

DIFFERNCE BETWEEN LogShipping & TRANSPORT-ON/OFF

■ TRANSPORT ON/OFF

Turn redo transport services on and off by setting the state of the primary database. Setting the primary database state to TRANSPORT-ON starts redo transport services to the standby databases, and setting the primary database state to TRANSPORT-OFF stops redo transport services to all the standby database.

EXè

DGMGRL> EDIT DATABASE 'BHU_A' SET STATE='TRANSPORT-OFF';

■ LogShipping

Turn redo transport services on and off to an individual standby database using the LogShipping database property on the standby database. If you set the LogShipping property to OFF for a standby database, redo transport services to this standby database are turned off, while redo transport services to other databases are not affected.

EXè

DGMGRL> EDIT DATABASE 'BHU_A' SET PROPERTY 'LogShipping'='OFF';

Property "LogShipping" updated

AGAIN IN STANDBY DB

TO ACTIVATE YOUR STANDBY DATABASE AND OPEN IT IN READ / WRITE MODE

SQL> alter database activate standby database;

Database altered.

IF YOU GET AN ERROR MESSAGE WHILE activate your Standby Database and open it in read / write mode, you don't care about whether the Standby is in-sync with your Primary Database before activation. You cancelled the Managed Recovery and the command of 'alter database activate standby database' failed with the following errors:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: ' +BHU_DATA1/BHU_b/datafile/system.268.762965715'

REASON FOR ERROR MESSAGE

The Managed Recovery was cancelled while it was in the half way of recovering an Archive log.

SOLUTION

à Cancel Managed Recovery

SQL> recover managed standby database cancel;

à Start manual recovery

SQL> recover automatic standby database;

à Provide the archive log file that the recovery asks at the prompt, and then enter CANCEL when the prompt occurs again.
For example,

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

à Activate the standby database

SQL> alter database activate standby database;

Database altered.

à Once the standby database is activate, we can see the difference in the controlfile status. It has changed it to CURRENT

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL

-------

CURRENT

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

à Open the standby database in the READ/WRITE Mode

SQL> ALTER DATABASE OPEN;

Database altered.

à SINCE I HAVE KEPT BOTH INSTANCE IN THE MOUNT STAGE AND I HAVE OPEN MANUALLY ON A SINGLE INSTANCE. SO OTHER INSTANCE SHOWS AS MOUNTED

SQL> select name,open_mode from gv$database;

NAME                 OPEN_MODE

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

BHU                  MOUNTED

BHU                  READ WRITE

STARTING NODE-2

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 16 17:55:52 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

SQL> select name,open_mode from gv$database;

NAME                 OPEN_MODE

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

BHU                  READ WRITE

BHU                  READ WRITE

à BELOW CAN BE DONE EITHER NODE1 (OR) NODE2

à I AM CREATING SOME TABLES & PERFORMING SOME DML OPERATIONS

SQL> create table bhuvan as select * from dba_objects;

Table created.

SQL> select count(1) from bhuvan;

  COUNT(1)

----------

     17065

SQL>  delete bhuvan where owner='BHUVAN';

9699 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(1) from bhuvan;

  COUNT(1)

----------

      7366

à CHECKING THE STATUS OF THE CONTROLFILE

SQL> select controlfile_type from gv$database;

CONTROL

-------

CURRENT

CURRENT

NOW BRING READ/WRITE STANDBY DATABASE TO THE OLD POSITION AND BRING STANDBY TO SYNC WITH THE PRIMARY DB

à STOP THE DB COMPLETELY USING SRVCTL COMMAND

$ srvctl stop database -d BHU_b

à STARTING ONE INSTANCE TO PERFORM A FLASHBACK FROM A RESTORE POINT

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 16 17:59:18 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.4431E+10 bytes

Fixed Size                  2240272 bytes

Variable Size            3892314352 bytes

Database Buffers         1.0503E+10 bytes

Redo Buffers               34148352 bytes

Database mounted.

SQL> FLASHBACK DATABASE TO RESTORE POINT STBY_FB_TEST;

Flashback complete.

à AFTER PERFOMING THE FLASHBACK, WE ARE CHECKING THE CONTROLFILE TYPE

SQL> select controlfile_type from v$database;

CONTROL

-------

BACKUP

à NOW WE ARE CHANGING BACK TO THE STANDBY DATABASE

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

à SHUTDOWN ONE INSTANCE AND WE ARE STARTING BOTH INSTANCE USING SRVCTL COMMAND

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

$ srvctl start database -d BHU_b

à AFTER CONVERTING TO THE STANBDY DATABASE, WE ARE CHECKING THE STATUS OF THE STANDBY DATABASE

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 16 18:02:23 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production with the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select controlfile_type from gv$database;

CONTROL

-------

STANDBY

STANDBY

à CHECKING THE STATUS OF THE RECOVERY PROCESS

SQL>  select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or           client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              2         45         22

MRP0      N/A               0          0          0

Note: No recovery is started

IN PRIMARY

I have given the example of modifying the log shipping through the SQLPLUS (or) using the DG BROKER UTILITY. YOU HAVE TO PERFORM EITHER ONE ONLY.

DGMGRL> edit database 'BHU_A' set PROPERTY 'LogShipping'='ON';

Property "LogShipping" updated

DGMGRL>

(Or)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

IN STANDBY

à AFTER ENABLING THE LOG SHIPPING ON THE PRIMARY DB, WE ARE CHECKING THE STATUS OF THE RECOVERY

SQL> l

  1*  select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR'

SQL> /

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              2         45        109

MRP0      N/A               2         45        108

à IT IS SAFE TO DROP THE RESTORE POINT ON THE STANDBY DATABASE

SQL> DROP RESTORE POINT STBY_FB_TEST;

Restore point dropped.

à CHECKING THE STATUS OF THE STANBDY SYNC WITH PRIMARY

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_A - Primary database

    BHU_B - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Happy learning, Please provide your feedback.