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 …Continue reading...
Flaws and Pitfalls
I’m tempted to copy and paste my blog post about DBMS_OPTIM_BUNDLE from February 2019, and exchange only 22.214.171.124 with 19c. But in this case the root cause is different. So this blog post is meant for Oracle 19.3.0 to be precise – in case you miss
DBMS_OPTIM_BUNDLE … again … ?!?
Information about DBMS_OPTIM_BUNDLE
You will find a lot of information about
DBMS_OPTIM_BUNDLE, what it does, since which release it exists and much more in these blog posts:
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.
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… Continue reading...
First of all, this blog post is not new. I blogged about this SCN topic a while ago already. But some of you seem to operate still older databases for various reasons. And even if you think that you are safe, double check for any older databases in your environments. You MUST patch 126.96.36.199 and 188.8.131.52 and older databases before June 23, 2019. And just to be clear: June 23, 2019 is going to happen in less than 4 months.
Who is NOT affected?
If you use the following Oracle database releases …Continue reading...
Strange things happen sometimes. I got alerted by a customer before Christmas about package
DBMS_OPTIM_BUNDLE missing after applying a Release Update. I’ve had a conversation with Nigel Bayliss, the Optimizer PM – but Nigel hasn’t heard of such things either. We both investigated but couldn’t reproduce the issue. But after the end-of-the-year holidays I received similar messages from other customers. In case you miss
DBMS_OPTIM_BUNDLE in 12.2, then this blog post should help you.
What is DBMS_OPTIM_BUNDLE?
I blogged about this package in the past already several times:Continue reading...
I work with several customers at the moment on their Oracle 12.2/18c “go live” projects. And one of these customers encountered a strange issue when comparing Oracle 184.108.40.206 to Oracle 220.127.116.11/18.3.0: Direct INSERTs into HCC tables may be slower since Oracle 12.2. Actually in this particular case, significantly slower. Read below why this can happen and how the workarounds look like.
Hybrid Columnar Compression
There’s a lot of material to read and study about Hybrid Columnar Compression (HCC). With HCC we store the same column for a group of rows together. The data block does not store data in row-major …Continue reading...
This blog lives a lot from customer’s feedback and updates. And these days, Nicolas Jardot from dbi-services in Switzerland sent me an email asking: Where does the database link SYS_HUB come from? And if he can delete it or if it has any deeper purpose. Neither Google nor MOS reveal a lot of useful information
Where does the database link SYS_HUB come from?
I took my vanilla 18.3.0 database in our hands-on lab and check:
column owner format a5 column db_link format a12 column username format a12 column host format a12 select con_id, owner, db_link, username, host from cdb_db_links; CON_ID… Continue reading...
I really don’t want to turn this blog into an accumulation of issues and flaws. But as I explained many times before, the blog for me is also a way to dump information I likely will need the sooner or later again.
Recently I blogged about another RMAN issue in Oracle 18.104.22.168 with traces. This was fixed with the July 2018 RU for Oracle 22.214.171.124. But the issue below about which Piero Ferraz from Brazil alerted me (thanks!!!), happens in exactly this RU.
RMAN Backup Gives RMAN-06091: No Channel Allocated for Maintenance
This issue gets introduced with the July …Continue reading...
We had several discussions on the internal mailing lists the other day about cloning with Pluggable Databases in Oracle 18c (or 126.96.36.199). And I blogged about this topic a while ago but realized I may need to refresh this a bit.
My example from the old blog post still works fine. But I realized that I switched my database into read-only mode. Hence, I like to repeat it and check different options.
Cloning a 12.1 PDB into Oracle 18c
In the source CDB1 I create a fresh PDB first, then open it and create a cloning user with the necessary …Continue reading...
Thanks to my team mates, Cindy and Hector, who alerted me on this newly detected issue. When your database has been upgraded from release to release, it could happen that a public synonym
XMLCONCAT exists. And before you upgrade to Oracle 12.2.01 or Oracle 18c you must drop this public synonym to avoid upgrade errors.
Drop public synonym XMLCONCAT prior to upgrade
The public synonym XMLCONCAT in pre-188.8.131.52 Oracle databases pointed to a PL/SQL function of the same name. In 184.108.40.206, this PL/SQL function was changed to a C function.The public synonym was no longer needed nor pointing to a …Continue reading...
Again I’ll have to thank my colleague Roland Gräff from the German ACS Support team in Stuttgart for bringing this into our radar. Roland alerted me a week ago about an issue with exports in Oracle 220.127.116.11 only when you are on a certain patch level. I summarize the issue here under Data Pump 18.104.22.168 – Wrong Dump File Version – ORA-39142.
In the below blog post you will learn about the actual issue, where it happens and when, and of course how to workaround it.
When does it happen?
The issue I will describe below happens only with …Continue reading...
Oracle 22.214.171.124 seems to be the “tracing” release without further patch or parameter treatments. After posting about MMON unconditional traces in Oracle 126.96.36.199 a day ago, I received a couple of comments either on the blog, via LinkedIn or Twitter regarding other trace facilities in Oracle Database 188.8.131.52. One of them is the case that RMAN backup generates traces in Oracle 184.108.40.206.
And please, very important upfront:
Oracle Database 220.127.116.11 is a very stable and reliable release based on all the customer feedback Roy and I received so far. For instance, when we check for optimizer issues …
I love visiting customers onsite. Last week I visited die Mobiliar in Bern, Switzerland. I received a list of open issues to discuss – which is very good to prepare a visit. And when we all were sitting together there was this “Ah, one final thing”. They have an issue with traces the databases writes every few seconds. As a remedy the DBAs increased the backup interval to remove the traces as otherwise the system would potentially run out of inodes or space. All the traces had the same pattern. And I learned quickly: these are MMON unconditional traces in …Continue reading...
One of the reasons why I have this blog is simply to stay in touch with smart people using Oracle and telling me about issues I haven’t seen before. Thanks to Tyler Van Vierzen I learned that an upgrade to Oracle 18.104.22.168 may alter the default local temporary tablespace.
Upgrade to Oracle 22.214.171.124: Check your DEFAULT temporary tablespaces
Tyler did contact me on Twitter:
“Curious if you’ve seen this… Seeing new “local temp tablespace” set to SYSTEM for some users after 12.2 upgrade. But not all. No discernable pattern. Some null (so default to their temp tablespace), some set equal …Continue reading...
I did blog in the past weeks about Fallback Strategies with Flashback Database. But two of my reference customers came across an interesting issue when they tried to fallback: Oracle 12.2 underscores appear in the SPFILE – magically – and prevent the fallback using the existing SPFILE.
Oracle 12.2 underscores appear in SPFILE – be aware when you flashback
In one case it happened during a test, in the other case it happened during a live fallback after the Data Guard Broker has interfered with the upgrade causing real trouble. In both cases the SPFILE sits in ASM.
Both …Continue reading...
There is a fancy new command to unplug a PDB in Oracle Database 126.96.36.199:
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO 'pdb1.pdb';
The nice thing with this command differing in the file ending of ‘
pdb‘ instead of ‘
xml‘ as you used it in Oracle 12.1 (and the ‘
xml‘ option is still available of course): Instead of just creating an
xml description file it
zips everything together into a PDB archive.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> create pluggable… Continue reading...
We discussed an interesting upgrade case last week together with our upgrade colleagues in Support – and learned the double-hop (or triple-hop) upgrade case is not described in the documentation somewhere.
What is a multiple-hop upgrade?
Actually this describes the case where somebody can’t upgrade directly and has to do several upgrades in a sequence to reach the targeted release. For example, you start of with an Oracle 10.2.0.3 database – and your targeted release is Oracle 188.8.131.52 . A direct upgrade is not possible. I would opt now for Data …Continue reading...
At the DOAG Conference in November in Nürnberg in November 2016 a customer asked me right after my talk about “Upgrade to Oracle Database 12.2. – Live and Uncensored” why the DBA_REGISTRY_HISTORY does not get updated when he applies a Bundle Patch and follows all instructions including the “./datapatch -verbose” call.
I was wondering as well and asked him to open an SR. Which he did. And he received the message from Support that it is not supposed to appear in Oracle 12c anymore this way but only in DBA_REGISTRY_SQLPATCH. Now I dug a bit deeper …Continue reading...
I while back I blogged about issues with “rowsets“, a new Oracle 12c feature which unfortunately had two known wrong result (WQR) bugs:
What does “rowsets” actually mean?
I’d like to thank Sankar, our Development manager for providing this explanation which sheds some light on how important this feature actually is:
… Continue reading...
“Rowsets is a SQL execution performance enhancement introduced in Oracle RDBMS release 12.1 and further extended in a future release of the Oracle Database.
Prior to 12.1, data processing in the SQL layer were
Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!!
Upgrade to Oracle 184.108.40.206 is slow in phase: #65 ?
You are wondering why phase: #65 of the database upgrade to Oracle Database 220.127.116.11 takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while:
dbms_output.put_line('catuposb, update 4 - rows updated ' || rows_updated); END; -- end of update for system internally generated objs /
The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade …Continue reading...
A while back we added this slide to our big slide deck:
The story behind this slide
A large and important customer in the US tested a patch set upgrade – but when they approached the production upgrade from 18.104.22.168 to 22.214.171.124 on a large RAC cluster they’ve had to cancel the attempt and revert to the previous state.
They’ve hit a dictionary corruption somewhere silently sleeping in the database causing no trouble at all so far – until the upgrade touched the broken structures
They’ve asked us:
“How could we ensure the database is really healthy and …
A while back I blogged already about Incremental Statistics collection in Oracle Database 126.96.36.199:
And you’ll find more information in our documentation and in posts by our optimizer folks:
- Database SQL Tuning Guide 12c:
Gathering Incremental Statistics on Partitioned Objects
- Incremental Statistics Maintenance – what statistics will be gathered after DML occurs on the table? (Mar 18, 2012)
And you may read on this follow-up blog post about a related real world customer example …
Important to know is …Continue reading...
This issue got raised to my via a customer I know for quite a while – all credits go to Andy Kielhorn for digging down into that issue and solving it.
Failed RMAN Catalog Upgrade from 188.8.131.52 to 184.108.40.206
The RMAN catalog upgrade:
SQL> @?/rdbms/admin/dbmsrmansys.sql $ rman CATALOG rman/xxx@rman01 RMAN> UPGRADE CATALOG; RMAN> UPGRADE CATALOG;
failed with the following sequence of error messages:
error creating modify_ts_pdbinc_key_not_null RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found error creating modify_tsatt_pdbinc_key_not_null RMAN-00571: =========================================================== RMAN-00569: =============== ERROR… Continue reading...
Directly after my DOAG (German Oracle User Group) Conference presentation about “How Single-/Multitenant will change a DBA’s life” Martin Bach (Enkitec) approached me and told me about his experiences with the DROP PLUGGABLE DATABASE command and future recoverability.
Martin discovered that once you issued the DROP PLUGGABLE DATABASE command you can’t reuse a previously taken backup of this particular PDB anymore and recover the PDB into this existing CDB. I wasn’t aware of this and I’m glad that Martin told me about it.Continue reading...
Related Blog Posts:
- DBUA and Read-Only Tablespaces – Things to Know – I (Feb 3, 2016)
- DBUA and Read Only Tablespaces – Things to Know – II (Mar 30, 2016)
- DBUA displays wrong RMAN Backup for Restore (Sep 21, 2015)
- DBUA 12c and “datapatch.pl” – things to know (Jul 20, 2015)
Thanks to Rodolfo Baselli commenting on a previous blog post about the DBUA and Read-Only Tablespaces I dug a bit deeper and found out that “assuming silently” does not mean “works as intended“.
But one piece after another.
Rodolfo commented that if he …Continue reading...