pdb时区问题：与当前时间不一致

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