client端如果想要连接到远程的数据库服务器，首先数据库服务器必须启动监听器
oracle监听器的配置在$ORACLE_HOME/network/admin/listener.ora,打开这个文件，我们看到配置如下：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
)
动态注册：oracle instance的pmon进程会在instance启动之后自动将instance_name和service_name注册到1521端口中，我们只需要在listener中修改配置如下，即可实现
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
)
)
注意我们不用修改SID_LIST_LISTENER中的配置
我们首先启动监听器 lsnrctl
LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(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 22-FEB-2012 07:40:42
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(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
由于我们没有启动instance，所以没有instance的注册信息
启动oracle实例
[oracle@localhost ~]$ export ORACLE_SID=prod
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 22 07:42:49 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 180355072 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
prod instance已经启动，稍等一会我们查看监听器状态
LSNRCTL> status
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 22-FEB-2012 07:40:42
Uptime 0 days 0 hr. 3 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "prod_XPT" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully
发现prod服务已经被动态注册进去，其中status为READY说明动态注册成功
静态注册：由于oracle的pmon进程会自动将instance的instance_name和service_name注册到1521端口，如果我们想把instance 注册到1521之外的端口，我们只能使用静态注册
下面我们修改listener.ora配置如下：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
)
staticReg =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1522))
)
)
SID_LIST_staticReg =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= prod)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
(SID_NAME = prod)
)
)
我们首先增加了监听配置：
staticReg =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1522))
)
)
不同于动态注册，我们必须指定listenername,这里为staticReg
然后增加了SID_LIST关于staticReg的指定配置
SID_LIST_staticReg =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= prod)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
(SID_NAME = prod)
)
)
由于是非标准端口号，我们必须指定SID_LIST配置
保存之后，启动监听：
[oracle@localhost admin]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-FEB-2012 07:51:03
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
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 22-FEB-2012 07:51:06
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
之后启动instance prod
[oracle@localhost ~]$ export ORACLE_SID=prod
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 22 07:42:49 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 180355072 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
无论我们等待多久，查看状态都是下面显示的内容
LSNRCTL> status
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 22-FEB-2012 07:51:06
Uptime 0 days 0 hr. 1 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
由于我们采用的监听端口是1522，pmon进程是不会自动注册是1521之外的端口
我们需要启动1522端口的监听
LSNRCTL> start staticReg
Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/staticreg.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.102)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias staticReg
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-FEB-2012 07:56:18
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0.1/network/log/staticreg.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1522)))
Services Summary...
Service "prod" has 1 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
由于prod采用的是静态注册，所以状态永远都是UNKNOW
等一会，我们就可以直接采用sqlplus连接，当然客户端必须有对应的tnsname的配置
C:\Users\lei_dong>sqlplus sys/prod@port1522 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 22 23:58:06 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
连接成功，
最后我们需要在client端配置tnsname,这时无论启动的是1521还是其他端口的监听，差别都不大，基本上只有端口号的差异
linux =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
port1522 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
出自：
