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 / as sysdba
startup
EOF
[oracle@guohui6 oracle]$ ./ dbstart

LSNRCTL for Linux : Version 10.2 . 0.1 . 0 - Production on 13 - FEB - 2011 20 : 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 : Version 10.2 . 0.1 . 0 - Production
System parameter file 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 for Linux : Version 10.2 . 0.1 . 0 - Production
Start Date 13 - FEB - 2011 20 : 11 : 15
Uptime 0 days 0 hr . 0 min . 0 sec
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 Log File / 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 " has 1 instance(s) .
Instance " ORCL " , status UNKNOWN , has 1 handler(s) for thisservice ...
Service " ORCL " has 1 instance(s) .
Instance " ORCL " , status UNKNOWN , has 1 handler(s) for thisservice ...
Service " PLSExtProc " has 1 instance(s) .
Instance " PLSExtProc " , status UNKNOWN , has 1 handler(s) for thisservice ...
The command completed successfully

SQL * Plus : Release 10.2 . 0.1 . 0 - Production on Sun Feb 13 20 : 11 : 16 2011

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 System Global Area 461373440 bytes
Fixed Size 1220000 bytes
Variable Size 75498080 bytes
Database Buffers 381681664 bytes
Redo Buffers 2973696 bytes
Database mounted .
Database opened .
SQL > Disconnected from Oracle Database 10g EnterpriseEdition Release 10.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 for Linux : Version 10.2 . 0.1 . 0 - Production on 13 - FEB - 2011 20 : 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 : Release 10.2 . 0.1 . 0 - Production on Sun Feb 13 20 : 17 : 27 2011

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 > select count ( * ) fromdate_log;

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

SQL >

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