oracle 10g 升级到oracle 11g需要升级timezone吗，怎么升级呢

516078670

我的数据是版本是oracle 10.2.0.5 for hpux ia64的，现在要升级到11.2.0，现在数据文件已经升级，文档上说还要升级timezone,但是我在执行升级timezone的时候报错。我想问一下ML牛人，一定要升级timezone吗，我出现的问题该怎么解决呢，急盼答案，在线等

文档上这样写的：

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



但是我执行的时候有以下报错：

SQL> SELECT version FROM v$timezone_file;
   VERSION
----------
         4
SQL> SELECT version FROM v$timezone_file;
   VERSION
----------
         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

SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> exec DBMS_DST.BEGIN_PREPARE(14);
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;
*
ERROR at line 1:
ORA-30094: failed to find the time zone data file for version 14 in
$ORACLE_HOME/oracore/zoneinfo
ORA-06512: at "SYS.DBMS_DST", line 57
ORA-06512: at "SYS.DBMS_DST", line 1258
ORA-06512: at line 1

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4  
PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE

SQL> exec DBMS_DST.BEGIN_PREPARE(14);
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;
*
ERROR at line 1:
ORA-30094: failed to find the time zone data file for version 14 in
$ORACLE_HOME/oracore/zoneinfo
ORA-06512: at "SYS.DBMS_DST", line 57
ORA-06512: at "SYS.DBMS_DST", line 1258
ORA-06512: at line 1

Maclean Liu(刘相兵

FOR 11gR2

SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        14


FOR 10.2.0.5

SQL>  select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL>  SELECT version FROM v$timezone_file;

   VERSION
----------
         4



action plan:

执行以下 查询并 贴出输出：

select * from V$version;
SELECT version FROM v$timezone_file;

saup007

对头，这个问题，我也想问。。。

没升级成功，还。Orz~~~

10.2.0.5 --> 11.2.0.2  升级后，还是4，怎么升级到14.

1. [oracle@dbaone ~]$ sqlplus  / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.2.0 Production on Tue May 15 16:44:46 2012
   
4. 
   
5. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
11. 
    
12. sys@SAUP10G> SELECT version FROM v$timezone_file;
    
13. 
    
14.    VERSION
    
15. ----------
    
16.          4
    
17. 
    
18. sys@SAUP10G> select * from  v$version;
    
19. 
    
20. BANNER
    
21. --------------------------------------------------------------------------------
    
22. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    
23. PL/SQL Release 11.2.0.2.0 - Production
    
24. CORE        11.2.0.2.0        Production
    
25. TNS for Linux: Version 11.2.0.2.0 - Production
    
26. NLSRTL Version 11.2.0.2.0 - Production

复制代码

参考：http://space.itpub.net/26277071/viewspace-713492

1. 1）Timezone升级前的准备工作：
   
2. 先检查一下当前的timezone版本：
   
3. conn / as sysdba
   
4. SELECT version FROM v$timezone_file;
   
5. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
   
6. 一个典型的输出是：
   
7. PROPERTY_NAME                  VALUE
   
8. ------------------------------ ------------------------------
   
9. DST_PRIMARY_TT_VERSION         4
   
10. DST_SECONDARY_TT_VERSION       0
    
11. DST_UPGRADE_STATE              NONE
    
12. 然后开始准备工作：
    
13. alter session set "_with_subquery"=materialize;
    
14. exec DBMS_DST.BEGIN_PREPARE(14);
    
15. 接着检查准备状态：
    
16. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    
17. FROM DATABASE_PROPERTIES
    
18. WHERE PROPERTY_NAME LIKE 'DST_%'
    
19. ORDER BY PROPERTY_NAME;
    
20. 一个典型的输出是：
    
21. PROPERTY_NAME                  VALUE
    
22. ------------------------------ ------------------------------
    
23. DST_PRIMARY_TT_VERSION         4
    
24. DST_SECONDARY_TT_VERSION       14
    
25. DST_UPGRADE_STATE              PREPARE
    
26. -- truncate logging tables if they exist.
    
27. TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
    
28. TRUNCATE TABLE sys.dst$affected_tables;
    
29. TRUNCATE TABLE sys.dst$error_table;
    
30. -- log affected data
    
31. set serveroutput on
    
32. BEGIN
    
33. DBMS_DST.FIND_AFFECTED_TABLES
    
34. (affected_tables => 'sys.dst$affected_tables',
    
35. log_errors => TRUE,
    
36. log_errors_table => 'sys.dst$error_table');
    
37. END;
    
38. /
    
39. 下面的语句都不能有返回结果：
    
40. SELECT * FROM sys.dst$affected_tables;
    
41. SELECT * FROM sys.dst$error_table;
    
42. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
    
43. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
    
44. SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

复制代码

[ 本帖最后由 saup007 于 2012-5-15 17:14 编辑 ]

saup007

1. SQL> SELECT * FROM sys.dst$affected_tables;
   
2. 
   
3. TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                                                                       ROW_COUNT ERROR_COUNT
   
4. ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------- -----------
   
5. SYS                            SCHEDULER$_JOB                 START_DATE                                                                                1           1
   
6. SYS                            SCHEDULER$_JOB                 LAST_ENABLED_TIME                                                                         1           1
   
7. SYS                            SCHEDULER$_JOB                 NEXT_RUN_DATE                                                                             1           1
   
8. SYS                            SCHEDULER$_JOB                 LAST_START_DATE                                                                           1           1
   
9. SYS                            SCHEDULER$_JOB                 LAST_END_DATE                                                                             1           1
   
10. SYS                            SCHEDULER$_JOB_RUN_DETAILS     REQ_START_DATE                                                                           16          16
    
11. SYS                            SCHEDULER$_JOB_RUN_DETAILS     START_DATE                                                                               16          16
    
12. 
    
13. 7 rows selected

复制代码

saup007

1. SQL> SELECT * FROM sys.dst$error_table;
   
2. 
   
3. TABLE_OWNER TABLE_NAME COLUMN_NAME RID ERROR_NUMBER
   
4. ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------
   
5. SYS SCHEDULER$_JOB START_DATE AAABPpAABAAACuiAAA 1882
   
6. SYS SCHEDULER$_JOB LAST_ENABLED_TIME AAABPpAABAAACuiAAA 1882
   
7. SYS SCHEDULER$_JOB NEXT_RUN_DATE AAABPpAABAAACuiAAA 1882
   
8. SYS SCHEDULER$_JOB LAST_START_DATE AAABPpAABAAACuiAAA 1882
   
9. SYS SCHEDULER$_JOB LAST_END_DATE AAABPpAABAAACuiAAA 1882
   
10. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXMAA6 1882
    
11. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXNAAB 1882
    
12. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXNAAJ 1882
    
13. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXNAA9 1882
    
14. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXOAAk 1882
    
15. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXPAAN 1882
    
16. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXPABD 1882
    
17. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXQAAe 1882
    
18. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIxAAQ 1882
    
19. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIxAA+ 1882
    
20. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIyAAO 1882
    
21. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIyAAg 1882
    
22. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI0AAI 1882
    
23. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI2AAC 1882
    
24. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI2AAv 1882
    
25. 
    
26. TABLE_OWNER TABLE_NAME COLUMN_NAME RID ERROR_NUMBER
    
27. ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------
    
28. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI4AAp 1882
    
29. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXMAA6 1882
    
30. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXNAAB 1882
    
31. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXNAAJ 1882
    
32. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXNAA9 1882
    
33. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXOAAk 1882
    
34. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXPAAN 1882
    
35. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXPABD 1882
    
36. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXQAAe 1882
    
37. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIxAAQ 1882
    
38. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIxAA+ 1882
    
39. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIyAAO 1882
    
40. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIyAAg 1882
    
41. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI0AAI 1882
    
42. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI2AAC 1882
    
43. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI2AAv 1882
    
44. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI4AAp 1882
    
45. 
    
46. 37 rows selected

复制代码

saup007

1. SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
   
2. 
   
3. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
   
4. 都没有数据

复制代码

1. SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');
   
2. 
   
3. TABLE_OWNER TABLE_NAME COLUMN_NAME RID ERROR_NUMBER
   
4. ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------
   
5. SYS SCHEDULER$_JOB START_DATE AAABPpAABAAACuiAAA 1882
   
6. SYS SCHEDULER$_JOB LAST_ENABLED_TIME AAABPpAABAAACuiAAA 1882
   
7. SYS SCHEDULER$_JOB NEXT_RUN_DATE AAABPpAABAAACuiAAA 1882
   
8. SYS SCHEDULER$_JOB LAST_START_DATE AAABPpAABAAACuiAAA 1882
   
9. SYS SCHEDULER$_JOB LAST_END_DATE AAABPpAABAAACuiAAA 1882
   
10. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXMAA6 1882
    
11. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXNAAB 1882
    
12. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXNAAJ 1882
    
13. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXNAA9 1882
    
14. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXOAAk 1882
    
15. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXPAAN 1882
    
16. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXPABD 1882
    
17. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAAXQAAe 1882
    
18. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIxAAQ 1882
    
19. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIxAA+ 1882
    
20. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIyAAO 1882
    
21. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJIyAAg 1882
    
22. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI0AAI 1882
    
23. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI2AAC 1882
    
24. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI2AAv 1882
    
25. 
    
26. 
    
27. SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE AAABQHAADAAAJI4AAp 1882
    
28. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXMAA6 1882
    
29. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXNAAB 1882
    
30. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXNAAJ 1882
    
31. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXNAA9 1882
    
32. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXOAAk 1882
    
33. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXPAAN 1882
    
34. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXPABD 1882
    
35. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAAXQAAe 1882
    
36. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIxAAQ 1882
    
37. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIxAA+ 1882
    
38. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIyAAO 1882
    
39. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJIyAAg 1882
    
40. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI0AAI 1882
    
41. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI2AAC 1882
    
42. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI2AAv 1882
    
43. SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE AAABQHAADAAAJI4AAp 1882
    
44. 
    
45. 37 rows selected

复制代码

Maclean Liu(刘相兵

If the database has the incorrect timezone files, then both the DBUA and command line upgrades will abort with an ORA 722 “invalid number” error.


若timezone files 不正确 ， dbua 或 catupgrad会报错 ORA-722

Starting with 10.2.0.4 the database includes timezone V4.  

从10.2.0.4 开始 数据库 包含timezone v4 ， 已升级到 timezone v4的DB 升级11.2



If the source database is using a timezone file lower or equal to :
- version 11 and target oracle home is 11.2.0.1
or
- version 14 and target oracle home is 11.2.0.2/11.2.0.3

then nothing to do in source home , it is suggested to use DBMS_DST package AFTER the upgrade :
Note 977512.1 : Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST


timezone version file < 4 无需更新 timezone

但是 oracle建议 在升级后 更新 DST

但是实际 如果没有在应用程序中使用 TIMESTAMP WITH TIME ZONE 数据类型的话 ，无需upgrade DST