Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 数据库连接问题

109 积分	0 好友	9 主题

发消息

[SQL调优] 数据库连接问题

1^#

发表于 2012-12-14 21:55:59 | 查看: 4597| 回复: 11

oracle服务器端操作系统 AIX 6.1，oracle 10.2.0.1 64位，监听状态
$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 14-DEC-2012 21:28:23

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.33)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
Start Date             14-DEC-2012 21:16:17
Uptime                   0 days 0 hr. 12 min. 5 sec
Trace Level             off
Security                ON: Local OS Authentication
SNMP                   ON
Listener Parameter File /oracle/product/10.2.0/network/admin/listener.ora
Listener Log File       /oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.33)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl service

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 14-DEC-2012 21:29:00

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.33)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
   Handler(s):
   "DEDICATED" established:0 refused:0
      LOCAL SERVER
The command completed successfully
监听文件内容为
SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /oracle/product/10.2.0)
   (PROGRAM = extproc)
   )
)

LISTENER =
(DESCRIPTION_LIST =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.33)(PORT = 1521))
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
   )
)

oracle 客户端为win7 旗舰版，32位，tnsnames.ora文件内容如下

DB_192.168.11.201 =
(DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.201)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SID = db)
   (SERVER = DEDICATED)
   )
)

DB_LOCAL =
(DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.33)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SID = db_local)
   (SERVER = DEDICATED)
   )
)

  DB_192.168.11.252 =
(DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.252)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SID = db)
   (SERVER = DEDICATED)
   )
)

使用tnsping命令检测正常
C:\Users\lich>tnsping db_local

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 14-12月-
2012 21:46:50

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

已使用的参数文件:
E:\oracleclient\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.11.33)(PORT = 1521))) (CONNECT_DATA = (SID = db_local) (SERVE
R = DEDICATED)))
OK (20 毫秒)
但是
使用sqlplus连接时提示不能识别的SID
C:\Users\lich>sqlplus sys/sys@db_local

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 12月 14 21:51:47 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12505: TNS: 监听程序当前无法识别连接描述符中所给出的 SID

请输入用户名:

请问这个是怎么回事?

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2012-12-15 13:41:40

你的listener中未注册任何服务，静态注册也没有设置任何服务，远程连接遇到ORA-12505 不是再正常不过的事情吗？

回复只看该作者道具举报

wind

3^#

发表于 2012-12-15 14:38:18

用动态侦听吧

把listener.ora文件删除了

lsnrctl reload

等上几分钟，再试一试

回复只看该作者道具举报

chunchun2012

4^#

发表于 2012-12-15 14:42:48

本帖最后由 chunchun2012 于 2012-12-15 14:44 编辑

Liu Maclean(刘相兵发表于 2012-12-15 13:41
你的listener中未注册任何服务，静态注册也没有设置任何服务，远程连接遇到ORA-12505 不是再正常不过的 ...

谢谢刘大指点，现在将服务器端监听文件修改为
SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /oracle/product/10.2.0)
   (PROGRAM = extproc)
   )
   (SID_DESC =
   (GLOBAL_DBNAME = db)
   (ORACLE_HOME = /oracle/product/10.2.0)
   (SID_NAME = db_local)
   )
)

LISTENER =
(DESCRIPTION_LIST =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.33)(PORT = 1521))
   )
)
说明：数据库 DB_name为db，instance_name为db_local
重新启动监听，查看监听状态
$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 15-DEC-2012 14:36:39

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.33)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
Start Date             15-DEC-2012 14:32:50
Uptime                   0 days 0 hr. 3 min. 48 sec
Trace Level             off
Security                ON: Local OS Authentication
SNMP                   ON
Listener Parameter File /oracle/product/10.2.0/network/admin/listener.ora
Listener Log File       /oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.33)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "db" has 1 instance(s).
Instance "db_local", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
客户端tnsnames.ora文件为
DB_LOCAL =
(DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.33)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SID = db_local)
   (SERVER = DEDICATED)
   )
)

再次在客户端连接，提示权限不够
C:\Users\lich>sqlplus sys/sys@hbmisdb_local as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 12月 15 14:36:32 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges
请问我是不是还有哪个地方设置不正确?

回复只看该作者道具举报

wind

5^#

发表于 2012-12-15 14:46:20

你的密码文件做了没有？

回复只看该作者道具举报

wind

6^#

发表于 2012-12-15 14:46:51

试一试system用户

回复只看该作者道具举报

chunchun2012

7^#

发表于 2012-12-15 14:50:38

wind 发表于 2012-12-15 14:38
用动态侦听吧

把listener.ora文件删除了

按你说的这个方法，我把服务器端监听停了后，把listener.ora重命名，然后lsnrctl sart,lsnrctl reload,运行lsnrctl status，显示内容如下
$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 15-DEC-2012 14:44:01

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
Start Date             15-DEC-2012 14:43:43
Uptime                   0 days 0 hr. 0 min. 17 sec
Trace Level             off
Security                ON: Local OS Authentication
SNMP                   ON
Listener Log File       /oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NS-43_boot)(PORT=1521)))
Services Summary...
Service "db" has 1 instance(s).
Instance "db_local", status READY, has 1 handler(s) for this service...
Service "dbXDB" has 1 instance(s).
Instance "db_local", status READY, has 1 handler(s) for this service...
Service "db_XPT" has 1 instance(s).
Instance "db_local", status READY, has 1 handler(s) for this service...
The command completed successfully
此时在客户端连接时，也提示权限不够，

回复只看该作者道具举报