本文详细介绍了在Windows客户端通过Oracle11gclient连接AIX双机热备中的一台Oracle服务器时遇到ORA-01034错误的问题，并提供了详细的解决方案，包括检查tnsnames配置和服务名称等。
一、问题描述：
采用windows机器上的Oracle11g client连接AIX双机热备里其中一台oracle服务器，sqlplus报错：
ORA -01034: ORACLE not available
ORA-27101:shared memory realm does not exist :
其实这个是orale的错误，只是在使用sde直连oracle库升级geodatabade的过程中遇到了，才记录到这里。
二、信息记录：
1、Windows客户端tnsnames配置：
SDEClient =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.87.125.140)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sde)
2、10.87.125.140机器上Oracle服务器端
listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle_sde/oracle/products/11.1/db)
(PROGRAM = extproc)
(ENVS="EXTPROC_DLLS=/home/arcsde/sdeexe100/lib/libst_shapelib_64.so")
)
(SID_DESC =
(GLOBAL_NAME = sde.oracle.com)
(ORACLE_HOME = /oracle_sde/oracle/products/11.1/db)
(SID_NAME = sde)
)
)
tnsnames.ora:
SDE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TNMS_APP4_service)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SDE.ORACLE.COM)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PLSExtProc)
)
)
3、数据库服务器hosts文件：
# 2000:1:1:1:209:6bff:feee:2b7f ipv6sample # ipv6 name/address
127.0.0.1 loopback localhost # loopback (lo0) name/address
10.87.125.139 TNMS_APP4_P_boot TNMS-APP4-P
10.87.125.140 TNMS_APP4_S_boot TNMS-APP4-S
10.87.125.141 TNMS_APP4_service
4、Oracle监听状态：
$ lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 11-OCT-2012 15:12:38
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date 11-OCT-2012 03:41:21
Uptime 0 days 11 hr. 31 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /oracle_sde/oracle/products//11.1/db/network/admin/listener.ora
Listener Log File /oracle_sde/oracle/products/diag/tnslsnr/TNMS-APP4-S/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "sde" has 1 instance(s).
Instance "sde", status UNKNOWN, has 1 handler(s) for this service...
Service "sde.oracle.com" has 1 instance(s).
Instance "sde", status READY, has 1 handler(s) for this service...
Service "sdeXDB.oracle.com" has 1 instance(s).
Instance "sde", status READY, has 1 handler(s) for this service...
The command completed successfully
$ netstat -na|grep 1521 |more
tcp4 0 0 10.87.125.141.1521 10.87.125.142.64643 ESTABLISHED
tcp4 0 0 10.87.125.141.1521 10.87.125.142.64644 ESTABLISHED
tcp4 0 0 10.87.125.141.1521 10.87.125.142.64837 ESTABLISHED
tcp4 0 0 *.1521 *.* LISTEN
tcp4 0 0 10.87.125.140.1521 10.87.125.140.46356 ESTABLISHED
tcp4 0 0 10.87.125.140.46356 10.87.125.140.1521 ESTABLISHED
f1000e000f50f408 stream 0 0 f1000a06c48aec20 0 0 0 /tmp/
.oracle/sEXTPROC1521
三、原因分析及解决
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))表示监听本机所有IP地址，从上面的对1521端口的监听情况可以看到，双机热备的物理地址140和漂移地址141都被正确监听了。
在数据库服务器上，无论是oracle用户还是sde用户，均能用sqlplus sde/sde登陆，也能用sqlplus sde/sde@sde正常登陆（在tnsnames.ora里已经配好了sde这个net service name）
从上面的信息可以看出，oracle的监听是正常的，所以另外一个windows机器上的32位客户端连不上oracle服务器应该还是tnsnames的问题。
对比数据库服务器上的tnsnames和监听，可以看到service name都用的是sde.oracle.com，而不是sde
sde.oracle.com 是配置的Globe_Name，所以我们在配置客户端tnsnames的时候，也应该用这个：
SDEClient =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.87.125.140)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sde.oracle.com)
)
)
所以既然数据库里启用了GLOBE_NAME，那就不能直接用SID了。
附：关于GLOBAL_NAME
一个是GLOBAL_NAME，一个是GLOBAL_NAMES参数，GLOBAL_NAME是全局数据库名，GLOBAL_NAMES参数设定是否启用全局数据库名。
GLOBAL_NAME的形式为:DB_NAME.DB_DOMAIN
修改global_name，只能用ALTER DATABASE RENAME GLOBAL_NAME TO <db_name.db_domain>命令进行修改，然后修改相应参数。
SQL> select * from global_name; --查看数据库的global_name
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> alter database rename global_name to orcl.robinson.com; --修改数据库的global_name
Database altered.
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.ROBINSON.COM
GLOBAL_NAMES参数通常对于分布式处理，Oracle 建议将该值设为TRUE，用于确保通连接到网络环境使用一致性的命名方式。
一般情况下假定全局数据库的名称为orcl.robinosn.com ，当GLOBAL_NAMES设置为true时，数据库执行调用时会核查链接的名字是否和远程
全局数据库名称一致，否则如果为false，可以任意定义数据库链接的名字。
假定远程数据库名称为orcl.robinosn.com ，参数global_names设为true,则必须使用下面的方式来调用
select * from scott.emp@orcl.robinson.com
如果global_names设为false,则可以使用定义的任意名字来调用
select * from scott.emp@orclalias
SQL> show parameter global_names --查看global_names参数的设置情况
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
关于GLOBAL_NAMES更多参考：Oracle Database Administrator’s Guide(Distributed Database Concepts)
扫一扫
672
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
