ORA-27101 Shared memory realm does not exist错误解决方案

一. 问题描述

在一次数据库灾难恢复过程中,数据库恢复到一个新的环境里,并且数据库已经成功启动了,可以在服务器上登录与访问。但客户端在访问时却报了ORA-27101的错误，无法通过定义数据库连接串远程连接到这个数据库。在数据库服务器上，定义一个访问本地数据库的连接串,使用这个连接串连接也报同样的错误。

错误的现象与现场如下：

$export ORACLE_SID=Port

本地可以使用管理员登录，数据库为open状态

[@more@]

$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 18 09:44:01 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

服务器与客户端的连接串定义如下：

$more $ORACLE_HOME/network/admin/tnsnames.ora

。。。。。。

port =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.192.215.77 )(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = port)

)

)

通过tnsping命令测试连接，服务器和客户端都是正常的，如下：

$ tnsping port

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.192.215.77)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = port)))

OK (90 msec)

通过sqlplus进行远程登录，报下面的错误：

$ sqlplus user1/pwd1@port

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

IBM AIX RISC System/6000 Error: 2: No such file or directory

Enter user-name:

二. 问题分析

首先检查数据库服务器的文件系统的操作权限，发现没有问题；

接着检查服务器的网络状况，也没有发现问题；

在ORACLE METALINK 网站上找到一遍文章,ID=122183.1,在该文中提到了listener.ora文件中,SID_NAME是大小写敏感的,如下:

Database Connections Fail With ORA-27101 Shared Memory Realm Does Not Exist [ID 122183.1]

For remote (TCP/listener) connections:

1. Review the listener.ora currently used to startup the listener and verify the ORACLE_HOME value is correct for all listed Oracle databases. If the ORACLE_HOME points to a different Oracle version then what was used when the database was created, then this error can occur.
2. Likewise, review the listener.ora currently used to startup the listener and verify the SID_NAME value is correct and has the right value (the SID_NAME is case sensitive).

检查数据库服务器的LISTENER.ORA文件,发现SID_NAME大小写书写错误了.

$more $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PORT)

(SID_NAME = PORT)

)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.192.215.77)(PORT =1522))

)

而数据库的实例名称为Port,通过查询系统进程的名称可以确认

$ps -ef|grep ora

。。。。。。

oracle 192612 1 0 Dec 09 - 16:12 ora_cjq0_Port

oracle 221436 1 0 Dec 09 - 13:55 ora_pmon_Port

oracle 286956 1 0 Dec 09 - 7:41 ora_mman_Port

oracle 299190 1 0 Dec 09 - 15:24 ora_mmon_Port

到此找到了问题的根源，原来是数据库监听配置文件的内容配置不当（SID_NAME没有注意大小写），导致了通过定义数据库连接串无法远程访问数据库的故障

三. 问题解决

修改监听器配置文件，内容修改如下：

$more $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = Port)

(SID_NAME = Port)

)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.192.215.77)(PORT =1522))

)

重新启动监听器，如下：

$lsnrctl start listener

在服务器与客户端进行数据库连接，可以成功登录了，如下：

$ sqlplus user1/pwd1@port

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 18 09:44:01 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

问题等到解决，笔者重新查阅了ORACLE 的HELP 文档，在network相关的章节，找不到SID_NAME 需要注意大小写的说明,只能在METALINK文档中可以查到相关的说明。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1046168/，如需转载，请注明出处，否则将追究法律责任。