[Oracle] How to convert a physical standby database into a snapshot standby database

Hi all,

in this tutorial we are going to convert a physical standby database into a snapshot standby database in a dataguard configuration.

First of all, check that the database is actually a physical standby:

select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

Next, stop the recovery process:

alter database recover managed standby database cancel;

And convert the database into a snapshot standby. This can be done trough SQL*Plus:

sqlplus / as sysdba

alter database convert to snapshot standby;

Or through the dgmgrl client:

dgmgrl /

convert database 'MYDBNAME' to snapshot standby;

NOTE: if you have a broker configuration enabled, use the dgmgrl client to convert the database, otherwise the configuration becomes stale.

Lastly, check if the database is actually a snapshot standby:

select database_role from v$database;

DATABASE_ROLE
----------------
SNAPSHOT STANDBY

That’s all, see you in the next tutorial!