DataGuard

Data Guard Administration

<- DataGuard

Status Checks: DGMGRL | SQL
Switchover Database
Failover Database
Aliases (add to .bashrc)
Change Data Guard Broker Setting
Cheat Sheet
Enable-Disable Log Xfer to SB (for non-Broker Env)
Enable-Disable Data Guard Broker
Logs, Data Guard drc<ORACLE_SID>.log
QC Data Test
Read-Only Mode
Start-Stop SB Database on OS Boot
Purge SB ASM Files
Remove Broker Configuration
Reset SB
Trouble Shooting: Oracle Docs | Enable Archive Tracing
References
SQL Relevant to DG

Password Changes

In 18c and later:

Changing the SYS password is automatically sync'd with the SB.
So you no longer have to copy the password file to the SB after changing SYS password.
SYSTEM, DBSNMP and other password changes are automatically sync'd with the SB.
So you do not need to OPEN the SB to change them on the SB.

Confirmed via 3-18917491621

Status Checks

Replace oradb with your database\instance name as required.

DG Broker Log Example

 $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/drc$ORACLE_SID.log

DB Alert Log Example

 $ORACLE_BASE/diag/rdbms/$ORACLE_DB/$ORACLE_SID/trace/alert_$ORACLE_SID.log

DGMGRL>

dgmgrl sys/go@oradb

show configuration;
show database oradb;
show database oradb InconsistentProperties;
show database oradb StatusReport;
show database verbose oradb; -- 12.2 and later (shows path to DG log file)
validate database oradb;
validate database verbose oradb;
validate database oradb_sb SPFILE; -- 18c and later (run from SB only)
validate NETWORK configuration for all; -- 18c and later (run from both)
validate STATIC CONNECT IDENTIFIER FOR all; -- 18c and later (run from both)

SQL>

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#;

 If in sync, MAX(SEQUENCE#) will be the same on both.

 -- 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#;

 If in sync, MAX(SEQUENCE#) will be the same on both. SWITCH LOGFILE increments value.

-- Show Database, Unique and Instance Names

 show parameter db_name
 show parameter db_unique_name
 show parameter instance_name

-- Show DB Status

COL database_role     FORMAT a20
COL db_unique_name    FORMAT a15 
COL instance          FORMAT a15 
COL open_mode         FORMAT a15
COL protection_level  FORMAT a25
COL switchover_status FORMAT a15
SELECT database_role, 
       db_unique_name Instance, 
       open_mode, 
       protection_level, 
       switchover_status 
FROM V$DATABASE;

Quick Mode Check (READ WRITE vs. MOUNTED): SELECT open_mode FROM v$database;

-- Get Current SCN
SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

-- Show Data Guard Status and Config Files

sho parameter dg_broker

-- On SB: Verify Managed Recovery Process (MRPn) Running on the Standby

 SELECT process,thread#,sequence#,status FROM v$managed_standby WHERE process LIKE 'MRP%';

Normally the SB should show WAIT_FOR_LOG or APPLYING_LOG.

-- Check RECOVERY_MODE

 SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

Normally the Primary should show MANAGED REAL TIME APPLY and SB IDLE.

-- Show Log Status (shows if logs applied at SB)

SELECT sequence#, first_time, applied
FROM v$archived_log
ORDER BY sequence#;

Also

archive log list

-- On Primary: Gap Status

 SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

Normally the Primary should show VALID - NO GAP and SB INACTIVE.

-- Determine the most recently archived sequence# for each thread

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

-- Determine the most recently archived redo log at destination (run from primary).

COL destination      FORMAT a35
COL status           FORMAT a15
COL archived_thread# FORMAT 999999
COL archived_seq#    FORMAT 999999

SELECT destination, status, archived_thread#, archived_seq# 
FROM v$archive_dest_status
WHERE status <> 'DEFERRED' AND status <> 'INACTIVE';

-- Find out if archived redo log files have been received at a redo transport destination (run from primary).

SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM 
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) 
LOCAL WHERE 
LOCAL.SEQUENCE# NOT IN 
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND 
THREAD# = LOCAL.THREAD#);

-- Show Data Guard Processes
SELECT process, status FROM V$MANAGED_STANDBY;

-- Standby_Became_Primary_SCN
SELECT standby_became_primary_scn FROM v$database;

Aliases

alias dglog='tail -f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/drc$ORACLE_SID.log'
alias dgloge='gedit $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/drc$ORACLE_SID.log &'

RAC Node Example

alias dglog='tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/drcoradb1.log'

Purge SB ASM Files

su - grid
Run: asmcmd

rm -rf +DATA/oradb_sb/CONTROLFILE/*
rm -rf +DATA/oradb_sb/DATAFILE/*
rm -rf +DATA/oradb_sb/ONLINELOG/*
rm -rf +DATA/oradb_sb/PARAMETERFILE/*
rm -rf +DATA/oradb_sb/PASSWORD/*
rm -rf +DATA/oradb_sb/TEMPFILE/*

rm -rf +FRA/oradb_sb/ARCHIVELOG/*
rm -rf +FRA/oradb_sb/CONTROLFILE/*
rm -rf +FRA/oradb_sb/FLASHBACK/*
rm -rf +FRA/oradb_sb/ONLINELOG/*

Enable-Disable Log Xfer to SB (for non-Broker Env)

 -- Disable
 alter system set log_archive_dest_state_2=defer scope=both;
 show parameter log_archive_dest_state_2
 -- Enable
 alter system set log_archive_dest_state_2=enable scope=both;
 show parameter log_archive_dest_state_2

Must be performed on Primary!

Enable-Disable Data Guard Broker

 dgmgrl sys/go@oradb
 DGMGRL> disable configuration
 ...
 DGMGRL> enable configuration

Must be performed on Primary!

Start-Stop SB Database on OS Boot

1. gedit /etc/oratab

 oradb_sb:/u01/app/oracle/product/18.3.0.0.0/dbhome_1:Y

2. Configure dbora script.

3. Edit the $ORACLE_HOME/bin/dbstart

 Change startup command to startup mount.

4. Testing

 oracle> $ORACLE_HOME/bin/dbstart $ORACLE_HOME
 cat /u01/app/oracle/product/18.3.0.0.0/db_home1/rdbms/log/startup.log

Change Data Guard Broker Setting

EDIT DATABASE <db_name> SET PROPERTY <PropertyName>='<Value>';

 dgmgrl sys/go@oradb
 DGMGRL> EDIT DATABASE oradb SET PROPERTY TransportLagThreshold='0';

See all properties: show database verbose oradb;

Database Switchover

Quick Status Checks

Before performing any operation it is a good idea to make sure everything is working OK. Run: dgmgrl sys/pw@oradb

State

 show database verbose oradb;
    TRANSPORT-ON
 show database verbose oradb_sb;
    APPLY-ON

Database Status

 show database oradb;
    Database Status: SUCCESS
 show database oradb_sb;
    Database Status: SUCCESS

Database Ready for a Role Change?

 show configuration;
 Primary
    Configuration Status: SUCCESS
 Standby
    Configuration Status: SUCCESS

 validate database oradb;
    Ready for Switchover:  Yes
 validate database oradb_sb;
    Ready for Switchover:  Yes

Network (if 18c or later)

   validate NETWORK configuration for all;
   Succeeded.
   The static connect identifier allows for a connection to database "oradb".
   ...
   Succeeded.
   The static connect identifier allows for a connection to database "oradb_sb".

sqlplus / as sysdba

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

 ALTER SYSTEM SWITCH LOGFILE;

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

 SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

Switch

 -- Switch to Standby (from Primary host)
 # lnx01> dgmgrl sys/go
 # DGMGRL> SWITCHOVER TO oradb_sb;

 -- Switch Back (from SB host)
 # lnx02> dgmgrl sys/go@oradb_sb
 # DGMGRL> SWITCHOVER TO oradb;

• In some instances you may need to manually perform a STARTUP MOUNT on the SB after a switch back.
• In some instances I've seen the DGMGRL> stay on the Oracle Clusterware is restarting database "oradb_sb"... point until I manually start\mount the SB from another console sqlplus session.
• If SB is using Oracle-Restart, it may take several minutes to restart the CRS and database on the SB when switching back. If database is not registered you will have to manually start it (STARTUP MOUNT). DGMGR> may stay on SWITCHOVER TO cmd until you start DB from another console via sqlplus.

Database Failover

If the primary database is not available the standby database can be activated as a primary database using the following. Connect to the standby database (oradb_sb) and failover.

Whether this is a real DR scenario or just a test, make sure your original Primary database does not do a normal startup. Start it as mounted so both don't become available as a Primary.

 -- Primary Shutdown or Failed
 If Primary not already down.
   lnx01> sqlplus / as sysdba
   SQLPlus> shutdown immediate;
   SQLPlus> exit

   If RAC or Oracle Restart: srvctl stop database -d <DB_Name>

 -- Failover
 lnx02> dgmgrl sys/go@oradb_sb
 DGMGRL> FAILOVER TO oradb_sb;

 -- Reinstate Primary
 Startup Original Primary
   lnx01> sqlplus / as sysdba
   SQLPlus> startup mount;

   If RAC or Oracle Restart: srvctl start database -d <DB_Name>

 From Standby:
   lnx02> dgmgrl sys/go@oradb_sb
   DGMGRL> REINSTATE DATABASE oradb;

 QC (run on both Primary and SB)
   SELECT database_role, db_unique_name Instance FROM V$DATABASE;

   If Primary's role = PHYSICAL STANDBY
     1. From Primary: DGMGRL> enable database oradb;
     2. From SB:      DGMGRL> SWITCHOVER TO oradb;

When RAC starts it will detect if it is Primary or SB and start accordingly (startup vs. startup mount).

Reinstate\Recreate SB

On Primary
dgmgrl sys/go@oradb
DGMGRL> remove configuration;

On Standby

Shutdown SB Database
sqlplus / as sysdba
SQLPlus> shutdown immediate;
SQLPlus> exit
Start SB Database Using Initial PFile
sqlplus / as sysdba
SQLPlus> STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init_tmp.ora';
Perform RMAN Duplicate database ? operation.
Start Recovery

alter system set log_archive_dest_2='' scope=both; # Required for 12.x
alter database recover managed standby database disconnect from session;

Enable Broker
ALTER SYSTEM SET dg_broker_start=true sid='*' scope=both;

On Primary

dgmgrl sys/go@oradb
CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS oradb CONNECT IDENTIFIER IS oradb;
ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;
show configuration;

On SB

Enable ? Flash Back database.

If show configuration; displays any ORA-16nnn errors they should resolve automatically in a few minutes. If not, on the SB do a shutdown immediate then startup mount;

Common Errors

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Fix: alter system set log_archive_dest_2='' scope=both;

Complete Session

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Workaround
- Save the log archive destination settings from both the Primary and Standby databases then remove the configuration.

  sho parameter log_archive_dest_2

  From oradb:    SERVICE=oradb_sb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_sb
  From oradb_sb: service=oradb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE)          db_unique_name=oradb

- Remove the Dataguard configuration
  DGMGRL> remove configuration;
  Removed configuration

- Set the log_archive_dest_2 settings from both the Primary and Standby 
  databases to be nothing.

  alter system set log_archive_dest_2='' scope=both sid='*';

- Disable then Enable the broker parameter on both the Primary and Standby 
  databases.

  -- Primary
  alter system set dg_broker_start=false scope=both sid='*';
  alter system set dg_broker_start=true scope=both sid='*';

  -- Standby
  alter system set dg_broker_start=false scope=both sid='*';
  alter system set dg_broker_start=true scope=both sid='*';


- On the Primary database create the broker configuration for the Primary and Standby database and this time it
  should work fine with no issues since the log archive destination 2 setting is not set.
  This is the workaround/solution.

Warning: ORA-16714: the value of property xxx is inconsistent with the member setting

On Primary get the current correct value.
SQLPlus> show parameter <param_name>
Set the value(s) on the Standby

Common Changes Example

 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 parameter log_archive_max_processes;
 show parameter archive_lag_target;
 show parameter log_archive_min_succeed_dest;
 show parameter data_guard_sync_latency;

Remove Broker Configuration

DGMGRL> remove configuration;

To Manually Delete Files

 cd $ORACLE_HOME/dbs
 ls dr* 
 -rw-r----- 1 oracle oinstall     8192 Oct 10 12:18 dr1oradb.dat
 -rw-r----- 1 oracle oinstall    16384 Oct 10 12:18 dr2oradb.dat
 rm dr1oradb.dat
 rm dr2oradb.dat

If Primary is RAC:

 rm -rf +DATA/oradb/DGBROKER/*
 rm -rf +FRA/oradb/DGBROKER/*

RESET

  --PRIMARY
  dgmgrl sys/go@oradb
  remove configuration;

  OS> rm -rf $ORACLE_HOME/dbs/dr*

  alter system NO FORCE LOGGING;
  alter system set log_archive_dest_state_2=defer scope=both;
  show parameter log_archive_dest_state_2

  --SB
  sqlplus> shutdown abort

  rm -rf /u01/app/oracle/admin/oradb_sb

  rm -rf /u01/oradata/ORADB
  rm -rf /u01/oradata/ORADB_SB
  rm -rf /u02/oradata/ORADB
  rm -rf /u02/oradata/ORADB_SB

  rm -rf $ORACLE_HOME/dbs/*.dat
  rm -rf $ORACLE_HOME/dbs/*.f
  rm -rf $ORACLE_HOME/dbs/*.ora
  rm -rf $ORACLE_HOME/dbs/lk*
  rm -rf $ORACLE_HOME/dbs/dr*
  rm -rf $ORACLE_HOME/dbs/orapw*

QC Data Test

Can be used to QC data changes upon a SWITCH or FAILOVER.

 UPDATE HR.cust SET name='Test111' WHERE cust_id=100; 
 COMMIT;
 SELECT name FROM HR.cust WHERE cust_id=100;

Uses HR.cust (schema.table). Change as required for your environment.

Usage:

Verify value before switch on Primary.
Perform SWITCH or FAILOVER.
From SB change (UPDATE) value.
For instance: name='Test222'
Reinstate Primary.
Verify value on Primary.
Value on Primary should be the same as what it was changed to on the SB.

Alert and DG Logs

Examples where Primary is oradb1 and SB oradb_sb.

Alert

 Primary: tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/alert_oradb1.log
 SB:      tail -f /u01/app/oracle/diag/rdbms/oradb_sb/oradb_sb/trace/alert_oradb_sb.log

 Primary: tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/drcoradb1.log
 SB:      tail -f /u01/app/oracle/diag/rdbms/oradb_sb/oradb_sb/trace/drcoradb_sb.log

Aliases

 alias dglog='tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/drcoradb1.log'
 alias dgloge='gedit /u01/app/oracle/diag/rdbms/oradb_sb/oradb_sb/trace/drcoradb_sb.log &'

Data Guard Administration

TOC

Password Changes

Status Checks

DGMGRL>

SQL>

Ensure Logs Being Transferred

Aliases

Purge SB ASM Files

Enable-Disable Log Xfer to SB (for non-Broker Env)

Enable-Disable Data Guard Broker

Start-Stop SB Database on OS Boot

Change Data Guard Broker Setting

Database Switchover

Quick Status Checks

Switch

Database Failover

Reinstate\Recreate SB

Common Errors

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Warning: ORA-16714: the value of property xxx is inconsistent with the member setting

Remove Broker Configuration

RESET

QC Data Test

Alert and DG Logs