标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,587)
- DB2 (22)
- MySQL (70)
- Oracle (1,457)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (525)
- Oracle安装升级 (83)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (13)
- PostgreSQL恢复 (3)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
- ORA-01595/ORA-600 4194处理
- 从ORA-00283 ORA-16433报错开始恢复
- 近期又遇到ORA-600 16703和ORA-702故障
- RECOVER_YOUR_DATA勒索恢复
- ORA-01033: ORACLE initialization or shutdown in progress 故障处理
- Oracle 19c/21c最新patch信息-202401
- 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
- ORA-600 kcrf_resilver_log_1故障处理
- ORA-00600: internal error code, arguments: [4193], [35191], [35263]
- mysql数据库被黑恢复—应用层面delete删除
分类目录归档:Oracle 监听
TNS-12518: TNS:listener could not hand off client connection
客户反馈业务经常性出现数据库连接异常,通过工具访问ORACLE进程报ORA-12170
通过分析发现lsnrctl status几乎hang住,tnsping延迟特别大
进一步分析监听日志发现TNS-12518: TNS:listener could not hand off client connection错误
12-MAR-2024 15:34:50 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client) (HOST=__jdbc__)(USER=Administrator))(SERVICE_NAME=ilas)) * (ADDRESS=(PROTOCOL=tcp) (HOST=ip)(PORT=52854)) * establish * ilas * 12518 TNS-12518: TNS:listener could not hand off client connection TNS-12547: TNS:lost contact TNS-12560: TNS:protocol adapter error TNS-00517: Lost contact Linux Error: 32: Broken pipe
根据经验和Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection描述,检查监听文件配置
[oracle@xff admin]$ cat listener.ora # listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:/home/u01/app/oracle/product/11.2.0/dbhome_1/bin/oraclr11.dll") ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /home/u01/app/oracle
根据经验和客户的业务进行分析,确认他们不会使用 external procedures方式访问数据库,直接修改监听配置
[oracle@xff admin]$ cat listener.ora # listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = xff) (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1) (GLOBAL_DBNAME = xff) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /home/u01/app/oracle
然后reload配置,再使用lsnrctl status查看结果秒出,tnsping也非常快
让客户测试应用也恢复正常,一切ok,问题在最小修改的情况下解决,和最初供应商建议的重装系统,双机,数据库等解决方案大大简化
ORA-01034 ORA-27101故障分析
客户的数据库通过监听访问报ORA-01034 ORA-27101错误无法正常使用:
SQL> conn app/xxxxx@192.168.129.145/orcl ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory Process ID: 0 Session ID: 0 Serial number: 0 SQL> conn app/xxxxx Connected.
检查数据库状态和tnsping都正常
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> !tnsping orcl TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2020 22:11:49 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.129.1) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (0 msec)
检查主机之间ping操作也正常:
[XIFENFEI@DB1 trace]$ [XIFENFEI@DB1 trace]$ ping 192.168.129.1 PING 192.168.129.1 (192.168.129.1) 56(84) bytes of data. 64 bytes from 192.168.129.1: icmp_seq=1 ttl=64 time=0.025 ms 64 bytes from 192.168.129.1: icmp_seq=2 ttl=64 time=0.032 ms 64 bytes from 192.168.129.1: icmp_seq=3 ttl=64 time=0.034 ms ^C --- 192.168.129.1 ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2318ms rtt min/avg/max/mdev = 0.025/0.030/0.034/0.006 ms
检查数据库和监听配置:
[XIFENFEI@DB1 trace]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 22:13:50 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cell_offloadgroup_name string db_file_name_convert string db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string oracle lock_name_space string log_file_name_convert string processor_group_name string service_names string orcl SQL> SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [XIFENFEI@DB1 trace]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2020 22:15:16 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 18-OCT-2020 22:05:04 Uptime 0 days 0 hr. 10 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /usr/local/oracle/diag/tnslsnr/DB1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.129.1)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [XIFENFEI@DB1 trace]$ cat /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = orcl) (ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1) (GLOBAL_DBNAME= orcl) ) (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.129.1) (PORT = 1521)) ) ) ADR_BASE_LISTENER = /usr/local/oracle [XIFENFEI@DB1 trace]$ [XIFENFEI@DB1 trace]$
这里看到数据库配置和监听配置不匹配,因此需要调整。
[XIFENFEI@DB1 admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 22:21:16 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn app/xxxxx@192.168.129.145/orcl Connected. SQL> SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [XIFENFEI@DB1 admin]$
利用VNCR修复CVE-2012-1675漏洞
随着对安全的重视,TNS Listener远程数据投毒漏洞(CVE-2012-1675)被很多单位要求进行整改,而且级别是高危
如果是11.2.0.4之前版本,特别是在rac环境中,如果要整改该问题相对比较麻烦,需要通过配置Oracle wallet来实现,配置比较复杂,而且还要重启实例,影响比较大,具体参考:Using Class of Secure Transport (COST) to Restrict Instance Registration in Oracle RAC (Doc ID 1340831.1),对于单机环境直接参考以前文章:Oracle Database Server ‘TNS Listener’远程数据投毒漏洞(CVE-2012-1675)的解决方案.从11.2.0.4开始Oracle引入了Valid Node Checking For Registration (VNCR)新特性,可以通过简单的配置即可完成该漏洞修复
在listener.ora文件中增加(grid/oracle用户)
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=ON REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(hisdb1,hisdb2)
重启监听
LSNRCTL>set current_listener listener_name LSNRCTL>stop LSNRCTL>start
验证是否生效
1.在远程机器尝试远程注册
alter system set remote_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.115.15)(PORT=1521))' scope=memory;
2.观察监听日志
类似这样证明已经生效.
补充说明
VALID_NODE_CHECKING_REGISTRATION_listener_name Values: OFF/0 - Disable VNCR//禁用VNCR,此功能不会对注册过来的service进行check; ON/1/LOCAL - The default. Enable VNCR. All local machine IPs can register. 启用VNCR,默认只允许本机的所有IP的服务注册到本监听,可通过REGISTRATION_INVITED_NODES参数添加其他有必要的服务器; SUBNET/2 - All machines in the subnet are allowed registration.//指定子网内的服务器可以注册过来 REGISTRATION_INVITED_NODES_listener-name 控制允许链接过来的节点,可以通过IP地址/主机名/网段来指定 Values are valid IPs, valid hosts, a subnet using CIDR notation (for ip4/6), or wildcard (*) for ipv4. For example:REGISTRATION_INVITED_NODES_Listener=(net-vm1, 127.98.45.209, 127.42.5.*) Note that when an INVITED list is set, it will automatically include the machine's local IP in the list. There is no need to include it. --11.2.0.4和12c不一样之处 在12.1 RAC数据库上,listener的参数VALID_NODE_CHECKING_REGISTRATION_listener_name默认设置为SUBNET / 2, 即子网中的所有计算机都允许注册.所以12c默认不能解决CVE-2012-1675漏洞
参考文档
Valid Node Checking For Registration (VNCR) (Doc ID 1600630.1)
How to Enable VNCR on RAC Database to Register only Local Instances (Doc ID 1914282.1)