There is a feature in Oracle Database 21c I planned to write about for a very long time: Zero Downtime Upgrade of Timezone Data in Oracle 21c and 23c. This sounds very promising – but let us take a closer look together in this blog post.
Photo by Bruno Figueiredo on Unsplash
Time Zone Upgrade?
There is of course an ongoing discussion whether and when you should upgrade the DST version of your database to a current value. Since we all live in a more and more globalized world, this topic becomes more and more important. Even more important as you may have read on our blog already, since Oracle 19.18.0 we ship all available DST patches by default easing your life hopefully quite a bit.
This is how you check the current time zone version of your database and PDBs easily:
column VALUE$ format a8 alter session set "_exclude_seed_cdb_view"=FALSE; select VALUE$, CON_ID from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by CON_ID; VALUE$ CON_ID -------- ---------- 35 1 35 2 35 3
So you’ll see above that all my 21c containers are on DST V.35, the default in Oracle Database 21c.
Oh, wait a bit. DST patches get only shipped with the 19c RUs – but not with 21c. I am quite surprised. Not a big deal for most of you since not many are using 21c – but for my tests it means that I need to fetch and install newer time zone patches before I can continue. MOS Note: 412160.1 – Primary Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches is my friend here.
Now, the big burden with time zone upgrades generally is that it requires downtime. The database will be restarted in UPGRADE mode to adjust the dictionary, and then it will be restarted again in NORMAL mode to adjust potential time zone related data in the database. Since this uses exclusive locks, as soon as you have DST data, you may consider this as additional downtime as an application would need to be able to react to locks on data it wants to INSERT, UPDATE and/or DELETE.
In order to ease this, you could use the scripts in ?/rdbms/admin:
$ ls utltz* utltz_countstar.sql utltzu92.sql utltz_upg_apply_sys.sql utltz_upg_check.sql utltz_countstats.sql utltz_upg_apply.sql utltz_upg_apply_user.sql utltzuv2.sql
The important ones are utltz_upg_check.sql and utltz_upg_apply.sql which need to be run one after another.
What do Oracle 21c and Oracle 23c promise?
The Zero Downtime Upgrade of Time Zone Data feature appeared in the Oracle Database 21c New Features Guide under the Globalization section which is a sub-section of Application Development. There it stated:
The process of upgrading timezone data to reflect up-to-date Governmental Daylight Saving Time rules and to change existing data to reflect these new rules is accomplished in a streamlined, simplified, and automated manner without the need of any downtime or significant impact on the availability of timezone data for queries and DML operations.
Zero downtime upgrade of timezone information removes the burden of database administrators to schedule a downtime window for this operation, removing complexity and obstacles towards continuous 24×7 availability of any database system.
This sounds very promising – and actualy exactly as what DBAs and SysOps people have looked for.
Once you jump to the Globalization Guide to chapter 4.7.1, you will find all the details – and quite a bit of text. But what puzzled me is that there seems to be a clear distinction between the above mentioned scripts and the new “no downtime” approach. At first sight it looks to me that the provided scripts are not capable of following the new approach. Of course, you will find out more when you read on.
A new parameter
In order to allow the Zero Downtime Upgrade for Time Zone data a new parameter got introduced in Oracle Database 21c:
show parameter TIMEZONE_VERSION_UPGRADE_ONLINE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ timezone_version_upgrade_online boolean FALSE
TIMEZONE_VERSION_UPGRADE_ONLINE is not in Oracle Database 19c obviously.
The explanation tells you:
In releases prior to Oracle Database 21c, you were required to put the database in UPGRADE mode in order to initiate a time zone data upgrade using the DBMS_DST package. Starting with Oracle Database 21c, you can perform such an upgrade while the database is in normal mode by setting the value of TIMEZONE_VERSION_UPGRADE_ONLINE to TRUE.
And this sounds even more promising now to me.
The provided Time Zone Upgrade scripts
After I applied the most recent DST V.42 RDBMS Patch 35220732 to my Oracle Database 21c home online, I could start with this new parameter.
At first, I wanted to see whether the parameter TIMEZONE_VERSION_UPGRADE_ONLINE has any effect when I use the check and apply scripts.
SQL> start ?/rdbms/admin/utltz_upg_check.sql Session altered. 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 21.0.0.0 . INFO: This database is a Multitenant database. INFO: Current container is CDB$ROOT . INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database INFO: will NOT update the RDBMS DST version of PDB databases in this CDB. WARNING: There are 1 open PDBs . WARNING: They will be closed when running utltz_upg_apply.sql . INFO: Database RDBMS DST version is DSTv35 . 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 DSTv42 . 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 utltz_upg_apply.sql to do the actual RDBMS DST update. INFO: Note that the utltz_upg_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt. Session altered.
Here you recognize that the check scripts warns me about a potential restart. I take this serious of course. And I will monitor it carefully in my alert.log in parallel when I kickoff the apply script.
SQL> start ?/rdbms/admin/utltz_upg_apply.sql Session altered. INFO: If an ERROR occurs, the script will EXIT SQL*Plus. INFO: The database RDBMS DST version will be updated to DSTv42 . INFO: This database is a Multitenant database. INFO: Current container is CDB$ROOT . INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database INFO: will NOT update the RDBMS DST version of PDB databases in this CDB. WARNING: There are 1 open PDBs . WARNING: They will be closed when CDB$ROOT is restarted 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 1845489680 bytes Fixed Size 9687056 bytes Variable Size 570425344 bytes Database Buffers 1258291200 bytes Redo Buffers 7086080 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 ... DST upgrade window has been successfully started. ERROR: Current DST_UPGRADE_STATE is NEEDRESTAR ! ERROR: DST_UPGRADE_STATE in DATABASE_PROPERTIES need to be UPGRADE ERROR: after a DBMS_DST.BEGIN_UPGRADE. ERROR: See note 1509653.1 for 12c . DECLARE * ERROR at line 1: ORA-20235: Stopping script - see previous message ... ORA-06512: at line 59 Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.10.0.0.0
Well, you realize: There was no need to monitor anything in the alert.log. The script’s output tells me that it forcefully restarted my database. And even worse, it failed.
This seems to be the problem:
ERROR: Current DST_UPGRADE_STATE is NEEDRESTAR !
So a restart did not happen. Read below for the explanation.
Learning Lessons so far
Learning lesson no.1:
The provided scripts are not able to deal with the advertised zero downtime mode. Don’t use use the scripts in this case but instead the DBMS_DST package directly. This is quite a disappointment since the reason for the scripts is simply to make your life easier, handle corner cases and prevent you from missing anything.
Learning lesson no.2:
Don’t use the new parameter TIMEZONE_VERSION_UPGRADE_ONLINE since it will break the utltz_upg_apply.sql script’s execution when the parameter it set to TRUE. I verified it with the parameter set to FALSE – and all went fine as expected. I would have hoped that at least the scripts would check this – they easily could stop execution. But no such check has been implemented yet.
Testing the new Zero Downtime Upgrade mode
So back to reality. Now I need to deal with DBMS_DST, something I completely skipped for years since we have the scripts around. The documentation lists this interesting section:
Upgrading the Time Zone Data Using the DBMS_DST Package
This method provides the most control over the individual steps of the time zone data upgrade. Starting with Oracle Database 21c, you have the option of performing this method while the database is online or offline. The online version of this method requires one restart of the database at your convenience and requires minimal locks on tables that need to be upgraded. It allows applications to query all time zone data and to insert and modify time zone data for all tables that can be migrated online. The offline version of this method, which was also available in previous releases, requires the database to be in UPGRADE mode during part of the procedure and is more restrictive about when applications can insert and modify time zone data.
But now I’m puzzled. At first, I see that there is an ONLINE and an OFFLINE option. But for ONLINE it says:
The online version of this method requires one restart of the database at your convenience and requires minimal locks on tables that need to be upgraded.
I leave it to you to interpret this sentence. And I rather try it out following exactly the documentation.
Prepare Window
According to the documentation, I need to start with the Prepare Window.
I decide to skip the creation of an explicit error table since there is no proposed code in the documentation for it. Instead, I will go with the standard, sys.dst$error_table.Then I purge the scheduler log as documented:
exec dbms_scheduler.purge_log;
Then the procedure asks me to purge histogram history and change the retention to turn it back afterwards. I blindly follow this but I am not sure whether these actions are appropriate in every PROD environment.
exec dbms_stats.alter_stats_history_retention(0); exec DBMS_STATS.PURGE_STATS(systimestamp);
If you do this, don’t forget to change it back afterwards to the retention from before.
Next step is to initiate the prepare window:
exec DBMS_DST.BEGIN_PREPARE(42);
And then you can query the affected tables:
exec DBMS_DST.FIND_AFFECTED_TABLES; column table_owner format a24 column table_name format a24 column column_name format a32 select table_owner, table_name, column_name, row_count, error_count from sys.dst$affected_tables order by 1,2,3;
There are no affected tables in my case. But there is one important thing to mention at this point: I do this only for the CDB$ROOT, not for any of the PDBs neither for PDB$SEED at this point.
Finally, I end the prepare window:
exec DBMS_DST.END_PREPARE;
Upgrade Window
Now I can actually change the DST version of my database (container). I am not interested in the OFFLINE method since this is handled with the scripts easily. I am solely interested in the ONLINE version to figure out the differences to the existing method.
It starts with enabling the TIMEZONE_VERSION_UPGRADE_ONLINE parameter. As you may note, the parameter is classified as PDB_MODIFIABLE.
ALTER SYSTEM SET TIMEZONE_VERSION_UPGRADE_ONLINE = true;
This call will now adjust the DST version of my data dictionary:
exec DBMS_DST.BEGIN_UPGRADE(42);
Of course, I immediately had to check whether this is visible in the dictionary already:
select VALUE$, CON_ID from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by CON_ID; VALUE$ CON_ID -------- ---------- 42 1 35 2 35 3
As assumed already, since this is a “in this active container only” action, this will need to be repeated in other containers separately. The documentation states an important caveat for this phase:
While the BEGIN_UPGRADE procedure is operating, you cannot add tables containing TSTZ columns to the database, nor can you add TSTZ columns to existing tables. TSTZ columns are columns defined on TIMESTAMP WITH TIME ZONE data types or object types containing attributes of TIMESTAMP WITH TIME ZONE data types.
And it gets more interesting:
If you are performing the online version of this procedure, then allow the database to continue running until you reach a convenient time to perform a reboot. During this time, the database is still operating with the old time zone version and the TSTZ data has not yet been updated. You are allowed to add tables that contain TSTZ columns to the database and you can add TSTZ columns to existing tables. When it is a convenient time to reboot the database, shut down the database and then restart it in normal mode. In Oracle RAC, you must shut down all instances first before restarting them.
The documentation asks me to check the time zone file:
SELECT * FROM V$TIMEZONE_FILE; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_35.dat 35 0
Before continuing, you must restart the entire database.
Otherwise, you will receive this error:
ORA-56930: database must be restarted before performing upgrade of user tables ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DST", line 899 ORA-06512: at "SYS.DBMS_DST", line 1218 ORA-06512: at line 4
So I will restart my database:
startup force
And then I actually can upgrade my user data tables:
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;
/
Now, at this stage you need to be careful. I had no affected tables above. So in my environment this procedure completed in less than a minute. But please take into account:
- You want to test this in a matching test environment – keep an eye on the runtime
- This procedure will hold exclusive locks – make sure your application is able to handle this correctly
- You need to do this for all your containers including PDB$SEED
You see that I added the DBMS_DST.END_UPGRADE call in the procedure above already.
And this is the output of my query:
select VALUE$, CON_ID from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by CON_ID; VALUE$ CON_ID -------- ---------- 42 1 35 2 35 3
Just to proof what I queried above:
CDB$ROOT> SELECT * FROM V$TIMEZONE_FILE; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_42.dat 42 0 CDB$ROOT> alter session set container=pdb3; Session altered. PDB3> SELECT * FROM V$TIMEZONE_FILE; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_35.dat 35 0
Summary
I need to take a deep breath at first.
What’s great with the new procedure is that I can change my database’s DST version for the dictionary without the previously required STARTUP UPGRADE. This is a huge improvement.
But it comes at a price. While you operate the database in this flux mode, there are internal conversions happening according to the documentation. I can’t foresee the performance impact and I did not investigate any of them. Even more important, the packaged utltz_upg* scripts don’t work with the new mode. That is a big hit when you consider that 21c and 23c are always and only CDB architecture database. You could have hundreds of PDBs within one single CDB. For instance, when AutoUpgrade takes care on your database upgrades and patching, it does the right thing for the time zone changes by utilizing the packaged scripts. That is much easier than scripting all this for all your containers by yourself.
And finally, this is clearly not a Zero Downtime Upgrade of Timezone Data in Oracle 21c and 23c, at least not in my understanding. You can change the dictionary DST version upfront while the database remains online. But you need to restart the entire database, regardless of whether you have a RAC or a non-RAC environment at a given point. In addition, you still have the burden with the exclusive locks when you change your actual user data.
It’s an improvement for sure – but it’s not at a point yet where you’d have a Timezone Data upgrade with Zero Downtime and no impact on your user data.
Addition on June 16, 2023
After having a longer discussion with the development team, I learned that in case you choose the track for the online mode your tables requiring time zone adjustment will be now rebuilt with ALTER TABLE MOVE …ONLINE. But currently as of now, the code has no barriers for very large tables. No algorithm tells it to rather use the “old” method with many exclusive locks instead of the “online” method with just an exclusive lock for the final flip. Especially in cases where you have very large tables, partitioned or not, this may be not the best solution at the moment.
Further Links and Information
- Since Oracle 19.18.0 we ship all available DST patches by default
- Oracle Database 21c: Zero Downtime Upgrade of Time Zone Data feature
- TIMEZONE_VERSION_UPGRADE_ONLINE
- MOS Note: 412160.1 – Primary Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
–Mike
Hi, Mike,
Thanks for the honest and open explanation !
Clarity is something we really need.
Well, thanks 🙂
Mike