OracleStudy之--OracleTimeZone升級

來源：懂視網責編：小采時間：2020-11-09 16:15:01

OracleStudy之--OracleTimeZone升級

OracleStudy之--OracleTimeZone升級:Oracle Study之--Oracle TimeZone升級 http://tiany.blog.51cto.com/513694/1411882 Oracle 10gR2升級到Oracle 11gR2 當Oracle database從10gR2升級到11gR2之后，需要升級timezone version，以下詳細介紹了timezone的升級

推薦度：

點擊下載本文 文檔為doc格式

導讀OracleStudy之--OracleTimeZone升級:Oracle Study之--Oracle TimeZone升級 http://tiany.blog.51cto.com/513694/1411882 Oracle 10gR2升級到Oracle 11gR2 當Oracle database從10gR2升級到11gR2之后，需要升級timezone version，以下詳細介紹了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 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 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

查看數據庫當前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升級到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：升級前，必須得給11g軟件打上該timezone版本的DST補丁，這種情況也很罕見。

3）低于14：大多數都是這種情況，在升級前不需要在11g軟件層面打補丁，在升級后需要再數據庫層面將Timezone升級至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

準備升級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/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的時區文件

準備升級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 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升級到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.

執行timezone升級過程：
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.

結束升級，校驗升級信息：
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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

OracleStudy之--OracleTimeZone升級

推薦度：

點擊下載本文 文檔為doc格式

標簽：升級時區 oracle

熱門焦點

OracleStudy之--OracleTimeZone升級

OracleStudy之--OracleTimeZone升級

OracleStudy之--OracleTimeZone升級

最新推薦

猜你喜歡

熱門推薦