数据库日志的时间和OS 时间不匹配

qcbz

现在的时间是20:30:08 2012，  我做个日志切换，但是数据库alert日志显示的时间为凌晨4点 多，如下：

查看了其他生产库 DBTIMEZONE 也是+00:00
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
+00:00


[email=oracle@shtdb01]oracle@shtdb01[/email]:~> date
Sat Jul 28 20:30:03 CST 2012
[email=oracle@shdb01]oracle@shdb01[/email]:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Jul 28 20:30:08 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-07-28 20:30:18
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>

现在查看alert.log

Sat Jul 28 04:30:25 2012
Thread 1 advanced to log sequence 215 (LGWR switch)
  Current log# 5 seq# 215 mem# 0: +DATA/mpdb/onlinelog/group_5.270.781824791
  Current log# 5 seq# 215 mem# 1: +DATA/mpdb/onlinelog/group_5.271.781824791
Sat Jul 28 04:32:31 2012
Thread 1 advanced to log sequence 216 (LGWR switch)
  Current log# 7 seq# 216 mem# 0: +DATA/mpdb/onlinelog/group_7.272.781824793
  Current log# 7 seq# 216 mem# 1: +DATA/mpdb/onlinelog/group_7.273.781824795
Thread 1 advanced to log sequence 217 (LGWR switch)
  Current log# 1 seq# 217 mem# 0: +DATA/mpdb/onlinelog/group_1.266.781824787
  Current log# 1 seq# 217 mem# 1: +DATA/mpdb/onlinelog/group_1.267.781824787
Sat Jul 28 04:32:52 2012
Thread 1 cannot allocate new log, sequence 218
Checkpoint not complete
  Current log# 1 seq# 217 mem# 0: +DATA/mpdb/onlinelog/group_1.266.781824787
  Current log# 1 seq# 217 mem# 1: +DATA/mpdb/onlinelog/group_1.267.781824787
Thread 1 advanced to log sequence 218 (LGWR switch)
  Current log# 2 seq# 218 mem# 0: +DATA/mpdb/onlinelog/group_2.268.781824789
  Current log# 2 seq# 218 mem# 1: +DATA/mpdb/onlinelog/group_2.269.781824789

Maclean Liu(刘相兵

action plan;

什么操作系统？

是否有人调整过 OS时钟？

select max(sequence#) from v$Log_history;

alter system switch logfile;

select max(sequence#) from v$Log_history;


上传alert.log中最新的500行

qcbz

os version :linux
db version 11.2.0.2
没人调整过

SQL> select max(sequence#) from v$Log_history;

MAX(SEQUENCE#)
--------------
           217
SQL> SQL>
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$Log_history;
MAX(SEQUENCE#)
--------------
           218
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$Log_history;
MAX(SEQUENCE#)
--------------
           219

hread 1 advanced to log sequence 209 (LGWR switch)
  Current log# 1 seq# 209 mem# 0: +DATA/mpdb/onlinelog/group_1.266.781824787
  Current log# 1 seq# 209 mem# 1: +DATA/mpdb/onlinelog/group_1.267.781824787
Sat Jul 28 02:16:34 2012
Thread 1 advanced to log sequence 210 (LGWR switch)
  Current log# 2 seq# 210 mem# 0: +DATA/mpdb/onlinelog/group_2.268.781824789
  Current log# 2 seq# 210 mem# 1: +DATA/mpdb/onlinelog/group_2.269.781824789
Sat Jul 28 02:20:15 2012
Thread 1 advanced to log sequence 211 (LGWR switch)
  Current log# 5 seq# 211 mem# 0: +DATA/mpdb/onlinelog/group_5.270.781824791
  Current log# 5 seq# 211 mem# 1: +DATA/mpdb/onlinelog/group_5.271.781824791
Sat Jul 28 02:20:53 2012
Thread 1 advanced to log sequence 212 (LGWR switch)
  Current log# 7 seq# 212 mem# 0: +DATA/mpdb/onlinelog/group_7.272.781824793
  Current log# 7 seq# 212 mem# 1: +DATA/mpdb/onlinelog/group_7.273.781824795
Sat Jul 28 02:50:14 2012
Thread 1 advanced to log sequence 213 (LGWR switch)
  Current log# 1 seq# 213 mem# 0: +DATA/mpdb/onlinelog/group_1.266.781824787
  Current log# 1 seq# 213 mem# 1: +DATA/mpdb/onlinelog/group_1.267.781824787
Thread 1 advanced to log sequence 214 (LGWR switch)
  Current log# 2 seq# 214 mem# 0: +DATA/mpdb/onlinelog/group_2.268.781824789
  Current log# 2 seq# 214 mem# 1: +DATA/mpdb/onlinelog/group_2.269.781824789
Sat Jul 28 04:30:25 2012
Thread 1 advanced to log sequence 215 (LGWR switch)
  Current log# 5 seq# 215 mem# 0: +DATA/mpdb/onlinelog/group_5.270.781824791
  Current log# 5 seq# 215 mem# 1: +DATA/mpdb/onlinelog/group_5.271.781824791
Sat Jul 28 04:32:31 2012
Thread 1 advanced to log sequence 216 (LGWR switch)
  Current log# 7 seq# 216 mem# 0: +DATA/mpdb/onlinelog/group_7.272.781824793
  Current log# 7 seq# 216 mem# 1: +DATA/mpdb/onlinelog/group_7.273.781824795
Thread 1 advanced to log sequence 217 (LGWR switch)
  Current log# 1 seq# 217 mem# 0: +DATA/mpdb/onlinelog/group_1.266.781824787
  Current log# 1 seq# 217 mem# 1: +DATA/mpdb/onlinelog/group_1.267.781824787
Sat Jul 28 04:32:52 2012
Thread 1 cannot allocate new log, sequence 218
Checkpoint not complete
  Current log# 1 seq# 217 mem# 0: +DATA/mpdb/onlinelog/group_1.266.781824787
  Current log# 1 seq# 217 mem# 1: +DATA/mpdb/onlinelog/group_1.267.781824787
Thread 1 advanced to log sequence 218 (LGWR switch)
  Current log# 2 seq# 218 mem# 0: +DATA/mpdb/onlinelog/group_2.268.781824789
  Current log# 2 seq# 218 mem# 1: +DATA/mpdb/onlinelog/group_2.269.781824789
Sat Jul 28 05:24:32 2012
Thread 1 advanced to log sequence 219 (LGWR switch)
  Current log# 5 seq# 219 mem# 0: +DATA/mpdb/onlinelog/group_5.270.781824791
  Current log# 5 seq# 219 mem# 1: +DATA/mpdb/onlinelog/group_5.271.781824791
Sat Jul 28 05:25:20 2012
Thread 1 advanced to log sequence 220 (LGWR switch)
  Current log# 7 seq# 220 mem# 0: +DATA/mpdb/onlinelog/group_7.272.781824793
  Current log# 7 seq# 220 mem# 1: +DATA/mpdb/onlinelog/group_7.273.781824795

Maclean Liu(刘相兵

select to_char(startup_time,'YYYY-MM-DD hh24:mi:ss') from v$instance;

查一下 并上传完整的alert.log为附件