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?自動(dòng)統(tǒng)計(jì)信息收集?SCHEDULER?每天早上6點(diǎn)執(zhí)行，與定義的22點(diǎn)不一致問題分析

【問題分析】

1.看到每個(gè)WINDOW的NEXT_START_DATE的時(shí)區(qū)為+00:00。這代表統(tǒng)計(jì)信息收集的窗口時(shí)區(qū)不對(duì),差了8小時(shí)

分析數(shù)據(jù)：

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

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使用的時(shí)區(qū)與dbtimezone無關(guān)

3.當(dāng)schedule建立時(shí)，如果startdate為null，則使用session timezone如果session級(jí)別沒有指定 則使用DEFAULT_TIMEZONE，統(tǒng)計(jì)信息收集schedule?窗口默認(rèn)開始時(shí)沒有指定startdate?沒有指定timezone,也沒有session?級(jí)的timezone指定，默認(rèn)使用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)

情況一致,該文檔描述如下,簡(jiǎn)單理解當(dāng)使用?General mode?(普通模式)建立庫(kù)時(shí)，PDB?和CDB的default timezone不一樣，是預(yù)期行為，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.

【測(cè)試驗(yàn)證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');

執(zhí)行：

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.如果上面的時(shí)區(qū)沒有調(diào)整完成，則修改窗口時(shí)區(qū)

<<<<<<<<<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);

執(zhí)行：

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

?

<<<<<<<<<<<<change timezone ?注意是start date,需要根據(jù)實(shí)際情況修改,修改成下次計(jì)劃的執(zhí)行時(shí)間.

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');

最終結(jié)果

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';

?