set linesize? 1000

col ATTRIBUTE_NAME for a30

col value for a30

select con_id,attribute_name,value

from cdb_scheduler_global_attribute

where ATTRIBUTE_NAME='DEFAULT_TIMEZONE';

col name format a20

select CON_ID,NAME from v$containers;

?

【問題描述】

?12c pdb?自動統計信息收集?SCHEDULER?每天早上6點執行，與定義的22點不一致問題分析

【問題分析】

1.看到每個WINDOW的NEXT_START_DATE的時區為+00:00。這代表統計信息收集的窗口時區不對,差了8小時

分析數據：

---------------

OWNER WINDOW_NAME RESOURCE_PLAN SCHEDULE_OWNER SCHEDULE_NAME SCHEDULE_TYPE START_DATE REPEAT_INTERVAL END_DATE DURATION WINDOW_P NEXT_START_DATE LAST_START_DATE ENABLED ACTIVE MANUAL_OPEN_TIME MANUAL_DURATION COMMENTS

SYS MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 LOW?2021/09/13?22:00:00 +00:00?2021/09/06?22:00:00 +00:00 TRUE FALSE Monday window for maintenance tasks

SYS TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 LOW?2021/09/14?22:00:00 +00:00?2021/09/07?22:00:00 +00:00 TRUE FALSE Tuesday window for maintenance tasks

2.DBMS_SCHEDULER has no relation to or does not use the database timezone (DBTIMEZONE).schedule使用的時區與dbtimezone無關

3.當schedule建立時，如果startdate為null，則使用session timezone如果session級別沒有指定 則使用DEFAULT_TIMEZONE，統計信息收集schedule?窗口默認開始時沒有指定startdate?沒有指定timezone,也沒有session?級的timezone指定，默認使用default_timezone

4.檢查PDB?與CDB default timezone?，可以看到CDB?為PRC，pdb?為UTC(= +00:00 ),CDB=PRC(=+08:00)

?5.PDB與cdb DEFAULT_TIMEZONE不一致，通過案例搜索，檢查與文檔?Default Scheduler Timezone Value In PDB$SEED Different Than CDB (Doc ID 2702230.1)

情況一致,該文檔描述如下,簡單理解當使用?General mode?(普通模式)建立庫時，PDB?和CDB的default timezone不一樣，是預期行為，18c及之前是utc,19c是?PST8PDT

?? On 18c and 19c, using DBCA General mode create DB, the default timezone of scheduler of PDB is different with CDB$ROOT.

?? The default timezone of scheduler of PDB is PST8PDT on 19c and Etc/UTC on 18c no matter what the timezone of scheduler is in CDB$ROOT.

?? But using create database command or using DBCA customize mode, the default timezone of scheduler of PDB is same with CDB$ROOT.

【測試驗證TESTCASE 12c pdb】

General mode create DB.

set linesize 1000

col ATTRIBUTE_NAME for a30

col value for a30

select con_id,attribute_name,value from cdb_scheduler_global_attribute

where ATTRIBUTE_NAME='DEFAULT_TIMEZONE';

?

問題：EST5EDT?

col window_name for a30

col start_date for a30

col next_start_date for a50

col owner for a30

set linesize 1000

select owner,window_name,start_date,next_start_date

from dba_scheduler_windows;

?

【解決方式】

1.pdb?中修改default_timezone，并檢查

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','PRC');

執行：

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','PST8PDT');

col window_name for a30

col start_date for a30

col next_start_date for a50

col owner for a30

set linesize 1000

select owner,window_name,start_date,next_start_date

from dba_scheduler_windows;

2.如果上面的時區沒有調整完成，則修改窗口時區

<<<<<<<<<disable windows

exec DBMS_SCHEDULER.DISABLE(name=>'SATURDAY_WINDOW',force=>TRUE);

exec DBMS_SCHEDULER.DISABLE(name=>'SUNDAY_WINDOW',force=>TRUE);

exec DBMS_SCHEDULER.DISABLE(name=>'MONDAY_WINDOW',force=>TRUE);

exec DBMS_SCHEDULER.DISABLE(name=>'TUESDAY_WINDOW',force=>TRUE);

exec DBMS_SCHEDULER.DISABLE(name=>'WEDNESDAY_WINDOW',force=>TRUE);

exec DBMS_SCHEDULER.DISABLE(name=>'THURSDAY_WINDOW',force=>TRUE);

exec DBMS_SCHEDULER.DISABLE(name=>'FRIDAY_WINDOW',force=>TRUE);

執行：

exec DBMS_SCHEDULER.DISABLE(name=>'SATURDAY_WINDOW',force=>TRUE);

?

<<<<<<<<<<<<change timezone ?注意是start date,需要根據實際情況修改,修改成下次計劃的執行時間.

exec DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'SATURDAY_WINDOW',attribute=>'start_date',value=>to_timestamp_tz('2022-05-17?05:00:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'));

exec DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'SUNDAY_WINDOW',attribute=>'start_date',value=>to_timestamp_tz('2022-05-17?05:00:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'));

<<<<<<<<<<<<<<<enable windows

exec DBMS_SCHEDULER.enable(name=>'SATURDAY_WINDOW');

exec DBMS_SCHEDULER.enable(name=>'SUNDAY_WINDOW');

exec DBMS_SCHEDULER.enable(name=>'MONDAY_WINDOW');

exec DBMS_SCHEDULER.enable(name=>'TUESDAY_WINDOW');

exec DBMS_SCHEDULER.enable(name=>'WEDNESDAY_WINDOW');

exec DBMS_SCHEDULER.enable(name=>'THURSDAY_WINDOW');

exec DBMS_SCHEDULER.enable(name=>'FRIDAY_WINDOW');

最終結果

set linesize 1000

col ATTRIBUTE_NAME for a30

col value for a30

select con_id,attribute_name,value from cdb_scheduler_global_attribute

where ATTRIBUTE_NAME='DEFAULT_TIMEZONE';

?