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
LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 09-9??-2008 11:28:19
Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.
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
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
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-test2)(PORT=1521)))
TNS-01169: The listener has not recognized the password
LSNRCTL> 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
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博客 ” ,链接:http://blog.itpub.net/751371/viewspace-715954/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/751371/viewspace-715954/