In many Oracle Data Guard environments, the standby database
is usually seen only as a DR database. But I have seen several customers use
their Snapshot Standby as a temporary sandbox for testing.
Before moving important changes to production whether it is
a patch, program change, or database modification they first validate
everything on snapshot standby. Since the standby stays very close to
production data, testing becomes much more reliable than using an outdated DEV
or TEST environment.
Recently, I had a similar requirement where the application
team wanted to validate some changes. Instead of refreshing a separate database
clone, we simply converted the physical standby into a Snapshot Standby and
used it for testing.
In fact they had a batch script in place which used to run
every night to keep the standby database in sync to make it very close to
actual production environment.
Environment details: -
|
Env details |
Primary DB |
Standby DB |
|
DB unique
Name |
Orcl |
DVDB |
|
DB name |
Orcl |
Orcl |
|
DB role |
Primary |
Standby |
|
DB Version |
19.29 |
19.29 |
|
OS Version |
Windows 2022 |
Windows 2022 |
Today I will show how to convert a physical standby database
into snapshot standby database.
In primary:-
FRA must be configured
Sufficient FRA space should exist
Standby should be mounted and healthy
|
SQL> show
parameter db_recovery NAME TYPE VALUE ------------------------------------
----------- ------------------------------ db_recovery_file_dest string D:\oracle\fast_recovery_area db_recovery_file_dest_size big integer 300G |
Check for archive log gap status in Primary:-
|
SQL>
select status,instance_name,database_role,open_mode from
v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------
---------------- ---------------- -------------------- OPEN orcl PRIMARY READ WRITE SQL>
select max(sequence#) from v$archived_log where archived='YES'; MAX(SEQUENCE#) -------------- 87655 |
In standby:-
|
SQL> show
parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------
----------- ------------------------------ db_recovery_file_dest string D:\oracle\fast_recovery_area db_recovery_file_dest_size big integer 200G SQL>
select max(sequence#) from v$archived_log where archived='YES'; MAX(SEQUENCE#) -------------- 87655 |
Now login to DGMGRL utility to perform conversion to
snapshot database
In standby:-
|
C:\Windows\system32>dgmgrl
sys@dvdb as sysdba DGMGRL for
64-bit Windows: Release 19.0.0.0.0 - Production on Fri May 22 13:32:26 2026 Version
19.29.0.0.0 Copyright (c)
1982, 2025, Oracle and/or its affiliates.
All rights reserved. Welcome to
DGMGRL, type "help" for information. Password: Connected to
"DVDB" Connected as
SYSDBA. DGMGRL>
show configuration Configuration
- my_dg_config Protection Mode: MaxPerformance Members: orcl
- Primary database dvdb
- Physical standby database Fast-Start
Failover: Disabled Configuration
Status: SUCCESS (status updated 39 seconds ago) |
Lets start conversion (in standby site)
|
DGMGRL>
CONVERT DATABASE dvdb to snapshot standby ; Converting
database "dvdb" to a Snapshot Standby database, please wait... Database
"dvdb" converted successfully DGMGRL>
show configuration Configuration
- my_dg_config Protection Mode: MaxPerformance Members: orcl
- Primary database dvdb
- Snapshot standby database Fast-Start
Failover: Disabled Configuration
Status: SUCCESS (status updated 50 seconds ago) |
|
SQL>
select status,instance_name,database_role,open_mode from
v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------
---------------- ---------------- -------------------- OPEN orcl SNAPSHOT STANDBY READ WRITE SQL> |
So this concludes the conversion of physical standby
database into standby database.
At this point we can start application and point to the
standby database as the database is currently in read write mode.
Note: Any changes made into standby database is not
permanent. They get removed whenever the database is converted back to physical
standby database.
No comments:
Post a Comment