[Oracle] How to convert a snapshot standby database into a physical standby database
ENVIRONMENT:
- Red Hat Linux 7
- Oracle Database 12
VARIABLES:
- MYDBNAME: database unique name of the 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!