Oracle DBA课程系列笔记（21）

来源：互联网发布：公知我的战争编辑：程序博客网时间：2024/05/08 08:01

第二十一章： 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

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

连接到:
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

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

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

ERROR:
ORA-12514: 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

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

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

连接到:
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>

本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/791841

0 0