Oracle Study之--Oracle TimeZone升级

Oracle Study之--Oracle TimeZone升级

http://tiany.blog.51cto.com/513694/1411882

Oracle 10gR2升级到Oracle 11gR2

当Oracle database从10gR2升级到11gR2之后，需要升级timezone version，以下详细介绍了timezone的升级过程。

Oracle timezone 升级

背景描述：

如果需要支持一个国际化的应用，那么数据库端的国际化特性的支持也就显得尤其重要。Oracle中有很多特性支持国际化，如字符集、时区等等。如果相关参数设置不当，或者由于对相关特性不够了解，以至于在设计阶段没有考虑完全，那么肯定会对应用造成一定的损失。

升级前准备：

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 16 14:07:28 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE    11.2.0.1.0      ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production

查看数据库当前timezone 版本：

SQL> SELECT version FROM v$timezone_file;   VERSION----------         4         根据当前timezone的版本，又分三种情况：1）等于14：这已经是11g需要的版本了，所以升级前后都不需要做任何事，这种情况很罕见。   注意：11.2.0.1.0的timezone最高支持到11，可以通过升级数据库到11.2.0.3.0，将timezone升级到14SQL> select * from v$version; BANNER----------------------------------------------------------------------------Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE   11.2.0.1.0      ProductionTNS for 64-bit Windows: Version 11.2.0.1.0- ProductionNLSRTL Version 11.2.0.1.0 - Production SQL> SELECT NAME,VALUE$ FROM PROPS$WHERE NAME='DST_PRIMARY_TT_VERSION'; NAME                           VALUE$-----------------------------------------------------------------------DST_PRIMARY_TT_VERSION         11 SQL> select * from v$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE   11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 -ProductionNLSRTL Version 11.2.0.3.0 - Production SQL> SELECT NAME,VALUE$ FROM PROPS$WHERE NAME='DST_PRIMARY_TT_VERSION'; NAME                           VALUE$-------------------------------------------------------------------------------DST_PRIMARY_TT_VERSION         142）高于14：升级前，必须得给11g软件打上该timezone版本的DST补丁，这种情况也很罕见。3）低于14：大多数都是这种情况，在升级前不需要在11g软件层面打补丁，在升级后需要再数据库层面将Timezone升级至14，具体看后面的步骤SQL> set linesize 120SQL> r  1* SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAMEPROPERTY_NAME                  VALUE------------------------------ --------------------------------------------------DST_PRIMARY_TT_VERSION         4DST_SECONDARY_TT_VERSION       0DST_UPGRADE_STATE              NONE

准备升级timezone到11：

（升级到14出现以下错误） 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/zoneinfoORA-06512: at "SYS.DBMS_DST", line 57ORA-06512: at "SYS.DBMS_DST", line 1258ORA-06512: at line 1[oracle@rh65 ~]$ find $ORACLE_HOME -name 'zoneinfo'/u01/app/oracle/product/11.2.0/db_1/oracore/zoneinfo[oracle@rh65 ~]$ ls -l /u01/app/oracle/product/11.2.0/db_1/oracore/zoneinfototal 10092drwxr-xr-x 2 oracle oinstall   4096 Mar 13 11:45 bigdrwxr-xr-x 2 oracle oinstall   4096 Mar 13 11:45 little-rw-r--r-- 1 oracle oinstall   5725 Jun 12  2009 readme.txt-rw-r--r-- 1 oracle oinstall  25681 Jul 16  2009 timezdif.csv-rw-r--r-- 1 oracle oinstall 792894 Jul 31  2009 timezlrg_10.dat-rw-r--r-- 1 oracle oinstall 787272 Jul 31  2009 timezlrg_11.dat-rw-r--r-- 1 oracle oinstall 493675 Jul 31  2009 timezlrg_1.dat-rw-r--r-- 1 oracle oinstall 507957 Jul 31  2009 timezlrg_2.dat-rw-r--r-- 1 oracle oinstall 527717 Jul 31  2009 timezlrg_3.dat-rw-r--r-- 1 oracle oinstall 531137 Jul 31  2009 timezlrg_4.dat-rw-r--r-- 1 oracle oinstall 587487 Jul 31  2009 timezlrg_5.dat-rw-r--r-- 1 oracle oinstall 586750 Jul 31  2009 timezlrg_6.dat-rw-r--r-- 1 oracle oinstall 601242 Jul 31  2009 timezlrg_7.dat-rw-r--r-- 1 oracle oinstall 616723 Jul 31  2009 timezlrg_8.dat-rw-r--r-- 1 oracle oinstall 801410 Jul 31  2009 timezlrg_9.dat-rw-r--r-- 1 oracle oinstall 345637 Jul 31  2009 timezone_10.dat-rw-r--r-- 1 oracle oinstall 345356 Jul 31  2009 timezone_11.dat-rw-r--r-- 1 oracle oinstall 274427 Jul 31  2009 timezone_1.dat-rw-r--r-- 1 oracle oinstall 274900 Jul 31  2009 timezone_2.dat-rw-r--r-- 1 oracle oinstall 286651 Jul 31  2009 timezone_3.dat-rw-r--r-- 1 oracle oinstall 286264 Jul 31  2009 timezone_4.dat-rw-r--r-- 1 oracle oinstall 286310 Jul 31  2009 timezone_5.dat-rw-r--r-- 1 oracle oinstall 286217 Jul 31  2009 timezone_6.dat-rw-r--r-- 1 oracle oinstall 286815 Jul 31  2009 timezone_7.dat-rw-r--r-- 1 oracle oinstall 302100 Jul 31  2009 timezone_8.dat-rw-r--r-- 1 oracle oinstall 351525 Jul 31  2009 timezone_9.dat在zoneinfo目录下没有version 14的时区文件准备升级timezone到11：SQL> exec DBMS_DST.BEGIN_PREPARE(11);PL/SQL procedure successfully completed.查看升级准备信息：SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  2  FROM DATABASE_PROPERTIES  3  WHERE PROPERTY_NAME LIKE 'DST_%'  4  ORDER BY PROPERTY_NAME;PROPERTY_NAME                  VALUE------------------------------ ----------------------------------------DST_PRIMARY_TT_VERSION         4DST_SECONDARY_TT_VERSION       11DST_UPGRADE_STATE              PREPARE

准备升级工作：

SQL> BEGIN  2  DBMS_DST.FIND_AFFECTED_TABLES  3  (affected_tables => 'sys.dst$affected_tables',  4  log_errors => TRUE,  5  log_errors_table => 'sys.dst$error_table');  6  END;  7  /PL/SQL procedure successfully completed.SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;Table truncated.SQL> TRUNCATE TABLE sys.dst$affected_tables;Table truncated.SQL> TRUNCATE TABLE sys.dst$error_table;Table truncated.SQL> SELECT * FROM sys.dst$affected_tables;no rows selectedSQL>SELECT * FROM sys.dst$error_table;no rows selectedSQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';no rows selectedSQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';no rows selectedSQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');no rows selected

结束升级准备：

SQL> EXEC DBMS_DST.END_PREPARE;PL/SQL procedure successfully completed.SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  2  FROM DATABASE_PROPERTIES  3  WHERE PROPERTY_NAME LIKE 'DST_%'  4  ORDER BY PROPERTY_NAME;PROPERTY_NAME                  VALUE------------------------------ ----------------------------------------DST_PRIMARY_TT_VERSION         4DST_SECONDARY_TT_VERSION       0DST_UPGRADE_STATE              NONE

升级过程：

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup upgrade;ORACLE instance started.Total System Global Area  627732480 bytesFixed Size                  1338336 bytesVariable Size             427820064 bytesDatabase Buffers          192937984 bytesRedo Buffers                5636096 bytesDatabase mounted.Database opened.SQL> set serveroutput onSQL> purge dba_recyclebin;DBA Recyclebin purged.SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;Table truncated.SQL> TRUNCATE TABLE sys.dst$affected_tables;Table truncated.SQL> TRUNCATE TABLE sys.dst$error_table;Table truncated.SQL> alter session set "_with_subquery"=materialize;Session altered.将timezone version升级到11：SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);PL/SQL procedure successfully completed.SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  2  FROM DATABASE_PROPERTIES  3  WHERE PROPERTY_NAME LIKE 'DST_%'  4  ORDER BY PROPERTY_NAME;PROPERTY_NAME                  VALUE------------------------------ ----------------------------------------DST_PRIMARY_TT_VERSION         11DST_SECONDARY_TT_VERSION       4DST_UPGRADE_STATE              UPGRADESQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';OWNER                          TABLE_NAME                     UPG------------------------------ ------------------------------ ---SYSMAN                         MGMT_PROV_NET_CONFIG           YESSYSMAN                         MGMT_PROV_IP_RANGE             YESSYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YESSYSMAN                         MGMT_PROV_BOOTSERVER           YESSYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YESSYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YESSYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YESSYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YESSYSMAN                         MGMT_PROV_STAGING_DIRS         YESSYSMAN                         MGMT_PROV_OPERATION            YESSYSMAN                         MGMT_PROV_ASSIGNMENT           YESOWNER                          TABLE_NAME                     UPG------------------------------ ------------------------------ ---SYSMAN                         MGMT_CONFIG_ACTIVITIES         YESSYSMAN                         MGMT_PROV_CLUSTER_NODES        YESSYSMAN                         MGMT_PROV_RPM_REP              YESSYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YESIX                             AQ$_STREAMS_QUEUE_TABLE_S      YESIX                             AQ$_STREAMS_QUEUE_TABLE_L      YESIX                             AQ$_ORDERS_QUEUETABLE_S        YESIX                             AQ$_ORDERS_QUEUETABLE_L        YES19 rows selected.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area  627732480 bytesFixed Size                  1338336 bytesVariable Size             427820064 bytesDatabase Buffers          192937984 bytesRedo Buffers                5636096 bytesDatabase mounted.Database opened.SQL> alter session set "_with_subquery"=materialize;Session altered.执行timezone升级过程：SQL> set serveroutput onSQL> VAR numfail numberSQL> BEGIN  2  DBMS_DST.UPGRADE_DATABASE(:numfail,  3  parallel => TRUE,  4  log_errors => TRUE,  5  log_errors_table => 'SYS.DST$ERROR_TABLE',  6  log_triggers_table => 'SYS.DST$TRIGGER_TABLE',  7  error_on_overlap_time => FALSE,  8  error_on_nonexisting_time => FALSE);  9  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); 10  END; 11  /Table list: SYSMAN.MGMT_PROV_SUITE_INST_MEMBERSNumber of failures: 0Table list: SYSMAN.MGMT_PROV_STAGING_DIRSNumber of failures: 0Table list: SYSMAN.MGMT_PROV_RPM_REPNumber of failures: 0Table list: SYSMAN.MGMT_PROV_OPERATIONNumber of failures: 0Table list: SYSMAN.MGMT_PROV_NET_CONFIGNumber of failures: 0Table list: SYSMAN.MGMT_PROV_IP_RANGENumber of failures: 0Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGENumber of failures: 0Table list: SYSMAN.MGMT_PROV_CLUSTER_NODESNumber of failures: 0Table list: SYSMAN.MGMT_PROV_BOOTSERVERNumber of failures: 0Table list: SYSMAN.MGMT_PROV_ASSIGNMENTNumber of failures: 0Table list: SYSMAN.MGMT_CONFIG_ACTIVITIESNumber of failures: 0Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_SNumber of failures: 0Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_LNumber of failures: 0Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_SNumber of failures: 0Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_LNumber of failures: 0Table list: IX.AQ$_STREAMS_QUEUE_TABLE_SNumber of failures: 0Table list: IX.AQ$_STREAMS_QUEUE_TABLE_LNumber of failures: 0Table list: IX.AQ$_ORDERS_QUEUETABLE_SNumber of failures: 0Table list: IX.AQ$_ORDERS_QUEUETABLE_LNumber of failures: 0Failures:0PL/SQL procedure successfully completed.结束升级，校验升级信息：SQL> VAR fail numberSQL> BEGIN  2  DBMS_DST.END_UPGRADE(:fail);  3  DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);  4  END;  5  /An upgrade window has been successfully ended.Failures:0PL/SQL procedure successfully completed.

确认升级成功：

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  2  FROM DATABASE_PROPERTIES  3  WHERE PROPERTY_NAME LIKE 'DST_%'  4  ORDER BY PROPERTY_NAME;PROPERTY_NAME                  VALUE------------------------------ ----------------------------------------DST_PRIMARY_TT_VERSION         11DST_SECONDARY_TT_VERSION       0DST_UPGRADE_STATE              NONESQL> SELECT * FROM v$timezone_file;FILENAME                VERSION-------------------- ----------timezlrg_11.dat              11