ORACLE DATA GUARD 19c Daily Monitoring Guide

ORACLE DATA GUARD 19c Daily Monitoring Guide

 

 

1.  Check standby database role and status (run on STANDBY)

select INST_ID,INSTANCE_NAME, name DB_NAME, db_unique_name,HOST_NAME,DATABASE_ROLE,OPEN_MODE,status, to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

 

2.  Check if redo apply (MRP) is running on standby

 

3.  Check apply lag (run on STANDBY)

4.  Check lag using v$managed_standby (MRP process)

 

 

 

5.  Archive log GAP

 

6.  Check all Data Guard processes (run on STANDBY)

 

7.  Verify Data Guard Configuration Status

 

Dgmgrl / as sysdba

Show configuration;

8.  DGMGRL>Check Database Status (Primary & Standby)

dgmgrl -silent / <<EOF >> $LOGFILE

SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> SHOW DATABASE VERBOSE <STANDBY_DB>;

 

9.  DGMGRL>Validate Database Health

dgmgrl -silent / <<EOF >> $LOGFILE

SHOW CONFIGURATION;

DGMGRL> VALIDATE DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> VALIDATE DATABASE VERBOSE <STANDBY_DB>;

 

 

10.              Daily Data Guard Monitoring Checklist

 

 

SQL Script (dg_check.sql)

SET LINES 200

SET PAGES 200

PROMPT === DATABASE STATUS ===

SELECT NAME, DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

 

PROMPT === DATAGUARD STATS ===

SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

 

PROMPT === ARCHIVE DEST ===

SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;

 

PROMPT === MRP STATUS ===

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

 

PROMPT === ARCHIVE GAP ===

SELECT * FROM V$ARCHIVE_GAP;

 

PROMPT === FRA USAGE ===

SELECT SPACE_LIMIT, SPACE_USED FROM V$RECOVERY_FILE_DEST;

 

Data Guard Daily Monitoring Automation Script

 

Shell Script (dg_health_check.sh)

 

#!/bin/bash

 

# Set Oracle Environment

export ORACLE_SID=YOUR_DB_SID

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

 

# Date

DATE=$(date +"%Y-%m-%d_%H-%M")

LOGFILE=/tmp/dg_health_$DATE.log

 

echo "=========================================" >> $LOGFILE

echo " Data Guard Daily Health Check - $DATE " >> $LOGFILE

echo "=========================================" >> $LOGFILE

 

# Run SQL Checks

sqlplus -s / as sysdba<<EOF >> $LOGFILE

 

SET LINESIZE 200

SET PAGESIZE 200

 

PROMPT === DATABASE ROLE & STATUS ===

SELECT NAME, DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS FROM V\\$DATABASE;

 

PROMPT === DATAGUARD STATS ===

SELECT NAME, VALUE, UNIT FROM V\\$DATAGUARD_STATS;

 

PROMPT === ARCHIVE DEST STATUS ===

SELECT DEST_ID, STATUS, ERROR FROM V\\$ARCHIVE_DEST WHERE STATUS <> 'INACTIVE';

 

PROMPT === ARCHIVE GAP ===

SELECT * FROM V\\$ARCHIVE_GAP;

 

PROMPT === MANAGED STANDBY ===

SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V\\$MANAGED_STANDBY;

 

PROMPT === STANDBY REDO LOGS ===

SELECT GROUP#, THREAD#, SEQUENCE#, STATUS FROM V\\$STANDBY_LOG;

 

PROMPT === FLASHBACK STATUS ===

SELECT FLASHBACK_ON FROM V\\$DATABASE;

 

PROMPT === FRA USAGE ===

SELECT NAME, SPACE_LIMIT, SPACE_USED FROM V\\$RECOVERY_FILE_DEST;

 

EXIT;

EOF

 

# Run DGMGRL Checks

echo "=== DGMGRL CONFIGURATION ===" >> $LOGFILE

 

dgmgrl -silent / <<EOF >> $LOGFILE

SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> SHOW DATABASE VERBOSE <STANDBY_DB>;

DGMGRL> VALIDATE DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> VALIDATE DATABASE VERBOSE <STANDBY_DB>;

EXIT;

EOF

 

# Basic Alert Check

echo "=== ALERT LOG ERRORS (last 50 lines) ===" >> $LOGFILE

tail -50 $ORACLE_BASE/diag/rdbms/*/*/trace/alert_*.log >> $LOGFILE

 

# Output location

echo "Health check completed. Log: $LOGFILE"

ACE Program

Announcement! Thrilled and proud to share that I’ve been selected as an Oracle ACE Apprentice!

 

Top Oracle GoldenGate Commands Every DBA Must Master

 GoldenGate is not just replication.

It’s real-time data movement with precision.
But most DBAs only scratch the surface.
If you’re serious about production-grade replication, these are the commands you must know inside GGSCI


1. INFO ALL
Your first diagnostic command.
GGSCI> INFO ALL
Shows status of Extract, Replicat, Manager.
Also gives checkpoint lag.
If lag increases, you investigate immediately.
This is your heartbeat monitor.


2. VIEW REPORT
When something fails, this is where truth lives.
GGSCI> VIEW REPORT EXT1
Gives detailed execution logs.
Error codes, trail issues, checkpoint problems.
Never guess. Always read report files.


3. STATS
Understand throughput and performance.
GGSCI> STATS EXTRACT EXT1
Shows number of operations processed.
Insert / Update / Delete counts.
Helps validate replication correctness during testing.


4. LAG
Real-time latency check.
GGSCI> LAG EXTRACT EXT1
or
GGSCI> LAG REPLICAT REP1
Critical in real-time systems (banking, telecom).
Even seconds matter.


5. START / STOP
Basic but dangerous if misused.
GGSCI> STOP EXTRACT EXT1
GGSCI> START EXTRACT EXT1
Always coordinate with downstream systems.
Stopping Extract blindly can cause backlog explosion.


6. ALTER EXTRACT / REPLICAT
Used for repositioning.
GGSCI> ALTER EXTRACT EXT1, BEGIN NOW
or
GGSCI> ALTER REPLICAT REP1, EXTSEQNO 15, EXTRBA 12345
Used during recovery scenarios.
Requires precision. One mistake = data inconsistency.


7. INFO DETAIL
Deep dive into process state.
GGSCI> INFO EXTRACT EXT1, DETAIL
Shows checkpoint, trail file, SCN position.
Essential during troubleshooting.


8. SEND Command
Real-time interaction with running process.
GGSCI> SEND EXTRACT EXT1 STATUS
No need to stop process.
Useful for live debugging.


9. LOGDUMP (Advanced)
Not GGSCI, but critical tool.
logdump> open ./dirdat/aa
Lets you read trail files.
This is where elite DBAs operate.


10. DELETE / ADD / REGISTER
Lifecycle management.
GGSCI> ADD EXTRACT EXT1, INTEGRATED TRANLOG
GGSCI> REGISTER EXTRACT EXT1 DATABASE
Especially important in integrated capture mode.


Quick Takeaway / Summary
GoldenGate is simple… until it breaks.
These commands are not optional—they’re survival tools.
Master them, and you control replication.
Ignore them, and replication controls you.

Oracle database is slow

when someone says:

🚨 “Oracle database is slow”

---

⚡ Oracle Performance Troubleshooting – Step-by-Step Playbook

---

🧠 Step 0: Stay Calm & Don’t Guess

Before doing anything:

👉 Don’t jump into AWR immediately
👉 Don’t restart database
👉 Don’t assume it’s “CPU issue”

First rule:

Find the bottleneck, not the symptom

---

🔍 Step 1: Identify Where Time is Spent (Your Query 👇)

SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC
FETCH FIRST 5 ROWS ONLY;

👉 What to check:

If you see	Meaning
db file sequential read	Index I/O
db file scattered read	Full table scan
log file sync	Commit issue
CPU time high	CPU pressure
enq: TX row lock	Blocking

✅ Now you know WHERE to focus

---

⚡ Step 2: Check Active Sessions (Real-Time)

SELECT sid, serial#, username, sql_id, event, wait_class, seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE'
ORDER BY seconds_in_wait DESC;

👉 Identify:

• Long running sessions

• Blocking sessions

• Top wait events

---

🚫 Step 3: Check Blocking / Locks

SELECT blocking_session, sid, serial#, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;

If blocking exists:

👉 That’s your issue (most critical)

---

🧮 Step 4: Identify Top SQL (Heavy Queries)

SELECT sql_id, executions, elapsed_time/1000000 total_sec
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

👉 Look for:

• High elapsed time

• High executions

• Same SQL repeated

---

📊 Step 5: Check CPU Usage

SELECT stat_name, value
FROM v$sysstat
WHERE stat_name LIKE '%CPU%';

👉 Or OS level:

top

If CPU is high:

• Bad SQL

• Missing indexes

• High concurrency

---

💾 Step 6: Check I/O Bottleneck

SELECT file#, phyrds, phywrts
FROM v$filestat;

👉 OR check wait events:

• db file sequential read → index reads

• db file scattered read → full scans

---

🔄 Step 7: Check Redo / Commit Issues

SELECT event, time_waited
FROM v$system_event
WHERE event = 'log file sync';

👉 If high:

• Too many commits

• Slow disk

---

🌐 Step 8: Check Network Issues

Look for:

• SQL*Net message from client

• SQL*Net more data

👉 Usually application-side delay

---

📈 Step 9: Check AWR (If Needed)

Only AFTER initial diagnosis:

@?/rdbms/admin/awrrpt.sql

Focus on:

• Top SQL

• Wait events

• Load profile

---

🧪 Step 10: Drill Down into Problem SQL

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));

👉 Check:

• Full table scans

• Bad joins

• Wrong cardinality

---

🚀 Step 11: Take Action (Based on Root Cause)

Issue	Action
Blocking	Kill session / fix transaction
Bad SQL	Tune query / add index
CPU high	Optimize SQL
I/O high	Add indexes / check storage
Redo issue	Reduce commits
Stats stale	Gather stats

---

🔥 Real DBA Flow (Golden Rule)

When DB is slow:

1. Check wait events ✅

2. Check active sessions ✅

3. Check blocking ✅

4. Identify top SQL ✅

5. Drill into execution plan ✅

---

🧠 Interview-Level Answer (Important)

If interviewer asks:

👉 “Database is slow, what will you do?”

Say:

“First I check system wait events to identify where time is spent, then I analyze active sessions and blocking, followed by identifying top SQL and reviewing execution plans before taking corrective action.”

---

⚡ Pro Tips (From Real Production Experience)

• 80% issues = bad SQL or missing index

• 10% = locking

• 10% = infrastructure (CPU/I/O)

---

🎯 Final Thought

“Performance tuning is not about fixing everything…
It’s about fixing the right thing first.”

---

Real-Time Troubleshooting Scenario

---

🚨 Real-Time Troubleshooting Scenario (Hands-On Practice)

🎯 Situation

Time: 11:15 AM
Environment: Production
Users complaint:

“Application is very slow. Pages are hanging.”

You have no prior info.

---

⏱️ Your Goal (as DBA)

• Identify root cause in 5–10 minutes

• Avoid guesswork

• Take correct action

---

🔍 Step 1: Where is time spent?

You run:

SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC
FETCH FIRST 5 ROWS ONLY;

📊 Output:

EVENT                          TIME_WAITED
----------------------------   -----------
enq: TX - row lock contention   9854321
db file sequential read         234567
CPU time                        123456

---

❓ Question 1 (Your Move)

👉 What does this indicate?

✅ Expected Thinking:

• Top wait = row lock contention

• This is blocking issue, not CPU or I/O

---

🔎 Step 2: Find Blocking Session

You run:

SELECT blocking_session, sid, serial#, event
FROM v$session
WHERE blocking_session IS NOT NULL;

📊 Output:

BLOCKING_SESSION   SID   EVENT
---------------   ----  -----------------------------
145               201   enq: TX - row lock contention
145               305   enq: TX - row lock contention
145               410   enq: TX - row lock contention

---

❓ Question 2

👉 What do you conclude?

✅ Answer:

• Session 145 is blocking multiple sessions

• This is a critical production issue

---

🔍 Step 3: Identify Blocking SQL

SELECT sid, serial#, username, sql_id, status
FROM v$session
WHERE sid = 145;

Output:

SID   SERIAL#   USERNAME   SQL_ID     STATUS
145   6789      APPUSER    abc123xyz  INACTIVE

---

❓ Question 3

👉 Why is this dangerous?

✅ Answer:

• Session is INACTIVE but holding locks

• Likely:

  • Uncommitted transaction

  • Application issue

---

🔥 Step 4: Business Decision Time

You have 2 options:

Option A:

👉 Wait for user to commit/rollback

Option B:

👉 Kill the session

---

❓ Question 4

👉 What will you do?

✅ Best Answer:

• If production is impacted → Kill session

---

⚡ Step 5: Kill Blocking Session

ALTER SYSTEM KILL SESSION '145,6789' IMMEDIATE;

---

📊 Step 6: Validate Fix

Check again:

SELECT blocking_session FROM v$session
WHERE blocking_session IS NOT NULL;

Output:

No rows selected

---

🎉 Result

• Blocking removed

• Application restored

• Issue resolved in minutes

---

🧠 Now Let’s Level Up (Scenario 2)

---

🚨 New Situation

Users say:

“Reports are very slow”

---

You run:

SELECT event, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

Output:

db file scattered read   9999999
CPU time                 8888888

---

❓ Question 5

👉 What does this indicate?

✅ Answer:

• Heavy Full Table Scans

• Likely:

  • Missing indexes

  • Bad SQL

---

🔍 Next Step

SELECT sql_id, elapsed_time, executions
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

---

❓ Question 6

👉 What will you do next?

✅ Answer:

• Pick top SQL_ID

• Check execution plan:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));

---

🎯 Final Diagnosis

• Full table scan detected

• Missing index

---

⚡ Fix

CREATE INDEX idx_col ON table_name(column_name);

---

🏁 Final Learning

🔥 Golden Troubleshooting Flow:

1. Check wait events

2. Identify type of issue

3. Find sessions / SQL

4. Take targeted action

---

🎯 Mock Interview Question (Based on Scenario)

👉 “How will you handle blocking in production?”

Perfect Answer:

“I first check wait events to confirm locking, identify blocking session using V$SESSION, verify the SQL and session state, and if it’s impacting users, I kill the blocking session and inform application team.”

---

---

🎯 Oracle SQL Tuning – Interview Questions & Answers

---

1. What is SQL Tuning in Oracle?

Answer:
SQL Tuning is the process of improving the performance of SQL queries by optimizing execution plans, reducing resource consumption (CPU, I/O), and improving response time.

---

2. How do you identify a slow SQL query?

Answer:
We can identify slow SQL using:

• V$SESSION (currently running SQL)

• V$SQL (high resource SQL)

• AWR reports

• ASH reports

Example:

SELECT sql_id, elapsed_time, executions
FROM v$sql
ORDER BY elapsed_time DESC;

---

3. What is SQL_ID and why is it important?

Answer:
SQL_ID is a unique identifier for a SQL statement in Oracle.
It helps in:

• Tracking SQL performance

• Fetching execution plans

• Monitoring historical execution

---

4. How do you check the execution plan of a query?

Answer:
Using:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));

This shows:

• Execution steps

• Actual vs estimated rows

• Cost and time

---

5. What is the difference between EXPLAIN PLAN and DBMS_XPLAN.DISPLAY_CURSOR?

Answer:

Feature	EXPLAIN PLAN	DISPLAY_CURSOR
Type	Estimated	Actual
Source	Optimizer	Cursor cache
Accuracy	Less	High

---

6. How do you check historical performance of a SQL?

Answer:
Using AWR views:

• DBA_HIST_SQLSTAT

• DBA_HIST_SQLTEXT

This helps analyze:

• Plan changes

• Execution trends

• Performance degradation

---

7. What is PLAN_HASH_VALUE?

Answer:
PLAN_HASH_VALUE uniquely represents an execution plan.
If it changes, it means the execution plan has changed.

---

8. What is SQL Profile?

Answer:
A SQL Profile is a set of optimizer hints that helps Oracle generate a better execution plan without modifying the SQL.

---

9. How do you create a SQL Profile using COE script?

Answer:

1. Identify best plan:

START coe_xfr_sql_profile.sql <SQL_ID>

2. Generate profile:

START coe_xfr_sql_profile.sql <SQL_ID> <PLAN_HASH_VALUE>

3. Apply:

@coe_xfr_sql_profile_<SQL_ID>_<PLAN_HASH>.sql

---

10. How do you verify SQL Profile is being used?

Answer:

SELECT sql_id, plan_hash_value
FROM v$sql
WHERE sql_id = '<SQL_ID>';

Also:

SELECT * FROM dba_sql_profiles;

---

11. How do you drop a SQL Profile?

Answer:

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('profile_name');

---

12. What is SQL Tuning Advisor?

Answer:
SQL Tuning Advisor is an Oracle tool that analyzes SQL and provides recommendations such as:

• Index creation

• SQL Profile

• Statistics gathering

• Alternative execution plans

---

13. How do you run SQL Tuning Advisor?

Answer:

BEGIN
    DBMS_SQLTUNE.create_tuning_task(
        task_name   => 'TASK1',
        sql_id      => '<SQL_ID>',
        time_limit  => 60
    );

    DBMS_SQLTUNE.execute_tuning_task('TASK1');
END;
/

---

14. What kind of recommendations does SQL Tuning Advisor give?

Answer:

• Create index

• Gather statistics

• Accept SQL Profile

• Rewrite query

• Suggest better execution plan

---

15. What is the role of statistics in SQL tuning?

Answer:
Optimizer relies on statistics to estimate:

• Cardinality

• Cost

• Execution plan

Stale statistics can lead to bad plans.

---

16. What is cardinality misestimation?

Answer:
When optimizer incorrectly estimates number of rows, leading to:

• Wrong join method

• Poor execution plan

---

17. When should you use SQL Profile vs Index?

Answer:

Scenario	Use
Temporary fix	SQL Profile
Long-term optimization	Index

---

18. What are common causes of slow SQL?

Answer:

• Missing indexes

• Full table scans

• Stale statistics

• Bad execution plans

• High I/O operations

---

19. What is ALLSTATS LAST in DBMS_XPLAN?

Answer:
It shows actual execution statistics of the last run, including:

• Actual rows processed

• Actual time taken

---

20. Real Interview Scenario Question

Question:
A query suddenly became slow. What will you do?

Answer:

1. Capture SQL_ID

2. Check current execution plan

3. Compare with historical plans

4. Identify plan change

5. Use COE or SQL Profile

6. Run SQL Tuning Advisor

7. Validate performance

---

✅ Pro Tip (For Interviews)

Always say:

“First I check whether the issue is due to plan change or data growth, then I decide whether to fix using SQL Profile, Index, or statistics.”

---

🚀 Bonus: Quick One-Liners

• SQL_ID → Unique identifier

• PLAN_HASH_VALUE → Execution plan identifier

• SQL Profile → Optimizer hint set

• AWR → Historical performance data

• ASH → Real-time session activity

---

Interview Question on Active Data Guard Configuration

 

Interview Questions

What is Active Data Guard?

Difference between Data Guard and Active Data Guard?

What is MRP process?

What is FAL_SERVER and FAL_CLIENT?

What is the use of Standby Redo Logs?

What is Switchover vs Failover?

Why FORCE LOGGING is required?

How do you check Data Guard lag?

What is DB_UNIQUE_NAME?

What is NOFILENAMECHECK in RMAN duplicate?