Time Zone – DST

ORA-39405 with impdp when you have mixed time zone versions

Today, Gisela, a former team mate from CSS/ACS Support contacted me regarding an issue seen at a customer. She reported an ORA-39405 with impdp when you have mixed time zone versions. But how can this happen? And what are the potential workarounds? Since some of our hosted environments got into the same situation already, this may be worth a blog post and some explanation.

Why does this happen?

You never cared about DST (Daylight Savings Time) files and versions of your database? This isn’t unusual, and in many environments actually not a problem …

Continue reading...

Zero Downtime Upgrade of Timezone Data in Oracle 21c and 23c?

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.

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 …

Continue reading...

Choose your desired time zone version (DST) upgrade

This morning I received a very reasonable question from a customer who has a time zone mismatch between source and target database. And he wants to transport into a PDB on Exadata. Even though, this sounds trivial at first sight, unfortunately it isn’t. But a bug fix done recently for a German automotive customer is very handy here. So let me show you how you can Choose your desired time zone version (DST) upgrade.

Choose your desired time zone version (DST) upgrade

Photo by Vince Veras on Unsplash

Where does the problem start?

In this particular – and not unusual case – the customer wants to migrate …

Continue reading...

RUs contain now all available DST patches

At first, Happy New Year everybody. There was silence on the blog for some weeks but I took an extended longer time off. Well, and once you return, there are some emails in your inbox which need some work. You’ll see several new blog posts getting published in the coming days. Back to normal, yesterday night RU 19.18.0 among other patch bundles has been released. And there’s a huge fantastic surprise in it: RUs contain now all available DST patches.


What’s the story?

There are some good news. We discussed this internally …

Continue reading...

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?

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 …

Continue reading...

How to patch all PDBs with the a new time zone file?

Yesterday I wrote about how to adjust the time zone setting in the PDB$SEED as by default the time zone scripts won’t touch the PDB$SEED when you execute them. And in addition, MOS Note:1509653.1 tells you, that the PDB$SEED can’t be adjusted. But this leads to a weird mix of time zone settings across a Multitenant deployment. Which I’d guess is not desired. Following a tweet reply by Marco Mischke I realized: I explained how to patch the PDB$SEED – but I didn’t explain how to patch all PDBs with the a new time zone file?

How to patch all PDBs with the a new time zone file?

Photo by Laureen

Continue reading...

Does the PDB$SEED get “time zone” patched or not?

Today I received a question from a very experienced Oracle ACS engineer whether the PDB$SEED get time zone patched or not when a time zone patch gets applied. I’d say spontaneously “Of course, it does” but after thinking for a few seconds I started having doubts. Question is: Does the PDB$SEED get “time zone” patched or not?

Does the PDB$SEED get "time zone" patched or not?

Photo by Luis Cortes on Unsplash

Time zone scripts are in ?/rdbms/admin since Oracle 18c

First of all, since Oracle Database 18c the scripts to adjust time zone settings of the database are in ?/rdbms/admin. If you did our Hands-On Lab already …

Continue reading...

Create a database with NON-DEFAULT Time Zone

One requirement of Transportable Tablespaces (and of course Full Transportable Export/Import as well) is to have identical database character sets AND identical time zone settings.


Source database has a lower time zone setting than the default target database in the destination home.

Lets assume you’d like to migrate an Oracle off IBM AIX and migrate it into Oracle on an Exadata meaning Oracle Linux 6. The source time zone version if it has never been upgraded would be TZ V14 – whereas the new Oracle database you’d create would get TZ V18 by default.

Time Zone Setting Oracle Database

Solution 1

Continue reading...

Transportable Tablespaces and READ ONLY in Oracle Database 12c

We recently worked with a customer who noticed that they were not able to use transportable tablespaces to connect the same tablespace data files to two databases at the same time, even after setting the tablespaces READ ONLY in SQL*Plus. This is new behavior in 12c, and many customers are not yet aware of this change. Here are the details of what changed, why, and how you might want to deal with it if the changes affect your environment.

What Changed?

Starting in 12.1, data pump sets tablespaces read write during the import phase of a transportable tablespace …

Continue reading...

Full Transportable Export/Import – Things to Know

This blog post is an addition to:

Seth Miller commented the pitfall of having a serious issue during the Data Pump run, Data Pump exiting and not finishing, and you’ll have to do the entire backup/restore/incremental-roll-foward thing again. Without any doubt, this is no fun at all.

So let me point out a few things to take into consideration – and some of them are not obvious I guess.

Do you need to backup/restore/incremental-roll-forward again?

When you let do Data Pump all the manual …

Continue reading...

Let’s do the Time Warp again!

Once you start reading about Daylight Saving Time changes in MyOracleSupport you’ll find still a lot of notes explaining this and that and back and forth. But sometimes there seems to be a bit too much information – and lacking clear instructions. Once a customer called that the “Time Zone Spaghetti” after reading MOS notes about DST for several hours ending up with the note where he has begun to read before still not clear what to do now 😉

I’m using usually the scripts from MOS Note:977512.1 as you’ll just have to exchange the DST version you are upgrading

Continue reading...

New Time Zone Patch DST V18 is available

Sorry for not updating the blog more often at the moment – but more updates will come soon as I play around with Oracle Restart and single instance databases in ASM with Oracle 11.2.

Just on the side there’s a new time zone patch to DST V18 available since May 2012. You can download it via PATCH download from MOS with the patch number: 13417321

What do you think? Will Lufthansa operate a faster jet the other night? Will the jet stream be more powerful? Or a better type of fuel? Or is it just the travel portal which hasn’t

Continue reading...

Are you still on Daylight Savings Time?

Central Europe and many other countries switched back from Daylight Savings Time to regular time on this Sunday’s night. But others don’t. Thanks to a “wise” decision in 2006 the US will switch back to regular time in one week. And another “wise” man has decided that there’s no need for Russia to switch back to regular time (see the last paragraph on “DST” in this linked Wikipedia article) – so Russia will stay forever on DST being one hour ahead 😉 Samoa will simply jump from the end of the time zone puzzle now heading it

Continue reading...

Time Zone Upgrade might be slow

One of the best things with my role in Oracle’s Upgrade Development is:
I work with customers – and I learn a lot from customers.

This is an issue Richie and his team from Accenture in Ireland brought to my attention. I try to support them currently in an EBS database upgrade. And he did highlight a potential issue with the post upgrade time zone change to me:

Time ZonesBug 10209691 – slow performance on ALL_TSTZ_TAB_COLS
Workaround: alter session set “_with_subquery”=materialize;

All together the upgrade of an Oracle EBS 11i database takes approx 30 minutes. And we still do recommend …

Continue reading...