昨天升级数据库，从10.2.0.5.0升级到11.2.0.2.0.按照预定的步骤很快就操作完了。升级完成后，开始跑一些应用和Job.有一个Job开始报错，Job是一个自动的同步job,中会有exp的动作，而且里面用到了consistent=y的选项，这样exp就大体如下：exp xxxx/xxxx file=xxx.dmp tables=xxxx consistent=y报错如下：Export: Release 11.2.0.2.0 - Production on Mon Sep 23 16:43:12 2013(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.EXP-00008: ORACLE error 1466 encountered1.初步的感觉是时间的问题，最先想到的系统时间的问题> hwclockdateTue 24 Sep 2013 07:29:54 AM ICT  -0.564711 seconds> dateTue Sep 24 07:29:54 ICT 2013但是查询物理时间和系统时间，都没问题。顺便提一句，如果在9i等版本中出现这个问题，很可能是物理时间和系统时间不同步造成的。这在tom大师的帖子中也有印证。http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7042250104782.排除这个问题，可能是object级的时间问题MOS上看到有可能是creation_date比系统时间还要晚，用如下的sql来排除select to_char(created,'dd-mm-yyyy hh24:mi:ss')"CREATION TIME", object_name, object_type, object_idfrom dba_objects where created > sysdate; 但是我这个例子没有任何输出，所以这个问题应该不是这个原因。3.我查询alert日志，发现这么一句，感觉很蹊跷

Mon Sep 23 15:58:26 2013

ORA-1466 (RO Tx began: 09/23/2013 08:58:25, Last DDL: 09/23/2013 11:14:16, Curr Time: 09/23/2013 08:58:26)

Mon Sep 23 15:58:38 2013

4.对于ORA-1466的解决方法，MOS上的一些建议是重建数据库，这也太狠了。

5.最后在TOM的帖子里找到了灵感，他是这么写的。

you mentioned out of sync clocks, that is what caught my eye on that note.

It could even be a TIMEZONE issue.  The dedicated server you are running might have a different TZ 

than the environment the export is running in.  Consider:

[tkyte@xtkyte-pc tkyte]$ echo $TZ

[tkyte@xtkyte-pc tkyte]$ plus

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jun 6 12:53:04 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

ops$tkyte@ORA9IR2> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual; 

TO_CHAR(SYSDATE,'DD-

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

06-jun-2005 12:53:15

ops$tkyte@ORA9IR2> !

[tkyte@xtkyte-pc tkyte]$ export TZ=PST

[tkyte@xtkyte-pc tkyte]$ plus 

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jun 6 16:53:23 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

ops$tkyte@ORA9IR2>  select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;                                                                               

TO_CHAR(SYSDATE,'DD-

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

06-jun-2005 16:53:33

One thing to check would be that the TZ of the export session is consistent with the rest of the 

sessions. 

排除了系统级的timezone问题，我觉得可能是db级的timezone问题。

最后发现升级timezone的时候没有把步骤做完。

SQL> select *from v$timezone_file;

FILENAME                VERSION

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

timezlrg_4.dat                4

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;  

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         4

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

最后给出完整的解决方法（升级timezone)

Timezone数据库层面的升级。注意：该步骤是否执行是和Step 6中的检查结果相关的，只有当Timezone的版本小于14时，才需要执行该步骤。主要参考：Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]一个典型的输出是：PROPERTY_NAME                  VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION         4DST_SECONDARY_TT_VERSION       0DST_UPGRADE_STATE              NONE然后开始准备工作：alter session set "_with_subquery"=materialize;;接着检查准备状态：SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value一个典型的输出是：PROPERTY_NAME                  VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION         4DST_SECONDARY_TT_VERSION       14DST_UPGRADE_STATE              PREPARE-- truncate logging tables if they exist.TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;-- log affected dataset serveroutput onSELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');-- end prepare window, the rows above will stay in those tables.EXEC DBMS_DST.END_PREPARE;-- check if this is ended一个典型的输出是：PROPERTY_NAME                  VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION         4DST_SECONDARY_TT_VERSION       0DST_UPGRADE_STATE              NONE2）真正开始升级Timezoneconn / as sysdbaSELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE 'DST_%'ORDER BY PROPERTY_NAME;一个典型的输出是：PROPERTY_NAME                  VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION         14DST_SECONDARY_TT_VERSION       4DST_UPGRADE_STATE              UPGRADE下面这条语句应该没有返回结果：SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';重启数据库：shutdown immediate升级相关的table：alter session set "_with_subquery"=materialize;如果没有错误，则结束升级：VAR fail number最后一次检查：SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value典型输出是：PROPERTY_NAME                  VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION         14DST_SECONDARY_TT_VERSION       0DST_UPGRADE_STATE              NONESELECT * FROM v$timezone_file;FILENAME                VERSION-------------------- ----------timezlrg_14.dat              14

升级完成后，可以用如下的方式来进行验证> exp prdrefwork/petrefwork file=a.dmp tables=csm_offer consistent=yExport: Release 11.2.0.2.0 - Production on Tue Sep 24 07:25:24 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in TH8TISASCII character set and UTF8 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table                      xxxxxx    2201 rows exportedExport terminated successfully without warnings.这次就不会跑错了。大功告成。