【SDE错误收集】Oracle客户端sqlplus连接服务器端报错ORA -01034和ORA-27101

最新推荐文章于 2021-04-06 21:18:09 发布

rocky_lx

最新推荐文章于 2021-04-06 21:18:09 发布

阅读量1.1k

点赞数 1

分类专栏： Geodatabase 文章标签： oracle 服务器数据库 service 数据库服务器 database

本文链接：https://blog.csdn.net/rocky_lx/article/details/8061436

版权

Geodatabase 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、问题描述：

采用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

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)

rocky_lx

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【SDE错误收集】Oracle客户端sqlplus连接服务器端报错ORA -01034和ORA-27101

一、问题描述：采用windows机器上的Oracle11g client连接AIX双机热备里其中一台oracle服务器，sqlplus报错：ORA -01034:ORACLE not availableORA-27101:shared memory realm does not exist : 其实这个是orale的错误，只是在使用sde直连oracle库升级geodataba
复制链接

扫一扫