Thursday, April 2, 2026

ACE Program

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

Friday, March 27, 2026

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.

Sunday, March 22, 2026

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:

Check wait events ✅
Check active sessions ✅
Check blocking ✅
Identify top SQL ✅
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:

Check wait events
Identify type of issue
Find sessions / SQL
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.”

Saturday, March 21, 2026

Oracle SQL Tuning – Interview Questions & Answers

🎯 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:

Identify best plan:

START coe_xfr_sql_profile.sql <SQL_ID>

Generate profile:

START coe_xfr_sql_profile.sql <SQL_ID> <PLAN_HASH_VALUE>

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:

Capture SQL_ID
Check current execution plan
Compare with historical plans
Identify plan change
Use COE or SQL Profile
Run SQL Tuning Advisor
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?

Friday, March 20, 2026

Configuring Active Data Guard in Oracle 26AI Multitenant Database

1. Introduction

Active Data Guard enables a standby database to be open in read-only mode while continuously applying redo logs from the primary database. This provides:

High availability
Disaster recovery
Real-time reporting

This document provides a complete step-by-step guide to configure Active Data Guard in an Oracle 26AI Multitenant environment.

2. Prerequisites

Source and Target servers with Oracle Linux 8/9
Primary server with Oracle 26AI Database installed
Standby server with Oracle 26AI Software installed
Network connectivity between servers (tnsping must work)

3. Environment Details

Database Name: AIDB
PDB Name: AIPDB
Primary Host: 192.168.226.129
Standby Host: 192.168.226.130

4. Network Configuration

4.1 Primary Database – TNS Configuration

File location:

$ORACLE_HOME/network/admin/tnsnames.ora

LISTENER_AIDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIMARY)
    )
  )

AIPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = AIPDB)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = STANDBY)
    )
  )

4.2 Primary Listener Configuration

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PRIMARY)
      (ORACLE_HOME = /u01/app/oracle/product/home)
      (SID_NAME = AIDB)
    )
  )

4.3 Standby Database – TNS Configuration

LISTENER_AIDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = STANDBY)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PRIMARY)
    )
  )

4.4 Standby Listener Configuration

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STANDBY)
      (ORACLE_HOME = /u01/app/oracle/product/home)
      (SID_NAME = AIDB)
    )
  )

5. Primary Database Configuration

5.1 Enable Archivelog Mode

STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

5.2 Enable Force Logging

ALTER DATABASE FORCE LOGGING;

5.3 Set Data Guard Parameters

ALTER SYSTEM SET db_unique_name='PRIMARY' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_1=
'LOCATION=/u01/app/oracle/product/home/dbs/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIMARY' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=STANDBY
 ASYNC
 NOAFFIRM
 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=STANDBY' SCOPE=BOTH;

ALTER SYSTEM SET FAL_CLIENT=PRIMARY SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER=STANDBY SCOPE=BOTH;

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

ALTER SYSTEM SET db_file_name_convert=
'/u01/.../AIDB','/u01/.../AIDB' SCOPE=BOTH;

ALTER SYSTEM SET log_file_name_convert=
'/u01/.../AIDB','/u01/.../AIDB' SCOPE=BOTH;

5.4 Create Standby Redo Logs

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/path/srl04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/path/srl05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/path/srl06.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/path/srl07.log') SIZE 200M;

5.5 Copy Required Files

scp initAIDB.ora orapwAIDB oracle@192.168.226.130:$ORACLE_HOME/dbs

6. Standby Database Configuration

6.1 Start Standby in NOMOUNT

STARTUP NOMOUNT PFILE='initAIDB.ora';

6.2 Duplicate Database Using RMAN

rman target sys/password@PRIMARY auxiliary sys/password@STANDBY

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK;

6.3 Verify Database State

SELECT name, open_mode FROM v$database;

7. Enable Active Data Guard

7.1 Open Standby in Read-Only Mode

ALTER DATABASE OPEN READ ONLY;

7.2 Start Managed Recovery Process (MRP)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

8. Validation Checks

8.1 Check MRP Status

SELECT process, status FROM v$managed_standby;

8.2 Check Lag

SELECT name, value
FROM v$dataguard_stats
WHERE name IN ('transport lag','apply lag');

8.3 Archive Gap Check

SELECT * FROM v$archive_gap;

8.4 Synchronization Check

SELECT
  arch.sequence# AS received,
  appl.sequence# AS applied,
  (arch.sequence# - appl.sequence#) AS gap
FROM
  (SELECT MAX(sequence#) sequence# FROM v$archived_log) arch,
  (SELECT MAX(sequence#) sequence# FROM v$log_history) appl;

9. Switchover Process

9.1 On Primary

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

9.2 On Standby

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

9.3 Restart MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

10. Useful Monitoring Queries

MRP Status

SELECT process, status FROM v$managed_standby;

Lag Check

SELECT name, value FROM v$dataguard_stats;

Archive Gap

SELECT * FROM v$archive_gap;

11. Conclusion

Active Data Guard setup involves:

Network configuration
Primary database preparation
Standby database creation using RMAN
Enabling redo apply (MRP)
Validating synchronization and lag

A properly configured Active Data Guard environment ensures high availability, data protection, and real-time reporting capabilities.