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文件中可以查到相關的說明。