标签云
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,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- 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备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- 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误删除数据文件恢复
月归档:八月 2012
11GR2 Control file enqueue hold time tracking dump
如果你比较心细,可能在11.2的数据库中发现alert文件中存在存在类此下面的记录
Errors in file /oradb/diag/rdbms/offon/offon2/trace/offon2_ckpt_19660878.trc:
查看trace文件发现
*** 2012-08-01 03:36:03.520 1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117 2: 890ms (rw) file: kcrf.c line: 10012 count: 6 total: 4266ms time: 1820928 3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116 4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time: 3207607 Control file enqueue hold time tracking dump at time: 3376956 *** 2012-08-03 02:14:38.714 1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117 2: 890ms (rw) file: kcrf.c line: 10012 count: 7 total: 4953ms time: 1820928 3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116 4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time: 3207607 Control file enqueue hold time tracking dump at time: 3384212
这个类此我们在10g中看到的lgwr的警告类此,见Warning: log write time 560ms, size 3KB,其实也就是oracle对lgwr进程写入日志慢的时候的一个trace功能记录下来的.
我们这里遇到的是因为oracle对ckpt进程的trace,当control file enqueue holding time tracking size超过10的时候,就会记录到trace文件中,oracle 内部文档对于该问题的一些描述如下:
About the issue, this is the expected behavior on 11.2. New controlfile enqueue hold time tracking statistics have been added in 11.2 to aid diagnosis of controlfile transaction related performance related issues: Control File Enqueue AWR Statistics: * max cf enq hold time - The maximum amount of time in milliseconds a client has held the control file enqueue. * total cf enq hold time - The total amount of time in milliseconds all clients have held the control file enqueue. * total number of cf enq holders - The total number of times clients have held the control file enqueue. Periodically, the CKPT process dumps statistics for the top N control file enqueue holders. N defaults to 10, but can be modified with the static hidden parameter: _controlfile_enqueue_holding_time_tracking_size.The dump looks like the following: Preface: "Control file enqueue hold time tracking dump at time: [relative time]". * a. Time the client has held the control file enqueue. * b. Type of client's control file enqueue transaction - rw or ro. * c. File name where the client obtained control file enqueue. * d. Line number where the client obtained control file enqueue. * e. Number of times the client has held the control file enqueue since it became a member of the top N. * f. Total time the client has held the control file in all those times from [e]. * g. Relative time the client obtained the control file enqueue from [a].
查询数据库默认值
SQL> col value for a24 SQL> col description for a70 SQL> set linesize 180 SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 2 from x$ksppi a,x$ksppcv b 3 where a.inst_id = USERENV ('Instance') 4 and b.inst_id = USERENV ('Instance') 5 and a.indx = b.indx 6 and upper(a.ksppinm) LIKE upper('%¶m%') 7 order by name 8 / Enter value for param: _controlfile_enqueue_holding_time_tracking_size old 6: and upper(a.ksppinm) LIKE upper('%¶m%') new 6: and upper(a.ksppinm) LIKE upper('%_controlfile_enqueue_holding_time_tracking_size%') NAME VALUE DESCRIPTION -------------------------------------------------- -------- ------------------------------------------------ _controlfile_enqueue_holding_time_tracking_size 10 control file enqueue holding time tracking size
虽然在alert日志中使用Error的形式显示该错误,但是这只是一个oracle作为诊断数据库Control File Enqueue性能的一个依据,大部分情况下,我们可以选择忽略或者关闭该诊断功能.屏蔽该提示方法如下:
The way to shut off is set _controlefile_enqueue_holding_time_tracking_size = 0 then restart the database -- spfile alter system set "_controlfile_enqueue_holding_time_tracking_size"=0 scope=spfile; -- pfile _controlfile_enqueue_holding_time_tracking_size=0 Restart database
发表在 Oracle
评论关闭
ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
数据库alert日志出现如下错误
Fatal NI connect error 12537, connecting to: (LOCAL=NO) VERSION INFORMATION: TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production Time: 21-AUG-2012 09:50:15 Tracing not turned on. Tns error struct: ns main err code: 12537 TNS-12537: TNS:connection closed ns secondary err code: 12560 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 opiodr aborting process unknown ospid (15204768) as a result of ORA-609
错误的原因
The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection process before the connection/authentication process was complete. Very often, this connection abort is due to a timeout. Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds. This time limit is often inadequate for the entire connection process to complete. We have also discovered that the ORA-609 occurs frequently in installations where the database is monitored by DB Console and the Enterprise Manager agent (emagent). After the DB Console is started and as a matter of routine, the emagent will repeatedly try to connect to the target instances. We can see frequent emagent connections in the listener.log without error. However, on occasion it may have failed to complete the connection process at the database so an ORA-609 is thrown. The emagent will simply retry the connection and may be successful on the subsequent try. (Provided there is no real fault occurring at the listener or database). This temporary failure to connect will not be reported back to DB Console and there will be no indication, except for the ORA-609, that a fault occurred.
出现这个问题的主要原因是因为从10.2开始inbound connect timeout默认为60 seconds,而在很多建立连接过程可能超过这个时间从而出现类此错误,常见的诱因是DB Console 和 Enterprise Manager agent (emagent). EM会重复的尝试连接到数据库。其过程中会偶尔的出现连接超时的问题,但是接下来会继续尝试,并获得成功。这种临时的失败不会导致EM报错而只会以ora-609的形式记录在alert log中.
处理方法
For that reason, we often recommend increasing the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure. If the problem is due to connection timeouts, an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s. e.g. Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180 Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120 These settings are in seconds. Again, the default is 60.
问题跟踪方法
If the issue persists and inbound connect does not have any effect, the following steps are intended to help locate the client that may be causing the errors. 1) Suppress the TNS errors in the alert.log by setting the following listener.ora file parameter: DIAG_ADR_ENABLED_listener_name=OFF This will cause the TNS errors to be posted to the ORACLE_HOME/network/log/sqlnet.log file that is local to the database and may yield useful information about the client's address. For example, here's a snippet from a server side sqlnet.log where client address info was posted: Production Time: 15-FEB-2010 07:15:01 Fatal NI connect error 12537, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=yourhost)(Port=1521))(CONNECT_DATA=(SID=PROD1DR) (CID=(PROGRAM=sqlplus)(HOST=client_host)(USER=client)))) Observe the PROGRAM and HOST fields on the last line. This is where the connection originated. Be sure to match timestamps in the sqlnet.log with the timestamps of the alert.log errors. Once you've located the offending client, you can enable client tracing to try and determine the cause: TRACE_LEVEL_CLIENT=16 TRACE_DIRECTORY_CLIENT=<dir location> TRACE_TIMESTAMP_CLIENT=TRUE DIAG_ADR_ENABLED=off <<<<<11g or newer client requirement If you need assistance with client or server tracing, please open an SR with Global Customer Support. 2) Check the listener.log for client connections that were logged at timestamps that match the ORA-609 timestamps as they appear in the alert.log. The client information is recorded in each listener.log entry. Since this error occurs AFTER the listener has handled the connection, do not expect to see errors in the listener.log. Here's an example snippet of an incoming client connection that was posted to the listener.log: 20-JAN-2009 17:08:45 (CONNECT_DATA=(SID=orcl)(CID= (PROGRAM=D:\oracle\product\10.1.0\Db_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=myclient) Note that the exact timestamp, program name and client host will often be recorded. Again, once you've located the offending client, enable tracing (see above) to try to capture the connection failure. 3) Enable server side Oracle Net tracing and capture the TNS error along with the incoming connection. Match the PID that accompanies the ORA-609 to the server trace label. e.g. ORA-609 : opiodr aborting process unknown ospid (4799_1) *Note the PID This PID would correspond to server trace labeled: svr_4799.trc. Check the server trace for either TNS error (the 609 will not appear) and try to locate the originating client address. If assistance is needed for this investigation, please open an SR with Oracle Support. See below for instuctions on enabling Oracle Net server tracing. The following details the discovery of the source of an ORA-609 for a real case: The alert.log reports the following messages intermittently but frequently: Mon Nov 16 22:39:22 2009 ORA-609 : opiodr aborting process unknown ospid (nnnn) Enabled Oracle Net server tracing: TRACE_LEVEL_SERVER=16 TRACE_DIRECTORY_SERVER=<dir location> TRACE_TIMESTAMP_SERVER=TRUE DIAG_ADR_ENABLED=off Reloaded listener and wait for error to appear again.: ORA-609 : opiodr aborting process unknown ospid (5233_1) Note that the server trace file set that corresponded to this event was named svr_5233*.trc. Of course the timestamps of the alert.log event and the server trace creation matched as well. A review of the server trace showed only an EOF failure and the TNS-12537 error: Read unexpected EOF ERROR nserror: nsres: id=0, op=68, ns=12537 In this particular case, there was no information about the client in the trace. This is atypical for a server trace. It may be that the client aborted before all the client information was posted to the file. However, there was post in the listener.log f or an emagent connection that was established at the same point in time. Here's an excerpt from a listener.log entry where an emagent establishes a connection: PROGRAM=D:\oracle\product\10.1.0\Db_1\bin\emagent.exe) Checked the EM Agent traces and logs and discovered the following entry: Fatal NI connect error 12547, connecting to: (LOCAL=NO) VERSION INFORMATION: TNS for Solaris: Version 11.1.0.7.0 - Production Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.1.0.7.0 - Production TCP/IP NT Protocol Adapter for Solaris: Version 11.1.0.7.0 - Production Time: 16-NOV-2009 22:39:22 ****Tracing to file: /backup/sid_traces/sqlnetlog/svr_5233.trc Tns error struct: ns main err code: 12547 TNS-12547: TNS:lost contact ns secondary err code: 12560 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 ****Note the name of the server trace which contains the PID: svr_5233.trc Also, the timestamp of the agent event matches the timestamp of the alert.log error. Check the following locations for EM Agent traces. If working with support on this issue and the EM Agent is suspected, upload ALL files under: $ORACLE_HOME/sysman/log/emagent.trc < Single node agent trace location $ORACLE_HOME/host/sysman/log/emagent.trc < RAC agent trace location It was determined that in this case, the emagent was aborting the connection before it was complete and then simply reconnecting and succeeding on the subsequent try. No errors were reported in the listener log or listener trace. No errors were returned to the DB Console. There was no apparent outage of any kind. No action was taken to correct the ORA-609 in this case. It was decided that the message was informational and completely benign.
参考文档:
ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log (Doc ID 1116960.1)
Troubleshooting Guide ORA-609 : Opiodr aborting process unknown ospid (Doc ID 1121357.1)