Convert Physical Standby into Snapshot Standby

 

1️⃣ What is happening?

• Physical Standby = Exact copy of production database, read-only.

• Snapshot Standby = Temporary version of standby that is read/write, used for testing.

---

2️⃣ Why convert to Snapshot Standby?

• Suppose the application team wants to test new features or queries on real production data.

• Instead of making a new clone of production (which is slow and heavy), we can reuse the existing physical standby.

• Conversion allows safe testing without touching production.

---

3️⃣ Key Advantage

• Reversible: After testing, the snapshot standby can be converted back to physical standby.

• Repeatable: This cycle can be done any number of times – convert → test → revert → sync with production.

---

4️⃣ How it works (simple cycle)

  PRODUCTION DB
        |
        v
PHYSICAL STANDBY (read-only)
        |
   Convert to Snapshot
        v
SNAPSHOT STANDBY (read/write) → Testing happens here
        |
   Revert back
        v
PHYSICAL STANDBY (read-only) → Resumes syncing with production

---

5️⃣ Student-friendly analogy

• Physical Standby = Photocopy of homework (can look, but can’t write)

• Snapshot Standby = Practice sheet (you can write, erase, test)

• After testing → erase practice sheet and revert to original photocopy

---

💡 One-liner for memory:

“Snapshot Standby lets us safely test on production data using standby, then revert and sync back without touching production.”

Convert physical standby to snapshot standby: We will now convert the physical standby database to snapshot standby

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> select name, open_mode from v$database; 		>> make sure its mounted
SQL> alter database convert to snapshot standby;
SQL> alter database open;				>> open the DB
SQL> select name, open_mode, database_role from v$database;

Verifying snapshot standby: Now you must be able to read-write on snapshot standby. Meanwhile, we can even check the standby alert log. The archives received from primary are not applied on standby. We can even check that there is a guaranteed restore point has been created. So that when you convert snapshot back to physical standby, it will be used.

On standby:
===========

First check if oracle created restore Point when we convert physical standby to snanpshot standby before.

SQL> select name, guarantee_flashback_database from v$restore_point;

Now when you convert it to physical standby Oracle will flashback to this point and then convert this snapshot standby to physical standby

lets try do our testing assume development team are testing at this stage

SQL> create table ss_test(sno number(2));
SQL> insert into ss_test values(1);
SQL> insert into ss_test values (2);
SQL> commit;
SQL> select * from ss_test;

Revert back snapshot standby to physical standby: Once application testing is done, you can revert back snapshot standby to same point when it was converted from physical standby to snapshot standby

On standby:
===========
SQL> select name, open_mode, database_role from v$database;
SQL> shut immediate;
SQL> startup mount;
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database recover managed standby database disconnect; 

SQL> select * from ss_test;