Oracle 11g RAC 环境下单实例非缺省监听及端口配置

如果在Oracle 11g RAC环境下使用dbca创建单实例数据库后，Oracle会自动将其注册到缺省的1521端口及监听器。大多数情况下我们使用的为非缺省监听器以及非缺省的监听端口。而且在Oracle 11g RAC环境中，对于集群监听器的配置由grid用户来接管了。基于这种情形的单实例非缺省监听及端口该如何配置呢？本文给出了解决方案，并且使用了静态及动态两种注册方法。

      关于单实例下非缺省监听及端口的配置可以参考下面的文章。实际上参照下列文章依旧可以完成RAC 环境下单实例非缺省监听及端口的配置，但
      RAC环境下较之前的单实例环境有些不同，所以记录下了这些个细小的差异。
            配置非默认端口的动态服务注册
            配置sqlnet.ora限制IP访问Oracle
            Oracle 监听器日志配置与管理
            设置 Oracle 监听器密码(LISTENER)
            配置ORACLE 客户端连接到数据库

1、dbca创建单实例数据库后监听器的情形

--环境[grid@linux1 ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
[grid@linux1 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.1.0]

--实例cnbo为RAC环境下使用dbca创建，当前处于offline状态
[oracle@linux1 ~]$ crsstat | grep cnbo
ora.cnbo.db                              database       C OFFLINE    OFFLINE      (linux1)         0    Instance Shutdown

--缺省的listener处于offline状态
[oracle@linux1 ~]$ crsstat | grep Listener | grep OFFLINE
ora.LISTENER.lsnr                        Listener       L OFFLINE    OFFLINE      (linux1)         0   

--启动实例及监听
[oracle@linux1 ~]$ srvctl start database -d cnbo
[oracle@linux1 ~]$ srvctl start listener -l LISTENER
PRCC-1014 : LISTENER was already running

[oracle@linux1 ~]$ export ORACLE_SID=cnbo
[oracle@linux1 ~]$ sqlplus / as sysdba
SQL> set linesize 190
SQL> show parameter cluster_d          --->此实例为RAC环境下的单实例

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
cluster_database                     boolean                           FALSE
cluster_database_instances           integer                           1

--下面的lsnrctl status可以看到实例被注册到1521的默认端口，dbca创建完实例cnbo后并没有为其配置监听
--而实例确确实实的被注册到了缺省的监听器，应该来说这个是dbca是自动添加的
--注：从Oracle 11g RAC开始，集群监听器的配置由grid用户来接管，因此可以在$ORA_CRS_HOME/network/admin/目录下找到对应的listener.ora文件
[grid@linux1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 12:35:43
  .............
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.31)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "cnbo.orasrv.com" has 1 instance(s).              --->该服务即是单实例cnbo提供的
  Instance "cnbo", status READY, has 1 handler(s) for this service...
Service "cnboXDB.orasrv.com" has 1 instance(s).
  Instance "cnbo", status READY, has 1 handler(s) for this service...
The command completed successfully

2、配置非缺省的监听器并实现动态注册

--切换到Oracle用户下，并清空其下的listener.ora与tnsnames.ora [oracle@linux1 ~]$ cat /dev/null>$ORACLE_HOME/network/admin/listener.ora[oracle@linux1 ~]$ cat /dev/null>$ORACLE_HOME/network/admin/tnsnames.ora --下面通过netca来为其配置监听器及tnsnames.ora[oracle@linux1 ~]$ export DISPLAY=192.168.7.133:0.0[oracle@linux1 ~]$ netca-- Author : Robinson Cheng-- Blog   : http://blog.csdn.net/robinson_0612-->下面是使用netca配置后的结果，包括listener.ora以及tnsnames.ora[oracle@linux1 ~]$ more $ORACLE_HOME/network/admin/listener.ora# listener.ora Network Configuration File: /u01/app/oracle/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.ADR_BASE_LISTENER_CNBO = /u01/app/oracleLISTENER_CNBO =                           #--监听器的名字为LISTENER_CNBO，端口为1522  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.21)(PORT = 1522))    )  )[oracle@linux1 ~]$ more $ORACLE_HOME/network/admin/tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.CNBO =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.21)(PORT = 1522))    )    (CONNECT_DATA =      (SERVICE_NAME = cnbo.orasrv.com)    )  )--提示没有找到监听器，无法ping通，因为我们配置的监听器并没有启动--尽管服务在缺省的监听器注册，但无法ping，即此路不通[oracle@linux1 ~]$ tnsping cnboTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 14:28:47Copyright (c) 1997, 2009, Oracle.  All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.21)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = cnbo.orasrv.com)))TNS-12541: TNS:no listener  --下面我们启动非缺省的监听器[oracle@linux1 ~]$ lsnrctl start LISTENER_CNBOLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 14:29:14  ..............Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux1.orasrv.com)(PORT=1522)))The listener supports no services               The command completed successfully[oracle@linux1 ~]$ lsnrctl status LISTENER_CNBOLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 14:30:20  .............Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux1.orasrv.com)(PORT=1522)))The listener supports no services    -->没有任何服务及实例注册到监听器，等N久也不会有实例注册The command completed successfully   -->因为这个是非缺省的，而且我们还没有配置动态注册--下面我们来设置动态注册--查看参数local_listener，此时已经被设置了，而且端口是1521，IP用的是虚IP--这就是为什么实例创建后，会被自动注册到grid用户下缺省监听器的缘故SQL> show parameter local_lisNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------local_listener                       string                            (DESCRIPTION=(ADDRESS_LIST=(AD                                                                       DRESS=(PROTOCOL=TCP)(HOST=linu                                                                       x1-vip)(PORT=1521))))--设置动态注册，收到了错误消息SQL> alter system set local_listener='LISTENER_CNBO';alter system set local_listener='LISTENER_CNBO'*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-00132: syntax error or unresolved network name 'LISTENER_CNBO'--对于前面出现的错误，给出两种解决方案，--一是按照前面local_listener参数值的格式设置新的ip及端口，或者将这个描述信息添加到tnsnames.ora文件中--下面我们选用了第二种解决方案[oracle@linux1 ~]$ echo "> LISTENER_CNBO =  >       (ADDRESS_LIST =>         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.21)(PORT = 1522))>       )">>$ORACLE_HOME/network/admin/tnsnames.ora--再次设置参数local_listenerSQL> alter system set local_listener='LISTENER_CNBO';System altered.--下面可以看到实例及服务已经自动注册到监听器LISTENER_CNBO                                                                       [oracle@linux1 ~]$ lsnrctl status LISTENER_CNBOLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 14:35:04    ...................Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux1.orasrv.com)(PORT=1522)))Services Summary...Service "cnbo.orasrv.com" has 1 instance(s).  Instance "cnbo", status READY, has 1 handler(s) for this service...Service "cnboXDB.orasrv.com" has 1 instance(s).  Instance "cnbo", status READY, has 1 handler(s) for this service...The command completed successfully--下面查看grid用户下原来的服务cnbo.orasrv.com及实例cnbo也不复存在[grid@linux1 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 14:37:46 .....Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.21)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.31)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...The command completed successfully

3、配置非缺省的监听器并实现静态注册

--对于非缺省监听器，我们也可以为其配置静态注册方式--首先我们停止监听器，并清空Oracle用户下的listener.ora与tnsnames.ora [oracle@linux1 ~]$ lsnrctl stop LISTENER_CNBO [oracle@linux1 ~]$ cat /dev/null>$ORACLE_HOME/network/admin/listener.ora[oracle@linux1 ~]$ cat /dev/null>$ORACLE_HOME/network/admin/tnsnames.ora SQL> alter system set local_listener='';--下面使用netmgr来配置监听器及tnsnames[oracle@linux1 ~]$ export DISPLAY=192.168.7.133:0.0[oracle@linux1 ~]$ netmgr--下面是配置后的结果，此时我们使用了新的端口号1523以及新的监听器名字LISTENER_NEW[oracle@linux1 ~]$ more $ORACLE_HOME/network/admin/listener.ora# listener.ora Network Configuration File: /u01/app/oracle/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.# 我们使用netmgr为其添加了数据库服务，即SID_LIST_LISTENER_NEW项，而netca无法完成此项任务LISTENER_NEW =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = linux1.orasrv.com)(PORT = 1523))  )ADR_BASE_LISTENER_NEW = /u01/app/oracleSID_LIST_LISTENER_NEW =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = cnbo.orasrv.com)      (ORACLE_HOME = /u01/app/oracle/db_1)      (SID_NAME = cnbo)    )  )  [oracle@linux1 ~]$ more $ORACLE_HOME/network/admin/tnsnames.ora        # tnsnames.ora Network Configuration File: /u01/app/oracle/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.CNBO =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = linux1.orasrv.com)(PORT = 1523))    )    (CONNECT_DATA =      (SERVICE_NAME = cnbo.orasrv.com)    )  )--启动新的监听器  [oracle@linux1 ~]$ lsnrctl start LISTENER_NEWLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 16:02:14  ................Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux1.orasrv.com)(PORT=1523)))Services Summary...Service "cnbo.orasrv.com" has 1 instance(s).  Instance "cnbo", status UNKNOWN, has 1 handler(s) for this service...  --注意状态为UNKNOWN，表明是静态注册The command completed successfully--我们看看grid用户缺省监听的状态，此时实例cnbo依旧被注册[grid@linux1 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 15:55:33  ..........Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.21)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.31)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "cnbo.orasrv.com" has 1 instance(s).  Instance "cnbo", status READY, has 1 handler(s) for this service...Service "cnboXDB.orasrv.com" has 1 instance(s).  Instance "cnbo", status READY, has 1 handler(s) for this service...The command completed successfully--下面检查一下local_listener参数SQL> show parameter db_nameNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------db_name                              string                            cnboSQL> show parameter local_lis    -->参数没有做任何设置NAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------local_listener                       string--从上面的情形来看，尽管没有设置local_listener参数，此时实例依旧被注册到监听--在此时我们已经可以通过tnsnames连接到数据库，也就是说客户端发起的连接最终还是通过监听器LISTENER_NEW，而不是缺省的监听器--对于这个情形在非RAC环境的Oracle 10g,11g 可以不用设置local_listener，也不会注册到缺省监听器--但grid用户下的缺省监听器提供的cnbo服务着实别扭，下面为还是添加监听器信息到tnsnames.ora[oracle@linux1 ~]$ echo " > LISTENER_NEW =                                                        >       (ADDRESS_LIST =                                                  >         (ADDRESS = (PROTOCOL = TCP)(HOST = linux1.orasrv.com)(PORT = 1523)) >       )">>$ORACLE_HOME/network/admin/tnsnames.ora          -->设置local_listenerSQL> alter system set local_listener='LISTENER_NEW';--下面的缺省监听状态下不再看到cnbo实例[grid@linux1 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2013 16:06:38  .........Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.21)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.31)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...The command completed successfully

4、后记
a、在Oracle 11g RAC环境下使用dbca创建单实例数据库会自动将实例配置到缺省的监听器以及缺省端口。
b、在Oracle 11g RAC环境下，对于集群监听的配置，需要在grid用户下来完成，这个不同于Oracle 10g RAC。关于10g RAC监听配置可参考尾部链接。
c、注意netca与netmgr在配置监听器时的差异，由上测试可知netca不能为监听添加数据库服务，即SID_LIST_<LISTENER_CNBO>项，而netmgr可以实现。建议使用netmgr。
d、动态服务注册需要配置local_listener参数，要么使用DESCRIPTION等一串描述，要么简化到tnsnames.ora文件之中。
e、对于Oracle 11g RAC环境下单实例的非缺省监听及端口的静态注册，如果不设置local_listener，尽管会注册到指定的监听器，但同时也会注册到缺省监听器。
f、对于上面e点描述的情形，在非RAC环境单实例10g，11g未曾出现过此现象，应该是Oralce尽可能要求使用动态注册。