Embedded Files
Oracle
Assumptions
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
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
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
Blackout required on Standby only.
Wait until standby is synchronized with primary (i.e. Apply Lag is no more than a few seconds) before ending OEM Blackout
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. Automated Snapshot Standby
chown oracle:oinstall /u01/dba/auto/scripts/oradgbsnapstandb.sh
chmod 700 /u01/dba/auto/scripts/oradgbsnapstandb.sh
Scheduling
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
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
Automated Diagnostic Repository (used by oradbdowntimerep.sh)
SYS Password Changes
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
Initial Setup
mkdir -p /u01/dba/auto/scripts
mkdir -p /u01/dba/auto/logs
SQLNET.ORA
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
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
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
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)
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
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
Alert Log - Convert to Snapshot Standby
Alert Log - Convert to Snapshot Standby
2022-10-17 17:18:46.269000 +01:00ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCELMRP0: Background Media Recovery cancelled with status 16037Errors in file /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/ORCL2_pr00_27459596.trc:ORA-16037: user requested cancel of managed recovery operationManaged Standby Recovery not using Real Time ApplyRecovery interrupted!Recovered data files to a consistent state at change 11078668104575Errors in file /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/ORCL2_pr00_27459596.trc:ORA-16037: user requested cancel of managed recovery operationMRP0: Background Media Recovery process shutdown (ORCL2)2022-10-17 17:18:48.272000 +01:00Managed Standby Recovery Canceled (ORCL2)Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCELalter database convert to snapshot standbyStarting background process RVWRRVWR started with pid=7, OS id=294256842022-10-17 17:18:49.461000 +01:00Allocating 15937344 bytes in shared pool for flashback generation buffer.Allocated 15937344 bytes in shared pool for flashback generation bufferCreated guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/17/2022 17:18:48Killing 4 processes (PIDS:13107226,25034834,17694778,12910798) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 273287342022-10-17 17:18:51.491000 +01:00Begin: Standby Redo Logfile archivalEnd: Standby Redo Logfile archivalRESETLOGS after incomplete recovery UNTIL CHANGE 11078668104575 time 10/17/2022 17:18:55Resetting resetlogs activation ID 1180896164 (0x46630ba4)Online log /flashrecovery/ORCL2/ORCL2/onlinelog/o1_mf_1_knfp7zxv_.log: Thread 1 Group 1 was previously clearedOnline log /u02/oradata/ORCL2/ORCL2/onlinelog/o1_mf_1_knfp81ov_.log: Thread 1 Group 1 was previously clearedOnline log /flashrecovery/ORCL2/ORCL2/onlinelog/o1_mf_2_knfp83c1_.log: Thread 1 Group 2 was previously clearedOnline log /u02/oradata/ORCL2/ORCL2/onlinelog/o1_mf_2_knfp8563_.log: Thread 1 Group 2 was previously clearedOnline log /flashrecovery/ORCL2/ORCL2/onlinelog/o1_mf_3_knfp86ys_.log: Thread 1 Group 3 was previously clearedOnline log /u02/oradata/ORCL2/ORCL2/onlinelog/o1_mf_3_knfp88oz_.log: Thread 1 Group 3 was previously clearedOnline log /flashrecovery/ORCL2/ORCL2/onlinelog/o1_mf_4_knfp8bof_.log: Thread 1 Group 4 was previously clearedOnline log /u02/oradata/ORCL2/ORCL2/onlinelog/o1_mf_4_knfp8d8r_.log: Thread 1 Group 4 was previously clearedStandby became primary SCN: 11078668104573Setting recovery target incarnation to 3CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standbyCompleted: alter database convert to snapshot standby2022-10-17 17:18:52.856000 +01:00ALTER DATABASE OPENData Guard Broker initializing...AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only accessPing without log force is disabled.Assigning activation ID 1600058127 (0x5f5ef30f)Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /flashrecovery/ORCL2/ORCL2/onlinelog/o1_mf_1_knfp7zxv_.log Current log# 1 seq# 1 mem# 1: /u02/oradata/ORCL2/ORCL2/onlinelog/o1_mf_1_knfp81ov_.logSuccessful open of redo thread 1MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setARC2: Becoming the 'no SRL' ARCHSMON: enabling cache recovery2022-10-17 17:18:54.721000 +01:00Incremental checkpoint up to RBA [0x1.3.0], current log tail at RBA [0x1.16.0][27328734] Successfully onlined Undo Tablespace 2.Undo initialization finished serial:0 start:1597245540 end:1597246844 diff:1304 ms (1.3 seconds)Dictionary check beginningErrors in file /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/ORCL2_dbw0_28049506.trc:ORA-01186: file 201 failed verification testsORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/u02/oradata/ORCL2/ORCL2/datafile/o1_mf_temp_%u_.tmp'File 201 not verified due to error ORA-01157Dictionary check completeVerifying minimum file header compatibility (11g) for tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryErrors in file /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/ORCL2_dbw0_28049506.trc:ORA-01186: file 201 failed verification testsORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/u02/oradata/ORCL2/ORCL2/datafile/o1_mf_temp_%u_.tmp'File 201 not verified due to error ORA-01157Re-creating tempfile /u02/oradata/ORCL2/ORCL2/datafile/o1_mf_temp_%u_.tmp as /u02/oradata/ORCL2/ORCL2/datafile/o1_mf_temp_knv03h3m_.tmpStarting background process SMCOSMCO started with pid=35, OS id=17694782Database Characterset is WE8MSWIN12522022-10-17 17:18:55.711000 +01:00No Resource Manager plan active**********************************************************WARNING: Files may exists in db_recovery_file_destthat are not known to the database. Use the RMAN commandCATALOG RECOVERY AREA to re-catalog any such files.If files cannot be cataloged, then manually delete themusing OS command.One of the following events caused this:1. A backup controlfile was restored.2. A standby controlfile was restored.3. The controlfile was re-created.4. db_recovery_file_dest had previously been enabled and then disabled.**********************************************************2022-10-17 17:18:56.826000 +01:00replication_dependency_tracking turned off (no async multimaster replication found)2022-10-17 17:18:58.777000 +01:00LOGSTDBY: Validating controlfile with logical metadataLOGSTDBY: Validation completeStarting background process AQPCAQPC started with pid=39, OS id=250348502022-10-17 17:19:00.175000 +01:00Starting background process CJQ0CJQ0 started with pid=44, OS id=15204414Completed: ALTER DATABASE OPENRFS[5]: Assigned to RFS process (PID:19267724)RFS[5]: Selected log 6 for thread 1 sequence 89375 dbid 1180906920 branch 698842858RFS[5]: Selected log 5 for thread 1 sequence 89376 dbid 1180906920 branch 698842858Archived Log entry 47 added for thread 1 sequence 89375 ID 0x46630ba4 dest 1:2022-10-17 17:19:03.615000 +01:00QPI : Found directory objects and ORACLE_HOME out of syncQPI : Trying to patch with the current ORACLE_HOMEQPI: ------QPI Old Directories -------QPI: OPATCH_SCRIPT_DIR:/u01/app/oracle/product/12.1.0.2/QOpatchQPI: OPATCH_LOG_DIR:/u01/app/oracle/product/12.1.0.2/QOpatchQPI: OPATCH_INST_DIR:/u01/app/oracle/product/12.1.0.2/OPatchQPI: op_scpt_path /u01/app/oracle/product/121020ORCL2/QOpatchQPI: Found QPI install at :/u01/app/oracle/product/121020ORCL2/QOpatchQPI: Self adjusting the directoriesQPI: [2] Please check the QPI directory objects and set them manually===========================================================Dumping current patch information===========================================================No patches have been applied===========================================================
Page updated
Google Sites
Report abuse