From The DBA Perspective: Highlights from Oracle OpenWorld 2017
Again this year Oracle OpenWorld was packed with too many keynotes, sessions and activities to choose from. In my very first blog post, I would like ... Read More
Learn more about why Eclipsys has been named the 2023 Best Workplaces in Technology and Ontario, Certified as a Great Place to Work in Canada and named Canada’s Top SME Employer!
Learn more!It is sometimes necessary to upgrade the time zone file and timestamp with time zone (TSTZ) data in an Oracle database, for instance when you upgrade to a new version of the database or when the rules for Daylight Saving Time (DST) change.
Since version 11.2, the update process was straightforward: one would simply download the proper scripts from this My Oracle Support (MOS) note and run them:
With version 12.2.0.1, the update procedure has changed drastically as the DST files are now included in the RDBMS home. The procedure is also part of the documentation:
In the following article, I will show the steps to upgrade the time zone file and TSTZ data on a database that has just been upgraded from 11.2.0.4 to 12.2.0.1.
1. Check the latest version of the time zone file available in the Oracle home:
echo $ORACLE_HOME /appl/oracle/12201 cd $ORACLE_HOME/oracore/zoneinfo head -2 readme.txt Current Structure version: 3 Current Content Version :26
According to the output the latest version available is 26. This version number will be used in the next steps.
2. Check the status of the database:
SQL> conn / as sysdba Connected. SQL> set pagesize 9999 linesize 132 SQL> col PROPERTY_NAME format a30 SQL> col value format a20 SQL> SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production TNS for Solaris: Version 12.2.0.1.0 - Production NLSRTL Version 12.2.0.1.0 - Production 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
We see above that the version of the database is 12.2.0.1.0 and that the version of the time zone file is 14.
SQL> set pagesize 9999 linesize 132 SQL> col PROPERTY_NAME format a30 SQL> col value format a20 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 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
The previous query is important, and one should expect the following output:
DST_PRIMARY_TT_VERSION Same as “select VERSION from v$timezone_file”
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
Do not continue if the output is different.
3. Understanding the upgrade process:
Step 1 (optional): start a prepare window
This step will show how much data must be updated in the database, give an estimate of how much time the upgrade will take, and show potential semantic errors. All the errors should be fixed before running the upgrade.
Step 2: start an upgrade window
This step is the time when changes to the data will occur.
4. Avoid performance issues:
The recycle bin should be purged, and some session parameters set as per the following MOS notes:
alter session set "_with_subquery"=materialize; alter session set "_simple_view_merging"=TRUE; purge dba_recyclebin;
5. Start a prepare window:
Use the version number (26) found in step 1 in the following statement:
SQL> set serveroutput on SQL> exec DBMS_DST.BEGIN_PREPARE(26); A prepare window has been successfully started. 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 14 DST_SECONDARY_TT_VERSION 26 DST_UPGRADE_STATE PREPARE
We see that the time zone file will be upgraded from version 14 to 26 and that the upgrade is in the prepare state.
Next, the
DBMS_DST.FIND_AFFECTED_TABLES
procedure can be invoked to find the tables that will be impacted by the upgrade and to list the errors.The default tables
sys.dst$affected_tables and sys.dst$error_table
will be populated:
SQL> exec DBMS_DST.FIND_AFFECTED_TABLES; PL/SQL procedure successfully completed. SQL> select count(*) from sys.dst$affected_tables; COUNT(*) ---------- 0 SQL> select count(*) from sys.dst$error_table; COUNT(*) ---------- 0
The errors can be fixed by looking at the documentation:
Finally, end the prepare window:
SQL> exec DBMS_DST.END_PREPARE; A prepare window has been successfully ended. 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 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
6. Start an upgrade window:
The following steps will modify the database, so it is recommended to either take a backup of the database or to enable Flashback Database before continuing.
Before starting an upgrade window using DBMS_DST.BEGIN_UPGRADE, the database needs to be restarted in UPGRADE mode:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade; ORACLE instance started. Total System Global Area 2181038080 bytes Fixed Size 8604296 bytes Variable Size 1258296696 bytes Database Buffers 905969664 bytes Redo Buffers 8167424 bytes Database mounted. Database opened. SQL> set serveroutput on SQL> exec DBMS_DST.BEGIN_UPGRADE(26); 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 26 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE UPGRADE DBMS_DST.BEGIN_UPGRADE will upgrade the system tables (owner by SYS, DBSNMP, etc.) that contain TSTZ data and will mark user tables (containing TSTZ data) with UPGRADE_IN_PROGRESS in the ALL_TSTZ_TABLES view:
col OWNER format a30 col TABLE_NAME format a30 SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES; OWNER TABLE_NAME UPG ------------------------------ ------------------------------ --- SYS ACLMVREFSTAT$ NO SYS ALERT_QT NO SYS AQ$_AQ_PROP_TABLE_L NO SYS AQ$_AQ_PROP_TABLE_S NO SYS AQ$_DEQUEUE_LOG_PARTITION_MAP NO ... GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_L YES GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S YES ...
Restart the database in normal mode:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2181038080 bytes Fixed Size 8604296 bytes Variable Size 1258296696 bytes Database Buffers 905969664 bytes Redo Buffers 8167424 bytes Database mounted. Database opened.
Truncate the sys.dst$error_table and sys.dst$trigger_table error and trigger tables before starting the upgrade process:
SQL> truncate table sys.dst$error_table; Table truncated. SQL> truncate table sys.dst$trigger_table; Table truncated.
Then, upgrade the TSTZ data in all user tables by invoking DBMS_DST.UPGRADE_DATABASE:
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 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 => TRUE, 8 error_on_nonexisting_time => TRUE); 9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); 10 END; 11 / 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 Failures:0 PL/SQL procedure successfully completed. SQL> SELECT * FROM sys.dst$error_table; no rows selected
The upgrade took just a few seconds since there is almost no user data to be modified in my database. The errors can be fixed by looking at the documentation:
Finally, end the prepare window:
SQL> BEGIN 2 DBMS_DST.END_UPGRADE(:numfail); 3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); 4 END; 5 / An upgrade window has been successfully ended. Failures:0 PL/SQL procedure successfully completed.
7. Check the status of the database:
SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_26.dat 26 0 SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 14 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 26 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
As you see above, v$timezone_file and registry$database don’t report the same information. This is because registry$database is only populated by the Pre-Upgrade information tool. It can be manually updated as shown in this MOS note:
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file); 1 row updated. SQL> commit; Commit complete. SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 26 SQL> exit
This completes this blog post on upgrading the time zone file and timestamp with time zone data in a 12.2 Oracle database.
Again this year Oracle OpenWorld was packed with too many keynotes, sessions and activities to choose from. In my very first blog post, I would like ... Read More