今天是个阴天，老天想下不下的，昏昏沉沉的总想睡觉…… 额……废话不多说了。 今天中午想做一个catalog库，我就在虚拟机上装了oracle11g，本想不同于target数据库， 所以当时配置监听时就没有用默认的端口号1521，和平常一样，我也懒得手工创建监听， netca
今天是个阴天，老天想下不下的，昏昏沉沉的总想睡觉……
额……废话不多说了。
今天中午想做一个catalog库，我就在虚拟机上装了oracle11g，本想不同于target数据库，
所以当时配置监听时就没有用默认的端口号1521，和平常一样，我也懒得手工创建监听，
netca创建监听，监听名默认名listener，端口号改为1526；
接着就是启动数据库，注册 alter system register，启动监听，[oracle@jibo admin]$ lsnrctl start
查看监听状态：
[oracle@jibo ~]$ lsnrctl status
lsnrctl for linux: version 11.2.0.4.0 - production on 23-sep-2014 21:25:12
copyright (c) 1991, 2013, oracle. all rights reserved.
connecting to (description=(address=(protocol=tcp)(host=jibo)(port=1526)))
status of the listener
------------------------
alias listener
version tnslsnr for linux: version 11.2.0.4.0 - production
start date 23-sep-2014 15:59:46
uptime 0 days 5 hr. 25 min. 26 sec
trace level off
security on: local os authentication
snmp off
listener parameter file /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
listener log file /u01/app/oracle/diag/tnslsnr/jibo/listener/alert/log.xml
listening endpoints summary...
(description=(address=(protocol=tcp)(host=jibo)(port=1526)))
(description=(address=(protocol=ipc)(key=extproc1526)))
services summary...
service prod has 1 instance(s).
instance prod, status ready, has 1 handler(s) for this service...
service prodxdb has 1 instance(s).
instance prod, status ready, has 1 handler(s) for this service...
the command completed successfully
嗯……很好，一切正常；
我就拷贝了一份catalog库的tnsnames.ora到target数据库的tnsnames.ora中，好让target数据库远程访问catalog库；
先测试一下是否可以连通：
sqlplus hr/hr@prod
(catalog库的服务名我设置为prod，target库服务名我设置为orcl )
结果…… 呃i 没通，报错……
我就纳闷了……安装数据库什么的都设置好好的啊，不记得有哪错了……
我想了一下，是catalog库的问题还是target库的问题呢？
不用想，target库一直用的好好的，监听什么的都没有问题，
（我target 主机名demon ip192.168.1.8/ catalog库主机名 jibo ip192.168.1.4）
检验一下target吧 ，tnsping 192.168.1.4
[oracle@demon admin]$ tnsping 192.168.1.4
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 00:43:42
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used hostname adapter to resolve the alias
attempting to contact (description=(connect_data=(service_name=))(address=(protocol=tcp)(host=192.168.1.4)(port=1521)))
tns-12541: tns:no listener
有错误……没有启动监听？ 不会啊
上面看已经启动了……
再自己tnsping一下;
[oracle@demon admin]$ tnsping 192.168.1.8
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 00:45:33
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used hostname adapter to resolve the alias
attempting to contact (description=(connect_data=(service_name=))(address=(protocol=tcp)(host=192.168.1.8)(port=1521)))
ok (110 msec)
自己tnsping自己没有问题
再测试一下catalog库
[oracle@jibo ~]$ tnsping 192.168.1.8
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 00:48:02
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used hostname adapter to resolve the alias
attempting to contact (description=(connect_data=(service_name=))(address=(protocol=tcp)(host=192.168.1.8)(port=1521)))
ok (100 msec)
tnsping target库没问题
再自己tnsping自己一下：
[oracle@jibo ~]$ tnsping 192.168.1.4
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 00:48:14
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used hostname adapter to resolve the alias
attempting to contact (description=(connect_data=(service_name=))(address=(protocol=tcp)(host=192.168.1.4)(port=1521)))
tns-12541: tns:no listener
额，错误，无监听，查看监听，什么原因呢……
[oracle@jibo admin]$ lsnrctl status listener
lsnrctl for linux: version 11.2.0.4.0 - production on 23-sep-2014 23:09:33
copyright (c) 1991, 2013, oracle. all rights reserved.
connecting to (description=(address=(protocol=tcp)(host=jibo)(port=1526)))
status of the listener
------------------------
alias listener
version tnslsnr for linux: version 11.2.0.4.0 - production
start date 23-sep-2014 23:01:40
uptime 0 days 0 hr. 7 min. 52 sec
trace level off
security on: local os authentication
snmp off
listener parameter file /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
listener log file /u01/app/oracle/diag/tnslsnr/jibo/listener/alert/log.xml
listening endpoints summary...
(description=(address=(protocol=tcp)(host=jibo)(port=1526)))
(description=(address=(protocol=ipc)(key=extproc1526)))
services summary...
service prod has 1 instance(s).
instance prod, status ready, has 1 handler(s) for this service...
service prodxdb has 1 instance(s).
instance prod, status ready, has 1 handler(s) for this service...
the command completed successfully
挺正常啊；catalog库有问题，啥问题呢……
再尝试tnsping 主机名
[oracle@jibo ~]$ tnsping jibo
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 00:51:55
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used hostname adapter to resolve the alias
attempting to contact (description=(connect_data=(service_name=))(address=(protocol=tcp)(host=192.168.1.4)(port=1521)))
tns-12541: tns:no listener
错误……
tnsping 服务名 试试……
[oracle@jibo ~]$ tnsping prod
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 00:53:09
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used tnsnames adapter to resolve the alias
attempting to contact (description = (address = (protocol = tcp)(host = jibo)(port = 1526)) (connect_data = (server = dedicated) (service_name = prod)))
ok (120 msec)
这个可以啊……那啥问题呢……
难道主机名和ip地址不匹配？
查看 /etc/hosts
[root@jibo ~]# cat /etc/hosts
# do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.1.4 jibo
[root@jibo ~]#
正常啊
重启监听lsnrctl reload
再试试……
仍然报tns-12541: tns:no listener错误；查看tnsnames.ora
prod =
(description =
(address = (protocol = tcp)(host = jibo)(port = 1526))
(connect_data =
(server = dedicated)
(service_name = prod)
)
)
listener_prod =
(address = (protocol = tcp)(host = jibo)(port = 1526))
在查看listener.ora
listener =
(description_list =
(description =
(address = (protocol = tcp)(host = jibo)(port = 1526))
(address = (protocol = ipc)(key = extproc1526))
)
)
adr_base_listener = /u01/app/oracle
我顿时有些纳闷，为啥netca自动创建的监听中tnsnames.ora和listener.ora的监听名不一样的，难道自动生成还有错不成……
那么在数据库中注册的监听到底用哪个名字啊
我再查看local_listener这个参数
sys@prod>show parameter local_listener
name type value
------------------------------------ ----------- ------------------------------
local_listener string listener_prod
sys@prod>
额，不是我创建的listener的监听名，为什么？
嗯……
哦，我想起来了listener监听名是oracle默认的监听名，同时默认的监听端口为1521，
我一直tnsping 主机名/ip时，根据默认监听名，它自动找端口为1521的监听，那当然找不到了……
netstat -pan|grep 1521 就没有启用这个端口……
看来默认监听名不是那么好用的，
我在listener.ora中手动把监听名改为listener_prod
重启监听 lsnrctl reload listener_prod
然后再关了默认监听lsnrctl stop
[oracle@jibo ~]$ sqlplus hr/hr@jibo:1526/prod
sql*plus: release 11.2.0.4.0 production on wed sep 24 09:01:14 2014
copyright (c) 1982, 2013, oracle. all rights reserved.
connected to:
oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
sql>
简单连接没问题，说明1526这个端口是可以用的
再tnsping试试
[oracle@jibo admin]$ tnsping 192.168.1.4
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 09:01:28
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used hostname adapter to resolve the alias
attempting to contact (description=(connect_data=(service_name=))(address=(protocol=tcp)(host=192.168.1.4)(port=1521)))
tns-12541: tns:no listener
[oracle@jibo admin]$ tnsping jibo
tns ping utility for linux: version 11.2.0.4.0 - production on 24-sep-2014 09:01:41
copyright (c) 1997, 2013, oracle. all rights reserved.
used parameter files:
used hostname adapter to resolve the alias
attempting to contact (description=(connect_data=(service_name=))(address=(protocol=tcp)(host=192.168.1.4)(port=1521)))
tns-12541: tns:no listener
[oracle@jibo admin]$
都不行，好像必须走1521这个端口，但默认的已经改了，
还是这个端口……嗯 谁有好的办法让它用1526这个端口的请留言啊……
结果成功了 ，用的是1526的端口；默认配置监听还真是坑……