记一次Oracle定时器调用存储过程的坑

背景

Oracle用scheduler写了一个定时器调用存储过程，每个月创建一次表。结果到定时器的执行时间了表却没有创建（预发布环境）。
本地环境正常，模拟环境正常，开发环境正常，测试环境正常
预发布环境异常没创建表
（此时心中：万马奔腾，不得不提一句，docker真香）

排查问题

刚发现这个问题的时候，一脸懵逼。特么本地环境，模拟环境，开发环境，测试环境都ok了，咋个预发布环境就出问题了？？

带着一脸懵逼，还是默默打开了oracle，查看了下定时器

select job_name,start_date,next_run_date,repeat_interval from user_scheduler_jobs;

一打开（由于预发布环境没有权限动，现在查不了异常环境的图。下图是正常情况的，这里展示一下字段。异常信息由下面写出。）：

查询后第一映入眼帘的是，我设置定时器每个月28号 09:15:05执行一次。咋到点了还没有表创建，NEXT_RUN_DATE字段下一次执行时间也没有更新

异常信息：

JOB_NAME：CREATE_TABLE_BY_MONTH

START_DATE：2020-09-27 18:13:05.046910 PST8PDT

NEXT_RUN_DATE：2020-09-28 09:15:05.000000 PST8PDT

REPEAT_INTERVAL：FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=28; BYHOUR=9; BYMINUTE=15

之后一顿百度操作猛如虎，查看各路大佬是否遇到这类问题

总结可能导致的原因如下：

• oracle定时器job长时间执行无法结束。意思就是执行的时候，出于某种原因，数据库崩了之类的。job卡死。
• 查询job_queue_processes参数
• 系统uptime超过497天100%bug

尝试定位问题方式一：oracle定时器job长时间执行无法结束

参考学习博文：oracle定时器job长时间执行无法结束
之后一顿操作：

-- 描述正在运行的Job的相关信息
select * from dba_scheduler_running_jobs;

-- 描述已经完成的Job是否成功，记录相关日志的视图，这里记录着所有已经执行完毕的日程
select * from  user_scheduler_job_run_details;

select * from user_scheduler_job_log ;

-- 描述当前数据库所有已知的日程。
select * from  dba_scheduler_schedules;

-- 描述当前数据库已经定义完毕的程序
select * from  dba_scheduler_programs;

-- 描述当前数据库已经定义完毕的作业
select * from  dba_scheduler_jobs;

查询正在运行的Job的相关信息：发现并没有正在运行的

查询已经完成的Job是否成功，发现全是成功的

这时候，我：

说明并不是这个原因。下来继续尝试另外的方式定位。

尝试定位问题方式二：查询job_queue_processes参数

网上说是job_queue_processes这个参数的问题，如果为0就会失败。查询了一下，为1000.说明也不是这个问题

select value from v$parameter where name like '%job_queue_processes%';

alter system set job_queue_processes=1000;

尝试定位问题方式三：uptime 100% but only after 497 days.

参考学习博文：Oracle 定时器失效——解决

简述就是 Oracle的一个bug，uptime的时间只要超过497天就会定时器失效。

之后马上查了一下uptime。发现已经 440 days 了，欸。觉得还有可能是这个问题。超过497天 100%出问题，接近497天概率出问题。

但当我准备根据博文一系列操作的时候，发现：

啥？要重启主机？？预发布环境还有客户在用呢？？

于是继续搁置。尝试另外的方式。

此时一上午已经过去。。。。

换个思路，重头开始看（解决）

START_DATE：2020-09-27 18:13:05.046910 PST8PDT

突然再看一下定时器信息，咋个定时器开始时间：START_DATE还是前一天27号的时间？？？难道不应该是我执行定时器的时间吗？？(定时器设置是开启即马上执行存储过程一次)

之后。。不停网上搜解决方式。。。

30 min later…
45 min later…
60 min later…

预发布环境的时间查完了，就是不知道这个START_DATE咋个出来的。

最后结论只有Oracle内部自己实现的START_DATE。（有无大佬可以指教一波，谢谢。网上搜半天都没找到讲解。）

之后找同事过来帮忙。。再一次看了下测试环境和预发布环境的user_scheduler_jobs信息。

同事： prc是啥？测试环境prc结尾，预发布环境PST8PDT结尾。

我：prc不知道，PST8PDT是个时区，美国加拿大那边的时区（之前注意到这的不同之处专门网上查了下，不过没在意这点！！！）

同事： 会不会就是这个时区问题

我（心里一惊）：
START_DATE：2020-09-27 18:13:05.046910 PST8PDT

NEXT_RUN_DATE：2020-09-28 09:15:05.000000 PST8PDT

下图是正常环境的图，这里只做展示字段。

算了算，我们在东8区，PST8PDT是西8区，反正就是我们快了15个小时，意思就是我当时执行时间 09-27 18:13:05. + 15小时 = 09-28 9：13：05 ， 鬼鬼，就是我排查问题的时间！！！问题就是这！！

此时：已下班1小时。。。

之后重新删了drop定时器，开启一个设置每次执行时间在START_DATE后面几分钟。测试成功，表成功创建。。。

整理一下时区查询的sql

-- 查询系统时间，数据库时间等等
select sysdate,systimestamp,current_date,current_timestamp,dbtimezone,sessiontimezone from dual;

-- 查询时区
SELECT * FROM V$TIMEZONE_NAMES;

SELECT * FROM V$TIMEZONE_NAMES WHERE TZNAME='PST8PDT';

SELECT * FROM V$TIMEZONE_NAMES WHERE TZNAME='PRC';

-- 比标准时间差多少小时
select tz_offset('PST8PDT') from dual;

-- 时区转换
select to_char(new_time(to_date('13-12-2013 17:03','dd-mm-yyyy hh24:mi'),'PST','EST'),'YYYY-MM-DD HH24:MI') from dual;

查询时区：

查询相差时间：

得到正在运行job的 session id

如果job 使用DBMS_JOB package创建，请使用如下语句：

set feedback off
alter session set nls_date_format=‘DD-MON-YYYY HH24:MI:SS’;
set feedback on

select jr.job, s.username, s.sid, s.serial#, p.spid, s.lockwait, s.logon_time
from dba_jobs_running jr, v$sessions,v$process p
where jr.sid = s.sid
and s.paddr = p.addr
order by jr.job;

如果job 使用DBMS_SCHEDULER 创建，请使用如下语句：

set feedback off
alter session set nls_date_format=‘DD-MON-YYYY HH24:MI:SS’;
set feedback on

select rj.job_name, s.username, s.sid, s.serial#, p.spid, s.lockwait, s.logon_time
from dba_scheduler_running_jobs rj, v$sessions,v$process p
where rj.session_id = s.sid
and s.paddr = p.addr
order by rj.job_name;

总结

• 不要放过任何一个不起眼的异常点，有时候的你以为没有问题就是一个思维盲区，陷进去就出不来了
• Oracle时区问题真坑（吐槽）
• 至此Oracle定时器START_DATE都不知道咋个计算的，需深入了解Oracle底层