Converting a DB to Physical Standby and Convert it Back to RW [For Testing Only]

 Introduction:

I had a requirement to test an application on an Active Data Guard ADG DB (19c), but I didn't have the time to build the same, I thought to share the quick steps of how to convert a DB to physical standby for the sake of testing for those who are interested.

Disclaimer: These steps I ran on non-production environment for the sake of testing, it's not meant to create an actual ADG setup using these steps, these steps are not applicable for production use as this approach will not maintain zero data loss. So please be careful!

Converting a normal DB to Standby:

-- Restart the DB in Mount mode:
SQL> SHU IMMEDIATE
SQL> STARTUP MOUNT
-- In case the DB is not in Archive Log mode:
SQL> alter database archivelog;
-- Convert the DB to Physical Standby:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> ALTER DATABASE OPEN;
-- Activate ADG mode: [I suppose you already aquiring the ADG license for the same ;-)]
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT;
 

Convert the Physical Standby back to a Read Write Primary DB:

-- Create a regular controlfile trace file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/noresetlog_control.trc' REUSE NORESETLOGS;
-- Comment the following lines in the generated trace file:
--RECOVER DATABASE
--ALTER SYSTEM ARCHIVE LOG ALL;
-- Restart the DB in NoMount mode to recreate the Controlfile:
SHU IMMEDIATE
STARTUP NOMOUNT
-- Create the Controlfile:
@/home/oracle/noresetlog_control.trc
-- The trace will automatically bring the DB in OPEN mode and will re-create the TEMPFILEs.