昨天做了一个实验,环境是这样的,建立一个静态监听端口1526以及一个动态监听端口1521,并且要通过在tnsname用两种连接字符串进行连接,静态连接的字符串直接连接没有问题,可是后来动态连接的字符串怎么也连接不进去,尝试注册
SQL> alter system register;
System altered.
[oracle@demo2 ~]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-OCT-2016 15:43:10
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=demo2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 26-OCT-2016 15:41:57
Uptime 0 days 0 hr. 1 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/demo2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=demo2)(PORT=1521)))
The listener supports no services
The command completed successfully
监听分为动态监听以及静态监听两种,静态监听直接指定数据库或者实例名,所以不需要注册,直接可以监听。动态监听需要注册才可以进行监听,因为静态监听没有问题,所以我开始进行动态监听的测试
首先我用netca配置了两个动态监听,一个端口是1526或是1521,一般情况下,
SQL> alter system register;
这样子会进行动态监听的注册,可是结果发现只有1521的动态监听监听上去了,1526的不为所动
[oracle@demo2 ~]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-OCT-2016 16:05:10
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=demo2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 26-OCT-2016 16:04:33
Uptime 0 days 0 hr. 0 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/demo2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=demo2)(PORT=1521)))
Services Summary...
Service "PROD.us.oracle.cn" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully
查询资料,发现pmon进程只会定期将1521的端口注册上去,不会注册除1521以外的进程注册上去,这个时候就需要另外一个参数local_listener,官方对loacl_listener的定义是
LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.
这个时候看一下监听的状态
[oracle@demo2 admin]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-OCT-2016 16:46:57
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=demo2)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
监听是没有起来的,这个时候我进入数据库修改local_listener
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
log_archive_local_first boolean TRUE
parallel_force_local boolean FALSE
SQL> alter system set local_listener='prod1526';
System altered.
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string prod1526
log_archive_local_first boolean TRUE
parallel_force_local boolean FALSE
这个时候可以看见我的参数已经指定为tnsname的1526端口,这个时候监听打开并动态注册,然后观测一下监听的状态
[oracle@demo2 ~]$ lsnrctl status prod1526
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-OCT-2016 16:56:05
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=demo2)(PORT=1526))(CONNECT_DATA=(service_name=PROD.us.oracle.cn)))
STATUS of the LISTENER
------------------------
Alias LISTENER8
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 26-OCT-2016 16:45:25
Uptime 0 days 0 hr. 10 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/demo2/listener8/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=demo2)(PORT=1526)))
Services Summary...
Service "PROD.us.oracle.cn" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully
注意查看监听必须加上tnsname的名字或者监听的名字,这个时候可以看见1526的这个监听已经监听到了,说明这个参数是可以指定动态连接的,也说明了除了1521这个端口是由pmon这个进程进行注册,其他的动态注册必须使用local_listener这个参数进行指定,这也就代表着一个实例只能由一个动态监听进行监听,除1521以外的端口必须指定locl_listener.
[oracle@demo2 admin]$ sqlplus scott/flllll@prod1526
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 26 16:55:01 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options