DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01034 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01034: ORACLE Not Available — Causes, Fixes & Prevention

ORA-01034 is one of the most common Oracle errors DBAs encounter in production environments. It occurs when a client application or user attempts to connect to an Oracle database instance that is not currently running or is unavailable. Simply put, the database engine is down, and no connections can be established until it is brought back online.

Top 3 Causes

1. Oracle Instance Is Not Started

The most frequent cause is that the Oracle instance was never started, or the server was rebooted without an auto-start configuration in place.

-- Connect as SYSDBA from the server OS
-- $ sqlplus / as sysdba

-- Check current instance status
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS
FROM V$INSTANCE;

-- Start the instance
STARTUP;

-- Verify after startup
SELECT INSTANCE_NAME, STATUS
FROM V$INSTANCE;
Enter fullscreen mode Exit fullscreen mode

2. Instance Crashed or Was Abnormally Terminated

Hardware failures, OS-level OOM (Out of Memory) kills, or power outages can cause an Oracle instance to crash unexpectedly. In this case, Oracle's automatic Instance Recovery typically handles redo log application upon the next startup.

-- Check alert log location first
SELECT VALUE
FROM V$DIAG_INFO
WHERE NAME = 'Diag Trace';

-- Attempt normal startup (auto instance recovery will run)
STARTUP;

-- If startup fails, try restricted mode
STARTUP RESTRICT;

-- Perform manual recovery if needed
RECOVER DATABASE;

-- Disable restricted session after recovery
ALTER SYSTEM DISABLE RESTRICTED SESSION;
Enter fullscreen mode Exit fullscreen mode

3. Incorrect Environment Variables (ORACLE_SID / ORACLE_HOME)

When ORACLE_SID or ORACLE_HOME is not set correctly, the client points to a non-existent or wrong instance. This is especially common on servers hosting multiple Oracle versions or multiple instances.

-- After setting correct environment variables on Linux:
-- $ export ORACLE_SID=ORCL
-- $ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
-- $ export PATH=$ORACLE_HOME/bin:$PATH

-- Verify the connected instance
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS DB_NAME,
       SYS_CONTEXT('USERENV', 'INSTANCE_NAME') AS INSTANCE_NAME
FROM DUAL;

-- Check listener status (remote connections)
-- $ lsnrctl status
-- $ lsnrctl start
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary 

-- Step 1: Connect as SYSDBA locally
-- $ sqlplus / as sysdba

-- Step 2: Start the database
STARTUP;

-- Step 3: Confirm database is open
SELECT STATUS FROM V$INSTANCE;
-- Expected result: OPEN

-- Step 4: Check for recent errors in alert log
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-%'
  AND ORIGINATING_TIMESTAMP > SYSDATE - 1
ORDER BY ORIGINATING_TIMESTAMP DESC;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Configure Auto-Startup on Server Reboot
Set the auto-start flag to Y in /etc/oratab and register dbstart/dbshut scripts with systemd or your OS init system. On Windows, ensure the OracleService{SID} service is set to start automatically.

2. Implement Proactive Monitoring
Use Oracle Enterprise Manager, custom shell scripts, or third-party tools (Zabbix, Nagios) to continuously monitor instance availability. Set up alerts so the on-call DBA is notified immediately when the instance goes down — before users start reporting connection failures.

-- Simple health check query for monitoring scripts
SELECT INSTANCE_NAME,
       HOST_NAME,
       STATUS,
       TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_STARTUP
FROM V$INSTANCE;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01033 — Oracle initialization or shutdown in progress
  • ORA-12541 — TNS: no listener
  • ORA-12514 — TNS: listener does not know of requested service
  • ORA-00600 — Internal error (often precedes a crash leading to ORA-01034)
  • ORA-03113 — End-of-file on communication channel

📖 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)