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 upgrading the Time Zone Data post upgrade as first of all we use the data type TIMESTAMP WITH TIMEZONE internally in the dictionary and – more important – the scheduler relies on correct settings. Otherwise it could happen that jobs don’t get executed.
Of course Roy and me added this information to the slides already 😉
Hi Mike,
I am busy testing to upgrade 2 11.2.0.4 databases to 19C. At this point it is Single Instance to single instance, but we plan to move to CDB once our production databases is 19C.
The timezone of the 11g db’s is 14. We don’t use any TIMESTAMP WITH TIMEZONE data in our databases and am in South Africa – no daylight saving time or different timezones.
What issues can I expect IF I upgrade the timezone and is it desirable to update the timezones?
No issues.
You just will see the PDB_PLUG_IN_VIOLATION about the TZ mismatch between source and target.
Be aware that when you automate it with AutoUpgrade (the upgrade/plugin/conversion), it will upgrade DST TZ by default unless you explicitly set:
upg1.timezone_upg=no
But if you have no such data in your DB, then the TZ upgrade is an additional restart and 30 seconds extra. So, why are you planning to skip it?
Cheers,
Mike
Thanks for the clarification. Will definitely upgrade tz
Thanks, Faan!
Cheers,
Mike