<- DataGuard

Configure Broker

Common Errors

Command Line Tools

 -- SQLPlus>
 sqlplus / as sysdba
 -- ASMCMD>
 asmcmd -p

Ensure FAL Entries Set

SQLPlus> show parameter fal

If You Need to Create\Change Them

 -- On Primary
 SQLPlus>
   alter system set fal_server='oradb_sb' SID='*' scope=both;
   alter system set fal_client='oradb'    SID='*' scope=both;

 -- On SB
 SQLPlus>
   alter system set fal_server='oradb'    scope=both;
   alter system set fal_client='oradb_sb' scope=both;

Note the single quotes used. This is to ensure same case used.

RAC\Oracle-Restart Prerequisites

Perform if using Oracle RAC or Oracle Restart

 -- On Both
 ASMCMD>
   mkdir +DATA/ORADB/DGBROKER
   mkdir +FRA/ORADB/DGBROKER

 SQLPlus>
   alter system set dg_broker_config_file1='+DATA/oradb/DGBROKER/dg_cfg1.dat' sid='*' scope=both;
   alter system set dg_broker_config_file2='+FRA/ORADB/DGBROKER/dg_cfg2.dat'  sid='*' scope=both;

 SQLPlus> show parameter dg_broker_config

• DG config files must reside on shared storage (ASM) for RAC nodes.
• If Primary is RAC and SB is Oracle-Restart create dirs on SB as they are on Primary:

Broker Configuration

 -- On Both
 SQLPlus> ALTER SYSTEM SET dg_broker_start=true  sid='*'  scope=both;
 SQLPlus> ALTER SYSTEM SET log_archive_dest_2='' sid='*' scope=both;

 -- On Primary
 dgmgrl sys/go@oradb

 DGMGRL> CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS oradb CONNECT IDENTIFIER IS oradb;
    Configuration "dg_cfg1" created with primary database "oradb"
 DGMGRL> ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
    Database "oradb_sb" added
 DGMGRL> ENABLE CONFIGURATION;
    Enabled.

• For a Standalone Standby the DG cfg files are created here: $ORACLE_HOME/dbs
• If show configuration; displays any ORA-16nnn errors they should resolve automatically in a few minutes. If not, on the SB:

Ensure Logs Being Transferred

 -- On Both
 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG 
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 
 GROUP BY THREAD#;

 -- On Primary
 ALTER SYSTEM SWITCH LOGFILE;

 -- On Both
 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG 
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 
 GROUP BY THREAD#;

Bounce Standby

 SQLPLUS> shutdown immediate
 SQLPLUS> exit
 sqlplus / as sysdba
 SQLPLUS> startup mount

Common Errors

Error

WARNING ORA-16714: the value of property

 show database oradb_sb StatusReport;

 STATUS REPORT
 INSTANCE_NAME SEVERITY ERROR_TEXT
 oradb_sb      WARNING ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
 oradb_sb      WARNING ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the member setting
 oradb_sb      WARNING ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the member setting
 oradb_sb      WARNING ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting <-- Introduced 18c

Solution

-- On Primary: Show Config Values

  show database verbose oradb;

-- On SB: Change Required Values

 show database verbose oradb_sb;

 alter system set log_archive_max_processes=4 scope=both sid='*';
 alter system set archive_lag_target=0 scope=both sid='*';
 alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
 alter system set data_guard_sync_latency=0 scope=both sid='*';

show database oradb_sb InconsistentProperties; may not show correctly.

Error: ORA-16698

DGMGRL> CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS oradb CONNECT IDENTIFIER IS oradb;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Solution

 -- On Primary
 DGMGRL> remove configuration;
         Removed configuration

 -- On Both
 SQLPlus> ALTER SYSTEM SET log_archive_dest_2='' scope=both;

Error: ORA-16525

DGMGRL> ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
Error: ORA-16525: The Oracle Data Guard broker is not yet available. Failed.

Solution

 mkdir -p /u01/oradata/oradb_sb/DGBROKER
 mkdir -p /u02/oradata/oradb_sb/DGBROKER

 chown -R oracle:oinstall /u01/oradata
 chown -R oracle:oinstall /u02/oradata
 chmod -R 775 /u01/oradata
 chmod -R 775 /u02/oradata

 DGMGRL> remove configuration;

 Reconfigure DG

Error: ORA-16571

DGMGRL> ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
ORA-16571: Oracle Data Guard configuration file creation failure

Solution

 Directories for DGBROKER do not exist on Standby. Create them.

Error: ORA-12514:

DGMGRL> ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

 Failed.

Solution

 TBD

<- DataGuard