oracle 11g upgrade到19c，手动更新TIMEZONE

王亮

更新 TIMEZONE 版本需要从MOS上下载所需的升级脚本(文档 ID 1585343.1)

查看当前版本
SQL> SELECT version FROM v$timezone_file;
   VERSION
----------
        14
1 row selected.  

解压升级包文件
[oracle@dbserver DBMS_DST_scriptsV1.9]$ ll
-rw-r--r--. 1 oracle dba  6294 Jan  8  2015 countstarTSTZ.sql
-rw-r--r--. 1 oracle dba  7213 Mar 17  2018 countstatsTSTZ.sql
-rw-r--r--. 1 oracle dba 19502 Aug 22  2014 upg_tzv_apply.sql
-rw-r--r--. 1 oracle dba 31010 Aug 22  2014 upg_tzv_check.sql

检查当前环境     
SQL> @DBMS_DST_scriptsV1.9/upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv4 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
WARNING: Some TSTZ data that needs correcting is detected
WARNING: during FIND_AFFECTED_TABLES.
WARNING: This is 1882 type data.
WARNING: For more information see
WARNING: note 977512.1 for 11gR2 or note 1509653.1 for 12c .
WARNING: This is a message in case you want to check this data manually.
WARNING: The exact rows are in SYS.DST$ERROR_TABLE
WARNING: The upg_tzv_apply.sql script will adjust this data automatically.
WARNING: It will not stop the DST upgrade.
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

更新TIMEZONE版本
SQL> @DBMS_DST_scriptsV1.9/upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area  713027608 bytes
Fixed Size                  8900632 bytes
Variable Size             486539264 bytes
Database Buffers          209715200 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area  713027608 bytes
Fixed Size                  8900632 bytes
Variable Size             486539264 bytes
Database Buffers          209715200 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.

再次检查版本
版本号已成功更新为32
SQL> SELECT version FROM v$timezone_file;
   VERSION
----------
        32
1 row selected.