Managing Automated Database Maintenance Tasks

Case 1:

在DBCA安裝時如果沒有勾選自動收集統計資訊，則oracle不會自動收集

查詢AUTOTASK_STATUS為 DISABLED

select * from DBA_AUTOTASK_WINDOW_CLIENTS

且查詢為空

select * from DBA_AUTOTASK_TASK

手動開啟的方法，開啟所有：

BEGIN

dbms_auto_task_admin.enable();

END;

開啟後到了windows的時間點可以在看到執行記錄

select * from DBA_AUTOTASK_JOB_HISTORY

執行完後這裡也可以查到記錄

select * from DBA_AUTOTASK_TASK

相關檢視：

select * from DBA_AUTOTASK_CLIENT

select * from DBA_AUTOTASK_CLIENT_HISTORY

select * from DBA_AUTOTASK_CLIENT_JOB

select * from DBA_AUTOTASK_JOB_HISTORY

select * from DBA_AUTOTASK_OPERATION

select * from DBA_AUTOTASK_SCHEDULE

select * from DBA_AUTOTASK_TASK

select * from DBA_AUTOTASK_WINDOW_CLIENTS

select * from DBA_AUTOTASK_WINDOW_HISTORY

SELECT * FROM dba_scheduler_programs

WHERE owner = 'SYS'

AND program_name = 'GATHER_STATS_PROG';

SELECT w.window_name, w.repeat_interval, w.duration, w.enabled

FROM dba_autotask_window_clients c, dba_scheduler_windows w

WHERE c.window_name = w.window_name

AND c.optimizer_stats = 'ENABLED';

Case 2:

Oracle 11.2.0.2 DB發現統計資訊task一直沒有執行成功

查詢下面檢視正常：

select * from DBA_AUTOTASK_WINDOW_CLIENTS

select * from DBA_AUTOTASK_TASK

但查詢執行歷史缺沒有記錄沒有週一到週五記錄，alert.log中也沒有window的記錄

SELECT * FROM DBA_SCHEDULER_WINDOW_DETAILS

嘗試修改一下WINDOWS 執行時間

BEGIN

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

( name => 'SYS.WEDNESDAY_WINDOW'

,attribute => 'REPEAT_INTERVAL'

,value => 'freq=daily;byday=WED;byhour=11;byminute=30; bysecond=0');

END;

執行之後發居然成功執行了

SELECT * FROM DBA_SCHEDULER_WINDOW_DETAILS

檢視後臺alert.log，window執行正常但呼叫dbms_stats時用報錯ORA-06564，原來這個DB是從oracle 10G impdp 全庫的方式匯入的11G。一些不需要的資料字典資訊也匯入了

Wed Aug 21 11:30:00 2013

Setting Resource Manager plan SCHEDULER[0x3109]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Wed Aug 21 11:30:00 2013

Starting background process VKRM

Wed Aug 21 11:30:00 2013

VKRM started with pid=52, OS id=6382

Wed Aug 21 11:30:02 2013

Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Wed Aug 21 11:30:24 2013

DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.

Errors in file /u01/app/oracle/diag/rdbms/eabudb/eabudb/trace/eabudb_j000_6391.trc:

ORA-20011: Approximate NDV failed: ORA-06564: object DMP does not exist

Wed Aug 21 11:32:47 2013

Thread 1 cannot allocate new log, sequence 734

Private strand flush not complete

Current log# 4 seq# 733 mem# 0: /u01/app/oracle/oradata/eabudb/redo04.log

Thread 1 advanced to log sequence 734 (LGWR switch)

Current log# 5 seq# 734 mem# 0: /u01/app/oracle/oradata/eabudb/redo05.log

Wed Aug 21 11:32:51 2013

Archived Log entry 1303 added for thread 1 sequence 733 ID 0x59630e1f dest 1:

Wed Aug 21 11:34:18 2013

End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Wed Aug 21 12:14:28 2013

Thread 1 advanced to log sequence 735 (LGWR switch)

Current log# 1 seq# 735 mem# 0: /u01/app/oracle/oradata/eabudb/redo01.log

Wed Aug 21 12:14:28 2013

Archived Log entry 1305 added for thread 1 sequence 734 ID 0x59630e1f dest 1:

Wed Aug 21 13:12:59 2013

Time drift detected. Please check VKTM trace file for more details.

Wed Aug 21 13:29:33 2013

Thread 1 advanced to log sequence 736 (LGWR switch)

Current log# 2 seq# 736 mem# 0: /u01/app/oracle/oradata/eabudb/redo02.log

Wed Aug 21 13:29:34 2013

Archived Log entry 1307 added for thread 1 sequence 735 ID 0x59630e1f dest 1:

Wed Aug 21 14:43:17 2013

Thread 1 advanced to log sequence 737 (LGWR switch)

Current log# 3 seq# 737 mem# 0: /u01/app/oracle/oradata/eabudb/redo03.log

Wed Aug 21 14:43:18 2013

Archived Log entry 1309 added for thread 1 sequence 736 ID 0x59630e1f dest 1:

Wed Aug 21 15:30:00 2013

Closing scheduler window

Closing Resource Manager plan via scheduler window

Clearing Resource Manager plan via parameter

查詢發現WEEKNIGHT_WINDOW,WEEKEND_WINDOW是TRUE

select WINDOW_NAME,ENABLED from dba_scheduler_windows

這兩個window是10G中收集統計資訊時使用的，11G預設是disable的，但因為從10G全庫impdp。導致這個成了enable.

Oracle 11G 統計資訊Task

相關文章