[20130422]修改oracle监听端口.txt

[20130422]修改oracle监听端口.txt

昨天朋友想尝试修改oracle的缺省监听端口，测试没有通过，而我在我的测试机器通过，通过对比监听文件，我发现其中的差异，问题出

在动态注册以及静态注册的问题，把一些测试记录下来，便于理解动态以及静态注册监听的问题。

1.测试环境:

SQL> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

$ cd /u01/app/oracle11g/product/11.2.0/db_2/network/admin

$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle11g/product/11.2.0/db_2/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = testtest)(PORT = 1521)(RATE_LIMIT=YES))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle11g

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 10:09:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testtest)(PORT=1521)(RATE_LIMIT=YES)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                21-APR-2013 09:59:56

Uptime                    0 days 0 hr. 9 min. 15 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /u01/app/oracle11g/product/11.2.0/db_2/network/admin/listener.ora

Listener Log File         /u01/app/oracle11g/diag/tnslsnr/testtest/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testtest)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "test.com" has 1 instance(s).

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

Service "testXDB.com" has 1 instance(s).

The command completed successfully

--动态注册，状态是 READY。

2.停止监听，修改端口为1529.在启动监听看看。

$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 10:10:38

Starting /u01/app/oracle11g/product/11.2.0/db_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/oracle11g/product/11.2.0/db_2/network/admin/listener.ora

Log messages written to /u01/app/oracle11g/diag/tnslsnr/testtest/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testtest)(PORT=1529)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testtest)(PORT=1529)(RATE_LIMIT=YES)))

Start Date                21-APR-2013 10:10:38

Uptime                    0 days 0 hr. 0 min. 0 sec

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testtest)(PORT=1529)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))

The listener supports no services

SQL> alter system register;

System altered.

--执行lsnrctl status,发现显示还是

--如果这个时候远程登录，会出现：

sqlplus scott/[email protected]:1529/test.com

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

而执行sqlplus scott/[email protected]:1521/test.com，是可以连接数据库的。为什么呢？

原来数据库动态注册仅仅连接的是1521端口，而不是监听需要的1529端口。

3.修改为静态监听看看。

$ lsnrctl stop

...

SID_LIST_LISTENER =

   (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = test.com)

      (ORACLE_HOME = /u01/app/oracle11g/product/11.2.0/db_2)

      (SID_NAME = test)

      )

   )

      (ADDRESS = (PROTOCOL = TCP)(HOST = testtest)(PORT = 1529))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1529))

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 10:21:07

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testtest)(PORT=1529)))

Start Date                21-APR-2013 10:21:07

  Instance "test", status UNKNOWN, has 1 handler(s) for this service...

--可以发现的监听状态是 UNKNOWN，是静态注册。

--这样使用端口1521，以及1529都可以连上。

sqlplus scott/[email protected]:1521/test.com

--也就是讲如果使用非标准端口，要配置静态注册，这样远端机器才能使用该端口。但是这样并不能屏蔽1521端口来连接数据库。

4.如何屏蔽1521端口，也就是不让用户使用1521端口呢？

--这样必须告诉数据库注册监听时使用非1521端口。

SQL> show parameter local_listener

NAME             TYPE     VALUE

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

local_listener   string

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=testtest)(PORT=1529))' scope=memory ;

sqlplus scott/[email protected]:1529/test.com =>通过

sqlplus scott/[email protected]:1521/test.com =>ORA-12514: TNS:listener does not currently know of service requested in connect

5.再关闭静态注册看看,先关闭监听。

# SID_LIST_LISTENER =

#    (SID_LIST =

#     (SID_DESC =

#       (GLOBAL_DBNAME = test.com)

#       (ORACLE_HOME = /u01/app/oracle11g/product/11.2.0/db_2)

#       (SID_NAME = test)

#       )

#    )

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 10:44:34

Start Date                21-APR-2013 10:44:34

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 10:44:48

Uptime                    0 days 0 hr. 0 min. 14 sec

sqlplus scott/[email protected]:1529/test.com =>通过。

5.总结：

1.要使用非标准端口，可以使用静态注册。这样不要修改参数local_listener.但是动态注册依旧是1521，也就是通过1521端口也能连接

数据库。

2.也可以修改local_listener参数，让动态注册使用非标准的端口来实现。这样就无法使用1521端口。