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.