Shulou(Shulou.com)06/01 Report--

Oracle Study-- Oracle TimeZone upgrade

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

Upgrade from Oracle 10gR2 to Oracle 11gR2

When Oracle database is upgraded from 10gR2 to 11gR2, timezone version needs to be upgraded. The process of upgrading timezone is described in detail below.

Oracle timezone upgrade

Background description:

If you need to support an internationalized application, then the database-side internationalization feature support is particularly important. There are many features in Oracle that support internationalization, such as character sets, time zones, and so on. If the relevant parameters are not set properly, or because of the lack of understanding of the relevant features, it is not fully considered in the design stage, then it will certainly cause some loss to the application.

Prepare before upgrade:

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

View the current timezone version of the database:

SQL > SELECT version FROM timezone file; VERSION- 4 is divided into three cases depending on the current timezone version: 1) equals 14: this is already the required version of 11g, so it doesn't need to do anything before and after the upgrade, which is rare. Note: timezone of 11.2.0.1.0 is supported up to 11. You can upgrade timezone to 14SQL > select * from v$version by upgrading the database to 11.2.0.3.0. 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) above 14: before upgrad It is also rare that 11g software has to be patched with this timezone version of DST. 3) less than 14: this is the case in most cases. You don't need to patch 11g software before upgrading, but you need to upgrade Timezone to 14 at database level after upgrade. See the following steps: 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

Prepare to upgrade timezone to 11:

(the following error occurred when upgrading to 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 -Rafael 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 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 timezone_7.dat-rw-r--r-- 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 does not have a time zone file for version 14 in the zoneinfo directory to upgrade timezone to 11:SQL > exec DBMS_DST.BEGIN_PREPARE (11) PL/SQL procedure successfully completed. View upgrade preparation information: 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

Prepare for the upgrade:

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 stories leading no rows selectedSQL > SELECT * FROM sys.dst$error_table where ERROR_NUMBER=' 1878 stories leading no rows selectedSQL > SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878') no rows selected

End the upgrade preparation:

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

Upgrade process:

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. Upgrade timezone version to 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. Perform the timezone upgrade process: 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. End the upgrade and verify the upgrade information: 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.

Confirm that the upgrade was successful:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.