1.错误描述
rac 集群节点2 写归档错误，查看告警日志
<msg time='2025-08-03T18:02:56.692+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='rac2' host_addr='92.178.12.163' module='DBMS_SCHEDULER'
pid='177429'>
<txt>Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7FBC24D8F000] [PC:0x49AE183, __intel_new_memcpy()+5395] [flags: 0x0, count: 1]
</txt>
</msg>
<msg time='2025-08-03T18:02:56.779+08:00' org_id='oracle' comp_id='rdbms'
msg_id='609256791' type='INCIDENT_ERROR' group='Access Violation'
level='1' host_id='rac2' host_addr='92.178.12.163'
prob_key='ORA 7445 [__intel_new_memcpy()+5395]' upstream_comp='' downstream_comp=''
ecid='' errid='426293' detail_path='/u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc'>
<txt>Errors in file /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc (incident=426293):
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
</txt>
</msg>
<msg time='2025-08-03T18:02:56.782+08:00' org_id='oracle' comp_id='rdbms'
msg_id='dbgexProcessError:1205:3370026720' type='TRACE' level='16'
host_id='rac2' host_addr='92.178.12.163'>
<txt>Incident details in: /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426293/yajj2_j001_177429_i426293.trc
</txt>
</msg>
<msg time='2025-08-03T18:02:56.783+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='rac2' host_addr='92.178.12.163' module='DBMS_SCHEDULER'
pid='177429'>
<txt>Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
</txt>
</msg>
<msg time='2025-08-03T18:02:56.791+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='rac2' host_addr='92.178.12.163' module='DBMS_SCHEDULER'
pid='177429'>
<txt>Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x68] [PC:0x8AB20BE, pfrtra()+66] [flags: 0x0, count: 2]
</txt>
</msg>
<msg time='2025-08-03T18:02:56.875+08:00' org_id='oracle' comp_id='rdbms'
msg_id='2015736762' type='INCIDENT_ERROR' group='Access Violation'
level='1' host_id='rac2' host_addr='92.178.12.163'
prob_key='ORA 7445 [pfrtra()+66]' upstream_comp='' downstream_comp='PLSQL_Code_Execution'
ecid='' errid='426294' detail_path='/u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc'>
<txt>Errors in file /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc (incident=426294):
ORA-07445: exception encountered: core dump [pfrtra()+66] [SIGSEGV] [ADDR:0x68] [PC:0x8AB20BE] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
</txt>
</msg>
<msg time='2025-08-03T18:02:56.878+08:00' org_id='oracle' comp_id='rdbms'
msg_id='dbgexProcessError:1205:3370026720' type='TRACE' level='16'
host_id='rac2' host_addr='92.178.12.163'>
<txt>Incident details in: /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426294/yajj2_j001_177429_i426294.trc
</txt>
</msg>
<msg time='2025-08-03T18:02:56.879+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='rac2' host_addr='92.178.12.163' module='DBMS_SCHEDULER'
pid='177429'>
<txt>Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
</txt>
</msg>
<msg time='2025-08-03T18:02:57.517+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='rac2' host_addr='92.178.12.163' module='DBMS_SCHEDULER'
pid='177429'>
<txt>Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x899E538, kgscDump()+520] [flags: 0x0, count: 3]
</txt>
</msg>
<msg time='2025-08-03T18:02:57.518+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='rac2' host_addr='92.178.12.163' module='DBMS_SCHEDULER'
pid='177429'>
<txt>Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
</txt>
</msg>
查看trace日志，截取错误信息：
Archived Log entry 1845100 added for thread 2 sequence 969570 ID 0xfe827259 dest 1:
Sun Aug 03 18:02:56 2025
Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7FBC24D8F000] [PC:0x49AE183, __intel_new_memcpy()+5395] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc (incident=426293):
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
Incident details in: /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426293/yajj2_j001_177429_i426293.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x68] [PC:0x8AB20BE, pfrtra()+66] [flags: 0x0, count: 2]
Errors in file /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc (incident=426294):
ORA-07445: exception encountered: core dump [pfrtra()+66] [SIGSEGV] [ADDR:0x68] [PC:0x8AB20BE] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
Incident details in: /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426294/yajj2_j001_177429_i426294.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x899E538, kgscDump()+520] [flags: 0x0, count: 3]
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2.分析过程
查看数据库版本
SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
OWB 11.2.0.4.0
Oracle Application Express 3.2.1.00.12
Oracle Enterprise Manager 11.2.0.4.0
OLAP Catalog 11.2.0.4.0
Spatial 11.2.0.4.0
Oracle Multimedia 11.2.0.4.0
Oracle XML Database 11.2.0.4.0
Oracle Text 11.2.0.4.0
Oracle Expression Filter 11.2.0.4.0
Oracle Rules Manager 11.2.0.4.0
Oracle Workspace Manager 11.2.0.4.0
COMP_NAME VERSION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
Oracle Database Catalog Views 11.2.0.4.0
Oracle Database Packages and Types 11.2.0.4.0
JServer JAVA Virtual Machine 11.2.0.4.0
Oracle XDK 11.2.0.4.0
Oracle Database Java Packages 11.2.0.4.0
OLAP Analytic Workspace 11.2.0.4.0
Oracle OLAP API 11.2.0.4.0
Oracle Real Application Clusters 11.2.0.4.0
19 rows selected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
查看 yajj2_j001_177429.trc 文件
查看 trace错误日志文件：
Trace file /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rac2
Release: 4.1.12-124.16.4.el6uek.x86_64
Version: #2 SMP Thu Jun 14 18:55:52 PDT 2018
Machine: x86_64
Instance name: yajj2
Redo thread mounted by this instance: 2
Oracle process number: 135
Unix process pid: 177429, image: oracle@rac2 (J001)
*** 2025-08-03 18:02:56.691
*** SESSION ID:(1621.859) 2025-08-03 18:02:56.691
*** CLIENT ID:() 2025-08-03 18:02:56.691
*** SERVICE NAME:(SYS$USERS) 2025-08-03 18:02:56.691
*** MODULE NAME:(DBMS_SCHEDULER) 2025-08-03 18:02:56.691
*** ACTION NAME:(ORA$AT_OS_OPT_SY_33509) 2025-08-03 18:02:56.691
Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7FBC24D8F000] [PC:0x49AE183, __intel_new_memcpy()+5395] [flags: 0x0, count: 1]
Incident 426293 created, dump file: /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426293/yajj2_j001_177429_i426293.trc
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
Incident 426294 created, dump file: /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426294/yajj2_j001_177429_i426294.trc
ORA-07445: exception encountered: core dump [pfrtra()+66] [SIGSEGV] [ADDR:0x68] [PC:0x8AB20BE] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid
Dump file /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc
***** Internal heap ERROR kghfrempty:ds addr=0x7fbc1ee19010 ds=0xc0cc6e0 *****
***** Dump of memory around addr 0x7fbc1ee19010:
******************************************************
Hla: 255
dbkedDefDump: errors signalled while dumping diagnostics
ORA-00600: internal error code, arguments: [dbkc_free_RDBMS_diagctx-1], [600], [], [], [], [], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [pfrtra()+66] [SIGSEGV] [ADDR:0x68] [PC:0x8AB20BE] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
Shadow_Core_Dump = PARTIAL
First chunk is corrupted
Corrupted stack chunk header at location 0x7fbc1edebf88
***** Dump of memory around addr 0x7fbc1edebf88:
7FBC1EDEAF80 00000000 00000000 [........]
7FBC1EDEAF90 00000000 00000000 00000000 00000000 [................]
Repeat 510 times
7FBC1EDECF80 00000000 00000000 [........]
Current stack segment pointer = 0x7fbc1ede9e88
Corrupted stack chunk header at location 0x7fbc1ede9ea0
***** Dump of memory around addr 0x7fbc1ede9ea0:
7FBC1EDE8EA0 00000000 00000000 00000000 00000000 [................]
Repeat 254 times
7FBC1EDE9E90 1EDEBFA8 00007FBC 00000000 00000000 [................]
7FBC1EDE9EA0 00000000 00000000 00000000 00000000 [................]
Repeat 255 times
*** 2025-08-03 18:02:57.541
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x2ABE2C8, kpuping()+236] [flags: 0x0, count: 4]
Registers:
%rax: 0x00007fbc1ee9e9f0 %rbx: 0x0000000000000000 %rcx: 0x00007fbc24a96ed0
%rdx: 0x0000000000000000 %rdi: 0x0000000000000000 %rsi: 0x00007fbc24a94470
%rsp: 0x00007ffd1a72d270 %rbp: 0x00007ffd1a72d2a0 %r8: 0x0000000000004028
%r9: 0x0000000000000008 %r10: 0x00007fbc24935250 %r11: 0x0000000000000000
%r12: 0x00007fbc24a94390 %r13: 0x00007fbc24a94470 %r14: 0x0000000000000013
%r15: 0x0000000000000000 %rip: 0x0000000002abe2c8 %efl: 0x0000000000010246
kpuping()+219 (0x2abe2b7) movzbl 0x4(%rax),%edx
kpuping()+223 (0x2abe2bb) test $0x4,%dl
kpuping()+226 (0x2abe2be) jnz 0x2abea9d
kpuping()+232 (0x2abe2c4) mov 0x10(%rax),%rdi
> kpuping()+236 (0x2abe2c8) mov 0x18(%rdi),%edx
kpuping()+239 (0x2abe2cb) test $0x40000,%edx
kpuping()+245 (0x2abe2d1) jnz 0x2abe957
kpuping()+251 (0x2abe2d7) mov %r12,%rdi
kpuping()+254 (0x2abe2da) mov $0x93,%esi
ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL
*** 2025-08-03 18:02:57.544
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x98723A4, kohdee()+296] [flags: 0x0, count: 5]
Registers:
%rax: 0x0000000000000000 %rbx: 0x00007fbc1ef0b3a0 %rcx: 0x0000000000000000
%rdx: 0x0000000000000000 %rdi: 0x000000000c0cc9e0 %rsi: 0x00007fbc24b88050
%rsp: 0x00007ffd1a72fe70 %rbp: 0x00007ffd1a72fec0 %r8: 0x00007fbc24b87e08
%r9: 0x00007fbc24667b48 %r10: 0x0000000000002466 %r11: 0x0000000000000000
%r12: 0x000000000c0cc9e0 %r13: 0x00007fbc24b88050 %r14: 0x00000000098670da
%r15: 0x00007ffd1a7300e8 %rip: 0x00000000098723a4 %efl: 0x0000000000010246
kohdee()+278 (0x9872392) movq $0x0,0x8(%rbx)
kohdee()+286 (0x987239a) xor %eax,%eax
kohdee()+288 (0x987239c) mov %ax,0x4(%rbx)
kohdee()+292 (0x98723a0) mov %ax,0x2(%rbx)
> kohdee()+296 (0x98723a4) mov %rdx,0x8(%rcx)
kohdee()+300 (0x98723a8) lea 0x40(%rbx),%r10
kohdee()+304 (0x98723ac) lea 0x20(%r13),%r9
kohdee()+308 (0x98723b0) mov 0x48(%rbx),%rdi
kohdee()+312 (0x98723b4) mov 0x40(%rbx),%rsi
ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL
*********START PLSQL RUNTIME DUMP************
***Got internal error Exception caught in pl/sql run-time while running PLSQL***
***Got ORA-600 while running PLSQL***
ssexhd: failed 6 times to record signal=11, crashing the process...
Shadow_Core_Dump = PARTIAL
*** MODULE NAME:(DBMS_SCHEDULER) 2025-08-03 18:02:56.691 ---调度异常
*** ACTION NAME:(ORA$AT_OS_OPT_SY_33509) 2025-08-03 18:02:56.691 ---自动作业故障
发现job异常停止：
i Oracle数据库不直接支持LIMIT子句，但可通过ROWNUM伪列或Oracle 12c及以上版本的FETCH FIRST/NEXT语法实现类似功能，用于限制查询结果的行数。
SQL> select a.job_name, a.LOG_ID, a.status, a.actual_start_date
from (select t.job_name, t.LOG_ID, t.status, t.actual_start_date
from dba_scheduler_job_run_details t
where t.job_name like 'ORA$AT_OS_OPT_SY_%'
order by t.actual_start_date desc) a
where rownum <= 100;
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_33509 286044 STOPPED 03-AUG-25 06.02.21.783210 PM PRC
ORA$AT_OS_OPT_SY_33489 286025 STOPPED 03-AUG-25 02.01.46.994629 PM PRC
ORA$AT_OS_OPT_SY_33469 285985 STOPPED 03-AUG-25 10.01.11.572776 AM PRC
ORA$AT_OS_OPT_SY_33449 285970 STOPPED 03-AUG-25 06.00.06.107113 AM PRC
ORA$AT_OS_OPT_SY_33429 285925 STOPPED 02-AUG-25 10.09.30.992833 PM PRC
ORA$AT_OS_OPT_SY_33409 285909 STOPPED 02-AUG-25 06.08.55.131000 PM PRC
ORA$AT_OS_OPT_SY_33389 285865 STOPPED 02-AUG-25 02.08.21.205795 PM PRC
ORA$AT_OS_OPT_SY_33369 285845 STOPPED 02-AUG-25 10.07.42.314778 AM PRC
ORA$AT_OS_OPT_SY_33349 285808 STOPPED 02-AUG-25 06.00.07.619728 AM PRC
ORA$AT_OS_OPT_SY_33329 285768 STOPPED 01-AUG-25 10.00.08.830719 PM PRC
ORA$AT_OS_OPT_SY_33142 285654 STOPPED 31-JUL-25 10.00.02.693195 PM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_33139 285559 STOPPED 30-JUL-25 10.00.02.576710 PM PRC
ORA$AT_OS_OPT_SY_33136 285464 STOPPED 29-JUL-25 10.00.02.559855 PM PRC
ORA$AT_OS_OPT_SY_33309 285352 STOPPED 28-JUL-25 10.00.06.331382 PM PRC
ORA$AT_OS_OPT_SY_33289 285271 STOPPED 27-JUL-25 10.04.54.463228 PM PRC
ORA$AT_OS_OPT_SY_33269 285259 STOPPED 27-JUL-25 06.04.24.059766 PM PRC
ORA$AT_OS_OPT_SY_33249 285225 STOPPED 27-JUL-25 02.03.54.262433 PM PRC
ORA$AT_OS_OPT_SY_33229 285205 STOPPED 27-JUL-25 10.03.23.411452 AM PRC
ORA$AT_OS_OPT_SY_33133 285165 STOPPED 27-JUL-25 06.00.01.615738 AM PRC
ORA$AT_OS_OPT_SY_33209 285125 STOPPED 26-JUL-25 10.01.46.422149 PM PRC
ORA$AT_OS_OPT_SY_33189 285099 STOPPED 26-JUL-25 06.01.14.461833 PM PRC
ORA$AT_OS_OPT_SY_33169 285065 STOPPED 26-JUL-25 02.00.47.373371 PM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_33149 285045 STOPPED 26-JUL-25 10.00.28.750102 AM PRC
ORA$AT_OS_OPT_SY_33130 285010 STOPPED 26-JUL-25 06.00.02.273633 AM PRC
ORA$AT_OS_OPT_SY_33127 284973 STOPPED 25-JUL-25 10.00.02.637804 PM PRC
ORA$AT_OS_OPT_SY_33069 283145 STOPPED 06-JUL-25 02.04.44.750841 PM PRC
ORA$AT_OS_OPT_SY_32286 277008 FAILED 16-MAY-25 10.00.00.858271 PM PRC
ORA$AT_OS_OPT_SY_29943 259823 STOPPED 01-JAN-25 10.00.02.314782 PM PRC
ORA$AT_OS_OPT_SY_29271 255152 STOPPED 25-NOV-24 10.00.02.261436 PM PRC
ORA$AT_OS_OPT_SY_28832 251748 STOPPED 29-OCT-24 10.00.02.309652 PM PRC
ORA$AT_OS_OPT_SY_23867 222838 STOPPED 12-APR-24 10.00.02.317965 PM PRC
ORA$AT_OS_OPT_SY_23853 222348 STOPPED 07-APR-24 10.04.58.365153 PM PRC
ORA$AT_OS_OPT_SY_23792 221798 STOPPED 03-APR-24 10.00.02.301363 PM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_23751 221322 STOPPED 30-MAR-24 10.10.35.486613 PM PRC
ORA$AT_OS_OPT_SY_23739 221157 STOPPED 29-MAR-24 10.00.00.918445 PM PRC
ORA$AT_OS_OPT_SY_23705 220647 STOPPED 24-MAR-24 10.07.14.493543 PM PRC
ORA$AT_OS_OPT_SY_23521 220043 STOPPED 19-MAR-24 10.00.01.422258 PM PRC
ORA$AT_OS_OPT_SY_23486 218748 SUCCEEDED 10-MAR-24 06.00.02.261216 AM PRC
ORA$AT_OS_OPT_SY_23484 218716 SUCCEEDED 09-MAR-24 10.03.41.612009 PM PRC
ORA$AT_OS_OPT_SY_23482 218674 SUCCEEDED 09-MAR-24 06.02.46.132322 PM PRC
ORA$AT_OS_OPT_SY_23480 218650 SUCCEEDED 09-MAR-24 02.01.50.782848 PM PRC
ORA$AT_OS_OPT_SY_23478 218630 SUCCEEDED 09-MAR-24 10.00.55.349478 AM PRC
ORA$AT_OS_OPT_SY_23475 218607 SUCCEEDED 09-MAR-24 06.00.08.555882 AM PRC
ORA$AT_OS_OPT_SY_23472 218550 SUCCEEDED 08-MAR-24 10.00.02.352015 PM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_23469 218456 SUCCEEDED 07-MAR-24 10.00.02.374714 PM PRC
ORA$AT_OS_OPT_SY_23450 218342 SUCCEEDED 06-MAR-24 10.00.00.916644 PM PRC
ORA$AT_OS_OPT_SY_23427 218246 SUCCEEDED 05-MAR-24 10.00.01.235230 PM PRC
ORA$AT_OS_OPT_SY_23424 218132 SUCCEEDED 04-MAR-24 10.00.01.744439 PM PRC
ORA$AT_OS_OPT_SY_23429 218002 SUCCEEDED 03-MAR-24 10.04.12.244955 AM PRC
ORA$AT_OS_OPT_SY_23421 217958 SUCCEEDED 03-MAR-24 06.00.01.566027 AM PRC
ORA$AT_OS_OPT_SY_23419 217906 SUCCEEDED 02-MAR-24 10.04.42.846479 PM PRC
ORA$AT_OS_OPT_SY_23417 217884 SUCCEEDED 02-MAR-24 06.03.46.855247 PM PRC
ORA$AT_OS_OPT_SY_23415 217840 SUCCEEDED 02-MAR-24 02.02.51.382285 PM PRC
ORA$AT_OS_OPT_SY_23413 217820 SUCCEEDED 02-MAR-24 10.01.57.365091 AM PRC
ORA$AT_OS_OPT_SY_23410 217777 SUCCEEDED 02-MAR-24 06.00.07.405420 AM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_23407 217721 SUCCEEDED 01-MAR-24 10.00.02.275660 PM PRC
ORA$AT_OS_OPT_SY_23404 217605 SUCCEEDED 29-FEB-24 10.00.01.705112 PM PRC
ORA$AT_OS_OPT_SY_23401 217491 SUCCEEDED 28-FEB-24 10.00.01.416014 PM PRC
ORA$AT_OS_OPT_SY_23398 217397 SUCCEEDED 27-FEB-24 10.00.01.254156 PM PRC
ORA$AT_OS_OPT_SY_23395 217263 SUCCEEDED 26-FEB-24 10.00.01.902725 PM PRC
ORA$AT_OS_OPT_SY_23393 217173 SUCCEEDED 25-FEB-24 10.11.21.326791 PM PRC
ORA$AT_OS_OPT_SY_23391 217131 SUCCEEDED 25-FEB-24 06.10.26.013153 PM PRC
ORA$AT_OS_OPT_SY_23389 217106 SUCCEEDED 25-FEB-24 02.09.30.498431 PM PRC
ORA$AT_OS_OPT_SY_23387 217086 SUCCEEDED 25-FEB-24 10.08.35.403089 AM PRC
ORA$AT_OS_OPT_SY_23384 217041 SUCCEEDED 25-FEB-24 06.00.02.242667 AM PRC
ORA$AT_OS_OPT_SY_23381 216948 SUCCEEDED 24-FEB-24 06.00.06.726404 AM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_23378 216909 SUCCEEDED 23-FEB-24 10.00.00.816274 PM PRC
ORA$AT_OS_OPT_SY_23375 216816 SUCCEEDED 22-FEB-24 10.00.00.915572 PM PRC
ORA$AT_OS_OPT_SY_23372 216702 SUCCEEDED 21-FEB-24 10.00.01.568155 PM PRC
ORA$AT_OS_OPT_SY_23369 216568 SUCCEEDED 20-FEB-24 10.00.02.353059 PM PRC
ORA$AT_OS_OPT_SY_23286 216454 SUCCEEDED 19-FEB-24 10.00.02.311747 PM PRC
ORA$AT_OS_OPT_SY_23349 216362 SUCCEEDED 18-FEB-24 10.03.56.356352 PM PRC
ORA$AT_OS_OPT_SY_23329 216342 SUCCEEDED 18-FEB-24 06.03.04.402281 PM PRC
ORA$AT_OS_OPT_SY_23309 216282 SUCCEEDED 18-FEB-24 02.02.06.477017 PM PRC
ORA$AT_OS_OPT_SY_23289 216262 SUCCEEDED 18-FEB-24 10.01.08.978961 AM PRC
ORA$AT_OS_OPT_SY_23283 216200 SUCCEEDED 18-FEB-24 06.00.02.271096 AM PRC
ORA$AT_OS_OPT_SY_23281 216168 SUCCEEDED 17-FEB-24 10.06.36.625579 PM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_23279 216126 SUCCEEDED 17-FEB-24 06.05.40.767517 PM PRC
ORA$AT_OS_OPT_SY_23277 216102 SUCCEEDED 17-FEB-24 02.04.46.244882 PM PRC
ORA$AT_OS_OPT_SY_23275 216082 SUCCEEDED 17-FEB-24 10.03.50.244195 AM PRC
ORA$AT_OS_OPT_SY_23272 216059 SUCCEEDED 17-FEB-24 06.00.07.886325 AM PRC
ORA$AT_OS_OPT_SY_23269 215983 SUCCEEDED 16-FEB-24 10.00.02.330370 PM PRC
ORA$AT_OS_OPT_SY_23266 215869 SUCCEEDED 15-FEB-24 10.00.01.701884 PM PRC
ORA$AT_OS_OPT_SY_23263 215775 SUCCEEDED 14-FEB-24 10.00.01.769047 PM PRC
ORA$AT_OS_OPT_SY_23260 215641 SUCCEEDED 13-FEB-24 10.00.02.300338 PM PRC
ORA$AT_OS_OPT_SY_23257 215527 SUCCEEDED 12-FEB-24 10.00.02.384537 PM PRC
ORA$AT_OS_OPT_SY_23255 215437 SUCCEEDED 11-FEB-24 10.03.30.259545 PM PRC
ORA$AT_OS_OPT_SY_23253 215415 SUCCEEDED 11-FEB-24 06.02.34.314100 PM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_23251 215370 SUCCEEDED 11-FEB-24 02.01.40.362965 PM PRC
ORA$AT_OS_OPT_SY_23249 215350 SUCCEEDED 11-FEB-24 10.00.44.501099 AM PRC
ORA$AT_OS_OPT_SY_23246 215305 SUCCEEDED 11-FEB-24 06.00.02.284029 AM PRC
ORA$AT_OS_OPT_SY_23244 215273 SUCCEEDED 10-FEB-24 10.07.55.387543 PM PRC
ORA$AT_OS_OPT_SY_23242 215231 SUCCEEDED 10-FEB-24 06.06.59.891993 PM PRC
ORA$AT_OS_OPT_SY_23240 215207 SUCCEEDED 10-FEB-24 02.06.04.714614 PM PRC
ORA$AT_OS_OPT_SY_23238 215187 SUCCEEDED 10-FEB-24 10.05.09.203409 AM PRC
ORA$AT_OS_OPT_SY_23235 215164 SUCCEEDED 10-FEB-24 06.00.08.534880 AM PRC
ORA$AT_OS_OPT_SY_23232 215107 SUCCEEDED 09-FEB-24 10.00.02.265036 PM PRC
ORA$AT_OS_OPT_SY_23229 214993 SUCCEEDED 08-FEB-24 10.00.02.314341 PM PRC
ORA$AT_OS_OPT_SY_23226 214899 SUCCEEDED 07-FEB-24 10.00.00.791255 PM PRC
JOB_NAME LOG_ID STATUS ACTUAL_START_DATE
---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_23223 214763 SUCCEEDED 06-FEB-24 10.00.00.737100 PM PRC
100 rows selected.
SQL>
若需要兼容 Oracle 12c+ 的语法，也可使用 FETCH FIRST 100 ROWS ONLY 替代 rownum，更直观：
select t.job_name, t.LOG_ID, t.status, t.actual_start_date
from dba_scheduler_job_run_details t
where t.job_name like 'ORA$AT_OS_OPT_SY_%'
order by t.actual_start_date desc
fetch first 100 rows only;
视图DBA_AUTOTASK_JOB_HISTORY来跟踪自动作业情况:
SQL> COL CLIENT_NAME FOR A35
SQL> COL JOB_NAME FOR A25
SQL> COL JOB_DURATION FOR A20
SQL> COL JOB_STATUS FOR A10
SQL> SELECT CLIENT_NAME
,JOB_NAME
,JOB_START_TIME
,JOB_DURATION
,JOB_STATUS
,JOB_ERROR
FROM DBA_AUTOTASK_JOB_HISTORY
WHERE JOB_NAME LIKE 'ORA$AT_OS_OPT_SY_33509'; 2 3 4 5 6 7 8
CLIENT_NAME JOB_NAME JOB_START_TIME JOB_DURATION JOB_STATUS JOB_ERROR
---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------ ----------
auto optimizer stats collection ORA$AT_OS_OPT_SY_33509 03-AUG-25 06.02.21.783210 PM PRC +000 00:10:42 STOPPED 0
SQL>
确认最近时间段，自动作业“auto optimizer stats collection”故障。
自动收集作业早已失效建议禁用：
SQL> select client_name,status from DBA_AUTOTASK_TASK;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
SQL> select client_name,status,WINDOW_GROUP from dba_autotask_client;
CLIENT_NAME STATUS WINDOW_GROUP
---------------------------------------------------------------- -------- ----------------------------------------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS
auto space advisor ENABLED ORA$AT_WGRP_SA
sql tuning advisor ENABLED ORA$AT_WGRP_SQ
SQL>
task可以被不同/多个client使用。因此，即使我们禁用了client，DBA_AUTOTASK_TASK仍然可以显示启用状态。
在当前版本中，task只有一个client。但是在oracle的未来版本中，task可以有多个客户机，因此DBA_AUTOTASK_TASK中的状态将显示为启用。
所以检查状态的正确方法是通过DBA_AUTOTASK_CLIENT。
启用和禁止维护任务,使用DBMS_AUTO_ADMIN pl/sql包来启用或禁用任务：
--- 禁用任务：
BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
--- 启用任务：
BEGIN
dbms_auto_task_admin.enable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
ORACLE 11g/12c中默认有9个统计信息自动收集窗口。其中WEEKNIGHT_WINDOW、WEEKEND_WINDOW是为了兼容之前的版本所保留的窗口，默认不启用。
其他7个启用的统计信息自动收集窗口分为
工作日模式（周一周五）和周末模式（周六周日）。
工作日模式的收集窗口为22:00-2:00(+1)，持续时间为4小时；
周末模式的收集窗口为6:00-2:00(+1)，持续时间为20小时。
当前收集窗口参数设置：
select window_name,repeat_interval,duration,enabled,active from dba_scheduler_windows;
分析该日志文件，发现日志中的信息 Dump of memory 的信息中有 EXAMINE 表的名字和字段，怀疑 auto optimizer stats collection 任务对该表进行分析时，出错。
查看 yajj2_j001_177429_i426293.trc
查看 yajj2_j001_177429_i426293.trc 进一步确定问题：
Dump file /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426293/yajj2_j001_177429_i426293.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rac2
Release: 4.1.12-124.16.4.el6uek.x86_64
Version: #2 SMP Thu Jun 14 18:55:52 PDT 2018
Machine: x86_64
Instance name: yajj2
Redo thread mounted by this instance: 2
Oracle process number: 135
Unix process pid: 177429, image: oracle@rac2 (J001)
*** 2025-08-03 18:02:56.782
*** SESSION ID:(1621.859) 2025-08-03 18:02:56.782
*** CLIENT ID:() 2025-08-03 18:02:56.782
*** SERVICE NAME:(SYS$USERS) 2025-08-03 18:02:56.782
*** MODULE NAME:(DBMS_SCHEDULER) 2025-08-03 18:02:56.782
*** ACTION NAME:(ORA$AT_OS_OPT_SY_33509) 2025-08-03 18:02:56.782
Dump continued from file: /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
========= Dump for incident 426293 (ORA 7445 [__intel_new_memcpy()+5395]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7FBC24D8F000] [PC:0x49AE183, __intel_new_memcpy()+5395] [flags: 0x0, count: 1]
Registers:
%rax: 0x00007fbc1ecd32e8 %rbx: 0x00007fbc1ed075c8 %rcx: 0x00007fbc1f814f70
%rdx: 0x00007fbc24d8f040 %rdi: 0x00007fbc1ecd32e8 %rsi: 0x00007fbc2424d3b8
%rsp: 0x00007ffd1a7255a8 %rbp: 0x00007ffd1a7256b0 %r8: 0x00000000ff4b992b
%r9: 0x0000000000b00000 %r10: 0x000000000000b5b3 %r11: 0x00000000049ace10
%r12: 0x0000000000000000 %r13: 0x0000005ea9677090 %r14: 0x00007fbc242542e8
%r15: 0x00000000ffffffff %rip: 0x00000000049ae183 %efl: 0x0000000000010206
__intel_new_memcpy()+5376 (0x49ae170) lea 0x40(%rcx),%rcx
__intel_new_memcpy()+5380 (0x49ae174) lea 0x40(%rdx),%rdx
__intel_new_memcpy()+5384 (0x49ae178) lea -0x40(%r8),%r8
__intel_new_memcpy()+5388 (0x49ae17c) prefetchnta 0x180(%rdx)
> __intel_new_memcpy()+5395 (0x49ae183) movdqu -0x40(%rdx),%xmm0
__intel_new_memcpy()+5400 (0x49ae188) movdqu -0x30(%rdx),%xmm1
__intel_new_memcpy()+5405 (0x49ae18d) cmp $64,%r8
__intel_new_memcpy()+5409 (0x49ae191) movntdq %xmm0,-0x40(%rcx)
__intel_new_memcpy()+5414 (0x49ae196) movntdq %xmm1,-0x30(%rcx)
*** 2025-08-03 18:02:56.787
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=0hxaqmw3hjakf) -----
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("HCBJ")),to_char(substrb(dump(min("HCBJ"),16,0,32),1,120)),to_char(substrb(dump(max("HCBJ"),16,0,32),1,120)),to_char(count("HCSJ")),to_char(substrb(dump(min("HCSJ"),16,0,32),1,120)),to_char(substrb(dump(max("HCSJ"),16,0,32),1,120)),to_char(count("RLZL")),to_char(substrb(dump(min("RLZL"),16,0,32),1,120)),to_char(substrb(dump(max("RLZL"),16,0,32),1,120)),to_char(count("SFBDZS")),to_char(substrb(dump(min("SFBDZS"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDZS"),16,0,32),1,120)),to_char(count("ZXZS")),to_char(substrb(dump(min("ZXZS"),16,0,32),1,120)),to_char(substrb(dump(max("ZXZS"),16,0,32),1,120)),to_char(count("ZDZS")),to_char(substrb(dump(min("ZDZS"),16,0,32),1,120)),to_char(substrb(dump(max("ZDZS"),16,0,32),1,120)),to_char(count("SFBDZZ")),to_char(substrb(dump(min("SFBDZZ"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDZZ"),16,0,32),1,120)),to_char(count("ZXZZ")),to_char(substrb(dump(min("ZXZZ"),16,0,32),1,120)),to_char(substrb(dump(max("ZXZZ"),16,0,32),1,120)),to_char(count("ZDZZ")),to_char(substrb(dump(min("ZDZZ"),16,0,32),1,120)),to_char(substrb(dump(max("ZDZZ"),16,0,32),1,120)),to_char(count("SFBDCWKC")),to_char(substrb(dump(min("SFBDCWKC"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDCWKC"),16,0,32),1,120)),to_char(count("CWKCFW")),to_char(substrb(dump(min("CWKCFW"),16,0,32),1,120)),to_char(substrb(dump(max("CWKCFW"),16,0,32),1,120)),to_char(count("SFBDZZL")),to_char(substrb(dump(min("SFBDZZL"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDZZL"),16,0,32),1,120)),to_char(count("ZXZZL")),to_char(substrb(dump(min("ZXZZL"),16,0,32),1,120)),to_char(substrb(dump(max("ZXZZL"),16,0,32),1,120)),to_char(count("ZDZZL")),to_char(substrb(dump(min("ZDZZL"),16,0,32),1,120)),to_char(substrb(dump(max("ZDZZL"),16,0,32),1,120)),to_char(count("SJLX")),to_char(substrb(dump(min("SJLX"),16,0,32),1,120)),to_char(substrb(dump(max("SJLX"),16,0,32),1,120)),to_char(count("SFBDCLLX")),to_char(substrb(dump(min("SFBDCLLX"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDCLLX"),16,0,32),1,120)),to_char(count("SFBDCJ")),to_char(substrb(dump(min("SFBDCJ"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDCJ"),16,0,32),1,120)),to_char(count("SFBDHPZL")),to_char(substrb(dump(min("SFBDHPZL"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDHPZL"),16,0,32),1,120)),to_char(count("SFBDRLZL")),to_char(substrb(dump(min("SFBDRLZL"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDRLZL"),16,0,32),1,120)),to_char(count("SFBDPFBZ")),to_char(substrb(dump(min("SFBDPFBZ"),16,0,32),1,120)),to_char(substrb(dump(max("SFBDPFBZ"),16,0,32),1,120)),to_char(count("PFBZ")),to_char(substrb(dump(min("PFBZ"),16,0,32),1,120)),to_char(substrb(dump(max("PFBZ"),16,0,32),1,120)),to_char(count("YJTSBJ")),to_char(substrb(dump(min("YJTSBJ"),16,0,32),1,120)),to_char(substrb(dump(max("YJTSBJ"),16,0,32),1,120)),to_char(count("GZBH")),to_char(substrb(dump(min("GZBH"),16,0,32),1,120)),to_char(substrb(dump(max("GZBH"),16,0,32),1,120)),to_char(count("GZLX")),to_char(substrb(dump(min("GZLX"),16,0,32),1,120)),to_char(substrb(dump(max("GZLX"),16,0,32),1,120)),to_char(count("GZMC")),to_char(substrb(dump(min("GZMC"),16,0,32),1,120)),to_char(substrb(dump(max("GZMC"),16,0,32),1,120)),to_char(count("GLBM")),to_char(substrb(dump(min("GLBM"),16,0,32),1,120)),to_char(substrb(dump(max("GLBM"),16,0,32),1,120)),to_char(count("WFSCBJ")),to_char(substrb(dump(min("WFSCBJ"),16,0,32),1,120)),to_char(substrb(dump(max("WFSCBJ"),16,0,32),1,120)),to_char(count("WFXW")),to_char(substrb(dump(min("WFXW"),16,0,32),1,120)),to_char(substrb(dump(max("WFXW"),16,0,32),1,120)),to_char(count("CFJG")),to_char(substrb(dump(min("CFJG"),16,0,32),1,120)),to_char(substrb(dump(max("CFJG"),16,0,32),1,120)),to_char(count("JGDW")),to_char(substrb(dump(min("JGDW"),16,0,32),1,120)),to_char(substrb(dump(max("JGDW"),16,0,32),1,120)),to_char(count("CLLX")),to_char(substrb(dump(min("CLLX"),16,0,32),1,120)),to_char(substrb(dump(max("CLLX"),16,0,32),1,120)),to_char(count("DLLX")),to_char(substrb(dump(min("DLLX"),16,0,32),1,120)),to_char(substrb(dump(max("DLLX"),16,0,32),1,120)),to_char(count("DLDM")),to_char(substrb(dump(min("DLDM"),16,0,32),1,120)),to_char(substrb(dump(max("DLDM"),16,0,32),1,120)),to_char(count("KSRQ")),to_char(substrb(dump(min("KSRQ"),16,0,32),1,120)),to_char(substrb(dump(max("KSRQ"),16,0,32),1,120)),to_char(count("JSRQ")),to_char(substrb(dump(min("JSRQ"),16,0,32),1,120)),to_char(substrb(dump(max("JSRQ"),16,0,32),1,120)),to_char(count("HPXZFS")),to_char(substrb(dump(min("HPXZFS"),16,0,32),1,120)),to_char(substrb(dump(max("HPXZFS"),16,0,32),1,120)),to_char(count("BDHPT")),to_char(substrb(dump(min("BDHPT"),16,0,32),1,120)),to_char(substrb(dump(max("BDHPT"),16,0,32),1,120)),to_char(count("GZRSFJX")),to_char(substrb(dump(min("GZRSFJX"),16,0,32),1,120)),to_char(substrb(dump(max("GZRSFJX"),16,0,32),1,120)),to_char(count("GZRJXSD")),to_char(substrb(dump(min("GZRJXSD"),16,0,32),1,120)),to_char(substrb(dump(max("GZRJXSD"),16,0,32),1,120)),to_char(count("ZMSFJX")),to_char(substrb(dump(min("ZMSFJX"),16,0,32),1,120)),to_char(substrb(dump(max("ZMSFJX"),16,0,32),1,120)),to_char(count("ZMJXSD")),to_char(substrb(dump(min("ZMJXSD"),16,0,32),1,120)),to_char(substrb(dump(max("ZMJXSD"),16,0,32),1,120)),to_char(count("JJRSFJX")),to_char(substrb(dump(min("JJRSFJX"),16,0,32),1,120)),to_char(substrb(dump(max("JJRSFJX"),16,0,32),1,120)),to_char(count("JJRJXSD")),to_char(substrb(dump(min("JJRJXSD"),16,0,32),1,120)),to_char(substrb(dump(max("JJRJXSD"),16,0,32),1,120)),to_char(count("SYXZ")),to_char(substrb(dump(min("SYXZ"),16,0,32),1,120)),to_char(substrb(dump(max("SYXZ"),16,0,32),1,120)),to_char(count("CLDJRQ")),to_char(substrb(dump(min("CLDJRQ"),16,0,32),1,120)),to_char(substrb(dump(max("CLDJRQ"),16,0,32),1,120)),to_char(count("JLZT")),to_char(substrb(dump(min("JLZT"),16,0,32),1,120)),to_char(substrb(dump(max("JLZT"),16,0,32),1,120)),to_char(count("CJJG")),to_char(substrb(dump(min("CJJG"),16,0,32),1,120)),to_char(substrb(dump(max("CJJG"),16,0,32),1,120)),to_char(count("CJR")),to_char(substrb(dump(min("CJR"),16,0,32),1,120)),to_char(substrb(dump(max("CJR"),16,0,32),1,120)),to_char(count("CJSJ")),to_char(substrb(dump(min("CJSJ"),16,0,32),1,120)),to_char(substrb(dump(max("CJSJ"),16,0,32),1,120)),to_char(count("GXSJ")),to_char(substrb(dump(min("GXSJ"),16,0,32),1,120)),to_char(substrb(dump(max("GXSJ"),16,0,32),1,120)),to_char(count("JXGZSM")),to_char(substrb(dump(min("JXGZSM"),16,0,32),1,120)),to_char(substrb(dump(max("JXGZSM"),16,0,32),1,120)),to_char(count("BZ")),to_char(substrb(dump(min("BZ"),16,0,32),1,120)),to_char(substrb(dump(max("BZ"),16,0,32),1,120)),to_char(count("CSBJ")),to_char(substrb(dump(min("CSBJ"),16,0,32),1,120)),to_char(substrb(dump(max("CSBJ"),16,0,32),1,120)),to_char(count("BJCSBJ")),to_char(substrb(dump(min("BJCSBJ"),16,0,32),1,120)),to_char(substrb(dump(max("BJCSBJ"),16,0,32),1,120)),to_char(count("WHCLFS")),to_char(substrb(dump(min("WHCLFS"),16,0,32),1,120)),to_char(substrb(dump(max("WHCLFS"),16,0,32),1,120)),to_char(count("WHCLMX")),to_char(substrb(dump(min("WHCLMX"),16,0,32),1,120)),to_char(substrb(dump(max("WHCLMX"),16,0,32),1,120)),to_char(count("RQGZ")),to_char(substrb(dump(min("RQGZ"),16,0,32),1,120)),to_char(substrb(dump(max("RQGZ"),16,0,32),1,120)),to_char(count("RQGZXQ")),to_char(substrb(dump(min("RQGZXQ"),16,0,32),1,120)),to_char(substrb(dump(max("RQGZXQ"),16,0,32),1,120)),to_char(count("XXHPZL")),to_char(substrb(dump(min("XXHPZL"),16,0,32),1,120)),to_char(substrb(dump(max("XXHPZL"),16,0,32),1,120)) from "RM"."TRFF_RULE_BREAK_CONF" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,ACL,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
PENALLNN: ((((ub1 **) (preg[4]))[0])) fails slrac! Value is (nil).
penexc: bad structure: preg[DL1]=0x7fbc1eea1670, PF_UMP=(nil), PF_ULP=(nil)
Because of this, no line numbers will be available for this native-mode library unit on the stack.
PENALLNN: ((((ub1 **) (preg[4]))[0])) fails slrac! Value is (nil).
PENALLNN: ((((ub1 **) (preg[4]))[0])) fails slrac! Value is (nil).
PENALLNN: ((((ub1 **) (preg[4]))[0])) fails slrac! Value is (nil).
PENALLNN: (preg[4]) fails slrac! Value is (nil).
penexc: bad structure: preg[DL1]=(nil), PF_UMP=(nil), PF_ULP=(nil)
Because of this, no line numbers will be available for this native-mode library unit on the stack.
*** 2025-08-03 18:02:56.787
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x68] [PC:0x8AB20BE, pfrtra()+66] [flags: 0x0, count: 2]
查看 yajj2_j001_177429_i426294.trc
查看相关的trace 文件
Dump file /u01/app/oracle/diag/rdbms/yajj/yajj2/incident/incdir_426294/yajj2_j001_177429_i426294.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rac2
Release: 4.1.12-124.16.4.el6uek.x86_64
Version: #2 SMP Thu Jun 14 18:55:52 PDT 2018
Machine: x86_64
Instance name: yajj2
Redo thread mounted by this instance: 2
Oracle process number: 135
Unix process pid: 177429, image: oracle@rac2 (J001)
*** 2025-08-03 18:02:56.878
*** SESSION ID:(1621.859) 2025-08-03 18:02:56.878
*** CLIENT ID:() 2025-08-03 18:02:56.878
*** SERVICE NAME:(SYS$USERS) 2025-08-03 18:02:56.878
*** MODULE NAME:(DBMS_SCHEDULER) 2025-08-03 18:02:56.878
*** ACTION NAME:(ORA$AT_OS_OPT_SY_33509) 2025-08-03 18:02:56.878
Dump continued from file: /u01/app/oracle/diag/rdbms/yajj/yajj2/trace/yajj2_j001_177429.trc
ORA-07445: exception encountered: core dump [pfrtra()+66] [SIGSEGV] [ADDR:0x68] [PC:0x8AB20BE] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid
========= Dump for incident 426294 (ORA 7445 [pfrtra()+66]) ========
*** 2025-08-03 18:02:57.516
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x899E538, kgscDump()+520] [flags: 0x0, count: 3]
DDE recursive invocation exceeded max allowed
DDE was called in a 'No Invocation Mode'
DDE encountered the following error:
ORA-49203: Too many recursive DDE invocations
ORA-07445: exception encountered: core dump [kgscDump()+520] [SIGSEGV] [ADDR:0x0] [PC:0x899E538] [SI_KERNEL(general_protection)] []
ORA-07445: exception encountered: core dump [pfrtra()+66] [SIGSEGV] [ADDR:0x68] [PC:0x8AB20BE] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5395] [SIGSEGV] [ADDR:0x7FBC24D8F000] [PC:0x49AE183] [Invalid permissions for mapped object] []
----- Beginning of Customized Incident Dump(s) -----
----- End of Customized Incident Dump(s) -----
ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL
3.分析结果：
auto optimizer stats collection 任务对该表 EXAMINE 进行分析时，出错。
Dump of memory 导致 dbs下产生了 core.XXXX 文件，消耗了大量的存储空间，导致该节点的实例，归档日志无法写入。
[root@rac2 dbs]# ls -lah
total 770G
drwxr-xr-x 2 oracle oinstall 4.0K Aug 6 09:54 .
drwxr-xr-x 74 oracle oinstall 4.0K Jul 15 2020 ..
-rw------- 1 oracle asmadmin 404G Aug 3 06:17 core.127664
-rw------- 1 oracle asmadmin 366G Aug 3 18:25 core.177445
-rw-rw---- 1 oracle asmadmin 1.6K Aug 6 09:54 hc_yajj2.dat
-rw-r--r-- 1 root root 3.3K Mar 4 2021 hg.sh
-rw-r--r-- 1 oracle oinstall 2.8K Jul 15 2020 init.ora
-rw-r----- 1 oracle oinstall 35 Mar 4 2021 inityajj2.ora
-rw-r----- 1 oracle oinstall 1.5K Jul 15 2020 orapwyajj2
-rw-r----- 1 oracle asmadmin 57M Nov 22 2021 snapcf_yajj2.f
-rw-r----- 1 oracle asmadmin 4.5K Jul 25 11:08 spfileyajj2.ora
[root@rac2 dbs]#
4.解决办法
清理冗余文件：删除 dbs 目录下的 core.XXXX 文件，释放存储空间。
重建问题表：重建 EXAMINE 表，修复可能的表结构或数据异常。
观察作业状态：重建表后，监控自动统计信息收集任务的运行状态，确认是否恢复正常。
参考文档：
Bug 21869685 - ORA-7445: exception encountered: core dump [evaudc()+180] (Doc ID 21869685.8)
|
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2025-8-10 18:42:34
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对