配置非默认端口的动态服务注册

--==============================

-- 配置非默认端口的动态服务注册

--==============================

 

    从Oracle客户端连接到服务器，除了使用静态注册方式之外，9i后续的版本支持动态注册服务，同时可以将服务注册到非默认的端口。将服务注册到非默认端口可以提高数据库系统的安全性。

    关于Oracle从客户端连接到数据库的详细配置方法可以参考：配置ORACLE客户端连接到数据库 

   

一、服务器端的配置

    1.配置非默认的listener.ora，即侦听器为list2，且端口号改为，可以使用netca，netmgr或vim等来完成，配置的内容如下：

        [oracle@oradb admin]$ cat listener.ora

        # listener.ora Network Configuration File: /u01/app/oracle/10g/network/admin/listener.ora

        # Generated by Oracle configuration 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 Network Configuration File: /u01/app/oracle/10g/network/admin/tnsnames.ora

        # Generated by Oracle configuration 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

 

        LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-SEP-2010 19:49:31

 

        Copyright (c) 1991, 2005, Oracle.  All rights reserved.

 

        Starting /u01/app/oracle/10g/bin/tnslsnr: please wait...

 

        TNSLSNR for Linux: Version 10.2.0.1.0 - Production

        System parameter file is /u01/app/oracle/10g/network/admin/listener.ora

        Log messages written to /u01/app/oracle/10g/network/log/list2.log

        Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb.robinson.com)(PORT=1421)))

 

        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb.robinson.com)(PORT=1421)))

        STATUS of the LISTENER

        ------------------------

        Alias                     list2

        Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

        Start Date                15-SEP-2010 19:49:31

        Uptime                    0 days 0 hr. 0 min. 0 sec

        Trace Level               off

        Security                  ON: Local OS Authentication

        SNMP                      OFF

        Listener Parameter File   /u01/app/oracle/10g/network/admin/listener.ora

        Listener Log File         /u01/app/oracle/10g/network/log/list2.log

        Listening Endpoints Summary...

          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb.robinson.com)(PORT=1421)))

        The listener supports no services

        The command completed successfully     

   

    4.设定local_listener参数

        SQL> show parameter local_listener;

 

        NAME                                 TYPE        VALUE

        ------------------------------------ ----------- ------------------------------

        local_listener                       string

        SQL> alter system set local_listener = 'list2';

 

        System altered.

 

    5.查看侦听器list2的状态，orcl.robinson.com服务已成功注册

      如果没有成功注册，执行这个语句alter system register;

        [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 1 instance(s).

          Instance "orcl", status READY, has 1 handler(s) for this service...

        Service "orclXDB.robinson.com" has 1 instance(s).

          Instance "orcl", status READY, has 1 handler(s) for this service...

        Service "orcl_XPT.robinson.com" has 1 instance(s).

          Instance "orcl", status READY, has 1 handler(s) for this service...

        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:23 2010

 

        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 ora 00132

                00132, 00000, "syntax error or unresolved network name '%s'"

                // *Cause:  Listener address has syntax error or cannot be resolved.

                // *Action: If a network name is specified, check that it corresponds

                //          to an entry in TNSNAMES.ORA or other address repository

                //          as configured for your system. Make sure that the entry

                //          is syntactically correct.

           

            --根据提示检查服务器端的tnsnames.ora

                [oracle@oradb admin]$ cat tnsnames.ora

                # tnsnames.ora Network Configuration File: /u01/app/oracle/10g/network/admin/tnsnames.ora

                # Generated by Oracle configuration 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 = ''

启动实例后再使用alter system set 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 Network Configuration File: /u01/app/oracle/10g/network/admin/listener.ora

            # Generated by Oracle configuration tools.

 

            LISTENER =

              (DESCRIPTION =

                (ADDRESS = (PROTOCOL = TCP)(HOST = oradb.robinson.com)(PORT = 1522))

              )

 

            [oracle@oradb admin]$ cat tnsnames.ora

            # tnsnames.ora Network Configuration File: /u01/app/oracle/10g/network/admin/tnsnames.ora

            # Generated by Oracle configuration 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 because specified value is 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 1 instance(s).

              Instance "orcl", status READY, has 1 handler(s) for this service...

            Service "orclXDB.robinson.com" has 1 instance(s).

              Instance "orcl", status READY, has 1 handler(s) for this service...

            Service "orcl_XPT.robinson.com" has 1 instance(s).

              Instance "orcl", status READY, has 1 handler(s) for this service...

            The command completed successfully     

四、总结

       

    1.对于使用非默认端口的动态注册，需要手工指定端口号。

    2.不管服务器段使用的是缺省的侦听器还是非缺省的侦听器，只要使用到了非默认端口，则需要修改服务器端的tnsnames.ora中的网络服务名使其与侦听器的名字相同。

    3.修改local_listener 参数设置本地侦听器的名称。因为pmon进程仅仅会轮询检查默认号端口的侦听器,并在查找到时进行注册。

        alter system set local_listener = 'listener_name';

    4.成功修改local_listener参数后，如果还没有看到注册的服务名，使用下面的命令来实现注册(10g不需要)

        alter system register;

    5.客户端的tnsnames.ora可以使用服务器端tnsnames.ora中的网络服务名，也可以使用其他的名字，即可以任意命名。

 

五、更多参考

 

Oracle 冷备份

 

SPFILE错误导致数据库无法启动

 

Oracle 用户、对象权限、系统权限

 

Oracle 角色、配置文件

 

  Oracle 联机重做日志文件(ONLINE LOG FILE)

 

  Oracle 控制文件(CONTROLFILE)

 

  Oracle 表空间与数据文件

 

Oracle 归档日志