今天一个同事来求助sqlplus+sid登录不上的问题，测试登录，发现有ora-12514的报错:
|
1 2 3 4 5 6 7 8 9 |
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 |
检查了sqlnet.ora文件，发现NAMES.DIRECTORY_PATH= (TNSNAMES)，是使用tsnnames，继续检查tnsnames.ora文件，发现里面的配置也没有错误，里面的采用IP配置的，IP地址也没有配错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
…… #### 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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
#### 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) ) ) |
从上面的文件可以看出，该库的使用了动态注册，一般情况下，就算reload了lsnr且不重启instance，一般也会在一分钟左右重新动态注册lsnr，但是在这个案例中，等待3分多钟，检查lsnrctl service，仍然没有注册，尝试重启instance，再次检查，发现还是没有实现动态注册：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
检查数据库初始化文件：
|
1 2 3 4 5 6 7 8 |
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ 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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 |
将hosts文件中的IP地址改为正确的IP地址后，lsnr的动态注册恢复正常：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
#### 关闭侦听 ##### 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是否配置正确。
3条评论
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/9.2.0)
(PROGRAM = extproc)
不太明白这个原理，如果在listener里面使用这样的SID_LIST_LISTENER的话，listener启动以后会自动检测本地是否有数据库服务，并为它提供侦听么？？？
为什么不直接为本地数据库配置侦听呢？？这样不是省掉了动态这一环节，更加可靠么？？
做活要细致，做好文档管理，经常做记录。 这样可以尽量的避免一些问题的产生，同时在发现问题的时候可以尽快的解决。 只是说说，发现自己在文档记录上也没真正做好。 不断努力中，争取早日加入oracle DBA的行列 ^_^
re 啊古：
会自动的监测到数据库的服务，pmon会根据数据库初始化文件中的db_name和services_name不断的注册到listener，这就是所谓的动态注册。其实db_name并不是动态注册必要的条件，但它是数据库启动的必要条件，真正需要配置的就是services_name了。如果没有配置services_name，默认的services_name就为db_name.domain。动态注册后，我们用lsnrctl service看到的服务名就是在初始化文件中配置的services_name。
你说的直接配置侦听，就是静态注册。
各有各的好处吧，动态的方便，静态的不容易出错。