Note: For snapshot standby, you do not need Flashback enabled at database level

Convert physical standby to snapshot standby

On Standby Database

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- -----------
TESTDB    MOUNTED

SQL> alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_02/28/2025
21:40:27'.
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL>  alter database open;

Database altered.

SQL>  select name, guarantee_flashback_database from v$restore_point;

NAME                                            GURA
----------------------------------------------  -------------

SNAPSHOT_STANDBY_REQUIRED_02/28/2025 21:41:04   YES


SQL> create table student(id number(2), name varchar2(10));

Table created.

SQL>  insert into student values(1,'John');

1 row created.

SQL> insert into student values (2,'James');

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from student;

       ID  NAME
---------- ----------
         1 John
         2 James

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             MOUNTED
PDB1                 MOUNTED

SQL>  alter pluggable database pdb1 open instances=all;

Pluggable database altered.

SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             MOUNTED
PDB1                 MOUNTED

Have to restart database for all PDB open.

$ srvctl stop database -d TESTDB -o immediate

$ srvctl start database -d TESTDB -o open

SQL>  col NAME format a20
SQL>  select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             READ ONLY
PDB1                 READ WRITE

SQL> select name, open_mode, database_role from v$database;

NAME                 OPEN_MODE            DATABASE_ROLE
-------------------- -------------------- ----------------
TESTDB               READ WRITE           SNAPSHOT STANDBY

Revert back snapshot standby to physical standby

SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ETSTDB    MOUNTED              SNAPSHOT STANDBY

SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance

$ srvctl stop  instance  -d TESTDB -i TESTDB2 -o immediate

SQL>  alter database convert to physical standby;

Database altered.

SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

$ srvctl start  database -d TESTDB -o mount

SQL> alter database recover managed standby database disconnect;

Database altered.