Oracle
Data Guard
Snapshot Standby
Assumptions
A Physical Standby Exists
Data Guard Broker is configured and enabled (and using Oracle Wallet)
Commands below will be executed from the Primary database environment
EMAGENT_HOME is set in the 'oracle' .profile
Convert to Snapshot Standby
dgmgrl /@ORCL1
CONVERT DATABASE ORCL2 TO SNAPSHOT STANDBY;
Converting database "orcl2" to a Snapshot Standby database, please wait...Database "orcl2" converted successfully
SHOW CONFIGURATION;
Configuration - orcl_dg_config
Protection Mode: MaxPerformance Members: orcl1 - Primary database orcl2 - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS (status updated 10 seconds ago)
Revert to Physical Standby
dgmgrl /@ORCL1
CONVERT DATABASE ORCL2 TO PHYSICAL STANDBY;
Converting database "orcl2" to a Physical Standby database, please wait...Operation requires shut down of instance "ORCL2" on database "orcl2"Shutting down instance "ORCL2"...Connected to "ORCL2"Database closed.Database dismounted.ORACLE instance shut down.Operation requires start up of instance "ORCL2" on database "orcl2"Starting instance "ORCL2"...ORACLE instance started.Database mounted.Connected to "ORCL2"Continuing to convert database "orcl2" ...Database "orcl2" converted successfully
SHOW CONFIGURATION;
Configuration - orcl_dg_config
Protection Mode: MaxPerformance Members: orcl1 - Primary database orcl2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS (status updated 51 seconds ago)
SHOW DATABASE ORCL2;
Database - orcl2
Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 8.00 KByte/s Real Time Query: OFF Instance(s): ORCL2
Database Status:SUCCESS
Automated Snapshot Standby
Use this if you want to open the Physical Standby as a Snapshot Standby on a regular basis for testing without the need for DBA involvement. chown oracle:oinstall /u01/dba/auto/scripts/oradgbsnapstandb.sh
chmod 700 /u01/dba/auto/scripts/oradgbsnapstandb.sh
Scheduling
To check if the automated Snapshot Standby functionality is already scheduled, run this (as 'oracle') on the Primary and all Standby database servers..
crontab -l | grep oradgbsnapstandb.sh
To schedule, add something like this in the 'oracle' crontab... where ORCL1 is your Primary and ORCL2 is your Physical Standby...
00 09 * * 1-5 /u01/dba/auto/scripts/oradgbsnapstandb.sh 1 ORCL1 ORCL2 >/u01/dba/auto/logs/oradgbsnapstandb.$(date +"\%Y\%m\%d\%H\%M\%S").log 2>&100 16 * * 1-5 /u01/dba/auto/scripts/oradgbsnapstandb.sh 0 ORCL1 ORCL2 3600 >/u01/dba/auto/logs/oradgbsnapstandb.$(date +"\%Y\%m\%d\%H\%M\%S").log 2>&1
The script can be scheduled on either server, but should not be scheduled on both.
For consistency it is recommended to schedule the script on the Server that is normally the Physical Standby.
The script should normally appear twice in the crontab: once as oradgbsnapstandb.ksh 1 to enable the snapshot standby and once as oradgbsnapstandb.ksh 0 to disable the snapshot standby (convert back to Physical Standby).
The example above opens the Snapshot Standby at 09:00 and reverts to Physical Standby at 16:00 each weekday (i.e. excludes Saturday and Sunday).
Manual Run
You can initiate the script from the command line as 'oracle' for ad-hoc Snapshot Standby invocation...
PRIMARY=ORCL1
STANDBY=ORCL2
WAIT=3600
/u01/dba/auto/scripts/oradgbsnapstandb.sh 1 ${PRIMARY} ${STANDBY}
/u01/dba/auto/scripts/oradgbsnapstandb.sh 0 ${PRIMARY} ${STANDBY} ${WAIT}
/u01/dba/auto/scripts/oradgbsnapstandb.sh s ${PRIMARY} ${STANDBY}
Arguments: 1 = Enable, 0 = Disable, s = Status report
The script automatically starts and ends a blackout. You do not need to do this is a separate task.
WAIT specifies the seconds to wait for the Standby to get back in sync before ending blackout (default 3600).
After running the script to disable the Snapshot Standby, you may want to run this script to report downtime.
/u01/dba/auto/scripts/oradbdowntimerep.sh
SYS Password Changes
When the SYS password changes you should do this...
for SID in ${DGSID[@]}do mkstore -wrl ${WALLPATH} -modifyCredential ${SID} SYS dgm="'show database ${SID} StaticConnectIdentifier'" crd=$(echo ${dgm} | xargs dgmgrl -silent /@${SID} | tail -1 | awk '{ print substr($3,2,length($3)-2) }' ) mkstore -wrl ${WALLPATH} -modifyCredential ${crd} SYSdoneYou will be prompted for the SYS password and the Wallet password for each entry
Initial Setup
mkdir -p /u01/dba/auto/scripts
mkdir -p /u01/dba/auto/logs
SQLNET.ORA
SQLNET.ORA must contain this stanza...
(assuming your Oracle Wallet is in /home/oracle/wallet)WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /home/oracle/wallet) ) )SQLNET.WALLET_OVERRIDE = TRUETNSNAMES.ORA
TNSNAMES.ORA must contain entries for the Primary and Standby databases...
ORCL1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=host1)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL1) ) )
ORCL2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=host2)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL2) ) )
LISTENER.ORA
LISTENER.ORA must contain this stanza on the Primary...
(SID_DESC = (GLOBAL_DBNAME = ORCL1_DGMGRL.mydomain) (ORACLE_HOME = /u01/app/oracle/product/121020ORCL1) (SID_NAME = ORCL1) )..and this stanza on the Standby...
(SID_DESC = (GLOBAL_DBNAME = ORCL2_DGMGRL.mydomain) (ORACLE_HOME = /u01/app/oracle/product/121020ORCL2) (SID_NAME = ORCL2) )These stanzas go in the SID_LIST section under SID_LIST_LISTENER. For a full example see the LISTENER.ORA page.Troubleshooting
To troubleshoot the automated Snapshot Standby script, review the logs in /u01/dba/auto/logs and the alert.log of both database instances.
Example Issue #1 (ORA-01013)
Log in /u01/dba/auto/logs shows...
------------------------------------------------------Converting to Physical Standby------------------------------------------------------Converting database "orcl2" to a Physical Standby database, please wait...Operation requires shutdown of instance "ORCL2" on database "atlas"Shutting down instance "ORCL2"...ORA-01013: user requested cancel of current operationPlease complete the following steps and reissue the CONVERT command: shut down instance "ORCL2" of database "atlas" start up and mount instance "ORCL2" of database "atlas"
Alert log for ORCL2 shows...
Tue Oct 25 17:01:08 2022Active call for process 42205378 user 'oracle' program 'oracle@myhost'SHUTDOWN: Active sessions prevent database close operation Shutdown did not complete in 60 minutesInstance shutdown cancelledTo fix, on the Standby...
Ensure WALLPATH is set to the correct Wallet.
Ensure TNS_ADMIN points to the correct TNS location (if you are not using the default)
sqlplus / as sysdba
shutdown immediate
startup mount
exit
dgmgrl /@ORCL1
CONVERT DATABASE ORCL2 TO PHYSICAL STANDBY;
exit
This will again prompt to shutdown/startup mount.
sqlplus / as sysdba
shutdown immediate
startup mount
exit
Wait for database apply lag to decrease to 0.
Troubleshooting
Start by attempting to invoke Snapshot Standby manually using debug mode...
dgmgrl -debug /@${ORACLE_SID}
Confirm that the connections exactly match the entries in the Wallet