Showing posts with label Oracle RAC. Show all posts

Friday, August 14, 2026

The Migration Project That Changed My Career

After almost 5 years of my Oracle journey, I joined HCL and got the opportunity to work on a Database Migration Project.

At that time, I didn't realize how important this project would become in my career.

Looking back now, I can confidently say — this project was one of the biggest turning points in my Oracle journey.

It was not just about migrating databases from one place to another. It was where I got the chance to learn the complete lifecycle of an Oracle database.

DB Creation? ✅
Schema creation? ✅
Objects? ✅
Data Guard setup and troubleshooting? ✅
GoldenGate setup and maintenance? ✅
OEM setup and monitoring? ✅
RMAN backup and restore? ✅
ZDLRA? ✅
PSU patching? ✅
Database upgrades? ✅

Honestly, you name an Oracle technology or activity, and somehow it was part of that migration project! 😄

And that's what made the project so special for me.

I learned not only how to perform these activities, but also why they matter in a real production environment. I learned how to troubleshoot when things don't go as planned, how to handle critical databases, and most importantly, how to take ownership.

There were long days, challenging migrations, unexpected issues, troubleshooting calls and plenty of learning along the way.

But every challenge added something to my experience.

That project gave me a strong foundation, and many of the things I learned there are still helping me today.

Five years into my Oracle journey, I thought I was just joining another project.

I didn't know I was joining a project that would shape the next chapter of my career.

Even today, I am still counting the lessons from that project. ❤️

Sometimes, a project is more than just a project.
Sometimes, it becomes a part of your career story.

#OracleDBA #OracleDatabase #DatabaseMigration #HCL #DataGuard #GoldenGate #OEM #RMAN #ZDLRA #OracleCommunity #DBALife #CareerJourney #LearningNeverStops

Friday, July 10, 2026

Oracle Zero Data Loss Recovery Appliance (ZDLRA)

More Than Just a Backup Appliance

1. Introduction

In today's enterprise environments, protecting critical Oracle databases is about much more than taking regular backups. Organizations need to minimize downtime, reduce data loss, meet strict Recovery Time Objectives (RTO) and Recovery Point Objectives (RPO), and ensure that backups are always recoverable.

This is where Oracle Zero Data Loss Recovery Appliance (ZDLRA) plays a vital role.

Many Oracle DBAs ask:

"If I already have Oracle Active Data Guard, do I still need ZDLRA?"

The answer is Yes.

Although both technologies enhance database protection, they serve different purposes:

Oracle Active Data Guard (ADG) provides High Availability (HA) and Disaster Recovery (DR).
Oracle Zero Data Loss Recovery Appliance (ZDLRA) provides enterprise-grade backup, recovery, and recovery assurance.

They are complementary technologies—not competitors.

2. What is Oracle ZDLRA?

Oracle Zero Data Loss Recovery Appliance (ZDLRA) is an Oracle Engineered System purpose-built for protecting Oracle databases using RMAN.

Unlike traditional backup servers, ZDLRA is designed to:

Protect Oracle databases with minimal production impact.
Eliminate repeated full backups.
Continuously receive redo data.
Validate backups automatically.
Provide very fast recovery.

It acts as a centralized backup repository for hundreds or even thousands of Oracle databases.

3. Oracle ZDLRA Architecture


                     Oracle Enterprise Manager
                               │
                               ▼
                  Oracle Zero Data Loss
                    Recovery Appliance
                               │
        ------------------------------------------------
        │              │               │
        ▼              ▼               ▼
   Production DB1  Production DB2  Production DB3
        │              │               │
        │              │               │
        └──── Incremental Backups ─────┘
                       │
                Continuous Redo
                       │
              Recovery Appliance
                       │
         Virtual Full Backups
                       │
             Long-Term Retention

4. Major Components

A ZDLRA environment typically consists of:

Oracle Recovery Appliance
RMAN
Protected Databases
Recovery Catalog
Backup Policies
Redo Transport
Oracle Enterprise Manager (optional)

5. How ZDLRA Works

Unlike traditional backup solutions, ZDLRA follows an Incremental Forever strategy.

Step 1

Take one Level 0 backup.


Production Database

↓

Level 0 Backup

↓

Recovery Appliance

Step 2

Daily backups send only changed blocks.


Changed Blocks

↓

Recovery Appliance

Step 3

Archived redo logs (or real-time redo, depending on configuration) are continuously transferred.


Redo Logs

↓

Recovery Appliance

Step 4

ZDLRA automatically creates Virtual Full Backups.

No additional full backup is generated from the production database.

6. Incremental Forever Strategy

Traditional Backup


Sunday

Full Backup

↓

Monday

Incremental

↓

Tuesday

Incremental

↓

Wednesday

Incremental

Every week another full backup is required.

ZDLRA


One Level 0 Backup

↓

Daily Incrementals

↓

Continuous Redo

↓

Virtual Full Backup

Only one physical full backup is required initially.

7. Continuous Redo Protection

One of the most powerful ZDLRA features is Continuous Redo Protection.

Instead of waiting for the next backup window:


Database

↓

Redo Generated

↓

Recovery Appliance

Redo is protected continuously, reducing potential data loss.

8. Virtual Full Backup

A Virtual Full Backup is logically equivalent to a full backup, but it is synthesized by the appliance from:

Initial Level 0 backup
Incremental backups
Redo information

Benefits:

No repeated full backups on production.
Faster recovery.
Lower storage usage.
Reduced backup windows.

9. Recovery Assurance

Traditional backups may complete successfully but still fail during restore due to corruption or missing files.

ZDLRA continuously validates:

Backup pieces
Archived logs
Database structure
Restore chain integrity

This capability is known as Recovery Assurance.

10. Real-Time Production Example

Environment

Database Size: 20 TB
Daily Redo Generation: 3 TB
Backup Window: 10 Hours
Production: Banking Database

Traditional Backup

Every weekend:


20 TB Full Backup

↓

10 Hours

↓

Heavy CPU Usage

↓

Heavy Storage I/O

↓

Heavy Network Traffic

Problems:

Long backup window
High production impact
Backup overlap with business hours
Large storage requirements

ZDLRA


Initial Level 0 Backup

↓

Daily Incrementals

↓

Continuous Redo

↓

Virtual Full Backup

Benefits:

Minimal production impact
Short backup windows
Faster recovery
Centralized backup management

11. Oracle Active Data Guard vs ZDLRA

Feature	Oracle Active Data Guard	Oracle ZDLRA
Primary Purpose	High Availability & Disaster Recovery	Backup & Recovery
Data Protection	Redo Apply	Incremental Backups + Redo Protection
Backup Repository	No	Yes
Point-in-Time Recovery	Limited by standby role	Excellent
Long-Term Retention	No	Yes
Recovery Assurance	No	Yes
Backup Validation	No	Automatic
Enterprise Backup	No	Yes
Read-Only Reporting	Yes (Active Data Guard)	No
Automatic Failover	Yes (with Data Guard Broker/FSFO)	No

12. Why Data Guard Alone Is Not Enough

Consider a situation where a user accidentally executes:


DROP TABLE CUSTOMER;

The change is replicated to the standby through redo.

Result:

Primary database: table deleted.
Standby database: table also deleted.

Active Data Guard cannot protect against this type of logical error by itself.

If a valid backup exists in ZDLRA:

Recover to a point in time before the error.
Restore the lost object or database as required.

13. ZDLRA + Active Data Guard Architecture


                    Users
                      │
                      ▼
              Production Database
                      │
        ┌─────────────┴─────────────┐
        ▼                           ▼
 Active Data Guard             Recovery Appliance
 (High Availability)      (Backup & Recovery)
        │                           │
        ▼                           ▼
 Disaster Recovery        Virtual Full Backups
                           Recovery Assurance
                           Long-Term Retention

14. Benefits of ZDLRA

Near-zero data loss protection.
Incremental Forever backups.
Continuous redo protection.
Virtual Full Backups.
Faster restores.
Centralized backup management.
Automatic backup validation.
Reduced production overhead.
Efficient storage utilization.
Integration with RMAN.

15. RMAN Integration

Protected databases use standard RMAN commands.

Example:


rman target /

BACKUP DATABASE;

RMAN communicates directly with the Recovery Appliance according to the configured protection policies.

16. Typical Enterprise Deployment

A large enterprise might protect:

500 Oracle databases
5 PB of protected data
Multiple data centers
Exadata
RAC
Data Guard
Multitenant (CDB/PDB)

All backups are managed centrally by the Recovery Appliance.

17. Common Use Cases

ZDLRA is ideal for:

Banking
Insurance
Healthcare
Government
Retail
Telecommunications
Large ERP systems (Oracle E-Business Suite, SAP on Oracle, PeopleSoft, etc.)
Oracle Exadata environments

18. Best Practices

Use ZDLRA with Oracle Enterprise Edition.
Integrate with RMAN.
Combine ZDLRA with Oracle Data Guard for comprehensive protection.
Regularly verify protection policies.
Monitor redo transport and appliance capacity.
Test restore and recovery procedures periodically.
Define retention policies based on business and compliance requirements.
Protect the Recovery Appliance itself according to Oracle recommendations.

19. Real Production Scenario

Environment

2-node Oracle RAC
Oracle Active Data Guard
Oracle ZDLRA
25 TB production database

Incident

A storage administrator accidentally deleted critical application data.

What Happened?

Active Data Guard had already applied the delete operation.
The standby contained the same logical error.
The operations team used ZDLRA to perform a point-in-time recovery to the required timestamp.
The lost data was restored successfully with minimal downtime.

Result

No permanent data loss.
Rapid recovery.
Business operations resumed quickly.

20. Interview Questions

Basic

What is Oracle ZDLRA?
What is Incremental Forever?
What is a Virtual Full Backup?
What is Recovery Assurance?
Why is ZDLRA different from traditional backup solutions?

Intermediate

How does Continuous Redo Protection work?
Why does ZDLRA reduce production overhead?
How does ZDLRA integrate with RMAN?
What is the difference between a physical full backup and a Virtual Full Backup?
Can ZDLRA replace Active Data Guard?

Advanced

Explain the architecture of Oracle ZDLRA.
Why do enterprises deploy both Active Data Guard and ZDLRA?
How does ZDLRA help achieve near-zero data loss?
How would you protect hundreds of Oracle databases using ZDLRA?
Explain a recovery scenario involving ZDLRA after logical data corruption.

21. Summary

Oracle Zero Data Loss Recovery Appliance (ZDLRA) is far more than a backup server. It is an engineered system designed to provide enterprise-class backup, recovery, and recovery assurance for Oracle databases. By using Incremental Forever backups, Continuous Redo Protection, Virtual Full Backups, and automatic backup validation, ZDLRA significantly reduces backup windows while improving restore performance and confidence in recoverability.

While Oracle Active Data Guard ensures high availability and disaster recovery through synchronized standby databases, ZDLRA protects against backup failures, logical corruption, accidental data loss, and long-term retention requirements. Together, these technologies provide a comprehensive data protection strategy that combines continuous availability with reliable, fast, and validated recovery—making them a best-practice architecture for mission-critical Oracle environments.

Saturday, July 4, 2026

Oracle RAC SCAN Listener Architecture (How It Works)

1. Introduction

One of the biggest challenges in a clustered database environment is providing a single, stable connection point for client applications. Before Oracle Database 11g Release 2, clients had to know the hostname or IP address of every RAC node. Whenever a new node was added or removed, client connection strings often needed to be updated.

To solve this problem, Oracle introduced SCAN (Single Client Access Name).

SCAN provides a single hostname that clients use to connect to the RAC database. Oracle Clusterware and the SCAN Listeners automatically redirect client connections to the most appropriate RAC node, providing load balancing, high availability, and simplified administration.

Definition:
SCAN (Single Client Access Name) is a cluster-wide virtual hostname that provides a single entry point for all client connections to an Oracle RAC database.

2. Why SCAN is Required

Before SCAN (Oracle RAC 10g)

Clients needed to specify every node in the connection string.

Example:

Node-1 : racnode1.bsl.com
Node-2 : racnode2.bsl.com
Node-3 : racnode3.bsl.com

Connection String:

(DESCRIPTION=
 (ADDRESS=(HOST=racnode1))
 (ADDRESS=(HOST=racnode2))
 (ADDRESS=(HOST=racnode3))
)

Problems:

Complex client configuration
Difficult maintenance
Client updates required when adding/removing nodes
Harder to manage large clusters

After SCAN (Oracle 11gR2 and Later)

Clients use a single hostname:

prod-scan.company.com

Oracle automatically routes the connection to an appropriate node.

3. What is SCAN?

SCAN consists of:

One SCAN Name (DNS hostname)
Typically three SCAN IP addresses
Three SCAN Listeners (recommended)

Example:

SCAN Name

prod-scan.bsl.com

↓

192.168.1.210

192.168.1.211

192.168.1.212

Oracle Clusterware manages the SCAN listeners as cluster resources.

4. Oracle RAC SCAN Architecture

                      Client Applications
               (SQL*Plus / JDBC / OEM / ODP.NET)
                             │
                             │
                    DNS Lookup (SCAN Name)
                             │
                 prod-scan.company.com
                             │
        ┌───────────────────────────────────────┐
        │            DNS Resolution             │
        └───────────────────────────────────────┘
                 │          │           │
                 ▼          ▼           ▼
          SCAN IP-1   SCAN IP-2   SCAN IP-3
                 │          │           │
                 ▼          ▼           ▼
         SCAN Listener1  SCAN Listener2  SCAN Listener3
                 │
                 ▼
        Cluster Resource Manager
                 │
                 ▼
       Determines Best Available Instance
                 │
        ┌────────┴────────┐
        ▼                 ▼
     Node-1            Node-2
   Local Listener    Local Listener
        │                 │
        ▼                 ▼
   Instance-1        Instance-2

5. Components of SCAN Architecture

Component	Purpose
SCAN Name	Single hostname used by clients
DNS	Resolves SCAN name to multiple SCAN IPs
SCAN VIP	Virtual IP for SCAN Listener
SCAN Listener	Accepts initial client connection
Local Listener	Listener running on each RAC node
Database Service	Determines which instance receives the connection
Oracle Clusterware	Manages SCAN resources

6. SCAN Name

Clients never connect directly to a node.

Instead they connect to:

prod-scan.bsl.com

Advantages:

One hostname
Easy administration
No client changes when nodes are added
Simplified configuration

7. SCAN IP Addresses

Oracle recommends three SCAN IPs.

Example:

SCAN Name	SCAN IP
prod-scan.bsl.com	192.168.1.210
prod-scan.bsl.com	192.168.1.211
prod-scan.bsl.com	192.168.1.212

The DNS server returns these IPs using round-robin resolution.

8. SCAN Listeners

Each SCAN IP hosts one SCAN Listener.

Example:

SCAN-1

↓

SCAN Listener-1

↓

Node-1

SCAN-2

↓

SCAN Listener-2

↓

Node-2

SCAN-3

↓

SCAN Listener-3

↓

Node-3

A SCAN Listener can run on any RAC node. If a node fails, Oracle Clusterware automatically relocates the SCAN Listener to another healthy node.

9. Local Listener

Every RAC node runs its own Local Listener.

Example:

Node-1

↓

Local Listener

↓

Instance-1

The Local Listener:

Registers local database services
Accepts redirected client connections
Creates server processes

10. Dynamic Service Registration

Oracle instances automatically register themselves with the Local Listener using PMON (or equivalent background registration mechanisms in newer releases).

The Local Listener then shares service information with the SCAN Listener.

Registered information includes:

Instance Name
Database Name
Services
Load Information
Instance Status

11. Complete Client Connection Flow

Step 1

Application connects:

prod-scan.bsl.com

Step 2

DNS returns:

192.168.1.210

192.168.1.211

192.168.1.212

Step 3

Client connects to one SCAN Listener.

Client

↓

SCAN Listener

Step 4

SCAN Listener checks:

Which services are available?
Which instances provide the requested service?
Which instance is least loaded?

Step 5

SCAN Listener redirects the client.

SCAN Listener

↓

Local Listener

↓

Node-2

Step 6

Local Listener creates a dedicated (or shared, if configured) server process.

Listener

↓

Dedicated Server Process

↓

Instance

Step 7

Client communicates directly with the database instance.

The SCAN Listener is no longer involved in SQL processing after the initial connection.

12. SCAN Connection Flow Diagram

Application
      │
      ▼
SCAN Name (prod-scan.bsl.com)
      │
      ▼
DNS (3 SCAN IPs)
      │
      ▼
SCAN Listener
      │
      ▼
Local Listener
      │
      ▼
Database Service
      │
      ▼
Oracle Instance
      │
      ▼
SQL Processing

13. Load Balancing

SCAN supports Connection Load Balancing.

Example:

100 Users

↓

SCAN Listener

↓

40 Users → Node-1

35 Users → Node-2

25 Users → Node-3

Distribution depends on service configuration and instance load.

14. Node Failure Scenario

Suppose:

Node-2 crashes.

Before Failure:

Node-1

Node-2

Node-3

After Failure:

Node-1

Node-3

Oracle Clusterware:

Detects node failure.
Restarts the SCAN Listener (if necessary) on another node.
Relocates affected services according to policy.
New client connections are redirected to healthy instances.

15. Adding a New RAC Node

Without SCAN:

Modify every client connection string.
Add the new node information.

With SCAN:

Add Node

↓

Cluster Registers Services

↓

Clients Continue Using Same SCAN Name

No client-side connection string changes are required.

16. SCAN vs VIP

SCAN	VIP
Single client entry point	Node-specific virtual IP
Cluster-wide	One per RAC node
Uses SCAN Listeners	Uses Local Listener
Simplifies client configuration	Provides fast node failover
Load balancing	Node availability

17. SCAN vs Local Listener

SCAN Listener	Local Listener
Accepts initial client connection	Accepts redirected connection
Cluster-wide	Runs on each node
Redirects client	Establishes database session
Managed by Clusterware	Associated with a specific node

18. Useful SCAN Commands

Check SCAN Configuration

srvctl config scan

Example:

SCAN name: prod-scan.bsl.com

Check SCAN Listener

srvctl status scan_listener

Check SCAN VIP

srvctl status scan

View Listener Status

lsnrctl status

View Cluster Resources

crsctl stat res -t

19. Real-Time Production Scenario

Scenario: One RAC Node Goes Down

Environment

3-node Oracle RAC
Banking application
SCAN configured with three IPs

Incident

Node-2 unexpectedly crashes due to a hardware failure.

What Happens?

CSSD detects heartbeat loss.
CRSD marks Node-2 resources offline.
SCAN Listener previously running on Node-2 is relocated to another node if required.
Services configured on Node-2 are restarted on surviving instances according to policy.
New client connections continue using prod-scan.company.com.
The SCAN Listener redirects new sessions to Node-1 or Node-3.

Result

Existing sessions on Node-2 are disconnected unless protected by Application Continuity.
New connections continue successfully.
Users typically experience minimal disruption.

20. SCAN Best Practices

Configure three SCAN IP addresses for production clusters.
Register the SCAN name in DNS (or use Grid Naming Service where appropriate).
Ensure SCAN resolves correctly from client systems.
Do not hard-code node hostnames in client applications.
Use database services instead of instance names.
Monitor SCAN Listener status daily.
Verify that dynamic service registration is functioning.
Test failover and connection redirection during maintenance windows.

21. Common SCAN Issues and Troubleshooting

Problem	Possible Cause	Verification
Clients cannot connect	DNS resolution failure	`nslookup prod-scan.bsl.com`
SCAN Listener offline	Cluster resource issue	`srvctl status scan_listener`
Services not visible	Dynamic registration issue	`lsnrctl status`
Uneven connection distribution	Service configuration	`srvctl config service -d <db_name>`
Connection delays	Network or listener problems	Check listener logs and Clusterware resources

22. Summary

The SCAN Listener Architecture is one of the most significant enhancements introduced in Oracle RAC. It provides a single, cluster-wide connection point that hides the complexity of multiple RAC nodes from client applications. Through DNS resolution, SCAN Listeners, Local Listeners, and database services, Oracle automatically directs client connections to the most appropriate database instance while supporting connection load balancing and high availability.

By eliminating node-specific connection strings and allowing seamless cluster expansion without client reconfiguration, SCAN greatly simplifies RAC administration and improves application resilience. In modern Oracle RAC deployments, SCAN, together with VIPs, Oracle Clusterware, and database services, forms the foundation of a robust and highly available client connectivity architecture.

Wednesday, July 1, 2026

Oracle RAC Monitoring Framework

For production environments, I recommend turning it into a modular RAC Monitoring Framework rather than a single script. That makes it easier to schedule, troubleshoot, and extend.

Oracle RAC Monitoring Framework

Directory Structure

rac_monitoring/
├── rac_health_check.sh
├── db_health.sql
├── asm_health.sql
├── wait_events.sql
├── blocking_sessions.sql
├── tablespace.sql
├── fra_usage.sql
├── archive_log.sql
├── cpu_memory.sh
├── alert_log.sh
├── generate_report.sh
├── reports/
├── logs/
└── config.env

1. Configuration File (config.env)

#!/bin/bash

export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/app/19.0.0/grid
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

export ORACLE_SID=PROD1

export PATH=$GRID_HOME/bin:$ORACLE_HOME/bin:$PATH

DB_NAME=PROD

REPORT_DIR=/home/oracle/rac_monitoring/reports
LOG_DIR=/home/oracle/rac_monitoring/logs

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

REPORT=${REPORT_DIR}/RAC_Health_${DATE}.html
LOGFILE=${LOG_DIR}/RAC_Health_${DATE}.log

2. RAC Health Check Script (rac_health_check.sh)

#!/bin/bash

source ./config.env

exec > $LOGFILE

echo "==============================================="
echo "Oracle RAC Health Check"
echo "Server : $(hostname)"
echo "Date   : $(date)"
echo "==============================================="

echo
echo "=============================="
echo "Clusterware Status"
echo "=============================="
crsctl check crs

echo
echo "=============================="
echo "Cluster Resources"
echo "=============================="
crsctl stat res -t

echo
echo "=============================="
echo "Node Status"
echo "=============================="
olsnodes -n -s

echo
echo "=============================="
echo "ASM Status"
echo "=============================="
srvctl status asm

echo
echo "=============================="
echo "Diskgroups"
echo "=============================="
asmcmd lsdg

echo
echo "=============================="
echo "Database Status"
echo "=============================="
srvctl status database -d ${DB_NAME}

echo
echo "=============================="
echo "Services"
echo "=============================="
srvctl status service -d ${DB_NAME}

echo
echo "=============================="
echo "Listener"
echo "=============================="
srvctl status listener

echo
echo "=============================="
echo "SCAN Listener"
echo "=============================="
srvctl status scan_listener

echo
echo "=============================="
echo "VIP"
echo "=============================="
srvctl status vip

echo
echo "=============================="
echo "OCR"
echo "=============================="
ocrcheck

echo
echo "=============================="
echo "Voting Disk"
echo "=============================="
crsctl query css votedisk

echo
echo "Health Check Completed"

3. Wait Event Monitoring (wait_events.sql)

set lines 200
col event format a45

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

4. Blocking Sessions

set lines 200

SELECT
inst_id,
sid,
serial#,
username,
blocking_session,
seconds_in_wait,
event
FROM gv$session
WHERE blocking_session IS NOT NULL;

5. ASM Monitoring

set lines 200

SELECT
name,
state,
type,
total_mb,
free_mb,
ROUND(free_mb*100/total_mb,2) FREE_PERCENT
FROM
v$asm_diskgroup;

6. Tablespace Monitoring

SELECT
tablespace_name,
ROUND(used_percent,2) USED_PERCENT
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

7. FRA Monitoring

SELECT
SPACE_LIMIT/1024/1024 MB_LIMIT,
SPACE_USED/1024/1024 MB_USED,
SPACE_RECLAIMABLE/1024/1024 MB_RECLAIMABLE
FROM
V$RECOVERY_FILE_DEST;

8. Archive Log Generation

SELECT
TRUNC(first_time),
COUNT(*),
ROUND(SUM(blocks*block_size)/1024/1024/1024,2) GB
FROM
v$archived_log
GROUP BY
TRUNC(first_time)
ORDER BY
1 DESC;

9. CPU & Memory Monitoring (cpu_memory.sh)

#!/bin/bash

echo "========== CPU =========="
top -bn1 | head -5

echo

echo "========== Memory =========="
free -g

echo

echo "========== Swap =========="
swapon -s

echo

echo "========== Disk =========="
df -h

10. Alert Log Monitoring (alert_log.sh)

#!/bin/bash

adrci exec="show alert -tail 200"

11. Cluster Log Collection

#!/bin/bash

diagcollection.pl --collect cluster

12. Email Report

mailx -s "Oracle RAC Health Report $(hostname)" \
shashi_dba@shashidba.com < $LOGFILE

13. Cron Scheduling

Run every hour:

0 * * * * /home/oracle/rac_monitoring/rac_health_check.sh

Run daily at 8 AM:

0 8 * * * /home/oracle/rac_monitoring/rac_health_check.sh

Run every Sunday:

0 6 * * 0 /home/oracle/rac_monitoring/rac_health_check.sh

Sample Health Check Output

===================================================
Oracle RAC Health Check
===================================================

Hostname : racnode1
Date     : 01-Jul-2026 08:00

✔ CRS Status               ONLINE
✔ Cluster Resources        ONLINE
✔ Node Status              ACTIVE
✔ ASM                      RUNNING
✔ Diskgroups               DATA, RECO, OCR
✔ Database                 PROD OPEN
✔ Services                 RUNNING
✔ Listener                 RUNNING
✔ SCAN                     RUNNING
✔ VIP                      RUNNING
✔ OCR                      HEALTHY
✔ Voting Disk              NORMAL

Tablespace Usage
----------------------------
SYSTEM        72%
SYSAUX        61%
USERS         42%
TEMP          15%

ASM Usage
----------------------------
DATA      67%
RECO      58%

Blocking Sessions : NONE

Top Wait Event
----------------------------
db file sequential read

CPU Usage : 18%
Memory Usage : 63%

Overall RAC Health : PASS

Oracle RAC Administration Handbook

Absolutely. Given the amount of content, this is best developed as a complete handbook rather than a single chat response.

📘 Oracle RAC Administration Handbook (100–150 Pages)

Section 1 – Oracle RAC Fundamentals

Oracle RAC Architecture
RAC Components
Grid Infrastructure
Oracle Clusterware
ASM Architecture
Cache Fusion
Global Cache Service (GCS)
Global Enqueue Service (GES)
OCR & Voting Disk
SCAN, VIP, GNS
RAC Networking
RAC Storage Architecture
RAC vs Single Instance
RAC vs Data Guard
Real-world RAC Deployment Architecture

Section 2 – Oracle RAC Installation

Hardware Prerequisites
OS Configuration
Kernel Parameters
User Configuration
Passwordless SSH
Network Planning
Storage Planning
ASM Configuration
Grid Infrastructure Installation
RAC Database Installation
Post-installation Verification
Architecture diagrams throughout

Section 3 – RAC Administration

Instance Management
Service Management
Listener Management
SCAN Management
VIP Management
OCR Backup & Restore
Voting Disk Management
Node Addition
Node Deletion
Database Creation
Database Deletion
RAC Patching
OPatchAuto
Rolling Patch
One-off Patch
RU Upgrade

Section 4 – Oracle RAC Health Check Framework

This section expands the framework into approximately 25–30 pages.

Includes:

Clusterware Health Check
ASM Health Check
Database Health Check
Node Health Check
Listener Health Check
VIP Health Check
SCAN Health Check
OCR Health Check
Voting Disk Health Check
CRS Resource Health Check
Cache Fusion Monitoring
Interconnect Latency Checks
Redo Log Health
Undo Health
Tablespace Health
FRA Health
Archive Log Health
Alert Log Review
ADRCI Diagnostics
AWR Health Indicators
ASH Monitoring
Blocking Sessions
Wait Events
OS Monitoring
Filesystem Checks

Each topic will include:

Purpose
Commands
Sample outputs
Interpretation
Common issues
Troubleshooting steps
Best practices

Section 5 – RAC Monitoring Scripts

Cluster Health Script

#!/bin/bash

echo "================================="
echo "Oracle RAC Health Check"
echo "================================="

hostname

echo
echo "CRS Status"
crsctl check crs

echo
echo "Node Status"
olsnodes -s

echo
echo "ASM Status"
srvctl status asm

echo
echo "Diskgroups"
asmcmd lsdg

echo
echo "Database Status"
srvctl status database -d PROD

echo
echo "Services"
srvctl status service -d PROD

echo
echo "VIP Status"
srvctl status vip

echo
echo "SCAN Listener"
srvctl status scan_listener

echo
echo "OCR"
ocrcheck

echo
echo "Voting Disk"
crsctl query css votedisk

echo
echo "Resources"
crsctl stat res -t

Wait Event Monitoring Script

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

Blocking Session Script

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

ASM Space Monitoring

SELECT
name,
total_mb,
free_mb,
ROUND(free_mb*100/total_mb,2) FREE_PERCENT
FROM
v$asm_diskgroup;

Cluster Resource Report

crsctl stat res -t

VIP Verification

srvctl status vip

OCR Verification

ocrcheck

CRS Alert Monitoring

adrci

show alert

Cluster Log Collection

diagcollection.pl --collect cluster

Section 6 – Automation Framework

The handbook will include a Daily Health Check Automation that generates HTML reports, CSV summaries, and email notifications.

Features:

Clusterware status
ASM status
Diskgroup utilization
Database status
Listener status
Services
SCAN
VIP
OCR
Voting disks
CPU
Memory
Disk usage
Top wait events
Blocking sessions
FRA usage
Archive log generation
Tablespace utilization
Alert log errors
CRS errors

Output formats:

HTML dashboard
CSV report
Email summary
Log file

Section 7 – Performance Tuning

Cache Fusion tuning
Interconnect tuning
ASM tuning
HugePages
NUMA
Linux kernel tuning
AWR analysis
ASH analysis
ADDM
SQL Monitoring
OSWatcher
ExaWatcher
Cluster Health Monitor (CHM)

Section 8 – Production Incident Runbooks (40+)

Examples include:

Node Eviction
CRS Won't Start
CSS Failure
ASM Disk Offline
OCR Corruption
Voting Disk Failure
VIP Not Failing Over
SCAN Listener Down
Split Brain
ORA-29740
ORA-29702
CRS-4535
CRS-4530
CRS-1606
PRCR-1079
PRCR-1064
ORA-15064
ORA-15032
ORA-15041
ORA-15042
ORA-00257
ORA-19809
Interconnect Packet Loss
High GCS Waits
gc buffer busy
gc cr request
gc current block busy

Each runbook will include:

Symptoms
Root cause
Diagnostic commands
Resolution steps
Validation
Prevention
Lessons learned

Section 9 – Oracle RAC Interview Guide

500+ interview questions
L1 questions
L2 questions
L3 questions
Oracle ACE–level scenarios
Whiteboard architecture questions
Real production case studies

Section 10 – Architecture Diagrams

The handbook will contain over 50 professional diagrams, including:

Oracle RAC Architecture
Grid Infrastructure
Cache Fusion Flow
GCS/GES Communication
SCAN Listener Flow
VIP Failover
OCR Architecture
Voting Disk Layout
ASM Diskgroup Architecture
Redo Thread Architecture
RAC Networking
Client Connection Flow
Clusterware Stack
Service Failover
Node Eviction Flow
Split Brain Detection
CRS Startup Sequence
Rolling Patch Architecture
RAC + Data Guard Hybrid Architecture
RAC Backup Architecture
RAC Disaster Recovery Design

Oracle RAC Health Check Framework

Standard Operating Procedure (SOP)

Document Version: 1.0
Applicable Versions: Oracle RAC 11gR2, 12c, 18c, 19c, 21c, 23ai, 26ai
Prepared For: Oracle Database Administrators (L1/L2/L3)

Purpose

This document provides a structured Oracle RAC Health Check Framework that helps DBAs verify the health of Oracle Clusterware, ASM, Database, Network, and Cluster Resources. Performing these checks regularly helps detect issues early, reduce downtime, and maintain high availability.

Health Check Workflow

Clusterware
      │
      ▼
Node Status
      │
      ▼
ASM Health
      │
      ▼
Database Health
      │
      ▼
Network Health
      │
      ▼
Cluster Resources

1. Clusterware Health Check

Objective

Verify that Oracle Clusterware components are running correctly.

Components

OHASD
CSSD
CRSD
EVMD

Command

crsctl check crs

Expected Output

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Validation

Component	Expected Status
OHASD	Online
CSSD	Online
CRSD	Online
EVMD	Online

If Failed

Check Clusterware logs.
Verify voting disks.
Verify OCR accessibility.
Restart Clusterware if required.

2. Node Health Check

Objective

Ensure all RAC nodes are available and participating in the cluster.

Commands

olsnodes

olsnodes -s

olsnodes -n

Expected Output

racnode1 Active
racnode2 Active

Validation

All nodes visible
Status should be Active
Node numbers should match cluster configuration

Troubleshooting

If a node is missing:

Verify private interconnect
Check Clusterware
Verify CSSD
Review node logs

3. ASM Health Check

Objective

Verify ASM availability and storage health.

Check ASM Status

srvctl status asm

Expected

ASM is running on racnode1
ASM is running on racnode2

Check Diskgroups

asmcmd lsdg

Example

DATA
RECO
OCR

Verify

Mounted
Free Space
Offline Disks
Redundancy

SQL Validation

SELECT
name,
state,
type,
total_mb,
free_mb
FROM v$asm_diskgroup;

Troubleshooting

Check failed disks
Verify ASM alert log
Validate storage connectivity

4. Database Health Check

Objective

Ensure all RAC database instances and services are available.

Database Status

srvctl status database -d <db_name>

Expected

Instance PROD1 is running
Instance PROD2 is running

Service Status

srvctl status service -d <db_name>

Verify

Application services
Preferred instances
Available instances

SQL Validation

SELECT
INSTANCE_NAME,
STATUS,
DATABASE_STATUS
FROM GV$INSTANCE;

Expected

OPEN
ACTIVE

5. Network Health Check

Objective

Verify communication between RAC nodes.

Public and Private Network

oifcfg getif

Verify

Public Interface
Private Interconnect

Network Configuration

srvctl config network

SCAN Configuration

srvctl config scan

Verify

SCAN Name
SCAN IPs
SCAN Listeners

VIP Status

srvctl status vip

Expected

VIP is enabled
VIP is running

Troubleshooting

Verify DNS
Check SCAN listeners
Verify VIP failover
Test private interconnect latency

6. Cluster Resource Health Check

Objective

Verify all Oracle Cluster resources are online.

Command

crsctl stat res -t

Verify

Database
ASM
Listeners
VIPs
SCAN Listeners
Diskgroups

Expected Status

ONLINE

Additional Recommended Health Checks

Listener Status

srvctl status listener

SCAN Listener Status

srvctl status scan_listener

OCR Check

ocrcheck

Expected

Status : healthy

Voting Disk

crsctl query css votedisk

Verify

All voting disks accessible

Cluster Synchronization

crsctl check css

CRS Stack

crsctl stat res -t

Verify every resource is ONLINE.

Daily RAC Health Check Checklist

Check	Status
Clusterware Running	☐
All Nodes Active	☐
ASM Running	☐
Diskgroups Mounted	☐
Database Open	☐
RAC Services Running	☐
Public Network Healthy	☐
Private Interconnect Healthy	☐
VIP Running	☐
SCAN Listener Running	☐
OCR Healthy	☐
Voting Disk Healthy	☐
Cluster Resources ONLINE	☐

Common Production Issues

Issue	Possible Cause	Resolution
Node Eviction	Interconnect failure	Check private network and CSS logs
ASM Down	Storage unavailable	Verify SAN/ASM disks and restart ASM
VIP Offline	Network issue	Validate interface and relocate VIP
Service Not Running	Instance failure	Start service with SRVCTL
CRS Resource Offline	Clusterware issue	Review CRS logs and restart the affected resource
Diskgroup Not Mounted	Disk failure	Check ASM disks and storage connectivity

Best Practices

Perform RAC health checks daily.
Monitor ASM free space and rebalance operations.
Verify OCR and voting disk health after maintenance.
Monitor interconnect latency to prevent node eviction.
Ensure SCAN listeners and VIPs are functioning correctly.
Keep Clusterware and database patches up to date.
Review alert logs and CRS logs regularly.
Automate routine health checks using shell scripts or Enterprise Manager where possible.

Conclusion

A disciplined RAC health check routine is essential for maintaining a stable Oracle RAC environment. Regular verification of Clusterware, nodes, ASM, databases, networking, and cluster resources helps identify issues proactively, minimize downtime, and ensure continuous availability of critical business applications.

Friday, May 22, 2026

Types of Patching in Oracle DBA

Patching is one of the most critical responsibilities of an Oracle DBA. Oracle regularly releases patches to fix bugs, close security vulnerabilities, improve database performance, and maintain overall system stability.

Understanding the different types of Oracle patches helps DBAs choose the correct patching strategy for production, testing, and mission-critical environments.

What is OPatch?

OPatch is Oracle’s official patching utility used to apply and manage patches in Oracle software.

It is located under:

$ORACLE_HOME/OPatch/opatch

Main Functions of OPatch

OPatch is used to:

Apply patches
Rollback patches
Verify installed patches
Check patch conflicts
Generate inventory details

Important OPatch Commands

Check OPatch Version

opatch version

Check Installed Patches

opatch lsinventory

Verify Oracle Home

opatch lsinventory -detail

Important Note Before Patching

Before applying any Oracle patch:

Always update OPatch utility to the latest version
Take complete database backup
Validate Oracle Home space
Check patch conflicts
Test patches in lower environments first

Types of Oracle Patching

Oracle provides multiple types of patches depending on the purpose and severity of fixes.

1. Interim Patch (One-Off Patch)

An Interim Patch, also called a One-Off Patch, is created to fix a specific issue or bug.

Oracle Support usually provides this patch when a customer encounters a particular production issue requiring immediate resolution.

Characteristics

Fixes only one specific issue
Released for a targeted bug
Usually provided through Oracle Support
Can be applied quickly without major upgrades

Use Case

Suppose a production database crashes due to a known Oracle bug. Oracle Support may provide a one-off patch specifically for that issue.

Advantages

Quick problem resolution
Minimal changes to environment
Faster deployment

Limitations

Fixes only one issue
May conflict with future patches
Requires careful compatibility checks

2. PSU (Patch Set Update)

Patch Set Update (PSU) was Oracle’s traditional quarterly patching model before RU became standard.

It contains:

Security fixes
Critical bug fixes
Stability improvements

Characteristics

Released quarterly
Cumulative patch
Safer and more stable than one-off patches
Recommended for production systems

Advantages

Lower risk
Well tested by Oracle
Improves overall stability

Typical Example

11.2.0.4 PSU
12.1 PSU

3. CPU (Critical Patch Update)

CPU patches mainly focus on security vulnerabilities.

These patches help protect Oracle databases from:

Cyber attacks
Privilege escalation
Security exploits
Vulnerability exposure

Characteristics

Security-focused patching
Released quarterly
Essential for compliance and audits

Importance

Organizations running internet-facing or critical systems must regularly apply CPU patches to remain secure.

Difference Between PSU and CPU

PSU	CPU
Includes security + bug fixes	Primarily security fixes
More comprehensive	Security-focused
Preferred for stability	Preferred for urgent security needs

4. RU (Release Update)

Release Update (RU) is Oracle’s modern patching model introduced for newer Oracle versions like Oracle 19c.

RU is now the recommended patching strategy by Oracle.

RU Includes

Security fixes
Optimizer fixes
Functional fixes
Performance improvements
Regression fixes

Characteristics

Released quarterly
Cumulative patching model
Includes all previous RUs
Standard patching method for Oracle 19c+

Example

Oracle Database 19c RU
19.22 RU
19.23 RU

Why RU is Important

RU keeps the database:

Secure
Stable
Fully supported by Oracle
Updated with latest fixes

5. RUR (Release Update Revision)

Release Update Revision (RUR) is a more conservative version of RU.

RUR contains:

Critical security fixes
Important regression fixes

But avoids introducing too many new changes.

Characteristics

More stable than RU
Smaller change set
Reduced risk
Suitable for highly sensitive production environments

Best Use Cases

RUR is useful for:

Banking environments
Financial systems
Telecom production systems
Highly critical enterprise applications

RU vs RUR

RU	RUR
Includes all new fixes and enhancements	Includes limited critical fixes
Faster innovation	Greater stability
More changes	Fewer changes
Recommended generally	Recommended for conservative environments

6. Patch Set

A Patch Set is a major patch release containing:

New features
Bug fixes
Functional enhancements

It is closer to a minor version upgrade.

Example

11.2.0.1 → 11.2.0.4

Characteristics

Larger upgrade process
Requires extensive testing
May require downtime
Includes many internal changes

Patch Set vs RU

Patch Set	RU
Major upgrade	Incremental update
Includes feature changes	Mostly fixes and stability
Larger downtime	Smaller maintenance window

Oracle Patching Best Practices

Every DBA should follow standard patching best practices.

1. Take Full Backup Before Patching

Always perform:

RMAN backup
Oracle Home backup
SPFILE backup
OCR/Voting Disk backup (for RAC)

2. Verify Patch Compatibility

Check:

opatch prereq CheckConflictAgainstOHWithDetail

3. Validate OPatch Version

Older OPatch versions may fail during patching.

Always use the latest supported OPatch utility.

4. Test in Non-Production First

Never directly patch production without testing in:

DEV
TEST
UAT

5. Monitor Logs Carefully

Important patch logs:

$ORACLE_HOME/cfgtoollogs/opatch

6. Use Datapatch for SQL Changes

For Oracle 12c and above:

datapatch -verbose

This updates SQL components after binary patching.

General Oracle Patching Workflow

Step 1 — Download Patch

Download from:

Oracle Support (My Oracle Support)

Step 2 — Unzip Patch

unzip p34765931_190000_Linux-x86-64.zip

Step 3 — Stop Database Services

srvctl stop database -d PROD

Step 4 — Apply Patch

opatch apply

Step 5 — Run Datapatch

datapatch -verbose

Step 6 — Verify Patch

opatch lsinventory

Common Oracle Patching Challenges

DBAs commonly face:

Patch conflicts
Inventory corruption
Insufficient Oracle Home space
OPatch version mismatch
Datapatch failures
RAC rolling patch issues

Proper planning and testing help avoid these problems.

Key Takeaways

OPatch is Oracle’s official patching utility
Interim patches fix specific issues quickly
PSU and CPU were traditional quarterly patch models
RU is the modern recommended patching standard
RUR provides extra stability with fewer changes
Patch Sets are major upgrade-level patch releases
Proper backup and testing are mandatory before patching

Final Thoughts

Oracle patching is not just a maintenance task — it is a critical part of database administration.

A skilled Oracle DBA must understand:

Which patch type to use
When to apply it
How to minimize downtime
How to maintain database stability

Proper patch management ensures that Oracle databases remain:

Secure
High performing
Stable
Fully supported by Oracle

A well-patched database environment is one of the strongest foundations of a reliable enterprise system.

#OracleDatabase #OracleDBA #Oracle19c #OraclePatching #OPatch #DatabaseAdministration #OracleRU #OracleRUR #OraclePSU #CriticalPatchUpdate #DatabaseSecurity #OracleSupport #PatchManagement #OracleRAC #OracleCloud #DatabaseMaintenance #OracleTechnology #DBACommunity #DatabasePerformance #OracleLearning #OracleAdmin #ITInfrastructure #EnterpriseDatabase #OracleExperts #OracleUpgrade #Datapatch #RMAN #DatabaseOperations #TechCommunity #AIOUG

Sunday, May 3, 2026

IP Requirements for 3-Node Oracle RAC Database

🎯 Objective

This SOP explains the IP addressing requirements for a 3-node Oracle Real Application Clusters (RAC) environment, including:

Public IPs
Private Interconnect IPs
Virtual IPs (VIPs)
SCAN IPs
DNS / Hosts configuration
Best practices

🏗️ 1. Oracle RAC Network Architecture Overview

A RAC cluster requires multiple network interfaces for:

Client connectivity
Cluster communication
Failover handling
SCAN-based load balancing

📊 2. IP Types Required in 3-Node RAC

IP Type	Purpose	Quantity Required
Public IP	Node communication & client access	3
Private IP	Interconnect / Cache Fusion	3
VIP (Virtual IP)	Fast failover handling	3
SCAN IP	Client load balancing	3
DNS Entry	Name resolution	Required

🧠 3. Understanding Each IP Type

🌐 A. Public IP

Purpose

Normal communication
SSH access
Node hostname resolution

Requirement

One public IP per node.

Example

Node	Hostname	Public IP
Node1	rac1	192.168.1.101
Node2	rac2	192.168.1.102
Node3	rac3	192.168.1.103

🔒 B. Private IP (Interconnect)

Purpose

Used for:

Cache Fusion
Cluster heartbeat
Internode communication

Important

This network must be:

Private
Low latency
High speed

Example

Node	Private Hostname	Private IP
Node1	rac1-priv	10.10.10.101
Node2	rac2-priv	10.10.10.102
Node3	rac3-priv	10.10.10.103

⚡ C. VIP (Virtual IP)

Purpose

Fast client failover.

If a node fails:

VIP relocates to another node
Client gets immediate TCP reset
Faster reconnection

Requirement

One VIP per node.

Example

Node	VIP Hostname	VIP IP
Node1	rac1-vip	192.168.1.111
Node2	rac2-vip	192.168.1.112
Node3	rac3-vip	192.168.1.113

🔄 D. SCAN IP (Single Client Access Name)

Purpose

Provides:

Load balancing
Simplified client connection
Transparent node addition/removal

Requirement

Minimum 3 SCAN IPs recommended by Oracle.

Example

SCAN Name	SCAN IPs
rac-scan	192.168.1.120
rac-scan	192.168.1.121
rac-scan	192.168.1.122

🧾 4. Total IP Requirement Summary

For 3-Node RAC

Type	Count
Public IP	3
Private IP	3
VIP	3
SCAN IP	3
Total	12 IPs

📁 5. Sample /etc/hosts Configuration

# Public IPs
192.168.1.101   rac1
192.168.1.102   rac2
192.168.1.103   rac3

# VIPs
192.168.1.111   rac1-vip
192.168.1.112   rac2-vip
192.168.1.113   rac3-vip

# Private IPs
10.10.10.101    rac1-priv
10.10.10.102    rac2-priv
10.10.10.103    rac3-priv

# SCAN
192.168.1.120   rac-scan
192.168.1.121   rac-scan
192.168.1.122   rac-scan

🌐 6. DNS Requirements

Oracle strongly recommends:

DNS configuration
OR
GNS (Grid Naming Service)

✅ DNS Checks

nslookup rac-scan

ping rac1-vip

⚙️ 7. Network Interface Example

Interface	Purpose
eth0	Public
eth1	Private Interconnect

🚨 8. Important Best Practices

✅ Public Network

Use bonded NICs if possible
Ensure low packet loss

✅ Private Interconnect

Dedicated network only
Jumbo frames recommended
No public traffic

✅ VIP

Must be unused IPs
Same subnet as public IP

✅ SCAN

Must resolve to 3 IPs
Round-robin DNS recommended

🔍 9. Pre-Installation Validation Commands

Verify Interconnect

oifcfg getif

Verify Cluster Network

cluvfy comp nodecon -n rac1,rac2,rac3 -verbose

Verify SCAN

nslookup rac-scan

🧪 10. Oracle RAC Connection Flow

Client
   ↓
SCAN Listener
   ↓
Node Listener
   ↓
RAC Instance

⚠️ 11. Common Issues

Issue	Cause
Node eviction	Private interconnect failure
Slow failover	VIP misconfiguration
Connection issue	SCAN DNS issue
Split brain	Interconnect latency

🔥 12. Interview Questions & Answers

Q1: Why are VIPs used in RAC?

Answer:

VIPs provide faster client failover by immediately rejecting failed TCP connections instead of waiting for timeout.

Q2: Why 3 SCAN IPs?

Answer:

Oracle recommends 3 SCAN IPs for high availability and load balancing.

Q3: Can SCAN IPs be on different subnet?

Answer:

No, SCAN IPs should typically be on same public subnet.

🏁 13. Final Checklist Before RAC Installation

Check	Status
Public IP configured	✅
Private IP configured	✅
VIP available	✅
SCAN resolves correctly	✅
DNS working	✅
Interconnect tested	✅

🚀 14. Recommended Enterprise Design

Production Environment

Bonded NICs
Redundant switches
Dedicated interconnect VLAN
DNS-managed SCAN