Oracle的监听器一直以来都存在一个严重的安全问题，那就是：如果不设置安全措施，那么能够访问的用户就可以远程关闭监听器
1.设置监听器密码
2.更改监听器密码

1.设置监听器密码
[oracle@jumper log]$ lsnrctl
C:\Documents and Settings\skate_db>lsnrctl
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-10月-2008 19:18:06
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
欢迎来到LSNRCTL，请键入"help"以获得信息。
LSNRCTL> set current_listener listener
Current Listener is listener
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.11)(PORT=1521)))
Password changed for listener
The command completed successfully
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.11)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Old Parameter File /opt/oracle/product/9.2.0/network/admin/listener.bak
The command completed successfully
---到此监听器已经设置好了密码，下面就测试下
LSNRCTL> service
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-01169: 监听器尚未识别口令
LSNRCTL> set password
Password:
命令执行成功
LSNRCTL> service
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
处理程序:
"DEDICATED" 已建立:0 已被拒绝:0
LOCAL SERVER
服务 "orcl9i" 包含 2 个例程。
例程 "orcl9i", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
处理程序:
"DEDICATED" 已建立:0 已被拒绝:0
LOCAL SERVER
例程 "orcl9i", 状态 READY, 包含此服务的 1 个处理程序...
处理程序:
"DEDICATED" 已建立:0 已拒绝:0 状态:ready
LOCAL SERVER
服务 "orcl9iXDB" 包含 1 个例程。
例程 "orcl9i", 状态 READY, 包含此服务的 1 个处理程序...
处理程序:
"D000" 已建立:0 已被拒绝:0 当前: 0 最大: 1002 状态: ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=skate)(PORT=3939))
命令执行成功
2.更改监听器密码
C:\Documents and Settings\skate_db>lsnrctl
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-10月-2008 19:18:06
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
欢迎来到LSNRCTL，请键入"help"以获得信息。

A：要更改监听器密码，先输入原密码以便更改成功
LSNRCTL> set password
Password:
命令执行成功
B：更改新密码
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
LISTENER的口令已更改
命令执行成功
LSNRCTL>
C：新密码起作用，输入新密码后才能运行save_config起作用
LSNRCTL> set password
Password:
命令执行成功
D：保存更改
LSNRCTL> save_config
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
保存的LISTENER配置参数。
监听器参数文件 E:\oracle9i\product\9.2.0.1\db_4\network\admin\listener.ora
旧的参数文件E:\oracle9i\product\9.2.0.1\db_4\network\admin\listener.bak
命令执行成功
LSNRCTL>

××××××××××××××××××××××××××oracle监听器加密码××××××××××××××××××××××××

数据库连接非常慢

1、故障现象
       在客户端连接服务器的时候，PL/SQL点击“登录”，需要10秒多钟才可以连接到数据库，连接后操作一切正常。
       在服务器本机用：conn username/passwd@service_name连接时也要等待10秒以上才可以登录。

在客户端用tnsping服务器，如下：
C:\Documents and Settings\Administrator>tnsping test_db_192.168.21.31 10
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-1月 -2011 23:32:21

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

已使用的参数文件:
E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.21.31)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test_db)))
OK (9730 毫秒)
OK (7700 毫秒)
OK (4770 毫秒)
OK (11110 毫秒)
OK (10810 毫秒)
OK (10770 毫秒)
OK (9500 毫秒)
OK (9370 毫秒)
OK (7820 毫秒)
OK (4450 毫秒)

C:\Documents and Settings\Administrator>

在服务器本机上tnsping服务名，如下：
bash-3.00$ tnsping test_db 10
TNS Ping Utility for Solaris: Version 9.2.0.8.0 - Production on 12-1月 -2011 23:34:28

Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.

Used parameter files:
/opt/oracle/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.21.31)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test_db)))
OK (35770 msec)
OK (10660 msec)
OK (10680 msec)
OK (10010 msec)
OK (10930 msec)
OK (10650 msec)
OK (9120 msec)
OK (10740 msec)
OK (11390 msec)
OK (11040 msec)
bash-3.00$

2、经咨询oracle工程师，他让检查$ORACLE_HOME/network/log/listener.log文件
       检查发现该文件有2.7G，把该文件mv后，重启监听，系统又自动生成新的listener.log文件，系统恢复正常。

       总结：oracle监听程序对每个登录进来的进程都会记录登录信息，这些信息就记录在$ORACLE_HOME/network/log/listener.log
文件中，这个文件太大就会导致写listener.log非常慢。所以，如果发现登录数据库非常慢的情况，就应该检查该日志文件。

3、HA双机监听配置
注：监听里用机器名，别用IP地址，或者用物理IP，用虚拟IP会无法监听第二个实例。
# LISTENER.ORA Network Configuration File: /opt/oracle1/oracle/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = hanms-nqafdb1)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle1/oracle/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = npmwnms)
      (ORACLE_HOME = /opt/oracle1/oracle/db_1)
      (SID_NAME = npmwnms)
    )
)

SID_LIST_LISTENER2 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle2/oracle/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = nmoswnms)
      (ORACLE_HOME = /opt/oracle2/oracle/db_1)
      (SID_NAME = nmoswnms)
    )
   )

how to turn on the tracing?

当我用lsnrctl status 查询db port status 时，发现：
[oracle@test admin]$ lsnrctl status testdb
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-AUG-2009 01:27:27
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(SID_NAME=testdb)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCTEST)))
STATUS of the LISTENER
------------------------
Alias                     testdb
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                05-AUG-2009 05:49:53
Uptime                    0 days 19 hr. 37 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
Listener Log File         /u01/app/oracle/oracle/product/10.2.0/db_3/network/log/testdb.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCTEST))(SID_NAME=testdb))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1666))(SID_NAME=testdb))
The listener supports no services
The command completed successfully

[oracle@test admin]$ tail -15 sqlnet.log
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1666))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=testdb)(CID=(PROGRAM=sqlplus@test)(HOST=test)(USER=oracle))))
VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
Time: 05-AUG-2009 05:07:10
Tracing not turned on.-------> how to turn on this tracing?-----------------
Tns error struct:                                                           |
    ns main err code: 12564                                                   |
    TNS-12564: TNS:connection refused                                         |
    ns secondary err code: 0                                                  |
    nt main err code: 0                                                       |
    nt secondary err code: 0                                                  |
    nt OS err code: 0
                                                      |
方法是：vi sqlnet.ora 添加以下红色部分的配置：
[oracle@test admin]$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES,hostname)
trace_level_client=16
trace_timestamp_client=true
trace_unique_client=on
trace_level_client      --- 这个参数Indicates the level at which the client program is to be traced.
# Available Values:
#       0 or OFF - No Trace output
#       4 or USER - User trace information
#       10 or ADMIN - Administration trace information
#       16 or SUPPORT - Worldwide Customer Support trace information

###########################
#trace_unique_client = ON
###########################
#
#Possible values: {ON, OFF}
#Default: OFF
#
#Purpose: Used to make each client trace file have a unique name to
# prevent each trace file from being overwritten by successive
# runs of the client program

你再次查看sqlnet.log:

[oracle@test admin]$ tail -15 sqlnet.log
Fatal NI connect error 12505, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1666))(CONNECT_DATA=(SERVER=DEDICATED)(SID=testdb)(CID=(PROGRAM=sqlplus@test)(HOST=test)(USER=oracle))))
VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
Time: 06-AUG-2009 02:45:00
Tracing to file: /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/cli_1861.trc
Tns error struct:
    ns main err code: 12564
    TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

或许trace log会对你分析问题提供一些线索。值得注意的是，每当你连一次DB，就会产生一个trace log file,所以，当你找到并解决问题会，可以将tracing log 关掉，不然会产生很多的log file.
为listener增加密码验证
在RAC中由于用到srvctl 管理，在srvctl start ...　命令时会去检查Listener的status(这个动作就等同于是执行了lsnrctl status命令来查询Listener状态)，所以这个时候会不通过(这是我目前在RAC环境加密Listener测试到挺麻烦的一个issue)，有时间再查一查。

rac-test2$lsnrctl <--- 此时是没有设置密码前的操作，可以正常作业(如status/start/stop/reload等操作)

LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 09-9??-2008 11:28:19

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-test2)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 9.2.0.8.0 - Production

Start Date 09-9??-2008 10:59:13

Uptime 0 days 0 hr. 29 min. 10 sec

Trace Level off

Security OFF

SNMP OFF

Listener Parameter File /oracle/9208/network/admin/listener.ora

Listener Log File /oracle/9208/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac-test2)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "testdb" has 1 instance(s).

Instance "testdb2", status READY, has 2 handler(s) for this service...

Service "testdb2" has 1 instance(s).

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

The command completed successfully

LSNRCTL> change_password     <---用change_password命令更改密码，若之前没有敲定过密码则在old password处直接回车跳过，在new password及reenter new password处分别输入新密码。

Old password:

New password:

Reenter new password:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-test2)(PORT=1521)))

Password changed for LISTENER

The command completed successfully

LSNRCTL> status    <--- 此时密码设定已经完毕，尝试一下status操作，收到报错提示：需要密码验证。

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-test2)(PORT=1521)))

TNS-01169: The listener has not recognized the password

LSNRCTL> set password    <--- 我们需要通过set password来验证密码，并输入之前设定的密码，验证通过方可执行相关操作。

Password:

The command completed successfully

LSNRCTL> status    <--- 可以试一下在密码验证成功后的操作结果是成功的。

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-test2)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 9.2.0.8.0 - Production

Start Date 09-9??-2008 10:59:13

Uptime 0 days 0 hr. 29 min. 36 sec

Trace Level off

Security ON

SNMP OFF

Listener Parameter File /oracle/9208/network/admin/listener.ora

Listener Log File /oracle/9208/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac-test2)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "testdb" has 1 instance(s).

Instance "testdb2", status READY, has 2 handler(s) for this service...

Service "testdb2" has 1 instance(s).

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

The command completed successfully

LSNRCTL> save_config    <--- 注意密码设定后一定要用save_config命令保存所做的设置，oracle会将这些设置的变动保留到listener.ora里面去。

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-test2)(PORT=1521)))

Saved LISTENER configuration parameters.

Listener Parameter File /oracle/9208/network/admin/listener.ora

Old Parameter File /oracle/9208/network/admin/listener.bak

The command completed successfully

LSNRCTL>

在设定了密码以及保存了设置后Listener.ora里会出现这样一条记录：

#----ADDED BY TNSLSNR 09-9-2008 11:28:52---

PASSWORDS_LISTENER = 62753F69B85AD170

#----------------------------------------------

所以，大家需要注意的是，以后重启DB后，启动Listener时需要先进入lsnrctl 命令里通过 set password命令先输入密码方可进行操作！
PS：Listener做密码设定不会影响任何client 端的东西，仅是为了防止listener遭到远程恶意作业时加的一阵密码验证过程。

ITPub博客

ORACLE LISTNER

最新文章