ORA-07445處理分析 關閉自動化任務

raysuen發表於2016-09-18
1 告警日誌內容：
[oracle@CnXzBus01POraclL01 ~]$ tail -1000f /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/alert_kcpt.log | grep -A 2 -B 2 "07445"
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j000_124105.trc  (incident=115772):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_115772/kcpt_j000_124105_i115772.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j000_57785.trc  (incident=114100):
ORA-07445: : [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_114100/kcpt_j000_57785_i114100.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j002_123525.trc  (incident=117492):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_117492/kcpt_j002_123525_i117492.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j002_188018.trc  (incident=117804):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_117804/kcpt_j002_188018_i117804.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j002_57136.trc  (incident=117764):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_117764/kcpt_j002_57136_i117764.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j002_121783.trc  (incident=117900):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_117900/kcpt_j002_121783_i117900.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j000_54232.trc  (incident=114412):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_114412/kcpt_j000_54232_i114412.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j002_120294.trc  (incident=118532):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_118532/kcpt_j002_120294_i118532.trc
Use ADRCI or Support Workbench to package the incident.
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Errors in file /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j002_185253.trc  (incident=117556):
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []
Incident details in: /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_117556/kcpt_j002_185253_i117556.trc
Use ADRCI or Support Workbench to package the incident.

2 檢視trace檔案

[root@CnXzBus01POraclL01 ~]# more /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_117556/kcpt_j002_185253_i117556.trc
Dump file /opt/app/oracle/diag/rdbms/kcpt/kcpt/incident/incdir_117556/kcpt_j002_185253_i117556.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      CnXzBus01POraclL01
Release:        2.6.32-431.el6.x86_64
Version:        #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine:        x86_64
Instance name: kcpt
Redo thread mounted by this instance: 1
Oracle process number: 489
Unix process pid: 185253, image: oracle@CnXzBus01POraclL01 (J002)


*** 2016-09-18 14:05:20.593
*** SESSION ID:(869.47729) 2016-09-18 14:05:20.593
*** CLIENT ID:() 2016-09-18 14:05:20.593
*** SERVICE NAME:(SYS$USERS) 2016-09-18 14:05:20.593
*** MODULE NAME:(DBMS_SCHEDULER) 2016-09-18 14:05:20.593
*** ACTION NAME:(ORA$AT_SQ_SQL_SW_740) 2016-09-18 14:05:20.593

Dump continued from file: /opt/app/oracle/diag/rdbms/kcpt/kcpt/trace/kcpt_j002_185253.trc
ORA-07445: exception encountered: core dump [apaneg()+227] [SIGSEGV] [ADDR:0x8] [PC:0x21607D1] [Address not mapped to object] []

========= Dump for incident 117556 (ORA 7445 [apaneg()+227]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x21607D1, apaneg()+227] [flags: 0x0, count: 1]
Registers:
%rax: 0x00007f3f41da1d68 %rbx: 0x00007f3f40ae2650 %rcx: 0x0000000000000000
%rdx: 0x00007f3f40ae2650 %rdi: 0x000000000c0cc9e0 %rsi: 0x00007f3f40ae2650
%rsp: 0x00007fffff3b07d0 %rbp: 0x00007fffff3b0850  %r8: 0x0000000000000000
 %r9: 0x0000000000000001 %r10: 0x00007f3f40e8af58 %r11: 0x0000000000000168
%r12: 0x0000000000000000 %r13: 0x00007fffff3b22e0 %r14: 0x0000000000000001
%r15: 0x0000000000000000 %rip: 0x00000000021607d1 %efl: 0x0000000000010202
  apaneg()+210 (0x21607c0) cmovne %r9d,%r10d
  apaneg()+214 (0x21607c4) mov %r10b,0x18(%r13)
  apaneg()+218 (0x21607c8) jmp 0x21607d1
  apaneg()+220 (0x21607ca) mov 0x9f91267(%rip),%rdi
> apaneg()+227 (0x21607d1) mov 0x8(%r15),%rax
  apaneg()+231 (0x21607d5) test %rax,%rax
  apaneg()+234 (0x21607d8) jnz 0x2160b6a
  apaneg()+240 (0x21607de) mov %r15,%rdx
…………………….



----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xc4fe165d0     11829  package body SYS.DBMS_SQLTUNE_INTERNAL
0xc47ec10e0         7  SYS.WRI$_ADV_SQLTUNE
0xc3fe43458       601  package body SYS.PRVT_ADVISOR
0xc3fe43458      2678  package body SYS.PRVT_ADVISOR
0xc57f0b298       241  package body SYS.DBMS_ADVISOR
0xc37f29ea0       821  package body SYS.DBMS_SQLTUNE
0xc57ec1d98         4  anonymous block

發現為自動任務呼叫，查詢對應的自動化任務

3 確定自動化任務為"sql tuning advisor"

set line 160 pages 500
col client_name for a40
col window_name for a20
col job_name for  a30
col job_start_time for a45
col job_status for a10
select client_name,window_name,job_name,job_start_time,job_status
from dba_autotask_job_history
order by 4,1;

SQL> set line 160 pages 500
SQL> col client_name for a40
SQL> col window_name for a20
SQL> col job_name for  a30
SQL> col job_start_time for a45
SQL> col job_status for a10
SQL> select client_name,window_name,job_name,job_start_time,job_status
  2  from dba_autotask_job_history where JOB_NAME like 'ORA$AT_SQ_SQL_SW%' and rownum < 10
  3  order by 4,1;

CLIENT_NAME                              WINDOW_NAME          JOB_NAME                       JOB_START_TIME                                JOB_STATUS
---------------------------------------- -------------------- ------------------------------ --------------------------------------------- ----------
sql tuning advisor                       WEDNESDAY_WINDOW     ORA$AT_SQ_SQL_SW_673           07-SEP-16 10.00.02.101075 PM PRC              SUCCEEDED
sql tuning advisor                       TUESDAY_WINDOW       ORA$AT_SQ_SQL_SW_707           13-SEP-16 10.00.00.402783 PM PRC              SUCCEEDED
sql tuning advisor                       THURSDAY_WINDOW      ORA$AT_SQ_SQL_SW_713           15-SEP-16 10.00.02.081122 PM PRC              SUCCEEDED
sql tuning advisor                       FRIDAY_WINDOW        ORA$AT_SQ_SQL_SW_716           16-SEP-16 10.00.02.099018 PM PRC              STOPPED
sql tuning advisor                       SATURDAY_WINDOW      ORA$AT_SQ_SQL_SW_719           17-SEP-16 06.00.01.154643 AM PRC              STOPPED
sql tuning advisor                       SATURDAY_WINDOW      ORA$AT_SQ_SQL_SW_722           17-SEP-16 10.04.25.635909 AM PRC              STOPPED
sql tuning advisor                       SATURDAY_WINDOW      ORA$AT_SQ_SQL_SW_725           17-SEP-16 02.04.32.461638 PM PRC              STOPPED
sql tuning advisor                       SATURDAY_WINDOW      ORA$AT_SQ_SQL_SW_728           17-SEP-16 06.04.39.462740 PM PRC              STOPPED
sql tuning advisor                       SATURDAY_WINDOW      ORA$AT_SQ_SQL_SW_731           17-SEP-16 10.04.45.942901 PM PRC              STOPPED

9 rows selected.

SQL>

select * from DBA_AUTOTASK_WINDOW_CLIENTS;


select client_name,status from DBA_AUTOTASK_CLIENT;

SQL> select client_name,status from DBA_AUTOTASK_CLIENT;

CLIENT_NAME                              STATUS
---------------------------------------- --------
auto optimizer stats collection          ENABLED
auto space advisor                       ENABLED
sql tuning advisor                       ENABLED

select client_name,operation_name from dba_autotask_operation t;

SQL> select client_name,operation_name from dba_autotask_operation t;

CLIENT_NAME                              OPERATION_NAME
---------------------------------------- ----------------------------------------------------------------
auto optimizer stats collection          auto optimizer stats job
auto space advisor                            auto space advisor job
sql tuning advisor                              automatic sql tuning task



4 
對某一個維護視窗禁用自動化任務。
BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation   => NULL,
window_name => 'MONDAY_WINDOW');
END;
/
或
BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation   => NULL,
window_name => NULL);
END;
/

SQL> BEGIN
  2  dbms_auto_task_admin.disable(
  3  client_name => 'sql tuning advisor',
  4  operation   => NULL,
  5  window_name => NULL);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

SQL> select client_name,status from DBA_AUTOTASK_CLIENT;

CLIENT_NAME                              STATUS
---------------------------------------- --------
auto optimizer stats collection          ENABLED
auto space advisor                       ENABLED
sql tuning advisor                       DISABLED

SQL>


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2125095/，如需轉載，請註明出處，否則將追究法律責任。

相關文章