因为hosts文件无法实现侦听动态注册
今天一个同事来求助sqlplus+sid登录不上的问题，测试登录，发现有ora-12514的报错:
oracle@my_testdb01:/oracle > sqlplus myuser/mypasswd@mysid
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Sep 5 15:36:06 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Sep 5 15:36:06 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor
检查了sqlnet.ora文件，发现NAMES.DIRECTORY_PATH= (TNSNAMES)，是使用tsnnames，继续检查tnsnames.ora文件，发现里面的配置也没有错误，里面的采用IP配置的，IP地址也没有配错：
……
#### tnsnames.ora文件 #####
mysid =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.84.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mysid)
)
)
……
#### tnsnames.ora文件 #####
mysid =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.84.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mysid)
)
)
……
那么基本排除sqlnet和tnsname的问题，继续检查listener.ora：
#### listener.ora文件 ####
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
)
从上面的文件可以看出，该库的使用了动态注册，一般情况下，就算reload了lsnr且不重启instance，一般也会在一分钟左右重新动态注册lsnr，但是在这个案例中，等待3分多钟，检查lsnrctl service，仍然没有注册，尝试重启instance，再次检查，发现还是没有实现动态注册：
oracle@my_testdb01:/oracle > lsnrctl service
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:32:46
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
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
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:32:46
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
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
检查数据库初始化文件：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string mysid
sys@ORALOCAL(192.168.0.22)> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string mysid
------------------------------------ ----------- ------------------------------
db_name string mysid
sys@ORALOCAL(192.168.0.22)> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string mysid
发现也正确的配置了这两个参数。
测试将动态注册改成静态注册，问题解决。
看来问题是处在动态注册上了，继续分析。
这个时候，经一个同事指点，因为这台主机的os是从另外一台主机copy过来的，但是2台主机的IP不同，是不是hosts文件还是保持的原来的配置？
检查/etc/hosts：
my_testdb01#[/etc]cat /etc/hosts
## Configured using SAM by root on Sun Jun 19 14:24:29 2005
## Configured using SAM by root on Mon Aug 27 16:45:00 2007
# @(#)B.11.11_LRhosts $Revision: 1.9.214.1 $ $Date: 96/10/08 13:20:01 $
#
# The form for each entry is:
# <internet address> <official hostname> <aliases>
#
# For example:
# 192.1.2.34 hpfcrm loghost
#
# See the hosts(4) manual page for more information.
# Note: The entries cannot be preceded by a space.
# The format described in this file is the correct format.
# The original Berkeley manual page contains an error in
# the format description.
#
127.0.0.1 localhost loopback
10.203.84.9 my_testdb05
10.203.84.7 my_testdb01<----这边的IP还是老主机的IP，当前主机的IP应该是10.203.84.76
10.203.84.42 my_testdb03
10.203.84.27 my_testdb02
## Configured using SAM by root on Sun Jun 19 14:24:29 2005
## Configured using SAM by root on Mon Aug 27 16:45:00 2007
# @(#)B.11.11_LRhosts $Revision: 1.9.214.1 $ $Date: 96/10/08 13:20:01 $
#
# The form for each entry is:
# <internet address> <official hostname> <aliases>
#
# For example:
# 192.1.2.34 hpfcrm loghost
#
# See the hosts(4) manual page for more information.
# Note: The entries cannot be preceded by a space.
# The format described in this file is the correct format.
# The original Berkeley manual page contains an error in
# the format description.
#
127.0.0.1 localhost loopback
10.203.84.9 my_testdb05
10.203.84.7 my_testdb01<----这边的IP还是老主机的IP，当前主机的IP应该是10.203.84.76
10.203.84.42 my_testdb03
10.203.84.27 my_testdb02
将hosts文件中的IP地址改为正确的IP地址后，lsnr的动态注册恢复正常：
#### 关闭侦听 #####
oracle@my_testdb01:/oracle > lsnrctl stop
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:31:41
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
oracle@my_testdb01:/oracle >
oracle@my_testdb01:/oracle >
#### 重启侦听 ####
oracle@my_testdb01:/oracle > lsnrctl start
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:31:46
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting /oracle/app/oracle/product/9.2.0/bin/tnslsnr: please wait...
TNSLSNR for HPUX: Version 9.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /oracle/app/oracle/product/9.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.203.84.6)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.4.0 - Production
Start Date 05-SEP-2007 15:31:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.203.84.6)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#### 检查侦听提供的服务 ####
oracle@my_testdb01:/oracle > lsnrctl service
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:31:53
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
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
#### 发现此时pmon还没有动态注册lsnr，mysid服务还没有启动，等待约1分钟
#### 再次检查侦听提供的服务 ####
oracle@my_testdb01:/oracle > lsnrctl service
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:32:22
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
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
Service "mysid" has 1 instance(s).
Instance "mysid", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
The command completed successfully
#### 发现mysid的服务已经起来，确实实现动态注册
oracle@my_testdb01:/oracle > lsnrctl stop
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:31:41
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
oracle@my_testdb01:/oracle >
oracle@my_testdb01:/oracle >
#### 重启侦听 ####
oracle@my_testdb01:/oracle > lsnrctl start
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:31:46
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting /oracle/app/oracle/product/9.2.0/bin/tnslsnr: please wait...
TNSLSNR for HPUX: Version 9.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /oracle/app/oracle/product/9.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.203.84.6)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.4.0 - Production
Start Date 05-SEP-2007 15:31:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.203.84.6)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#### 检查侦听提供的服务 ####
oracle@my_testdb01:/oracle > lsnrctl service
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:31:53
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
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
#### 发现此时pmon还没有动态注册lsnr，mysid服务还没有启动，等待约1分钟
#### 再次检查侦听提供的服务 ####
oracle@my_testdb01:/oracle > lsnrctl service
LSNRCTL for HPUX: Version 9.2.0.4.0 - Production on 05-SEP-2007 15:32:22
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
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
Service "mysid" has 1 instance(s).
Instance "mysid", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
The command completed successfully
#### 发现mysid的服务已经起来，确实实现动态注册
结论：要实现侦听的动态注册，不仅仅要配置好初始化文件，配置好listener.ora文件，还需要检查/etc/hosts文件中本机的IP是否配置正确。
· 【文章发布信息】发表于： 2007-09-06 @ 00:09:02 · ||分类： ..troubleshooting, Working case
CopyRight ©
啊古 于 2007-09-06 @ 16:21:32 留言 ：
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/9.2.0)
(PROGRAM = extproc)
不太明白这个原理，如果在listener里面使用这样的SID_LIST_LISTENER的话，listener启动以后会自动检测本地是否有数据库服务，并为它提供侦听么？？？
为什么不直接为本地数据库配置侦听呢？？这样不是省掉了动态这一环节，更加可靠么？？
dbaeyes 于 2007-09-07 @ 00:03:32 留言 ：
做活要细致，做好文档管理，经常做记录。 这样可以尽量的避免一些问题的产生，同时在发现问题的时候可以尽快的解决。 只是说说，发现自己在文档记录上也没真正做好。 不断努力中，争取早日加入oracle DBA的行列 ^_^
小荷 于 2007-09-07 @ 02:10:58 留言 ：
re 啊古：
会自动的监测到数据库的服务，pmon会根据数据库初始化文件中的db_name和services_name不断的注册到listener，这就是所谓的动态注册。其实db_name并不是动态注册必要的条件，但它是数据库启动的必要条件，真正需要配置的就是services_name了。如果没有配置services_name，默认的services_name就为db_name.domain。动态注册后，我们用lsnrctl service看到的服务名就是在初始化文件中配置的services_name。
你说的直接配置侦听，就是静态注册。
各有各的好处吧，动态的方便，静态的不容易出错。