Oracle RAC Database Shutdown Hang Due to LogMiner Sessions

Resolving Oracle Database Shutdown Hang Due to LogMiner Sessions (GoldenGate Microservices)

Introduction:In Oracle environments utilizing GoldenGate Microservices, it’s not uncommon to encounter situations where the database shutdown process hangs due to active LogMiner sessions. In this step-by-step blog post, we’ll walk through how to identify, troubleshoot, and resolve this issue. This guide is tailored for DBAs and architects working with Oracle RAC or standalone databases, especially in replication-enabled environments.

Problem Statement:Executing a database shutdown using srvctl stop database hangs indefinitely. Alert logs indicate active LogMiner operations, and yet, no Integrated Capture (Streams/XStream) is configured.

Symptoms Observed:

1. Command srvctl stop database -d <DB_UNIQUE_NAME> hangs.
2. Alert log shows continuous LogMiner activity:
   LOGMINER: Begin mining logfile for session 1 thread 3 sequence 123, +DATA/DB_NAME/ONLINELOG/group_12.abc123.987654321
3. A log switch does not end the mining session; instead, LogMiner attaches to the next log sequence.
4. However, querying active sessions shows LogMiner readers tied to GoldenGate:
   SELECT SID, SERIAL#, PROGRAM, MODULE, EVENT FROM V$SESSION WHERE EVENT LIKE '%logminer%';

Example output:
SID SERIAL# MODULE EVENT ---- -------- ----------- ------------------------------ 1234 56789 GoldenGate LogMiner reader: redo (idle) 1256 54321 GoldenGate LogMiner reader: redo (idle)

Root Cause:GoldenGate Microservices Extract was actively using LogMiner to mine redo logs. Oracle’s shutdown process waits for these sessions to exit, causing the srvctl stop database command to hang until they are terminated or naturally exit.

Resolution Steps:

Step 1: Identify LogMiner Sessions

Run the following query to identify LogMiner consumers:

SELECT SID, SERIAL#, USERNAME, PROGRAM, MODULE, STATUS, EVENT
FROM V$SESSION
WHERE EVENT LIKE '%logminer%' OR MODULE LIKE '%GoldenGate%';

Step 2: Confirm GoldenGate Extract is Active

Check the GoldenGate Microservices Admin Server dashboard or use AdminClient CLI:

/adminclient
> INFO EXTRACT *;

Step 3: Gracefully Stop GoldenGate Extract

Stop the extract to cleanly disconnect LogMiner from the database:

STOP EXTRACT <extract_name>;

Wait a few moments and confirm the LogMiner sessions disappear from V$SESSION.

Step 4: Retry Database Shutdown

Now re-issue the database stop command:

srvctl stop database -d <DB_UNIQUE_NAME>

This time, the shutdown should proceed successfully.

Step 5: (Optional) Force LogMiner Session Termination

If necessary (and approved), force kill sessions:

ALTER SYSTEM KILL SESSION '1234,56789' IMMEDIATE;
ALTER SYSTEM KILL SESSION '1256,54321' IMMEDIATE;

Post-Resolution Verification:

• Ensure the database is completely stopped.
• Start the database:

srvctl start database -d <DB_UNIQUE_NAME>

• Resume the extract:

START EXTRACT <extract_name>;

Best Practices Moving Forward:

1. Always coordinate with replication or integration teams before shutdowns.
2. Consider scripting GoldenGate extract stop/start as part of your patching or shutdown procedures.
3. Monitor LogMiner sessions regularly in environments with replication.

Conclusion:Hanging shutdowns due to LogMiner readers can disrupt maintenance operations, especially when GoldenGate Microservices are in use. With the above steps, Oracle DBAs can safely and quickly resolve such hangs, ensuring minimal downtime. Bookmark this guide to reference during patching or change windows.

Tags:Oracle DBA, GoldenGate Microservices, LogMiner, Oracle shutdown hang, srvctl, replication, Oracle RAC, Oracle troubleshooting, Oracle redo log mining, database shutdown issues

Author : Rohit Mishri