Oracle 静态监听注册详解

Oracle 静态监听注册详解

       网上有很多关于oracle 监听静态注册的文章，但大多都是简单说说，并没有详细的例子，这里，将结合linux as4 下的oracle 10gR2.0.1 举一个具体的例子
1、在 $ORACLE_HOME/network/admin/listener.ora 文件中加入一个静态注册的节点

[oracle@prudent oracle]$ cd $ORACLE_HOME/network/admin[oracle@prudent admin]$ vi listener.ora# listener.ora Network Configuration File: /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (SID_NAME = PLSExtProc)      (ORACLE_HOME = /mydatafile2/app/oracle/oracle/product/11.2.0/db_1)      (PROGRAM = extproc)    )    (SID_DESC =      (SID_NAME = ORCL)      (ORACLE_HOME = /mydatafile2/app/oracle/oracle/product/11.2.0/db_1)      (GLOBAL_DBNAME=WOO.COM)    )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))      (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521))    )  )

注意这里的GLOBAL_DBNAME=WOO.COM
SID_NAME=ORCL 
这个SID_NAME 应与你对外提供服务的 $ORACLE_SID 一致

[oracle@prudent admin]$ echo $ORACLE_SID ORCL

 2、配置对应的tnsnames.ora 中的节点

[oracle@prudent admin]$ vi tnsnames.ora# tnsnames.ora Network Configuration File: /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.ORCL=  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ORCL)    )  )WOOORCL=  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = WOO.COM)    )  )

tnsname WOOORCL 中的 SERVICE_NAME=WOO.COM

这里的服务名为 WOO.COM 而不是通常的 ORCL，因为在 listener.ora 中已经注册了 WOO.COM,lsnrctl 启动时会监听 WOO.COM ，并对应到 SID_NAME=ORCL 上。

3、启动监听和服务

[oracle@prudent oracle]$ cat dbstartlsnrctl startsqlplus /nolog <<EOFconnect /as sysdbastartupEOF[oracle@prudent oracle]$ ./dbstartLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-FEB-2011 20:11:15Copyright (c) 1991, 2005, Oracle.  All rights reserved.Starting /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionSystem parameter file is /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/listener.oraLog messages written to /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/log/listener.logListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prudent)(PORT=1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date                13-FEB-2011 20:11:15Uptime                    0 days 0 hr. 0 min. 0 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/listener.oraListener Log File         /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/log/listener.logListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prudent)(PORT=1521)))Services Summary...Service "WOO.COM" has 1 instance(s).  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...Service "ORCL" has 1 instance(s).  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...Service "PLSExtProc" has 1 instance(s).  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...The command completed successfullySQL*Plus: Release 11.2.0.1.0 - Production on Sun Feb 13 20:11:16 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.SQL> Connected to an idle instance.SQL> ORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started.Total System Global Area  461373440 bytesFixed Size                  1220000 bytesVariable Size              75498080 bytesDatabase Buffers          381681664 bytesRedo Buffers                2973696 bytesDatabase mounted.Database opened.SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options

可以看到  
Service "WOO.COM" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
正在被监听。

4、验证该服务可以到达

[oracle@prudent oracle]$ tnsping WOOORCLTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-FEB-2011 20:14:59Copyright (c) 1997, 2005, Oracle.  All rights reserved.Used parameter files:/mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = WOO.COM)))OK (10 msec)

5、利用静态注册的服务登入oracle

[oracle@prudent oracle]$ sqlplus system/oracle@WOOORCLSQL*Plus: Release 11.2.0.1.0 - Production on Sun Feb 13 20:17:27 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> select count(*) from date_log;  COUNT(*)----------SQL>

至此：已验证该静态注册可以成功的被解析，监听，连接。