Convert a Physical Standby Database into a Snapshot Standby Database

On the standby database stop redo apply.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE

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

OPEN         STBYDB           PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>shut immediate

SQL>startup mount

SQL> alter database convert to snapshot standby;

SQL> alter database open;

Database altered.

SQL> select status, instance_name, database_role,open_mode from v$database,v$Instance;

STATUS      INSTANCE_NAME   DATABASE_ROLE    OPEN_MODE

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

OPEN        STBYDB           SNAPSHOT STANDBY READ WRITE

Note: Oracle Database 11g introduced the Snapshot Standby feature in Data Guard.