从Oracle客户端连接到服务器,除了使用静态注册方式之外,9i后续的版本支持动态注册服务,同时可以将服务注册到非默认的端口。将服务注册到非默认端口可以提高数据库系统的安全性。 关于Oracle从客户端连接到数据库的详细配置方法可以参考:配置ORACLE客户端连接到数据库
一、服务器端的配置
1.配置非默认的listener.ora,即侦听器为list2,且端口号改为,可以使用netca,netmgr或vim等来完成,配置的内容如下:
[oracle@oradb admin]$ cat listener.ora
# listener.ora NetworkConfigurationFile: /u01/app/oracle/10g/network/admin/listener.ora
# Generatedby Oracleconfiguration tools.
LIST2=
(DESCRIPTION=
(ADDRESS= (PROTOCOL= TCP)(HOST=oradb.robinson.com)(PORT= 1421))
)
2.配置tnsnames.ora,同样可以使用netca,netmgr或vim等来完成,配置的内容如下:
[oracle@oradb admin]$ cat tnsnames.ora
# tnsnames.ora NetworkConfigurationFile: /u01/app/oracle/10g/network/admin/tnsnames.ora
# Generatedby Oracleconfiguration tools.
list2=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL= TCP)(HOST=oradb.robinson.com)(PORT= 1421))
)
(CONNECT_DATA=
(SERVICE_NAME= orcl.robinson.com)
)
)
3.启动非默认的侦听器
[oracle@oradb admin]$ lsnrctl start list2
LSNRCTLfor Linux: Version 10.2.0.1.0- Production on 15-SEP-201019:49:31
Copyright(c) 1991,2005, Oracle. All rights reserved.
Starting/u01/app/oracle/10g/bin/tnslsnr:please wait...
TNSLSNRfor Linux: Version 10.2.0.1.0- Production
System parameterfile is /u01/app/oracle/10g/network/admin/listener.ora
Log messages writtento/u01/app/oracle/10g/network/log/list2.log
Listeningon: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb.robinson.com)(PORT=1421)))
Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb.robinson.com)(PORT=1421)))
STATUSof the LISTENER
------------------------
Alias list2
Version TNSLSNRfor Linux: Version 10.2.0.1.0- Production
Start Date 15-SEP-2010 19:49:31
Uptime 0 days 0 hr. 0 min. 0sec
TraceLevel off
Security ON: Local OS Authentication
SNMP OFF
Listener ParameterFile /u01/app/oracle/10g/network/admin/listener.ora
ListenerLog File /u01/app/oracle/10g/network/log/list2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb.robinson.com)(PORT=1421)))
The listener supportsnoservices
The command completed successfully
4.设定local_listener参数
SQL> show parameterlocal_listener;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
local_listener string
SQL>alter system set local_listener= 'list2';
System altered.
5.查看侦听器list2的状态,orcl.robinson.com服务已成功注册
如果没有成功注册,执行这个语句altersystemregister;
[oracle@oradb admin]$ lsnrctl status list2
----------------------------中间部分省略-----------------------------------
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb.robinson.com)(PORT=1421)))
Services Summary...
Service "orcl.robinson.com" has 1instance(s).
Instance"orcl", status READY, has 1 handler(s)for thisservice...
Service "orclXDB.robinson.com" has1 instance(s).
Instance"orcl", status READY, has 1 handler(s)for thisservice...
Service "orcl_XPT.robinson.com"has 1 instance(s).
Instance"orcl", status READY, has 1 handler(s)for thisservice...
The command completed successfully
二、客户端配置
客户端为OS为Winxp,Oracle客户端软件为Oracle 10g 客户端
可以使用Winxp上的配置工具配置tnsnames.ora,也可以将服务器上的tnsnames.ora配置文件复制到本地OS或将内容更新到本地的tnsnames.ora文件
演示中为从服务器上的tnsnames.ora的内容更新到本地客户端的tnsnames.ora
C:/>sqlplus/nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二9月14 20:05:232010
Copyright(c) 1982,2005, Oracle. All rights reserved.
SQL> conn scott/tiger@list2;
Connected.
将客户端的网络服务名更新为abc,如下
abc=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL= TCP)(HOST=oradb.robinson.com)(PORT= 1421))
)
(CONNECT_DATA=
(SERVICE_NAME= orcl.robinson.com)
)
)
尝试再次连接,可以成功连接,由此可以看出跟网络服务名无关,重要的连接描述信息
SQL> conn system/redhat@abc
Connected.
三、常见故障(ORA-00119和ORA-00132错误解决)
1.启动实例时收到如下错误提示
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'list2'
方法一
--从描述中可以看到参数local_listener设置的值list2无效
SQL> ho oerr ora00132
00132, 00000, "syntax error or unresolved network name'%s'"
//*Cause: Listener address hassyntax erroror cannot beresolved.
//*Action:If a networkname isspecified,check that it corresponds
// to an entry in TNSNAMES.ORAor otheraddress repository
// as configured for your system. Make sure that the entry
// is syntacticallycorrect.
--根据提示检查服务器端的tnsnames.ora
[oracle@oradb admin]$ cat tnsnames.ora
# tnsnames.ora NetworkConfiguration File:/u01/app/oracle/10g/network/admin/tnsnames.ora
# Generatedby Oracleconfiguration tools.
list= --此处为list,将其改为list2
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL= TCP)(HOST=oradb.robinson.com)(PORT= 1421))
)
(CONNECT_DATA=
(SERVICE_NAME= orcl.robinson.com)
)
)
--改为list2后,实例正常启动
方法二
也可以先使用pfile启动,在pfile中指定spfile的路径,然后再增加一行local_listener=''如下:
spfile='/u01/app/oracle/10g/dbs/spfileorcl.ora'
local_listener= ''
启动实例后再使用altersystemset local_listener= '' scope= spfile;来修改spfile 文件,重启即可
关于pfile的常见故障可以参考:
Oracle参数文件
SPFILE错误导致数据库无法启动(ORA-01565)
又一例SPFILE设置错误导致数据库无法启动
2.修改参数local_listener时出现的错误提示
假定此处中的侦听为默认的侦听器,但端口号使用的是非默认端口,而是使用的.
服务器端的listener.ora和tnsnames.ora的配置信息如下:
[oracle@oradb admin]$ cat listener.ora
# listener.ora NetworkConfiguration File:/u01/app/oracle/10g/network/admin/listener.ora
# Generatedby Oracleconfiguration tools.
LISTENER=
(DESCRIPTION=
(ADDRESS= (PROTOCOL= TCP)(HOST=oradb.robinson.com)(PORT= 1522))
)
[oracle@oradb admin]$ cat tnsnames.ora
# tnsnames.ora NetworkConfiguration File:/u01/app/oracle/10g/network/admin/tnsnames.ora
# Generatedby Oracleconfiguration tools.
orcl.robinson.com=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL= TCP)(HOST=oradb.robinson.com)(PORT= 1522))
)
(CONNECT_DATA=
(SERVICE_NAME= orcl.robinosn.com)
)
)
对于非默认端口,我们需要修改local_listener,如下
SQL>alter system set local_listener='listener';--修改时收到了ORA-02097、ORA-00119错误
alter system set local_listener ='listener'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified becausespecified valueis invalid
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'listener'
对于默认的侦听器,同样需要修改服务器端tnsnames.ora的网络服务名,使得与侦听器的名字相同
则tnsnames.ora修改后的内容为:
listener=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL= TCP)(HOST=oradb.robinson.com)(PORT= 1522))
)
(CONNECT_DATA=
(SERVICE_NAME= orcl.robinosn.com)
)
)
再次修改local_listener参数
SQL>alter system set local_listener= 'listener';
System altered.
修改完毕后,可以看到服务已经注册
Services Summary...
Service "orcl.robinson.com" has 1instance(s).
Instance"orcl", status READY, has 1 handler(s)for thisservice...
Service "orclXDB.robinson.com" has1 instance(s).
Instance"orcl", status READY, has 1 handler(s)for thisservice...
Service "orcl_XPT.robinson.com"has 1 instance(s).
Instance"orcl", status READY, has 1 handler(s)for thisservice...
The command completed successfully
四、总结
1.对于使用非默认端口的动态注册,需要手工指定端口号。
2.不管服务器段使用的是缺省的侦听器还是非缺省的侦听器,只要使用到了非默认端口,则需要修改服务器端的tnsnames.ora中的网络服务名使其与侦听器的名字相同。
3.修改local_listener 参数设置本地侦听器的名称。因为pmon进程仅仅会轮询检查默认号端口的侦听器,并在查找到时进行注册。
alter systemset local_listener ='listener_name';
4.成功修改local_listener参数后,如果还没有看到注册的服务名,使用下面的命令来实现注册(10g不需要)
alter system register;
5.客户端的tnsnames.ora可以使用服务器端tnsnames.ora中的网络服务名,也可以使用其他的名字,即可以任意命名。