第二十一章: Oracle 网络
1、listener 监听器:在oracle server 上启动,负责接收client process 并派生server process ,与client process 建立session
2、建立listener :通过 netca
3、listener 配置
[oracle@work ~]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-AUG-2011 10:40:26
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
LSNRCTL>
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=work)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 12-AUG-2011 10:39:20
Uptime 0 days 0 hr. 1 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(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
LSNRCTL> exit
[oracle@work ~]$ netstat -an|grep 1521
tcp 0 0 0.0.0.0:1521 0.0.0.0:* LISTEN
tcp 0 0 192.168.8.240:1521 192.168.8.240:32795 TIME_WAIT
[oracle@work ~]$
4、客户端链接
--------本地net服务名:通过netca配置(配置文件:$ORACLE_HOME/network/admin/tnsnames.ora)
1、配置service_name
10:43:24 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string prod
db_unique_name string prod
global_names boolean FALSE
instance_name string prod
lock_name_space string
log_file_name_convert string
service_names string prod
10:43:29 SQL>
2、客户端连接
C:\Documents and Settings\liqx>sqlplus sys/oracle@prod as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 8月 12 11:21:11 2011
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> show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- -------------------
----------
db_file_name_convert string
db_name string prod
db_unique_name string prod
global_names boolean FALSE
instance_name string prod
lock_name_space string
log_file_name_convert string
service_names string prod
SQL>
5、lisenter 注册:告诉listener ,database的instance name 和 service name
1)静态注册:当listener 使用的是非标准端口(1521),在listener.ora的文件里手工注册(添加instance name 和 service name)
2)动态注册:listener 采用的是默认端口(1521),当实例启动时,由pmon 自动将instance name 和service name 注册到listener
[oracle@work ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-AUG-2011 10:46:04
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=work)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 12-AUG-2011 10:39:20
Uptime 0 days 0 hr. 6 min. 44 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(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...
---------prod instance 通过pmon 注册成功
Service "prodXDB" 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
[oracle@work ~]$
---------listener 刚启动时,instance还未注册成功,客户端是不能链接的
[oracle@work ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-AUG-2011 10:58:22
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=work)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 12-AUG-2011 10:58:06
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(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
--------还未注册成功
C:\Documents and Settings\liqx>sqlplus sys/oracle@prod as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 8月 12 11:36:26 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误
请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误
SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus
-----客户端无法访问
7、手工注册
1、通过netca 建立非标准端口的listener :lis2(1522)
2、修改listener.ora
---------查看listener.ora
[oracle@work ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@work admin]$ ls
listener11081211AM0052.bak listener.ora samples shrept.lst tnsnames11081211AM0052.bak tnsnames.ora
[oracle@work admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LSN2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = work)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = work)(PORT = 1521))
)
)
[oracle@work admin]$
----添加静态注册信息
[oracle@work admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LSN2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = work)(PORT = 1522))
)
)
#------------添加以下静态注册信息
SID_LIST_LSN2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= prod)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = prod)
)
)
#------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = work)(PORT = 1521))
)
)
[oracle@work admin]$
------------启动lsn2
[oracle@work admin]$ netstat -an|grep 1522
tcp 0 0 0.0.0.0:1522 0.0.0.0:* LISTEN
tcp 0 0 192.168.8.240:1522 192.168.8.248:3371 ESTABLISHED
tcp 0 0 192.168.8.240:33187 192.168.8.240:1522 ESTABLISHED
tcp 0 0 192.168.8.240:1522 192.168.8.240:33187 ESTABLISHED
[oracle@work admin]$
[oracle@work admin]$ lsnrctl start lsn2
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-AUG-2011 11:13:33
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /u01/app/oracle/product/10.2.0/db_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/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/lsn2.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=work)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias lsn2
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 12-AUG-2011 11:13:33
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/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/lsn2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(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
----------查看lsn2 status
[oracle@work admin]$ lsnrctl status lsn2
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-AUG-2011 11:16:18
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=work)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias lsn2
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 12-AUG-2011 11:13:33
Uptime 0 days 0 hr. 2 min. 44 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/lsn2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(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
-------------status unknown 表示instance 是以静态注册方式注册
-----------server 配置tnsnames.ora
lsn2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.240)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
-----------客户端链接
C:\Documents and Settings\liqx>sqlplus scott/tiger@prod
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 8月 12 12:14:25 2011
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> show user;
USER 为 "SCOTT"
SQL>
转载于:https://blog.51cto.com/tiany/791841