Convert the Physical Standby Database into a Snapshot Standby Database

Manual: Convert the Physical Standby Database into a Snapshot Standby Database 

Reference: 11g Using Snapshot Standby Database. (Doc ID 443720.1)

Step:1 Please set db_recovery_file_dest if  not set.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /orabin/fast_recovery_area

db_recovery_file_dest_size           big integer 4122M

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

PRDDR    MOUNTED

SQL> select flashback_on from V$database;

FLASHBACK_ON

------------------

NO

Step:2 Cancel media recovery and validate.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select process,status from v$managed_standby;

PROCESS   STATUS

--------- ------------

ARCH      CLOSING

ARCH      CONNECTED

ARCH      CLOSING

RFS       IDLE

RFS       IDLE

9 rows selected.

Step:3 Please enable flashback.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from V$database;

FLASHBACK_ON

------------------

YES

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

no rows selected

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

MOUNTED              PHYSICAL STANDBY

Step:4 CONVERT database to snapshot mode.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

MOUNTED              SNAPSHOT STANDBY

Step:5 Open database in READ/Write mode.

SQL> alter database open;

Database altered.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

NAME

--------------------------------------------------------------------------------

GUA

---

SNAPSHOT_STANDBY_REQUIRED_01/29/2019 11:35:41

YES

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

READ WRITE           SNAPSHOT STANDBY

SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log

   WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax

    FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;  2    3

    Thread Last Seq Received Last Seq Applied

---------- ----------------- ----------------

         1                 1                1

Step:6 Shutdown database to convert back in physical standby mode.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

---

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2522791936 bytes

Fixed Size                  2161944 bytes

Variable Size             704643816 bytes

Database Buffers         1795162112 bytes

Redo Buffers               20824064 bytes

Database mounted.

---

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

MOUNTED              SNAPSHOT STANDBY

CONVERT database in physical mode.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> select open_mode,database_role from v$database;

select open_mode,database_role from v$database

                                    *

ERROR at line 1:

ORA-01507: database not mounted

SQL> startup mount;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> select status from v$instance;

STATUS

------------

STARTED

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

Step:7 Start database in mount mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2522791936 bytes

Fixed Size                  2161944 bytes

Variable Size             704643816 bytes

Database Buffers         1795162112 bytes

Redo Buffers               20824064 bytes

Database mounted.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

MOUNTED              PHYSICAL STANDBY

Step:8 Disable flashback in database.

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from gV$database;

FLASHBACK_ON

------------------

NO

Step:9 Start the media recovery and check replication.

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

SQL> select process,status from v$managed_standby;

PROCESS   STATUS

--------- ------------

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CLOSING

ARCH      CLOSING

RFS       IDLE

RFS       IDLE

RFS       IDLE

RFS       IDLE

MRP0      APPLYING_LOG

10 rows selected.

SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log

WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax

FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;

    Thread Last Seq Received Last Seq Applied

---------- ----------------- ----------------

         1             84591            84591

*********************************************************************************