CONVERTING PHYSICAL STANDBY TO SNAPSHOT STANDBY DATABASE

Oracle 11gR2 Convert Physical Standby Database To SNAPSHOT STANDBY DATABASE

Overview:

Part # 1. Convert Physical standby to Snapshot

Part # 2. Revert Snapshot Standby to Physical Standby

Part # 3.  Convert Physical RAC Standby Database to snapshot / Revert to Physical Standby

Summery:

"SNAPSHOT STANDBY allows the standby database to be opened in read-write mode.

When switched back into standby mode, all changes made whilst in read-write mode are lost.

Converting physical standby Database to READ/WRITE mode Snapshot Standby and Revert to Physical standby either single instance or RAC Nodes.

Step 1: Check if Flashback is enabled. If not, enable in mount state in Physical Standby database

i)             SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
-------------------------------------------------------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area/
db_recovery_file_dest_size big integer 5G

ii)            ii) SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down. 

iv) SQL> startup mount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 162887636 bytes
Database Buffers 41943040 bytes
Redo Buffers 4096000 bytes
Database mounted. 

SQL> alter database flashback on;

SQL> select flashback_on from v$database;

FLASHBACK
---------
YES

Step 2 : Cancel recovery on Standby Database

SQL> alter database recover managed standby database cancel;
Database altered.

Step 3 : Converting Physical Standby database to Snapshot Standby database

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.

Step 4: Shutdown the Standby Database Normal

SQL> shut immediate
ORACLE instance shut down.

Step 5: Startup Standby database Normal

SQL> Startup
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
Database mounted.
Database opened.

Step 6: Check Database role

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME OPEN_MODE GUARD_S DATABASE_ROLE
-------------------------------------- ---------- ------- -------------------------------
ORCL READ WRITE NONE SNAPSHOT STANDB

Part # 2:

Revert Snapshot Standby to Physical Standby 

Step 1: Check for current database role

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

Step 2 : Shutdown Snapshot Standby Database

SQL> shut immediate

Step 3 : Startup in mount mode

SQL> Startup mount;

Step 5: Converting Snapshot Standby to Physical Standby Database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Step 6: Shutdown the database

SQL> shut immediate

SQL> startup mount;

Step 8 :Start Recovery on Standby Database

  

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 9 : Check Status Now 

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

 "IF STANDBY DATABASE IS RAC THEN FOLLOW BELOW STEPS"

Part # 3.  Convert Physical RAC Standby Database to snapshot / Revert to Physical Standby