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

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

一、静态注册

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

配置示例：

listener.ora文件：

[[email protected] ~]$ 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参数可选

(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))

)

运行lsnrctl status，看到如下返回值：

[[email protected] ~]$ lsnrctl status

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

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即表明为静态注册，不保证能连接数据库。

tnsnames.ora文件：

[[email protected] ~]$ 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、修改监听配置后可以不重启监听，直接执行lsnrctl reload生效。

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

3、配置监听时，可以设置全局数据库名，GLOBAL_DBNAME 可以不等于参数文件中的全局数据库名（数据库名+数据库域）。

二、动态注册

1）、缺省的动态注册

pmon在数据库启动到nomount、mount或open时，动态从参数文件中读取service_names值；

并且在数据库运行时，pmon会每隔一段时间进行动态注册。

service_names 缺省为全局数据库名（数据库名+数据库域），可以通过alter system set service_names='A,B,C'设置多个。

[[email protected] ~]$ lsnrctl status

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

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参数。

步骤如下：

a）新增监听器

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

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

LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

b）指定监听参数

方法一：直接通过修改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)、查看监听器状态

[[email protected] ~]$ lsnrctl status listener1

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

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/[email protected]的时候，执行过程如下

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/[email protected] 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。

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

继续阅读

快速删除ORACLE重复记录

oracle海量数据表删除重复记录

[引]在Oracle中如何利用Rowid查找和删除表中的重复记录

oralce与mysql中，如何删除重复记录

查询表中重复记录

ORACLE 表压缩

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程序

LoadRunner 的多种Vuser类型

性能测试（并发负载压力）测试分析－简要篇

赋予用户dba权限--解释以上：

Oracle的基本操作

为什么要选择UniDAC

windows不能在本地计算机上运行oracleDbConsoleorcl

SQL语言基础：常用的数据查询语句

Oracle 批量查询传入List 返回List