Day 1: Oracle Data Guard Overview

Key Components

• Primary Database: The production database that serves application requests
• Standby Database(s): Replicas of the primary database (Physical or Logical)
• Redo Transport Services: Ships redo data from primary to standby
• Redo Apply Services: Applies redo data on physical standby databases
• SQL Apply Services: Applies redo data on logical standby databases
• Data Guard Broker: Centralized management framework

BASH
┌────────────────────────────────┐
│   PRIMARY DATABASE          │
│   (Production Site)         │
│                             │
│   ┌─────────────────────┐     │
│   │  Online Redo Logs │     │
│   └──────────┬──────────┘     │
│             │               │
│   ┌──────────▼──────────┐     │
│   │  Redo Transport   │     │
│   │  Services         │     │
│   └──────────┬──────────┘     │
└──────────────┼─────────────────┘
               │
               │ Redo Data Shipping
               │ (SYNC/ASYNC)
               │
               ▼
┌──────────────────────────────┐
│   STANDBY DATABASE         │
│   (Disaster Recovery Site) │
│                            │
│   ┌──────────────────────┐   │
│   │  Standby Redo Logs │   │
│   └──────────┬───────────┘   │
│             │              │
│   ┌──────────▼───────────┐   │
│   │  Redo Apply/       │   │
│   │  SQL Apply Services│   │
│   └──────────────────────┘   │
└──────────────────────────────┘
                

Why Do We Need Oracle Data Guard?

1. Disaster Recovery Protection

2. Zero Data Loss Guarantee

3. Minimal Downtime

4. Data Protection from Corruption

5. Cost-Effective High Availability

Real-World Business Cases

Data Guard Protection Modes Comparison

Common Day-to-Day Issues & Solutions

SQL-- Check apply lag on standby
SELECT NAME, VALUE, UNIT, TIME_COMPUTED
FROM V$DATAGUARD_STATS
WHERE NAME IN ('apply lag', 'transport lag');

-- Check MRP process status
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY
WHERE PROCESS LIKE 'MRP%';

SQL-- Enable redo transport compression
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=standby_db ASYNC 
COMPRESSION=ENABLE 
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
DB_UNIQUE_NAME=standby_db';

SQL-- On standby database
ALTER SYSTEM SET PARALLEL_THREADS_PER_CPU=4;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
    USING CURRENT LOGFILE PARALLEL 8 DISCONNECT;

SQL-- Check if lag is decreasing
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') TIME,
       ROUND((SYSDATE - MAX(FIRST_TIME)) * 24 * 60) AS "Apply Lag (Minutes)"
FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES';

SQL-- Check log archive destination status on primary
SELECT DEST_ID, STATUS, ERROR, DESTINATION
FROM V$ARCHIVE_DEST_STATUS
WHERE DEST_ID = 2;

-- Check alert log for errors
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > SYSDATE - 1/24
AND MESSAGE_TEXT LIKE '%ARC%'
ORDER BY ORIGINATING_TIMESTAMP DESC;

SQL
-- From primary server, test TNS connection
tnsping standby_db

-- Test SQL*Net connection
sqlplus sys/password@standby_db as sysdba

SQL
-- Check if destination is deferred
SELECT DEST_ID, STATUS, DESTINATION
FROM V$ARCHIVE_DEST
WHERE DEST_ID = 2;

-- Enable if deferred
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

SQL-- On primary, force archive current log
ALTER SYSTEM ARCHIVE LOG CURRENT;

-- If there are gaps, copy and register manually on standby
-- After copying archive logs to standby:
ALTER DATABASE REGISTER PHYSICAL LOGFILE 
'/standby_archive_location/arch_1_123_456789.arc';

SQL
-- Connect to DGMGRL
dgmgrl sys/password

-- Show current configuration
SHOW CONFIGURATION;

-- Increase threshold from 30 to 60 seconds
EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 60;

-- Verify
SHOW CONFIGURATION;

SQL-- Start additional observer on different host
dgmgrl sys/password
START OBSERVER observer2 IN BACKGROUND 
    LOGFILE '/u01/app/oracle/observer2.log';

SQL-- Add health check conditions
EDIT DATABASE primary_db SET PROPERTY 
    FastStartFailoverPmyShutdown = TRUE;
EDIT DATABASE standby_db SET PROPERTY 
    FastStartFailoverTarget = 'primary_db';

🎓 Key Takeaways

Quick Reference Commands

SQL-- Check Data Guard status
SELECT DATABASE_ROLE, OPEN_MODE, PROTECTION_MODE, 
       PROTECTION_LEVEL, SWITCHOVER_STATUS
FROM V$DATABASE;

-- Monitor apply progress
SELECT ARCH.THREAD# "Thread", 
       ARCH.SEQUENCE# "Last Sequence Received", 
       APPL.SEQUENCE# "Last Sequence Applied", 
       (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD#, SEQUENCE# 
      FROM V$ARCHIVED_LOG 
      WHERE (THREAD#, FIRST_TIME) IN 
            (SELECT THREAD#, MAX(FIRST_TIME) 
             FROM V$ARCHIVED_LOG 
             GROUP BY THREAD#)) ARCH,
     (SELECT THREAD#, SEQUENCE# 
      FROM V$LOG_HISTORY 
      WHERE (THREAD#, FIRST_TIME) IN 
            (SELECT THREAD#, MAX(FIRST_TIME) 
             FROM V$LOG_HISTORY 
             GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;

-- Check archive gap
SELECT * FROM V$ARCHIVE_GAP;

Coming Next