Upgrade: What if your time zone in source is newer than in target?

Sometimes there is just a simple question at the beginning. Like Daniel and I discussed this morning. Does AutoUpgrade check if the time zone file in source is higher than in target? While we discussed this, I downloaded the patches already to try it out. And in addition, I could double-check whether the time zone patches for 19c are now RU-independent finally. But the question remains for Upgrade: What if your time zone in source is newer than in target?

Photo by Srikanta H. U on Unsplash

My intention wasn’t initially to write a longer blog post. But read below …

Patching my 11.2.0.4 database with DST V.34

It all starts with the right time zone patch – and this strange sequence of steps I have to do at first.

Time Zone Spaghetti

1. Find the most recent time zone patch available:
   MOS Note: 412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
   Gosh, this note really needs a cleanup … At least after scrolling back and forth for 3 minutes I find what I’m looking for:
   .
   
   .
2. Download the patch – in my case for the 11.2.0.4 environment:
   

   Patch Download for DST V.34 from MyOracle Support

   .

3. The patch has a readme associated with it. But there is also a note linked from MOS Note: 412160.1 on how to apply the patch:
   MOS Note: 2602555.1 – Applying the DSTv34 update for the Oracle Database
   .
   
   .
4. To update the time zone settings in my 11.2.0.4 database I then I need to download these scripts from MOS 1585343.1 – Scripts to update the RDBMS DST (timezone) version in an 11gR2 or 12c database. Just on the side, from Oracle 18c on these scripts are included in the ?/rdbms/admin – but not in 11.2.0.4.
   .

If I’d ever asked myself why customers don’t apply time zone patches so often, you’ll find the answer above. Or how did a customer from Poland call it a while ago: “Time Zone Spaghetti“. He told me that he started reading about DST on MOS, and after two hours he ended up with the note he started reading initially – and still didn’t know what to do.

And at this point you know why my entry sentence was meant honestly: Such a simple question …

Patch, patch, patch …

Yes, well … now I hope I have everything. But I need to apply the patch. As I do this quite often, I know what to do:

1. Unpack the patch into a directory
2. cd to this directory
3. opatch conflict check:
   opatch prereq CheckConflictAgainstOHWithDetail -ph ./
4. opatch apply

And then connect to my database and run the two scripts:

1. start upg_tzv_check.sql

2. start upg_tzv_apply.sql

The latter script will restart the database twice. So despite the fact that you can apply the time zone patch binary while the database is up, you will encounter downtime when you adjust the time zone. And the downtime depends on the amount of data requiring change as MOS 1585343.1 explains in detail:

For the second part of upg_tzv_apply.sql after the “INFO: Upgrading all non-SYS TSTZ data.” message -> the DBMS_DST.UPGRADE_DATABASE used by upg_tzv_apply.sql will take exclusive locks on the non-SYS tables when they are actually upgraded, so this might provoke issues (deadlocks have been observed) and we strongly suggest to NOT start any applications who use the tables processed until the complete DST update is done. Other applications may already be restarted if needed.

Maybe this is another reason why time zone patches don’t get applied frequently.

Final check whether my database’s time zone got upgraded:

column property_name format a32
column value format a14

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

Wow! I’m done with the preparation for my test.

Test Setup with higher DST in source

After all these easy steps, I’m fully warmed up now. I can concentrate on the initial question.

My 11.2.0.4 database has now DST V.34 – and let me check my 19.6.0 database environment I’d like to upgrade to. I connect to an already existing 19c database:

PROPERTY_NAME			 VALUE
-------------------------------- --------------
DST_PRIMARY_TT_VERSION		 32
DST_SECONDARY_TT_VERSION	 0
DST_UPGRADE_STATE		 NONE

SELECT version FROM v$timezone_file;

   VERSION
----------
	32

This is the situation I wanted to check for. The target home has a lower time zone version installed than the source. How will the upgrade react to it?

Just in case you are asking now how to verify this when you have NO existing database? Go to /u01/app/oracle/product/19/oracore/zoneinfo, and check for the highest number in timezone_n.dat. In my case this is timezone_32.dat whereas in my 11.2.0.4 home it is already timezone_34.dat.

AutoUpgrade

I invoke an analyze run.

java -jar $OH19/rdbms/admin/autoupgrade.jar -config UP19.cfg -mode analyze
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 for FTEX

This looks good at first. But let us check the ftex_preupgrade.html file in ~/upg_logs/FTEX/100/prechecks:

AutoUpgrade Prechecks HTML File gives you a good overview on checks and results

Hm? I’m skeptical because of the FixUp Available: Yes. But my config file says upg1.timezone_upg=no. Let me change this to “yes” and repeat the analyze run. But this doesn’t change anything.

The message is clear. I would need to patch my target home at first.

But what happens if I ignore this?

$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config UP19.cfg -mode deploy
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> 
-------------------------------------------------
Errors in database [FTEX]
Stage     [DBUPGRADE]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-1400
UPGRADE FAILED [FTEX]
Cause: This indicates that the database upgrade failed with errors.
For further details, see the log file located at /home/oracle/upg_logs/FTEX/102/autoupgrade_20200409_user.log]

-------------------------------------------------
Logs: [/home/oracle/upg_logs/FTEX/102/autoupgrade_20200409_user.log]
-------------------------------------------------

Very simple – the upgrade fails shortly after invocation. It has to. The log tells me what has happened:

2020-04-09 11:20:07.245 ERROR
DATABASE NAME: FTEX
         CAUSE: ERROR at Line 892 in [/home/oracle/upg_logs/FTEX/102/dbupgrade/catupgrd20200409111829ftex0.log]
        REASON: ORA-01722: invalid number
        ACTION: [MANUAL]
        DETAILS: 01722, 00000, "invalid number"
// *Cause: The specified number was invalid.
// *Action: Specify a valid number.

Magic ORA-1722 – invalid number. This means one of the initial upgrade checks failed when the upgrade got kicked off. And it prevents your database from being damaged. Nothing to restore from your side here. You just need to shut the database down in the 19c environment, and start it up again in the source environment.

preupgrade.jar and dbupgrade

If you’d gone the classic approach of using preupgrade.jar and dbupgrade, you would have gotten the same result

$ java -jar $OH19/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-04-09T11:33:33

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  FTEX
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
     DB Patch Level:  PSU 11.2.0.4.200114
         Compatible:  11.2.0.4.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  34
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID         

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  ...

  2.  Patch the new 19 $ORACLE_HOME/oracore/zoneinfo/ with the version 34 time
      zone data file from the 11.2.0.4.0 $ORACLE_HOME/oracore/zoneinfo/.
      
      The database is using a time zone file version that is newer than the
      version shipped with the 19 release.
      
      The time zone file version used in your database must exist in the new
      Oracle home before upgrading the database.
 ...

Preupgrade complete: 2020-04-09T11:33:33

Well, or not exactly. We will discuss in the team whether this should be marked as a REQUIRED ACTION instead.

The dbupgrade run will error out as well with some longer debug output. Of course, in this case as well, I need to SHUTDOWN my database, and STARTUP it in the source environment. No restore needed.

DBUA

Well, I typically don’t use the DBUA for 1001 reasons. But I gave it a chance, too. And of course it had to fail as well. This is expected as the underlying checks are general checks.

It gave me a warning before upgrade:

DBUA 19c: Time Zone Warning

And failed with ORA-1722 when I started the upgrade process:

DBUA 19c: Error with ORA-1722

When you click “OK”, what can you do then?

I hit the RETRY button (greyed out in the screenshot above but visible and clickable when you confirm the error box with “OK”). Now the ORA-1722 wasn’t visible anymore – but instead a SEVERE error got displayed:

DBUA 19c: Retry is not a good idea – and makes no sense

There is the Progress Log button – let me see what it says:

DBUA 19c: Where is the correct log?

Well, it looks as if should check the directory: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-04-09_12-38-56PMFTEX. And being honest, I gave up after a few minutes. Not even does the DBUA tell me which log file contains the error. But at least it displayed the correct error during the first run.

What really annoys me, and this is one of the 1001 reasons I mentioned above, is what happens when I abort the upgrade with DBUA:

DBUA 19c: Misleading famous last words … DON’T RESTORE!

This is at first incorrect, at second misleading and at third can cause you a lot of extra work. If I hadn’t called or mailed by customers several times already I would ignore that. But those got told by Support to restore their database at such point were not happy to learn later that this wasn’t needed.

The database is in good shape, the DBUA didn’t “destroy” it. You simply have to go back to your source environment and start the database up. That’s it.

Finally …

This is the error condition in the standard catupgrd0.log, the main worker’s log file:

12:41:29 DOC>#######################################################################
12:41:29 DOC>#######################################################################
12:41:29 DOC>   The following error is generated if (1) the old release uses a time
12:41:29 DOC>   zone file version newer than the one shipped with the new oracle
12:41:29 DOC>   release and (2) the new oracle home has not been patched yet:
12:41:29 DOC>
12:41:29 DOC>      SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
12:41:29 DOC>                       *
12:41:29 DOC>      ERROR at line 1:
12:41:29 DOC>      ORA-01722: invalid number
12:41:29 DOC>
12:41:29 DOC>     o Action:
12:41:29 DOC>       Shutdown database ("alter system checkpoint" and then "shutdown abort").
12:41:29 DOC>       Patch new ORACLE_HOME to the same time zone file version as used
12:41:29 DOC>       in the old ORACLE_HOME.
12:41:29 DOC>
12:41:29 DOC>#######################################################################
12:41:29 DOC>#######################################################################
12:41:29 DOC>#
12:41:29 SQL>
12:41:29 SQL> Rem   SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
12:41:29 SQL>
12:41:29 SQL> Rem Check if time zone file version used by the database exists in new home
12:41:29 SQL> SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
12:41:29   2     FROM sys.props$
12:41:29   3     WHERE
12:41:29   4       (
12:41:29   5        (name = 'DST_PRIMARY_TT_VERSION' AND TO_NUMBER(value$) > &C_LTZ_CONTENT_VER)
12:41:29   6        AND
12:41:29   7        (0 = (select count(*) from v$timezone_file))
12:41:29   8       );
old   5:       (name = 'DST_PRIMARY_TT_VERSION' AND TO_NUMBER(value$) > &C_LTZ_CONTENT_VER)
new   5:       (name = 'DST_PRIMARY_TT_VERSION' AND TO_NUMBER(value$) > 32)
SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
                 *
ERROR at line 1:
ORA-01722: invalid number


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Summary

Well, such a simple question … but it took me a while to verify all potential scenarios. And of course to patch my source with the DST V.34 patch. No idea why the time zone patch selection isn’t handled by an assistant note. It almost begs for such a note asking you for your database version you’d like to patch, then give you a choice to say either “Newest” or choose from an optional list, then give you the link to the patch to download and the scripts to apply it if necessary. Could be very simple …

Of course, especially AutoUpgrade may need a few additional tweaks to improve the process. We will discuss this internally and see what we can do.

Solution

If you wonder why the solution is coming after the summary: This is pure coincidence. When I wanted to shutdown my environment, I thought to myself: It isn’t finished yet. I will need to apply the DST V.34 patch now to my target environment and see if all works fine. And it will be a good chance to check whether time zone patches in 19c now have been created patch-level-independently, or as initially falsely depending on a specific patch level (i.e., various patches for 19.4.0, 19.5.0, 19.6.0 and so on).

I downloaded the patch marked as 19.0.0.0.0 from the same patch number from MOS as the one for 11.2.0.4 above.

Then I applied it without downtime to my target 19.6.0. home. And this worked. So good news here. The time zone patches are again patch-level independent.

Finally, I kick off AutoUpgrade again. But before this can succeed, I need to clear the recovery data (please see my troubleshooting blog post from the other day) as otherwise AutoUpgrade would error out again.

$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config UP19.cfg -mode deploy -clear_recovery_data
The recovery data was removed, the AutoUpgrade will start from the beginning
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
| 103|   FTEX|DBUPGRADE|EXECUTING|RUNNING|20/04/09 13:47|     N/A|13:50:55|0%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
Total jobs 1

And soon after …

upg> Job 103 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 103 for FTEX

Yes! Strike!

More Information and Links

• MOS Note: 412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
• MOS Note: 2602555.1 – Applying the DSTv34 update for the Oracle Database
• MOS 1585343.1 – Scripts to update the RDBMS DST (timezone) version in an 11gR2 or 12c database

• Create a database with NON-DEFAULT Time Zone
• Does PDB$SEED get time zone patched?
• How to patch all my PDBs with a new time zone file?
• The DST patch-dependency problem
• Data Pump: The Time Zone Pitfalls
• Database Migration from non-CDB to PDB: The Time Zone Pitfall
• Troubleshooting, restoring and restarting AutoUpgrade

–Mike