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
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
FLASHBACK_ON
------------------
NO
SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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.
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
---------
YES
Step 2 : Cancel recovery on Standby Database
SQL> alter database recover managed standby database cancel;
Database altered.
Database altered.
Step 3 : Converting Physical Standby database to Snapshot
Standby database
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
Database altered.
Step 4: Shutdown the Standby Database Normal
SQL> shut immediate
ORACLE instance shut down.
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.
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
-------------------------------------- ---------- ------- -------------------------------
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;
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;