FOA(Fun Oracle Apps) - Learning Never Stops

Oracle Reports RWConverter Crash During REX to RDF Conversion

noreply@blogger.com (Himanshu) — Thu, 4 Jun 2026 09:29:22 +0530

Oracle Reports RWConverter Crash During REX to RDF Conversion

While working on an Oracle Reports migration task, I encountered an issue where rwconverter consistently failed while converting a report from REX to RDF format.

The error was not very descriptive. The converter crashed and generated a stack trace referencing Oracle Reports runtime libraries (librw.so) and PL/SQL parsing functions. At first glance, it appeared to be a report corruption issue or an Oracle Reports bug.

Fortunately, the fix turned out to be quite simple.

The Problem

The report conversion was being executed using:

rwconverter.sh \
userid=user/password@DB \
stype=rex \
source=myreport.rex \
dtype=rdf \
dest=myreport.rdf

Instead of completing successfully, rwconverter terminated unexpectedly and displayed a stack trace containing references to Oracle Reports internal libraries and PL/SQL processing routines.

Because the failure occurred during report parsing, initial troubleshooting focused on:

Invalid report objects
Corrupted report definitions
Invalid database packages
Oracle Reports patches
Environment configuration issues

None of these investigations revealed the root cause.

The Solution

Before executing rwconverter, set the following environment variable:

export DE_DISABLE_PLS_512=0

Then rerun the conversion:

export DE_DISABLE_PLS_512=0

rwconverter.sh \
userid=user/password@DB \
stype=rex \
source=myreport.rex \
dtype=rdf \
dest=myreport.rdf

After setting this variable, the conversion completed successfully and the RDF file was generated without errors.

What Does DE_DISABLE_PLS_512 Mean?

DE_DISABLE_PLS_512 is an Oracle Developer/Reports environment variable related to the PL/SQL engine used by Oracle Developer tools.

The variable influences how Oracle Reports handles PL/SQL processing internally. During report compilation, conversion, and validation, Oracle Reports parses embedded PL/SQL contained in:

Formula columns
Report triggers
Program units
Lexical parameters
PL/SQL libraries

In certain environments, PL/SQL processing can cause unexpected failures during report conversion. Setting:

export DE_DISABLE_PLS_512=0

ensures that the appropriate PL/SQL handling mechanism is enabled during execution, which can prevent crashes encountered while converting reports.

Although Oracle has not widely documented every internal behavior associated with this variable, it has been known within Oracle Reports troubleshooting scenarios to resolve issues related to PL/SQL parsing and report compilation.

When Should You Try This?

Consider testing this setting if:

rwconverter crashes during execution
You are converting reports from REX to RDF
Stack traces reference librw.so
The failure appears related to PL/SQL parsing
Database objects are valid but the converter still fails
Manual report recompilation is being performed as part of troubleshooting

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

ORA-01555 on Physical Standby Databases Opened in Read Only Mode

noreply@blogger.com (Himanshu) — Fri, 29 May 2026 10:47:59 +0530

ORA-01555 on Physical Standby Databases Opened in Read Only Mode

Deep Dive into Snapshot Too Old Errors in Active Data Guard Environments

Modern Oracle environments frequently use standby databases not only for disaster recovery but also for reporting and analytics. With Active Data Guard, organizations can open a physical standby database in read-only mode while redo apply continues in real time. This architecture is powerful, but it introduces a problem many DBAs do not initially expect:

ORA-01555: snapshot too old

Most administrators associate ORA-01555 with highly transactional primary databases. However, this error can also occur on standby databases even though users are not directly modifying data there.

This article explains why ORA-01555 happens on standby databases, how Oracle internally manages read consistency during redo apply, and the most effective methods to eliminate the issue in production systems.

Understanding ORA-01555

ORA-01555 occurs when Oracle cannot reconstruct an older version of a data block required for query consistency.

Oracle guarantees that a query sees data exactly as it existed when the query started. This mechanism is called read consistency.

To achieve this consistency, Oracle uses undo records. When blocks change, Oracle stores older versions of the data inside undo segments so queries can continue reading a consistent image.

The error appears when:

A query runs for a long period
Oracle needs older undo information
The required undo has already been overwritten

At that point, Oracle can no longer recreate the earlier block image, and the query fails with ORA-01555.

Why ORA-01555 Happens on a Read-Only Standby

One of the biggest misconceptions is that standby databases do not generate undo because users cannot perform DML operations.

That assumption is incorrect.

In an Active Data Guard configuration:

Redo is continuously shipped from the primary database
Managed Recovery Process (MRP) applies those changes on standby
Data blocks on standby are constantly changing internally
Oracle still requires undo information to maintain read consistency for active queries

Although the standby is open in read-only mode for users, redo apply behaves like continuous internal update activity.

As a result, reporting queries running on standby may need older versions of blocks while redo apply continues modifying them.

If the necessary undo information disappears before the query completes, Oracle raises:

ORA-01555: snapshot too old

Internal Concept Behind the Error

To fully understand the issue, consider the following example.

A reporting query begins at:

10:00 AM

At the same time:

Redo from primary continues arriving
MRP continuously applies changes
Blocks are updated internally on standby
Undo records are generated and reused

Suppose the query still requires an old version of a block at:

10:45 AM

But the undo information needed to reconstruct that block has already been overwritten due to ongoing redo apply activity.

Oracle can no longer provide a consistent image to the query, which leads to:

ORA-01555

This is why even a “read-only” standby database can experience snapshot too old errors.

Common Reasons for ORA-01555 on Standby

1. Long-Running Reporting Queries

This is the most common cause.

Large reporting jobs, BI dashboards, or ETL extraction queries may run for several hours while redo apply continuously changes underlying blocks.

The longer the query runs, the higher the chance that required undo information gets overwritten.

2. Heavy Redo Generation on Primary

Standby databases directly inherit workload pressure from the primary database.

Operations such as:

Bulk updates
Data purges
Massive ETL jobs
Index rebuilds
Batch processing

generate huge amounts of redo.

When redo apply becomes extremely active, undo segments on standby recycle more aggressively.

This significantly increases ORA-01555 probability.

3. Small Undo Tablespace

Even if undo retention is configured correctly, a small undo tablespace forces Oracle to reuse undo extents earlier than desired.

Many standby databases are configured with minimal storage because administrators assume they only serve DR purposes. Once reporting workloads begin using standby, that sizing becomes insufficient.

4. Low Undo Retention

Undo retention determines how long Oracle attempts to preserve undo data.

If retention is shorter than the runtime of reporting queries, undo may disappear before queries finish.

5. Simultaneous Reporting and Recovery Peaks

ORA-01555 commonly appears during periods where:

Reporting activity is high
Redo apply is also extremely active

For example:

Nightly reporting overlapping with ETL windows
Financial reporting during month-end loads
BI extractions during bulk imports

Diagnosing ORA-01555 on Standby

The first step is confirming that the standby database is actively applying redo while serving queries.

Check Database Role and Open Mode

SELECT database_role,
       open_mode
FROM v$database;

Typical output:

PHYSICAL STANDBY
READ ONLY WITH APPLY

This confirms Active Data Guard is enabled.

Identifying Long Running Queries

Queries with long execution times are primary candidates.

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

Focus on sessions with very large LAST_CALL_ET values.

Analyzing Undo Statistics

Oracle provides undo history in V$UNDOSTAT.

SELECT begin_time,
       tuned_undoretention,
       maxquerylen,
       undoblks,
       txncount
FROM v$undostat
ORDER BY begin_time;

Important values:

Column	Meaning
TUNED_UNDORETENTION	-- Actual effective undo retention
MAXQUERYLEN	-- Longest running query duration

If:

MAXQUERYLEN > TUNED_UNDORETENTION

then ORA-01555 risk is extremely high.

Checking Undo Tablespace Size

SELECT tablespace_name,
       SUM(bytes)/1024/1024 MB
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%'
GROUP BY tablespace_name;

Small undo tablespaces are often the root cause in standby reporting systems.

Reviewing Alert Logs

Alert logs help correlate ORA-01555 with workload spikes.

Search for:

ORA-01555

Then compare timestamps with:

ETL execution
Batch jobs
Redo spikes
Reporting schedules

Best Solutions for ORA-01555 on Standby

Increase Undo Retention

One of the most effective fixes is increasing undo retention.

Check current value:

SHOW PARAMETER undo_retention;

Increase retention:

ALTER SYSTEM SET undo_retention=14400;

This preserves undo for four hours.

However, retention alone is not sufficient unless adequate undo space exists.

Expand Undo Tablespace

A small undo tablespace causes Oracle to overwrite undo regardless of retention settings.

Adding additional space is often the real fix.

Example:

ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/u01/oradata/UNDOTBS02.dbf'
SIZE 20G AUTOEXTEND ON;

Also consider enabling autoextend:

ALTER DATABASE DATAFILE
'/u01/oradata/UNDOTBS01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

Optimize Reporting Queries

Long-running queries increase ORA-01555 exposure.

Performance tuning can dramatically reduce failures.

Key improvements include:

Using indexes efficiently
Eliminating unnecessary full table scans
Partition pruning
Reducing sorting overhead
Improving join conditions
Avoiding Cartesian joins

Reducing query runtime reduces undo dependency duration.

Reduce Redo Apply During Reporting Windows

Some organizations temporarily pause redo apply during critical reporting periods.

Stop apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Restart apply afterward:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

This approach reduces internal block churn while reports execute.

However, it also increases standby lag and should be used carefully.

Separate Reporting from Disaster Recovery

Large enterprise environments often deploy:

One standby for disaster recovery
Another standby dedicated for reporting

This isolates reporting workloads from recovery activity and significantly improves stability.

Monitor Undo Proactively

Many ORA-01555 incidents can be avoided through monitoring.

Recommended query:

SELECT tuned_undoretention,
       maxquerylen
FROM v$undostat;

If maximum query length consistently approaches undo retention values, action should be taken before failures occur.

Real Production Scenario

A financial institution deployed Oracle 19c Active Data Guard for reporting.

The standby database was configured with:

8 GB undo tablespace
900-second undo retention

Nightly ETL jobs on primary generated massive redo volumes between 1 AM and 3 AM.

At the same time, reporting teams executed large BI reports that ran for nearly two hours.

During peak windows, reports failed repeatedly with:

ORA-01555: snapshot too old

Investigation revealed that redo apply activity recycled undo much faster than reporting queries could complete.

The DBA team implemented the following changes:

Increased undo retention to four hours
Expanded undo tablespace from 8 GB to 64 GB
Tuned reporting SQL
Rescheduled ETL overlap

After these adjustments:

ORA-01555 incidents disappeared
Reporting stabilized
Standby lag remained acceptable

Key Takeaways

ORA-01555 on standby databases is not unusual in Active Data Guard environments.

Even though users cannot modify data directly on standby, redo apply continuously changes blocks internally, and Oracle still relies on undo for consistent reads.

The most common causes include:

Long-running queries
Heavy redo apply
Insufficient undo retention
Small undo tablespaces

The most effective solutions are:

Increase undo retention
Expand undo tablespace
Tune reporting SQL
Reduce overlap between heavy redo generation and reporting workloads

Understanding how Oracle maintains read consistency during redo apply is essential for designing stable and scalable standby reporting environments.

When properly sized and monitored, Active Data Guard can support large reporting workloads without ORA-01555 interruptions.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Oracle RAC Node Eviction – Full Checklist. and Troubleshooting

noreply@blogger.com (Himanshu) — Wed, 6 May 2026 20:30:00 +0530

How to Find Why CRS/CSSD Rebooted a Server in Oracle RAC – Complete Checklist

Oracle RAC node eviction is one of the most critical issues in clustered database environments. When a node is evicted, Oracle Clusterware (CRS/CSSD) removes the node from the cluster to prevent split-brain conditions and ensure data consistency. In many cases, this eviction results in an automatic server reboot.

This complete Oracle RAC node eviction troubleshooting guide explains how to identify whether CRS rebooted the server, how to locate the exact cause, and how to fix it using a step-by-step checklist. This guide includes the best commands, logs, and root cause analysis techniques used by RAC administrators.

What is Oracle RAC Node Eviction?

Oracle RAC node eviction occurs when Oracle Clusterware detects a node is no longer stable or reliable for cluster participation. The node is removed forcibly, usually due to:

Interconnect heartbeat loss
Voting disk inaccessibility
Storage latency or multipath failure
OS CPU hang or kernel lockup
Time drift between nodes (NTP/Chrony failure)
Hardware issues (NIC, memory, CPU)

Eviction is triggered mainly by CSSD (Cluster Synchronization Services Daemon).

Does CRS Really Reboot the Server?

This is a common question.

CRS does not reboot the server randomly.

Instead, Oracle Clusterware can:

Evict the node
Trigger OS watchdog/hangcheck
Stop CRS stack
Force restart of cluster services
Initiate node reboot in extreme conditions

The OS reboot can happen because:

watchdog mechanism triggers reboot
hangcheck timer triggers reboot
node becomes unresponsive and system resets

Step 1: Confirm If the Server Rebooted (OS Level Proof)

The first step in Oracle RAC reboot troubleshooting is confirming whether the operating system restarted.

1.1 Check current uptime


uptime

1.2 Check last reboot time


who -b

1.3 Check reboot/shutdown history


last -x | head -50

What to look for:

reboot system boot
shutdown system down
crash

If reboot time matches eviction time, OS reboot occurred.

Step 2: Confirm CRS Stack Restart (CRS Restart vs OS Restart)

Sometimes the OS stays up but CRS restarts.

2.1 Check CRS stack status


crsctl check crs

Expected output includes:

OHASD online
CSSD online
CRSD online
EVMD online

2.2 Check cluster status on all nodes


crsctl check cluster -all

Step 3: Identify the Exact Node Eviction Reason (Most Important Log)

The #1 log file for Oracle RAC node eviction analysis is:

ocssd.log

Location:


$GRID_HOME/log/<hostname>/cssd/ocssd.log

3.1 View recent eviction details


tail -500 $GRID_HOME/log/$(hostname)/cssd/ocssd.log

3.2 Search for eviction messages


grep -i "evict" $GRID_HOME/log/$(hostname)/cssd/ocssd.log | tail -50

3.3 Search for common root cause patterns


grep -i "misscount\|voting\|heartbeat\|reconfig\|panic\|interface" \
$GRID_HOME/log/$(hostname)/cssd/ocssd.log | tail -100

Step 4: Check Grid Infrastructure Alert Log (CRS Alert Log)

The CRS alert log gives high-level cluster failure information.

Location:


$GRID_HOME/log/<hostname>/alert<hostname>.log

4.1 View recent log section


tail -400 $GRID_HOME/log/$(hostname)/alert$(hostname).log

4.2 Search for reboot and eviction keywords


grep -i "evict\|reboot\|cssd\|shutdown\|fatal\|restart" \
$GRID_HOME/log/$(hostname)/alert$(hostname).log | tail -100

Step 5: Check OHASD Log (Why CRS Restarted)

OHASD controls the entire cluster stack startup.

Location:


$GRID_HOME/log/<hostname>/ohasd/ohasd.log

5.1 View OHASD log


tail -300 $GRID_HOME/log/$(hostname)/ohasd/ohasd.log

5.2 Search for failure patterns


grep -i "restart\|terminate\|shutdown\|fail\|kill" \
$GRID_HOME/log/$(hostname)/ohasd/ohasd.log | tail -100

Step 6: Check CRSD Log (Resource Failure Analysis)

CRSD manages cluster resources (DB, VIP, listener, ASM).

Location:


$GRID_HOME/log/<hostname>/crsd/crsd.log

6.1 View CRSD log


tail -300 $GRID_HOME/log/$(hostname)/crsd/crsd.log

6.2 Search for eviction triggers


grep -i "evict\|fatal\|terminate\|restart\|offline" \
$GRID_HOME/log/$(hostname)/crsd/crsd.log | tail -100

Step 7: Check Voting Disk and OCR Health

Voting disk loss is the 2nd most common cause of node eviction.

7.1 Check voting disk status


crsctl query css votedisk

7.2 Check OCR health


ocrcheck

If OCR is unhealthy, node eviction can occur.

Step 8: Check ASM Diskgroup and Storage Status

If voting disk is stored in ASM, ASM disk problems can evict node.

8.1 Check ASM diskgroup status


asmcmd lsdg

8.2 Check ASM disks


asmcmd lsdsk

8.3 Check ASM alert log


tail -200 $GRID_HOME/diag/asm/+asm/+ASM*/trace/alert_+ASM*.log

Search for storage errors:


grep -i "error\|I/O\|offline\|fail\|timeout" \
$GRID_HOME/diag/asm/+asm/+ASM*/trace/alert_+ASM*.log | tail -50

Step 9: Check Multipath / SAN Issues (Path Flapping)

Storage instability causes intermittent voting disk access loss.

9.1 Check multipath status


multipath -ll

9.2 Check SAN timeout errors


dmesg -T | egrep -i "scsi|timeout|abort|reset|I/O error"

9.3 Check OS messages log


egrep -i "path.*down|path.*up|scsi|timeout|abort|reset" \
/var/log/messages | tail -200

Step 10: Check RAC Interconnect (Private Network Failure)

Interconnect issues are the most frequent root cause of eviction.

10.1 Identify interconnect interface


oifcfg getif

10.2 Check interface health and packet drops


ip -s link show <interface>

10.3 Check bonding (if configured)


cat /proc/net/bonding/bond0

10.4 Ping test between RAC nodes (private IP)


ping -I <private_interface> <other_node_private_ip>

Packet loss indicates network issue.

Step 11: MTU Mismatch Check (Very Common Hidden Issue)

MTU mismatch causes intermittent cluster heartbeat failures.

11.1 Check MTU


ip link show | grep mtu

Ensure all nodes + switches have same MTU.

Step 12: Check CPU Hang / OS Lockup / Soft Lockup Errors

If CPU is overloaded, heartbeat delays occur.

12.1 Check kernel logs


dmesg -T | tail -200

12.2 Search for hung tasks


dmesg -T | egrep -i "soft lockup|hard lockup|blocked for more than"

12.3 Check for OOM killer


dmesg -T | grep -i oom

Step 13: Check for Watchdog or Hangcheck Timer Reboot

Some RAC setups reboot node automatically if hung.

13.1 Check hangcheck module


lsmod | grep -i hangcheck

13.2 Check watchdog errors


grep -i watchdog /var/log/messages | tail -100

If you see watchdog triggers, OS reboot was forced.

Step 14: Check Time Drift / NTP / Chrony Issues (Critical)

Time drift can break RAC heartbeat synchronization.

14.1 Check chrony status


timedatectl status
chronyc tracking
chronyc sources -v

14.2 Compare node times


date
ssh <othernode> date

If time difference is > 1 second, fix NTP.

14.3 Check CTSSD log


tail -200 $GRID_HOME/log/$(hostname)/ctssd/ctssd.log

Search:


grep -i "drift\|clock\|time" \
$GRID_HOME/log/$(hostname)/ctssd/ctssd.log | tail -50

Step 15: Check Hardware Errors (ECC/MCE)

Hardware faults can cause sudden eviction and reboot.


dmesg -T | egrep -i "mce|ecc|hardware error|fatal"
journalctl -k | egrep -i "mce|ecc|hardware error"

If errors exist, escalate to hardware vendor.

Step 16: Check OS Journal Logs for Shutdown Events

If system uses systemd:

16.1 List boots


journalctl --list-boots

16.2 Check previous boot logs


journalctl -b -1 | tail -200

Search:


journalctl -b -1 | grep -i "reboot\|shutdown\|panic\|watchdog\|hangcheck"

Step 17: Confirm If CRS Triggered the Reboot (Direct Proof)

The best confirmation is finding these in ocssd.log:

evicting node
initiating eviction
misscount exceeded
voting file inaccessible
reconfiguration started
clssnmvDHBValidateNCopy

Search all logs:


grep -Rni "evict" $GRID_HOME/log/$(hostname) | tail -100
grep -Rni "reboot" $GRID_HOME/log/$(hostname) | tail -100

If eviction exists in ocssd.log and reboot exists in OS logs, CRS eviction caused reboot.

Step 18: Single Command Script to Collect All Evidence

Run immediately after node comes back:


echo "===== BASIC INFO ====="
date
hostname
uptime
who -b
last -x | head -30

echo "===== CRS STATUS ====="
crsctl check crs
crsctl check cluster -all
crsctl stat res -t
crsctl query css votedisk
ocrcheck

echo "===== GI ALERT LOG ====="
tail -300 $GRID_HOME/log/$(hostname)/alert$(hostname).log

echo "===== CSSD LOG ====="
tail -500 $GRID_HOME/log/$(hostname)/cssd/ocssd.log

echo "===== OHASD LOG ====="
tail -300 $GRID_HOME/log/$(hostname)/ohasd/ohasd.log

echo "===== CRSD LOG ====="
tail -300 $GRID_HOME/log/$(hostname)/crsd/crsd.log

echo "===== CTSSD LOG ====="
tail -200 $GRID_HOME/log/$(hostname)/ctssd/ctssd.log

echo "===== OS LOGS ====="
dmesg -T | tail -200
tail -200 /var/log/messages

echo "===== TIME SYNC ====="
timedatectl status
chronyc tracking
chronyc sources -v

echo "===== NETWORK ====="
oifcfg getif
ip link show
ip -s link

echo "===== MULTIPATH ====="
multipath -ll

Step 19: Root Cause Mapping (Fast Troubleshooting Table)

If ocssd.log shows misscount exceeded

Root cause: Interconnect latency OR CPU starvation
Fix: Check NIC drops, switch issues, MTU mismatch, CPU load

If ocssd.log shows voting file not accessible

Root cause: Storage/SAN/multipath issue
Fix: Check multipath, SAN latency, ASM disk offline events

If OS log shows watchdog reset

Root cause: OS hang/hardware freeze
Fix: Kernel tuning, firmware update, CPU/memory check

If CTSSD shows time drift

Root cause: NTP/Chrony problem
Fix: Fix chrony config and synchronize time across nodes

If /var/log/messages shows MCE/ECC

Root cause: Hardware failure
Fix: Replace memory/CPU/NIC and check ILO/IDRAC logs

Step 20: Best Practices to Prevent Oracle RAC Node Eviction

To prevent high-frequency evictions:

Recommended RAC Hardening Checklist

Use redundant private interconnect
Use bonding active-backup for private network
Keep MTU consistent across all nodes and switches
Ensure voting disks have redundant storage paths
Configure multipath correctly with proper timeouts
Ensure chrony/ntp is stable across all nodes
Keep GI and DB patched with latest RU
Monitor network latency and packet loss
Monitor SAN response times and path flapping

How to Prove CRS Rebooted the Node

CRS triggered reboot if ALL are true:

OS reboot timestamp matches incident time (last -x)
ocssd.log contains eviction messages (evicting node)
alert log shows CSSD heartbeat/voting failure
OS log shows watchdog/hangcheck reboot or forced restart

If ocssd.log has eviction but OS reboot is absent → CRS evicted but OS stayed up (only stack restart).

If you paste the output of this command, the exact root cause can be identified quickly:


grep -i "evict\|misscount\|voting\|heartbeat\|reconfig" \
$GRID_HOME/log/$(hostname)/cssd/ocssd.log | tail -80

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment