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

Hi all,

in this tutorial we are going to convert a snapshot standby database into a physical standby database in a dataguard configuration. We also suppose that the database is a RAC database.

First of all, check when the database became a snapshot database:

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI';
select max(first_time)
from gv$archived_log
where applied='YES'
order by thread#,sequence# asc;

The output of the above query should indicate the time the database stopped applying logs from primary site.

Next, stop the cluster database and startup it in single-instance mode:

srvctl stop database -d MYDBNAME
startup mount restrict;

Next, be sure that the database is mounted and is in snapshot standby mode and that has a restore point to when it became a snapshot standby:

select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
MYDBNAME                       MOUNTED              SNAPSHOT STANDBY

select name, guarantee_flashback_database from v$restore_point;
NAME											  GUA
-------------------------------------------------   ---
SNAPSHOT_STANDBY_REQUIRED_07/17/2020 22:15:51       YES

Next, perform the status change. This can be done trough SQL*Plus:

sqlplus / as sysdba

alter database convert to physical standby;

Or through the dgmgrl client:

dgmgrl /

alter database 'MYDBNAME' convert to physical standby;

NOTE: if you have a broker configuration, use the dgmgrl client to change the database mode from snapshot standby to physical standby and viceversa. Do not use SQL*Plus, otherwise your broker configuration will become stale.

Now, check that your database is correctly set as physical standby database:

select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
MYDBNAME                       MOUNTED              PHYSICAL STANDBY

And start the redo logs apply:

alter database recover managed standby database using current logfile disconnect;

Lastly, check the apply lag through the dgmgrl client or through the following query:

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI';
select max(first_time)
from gv$archived_log
where APPLIED='YES'
order by thread#,sequence# asc;

When the database has finished applying logs, the output of the above query is equal to sysdate.

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