最佳实践｜Oracle 12c 数据库升级时区文件和带有时区数据的时间戳

有时需要在 Oracle 数据库中升级时区文件和带有时区 (TSTZ) 数据的时间戳，例如当您升级到新版本的数据库或夏令时 (DST) 规则发生变化时。

下面是我们的步骤：

1. 检查 Oracle 主目录中可用的最新版本的时区文件：

[oracle@hostname]$ cd $ORACLE_HOME/oracore/
[oracle@hostname oracore]$ ls
mesg zoneinfo

2. 备份当前 zoneinfo 文件

[oracle@hostname zoneinfo]$ 更多 $ORACLE_HOME/oracore/zoneinfo/timezdif.csv

文件版本 1.0
字段：VERSION#、TIMEZONE_NAME、FROM_YEAR、TO_YEAR
注意：TO_YEAR 的 NULL/空白表示 db 可表示/允许的最大年份。

14, Africa/Casablanca, 2010,
14, Africa/Tunis, 2010,
14, America/Argentina/San_Luis, 2010,
14, America/Tijuana, 2010,
14, America/Santiago, 2010, 2010
14, America/Asuncion, 2010 ,
14, 南极洲/凯西, 2010,

还从 sqlplus 检查：

SQL> 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        |14
DST_SECONDARY_TT_VERSION      |0
DST_UPGRADE_STATE             |NONE

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_14.dat              14          0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
14

应清除回收站，并根据以下 MOS 说明设置一些会话参数：

在有大量数据受新 TZ 文件影响的数据库中，通过 DBUA 执行时区升级需要更多时间（文档 ID 2259734.1）
使用 DBMS_DST 更新 12c 第 1 版（12.1.0.1 及更高版本）中的 RDBMS DST 版本（文档 ID 1509653.1）

更改会话集“_with_subquery”=materialize；
更改会话集“_simple_view_merging”=TRUE；
清除 dba_recyclebin；

5. 开始升级过程

SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_PREPARE(26);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.

SQL> shutdown immediate;

SQL> startup upgrade;
ORACLE instance started.

SQL> exec DBMS_DST.BEGIN_UPGRADE(28);

PL/SQL procedure successfully completed.

SQL> 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         28
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              UPGRADE

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

SQL> startup
ORACLE instance started.

SQL> truncate table sys.dst$error_table;

Table truncated.

SQL> truncate table sys.dst$trigger_table;

Table truncated.

--Check possibe errors here
SQL>  SELECT * FROM sys.dst$error_table;

no rows selected

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
     DBMS_DST.UPGRADE_DATABASE(:numfail,
     parallel                  => TRUE,
     log_errors                => TRUE,
     log_errors_table          => 'SYS.DST$ERROR_TABLE',
     log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
     error_on_overlap_time     => TRUE,
     error_on_nonexisting_time => TRUE);
     DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
     END;
   /
 
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"."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.

--Check error again

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

-- Check timezone 
SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_28.dat              28

SQL> 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         28
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

原文标题：Upgrading The Time Zone File And Timestamp With Time Zone Data Oracle Database12c

原文作者：Helios
原文链接：https://heliosguneserol.com/2022/10/04/upgrading-the-time-zone-file-and-timestamp-with-time-zone-data-oracle-database12c/