After upgrading to Oracle 12.2, we face the issue of RDBMS DST timezones.Then either check support id 1585343.1 or else follow the below step
1.Connect to “sqlplus” and run the “upg_tzv_check.txt” file. If you want to download the content of upg_tzv_check
[oracle@oracle122 admin]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Mar 4 03:32:22 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> @upg_tzv_check.txt 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 12.2.0.1 . WARNING: Current DST_UPGRADE_STATE is PREPARE ! WARNING: DST_UPGRADE_STATE in DATABASE_PROPERTIES need to be NONE WARNING: before running upg_tzv_check.sql . WARNING: Trying to end PREPARE window and then continue A prepare window has been successfully ended. INFO: Database RDBMS DST version is DSTv14 . 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 DSTv26 . INFO: Next step is checking all TSTZ data. INFO: It might take a while before any further output is seen ... 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.
2. Now check the current timezone.
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 14 1 row selected.
3. Now run the “upg_tzv_apply.txt” file. If you want to download the content of upg_tzv_apply
SQL> @upg_tzv_apply.txt INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: The database RDBMS DST version will be updated to DSTv26 . 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 2483027968 bytes Fixed Size 8623736 bytes Variable Size 671091080 bytes Database Buffers 1795162112 bytes Redo Buffers 8151040 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 2483027968 bytes Fixed Size 8623736 bytes Variable Size 671091080 bytes Database Buffers 1795162112 bytes Redo Buffers 8151040 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: "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 Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES2" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_FEEDBACK" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_FEEDBACK_FOLLOWUP" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_WORKSHEET_NOTIFY" 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 DSTv26 . 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.
4. Now check the updated timezone.
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 26 1 row selected.
5. Now again re-run the postupgrade_fixups.sql
As we see now error has been fixed.
