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; 

Rebuild Primary After Failover

 Post failover, there are two methods of rebuilding your failed primary

• Method 1:Rebuild from scratch RMAN duplicate

• Method 2: Flashback database only if Flashback was enabled

• 
  

In our earlier activity, we have performed Failover. Current state of your servers should be

because we have performed Failover.

action:

Let’s crash primary (test): In order to simulate failure, we will shut down the primary server test. As root user, shutdown the server without shutting down DB.

On new primary

newpr>Select to_char(standby_became_primary_scn) from v$database;

This is sin when standby became primary

On crush primary server  

connect and restart it and now server is up and running . So you admin told you that they have rebuild the server and you can connect to the server

1st step: 

Su - oracle

Start listener

Lsnrctl start listener

Sqlplus / as sysdba

Do not hist startup command here because check what is the role this server is ready to take after crush?

Answer : standby so crush primary is taking standby role now so do not hist status so standby should be in mount mode

And you should think the crush primary is still primary no it is taking standby role now

Take it to mount mode

stbSQL>startup mount;

Flashback the database back to the exact SCN where the standby was converted to primary

So on new primary what ever redo is generated we want that redo to flow back and to be applied onto the new standby that we are building now so with this we make it consistent with exact SCN number when standby became primary

prmSQL> flashback database to SCN 18151989;

Now we are telling data Guard configuration from now onward this server is going to act as standby 

So that’s why we need to convert this database to standby

prmSQL> alter database convert to physical standby;

prmSQL>Shut immediate;

SQL> exit

prm$Sqlplus / as sysdba

prmSQL>Startup mount;

Now start the MRP process to apply redo on new physical standby

prmSQL> alter database recover managed standby database disconnect;

At this stage check the sync of data Guard 

On new primary switch log

stbSQL> alter system switch log file;

/

/

On new physical standby check sync log

prmSQL> select process, status ,sequence# from v$managed_standby;

So with this we confirm our data Guard configuration is sync

Check client connectivity=================

To revert back to our original configuration simply do normal switchover action:

Perform Manual Switchover on Physical Standby

-----------------------------------------------------------

Connect to test database via client and keep querying below

sqlplus sys/sys@test as sysdba

 

select name, open_mode, db_unique_name, database_role from v$database;

 

NAME      OPEN_MODE      DB_UNIQUE_NAME         DATABASE_ROLE

--------- -------------- ---------------------- --------------

test    READ WRITE     test                 PRIMARY

Check primary and standby for any gaps

On primary:

===========

select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

 

On standby:

===========

select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;

We will first convert primary to standby and later standby to primary

On primary:

===========

select SWITCHOVER_STATUS from V$DATABASE;

You must see TO STANDBY or SESSIONS ACTIVE

SQL>alter database commit to switchover to physical standby with session shutdown;

 

SQL>startup mount;

At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary.

select name, open_mode, db_unique_name, database_role from v$database;

Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary

select SWITCHOVER_STATUS from V$DATABASE;

 

SQL>alter database commit to switchover to primary with session shutdown;

 

SQL> alter database open;

At this stage, the client query would execute successfully!

On new standby – Initially your primary database: Start MRP

SQL>alter database recover managed standby database disconnect;

Data Guard Startup & Shutdown Steps

Oracle Data Guard Startup & Shutdown Steps

Follow the correct steps to start up and shut down Oracle Data Guard environments.

In this article we will look at Oracle Data Guard startup and shutdown sequence. You must follow proper shutdown order to perform a graceful shutdown.

Data Guard Shutdown Sequence

Stop log apply service or MRP and shutdown the standby

on Standby

=========

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> SHUT IMMEDIATE;

Stop log shipping from primary and shutdown primary database

on primary

========

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER';

SQL> SHUT IMMEDIATE;

Data Guard Startup Sequence

Startup primary database and enable log shipping

on primary

========

SQL> STARTUP;

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE';

Startup standby and enable log apply service or MRP

on standby

=========

SQL> startup nomount;

SQL> alter database mount standby database;

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