Convert Physical Standby to Snapshot Standby

Snapshot Standby:-  Oracle indroduces Snapshot standby database feature in Datagurad 11g. where one can convert a Physical standby database(Read-Only) to a Snapshot Standby(Read/Write) in READ/WRITE mode and make any DDL/DML operation further and once the Testing purpose is completed, you can resyncronize your database as physical standby back.

Convert a Physical Standby to Snapshot Standby with SQL*Plus

Check that  Flashback feature is enabled/disabled
If that is not enabled, perform the following steps to enable flashback on both  database(standby & primary) :

Set (if needed) the db_recovery parameters on both database(standby & primary)  

–On Primary
SQL> show parameters db_recovery;


 NAME_COL_PLUS_SHOW_PARAM                                                         TYPE        VALUE_COL_PLUS_SHOW_PARAM
——————————————————————————– ———– ——————————
db_recovery_file_dest                                                            string      /data02/oradata/flash_recovery
                                                                                             _area
db_recovery_file_dest_size                                                       big integer 2G

 

If needed, follow below:

SQL> alter system set db_recovery_file_dest_size=50g scope=both;

SQL> alter system set db_recovery_file_dest='/data02/oradata/flash_recovery_area' scope=both;
SQL> alter system set db_flashback_retention_target=60 scope=both;
SQL> shutdown immediate

–On Standby
SQL> show parameters db_recovery;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 2G

SQL> alter system set db_recovery_file_dest_size=50g scope=both;
SQL> Alter system set db_recovery_file_dest='/u01/oradata/cobradup/flash_recovery_area' scope=both;
SQL> alter system set db_flashback_retention_target=60 scope=both;
SQL> shutdown immediate;
SQL> startup mount; 
SQL> select name,database_role,flashback_on from v$database;

NAME      DATABASE_ROLE    FLASHBACK_ON
——— —————- ——————
COBRA     PHYSICAL STANDBY NO


SQL> alter database flashback on;
SQL>  select name,database_role,flashback_on from v$database;

NAME      DATABASE_ROLE    FLASHBACK_ON
——— —————- ——————
COBRA     PHYSICAL STANDBY YES

-On Primary
SQL> startup mount
SQL> select name,database_role,flashback_on from v$database;

 NAME                                     DATABASE_ROLE    FLASHBACK_ON
—————————————- —————- ——————
COBRA                                    PRIMARY          NO


SQL> alter database flashback on;

Database altered.

SQL> select name,database_role,flashback_on from v$database;

 NAME                                     DATABASE_ROLE    FLASHBACK_ON
—————————————- —————- ——————
COBRA                                    PRIMARY          YES

SQL> alter database open;
 
Convert the Standby in Snapshot Standby

Once conversion takes place, internally restore point has been created by oracle and 
the database will be opened in read-write mode:


SQL> shutdown immediate
SQL> startup mount
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 

After that you are ready to perform any DDL and DML operations on it.
SQL> shutdown immediate;
SQL> startup;
SQL> select name,database_role,flashback_on from v$database;

NAME      DATABASE_ROLE    FLASHBACK_ON
——— —————- ——————
COBRA     SNAPSHOT STANDBY YES

 

Request you all to put your valuable comments on  posts of  this blog, will motivate me to write more good post. smiley

 

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply