ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5424]

ORA-07445告警：


Thu May 21 23:26:00 2015
Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7F167EC05000] [PC:0x49E0CF0, __intel_new_memcpy()+5424] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/oms/OMS3/trace/OMS3_j001_10768.trc  (incident=1635086):
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5424] [SIGSEGV] [ADDR:0x7F167EC05000] [PC:0x49E0CF0] [Invalid permissions for mapped object] []
Incident details in: /u01/app/oracle/diag/rdbms/oms/OMS3/incident/incdir_1635086/OMS3_j001_10768_i1635086.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:0x1A0] [PC:0x8AD6C01, pfrtcs()+61] [flags: 0x0, count: 2]
Errors in file /u01/app/oracle/diag/rdbms/oms/OMS3/trace/OMS3_j001_10768.trc  (incident=1635087):
ORA-07445: exception encountered: core dump [pfrtcs()+61] [SIGSEGV] [ADDR:0x1A0] [PC:0x8AD6C01] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5424] [SIGSEGV] [ADDR:0x7F167EC05000] [PC:0x49E0CF0] [Invalid permissions for mapped object] []
Incident details in: /u01/app/oracle/diag/rdbms/oms/OMS3/incident/incdir_1635087/OMS3_j001_10768_i1635087.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:0x4A404800008] [PC:0x89C2EB8, 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.


檢視trace檔案：
Trace file /u01/app/oracle/diag/rdbms/oms/OMS3/trace/OMS3_j001_10768.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.4/dbhome_1
System name:    Linux
Node name:      dm01db03.corp.haier.com
Release:        2.6.39-400.128.17.el5uek
Version:        #1 SMP Tue May 27 13:20:24 PDT 2014
Machine:        x86_64
Instance name: OMS3
Redo thread mounted by this instance: 3
Oracle process number: 176
Unix process pid: 10768, image: oracle@dm01db03.corp.haier.com (J001)



*** 2015-05-21 23:26:00.859
*** SESSION ID:(1516.36233) 2015-05-21 23:26:00.859
*** CLIENT ID:() 2015-05-21 23:26:00.859
*** SERVICE NAME:(SYS$USERS) 2015-05-21 23:26:00.859
*** MODULE NAME:(DBMS_SCHEDULER) 2015-05-21 23:26:00.859                  -------排程異常
*** ACTION NAME:(ORA$AT_OS_OPT_SY_47608) 2015-05-21 23:26:00.859   -------自動作業故障


Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7F167EC05000] [PC:0x49E0CF0, __intel_new_memcpy()+5424] [flags: 0x0, count: 1]
Incident 1635086 created, dump file: /u01/app/oracle/diag/rdbms/oms/OMS3/incident/incdir_1635086/OMS3_j001_10768_i1635086.trc
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5424] [SIGSEGV] [ADDR:0x7F167EC05000] [PC:0x49E0CF0] [Invalid permissions for mapped object] []


Incident 1635087 created, dump file: /u01/app/oracle/diag/rdbms/oms/OMS3/incident/incdir_1635087/OMS3_j001_10768_i1635087.trc
ORA-07445: exception encountered: core dump [pfrtcs()+61] [SIGSEGV] [ADDR:0x1A0] [PC:0x8AD6C01] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5424] [SIGSEGV] [ADDR:0x7F167EC05000] [PC:0x49E0CF0] [Invali


發現job異常停止：
SQL>  SELECT job_name,status,TO_CHAR(ACTUAL_START_DATE,'YYYY-MM-DD HH24:MI:SS')  start_date,TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') log_date,RUN_DURATION FROM dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT_SY_47608';


JOB_NAME                                 STATUS                         START_DATE          LOG_DATE            RUN_DURATION
---------------------------------------- ------------------------------ ------------------- ------------------- ------------------------------
ORA$AT_OS_OPT_SY_47608                   STOPPED                        2015-05-21 22:00:02 2015-05-21 23:26:28 +000 01:26:26


由於是多次出現問題，檢視job執行歷史資訊，最近成功的job為2011-10-01：
SQL>  SELECT job_name,status,TO_CHAR(ACTUAL_START_DATE,'YYYY-MM-DD HH24:MI:SS')  start_date,TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') log_date,RUN_DURATION FROM dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT_SY_%' order by 3;


JOB_NAME                                 STATUS                         START_DATE          LOG_DATE            RUN_DURATION
---------------------------------------- ------------------------------ ------------------- ------------------- ------------------------------
ORA$AT_OS_OPT_SY_41                      SUCCEEDED                      2011-09-25 22:04:46 2011-09-25 22:04:59 +000 00:00:13
ORA$AT_OS_OPT_SY_43                      SUCCEEDED                      2011-09-26 22:00:09 2011-09-26 22:00:33 +000 00:00:24
ORA$AT_OS_OPT_SY_61                      SUCCEEDED                      2011-09-27 22:00:08 2011-09-27 22:00:42 +000 00:00:34
ORA$AT_OS_OPT_SY_64                      SUCCEEDED                      2011-09-28 22:00:08 2011-09-28 22:01:24 +000 00:01:16
ORA$AT_OS_OPT_SY_67                      SUCCEEDED                      2011-09-29 22:00:03 2011-09-29 22:00:35 +000 00:00:32
ORA$AT_OS_OPT_SY_84                      SUCCEEDED                      2011-09-30 22:00:04 2011-09-30 22:01:07 +000 00:01:03
ORA$AT_OS_OPT_SY_87                      SUCCEEDED                      2011-10-01 06:00:08 2011-10-01 06:00:53 +000 00:00:45
ORA$AT_OS_OPT_SY_90                      SUCCEEDED                      2011-10-01 10:00:25 2011-10-01 10:00:32 +000 00:00:06
ORA$AT_OS_OPT_SY_92                      SUCCEEDED                      2011-10-01 14:00:42 2011-10-01 14:00:53 +000 00:00:11
ORA$AT_OS_OPT_SY_370                     STOPPED                        2011-10-11 22:00:05 2011-10-12 02:00:07 +000 04:00:02
ORA$AT_OS_OPT_SY_430                     STOPPED                        2011-10-14 22:00:04 2011-10-15 02:00:06 +000 04:00:02
。。。。。。。
。。省略多行
ORA$AT_OS_OPT_SY_47488                   STOPPED                        2015-05-17 14:02:45 2015-05-17 14:39:23 +000 00:36:38
ORA$AT_OS_OPT_SY_47508                   STOPPED                        2015-05-17 18:03:14 2015-05-17 18:21:16 +000 00:18:02
ORA$AT_OS_OPT_SY_47528                   STOPPED                        2015-05-17 22:03:38 2015-05-17 22:24:15 +000 00:20:37
ORA$AT_OS_OPT_SY_47548                   STOPPED                        2015-05-18 22:00:02 2015-05-18 23:13:11 +000 01:13:08
ORA$AT_OS_OPT_SY_47568                   STOPPED                        2015-05-19 22:00:06 2015-05-19 23:51:27 +000 01:51:21
ORA$AT_OS_OPT_SY_47588                   STOPPED                        2015-05-20 22:00:08 2015-05-20 23:50:30 +000 01:50:23
ORA$AT_OS_OPT_SY_47608                   STOPPED                        2015-05-21 22:00:02 2015-05-21 23:26:28 +000 01:26:26


206 rows selected.

檢視DBA_AUTOTASK_JOB_HISTORY來跟蹤自動作業情況。
COL CLIENT_NAME FOR A35
COL JOB_NAME FOR A25
COL JOB_DURATION FOR A20
COL JOB_STATUS FOR A10
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_47608';
CLIENT_NAME                         JOB_NAME                  JOB_START_TIME                           JOB_DURATION         JOB_STATUS  JOB_ERROR
----------------------------------- ------------------------- ---------------------------------------- -------------------- ---------- ----------
auto optimizer stats collection     ORA$AT_OS_OPT_SY_47608    21-MAY-15 10.00.02.698622 PM PRC         +000 01:26:26        STOPPED             0
可見是自動作業“auto optimizer stats collection”故障

自動收集作業早已失效建議禁用：
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


1.啟用和禁止維護任務：
使用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;