ORA-01034 ORA-27101

Local Connections Works, Connecting Via Network To The Database Fails With ORA-01034 ORA-27101 [ID 375688.1]

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 10.2.0.4 - Release: 9.2 to 10.2
Information in this document applies to any platform.
Checked for relevance on 30-SEP-2011.

Symptoms

On a system with multiple IP addresses, when we connect with sqlplus locally on the server (i.e. "sqlplus user/password") everything is OK, but connecting through a TNS alias, either from the network or locally on the server, we get the the following errors:

sqlplus user/password@db_alias

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory 

Changes

May be triggered by:

• SID_LIST_<listener> changes in LISTENER.ORA
• Listener port/host reconfiguration
• Using (IP=FIRST) in LISTENER.ORA
• Database startup scripts changes
• Oracle user environment changes

Cause

Database is not dynamically registering with the listener and most likely the static setup in LISTENER.ORA is wrong in respect of ORACLE_HOME value for the desired database instance (SID_NAME).
OR

The SID_NAME specified in the listener.ora file or the SID value in the tnsnames.ora file are not set to the correct case. The SID_NAME and SID are case sensitive.

OR

The Listener has been configured with a hostname or address which points to an IP different than the one to which the system hostname points; because of this the listener will bind only to that particular IP (see Note 421305.1).
However, the database instance will attempt, by default, to register with the listener on the system hostname — but the listener cannot catch these connections in the above setup.

Solution

1. Fix the static database entries in LISTENER.ORA

Check that the SID_DESC statements in SID_LIST_<listener> in LISTENER.ORA contain correct values for the ORACLE_HOME, SID_NAME and GLOBAL_DBNAME entries. This also includes the correct case (UPPER or lower) for the SID_NAME value in the listener.ora or the SID value in the tnsnames.ora files.
Alternatively you may remove the SID_DESC entries for the databases in order to force the listener to use only dynamic instance registrations.

2. Activate dynamic database instance registration

Please verify the following:

1. Check that the listener is configured to bind on the system hostname (e.g. change the HOST statement in LISTENER.ORA). Database(s) will be able to dynamically register with the listener;
2. Make sure that the boot scripts will start the listener before the database startup.

If you want to leave the listener binding only to certain address(es)/port(s), then you may force the database to register with the listener on that particular address. In this case you will have to modify the "local_listener" database parameter to contain either a service name pointing to the listener, or an in-place complete address. The "local_listener" database parameter will let PMON know exactly on what IP address / port is the listener accepting connections.

To modify the local_listener parameter, either use the dbca utility or the Enterprise Manager interface. Otherwise execute the following SQL statement as SYSDBA:

ALTER SYSTEM SET local_listener='service_name_towards_listener' SCOPE=BOTH;

Replace the "service_name_towards_listener" value with an entry from TNSNAMES.ORA pointing to your listener host/port combination.