Converting a RAC Physical Standby to a Snapshot Standby in a RAC

 

Converting a RAC Physical Standby to a Snapshot Standby in a RAC environment requires careful execution of specific steps. 

Here's the procedure, broken down:

---

Step 1: Pre-requisite

Ensure the following prerequisites are in place before proceeding:

1. FRA Configuration
   Ensure the Flash Recovery Area (FRA) is configured and sufficient space is available.

   Commands:

   SHOW PARAMETER db_recovery_file_dest;
   SHOW PARAMETER db_recovery_file_dest_size;
   

2. Online Redo Logs (ORL) Path
   Ensure that the Online Redo Logs (ORL) path exists and is correctly configured.

   Command to Check:

   SELECT thread#, group#, sequence#, bytes, archived, l.status, blocksize, member 
   FROM v$log l 
   JOIN v$logfile f USING (GROUP#) 
   ORDER BY thread#, group#;
   

---

Step 2: Prepare Standby Database

1. Stop the Standby Database
   Use srvctl to stop the database on the standby node.

   Command:

   srvctl stop database -d ORCLDR -o immediate

2. Start the Database in Mount Mode
   Start the database in mount mode to make the database available for changes.

   Commands:

   srvctl start database -d ORCLDR -o mount
   srvctl status database -d ORCLDR
   

   Ensure the status shows "MOUNTED".

---

Step 3: Enable Flashback on Standby Database

1. Enable Flashback and Set Flashback Retention Target
   Connect to SQL*Plus and enable flashback for the database. Set the db_flashback_retention_target for the required time (e.g., 1440 minutes for 24 hours).

   Commands:

   ALTER SYSTEM SET db_flashback_retention_target = 1440;
   CREATE RESTORE POINT before_snapshot_19c GUARANTEE FLASHBACK DATABASE;
   SELECT scn, storage_size, time, name FROM v$restore_point;
   

2. Ensure Database is Mounted and Flashback Is Enabled
   Verify the database is mounted and flashback is enabled.

   Commands:

   SELECT status FROM v$instance;
   ALTER DATABASE FLASHBACK ON;
   ALTER SYSTEM SET db_flashback_retention_target = <value> SCOPE=BOTH SID='*';
   SELECT flashback_on FROM v$database;
   SELECT * FROM v$flash_recovery_area_size;
   SELECT * FROM v$restore_point;
   

---

Step 4: Shutdown and Start Database in Mount Mode

1. Shutdown and Start the Database in Mount Mode Again

   Commands:

   srvctl stop database -d ORCLDR -o immediate
   srvctl status database -d ORCLDR
   srvctl start database -d ORCLDR -o mount
   srvctl status database -d ORCLDR
   

   Ensure the database is in "MOUNTED" state.

---

Step 5: Convert to Snapshot Standby

1. Connect and Convert to Snapshot Standby
   Connect to SQL*Plus and convert the physical standby database to a snapshot standby.

   Commands:

   SELECT status FROM v$instance;  -- Should show Mounted
   ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
   ALTER DATABASE OPEN;
   

2. Verify Database Role
   Once the conversion is done, verify the database role and controlfile type.

   Command:

   SELECT database_role, controlfile_type FROM v$database;

---

Step 6: Restart Database (if required)

1. Restart the Database if Needed
   If necessary, restart the database to ensure proper application of changes.

   Commands:

   srvctl stop database -d ORCLDR -o immediate
   srvctl status database -d ORCLDR
   srvctl start database -d ORCLDR -o open
   

---

Revert Back to Physical Standby

Once application testing is completed and confirmed by the application team, you may need to revert the snapshot standby back to a physical standby.

1. Shutdown DR Database
   Stop the DR database and restart it in mount mode.

   Commands:

   srvctl stop database -d ORCLDR -o immediate
   srvctl start database -d ORCLDR -o mount
   

2. Convert Back to Physical Standby
   Convert the database back to a physical standby.

   Commands:

   ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
   srvctl stop database -d ORCLDR -o immediate
   srvctl start database -d ORCLDR
   

3. Start Managed Recovery
   Start the managed recovery process to synchronize the physical standby with the primary.

   Command:

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

4. Disable Flashback and Drop Restore Point
   Turn off flashback and drop the restore point created earlier.

   Commands:

   ALTER DATABASE FLASHBACK OFF;
   DROP RESTORE POINT before_snapshot_19c;
   

---

Monitor DR Sync Progress

Monitor the Data Guard (DR) synchronization progress to ensure that the primary and standby databases are properly synchronized.

---

This procedure will help you safely convert a RAC physical standby database to a snapshot standby and revert it back when required, all while ensuring the integrity and stability of your environment.