Convert Physical Standby To Snapshot Standby ~ OraEasy

Introduction: Snapshot standby is a feature since Oracle 11g that allows doing a read-write operation on the standby database. Suppose application team want to do testing on Fresh Live Data, but DBA cannot allow them to test on Primary. To help on this, DBA can convert physical standby to snapshot standby in read-write mode. Then application team can make their changes in snapshot database. Whatever changes done on snapshot standby will be rollbacked once convert back to physical standby database from snapshot standby. Primary database changes will not be applied to snapshot standby database as there is no MRP process running snapshot database.
Prerequisites:

Oracle Data Guard should be configured.
Disable DG broker if any as it will start MRP automatically.

Environment:

Server	Primary	Standby
Hostname	Source	Target
IP	192.168.80.51	192.168.80.111
OS	OEL 9	OEL 9
SID	ORCLDC	ORCLDR
Service Name	ORCLDC	ORCLDR

Now proceed to convert into Snapshot Standby Database activity step by step:

1. Check the sync between Primary and Standby
At Primary:


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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDC                         PRIMARY          READ WRITE

SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
 (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>  

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        250         250 14-APR-2025 12:27:43          0

At Standby:


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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>  

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDR                         PHYSICAL STANDBY          1        250         250 14-APR-2025 12:27:43          0

SQL>

2. Now on Standby, stop MRP & shutdown the database.


SQL> alter database recover managed standby database cancel;

Database altered.

SQL>
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';

no rows selected

SQL> create pfile='/home/oracle/drpfile.ora' from spfile;

File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3. Now Standby in mount & convert into Snapshot Standby.


SQL> startup mount
ORACLE instance started.

Total System Global Area 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size            1224736768 bytes
Database Buffers          218103808 bytes
Redo Buffers                7598080 bytes
Database mounted.
SQL>


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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY MOUNTED

SQL> alter database convert to snapshot standby;

Database altered.

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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         SNAPSHOT STANDBY MOUNTED

SQL> alter database open;

Database altered.

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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         SNAPSHOT STANDBY READ WRITE

Note: For RAC system where there are two or more nodes system. Shutdown the database on both the nodes. Then run the convert command on one node. Once completed then start the Database on other nodes.
4. At Primay you will observe the sync gap, but that is no worry.


SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        259         250 14-APR-2025 12:27:43          9

5. Now post application team work, you can do the rollback as below:


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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         SNAPSHOT STANDBY READ WRITE

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size            1224736768 bytes
Database Buffers          218103808 bytes
Redo Buffers                7598080 bytes
Database mounted.
SQL>
SQL> alter database convert to physical standby;

Database altered.

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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY MOUNTED

.6 Now start the MRP & verify the sync:


SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0      APPLYING_LOG          1        260        634

SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL> 

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        259         250          9 14-APR-2025 12:27:43       47.02           2821

SQL> /
DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        259         252          7 14-APR-2025 12:39:31       37.98           2279

SQL>
SQL> /

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        260         260          0 14-APR-2025 13:19:47        1.45             87

Thanks for visiting!!

OraEasy

Tuesday, May 27, 2025

Convert Physical Standby To Snapshot Standby

0 comments:

Post a Comment

Popular Posts

Recent Posts

Search This Blog

About us

Contact form

Technical Disclaimer