Conversion of Physical standby to Snapshot standby and vice versa

Conversion of PHYSICAL standby to SNAPSHOT standby
******************************************************************

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>
SQL> sho parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/standby/fra
db_recovery_file_dest_size           big integer 3882M
SQL>
SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

SQL>
SQL>
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
SQL>
SQL> alter database convert to snapshot standby;

Database altered.

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  SNAPSHOT STANDBY NOT ALLOWED

SQL> alter database open;

Database altered.

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;


OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ WRITE           MAXIMUM PERFORMANCE  SNAPSHOT STANDBY NOT ALLOWED

SQL> SQL>



Conversion of SNAPSHOT standby to PHYSICAL standby
*******************************************************************

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
SQL>
SQL> alter database convert to physical standby;

Database altered.

SQL> SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
Database opened.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY            MAXIMUM PERFORMANCE  PHYSICAL STANDBY RECOVERY NEEDED

SQL>
SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL>
SQL> select RECOVERY_MODE from v$archive_dest_status;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE

32 rows selected.

SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL>
SQL>