13518219792
昨天做了一个实验，环境是这样的，建立一个静态监听端口1526以及一个动态监听端口1521，并且要通过在tnsname用两种连接字符串进行连接，静态连接的字符串直接连接没有问题，可是后来动态连接的字符串怎么也连接不进去，尝试注册
创新互联建站主要从事成都网站制作、网站设计、网页设计、企业做网站、公司建网站等业务。立足成都服务会宁,十载网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:028-86922220
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
