Troubleshooting ORA-01034 and ORA-27101 Errors in Oracle Database Connections

Introduction

When working with Oracle databases, encountering errors such as ORA-01034: ORACLE not available and ORA-27101: shared memory realm does not exist can be frustrating. These errors typically indicate issues related to the database instance’s availability or configuration settings. This tutorial will guide you through understanding these errors and provide step-by-step solutions to resolve them.

Understanding the Errors

ORA-01034: Oracle Not Available

This error suggests that the Oracle database instance is not running or cannot be accessed at the time of the connection attempt. It often occurs when trying to connect to a service name that is either not registered with the listener or if the database itself is shut down.

ORA-27101: Shared Memory Realm Does Not Exist

This error points to issues related to Oracle’s internal shared memory, which might be due to incorrect initialization parameters or environment settings such as ORACLE_HOME and ORACLE_SID.

Common Causes

1. Database Instance Not Running: The database instance may not be started.
2. Incorrect Listener Configuration: The listener configuration file (listener.ora) may have incorrect service names or addresses.
3. Environment Variables Misconfigured: Environment variables like ORACLE_HOME and ORACLE_SID might be set incorrectly.

Step-by-Step Solutions

1. Verify Database Instance Status

First, ensure that the Oracle database instance is running. You can do this by logging into the server where the database resides and using SQL*Plus:

sqlplus / as sysdba

Once logged in, check the status of the instance:

SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

If the status is not OPEN, proceed to start the instance.

2. Start the Database Instance

To start the database, use the following commands in SQL*Plus as a sysdba:

SHUTDOWN IMMEDIATE;
STARTUP;

Alternatively, if you need more control over the startup process (e.g., starting without mounting), use:

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

3. Verify Listener Configuration

Check your listener.ora file to ensure that it correctly references the service names and addresses. Here is an example configuration:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dms)
      (SERVICE_NAME = dms)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC =
     (SID_NAME = orcl)
     (SERVICE_NAME = orcl)
     (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    )
  )

Ensure that the SERVICE_NAME for each SID matches what is expected by clients.

4. Check Environment Variables

On Unix/Linux systems, verify that ORACLE_HOME and ORACLE_SID are set correctly:

echo $ORACLE_HOME
echo $ORACLE_SID

If they are incorrect, set them using:

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl

On Windows, check these variables through the System Properties dialog under Environment Variables.

5. Start the Listener

Ensure that the Oracle listener is running by executing:

lsnrctl start

If it was stopped or not started at all, this command will initiate the listener service.

Conclusion

By following these steps, you should be able to resolve ORA-01034 and ORA-27101 errors effectively. Ensuring that your database instance is running, verifying configuration files, and setting environment variables correctly are crucial steps in maintaining a healthy Oracle database environment. If issues persist, consider consulting with a DBA for further assistance.