DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01090 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01090: Shutdown in Progress - Connection Is Not Permitted

ORA-01090 is an Oracle error that occurs when a client attempts to establish a new database connection while the Oracle instance is in the process of shutting down. Even though the listener may still be running and accepting incoming requests, the Oracle instance itself refuses new session creation during this window. This error is especially common in environments with aggressive connection pool retry logic, automated monitoring tools, or RAC configurations without proper service relocation.

Top 3 Causes and Fixes

Cause 1: Application Reconnection During Planned Shutdown

Connection pools often detect stale connections and immediately attempt to reconnect, hitting the database mid-shutdown.

Fix: Drain the connection pool before initiating shutdown and verify active sessions first.

-- Check active user sessions before shutdown
SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE
FROM V$SESSION
WHERE USERNAME IS NOT NULL
  AND STATUS = 'ACTIVE'
ORDER BY LOGON_TIME;

-- Generate kill session statements for cleanup
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE;'
FROM V$SESSION
WHERE USERNAME IS NOT NULL
  AND USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
  AND STATUS = 'ACTIVE';

-- Then safely shut down
SHUTDOWN IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Connecting Too Early After SHUTDOWN ABORT

After a SHUTDOWN ABORT, Oracle performs Instance Recovery on the next startup. Attempting to connect before recovery completes can trigger ORA-01090.

Fix: Monitor instance recovery progress before allowing application connections.

-- Monitor Instance Recovery progress after STARTUP
SELECT RECOVERY_ESTIMATED_IOS,
       ACTUAL_IOS_DONE,
       ESTIMATED_IOS,
       RECOVERY_ESTIMATED_IOS - ACTUAL_IOS_DONE AS REMAINING
FROM V$INSTANCE_RECOVERY;

-- Confirm database is fully OPEN before reconnecting
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS
FROM V$INSTANCE;

-- Check undo segments are all ONLINE
SELECT SEGMENT_NAME, STATUS
FROM DBA_ROLLBACK_SEGS
WHERE STATUS != 'ONLINE';
Enter fullscreen mode Exit fullscreen mode

Wait until STATUS = 'OPEN' and DATABASE_STATUS = 'ACTIVE' before allowing new connections.

Cause 3: RAC Node Shutdown Without Service Relocation

In a RAC environment, shutting down a node without first relocating its services causes the load balancer to continue routing new connections to the shutting-down node.

Fix: Always relocate services before shutting down a RAC node.

-- Check which services are running on the target node
SELECT SERVICE_NAME, INST_ID, STATUS
FROM GV$SESSION
WHERE INST_ID = 1  -- Node to be shut down
GROUP BY SERVICE_NAME, INST_ID, STATUS;

-- Verify no active user sessions remain on the node
SELECT COUNT(*) AS REMAINING_SESSIONS
FROM GV$SESSION
WHERE INST_ID = 1
  AND USERNAME IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode 
# Relocate service at OS level using SRVCTL before shutdown
srvctl relocate service -d mydb -s myservice -i mydb1 -t mydb2

# Then shut down the node safely
srvctl stop instance -d mydb -i mydb1
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary 

-- Step 1: Connect as SYSDBA
CONNECT / AS SYSDBA

-- Step 2: Check current instance state
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

-- Step 3: If shutdown is complete, restart the database
STARTUP;

-- Step 4: Validate database is open and accepting connections
SELECT STATUS FROM V$INSTANCE WHERE STATUS = 'OPEN';

-- Step 5: Use lightweight validation query in connection pools
SELECT 1 FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always drain connection pools before maintenance.
Configure your middleware or WAS with a validation query (SELECT 1 FROM DUAL) and a graceful drain period before any scheduled shutdown. Use Oracle's SHUTDOWN IMMEDIATE rather than SHUTDOWN ABORT whenever possible to give sessions time to complete.

2. Implement exponential backoff in reconnection logic.
Rather than immediately retrying on connection failure, configure your connection pool (HikariCP, UCP, etc.) with exponential backoff. This prevents log flooding and reduces unnecessary load on a recovering database instance.

Related Errors

Error Code Description
ORA-01089 Immediate shutdown in progress — existing session SQL execution blocked
ORA-01092 Oracle instance terminated — forced disconnection after ABORT
ORA-12528 TNS listener blocking all new connections at the listener level
ORA-12514 TNS service not registered — often seen after instance shutdown

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)