OracleStudy之--OracleTimeZone升級(jí)

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

SQL> SELECT version FROM v$timezone_file;
 VERSION
----------
 4
 
根據(jù)當(dāng)前timezone的版本，又分三種情況：
1）等于14：這已經(jīng)是11g需要的版本了，所以升級(jí)前后都不需要做任何事，這種情況很罕見。
 注意：11.2.0.1.0的timezone最高支持到11，可以通過升級(jí)數(shù)據(jù)庫(kù)到11.2.0.3.0，將timezone升級(jí)到14

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit 
Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0- Production
NLSRTL 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 
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 NAME,VALUE$ FROM PROPS$WHERE 
NAME='DST_PRIMARY_TT_VERSION';
 
NAME VALUE$
-------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 14


2）高于14：升級(jí)前，必須得給11g軟件打上該timezone版本的DST補(bǔ)丁，這種情況也很罕見。

3）低于14：大多數(shù)都是這種情況，在升級(jí)前不需要在11g軟件層面打補(bǔ)丁，在升級(jí)后需要再數(shù)據(jù)庫(kù)層面將Timezone升級(jí)至14，具體看后面的步驟

SQL> set linesize 120
SQL> r
 1* 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

（升級(jí)到14出現(xiàn)以下錯(cuò)誤） 
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

[oracle@rh55 ~]$ find $ORACLE_HOME -name 'zoneinfo'
/u01/app/oracle/product/11.2.0/db_1/oracore/zoneinfo

[oracle@rh55 ~]$ ls -l /u01/app/oracle/product/11.2.0/db_1/oracore/zoneinfo
total 10092
drwxr-xr-x 2 oracle oinstall 4096 Mar 13 11:45 big
drwxr-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的時(shí)區(qū)文件

準(zhǔn)備升級(jí)timezone到11：
SQL> exec DBMS_DST.BEGIN_PREPARE(11);
PL/SQL procedure successfully completed.

查看升級(jí)準(zhǔn)備信息：
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 4
DST_SECONDARY_TT_VERSION 11
DST_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 selected

SQL>SELECT * FROM sys.dst$error_table;
no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
no rows selected

SQL> 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 4
DST_SECONDARY_TT_VERSION 0
DST_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 bytes
Fixed Size 1338336 bytes
Variable Size 427820064 bytes
Database Buffers 192937984 bytes
Redo Buffers 5636096 bytes
Database mounted.
Database opened.

SQL> set serveroutput on
SQL> 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升級(jí)到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 11
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE

SQL> 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 YES
SYSMAN MGMT_PROV_IP_RANGE YES
SYSMAN MGMT_PROV_SUITE_INST_MEMBERS YES
SYSMAN MGMT_PROV_BOOTSERVER YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_L YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_S YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_L YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_S YES
SYSMAN MGMT_PROV_STAGING_DIRS YES
SYSMAN MGMT_PROV_OPERATION YES
SYSMAN MGMT_PROV_ASSIGNMENT YES
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_CONFIG_ACTIVITIES YES
SYSMAN MGMT_PROV_CLUSTER_NODES YES
SYSMAN MGMT_PROV_RPM_REP YES
SYSMAN MGMT_PROV_DEFAULT_IMAGE YES
IX AQ$_STREAMS_QUEUE_TABLE_S YES
IX AQ$_STREAMS_QUEUE_TABLE_L YES
IX AQ$_ORDERS_QUEUETABLE_S YES
IX AQ$_ORDERS_QUEUETABLE_L YES
19 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 627732480 bytes
Fixed Size 1338336 bytes
Variable Size 427820064 bytes
Database Buffers 192937984 bytes
Redo Buffers 5636096 bytes
Database mounted.
Database opened.

SQL> alter session set "_with_subquery"=materialize;
Session altered.

執(zhí)行timezone升級(jí)過程：
SQL> set serveroutput on
SQL> VAR numfail number
SQL> 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_MEMBERS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_STAGING_DIRS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_RPM_REP
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_OPERATION
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_NET_CONFIG
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_IP_RANGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_CLUSTER_NODES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_BOOTSERVER
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
Number of failures: 0
Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_L
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_S
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_L
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_S
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_L
Number of failures: 0
Failures:0
PL/SQL procedure successfully completed.

結(jié)束升級(jí)，校驗(yàn)升級(jí)信息：
SQL> VAR fail number
SQL> 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:0
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 11
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_11.dat 11