oracle 监听静态注册举例解析

网上有很多关于oracle 监听静态注册的文章，但大多都是简单说说，并没有详细的例子，这里，将结合linux as3下的oracle 10gR2.0.1 举一个具体的例子

1、在 $ORACLE_HOME/network/admin/listener.ora 文件中加入一个静态注册的节点

[oracle@guohui6 oracle]$cd $ORACLE_HOME/network/admin
[oracle@guohui6 admin]$ vi listener.ora
# listener.oraNetwork Configuration File:/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracleconfiguration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/mydatafile2/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)

(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME =/mydatafile2/app/oracle/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME=HJD.COM.CN)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521))
)
)

注意这里的global_dbname=HJD.COM.CN

SID_NAME=ORCL

这个SID_NAME 应与你对外提供服务的 $ORACLE_SID 一致

[oracle@guohui6 admin]$echo$ORACLE_SID
ORCL

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

[oracle@guohui6 admin]$ vitnsnames.ora

# tnsnames.ora Network Configuration File:/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracleconfiguration tools.
ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =ORCL)
)
)

GUOHUIORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =HJD.COM.CN)
)
)

tnsname GUOHUIORCL 中的 SERVICE_NAME=HJD.COM.CN

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

3、启动监听和服务

[oracle@guohui6 oracle]$ catdbstart
lsnrctl start
sqlplus /nolog<<EOF
connect /assysdba
startup
EOF
[oracle@guohui6 oracle]$ ./dbstart

LSNRCTL for Linux:Version10.2.0.1.0-Production on 13-FEB-201120:11:15

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

Starting /mydatafile2/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr:pleasewait...

TNSLSNR for Linux:Version10.2.0.1.0-Production
System parameterfile is/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messageswritten to/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=guohui6)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR forLinux:Version 10.2.0.1.0-Production
Start Date 13-FEB-201120:11:15
Uptime 0 days0hr.0min.0sec
Trace Level off
Security ON:Local OS Authentication
SNMP OFF
Listener Parameter File/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=guohui6)(PORT=1521)))
Services Summary...
Service "HJD.COM.CN"has1 instance(s).
Instance "ORCL",status UNKNOWN,has1 handler(s) for thisservice...
Service "ORCL"has1 instance(s).
Instance "ORCL",status UNKNOWN,has1 handler(s) for thisservice...
Service "PLSExtProc"has1 instance(s).
Instance "PLSExtProc",status UNKNOWN,has1 handler(s) for thisservice...
The command completed successfully

SQL*Plus:Release10.2.0.1.0-Production on Sun Feb 13 20:11:162011

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

SQL> Connected to an idle instance.
SQL> ORA-32004:obsolete and/or deprecatedparameter(s) specified
ORACLE instance started.

Total SystemGlobalArea 461373440bytes
Fixed Size 1220000bytes
Variable Size 75498080bytes
Database Buffers 381681664 bytes
Redo Buffers 2973696bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 10g EnterpriseEdition Release10.2.0.1.0-Production
With the Partitioning,OLAP and Data Miningoptions

 

可以看到 

Service "HJD.COM.CN" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1handler(s) for this service...

正在被监听。

4、验证该服务可以到达

[oracle@guohui6 oracle]$ tnspingGUOHUIORCL

TNS Ping Utility forLinux:Version 10.2.0.1.0-Production on 13-FEB-201120:14:59

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

Used parameter files:
/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521))(CONNECT_DATA =(SERVER =DEDICATED)(SERVICE_NAME =HJD.COM.CN)))
OK (10 msec)

tnsping guohuiorcl 就是刚才tnsnames.ora 中配置的tnsname.可以看到，该地址可以通达。

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

[oracle@guohui6 oracle]$ sqlplustina/panda@guohuiorcl

SQL*Plus:Release10.2.0.1.0-Production on Sun Feb 13 20:17:272011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning,OLAP and Data Mining options

SQL> selectcount(*) fromdate_log;

COUNT(*)
----------
3998

SQL>

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