Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Never Name a Database "Standby" (and nine other...

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

Seán Scott PRO

November 03, 2025

Transcript

71 slides
  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. 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. 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
  4. 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
  5. 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
  6. www.viscosityna.com @ViscosityNA Choose a "Good" DB Unique Name As a

    general rule, consider using the SID + a location identifier. ORACLE_SID + LOCATION
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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!
  14. 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>;
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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!
  21. 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
  22. 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='*';
  23. www.viscosityna.com @ViscosityNA Use the Force Broker, Luke! edit database DB_UNQNAME

    set state='APPLY-OFF'; alter database recover managed standby database cancel;
  24. www.viscosityna.com @ViscosityNA Changes to a configuration aren't immediately active. They

    must be applied via enable configuration. Enable the Configuration Following Changes
  25. 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
  26. 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
  27. 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)
  28. 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)
  29. 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)
  30. 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)
  31. 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)
  32. 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
  33. 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' ...
  34. 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
  35. 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
  36. 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
  37. 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
  38. 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>;
  39. 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)
  40. 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
  41. 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>;
  42. 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.
  43. 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
  44. 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
  45. 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 [email protected]:1521/dgcdb_lax Password: Connected to "dgcdb_lax" Connected as SYSDG.
  46. 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 ...
  47. 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
  48. 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)
  49. 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
  50. 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
  51. 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
  52. 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?
  53. 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?
  54. www.viscosityna.com @ViscosityNA Export, Edit, & Import Configurations as XML DGMGRL>

    export configuration to my_config.txt DGMGRL> import configuration my_config.txt
  55. 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
  56. 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>
  57. 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!
  58. 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
  59. 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
  60. 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...
  61. 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!

More Decks by Seán Scott

Other Decks in Technology