Oracle DST (Daylight Saving Time)

Leave a Comment / Oracle / By

DST 介紹

夏令時、日光節約時間（daylight saving time，DST) 簡單來說，在全年白天時間較短的國家(太陽珍貴)，因為夏天時天比較快亮，而得出想要享有更多的日光的做法，方式是夏天時將時間”某天”調快一小時，(大部分是在周末凌晨23:59 突然變成01:00，為了不影響週間的工作)，早點起床，那其實物理上是少睡了一小時，等秋天時再把他調回去(這稱為標準時間)。

這是以前的做法，如今也有許多人探討，(光照發明)已不在被日光給受限，加上鎢絲燈被LED等取代，其實耗用不會太多的電，(冷氣發明)在炎熱的國家我未必想出門，日光的善用與能源的耗用，真的值得嗎? (通訊進步)DST的調整如果發生在3、4個國家的跨國會議上，是相當複雜的，更慘的是每半年調整時都要再折騰一次。 (以上需要致敬這支影片，10年前的作品介紹得相當好。)

世界時區地圖 / 時區轉換網站

Oracle DST

針對DST 的議題，各個地區有時候還會對其做修改，而資訊系統在這上面的應用就相當麻煩，所以Oracle對其則就需要去更新DST的補丁，以用來更新time zone file，目前的版本已經出到DSTv38，這些time zone file都是會包含先前的版本更改、是累積的。每當發出新的一版，就會指出與前一版的差別，哪一個地區調整了DST。以 DSTv38 舉例 :
 
Changes included in tzdata2022a
Palestine(巴基斯坦) will spring forward on 2022-03-27, not -03-26.
zdump -v now outputs better failure indications.
Bug fixes for code that reads corrupted TZif data.
 
也可以在Timezone Data Versions in the JRE Software 看到time zone file的內容。
 

查詢DST版本

11gR2以上的版本

SQL> SELECT version FROM v$timezone_file;

正常來說這個值要等同於，下面的 DST_PRIMARY_TT_VERSION 產出

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like ‘DST%’;
DST_PRIMARY_TT_VERSION : 現在的DST版本
DST_SECONDARY_TT_VERSION : 正常應該為 0，如果不是可能正處於升級階段
DST_UPGRADE_STATE : 正常應該為NONE ，另外也有可能是PREPARE，UPGRADE,或 DATAPUMP

     如果顯示PREPARE，代表正在做升級前的準備，可以執行SQL> EXEC DBMS_DST.END_PREPARE; 將它結束
     如果顯示UPGRADE，代表正在做升級
     如果顯示DATAPUMP，代表有Datapump正在執行，可以查詢DBA_DATAPUMP_JOBS

升級DST版本

一般而言平時即便有新的DST 版本，我們也無需去更新，但我們在使用autoupgrade升級資料庫時，預設在config file內的設定也是會自動幫我們升級的，以下有一個範例是當autoupgrade失敗時我們採取手動升級DST的步驟。關於Autoupgrade的步驟可以參考這篇文章

1.檢視autoupgrade 相關log，可以看出在postfix階段升級timezone時失敗了

$cat /oracle/app/db/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
[Stage Name] POSTFIXUPS
[Status] FAILURE 
[Start Time] 2022-07-06 11:27:07 
[Duration] 0:04:19 
[Log Directory] /oracle/app/db/cfgtoollogs/autoupgrade/orcl/orcl1/101/postfixups 
[Detail] /oracle/app/db/cfgtoollogs/autoupgrade/orcl/orcl1/101/postfixups/postfixups.xml 
Cause:None 
Reason:None 
Action:None 
Info:None 
ExecutionError:Yes 
Error Message:1 fixups with runtime errors
$cat /oracle/app/db/cfgtoollogs/autoupgrade/orcl/orcl1/101/postfixups/postfixups_orcl1.log
2022-07-06 11:31:14.899 ERROR There was a problem while executing the TimeZone Upgrade on [orcl1]
It is recommended to run the TimeZone Upgrade manually on [orcl1] before resuming. Refer to MOS Note 1509653.1 for 
details.{1} - TimeZoneAgent.executeUpgrade
java.sql.SQLException: There were errors during the TimeZone Upgrade 
at oracle.upgrade.autoupgrade.timezone.TimeZoneAgent.executeUpgrade(TimeZoneAgent.java:122) 
at oracle.upgrade.commons.dbinspector.checks.old_time_zones_exist.fixUpCode(old_time_zones_exist.java:198) 
at oracle.upgrade.commons.dbinspector.tools.Check.runFix(Check.java:253) 
at oracle.upgrade.commons.dbinspector.tools.FixUpTrigger.executeFixUp(FixUpTrigger.java:204) 
at oracle.upgrade.commons.dbinspector.tools.FixUpTrigger.call(FixUpTrigger.java:145) 
at oracle.upgrade.commons.dbinspector.tools.FixUpTrigger.call(FixUpTrigger.java:70) 
at java.util.concurrent.FutureTask.run(FutureTask.java:277) 
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:522) 
at java.util.concurrent.FutureTask.run(FutureTask.java:277) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1153) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) 
at java.lang.Thread.run(Thread.java:785)
2022-07-06 11:31:14.914 ERROR 
============================ check info ============================ 
[orcl1][OLD_TIME_ZONES_EXIST][WARNING] 
============================ check info ============================ 
=========================== trace start ============================== 
Exception: SQLException Err message: There were errors during the TimeZone Upgrade 
java.sql.SQLException: There were errors during the TimeZone Upgrade 
at oracle.upgrade.autoupgrade.timezone.TimeZoneAgent.executeUpgrade(TimeZoneAgent.java:122) 
at oracle.upgrade.commons.dbinspector.checks.old_time_zones_exist.fixUpCode(old_time_zones_exist.java:198) 
at oracle.upgrade.commons.dbinspector.tools.Check.runFix(Check.java:253) 
at oracle.upgrade.commons.dbinspector.tools.FixUpTrigger.executeFixUp(FixUpTrigger.java:204) 
at oracle.upgrade.commons.dbinspector.tools.FixUpTrigger.call(FixUpTrigger.java:145) 
at oracle.upgrade.commons.dbinspector.tools.FixUpTrigger.call(FixUpTrigger.java:70) 
at java.util.concurrent.FutureTask.run(FutureTask.java:277) 
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:522) 
at java.util.concurrent.FutureTask.run(FutureTask.java:277) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1153) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) 
at java.lang.Thread.run(Thread.java:785) 
============================ trace end ============================= FixUpTrigger.executeFixUp 
2022-07-06 11:31:14.918 INFO Finished fixup [OLD_TIME_ZONES_EXIST][orcl1][RUNTIME ERROR] - FixUpTrigger.executeFixUp

2. 檢視目前DST版本

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like ‘DST%’;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ---------------
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 26
DST_UPGRADE_STATE NONE

SQL> select tz_version from registry$database;
TZ_VERSION 
---------- 
26

SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID 
-------------------- ---------- ---------- 
timezlrg_26.dat 26 0


3. GET_LATEST_TIMEZONE_VERSION使用包中的函數顯示時區文件的最新可用版本DBMS_DST。

SQL> select dbms_DST.get_latest_timezone_version from dual;
GET_LATEST_TIMEZONE_VERSION
--------------------------- 
32

4.準備DST升級

SQL> DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_prepare(l_tz_version);
END;
/

5. 檢查我們將要嘗試的升級

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like ‘DST%’;

PROPERTY_NAME PROPERTY_VALUE 
------------------------------ --------------- 
DST_SECONDARY_TT_VERSION 32 
DST_PRIMARY_TT_VERSION 26 
DST_UPGRADE_STATE PREPARE


6. 清空包含受影響表列表和錯誤的默認表

SQL> truncate table sys.dst$affected_tables;
SQL> truncate table sys.dst$error_table;

7. 查找受升級影響的表

SQL> exec DBMS_DST.find_affected_tables;
SQL> select * from sys.dst$affected_tables;
SQL> select * from sys.dst$error_table;

8.結束準備階段

SQL> EXEC DBMS_DST.end_prepare;
A prepare window has been successfully ended.

9. 調整參數將資料庫以單節點模式開成mount mode

SQL> alter system set cluster_database=false scope=spfile;
$ srvctl stop database -d emdbs
SQL> startup upgrade

10. 開始升級到最新版本

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
l_tz_version PLS_INTEGER;
BEGIN
SELECT DBMS_DST.get_latest_timezone_version
INTO l_tz_version
FROM dual;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;/
l_tz_version=32
An upgrade window has been successfully started.

11. 重新開啟RAC資料庫

SQL> alter system set cluster_database=TRUE scope=spfile;
SQL> shutdown immediate;
$ srvctl start database -d emdbs

12. 升級 database time zone file

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line(‘DBMS_DST.upgrade_database : l_failures=’ || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line(‘DBMS_DST.end_upgrade : l_failures=’ || l_failures);
END;
/

13. 升級完成，檢視結果

SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0


SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like ‘DST%’;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ---------------
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 32
DST_UPGRADE_STATE NONE
Oracle DST
Kxodia 肯佐迪亞

Leave a Comment

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *