Never Name a Database "Standby" (and nine other things they don't teach in Data Guard school)

Transcript

1. Never Name a Database "Standby" (...and nine other things they don't teach in Data Guard school) CONTECH 25 - Bucharest 3 November 2025 Seàn Scott Oracle ACE Director Managing Principal Consultant Viscosity North America
2. Get the slides:
3. Database Reliability Engineering MAA ⁘ RAC ⁘ RMAN ⁘ ZDLRA ⁘ ASM Data Guard ⁘ Sharding ⁘ Partitioning Information Lifecycle Management Exadata & Engineered Systems Database Modernization Upgrades ⁘ Patching ⁘ Migrations Cloud ⁘ Hybrid Automation DevOps ⁘ IaC ⁘ Containers ⁘ Terraform Vagrant ⁘ Ansible Observability AHF ⁘ TFA ⁘ CHA ⁘ CHM
4. Oracle on Docker Running Oracle Databases in Linux Containers Free sample chapter: h tt ps://oraclesean.com
5. Tech Superstars Unite Get worldwide recognition as an Oracle ACE Oracle.com profile page Exclusive content Your own Oracle cloud account Swag, certification exam credit & event passes Networking events Travel support Learn more at: ace.oracle.com @oracleace Linkedin.com/groups/72183 @oracleace.bsky.social
6. 10 Conferences 15 Presentations 5 Panels 🌐 15 Time Zones 33 Days 54:28 Hours 15 Nations • 16 Airports • 20 Flights 43,826 km 23,664 nm and More Trains Trains Trains
7. www.viscosityna.com @ViscosityNA WARNING: Opinions Ahead!
8. Oracle, you OK?
9. www.viscosityna.com @ViscosityNA KISS: Don't Add Confusion to Chaos Keep things as consistent and intuitive as possible! Anything that isn't obvious makes troubleshooting harder for: • DBAs—even you—who are tired or stressed; • Consultants; • Oracle Support "BTW, PROD10G is actually running 19.26, L OL" PROD → PSTDBY ORA122 → STBY122
10. www.viscosityna.com @ViscosityNA Never name a database "Standby" Roles are temporary. Versions change. db_unique_name is forever.
11. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name As a general rule, consider using the SID + a location identifier. ORACLE_SID + LOCATION
12. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name As a general rule, airport codes make good location identifiers. PRODDWSAN - San Diego PRODDWSFO - San Francisco
13. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name As a general rule, airport codes make good location identifiers. These are easily identified at a glance (different last letters). PRODDWSAN - San Diego PRODDWSFO - San Francisco
14. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name Underscores help separate prefix from suffix. This is good for mixed/overlapping environments. PRODDWSAN PRODDWSFO PRODTXSAN PRODTXSFO PRODDW_SAN PRODDW_SFO PRODTX_SAN PRODTX_SFO San Diego Data Warehouse San Francisco Data Warehouse San Diego OLTP San Francisco OLTP
15. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name Still, an airport code isn't always the best choice. Aim for something that has visual clarity. PRODTX_PHX - Phoenix PRODTX_PDX - Portland
16. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name What about numbers? Could be misinterpreted as an instance name Difficult to associate to a role or location PRODTX1 PRODTX2
17. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name • OMF wasn't used; • RMAN duplicate attempted to restore control files; • Thinking SID2 was the standby, DBA deleted the "offending" files! ORA-01503: CREATE CONTROLFILE failed ORA-00200: control file could not be created ORA-00202: control file: '/u03/SID2/controlfile/control01.ctl' ORA-27038: created file already exists Additional information: 1
18. www.viscosityna.com @ViscosityNA Make Documentation "Copy/Paste-Proof" # This: switchover to $__target_db switchover to <target_db> # Harder to spot values that must be changed! switchover to prod_stdby These always fails if not corrected or the environment isn't set If this works, it may not do what you want!
19. www.viscosityna.com @ViscosityNA Set an Archive Log Deletion Policy Set a policy that includes both: • An applied on/shipped to clause • A backed up to device condition configure archivelog deletion policy to [applied on|shipped to] all standby backed up 2 times to <device>;
20. www.viscosityna.com @ViscosityNA shipped to alone is not a guarantee! Scenario: Daily log backups on primary Hourly log purge with 'sysdate - 1/24' on both primary & standby • All logs less than an hour old were deleted DBA suspended standby apply for maintenance: • Logs were shipped but not applied—satisfying the deletion policy • After apply resumed, deleted logs meant standby couldn't recover • Logs were also missing from the primary! Set an Archive Log Deletion Policy
21. www.viscosityna.com @ViscosityNA Standby redo logs are not online redo logs! They can be pulled from the primary • Data Guard does this automatically • As defined by FAL (Fetch Archive Log) parameters • ...assuming there's an appropriate archive log deletion policy! Multiplexing standby redo logs makes Data Guard slower! Don't Multiplex Standby Redo Logs
22. www.viscosityna.com @ViscosityNA Multiplex Broker Files Locate Broker configurations on separate disk groups! dg_broker_config_file1 dg_broker_config_file2
23. www.viscosityna.com @ViscosityNA Most common recommendation from DB review: Increase redo log size If the alert log frequently reports Checkpoint not complete • Add redo log groups • Check storage performance Size Redo Logs Properly
24. www.viscosityna.com @ViscosityNA Most common recommendation from DB review: Increase redo log size Strive for no more than four log switches per hour • Every log switch creates a corresponding action on the standby • Every log apply demands a reply from standby to primary In RAC environments with a single standby apply instance, frequent log switches can saturate the standby! Size Redo Logs Properly
25. www.viscosityna.com @ViscosityNA Low values increase checkpoint activity and increase standby load Sets the desired mean time to recover • Limits roll-forward after instance failure by issuing checkpoints • Forces DBWR to flush dirty blocks to disk • If set to 0, every commit issues a checkpoint! Oracle recommendations (from MOS Note 1095774.1) • Minimum of 300 • 3600 or the desired Recovery Time Objective Checkpoints and fast_start_mttr_target
26. www.viscosityna.com @ViscosityNA When primary & standby lose contact, the primary still advances; Initiating a failover incurs data loss (missing redo at standby); Reinstating the failed primary requires rolling back lost transactions • ...by using Flashback • ...or completely rebuilding the former primary Flashback is a Data Guard requirement! Turn on Flashback!
27. www.viscosityna.com @ViscosityNA You can but you don't want to Without Flashback, a partial restore may be possible if: • Primary and standby were at the same SCN at failover • No changes were made at the primary after failover Even if this criteria is met, the recovery steps require at minimum: • A partial incremental backup on the new primary using an SCN; • Recovery using the noredo option; • Usually includes manual file movement + RMAN catalog Recovering from a Failover without Flashback
28. www.viscosityna.com @ViscosityNA Comment Parameter Changes Helps people understand the parameter is Data Guard related. Future you thanks you! alter system set parameter=value comment='For Data Guard' scope=both sid='*';
29. www.viscosityna.com @ViscosityNA Use the Force Broker, Luke! edit database DB_UNQNAME set state='APPLY-OFF'; alter database recover managed standby database cancel;
30. www.viscosityna.com @ViscosityNA Changes to a configuration aren't immediately active. They must be applied via enable configuration. Enable the Configuration Following Changes
31. www.viscosityna.com @ViscosityNA RMAN> backup database plus archivelog; On a primary database, running in read-write mode, this command: • Backs up existing archive log files; • Backs up data files; • Performs a log switch; • Backs up archive log files generated during the backup. Offload Backups to a Standby
32. www.viscosityna.com @ViscosityNA RMAN> backup database plus archivelog; On a standby database, running in read-only mode, this command: • Backs up existing archive log files; • Backs up data files; • Performs a log switch; • Backs up archive log files generated during the backup. Without the log switch, the backup does not include the active redo, is not consistent, and cannot support a consistent recovery! Offload Backups to a Standby
33. www.viscosityna.com @ViscosityNA Offload Backups to a Standby Creates a consistent backup set on primary or standby; Automatically includes control file and necessary archive logs. backup consistent database; Solution 2: Make a consistent backup (19c+ and Active Data Guard only)
34. www.viscosityna.com @ViscosityNA Offload Backups to a Standby The plus archivelog option is unsupported (and unnecessary) backup consistent database plus archivelog; RMAN-06964: option consistent cannot be used with ALSPEC Solution 2: Make a consistent backup (19c+ and Active Data Guard only)
35. www.viscosityna.com @ViscosityNA Offload Backups to a Standby Does not support incremental backups! backup incremental level 0 consistent database; RMAN-06964: option consistent cannot be used with LEVEL Solution 2: Make a consistent backup (19c+ and Active Data Guard only)
36. www.viscosityna.com @ViscosityNA Offload Backups to a Standby Create a script that connects to the primary and forces a log switch: sqlplus sys/<password>@<primary db> as sysdba << EOF alter system archive log current; EOF Solution 1: Arti fi cially force a log switch (Data Guard, Active Data Guard)
37. www.viscosityna.com @ViscosityNA Offload Backups to a Standby Modify the backup to call the script, then back up archive logs. Block Change Tracking supported with additional configuration. configure controlfile autobackup on; backup database plus archivelog; host "/script_dir/force_log_switch.sh" backup archivelog all; Solution 1: Arti fi cially force a log switch (Data Guard, Active Data Guard)
38. www.viscosityna.com @ViscosityNA Network Configuration
39. www.viscosityna.com @ViscosityNA Risks associated with using TNS aliases in Data Guard environments Using TNS aliases adds a dependency on the local tnsnames.ora • Users may not realize/remember the aliases are used by Data Guard • tnsnames.ora may be updated, may not be under change control • Transport often survive such changes, but role transitions may not iFiles add another layer of risk! Changing local_listener silently prevents the Broker from managing the StaticConnectIdentifier setting! Data Guard and EZConnect
40. www.viscosityna.com @ViscosityNA Data Guard and EZConnect EZConnect strings disconnect Broker connections from TNS DGMGRL> show database verbose dgcdb_san Database - dgcdb_san Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): dgcdb Properties: DGConnectIdentifier = 'DGSAN.orapub.com:1521/dgcdb_san' ...
41. www.viscosityna.com @ViscosityNA Rules for the DGConnectIdentifier Used to communicate among members in the configuration Must allow connection to any/all members (including self-connection); Must support connections to all instances in RAC; Use a service that dynamically registers with listeners • Allows connect-time failover on RAC • Must not be defined or managed by Clusterware • Failover attributes must allow transport to any RAC instance DGConnectIdentifier
42. www.viscosityna.com @ViscosityNA Instance-speci fi c setting Data Guard uses to start databases Used for switchover, convert, and reinstate operations This connection is exclusively for the Data Guard Broker! Oracle sets StaticConnectIdentifier based on local_listener • ...auto-appends _DGMGRL to the service Oracle automatically manages this value unless users change: • The Broker's StaticConnectIdentifier • The local_listener parameter StaticConnectIdentifier
43. www.viscosityna.com @ViscosityNA NEVER use the StaticConnectIdentifier for other purposes, eg: • The DGConnectIdentifier • RMAN connections (eg, duplicate) • OEM or monitoring NEVER define static entries for the Broker's _DGB service! NEVER create TNS entries for the _DGB or _DGMGRL services! NEVER register Data Guard connections with a SCAN listener! Data Guard Connection No-Nos
44. www.viscosityna.com @ViscosityNA Shipping and apply work but role transitions will fail • Use the wrong Connect Identifiers • Change TNS aliases used by Data Guard connections • Missing or Incorrect static listener configurations • Create TNS aliases for Data Guard's reserved services • Manually configure/change log_archive_dest_n • Add Data Guard services to Clusterware • Alter the local_listener parameter How to Quietly Break Data Guard
45. www.viscosityna.com @ViscosityNA Useful show commands show configuration verbose; show configuration lag verbose; show configuration when primary is <DB_UNQNAME>; show database verbose <DB_UNQNAME>; show database <DB_UNQNAME> logxptstatus; show database <DB_UNQNAME> InconsistentProperties; show database <DB_UNQNAME> InconsistentLogXptProps; show instance verbose '<INSTANCE>' on database <DB_UNQNAME>;
46. www.viscosityna.com @ViscosityNA show configuration vs. show configuration lag DGMGRL> show configuration verbose; Configuration - dgcdb Protection Mode: MaxPerformance Members: dgcdb_san - Primary database dgcdb_lax - Physical standby database DGMGRL> show configuration lag verbose; Configuration - dgcdb Protection Mode: MaxPerformance Members: dgcdb_san - Primary database dgcdb_lax - Physical standby database Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago)
47. www.viscosityna.com @ViscosityNA show configuration when primary is ... DGMGRL> show configuration when primary is dgcdb_san Configuration when dgcdb_san is primary - dgcdb Members: dgcdb_san - Primary database dgcdb_lax - Physical standby database
48. www.viscosityna.com @ViscosityNA Useful validate commands validate database verbose <PRIMARY>; validate database verbose <STANDBY>; validate database verbose <STANDBY> spfile; validate network configuration for all; validate static connect identifier for all; validate dgconnectidentifier <CONNECT_IDENTIFIER>;
49. www.viscosityna.com @ViscosityNA validate database verbose <primary> DGMGRL> validate database verbose dgcdb_san Database Role: Primary database Ready for Switchover: Yes Flashback Database Status: dgcdb_san: On Capacity Information: Database Instances Threads dgcdb_san 1 1 Managed by Clusterware: dgcdb_san: NO Validating static connect identifier for the primary database dgcdb_san... Connecting to instance "dgcdb_san" on database "dgcdb_san" ... Connected to "dgcdb_san" Succeeded.
50. www.viscosityna.com @ViscosityNA validate database verbose <standby> DGMGRL> validate database verbose dgcdb_lax Database Role: Physical standby database Primary Database: dgcdb_san Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: dgcdb_san: On dgcdb_lax: On ... Standby Apply-Related Information: Apply State: Running Apply Lag: 0 seconds (computed 1 second ago) Apply Delay: 0 minutes
51. www.viscosityna.com @ViscosityNA validate database verbose <standby> (cont) Transport-Related Information: Transport On: Yes Gap Status: No Gap Transport Lag: 0 seconds (computed 1 second ago) Transport Status: Success ... Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (dgcdb_san) (dgcdb_lax) 1 7 8 Sufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (dgcdb_lax) (dgcdb_san) 1 7 8 Sufficient SRLs
52. www.viscosityna.com @ViscosityNA validate database verbose <standby> spfile DGMGRL> validate database verbose dgcdb_lax spfile; Command requires a connection that uses database or external credentials. DGMGRL> connect sysdg@DGLAX.orapub.com:1521/dgcdb_lax Password: Connected to "dgcdb_lax" Connected as SYSDG.
53. www.viscosityna.com @ViscosityNA validate database verbose <standby> spfile DGMGRL> validate database verbose dgcdb_lax spfile; Connecting to "dgcdb_san". Connected to "dgcdb_san" Connecting to "dgcdb_lax". Connected to "dgcdb_lax" Parameter Settings: audit_file_dest: dgcdb_san (PRIMARY) : /u01/app/oracle/admin/dgcdb_san/adump dgcdb_lax : /u01/app/oracle/admin/dgcdb_lax/adump audit_sys_operations: dgcdb_san (PRIMARY) : false dgcdb_lax : false ...
54. www.viscosityna.com @ViscosityNA Data Guard is broken if show/validate doesn't report SUCCESS There is no situation where a status of WARNING or ERROR is acceptable! SUCCESS from individual components ≠ overall SUCCESS • A configuration can succeed even if a database doesn't • You must check status in the Broker for ALL members! SUCCESS in the Broker still doesn't guarantee everything is OK! Accept Nothing Less Than SUCCESS
55. www.viscosityna.com @ViscosityNA When SUCCESS = FAILURE DGMGRL> show configuration Configuration - dgcdb Protection Mode: MaxPerformance Members: dgcdb_san - Primary database dgcdb_lax - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 47 seconds ago)
56. www.viscosityna.com @ViscosityNA When SUCCESS = FAILURE DGMGRL> show database dgcdb_lax Database - dgcdb_lax 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: 125.00 KByte/s Real Time Query: OFF Instance(s): dgcdb Database Status: SUCCESS
57. www.viscosityna.com @ViscosityNA When SUCCESS = FAILURE DGMGRL> show database verbose dgcdb_san Database - dgcdb_san ... Log file locations: Alert log : /u01/app/oracle/diag/rdbms/dgcdb_san/dgcdb/trace/alert_dgcdb.log Data Guard Broker log : /u01/app/oracle/diag/rdbms/dgcdb_san/dgcdb/trace/drcdgcdb.log Database Status: SUCCESS
58. www.viscosityna.com @ViscosityNA When SUCCESS = FAILURE Yet the Broker log shows: Data Guard Broker Status Summary: Type Name Severity Status Configuration dgcdb Warning ORA-16608: one or more members have warnings Primary Database dgcdb_san Success ORA-0: normal, successful completion Physical Standby Database dgcdb_lax Warning ORA-16809: multiple warnings detected for the member
59. www.viscosityna.com @ViscosityNA Redo shipping and apply continues normally, without lag Usually occurs following: • Host migration • Database upgrade • Changes to: • global_name, domain_name • log_archive_dest_* show = SUCCESS, log = FAILURE?
60. www.viscosityna.com @ViscosityNA Solution validate database verbose <standby> spfile • Check and correct inconsistencies Drop and recreate the configuration • Dropping the config shouldn't remove redo routes • Ship/apply continues during recreation Export, fix, and import the configuration • Check for bad entries in the XML show = SUCCESS, log = FAILURE?
61. www.viscosityna.com @ViscosityNA Export, Edit, & Import Configurations as XML DGMGRL> export configuration to my_config.txt DGMGRL> import configuration my_config.txt
62. www.viscosityna.com @ViscosityNA Export, Edit, & Import Configurations as XML File location is the Diagnostic trace directory (same as alert log). You cannot change this! DGMGRL> export configuration to '/home/oracle/dgcdb.txt'; ORA-16540: invalid argument ORA-06512: at "SYS.DBMS_DRS", line 1947 ORA-06512: at line 1
63. www.viscosityna.com @ViscosityNA <?xml version="1.0" encoding="UTF-8"?> <DRC Version="19.0.0.0.0" CurrentPath="True" Name="dgcdb"> <DefaultState>ONLINE</DefaultState> <DRC_UNIQUE_ID>705151955</DRC_UNIQUE_ID> ... <Member MemberID="1" CurrentPath="True" Enabled="True" MultiInstanced="True" Name="dgcdb_san"> <IntendedState>PRIMARY</IntendedState> <DefaultState>PRIMARY</DefaultState> <Status> <Severity>Success</Severity> <Error>0</Error> <Timestamp>1750881160</Timestamp> </Status> <StandbyType>PhysicalStandby</StandbyType> <DGConnectIdentifier>DGSAN.orapub.com:1521/dgcdb_san</DGConnectIdentifier> <DbDomain>orapub.com</DbDomain> <ResourceType>Database</ResourceType> <Instance InstanceID="1" CurrentPath="True" Enabled="True" MultiInstanced="True" DefaultWriteOnce="True" Name="dgcdb"> <PlannedState/> <HostName Default="True">DGSAN.orapub.com</HostName> <StaticConnectIdentifier Default="True">(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DGSAN.orapub.com)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=dgcdb_san_DGMGRL.orapub.com)(INSTANCE_NAME=dgcdb)(SERVER=DEDICATED))) </StaticConnectIdentifier>
64. www.viscosityna.com @ViscosityNA Or, why you can't trust everything on the web! Customer running EBS, Active Data Guard for eight years performed semi-annual switchover tests: • Quiesced the environment • Switched to standby • Confirmed connections with simple tests • Did not validate data/test transactions in the new database • Immediately switched back to the primary Checked lag using a widely reproduced/referenced query from a blog Don't Reinvent the Wheel!
65. www.viscosityna.com @ViscosityNA Don't Reinvent the Wheel! -- From the standby alert log: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Attempt to start background Managed Standby Recovery process (xxx) MRP0 started with pid=72, OS id=9446 MRP0: Background Managed Standby Recovery process started (xxx) started logmerger process Managed Standby Recovery not using Real Time Apply Warning: Datafile 1 (.../o1_mf_system_XXXXXXXX_.dbf) is infinitely media recovery fuzzy <snip> Media Recovery Log .../o1_mf_1_XXXXX_XXXXXXXX_.arc Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
66. www.viscosityna.com @ViscosityNA Don't Reinvent the Wheel! alter database open Data Guard Broker initializing... Data Guard - stopping apply to allow Active Data Guard enabled database to open ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL MRP0: Background Media Recovery cancelled with status 16037 <snip> Some recovered datafiles maybe left media fuzzy Media recovery may continue but open resetlogs may fail MRP0: Background Media Recovery process shutdown (xxx) Managed Standby Recovery Canceled (xxx) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Data Guard Broker initialization complete
67. www.viscosityna.com @ViscosityNA Don't Reinvent the Wheel! Beginning Standby Crash Recovery. Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Warning: Datafile 1 (.../o1_mf_system_XXXXXXXX_.dbf) is infinitely media recovery fuzzy Standby database will not open with this datafile online! Standby Crash Recovery aborted due to error 10554. ORA-10554: Media recovery failed to bring datafile 1 to a consistent point ORA-01110: data file 1: '.../o1_mf_system_XXXXXXXX_.dbf' Completed Standby Crash Recovery. ORA-10458: standby database requires recovery ORA-01196: file 1 is inconsistent due to a failed media recovery session ORA-01110: data file 1: '.../o1_mf_system_XXXXXXXX_.dbf' ORA-10458 signalled during: alter database open ... alter database open resetlogs ORA-1666 signalled during: alter database open resetlogs...
68. www.viscosityna.com @ViscosityNA An example of why you can't trust everything on the web! • Switchover and connection tests "worked" • TNS errors in the alert log were in a monitoring ignore list • The "fuzzy datafile" messages aren't "ORA-XXXXX" errors • The Broker log wasn't being monitored DBAs weren't using the Broker's built-in lag monitoring • Instead, they monitored lag using a popular internet query • See: https://oraclesean.com/blog/how-not-to-find-data-guard-gaps Don't Reinvent the Wheel!
69. Get the slides:
70. Questions? Contact Me! sean.scott@viscosityna.com https://linktr.ee/oraclesean www.viscosityna.com