Oracle 11g CONVERTING PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE

CONVERTING PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE

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 u03/oradata/ODSP1/flash_recovery_area/
db_recovery_file_dest_size big integer 2G

ii) SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
iii) 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.
v) SQL> alter database flashback on;
Database altered.
vi) SQL> select flashback_on from v$database;
FLASHBACK
---------
YES
vii) SQL> alter database open;
Database altered.
viii) SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME OPEN_MODE GUARD_S DATABASE_ROLE
---------------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY

Step 2 : Cancel recovery from Primary Database to Physical 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
ORA-01507: Database is niet aangekoppeld.

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
-------------------------------------- ---------- ------- -------------------------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY

CONVERTING SNAPSHOT STANDBY DATABASE TO PHYSICAL STANDBY DATABASE

Step 1: Check for current database role

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
----------------------------------------- ---------- ------- ----------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY

Step 2 : Shutdown Snapshot Standby Database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 3 : Start in Nomount mode

SQL> Startup nomount pfile='$ORACLE_HOME/dbs/initOLPTP1.ora'
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

Step 4: Mounting Snapshot Standby Database

SQL> alter database mount;
Database altered.

Step 5: Converting Snapshot Standby to Physical Standby Database

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

Step 6: Shutdown the database
SQL> shut immediate
ORA-01507: Database is niet aangekoppeld.

ORACLE instance shut down.

Step 7: Starting database in Nomount mode and mount

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initOLPTP1.ora'
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

SQL> alter database mount standby database;
Database altered.

Step 8 : Cancel recovery from primary database and open database

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database open;
Database altered.

Step 9 : Check for redo’s are applied in standby database

SQL> select process, status from v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE

IF RFS is not there, it indicates that there is no communication between primary and standby
Check below query in primary database

Step 10: Check in primary database

SQL>select error from v$archived_dest;
The output shows "END OF FILE on COMMUNICATION FILE or ORACLE NOT FOUND".
Wait until this error get cleared (it will be cleared automatically after some minutes)

Implies that redos are transferring, but not applied as we issued recover managed standby database cancel (As per Step 8)

Step 11 : Recover Physical Standby database

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_GAP
RFS IDLE

6 rows selected.

Shows that archives apply is under process as we are recovering standby database

(IMPORTANT NOTE : RFS - implies redo transfer from primary to standby is under process.
MRP0 - indicates it is applying archive logs in standby)

Step 12 : Check for database role

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

NAME OPEN_MODE GUARD_S DATABASE_ROLE
--------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY