Time Zone

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.

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 …

Choose your desired time zone version (DST) upgrade

Posted on May 24, 2023 by Mike.Dietrich Time Zone - DST Transportable 4

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 …

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

Posted on April 9, 2020 by Mike.Dietrich Time Zone - DST 15

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 …

Database Migration from non-CDB to PDB – Migration with Data Pump

Posted on August 14, 2019 by Mike.Dietrich Data Pump Single-/Multitenant 10

You may have realized that there are a few techniques missing describing how to do a Database Migration from non-CDB to PDB – Migration with Data Pump is one of them. I will explain the most simple approach of going to Single- or Multitenant. It isn’t the coolest – and it isn’t very fast as soon as your database has a significant size. But it is not complex. And it allows you to move even from very old versions directly into an Oracle 19c PDB – regardless of patch levels or source and destination platform.

Database Migration from non-CDB to PDB – Migration with Data Pump

High Level Overview

Endianness change

Database Migration from non-CDB to PDB – Various Pitfalls

Posted on August 2, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 22

There are several pitfalls when you plugin a non-CDB into a CDB environment. I’d like to highlight some of them – and show you potential workarounds as well. This is part of a series of blog posts to make your migration from non-CDB to PDB a bit smoother.

Photo by timJ on Unsplash

Database Migration from non-CDB to PDB – Various Pitfalls

In all the previous blog posts of this series I tried to explain specific pitfalls, and how you can workaround them. This article is meant to collect the “leftovers”, the minor issues and pitfalls which you may not …

Database Migration from non-CDB to PDB – The Patch Level Pitfall

Posted on August 1, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

Photo by Piron Guillaume on Unsplash

Database Migration from non-CDB to PDB – The Patch Level Pitfall

When you consolidate on a larger scale, it is very likely that you have different patch levels in your database environments. But when you attempt to plugin a non-CDB into a CDB, you may see …

Database Migration from non-CDB to PDB – The Component Pitfall

Posted on July 31, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 6

Photo by Brett Jordan on Unsplash

The Component Pitfall

With component we mean the database component which you can find in DBA_REGISTRY – or CDB_REGISTRY. When Multitenant became available over 5 years ago, a decision had been made to make all options/components mandatory in a container database. This decision had to …

Database Migration from non-CDB to PDB – The Time Zone Pitfall

Posted on July 30, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

Photo by Allef Vinicius on Unsplash

Database Migration from non-CDB to PDB – The Time Zone Pitfall

Interestingly, there is no issue with different time zone settings within a single CDB. Your CDB$ROOT can be on DST V.32 whereas a PDB you plugin can be already on DST V.33. But only …

Database Migration from non-CDB to PDB – The COMPATIBLE pitfall

Posted on July 29, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

Database Migration from non-CDB to PDB - The COMPATIBLE pitfall

Database Migration from non-CDB to PDB – The COMPATIBLE pitfall

When you migrate your non-CDB to PDB, in most cases the COMPATIBLE setting of the non-CDB will be lower than the setting of the receiving CDB. But still in this case you may see warnings. I will explain how to deal with …

Database Migration from non-CDB to PDB – Typical Plugin Issues and Workarounds

Posted on July 29, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

In the previous blog posts I showed different approaches on how to migrate your database on a same Endianness platform into Multitenant. Whether you prefer to upgrade first or plugin first is up to you. I recommend upgrading first as this allows you a seamless fallback. But regardless of which approach you prefer, you may take care on potential pitfalls. Hence, this blog post is about Database Migration from non-CDB to PDB – Typical Plugin Issues and Workarounds. It may not be complete when I publish it and I may extend it later on. Let me know if you have …

Data Pump: The Time Zone Pitfalls

Posted on May 14, 2019 by Mike.Dietrich Flaws and Pitfalls 13

Photo by Luis Cortes on Unsplash

Last week a very experienced colleague called me. He had issues with an export dump taken from an Oracle 20c database importing into 19c. It failed. So this blog post is about Data Pump: The Time Zone Pitfalls.

The Case

Usually you will see this issue only when you try to export from a higher version, and then attempt to import into a lower one. But the same thing can happen when you patched your databases partially with a newer time zone patch regardless of the version.

My colleague saw this error:

impdp system/welcome1@//localhost:1521/MYDB

DST Time Zone Patch V.32 and V.33 for Oracle 18.5.0

Posted on February 1, 2019 by Mike.Dietrich Uncategorized 7

If you’d ever attended one of Roy’s or my workshops, you know that we talk about time zone adjustments after the database upgrade. We explain why this is important. But as everything, it depends if you really need to apply and adjust. In our workshops we recommend one single note for time zone adjustments: MOS Note:412160.1 . This note has been updated and republished. It contains DST V.32 and DST V.33 links and references. Amit Grover from AWS contacted me and told me about a potential dependency of DST patches to the Oracle Update such as RU 18.4.0 or 18.5.0.…

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

Posted on December 17, 2018 by Mike.Dietrich Single-/Multitenant Time Zone - DST 1

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 …

Oracle Database 18.3.0 Upgrade on premises

Posted on July 26, 2018 by Mike.Dietrich Upgrade / Migration 2

As Oracle Database 18c is now available on Linux on premises, I blogged about the installation already. And the next logical step is the Oracle Database 18.3.0 Upgrade on premises. In this case I will show an upgrade on the command line from Oracle 11.2.0.4 to Oracle 18.3.0. A DBUA upgrade will follow in a separate blog post later.

Oracle Database 18.3.0 Upgrade on premises

Before you can start with the upgrade, you may download the software first, and then install it:

If …

Create a database with NON-DEFAULT Time Zone

Posted on December 8, 2016 by Mike.Dietrich DBCA Time Zone - DST Transportable Upgrade / Migration 0

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.

Problem

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 11.2.0.4 off IBM AIX and migrate it into Oracle 12.1.0.2 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 12.1.0.2 database you’d create would get TZ V18 by default.

Time Zone Setting Oracle Database

Solution 1

Transportable Tablespaces and READ ONLY in Oracle Database 12c

Posted on December 2, 2016 by Roy.Swonger Data Pump Time Zone - DST Transportable 1

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 …

Full Transportable Export/Import – Things to Know

Posted on July 4, 2016 by Mike.Dietrich TDE - Transparent Database Encryption Time Zone - DST Transportable 8

This blog post is an addition to:

Full Transportable Export/Import – Migrating an 11.2.0.4 database to Oracle Database 12c- into the Oracle Cloud

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 …

Let’s do the Time Warp again!

Posted on June 20, 2012 by Mike.Dietrich Time Zone - DST 0

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 …

New Time Zone Patch DST V18 is available

Posted on June 19, 2012 by Mike.Dietrich Time Zone - DST 0

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 …

Are you still on Daylight Savings Time?

Posted on October 31, 2011 by Mike.Dietrich Time Zone - DST 0

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…

Time Zone Upgrade might be slow

Posted on May 23, 2011 by Mike.Dietrich Time Zone - DST 0

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:

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

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

Time Zone

Time Zone Upgrade?

Where does the problem start?

High Level Overview

Database Migration from non-CDB to PDB – Various Pitfalls

Database Migration from non-CDB to PDB – The Patch Level Pitfall

The Component Pitfall

Database Migration from non-CDB to PDB – The Time Zone Pitfall

Database Migration from non-CDB to PDB – The COMPATIBLE pitfall

The Case

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

Oracle Database 18.3.0 Upgrade on premises

Problem

Solution 1

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

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Follow me on Twitter

Recent Posts