Oracle监听器的静态注册与动态注册

这次实验所用的数据库的DB_NAME=orcl，SID=orcl

一、静态注册

由于静态注册，参数是手动静态添加，数据库无法确认监听是否正确配置。因此，lsnrctl status显示状态为unkown，不保证能连通数据库。

静态注册的好处在于在数据库没有启动的时候，也能通过注册服务远程启动数据库。

在运行lsnrctl命令的status时，看到如下返回值：
[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 17-JUN-2014 10:29:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.147)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                17-JUN-2014 10:27:42
Uptime                    0 days 0 hr. 1 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.147)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

这里的，状态UNKOWN即表明为静态注册

监听文件：

[oracle@localhost ~]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      #(GLOBAL_DBNAME = orcl_pd)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

TNS文件：

[oracle@localhost ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

这里的ORCL为服务命名。可以为任意值，在客户端连接服务器时，填写的主机字符串，即为此服务命名ORCL。

 注意：

1、修改监听配置后要reload才生效，不需要重启监听。

2、静态注册监听，客户端在配置tnsnames.ora服务命名时，“（Oracle 8i或更高版本）服务名”里填写内容要与服务端静态注册监听器时的全局数据库名一致。

3、配置监听时，可以设置全局数据库名，全局数据库名可以写任意内容，也可以不设置全局数据库名，但是要保证SID正确。

4、如果监听文件中的全局数据库名不等于参数文件中的全局数据库名（数据库名+数据库域），则需要执行alter system set service_names='a,b,c';将这全局数据库名注册到参数文件中。

 二、动态注册

1、缺省的动态注册

pmon在数据库启动到nomount、mount或open时，动态从参数文件中读取service_names值；并且在数据库运行时，pmon会每隔一段时间进行动态注册。

service_names缺省为dbca建立数据库时的全局数据库名。

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 17-JUN-2014 10:29:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.147)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                17-JUN-2014 10:27:42
Uptime                    0 days 0 hr. 1 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.147)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "eas" has 1 instance(s).
  Instance "eas", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

状态为READY的记录，表明为动态注册（由PMON进程自动从参数文件获取）。

注意：

1、不管参数service_names为何值，pmon都会自动以全局数据库名(这里为orcl)为服务名，动态注册一个监听。
2、数据库启动到nomount、mount状态时，动态注册的服务所对应的实例状态为BLOCKED。

 2、自定义端口的动态监听注册

若要启用非默认端口的动态监听注册，必须设置local_listener参数。并在服务端配置tnsnames.ora指定监听参数，或者直接通过修改local_listener指定监听参数。步骤如下：

1）新增监听器

方法一：运行netmgr，配置监听程序，监听端口为1522（非默认端口），保存配置

方法二：直接编辑监听文件，增加以下内容

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1525))
    )
  )

2）、指定监听参数

方法一：直接通过修改local_listener参数指定

SQL>alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.147)(PORT=1522))';
 System altered

 SQL>alter system register

 System altered

方法二：在Oracle服务器端文件$ORACLE_HOME/network/admin/tnsnames.ora填入如下内容

mytest =

(DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS=(PROTOCOL=TCP)(HOST = 192.168.0.147)(PORT = 1522))
  )
)

SQL>alter system set local_listener=mytest;
System altered

SQL>alter system register;

3)、查看监听器状态

[oracle@localhost ~]$ lsnrctl status listener1

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 17-JUN-2014 11:45:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.147)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                17-JUN-2014 11:41:54
Uptime                    0 days 0 hr. 3 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/db_1/network/log/listener1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.147)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

 三、当你输入sqlplus sys/oracle@orcl的时候，执行过程如下

1. 查询sqlnet.ora看看名称的解析方式，发现是TNSNAME

2. 则查询tnsnames.ora文件，从里边找orcl的记录，并且找到主机名，端口和service_name

3. 如果listener进程没有问题的话，建立与listener进程的连接。

4. 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。默认是专用服务器模式，没有问题的话客户端就连接上了数据库的server process。

5. 这时候网络连接已经建立，listener进程的历史使命也就完成了。

四、几种连接用到的命令形式

1.sqlplus / as sysdba 这是典型的操作系统认证，不需要listener进程

2.sqlplus sys/oracle 这种连接方式只能连接本机数据库，同样不需要listener进程

3.sqlplus sys/oracle@orcl 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。

备注：可以使用具有sysdba权限的用户本地操作系统认证启动数据库和远程服务连接启动数据库。