0 0 0

+21

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. 

0 0 0