监听独立于数据库服务器的配置,解决ORA-12520及ORA-12545错误

一、环境:


1、linux主机ocm(192.168.217.130)作为数据库
sys@OCM> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

2、linux主机ocp(192.168.217.133)作为独立远程监听
sys@OCP> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

3、windows主机(192.168.217.2)作为客户端
 windows 7


二、在ocm主机(192.168.217.130)上操作:


1、配tnsnames.ora


LISTENER_OCP =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.133)(PORT = 1521))
)


2、配参数
sys@OCM> alter system set remote_listener=LISTENER_OCP;


3、查监听
[oracle@ocm ~]$ lsnrctl status


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-MAY-2013 21:13:28


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


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-MAY-2013 20:34:42
Uptime 0 days 0 hr. 38 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ocm/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm.example.com)(PORT=1521)))
Services Summary...
Service "ocm" has 1 instance(s).
Instance "ocm", status READY, has 1 handler(s) for this service...
Service "ocmXDB" has 1 instance(s).
Instance "ocm", status READY, has 1 handler(s) for this service...
The command completed successfully



三、在ocp主机(192.168.217.133)上操作:


1、查监听

[oracle@ocp ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2013 20:49:22

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 29-MAY-2013 20:44:12
Uptime 0 days 0 hr. 5 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ocp/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocp)(PORT=1521)))
Services Summary...
Service "ocm" has 1 instance(s).
Instance "ocm", status READY, has 1 handler(s) for this service...
Service "ocmXDB" has 1 instance(s).
Instance "ocm", status READY, has 1 handler(s) for this service...
Service "ocp" has 1 instance(s).
Instance "ocp", status READY, has 1 handler(s) for this service...
The command completed successfully

2、配tnsnames.ora
remote_130 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.133)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ocm)
)
)

3.连接测试
[oracle@ocm admin]$ sqlplus hr/hr@remote_130


SQL*Plus: Release 11.2.0.3.0 Production on Wed May 29 23:07:19 2013


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

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

Enter user-name:

4、解决方法:(192.168.217.133)主机上一定要配一下
vi /etc/hosts
192.168.217.130 ocm
192.168.217.133 ocp



四、从windows客户端连接


remote_130 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.133)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ocm)
)
)


C:\Users\Administrator>sqlplus hr/hr@remote_130


SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 5月 29 21:02:34 2013


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


ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败


请输入用户名:


解决方法在C:\Windows\System32\drivers\etc
编辑hosts添加如下:
192.168.217.130 ocm
192.168.217.133 ocp



五、最后总结配置的整过程和解决:
1、配tnsnames.ora


LISTENER_OCP =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.133)(PORT = 1521))
)


2、配参数
sys@OCM> alter system set remote_listener=LISTENER_OCP;


3、查服务
[oracle@ocm ~]$ lsnrctl services
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-MAY-2013 23:28:55


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


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "ocm" has 1 instance(s).
Instance "ocm", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ocmXDB" has 1 instance(s).
Instance "ocm", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: ocm, pid: 12608>
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm.example.com)(PORT=46963))
Service "ocp" has 1 instance(s).
Instance "ocp", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:5 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=ocp)(PORT=1521))
The command completed successfully


(ADDRESS=(PROTOCOL=TCP)(HOST=ocp)(PORT=1521))从这里发现问题的所在。。。。明白了监听是用主机名,这里必须配/etc/hosts或DNS


4、在windows客户端配tnsnames.ora,或在linux客户端配tnsnames.ora

remote_130 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.133)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ocm)
)
)


5、连接报错
windows中报错
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败


请输入用户名:


linux中报错
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server


Enter user-name:


6、最后解决办法:
(1)windows
C:\Windows\System32\drivers\etc
编辑hosts,添加如下:
192.168.217.130 ocm
192.168.217.133 ocp

(2)linux
vi /etc/hosts
192.168.217.130 ocm
192.168.217.133 ocp

(3)要是还报错,配参数listener_local,嘿嘿,一般是不需要配的,我这里就没有配置,不过配上也没关系。

alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.217.130)(PORT=1521))';


(4)如果以上三步还报错,那可能是bug.我这个版本还没有测出来。。。

操作完毕,有其它的不足的地方,请大家指正,谢谢!


**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe

QQ: 252803295

Email: oracledba_cn@hotmail.com

Blog: http://blog.csdn.net/guoyJoe

ITPUB: http://www.itpub.net/space-uid-28460966.html

OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!

答案在:http://blog.csdn.net/guoyjoe/article/details/8624392

DSI&Core Search Ⅰ群:127149411(加群验证信息回复:from guoyJoe@csdn)

DSI&Core Search Ⅱ 群:177089463 (加群验证信息回复:from guoyJoe@csdn)

DSI&Core Search Ⅲ群 284596437(加群验证信息回复:from guoyJoe@csdn)

DSI&Core Search Ⅳ 群192136702(加群验证信息回复:from guoyJoe@csdn)

DSI&Core Search Ⅴ群:285030382(加群验证信息回复:from guoyJoe@csdn)





  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值