Oracle DBA

This procedural article describes configuring a Snapshot Standby (Data Guard) and exercising the convert-to-snapshot / convert-back lifecycle. Follow the steps precisely and validate each checkpoint in your environment before proceeding.

---

Step 1 — Verify roles on Primary and Standby

On Primary:

SQL> SELECT status, instance_name, database_role, open_mode
     FROM v$database, v$instance;

Sample output (Primary):

STATUS   INSTANCE_NAME   DATABASE_ROLE   OPEN_MODE
OPEN     class           PRIMARY         READ WRITE

On Standby:

SQL> SELECT status, instance_name, database_role, open_mode
     FROM v$database, v$instance;

Sample output (Standby):

STATUS   INSTANCE_NAME   DATABASE_ROLE      OPEN_MODE
MOUNTED  class           PHYSICAL STANDBY    MOUNTED

---

Step 2 — Confirm archived log synchronization

On the standby, check the highest archived sequence received:

SQL> SELECT thread#, MAX(sequence#) FROM v$archived_log GROUP BY thread#;

Sample:

THREAD#   MAX(SEQUENCE#)
1         37

---

Step 3 — Verify Flashback and FRA configuration on Standby

Flashback must be enabled on the standby for snapshot standby conversion:

SQL> SELECT flashback_on FROM v$database;
SQL> SHOW PARAMETER db_recovery_file_dest;

Sample:

FLASHBACK_ON
YES

NAME                         VALUE
db_recovery_file_dest        /u01/app/oracle/recovery_dest/
db_recovery_file_dest_size   2G

If FLASHBACK_ON is NO, enable flashback and set an adequate DB_RECOVERY_FILE_DEST before proceeding.

---

Step 4 — Stop managed recovery on Standby

Cancel the MRP (managed recovery) process on the standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Expected output:

Media recovery complete.

---

Step 5 — Bounce the standby to ensure clean state and keep it MOUNTED

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Confirm database is mounted.

---

Step 6 — Convert the Physical Standby to a Snapshot Standby

On standby (mounted):

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Expected:

Database altered.

---

Step 7 — Open the Snapshot Standby read/write

SQL> ALTER DATABASE OPEN;

Expected:

Database altered.

Confirm role and mode:

SQL> SELECT status, instance_name, database_role, open_mode
     FROM v$database, v$instance;

Sample:

STATUS  INSTANCE_NAME  DATABASE_ROLE     OPEN_MODE
OPEN    class          SNAPSHOT STANDBY   READ WRITE

---

Step 8 — Perform local testing on Snapshot Standby

Create users, tables, insert data and commit — these are local changes on the snapshot standby and will be discarded when the standby is converted back to physical standby.

Example:

SQL> CREATE USER test IDENTIFIED BY test;
SQL> GRANT CONNECT, RESOURCE TO test;
SQL> ALTER USER test QUOTA UNLIMITED ON USERS;

SQL> CONNECT test/test;
SQL> CREATE TABLE t1(sno NUMBER, name VARCHAR2(20));
SQL> INSERT INTO t1 VALUES (1, 'example');
SQL> COMMIT;
SQL> SELECT * FROM t1;

Sample output:

SNO  NAME
1    example

These operations validate the snapshot standby accepts local DML/DDL and can be used for reporting or testing.

---

Step 9 — Observe archived log progress (optional)

While the snapshot standby is open read/write it continues to receive and archive redo from primary but does not apply it. On both sides you can monitor archived sequences:

On Primary:

SQL> SELECT thread#, MAX(sequence#) FROM v$archived_log GROUP BY thread#;

On Standby:

SQL> SELECT thread#, MAX(sequence#) FROM v$archived_log GROUP BY thread#;

You should see the standby keeping pace with primary for archived log reception.

---

Step 10 — Prepare to convert back to Physical Standby

Before converting back, ensure local work on the snapshot is complete and that you want to discard all snapshot local updates. Converting back will throw away local writes and re-synchronize the standby by applying the accumulated redo.

Bounce the standby and mount it:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

---

Step 11 — Convert the Snapshot Standby back to a Physical Standby

On standby (MOUNTED):

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Expected:

Database altered.

---

Step 12 — Restart and open the standby (READ ONLY)

Bounce the instance and open:

SQL> SHUTDOWN IMMEDIATE;    -- if not already down
SQL> STARTUP;               -- this will mount and open per spfile/pfile

Verify mode:

SQL> SELECT status, instance_name, database_role, open_mode
     FROM v$database, v$instance;

Sample:

STATUS  INSTANCE_NAME  DATABASE_ROLE    OPEN_MODE
OPEN    class          PHYSICAL STANDBY  READ ONLY

---

Step 13 — Re-enable Managed Recovery (MRP)

Start MRP to resume applying the accumulated redo:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Expected:

Database altered.

Confirm MRP is running:

SQL> SELECT process, status, sequence# FROM v$managed_standby;

---

Step 14 — Understand implications for local snapshot DDL/DML

After converting back to physical standby and restarting MRP, any local changes made on the snapshot standby (for example, CREATE TABLE test.t1) are discarded — they do not exist on the restored physical standby. If you attempt to query the object created during the snapshot phase:

SQL> SELECT * FROM test.t1;

You will receive:

ORA-00942: table or view does not exist

This is expected behavior: the conversion to physical standby discards snapshot local updates and the standby is re-synchronized with the primary via redo apply.

---

Key operational notes & cautions

• Flashback must be enabled on the standby to convert to snapshot standby; otherwise conversion is not permitted. Ensure FLASHBACK_ON = YES and there is sufficient DB_RECOVERY_FILE_DEST space.
• Local updates on snapshot standby are ephemeral. Any DDL/DML performed on the snapshot will be lost when converting back to physical standby. If you need to preserve data created on the snapshot, export it (Data Pump) before converting back.
• Monitor archived redo: snapshot standby continues to receive archived redo but defers apply; ensure enough FRA space to hold accumulated archives until convert back and apply.
• Automation / scheduling: treat the snapshot standby lifecycle as a controlled operation — schedule maintenance windows, notify stakeholders, and document the intended duration of snapshot mode.
• Testing and isolation: snapshot standby is ideal for read/write testing, reporting, or patch testing in an isolated environment without impacting primary. Always verify test artifacts are exported if needed for retention.
• Security: ensure access control prevents unauthorized changes on the snapshot that could be mistakenly considered persistent.