本帖最后由 yongzhi2008 于 2013-3-19 08:31 编辑
本来要添加一个job 定时执行一个任务， 添加之后发现next_date 时间不对， 困惑中却发现以下灵异事件：
1、select sysdate from dual 查询出来的时间是正常的， 而job中的时间确快了一个小时。 系统的时间也是正确的， 跟select sysdate from dual 查询的时间一致。
2、在客户端用sqlplus连接数据库， 查询系统时间确实快一个小时， 在服务器本地查询却是正常的。
下面是演示：
在数据库服务器上：
[oracle@ora02 ~]$ sqlplus ZHAOPIN_USER/oracle
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 18 23:22:55 2013
Copyright (c) 1982, 2011, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select job, log_user, last_date,next_date, interval, what from user_jobs;
no rows selected
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-18 23:11:57
SQL> begin
2dbms_job.submit(:jobno,'begin proc_test; end;', sysdate + 10/1440, 'sysdate+2/24');
3end;
4/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-18 23:12:26
SQL> select job, log_user, last_date,next_date, interval, what from user_jobs;
JOB LOG_USER
LAST_DATE NEXT_DATE INTERVAL
WHAT
---------- ------------------------------ ------------------- ------------------- ------------------------------ ------------------------------
90 ZHAOPIN_USER
2013-03-19 00:12:21 2013-03-19 02:12:21 sysdate+2/24
begin proc_test; end;
--刚刚添加， last_date 就有值了， 说明已经执行了， 执行时间为添加时的时间， 但是next_date 却是正常的。 再次确认时间：
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-18 23:13:01
SQL> ! date
Mon Mar 18 23:15:01 GMT+08:00 2013
SQL> /
SYSDATE
-------------------
2013-03-18 23:15:37
SQL> begin
2dbms_job.submit(:jobno,'begin proc_test; end;', sysdate + 10/1440, 'sysdate+2/24');
3commit;
4end;
5/
PL/SQL procedure successfully completed.
SQL> select job, log_user, last_date,next_date, interval, what from user_jobs;
JOB LOG_USER
LAST_DATE NEXT_DATE INTERVAL
WHAT
---------- ------------------------------ ------------------- ------------------- ------------------------------ ------------------------------
91 ZHAOPIN_USER
2013-03-19 00:16:01 2013-03-19 02:16:01 sysdate+2/24
begin proc_test; end;
90 ZHAOPIN_USER
2013-03-19 00:12:21 2013-03-19 02:12:21 sysdate+2/24
begin proc_test; end;
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-18 23:16:16
--从客户端连接，发现时间提前了一个小时
[oracle@oracle admin]$ sqlplus zhaopin_user/oracle@ncssdb2;
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 3月 18 23:19:25 2013
Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ncssdb2>select sysdate from dual;
SYSDATE
-------------------
2013-03-19 00:19:30
－－tnsnames.ora 文件中对ncssdb2 的配置
ncssdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ncssdb)
)
)
[oracle@ora02 ~]$ ping ora02
PING ora02: (192.168.15.102): 56 data bytes
64 bytes from 192.168.15.102: icmp_seq=0 ttl=255 time=0 ms
64 bytes from 192.168.15.102: icmp_seq=1 ttl=255 time=0 ms
[oracle@ora02 ~]$ lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 18-MAR-2013 23:50:36
Copyright (c) 1991, 2011, Oracle.All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date
08-MAR-2013 14:53:40
Uptime
10 days 8 hr. 56 min. 55 sec
Trace Level
off
Security
ON: Local OS Authentication
SNMP
ON
Listener Parameter File /u01/oracle/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/oracle/app/grid/diag/tnslsnr/ora02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.102)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.202)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ncssdb" has 1 instance(s).
Instance "ncssdb2", status READY, has 1 handler(s) for this service...
Service "ncssdbXDB" has 1 instance(s).
Instance "ncssdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 18 23:21:53 2013
Copyright (c) 1982, 2011, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-18 23:21:57
系统为RAC， 前几天一个节点因为夏令时的原因出现过时间提前一个小时的情况。 当时直接把那节点停掉了一个多小时， 服务器时间同步后， 再将节点重启就解决了时间不同步的问题。
本以为没有问题了， 谁想另一个节点的时间居然又快了一个小时， 而且用select sysdate from dual 还不容易查询出来。
真是乱套了！后将数据库重启， 遂解决了此问题。
--数据库重启之后：
[oracle@ora02 ~]$ sqlplus zhaopin_user/oracle
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 18 23:34:26 2013
Copyright (c) 1982, 2011, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-18 23:34:53
SQL> var jobno number;
SQL> begin
2dbms_job.submit(:jobno,'begin proc_test; end;', sysdate + 10/1440, 'sysdate+2/24');
3end;
4/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> print jobno;
JOBNO
----------
94
SQL> set line 180;
SQL> col what for a30;
SQL> col log_user for a15;
SQL> col interval for a40;
SQL> /
Commit complete.
SQL> select job, log_user, last_date,next_date, interval, what from user_jobs;
JOB LOG_USERLAST_DATE NEXT_DATE INTERVAL
WHAT
---------- --------------- ------------------- ------------------- ---------------------------------------- ------------------------------
91 ZHAOPIN_USER2013-03-19 00:16:01 2013-03-19 02:16:01 sysdate+2/24
begin proc_test; end;
90 ZHAOPIN_USER2013-03-19 00:12:21 2013-03-19 02:12:21 sysdate+2/24
begin proc_test; end;
92 ZHAOPIN_USER2013-03-19 00:25:57 2013-03-19 02:25:57 sysdate+2/24
begin proc_test; end;
93 ZHAOPIN_USER2013-03-19 00:27:27 2013-03-19 02:27:27 sysdate+2/24
begin proc_test; end;
94 ZHAOPIN_USER
2013-03-18 23:45:09 sysdate+2/24
begin proc_test; end;
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-18 23:36:26
另外， 重新从客户端用sqlplus连接， 查询时间正常了。
这里粗略介绍一下， 大家有何高见， 欢迎回帖呀， 谢谢，呵呵。
|
