分类目录归档：Oracle 监听

Oracle Database Server ‘TNS Listener’远程数据投毒漏洞(CVE-2012-1675)的解决方案

发表于 2018 年 11 月 2 日 由 惜分飞

根据oracle mos官方描述,该问题需要打patch和配置同步进行,这篇主要提供单机Oracle Database Server ‘TNS Listener’远程数据投毒漏洞(CVE-2012-1675)的解决方案,参考文档Using Class of Secure Transport (COST) to Restrict Instance Registration (Doc ID 1453883.1),因为文档本身描述比较繁琐,这里对其进行简单总结:
数据库版本要求
包含12880299补丁,最低版本要求(高于以下版本即可)
12880299

通过$ORACL_HOME/OPatch/opatch lsinventory 命令获取版本信息，大于等于上述文档版本即可，具体版本对应关系参考：数据库补丁对应关系
listener.ora文件配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
     # (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  --注释掉，一般不会使用ipc，绝大部分应用使用tcp连接数据库
    )
  )

ADR_BASE_LISTENER = /home/u01/app/oracle
SECURE_REGISTER_LISTENER = (TCP)  --增加该参数

重启监听

lsnrctl stop
lsnrctl start

验证配置生效

1.查看数据库监听日志
$ORACLE_BASE/diag/tnslsnr/主机名/listener/trace/listener.log

2.在局域网中找一台数据库服务器(单机环境)，登录数据库
Sqlplus / as sysdba
Show parameter remote_listener;--记录该值（一般是空）
alter system set remote_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=ip)(PORT=1521))' scope=memory;
IP为修改为上述监听的数据库服务器地址

3.再次查看监听日志，会发现类似记录（亦可在数据库中之执行alter system register观察）
02-NOV-2018 20:37:53 * service_register_NSGR * 1194
TNS-01194: The listener command did not arrive in a secure transport
02-NOV-2018 20:37:56 * service_register_NSGR * 1194
TNS-01194: The listener command did not arrive in a secure transport
观察一会儿，表明我们的监听配置生效，数据库拒绝远程监听，修复该漏洞.
如果没有出现类似记录，请核查数据库版本补丁是否满足要求，listener.ora参数配置是否正确

4.还原remote_listener参数以前值
sqlplus / as sysdba
alter system set remote_listener='2中查询记录的值' scope=memory;

对于rac环境,配置比较复杂,参考mos文档:Using Class of Secure Transport (COST) to Restrict Instance Registration in Oracle RAC (Doc ID 1340831.1)

发表在 Oracle 监听 | 标签为 , | 评论关闭

windows平台listener.log超过4G导致监听异常

发表于 2015 年 11 月 19 日 由 惜分飞

今天有个朋友咨询生产库非常慢,应用无法连接数据库,通过分析是由于win 32位 数据库11.2.0.1版本 文件系统为ntfs,但是listener.log为4G,导致监听工作不正常.
tnsping几乎hang住
tnsping

lsnrctl status 也几乎hang住
lsnrctl-status
直接ping ip正常
证明不是网络问题,导致监听异常,现在判断是监听问题
ping-ip

监听日志超过4G
listener_file
关闭监听日志
log_status_off
监听正常
关闭监听日志之后,监听恢复正常
2
文件系统为ntfs格式
这里证明，不是由于文件系统格式的限制ntfs最大文件允许64T,这里4G肯定不是系统的极限
ntfs

相关文章
Bug 9879101 : THE CONNECT THROUGH LISTENER WAS SLOW WHEN LISTNER LOG GROWED 4GB
WINDOWS: Listener Hangs & Lsnrctl Commands Are Slow or Hang

发表在 Oracle 监听 | 标签为 , , , | 评论关闭

dns解析导致opiodr aborting process unknown ospid (7266) as a result of ORA-609类似错误

发表于 2015 年 10 月 29 日 由 惜分飞

数据库alert日志中出现大量类似opiodr aborting process unknown ospid (7266) as a result of ORA-609错误

Tue Oct 27 07:51:10 2015
opiodr aborting process unknown ospid (7266) as a result of ORA-609
Tue Oct 27 08:16:56 2015
opiodr aborting process unknown ospid (7660) as a result of ORA-609
Tue Oct 27 08:17:16 2015
opiodr aborting process unknown ospid (7666) as a result of ORA-609
Tue Oct 27 08:21:50 2015
opiodr aborting process unknown ospid (7725) as a result of ORA-609
Tue Oct 27 08:37:43 2015
opiodr aborting process unknown ospid (7940) as a result of ORA-609
Tue Oct 27 08:51:25 2015
opiodr aborting process unknown ospid (8126) as a result of ORA-609
Tue Oct 27 09:07:16 2015
opiodr aborting process unknown ospid (8359) as a result of ORA-609
Tue Oct 27 09:26:09 2015
opiodr aborting process unknown ospid (8600) as a result of ORA-609

测试tnsping

[oracle@Sql admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:40:55

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (20120 msec)
[oracle@Sql admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:42:35

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (41600 msec)

hosts文件配置

[oracle@Sql admin]$ vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 
192.168.2.162 Sql.xifenfei.com
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

同时测试lsnrctl start/stop/status都很慢,但是ping localhost非常快,证明问题在监听之上.search 关键字lsnrctl slow/tnsping slow关键字发现有相关文档:
Bug 8307164 : TNSPING 11G USING DNS AND NOT HOSTS FILE
Listener Startup or Connections Hang in 11g (Doc ID 803838.1)
11g: Remote Connections Take Very Long to Establish (Doc ID 856820.1)
通过这些文档,可以知道在oracle 10g中解析主机名使用gethostbyname()函数,11g中使用了使用getaddrinfo()函数.这个函数对于/etc/nsswitch.conf的调用和ethostbyname()不一样,从而出现此类问题.根据官方建议:

Linux
hosts: files [NOTFOUND=continue] dns OR  hosts:files/dns

HPUX and Solaris
ipnodes: files [NOTFOUND=continue] dns

本次解决方法
因为对于没有使用到dns服务器的数据库环境而言,没有必要配置这玩意,给自己埋雷，估计是当时安装yum之时没有清理掉

vi /etc/nsswitch.conf
hosts:      files nds  -->hosts:      files

再次测试tnsping,lsnrctl等命令,速度ok

[oracle@Sql"]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:58:30

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (10 msec)

对于此类监听慢的问题，可以通过对client/server端trace进一步跟踪

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client/server
TRACE_DIRECTORY_CLIENT = [any valid directory path]
TRACE_TIMESTAMP_CLIENT = ON
DIAG_ADR_ENABLED=off
发表在 Oracle 监听 | 标签为 , , | 评论关闭