如果在Oracle g RAC环境下使用dbca创建单实例数据库后Oracle会自动将其注册到缺省的端口及监听器大多数情况下我们使用的为非缺省监听器以及非缺省的监听端口而且在Oracle g RAC环境中对于集群监听器的配置由grid用户来接管了基于这种情形的单实例非缺省监听及端口该如何配置呢?本文给出了解决方案并且使用了静态及动态两种注册方法
关于单实例下非缺省监听及端口的配置可以参考下面的文章实际上参照下列文章依旧可以完成RAC 环境下单实例非缺省监听及端口的配置但
RAC环境下较之前的单实例环境有些不同所以记录下了这些个细小的差异
配置非默认端口的动态服务注册
配置sqlnetora限制IP访问Oracle
Oracle 监听器日志配置与管理
设置 Oracle 监听器密码(LISTENER)
配置ORACLE 客户端连接到数据库
dbca创建单实例数据库后监听器的情形
[sql]
环境
[grid@linux ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release (Carthage)
Kernel \r on an \m
[grid@linux ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is []
实例cnbo为RAC环境下使用dbca创建当前处于offline状态
[oracle@linux ~]$ crsstat | grep cnbo
bodb database C OFFLINE OFFLINE (linux) Instance Shutdown
缺省的listener处于offline状态
[oracle@linux ~]$ crsstat | grep Listener | grep OFFLINE
oraLISTENERlsnr Listener L OFFLINE OFFLINE (linux)
启动实例及监听
[oracle@linux ~]$ srvctl start database d cnbo
[oracle@linux ~]$ srvctl start listener l LISTENER
PRCC : LISTENER was already running
[oracle@linux ~]$ export ORACLE_SID=cnbo
[oracle@linux ~]$ sqlplus / as sysdba
SQL> set linesize
SQL> show parameter cluster_d >此实例为RAC环境下的单实例
NAME TYPE VALUE
cluster_database boolean FALSE
cluster_database_instances integer
下面的lsnrctl status可以看到实例被注册到的默认端口dbca创建完实例cnbo后并没有为其配置监听
而实例确确实实的被注册到了缺省的监听器应该来说这个是dbca是自动添加的
注从Oracle g RAC开始集群监听器的配置由grid用户来接管因此可以在$ORA_CRS_HOME/network/admin/目录下找到对应的listenerora文件
[grid@linux ~]$ lsnrctl status
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
Services Summary
Service +ASM has instance(s)
Instance +ASM status READY has handler(s) for this service
Service has instance(s) >该服务即是单实例cnbo提供的
Instance cnbo status READY has handler(s) for this service
Service has instance(s)
Instance cnbo status READY has handler(s) for this service
The command completed successfully
配置非缺省的监听器并实现动态注册
[sql]
切换到Oracle用户下并清空其下的listenerora与tnsnamesora
[oracle@linux ~]$ cat /dev/null>$ORACLE_HOME/network/admin/listenerora
[oracle@linux ~]$ cat /dev/null>$ORACLE_HOME/network/admin/tnsnamesora
下面通过netca来为其配置监听器及tnsnamesora
[oracle@linux ~]$ export DISPLAY=:
[oracle@linux ~]$ netca
Author : Robinson Cheng
Blog :
>下面是使用netca配置后的结果包括listenerora以及tnsnamesora
[oracle@linux ~]$ more $ORACLE_HOME/network/admin/listenerora
# listenerora Network Configuration File: /u/app/oracle/db_/network/admin/listenerora
# Generated by Oracle configuration tools
ADR_BASE_LISTENER_CNBO = /u/app/oracle
LISTENER_CNBO = #监听器的名字为LISTENER_CNBO端口为
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
)
[oracle@linux ~]$ more $ORACLE_HOME/network/admin/tnsnamesora
# tnsnamesora Network Configuration File: /u/app/oracle/db_/network/admin/tnsnamesora
# Generated by Oracle configuration tools
CNBO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
(CONNECT_DATA =
(SERVICE_NAME = )
)
)
提示没有找到监听器无法ping通因为我们配置的监听器并没有启动
尽管服务在缺省的监听器注册但无法ping即此路不通
[oracle@linux ~]$ tnsping cnbo
TNS Ping Utility for Linux: Version Production on AUG ::
Copyright (c) Oracle All rights reserved
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )))
(CONNECT_DATA = (SERVICE_NAME = )))
TNS: TNS:no listener
下面我们启动非缺省的监听器
[oracle@linux ~]$ lsnrctl start LISTENER_CNBO
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
The listener supports no services
The command completed successfully
[oracle@linux ~]$ lsnrctl status LISTENER_CNBO
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
The listener supports no services >没有任何服务及实例注册到监听器等N久也不会有实例注册
The command completed successfully >因为这个是非缺省的而且我们还没有配置动态注册
下面我们来设置动态注册
查看参数local_listener此时已经被设置了而且端口是IP用的是虚IP
这就是为什么实例创建后会被自动注册到grid用户下缺省监听器的缘故
SQL> show parameter local_lis
NAME TYPE VALUE
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=linu
xvip)(PORT=))))
设置动态注册收到了错误消息
SQL> alter system set local_listener=LISTENER_CNBO;
alter system set local_listener=LISTENER_CNBO
*
ERROR at line :
ORA: parameter cannot be modified because specified value is invalid
ORA: invalid specification for system parameter LOCAL_LISTENER
ORA: syntax error or unresolved network name LISTENER_CNBO
对于前面出现的错误给出两种解决方案
一是按照前面local_listener参数值的格式设置新的ip及端口或者将这个描述信息添加到tnsnamesora文件中
下面我们选用了第二种解决方案
[oracle@linux ~]$ echo
> LISTENER_CNBO =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
> )>>$ORACLE_HOME/network/admin/tnsnamesora
再次设置参数local_listener
SQL> alter system set local_listener=LISTENER_CNBO;
System altered
下面可以看到实例及服务已经自动注册到监听器LISTENER_CNBO
[oracle@linux ~]$ lsnrctl status LISTENER_CNBO
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
Services Summary
Service has instance(s)
Instance cnbo status READY has handler(s) for this service
Service has instance(s)
Instance cnbo status READY has handler(s) for this service
The command completed successfully
下面查看grid用户下原来的服务及实例cnbo也不复存在
[grid@linux ~]$ lsnrctl status
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
Services Summary
Service +ASM has instance(s)
Instance +ASM status READY has handler(s) for this service
The command completed successfully
配置非缺省的监听器并实现静态注册
[sql]
对于非缺省监听器我们也可以为其配置静态注册方式
首先我们停止监听器并清空Oracle用户下的listenerora与tnsnamesora
[oracle@linux ~]$ lsnrctl stop LISTENER_CNBO
[oracle@linux ~]$ cat /dev/null>$ORACLE_HOME/network/admin/listenerora
[oracle@linux ~]$ cat /dev/null>$ORACLE_HOME/network/admin/tnsnamesora
SQL> alter system set local_listener=;
下面使用netmgr来配置监听器及tnsnames
[oracle@linux ~]$ export DISPLAY=:
[oracle@linux ~]$ netmgr
下面是配置后的结果此时我们使用了新的端口号以及新的监听器名字LISTENER_NEW
[oracle@linux ~]$ more $ORACLE_HOME/network/admin/listenerora
# listenerora Network Configuration File: /u/app/oracle/db_/network/admin/listenerora
# Generated by Oracle configuration tools
# 我们使用netmgr为其添加了数据库服务即SID_LIST_LISTENER_NEW项而netca无法完成此项任务
LISTENER_NEW =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
ADR_BASE_LISTENER_NEW = /u/app/oracle
SID_LIST_LISTENER_NEW =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = )
(ORACLE_HOME = /u/app/oracle/db_)
(SID_NAME = cnbo)
)
)
[oracle@linux ~]$ more $ORACLE_HOME/network/admin/tnsnamesora
# tnsnamesora Network Configuration File: /u/app/oracle/db_/network/admin/tnsnamesora
# Generated by Oracle configuration tools
CNBO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
(CONNECT_DATA =
(SERVICE_NAME = )
)
)
启动新的监听器
[oracle@linux ~]$ lsnrctl start LISTENER_NEW
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
Services Summary
Service has instance(s)
Instance cnbo status UNKNOWN has handler(s) for this service 注意状态为UNKNOWN表明是静态注册
The command completed successfully
我们看看grid用户缺省监听的状态此时实例cnbo依旧被注册
[grid@linux ~]$ lsnrctl status
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
Services Summary
Service +ASM has instance(s)
Instance +ASM status READY has handler(s) for this service
Service has instance(s)
Instance cnbo status READY has handler(s) for this service
Service has instance(s)
Instance cnbo status READY has handler(s) for this service
The command completed successfully
下面检查一下local_listener参数
SQL> show parameter db_name
NAME TYPE VALUE
db_name string cnbo
SQL> show parameter local_lis >参数没有做任何设置
NAME TYPE VALUE
local_listener string
从上面的情形来看尽管没有设置local_listener参数此时实例依旧被注册到监听
在此时我们已经可以通过tnsnames连接到数据库也就是说客户端发起的连接最终还是通过监听器LISTENER_NEW而不是缺省的监听器
对于这个情形在非RAC环境的Oracle gg 可以不用设置local_listener也不会注册到缺省监听器
但grid用户下的缺省监听器提供的cnbo服务着实别扭下面为还是添加监听器信息到tnsnamesora
[oracle@linux ~]$ echo
> LISTENER_NEW =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
> )>>$ORACLE_HOME/network/admin/tnsnamesora
>设置local_listener
SQL> alter system set local_listener=LISTENER_NEW;
下面的缺省监听状态下不再看到cnbo实例
[grid@linux ~]$ lsnrctl status
LSNRCTL for Linux: Version Production on AUG ::
Listening Endpoints Summary
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
Services Summary
Service +ASM has instance(s)
Instance +ASM status READY has handler(s) for this service
The command completed successfully
后记
a在Oracle g RAC环境下使用dbca创建单实例数据库会自动将实例配置到缺省的监听器以及缺省端口
b在Oracle g RAC环境下对于集群监听的配置需要在grid用户下来完成这个不同于Oracle g RAC关于g RAC监听配置可参考尾部链接
c注意netca与netmgr在配置监听器时的差异由上测试可知netca不能为监听添加数据库服务即SID_LIST_<LISTENER_CNBO>项而netmgr可以实现建议使用netmgr
d动态服务注册需要配置local_listener参数要么使用DESCRIPTION等一串描述要么简化到tnsnamesora文件之中
e对于Oracle g RAC环境下单实例的非缺省监听及端口的静态注册如果不设置local_listener尽管会注册到指定的监听器但同时也会注册到缺省监听器
f对于上面e点描述的情形在非RAC环境单实例gg未曾出现过此现象应该是Oralce尽可能要求使用动态注册
