Data Guard Networking Tips and Tricks

Transcript

1. Data Guard Networking Tips and Tricks Utah Oracle User Group - May 14, 2026 Sean Scott Managing Principal Consultant — Viscosity North America Oracle ACE Director
2. 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
3. Oracle on Docker Running Oracle Databases in Linux Containers Free sample chapter: h tt ps://oraclesean.com
4. 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
5. www.viscosityna.com @ViscosityNA 99.8%* of Data Guard issues are network-related * A number I made up
6. www.viscosityna.com @ViscosityNA 99.8%* of Data Guard issues are network-related (Layer 8 is part of your network) * Feels accurate now, doesn't it?
7. www.viscosityna.com @ViscosityNA Connect Identifiers
8. www.viscosityna.com @ViscosityNA Static CID - The one that can safely be wrong up until it matters Connect Identifiers (CID) in Data Guard The Static CID is instance-specific and used by the Data Guard Broker to remotely start database instances. It references a static service to connect to a stopped or otherwise unreachable remote database when: • Performing switchover from primary to standby • Reinstating a failed primary after failover • Reverting a Snapshot Standby to Physical Standby
9. www.viscosityna.com @ViscosityNA Static CID - The one that can safely be wrong up until it matters Connect Identifiers (CID) in Data Guard The listener for every member in a Data Guard configuration must include an entry for Data Guard's static service: {db_unique_name}_DGMGRL • Static listeners must be configured for local_listener • The Static CID must NEVER reference a service on a SCAN listener
10. www.viscosityna.com @ViscosityNA Static CID rules Connect Identifiers (CID) in Data Guard NEVER create TNS entries for the _DGMGRL or _DGB services NEVER use the Static CID for other purposes, including: • DBA connections • RMAN • Applications • Users • Anything else
11. www.viscosityna.com @ViscosityNA Data Guard con fi gures the Static CID. Let it. Connection Identifiers, Services, and the Listener The default value for Static CID is based on local_listener The default is always correct The Broker automatically updates the Static CID when instances start on different hosts
12. www.viscosityna.com @ViscosityNA Data Guard con fi gures the Static CID. Let it. Connection Identifiers, Services, and the Listener The default value for Static CID is based on local_listener The default is always correct The Broker automatically updates the Static CID when instances start on different hosts Why is this important?
13. www.viscosityna.com @ViscosityNA Data Guard con fi gures the Static CID. Let it. Connection Identifiers, Services, and the Listener The default value for Static CID is based on local_listener The default is always correct The Broker automatically updates the Static CID when instances start on different hosts UNLESS the DBA changes the Static CID or the value of local_listener
14. www.viscosityna.com @ViscosityNA Data Guard CID - What DG Broker uses to connect to members Connect Identifiers (CID) in Data Guard A proper Data Guard CID must: • ...allow all members in a configuration to reach every other member • ...allow the member to reach itself
15. www.viscosityna.com @ViscosityNA Follow these guidelines now, avoid problems later General Rules for Connection Identifiers Always: • Reference the db_unique_name service in the Data Guard CID • Set remote_listener to the SCAN address (RAC) • Use SCAN for the Data Guard CID (RAC)
16. www.viscosityna.com @ViscosityNA Follow these guidelines now, avoid problems later General Rules for Connection Identifiers Never: • Change the Static CID; let Data Guard set it • Use SCAN for the Static CID Set or change the Static CID • Set local_listener (it defaults to hostname:port) • Use any service for Data Guard beside the default (db_unique_name) • Use clusterware-managed services for the Data Guard CID
17. www.viscosityna.com @ViscosityNA Follow these guidelines now, avoid problems later General Rules for Connection Identifiers The DG Broker expects a static service based on local_listener: • hostname • port • db_unique_name • db_domain ...and suffixed by _DGMGRL
18. www.viscosityna.com @ViscosityNA Follow these guidelines now, avoid problems later General Rules for Connection Identifiers For clustered environments, the Data Guard and Static CIDs should not be the same. For non-clustered environments, the Data Guard and Static CIDs should be identical.
19. www.viscosityna.com @ViscosityNA Follow these guidelines now, avoid problems later General Rules for Database Connectivity Don't use service_names or the default database service (db_unique_name) for client connections, instead, create a role-based database service via srvctl that's active only on primary: srvctl add service -db orcl -service tac_service -pdb pdb1 -preferred orclcdb1,orclcdb2 -failover_restore AUTO -commit_outcome TRUE -failovertype AUTO -replay_init_time 2400 -retention 86400 -notification TRUE -drain_timeout 300 -stopoption IMMEDIATE -role PRIMARY
20. www.viscosityna.com @ViscosityNA Follow these guidelines now, avoid problems later General Rules for Database Configuration Oracle recommends setting log_buffer to a minimum of 256 MB for databases with high redo rates using ASYNC transport. This allows Data Guard to read redo from the log buffer, bypassing I/O on redo logs.
21. www.viscosityna.com @ViscosityNA Avoid dependencies on TNS fi les Data Guard CID and EZConnect When Data Guard relies on the tnsnames.ora, changes there can silently break Data Guard. Instead, use EZConnect to define the Data Guard CID, eg: scan-name:1521/db_unique_name (RAC) hostname:1521/db_unique_name (non-RAC)
22. www.viscosityna.com @ViscosityNA Avoid dependencies on TNS fi les Data Guard CID and EZConnect Be weary of iFiles in TNS configurations used by Data Guard! • TNS files are are evaluated sequentially (top to bottom) • Duplicate entries in iFiles can confuse/create unintended behavior • iFiles can be nested, but Oracle only evaluates three levels
23. www.viscosityna.com @ViscosityNA Data Guard Configuration
24. www.viscosityna.com @ViscosityNA The Broker makes everything easier Use the Data Guard Broker The Broker simplifies every aspect of Data Guard configuration, management, and monitoring. In Oracle AI Database 26ai, Broker commands are available via: • Data Guard Broker CLI (dgmgrl) • SQLcl • PL/SQL API • REST API
25. www.viscosityna.com @ViscosityNA The Broker makes everything easier Use the Data Guard Broker In Oracle AI Database 26ai, the Broker adds automatic primary database preparation, including: • Setting archive log mode • Creating an spfile and setting the necessary parameters • Activating Flashback and the required logging • Creating standby redo logs • Adding an archive log deletion policy in RMAN
26. www.viscosityna.com @ViscosityNA Set thresholds in the Broker Use the Data Guard Broker The Broker can generate warnings when communication between primary and standby is interrupted or when transport/apply lag grows. • ApplyLagThreshold (900) • TransportLagThreshold (900) • TransportDisconnectedThreshold (30) Values are in seconds; setting these to zero (0) disables warnings
27. www.viscosityna.com @ViscosityNA Network Configuration
28. www.viscosityna.com @ViscosityNA Use the Bandwidth Delay Product to " fi ll the pipe" Network Configuration Redo transport doesn't always fully utilize a network's capabilities. You can optimize Data Guard TNS using the Bandwidth Delay Product (BDP): bandwidth (Kb/s) x latency Maximize socket buffer size for redo transport by setting the SEND_BUF_SIZE & RECV_BUF_SIZE to BDP x 3.
29. www.viscosityna.com @ViscosityNA Use the Bandwidth Delay Product to " fi ll the pipe" Network Configuration Example: bandwidth = 24 Mb/s and latency = 15 ms, set TCP Buffers to: ((24 Mb/s x 1000 Kb/Mb) ----------------------- x .015s x 3 8 Kb/s = 135 Kb = 138240
30. www.viscosityna.com @ViscosityNA Use the Bandwidth Delay Product to " fi ll the pipe" Network Configuration However, BDP-based values may not be practical for all connections and setting them in sqlnet.ora is not always the best option. Fortunately, listener.ora, TNS aliases and EZConnect descriptors include options for socket buffer sizing: standby = (DESCRIPTION = (SEND_BUF_SIZE = 138240) (RECV_BUF_SIZE = 138240) (ADDRESS = (PROTOCOL=tcp)(HOST=stby_host)(PORT=1521) ) (CONNECT_DATA = (SERVICE_NAME=standby)) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = stby_host) (PORT = 1521) (SEND_BUF_SIZE = 9375000) (RECV_BUF_SIZE = 9375000) ) )
31. www.viscosityna.com @ViscosityNA Use the Bandwidth Delay Product to " fi ll the pipe" Network Configuration BDP sizing applies to both ASYNC and SYNC transport, but remember: • Socket buffer size must be equal across databases in a configuration • Set appropriate values for net.core.rmem_max and net.core.wmem_max
32. www.viscosityna.com @ViscosityNA Set SDU and MTU size (SYNC transport only) Network Configuration Oracle recommends setting Session Data Unit (SDU) to 65536 to maximize SYNC transport. For the database, set it in listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SDU = 65536) ...
33. www.viscosityna.com @ViscosityNA Set SDU and MTU size (SYNC transport only) Network Configuration Oracle recommends setting Session Data Unit (SDU) to 65536 to maximize SYNC transport. Set it for all connections in sqlnet.ora: DEFAULT_SDU_SIZE = 65536
34. www.viscosityna.com @ViscosityNA Set SDU and MTU size (SYNC transport only) Network Configuration Oracle recommends setting Session Data Unit (SDU) to 65536 to maximize SYNC transport. Set it on a per-connection basis in tnsnames.ora: STANDBY = (DESCRIPTION = (SDU = 65536) ...
35. www.viscosityna.com @ViscosityNA Set SDU and MTU size (SYNC transport only) Network Configuration Increasing Maximum Transmission Unit (MTU) size can increase throughput for SYNC transport by 2-8x by reducing round-trips. Oracle recommends MTU=9000 for cross-region replication. • Test in non-production environments using oratcp to identify optimal values for SDU and MTU • See KB150844: Assessing and Tuning Network Performance for Data Guard and RMAN for more details on testing network throughput
36. www.viscosityna.com @ViscosityNA Network Configuration
37. www.viscosityna.com @ViscosityNA Redo Log Configuration
38. www.viscosityna.com @ViscosityNA Standards for redo log and standby redo log sizing and placement Redo Log Configuration Undersized redo logs add database and network pressure. The ideal redo log size produces at most four log switches per hour for peak (not average) redo rate. • Redo and standby redo logs must be the same size & block size • Create (redo log groups + 1) standby redo logs per thread • Place standby redo logs on the fastest available disk group/volume • Never multiplex standby redo logs!
39. www.viscosityna.com @ViscosityNA Calculating Peak Redo Rate select thread# , sequence# , blocks * block_size / 1024 / 1024 MB , (next_time - first_time) 86400 SEC , (blocks block_size / 1024 / 1024) / ((next_time - first_time) 86400) MBPS from v$archived_log where ((next_time - first_time) * 86400 != 0) and first_time between sysdate - 7 and sysdate and dest_id = 2 order by first_time;
40. www.viscosityna.com @ViscosityNA Common redo log related errors/issues/oversights Redo Log Configuration • Peak redo rate changes over time; revisit this calculation 2-4 times annually and resize logs as needed • For RAC, evaluate SRL counts on a per-thread basis, especially when primary/standby hosts are asymmetric • Check for redo and standby redo logs created for missing or non- existent threads (common when converting to/from RAC, adding/ dropping nodes)
41. www.viscosityna.com @ViscosityNA Evaluating Redo/Standby Redo Log Configuration set pages 999 lines 300 col tstatus heading "THREAD|STATUS" col instance for a16 col member for a100 break on thread# on tstatus on enabled on instance skip 1 on type skip 1 on group# select l.thread# , t.status as tstatus , t.enabled , t.instance , f.type , l.group# , l.bytes/1024/1024 as size_mb , l.blocksize , l.status , f.member from v$logfile f , v$thread t ,(select thread# , group# , bytes , blocksize , status from v$log union select thread# , group# , bytes , blocksize , status from v$standby_log) l where f.group# = l.group# and l.thread# = t.thread# (+) order by 1, 2, 3, 4, 5, 6;
42. www.viscosityna.com @ViscosityNA THREAD THREAD# STATUS ENABLED INSTANCE TYPE GROUP# SIZE_MB BLOCKSIZE STATUS MEMBER ------- ------ ------- -------- ------- ------ ------- --------- ---------- -------- 1 OPEN PUBLIC dg26ai ONLINE 1 200 512 INACTIVE /u03/... 2 200 512 INACTIVE /u03/... 3 200 512 CURRENT /u03/... STANDBY 4 200 512 UNASSIGNED /u03/... 5 200 512 UNASSIGNED /u03/... 6 200 512 UNASSIGNED /u03/... 7 200 512 UNASSIGNED /u03/... Evaluating Redo/Standby Redo Log Configuration
43. www.viscosityna.com @ViscosityNA Leveraging FarSync
44. www.viscosityna.com @ViscosityNA FarSync has bene fi ts beyond SYNC transport over distance Leveraging FarSync Max Availability and Max Protection use SYNC transport, but high latency between primary/standby can impact primary performance. A typical FarSync configuration has: • A primary & FarSync instance co-located, using SYNC transport • FarSync cascading redo to a distant standby via ASYNC transport However, FarSync can perform redo log compression and encryption, allowing this work to be offloaded from the primary database.
45. www.viscosityna.com @ViscosityNA Monitoring and Validation
46. www.viscosityna.com @ViscosityNA 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>; Useful Validate Commands
47. www.viscosityna.com @ViscosityNA 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. Validate Database (Primary)
48. www.viscosityna.com @ViscosityNA 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 Validate Database (Standby)
49. www.viscosityna.com @ViscosityNA 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 Validate Database (Standby)
50. www.viscosityna.com @ViscosityNA 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. Validate Database SPFile (Standby)
51. www.viscosityna.com @ViscosityNA 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 ... Validate Database SPFile (Standby)
52. www.viscosityna.com @ViscosityNA DGMGRL> VALIDATE DGConnectIdentifier ORION:1521/orion At instance 'dg26ai' of member 'orion' 'orion:1521/orion' translates to: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orion))(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.0.3)(PORT=1521))) Environment Variables: TNS_ADMIN: /u01/app/oracle/product/23.26/dbhome_1/network/admin ORACLE_HOME: /u01/app/oracle/product/23.26/dbhome_1 ORACLE_BASE: /u01/app/oracle Initialization Parameters: LOCAL_LISTENER: LISTENER_DG26AI Connection Test Result: Connected to instance 'dg26ai' at member 'orion' ... Validate DGConnectIdentifier
53. www.viscosityna.com @ViscosityNA ... At instance 'dg26ai' of member 'taurus' 'orion:1521/orion' translates to: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orion))(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.0.3)(PORT=1521))) Environment Variables: TNS_ADMIN: /u01/app/oracle/product/23.26/dbhome_1/network/admin ORACLE_HOME: /u01/app/oracle/product/23.26/dbhome_1 ORACLE_BASE: /u01/app/oracle Initialization Parameters: LOCAL_LISTENER: LISTENER_DG26AI Connection Test Result: Connected to instance 'dg26ai' at member 'orion' Validate DGConnectIdentifier (Cont)
54. www.viscosityna.com @ViscosityNA DGMGRL> validate network configuration for all Validating at "orion" Instance "dg26ai": successfully connected to instance "dg26ai" on member "taurus" Validating at "taurus" Instance "dg26ai": successfully connected to instance "dg26ai" on member "orion" Oracle Clusterware is not configured on database "orion". Connecting to database "orion" using static connect identifier "(DESCRIPTION= ... Succeeded. The static connect identifier allows for a connection to database "orion". Oracle Clusterware is not configured on database "taurus". Connecting to database "taurus" using static connect identifier "(DESCRIPTION= ... Succeeded. The static connect identifier allows for a connection to database "taurus". Validate Network Configuration
55. www.viscosityna.com @ViscosityNA DGMGRL> validate static connect identifier for all Oracle Clusterware is not configured on database "orion". Connecting to database "orion" using static connect identifier "(DESCRIPTION= ... Succeeded. The static connect identifier allows for a connection to database "orion". Oracle Clusterware is not configured on database "taurus". Connecting to database "taurus" using static connect identifier "(DESCRIPTION= ... Succeeded. The static connect identifier allows for a connection to database "taurus". Validate Static Connect Identifier
56. 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
57. 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)
58. 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
59. 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
60. 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
61. 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?
62. 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?
63. Questions? Contact Me! sean.scott@viscosityna.com https://linktr.ee/oraclesean www.viscosityna.com