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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25583515/viewspace-2146424/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25583515/viewspace-2146424/