10g升級至11g exp的問題解決

按照預定的步驟很快就操作完了。升級完成後，開始跑一些應用和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
Copyright (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 Produ
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in TH8TISASCII character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table xxxx 2201 rows exported
EXP-00008: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed
Export terminated successfully with warnings.

1.初步的感覺是時間的問題，

最先想到的系統時間的問題

但是查詢物理時間和系統時間，都沒問題。順便提一句，如果在9i等版本中出現這個問題，很可能是物理時間和系統時間不同步造成的。這在tom大師的帖子中也有印證。

2.排除這個問題，可能是object級的時間問題

MOS上看到有可能是creation_date比系統時間還要晚，用如下的sql來排除

但是我這個例子沒有任何輸出，所以這個問題應該不是這個原因。

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資料庫層面的升級。
注意：該步驟是否執行是和Step 6中的檢查結果相關的，只有當Timezone的版本小於14時，才需要執行該步驟。
主要參考：Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
1）Timezone升級前的準備工作：
先檢查一下當前的timezone版本：
conn / as sysdba
SELECT version FROM v$timezone_file;
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
然後開始準備工作：
alter session set "_with_subquery"=materialize;
exec DBMS_DST.BEGIN_PREPARE(14);
接著檢查準備狀態：
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       14
DST_UPGRADE_STATE              PREPARE
-- truncate logging tables if they exist.
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
-- log affected data
set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
下面的語句都不能有返回結果：
SELECT * FROM sys.dst$affected_tables;
SELECT * FROM sys.dst$error_table;
SELECT * 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
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

2）真正開始升級Timezone
conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
purge dba_recyclebin;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
alter session set "_with_subquery"=materialize;
EXEC DBMS_DST.BEGIN_UPGRADE(14);
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         14
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE
下面這條語句應該沒有返回結果：
SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
重啟資料庫：
shutdown immediate
startup
升級相關的table：
alter session set "_with_subquery"=materialize;
set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
如果沒有錯誤，則結束升級：
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
最後一次檢查：
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         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE
SELECT * FROM v$timezone_file;
FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14

升級完成後，可以用如下的方式來進行驗證

這次就不會跑錯了。大功告成。