Issue

Transportable Tablespace PDB Migration and Local Undo

Transportable Tablespace PDB Migration and Local Undo

Photo by Steve Johnson on Unsplash

Sometimes it is necessary to warn you about known pitfalls to avoid frustration. In this particular case I decided not to blog about it simply because I thought this won’t happen to too many other people. Well, yesterday my good friend Philippe Fierens dropped me a message about an issue he ran into with a Transportable Tablespace PDB Migration and Local Undo. And I immediately knew what caused him trouble – and I regret that I didn’t blog about it (sorry Philippe!). We’ve seen the same problem with a large ExaCC migration project …

Continue reading...

Why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE

Why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE

There usually a philosophic battle happening when it comes to underscore parameters. The official statement is more or less: It’s an underscore, hence it is undocumented and you shouldn’t touch it unless Oracle Support advises you to do so. I agree in many cases. But in this specific one, I don’t. And in this post I will explain why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE in all your singe- and Multitenant environments. Always!

A bit of history

At first, a little bit of a history excursion. This parameter wasn’t an underscore in Oracle 12.1. You can find it as EXCLUDE_SEED_CDB_VIEW in 12.1.0.1 …

Continue reading...

RMAN Backup Gives RMAN-06091: No Channel Allocated for Maintenance

RMAN Backup Gives RMAN-06091: No Channel Allocated for MaintenanceI 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 12.2.0.1 with traces. This was fixed with the July 2018 RU for Oracle 12.2.0.1. 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...

Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1 with JDBC

Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1 with JDBCThanks to my support colleague Roland Graeff who told me about this issue at a customer today. And I consider this a pretty serious issue. It can happen that Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1. with JDBC.

This is as bad as it sounds. Roland told me about a case where the application showed weird characters instead of German umlauts (ä, ö, ü, Ä, Ö, Ü) after an upgrade from Oracle Database 12.1.0.2 to 12.2.0.1.

Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1 with JDBC

Roland explained to me that …

Continue reading...

RMAN backup generates traces in Oracle 12.2.0.1

RMAN backup generates traces in Oracle 12.2.0.1Oracle 12.2.0.1 seems to be the “tracing” release without further patch or parameter treatments. After posting about MMON unconditional traces in Oracle 12.2.0.1 a day ago, I received a couple of comments either on the blog, via LinkedIn or Twitter regarding other trace facilities in Oracle Database 12.2.0.1. One of them is the case that RMAN backup generates traces in Oracle 12.2.0.1.

And please, very important upfront:
Oracle Database 12.2.0.1 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 …

Continue reading...

MMON unconditional traces in Oracle 12.2.0.1

MMON unconditional traces in Oracle 12.2.0.1I 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...

Issue with PDB Archives in Oracle 12.2.0.1 in ASM

There is a fancy new command to unplug a PDB in Oracle Database 12.2.0.1:

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...

DBA_REGISTRY_HISTORY vs DBA_REGISTRY_SQLPATCH

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...

_rowsets_enabled – Apply patch and use the default

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:

“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

Continue reading...

GC Freelist Session Waits causing slowness and hangs

Best Practice Hint

One of the best things in my job:
I learn from you folks out there. Everyday.

Credits here go to Maciej Tokar who did explain the below topic to me via LinkedIn – thanks a lot, Maciej!

Locks are not being closed fast enough, resulting in gc freelist waits

You can find a reference for Global Cache Freelist in the Oracle Documentation. This issue here can or will lead to database being slow, up to complete hangs. Based on my research it looks as the issue is not related to RAC only but a general thing. In your session …

Continue reading...

Switch off “_rowsets_enabled” in Oracle Database 12c

Please find a recent update here:

and more important:


Twitter is a good thing. I get alerted on things I haven’t seen before. And sometimes some things are more than interesting.

This one is actually proven by Jonathan Lewis – and you can read all the details in Jonathan’s blog post here:

There seems to be a realistic chance to get wrong query results displayed (regardless of using SQL*Plus or a JDBC or any other client – see the comment by Stefan Koehler below …

Continue reading...

Ouch, this hurts: bug 20880215 – patch Oracle 12c

Patch

Just realized that my preivous blog post heading was misleading: it said “patch AFTER upgrade” which is misleading. You should apply this patch BEFORE you upgrade but of course to your new/future Oracle 12c home. If you hit the issue then apply the patch after upgrading to remedy it. Sorry for the confusion.


No updates for over a week? Sorry for that but too many workshops, swamped with OOW prep work – and a wonderful issue with the browser cache and the blog software putting all my new blog post work directly into /dev/null.

Thanks to Marcel Paul for

Continue reading...

DBCA 12c and “datapatch.pl” – things to know

A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing ‘datapatch’ (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:

For DBUA, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl – as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch’s execution):

Datapatch phases command line upgrade catctl.pl

But afterwards I have learned that things are worse.
The same behavior is true when you create

Continue reading...

Optimizer Issue in Oracle 12.0.1.2: “Reduce Group By”

Wrong Query Results BugDBAs biggest fears I’d guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying “Sorry, I was in a bad mood today …”.

The Oracle Database Optimizer is a complex piece – and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues – and sometimes it is necessary to switch off tiny little pieces …

Continue reading...

Log Writer Slave Issues in 12.1.0.2 – mainly on IBM AIX and HP-UX but also on Linux

Currently we see a lot of issues with the Multiple Logwriter feature in Oracle Database 12.1.0.2, especially on the IBM AIX platform.

What are Multiple LGWRs?

You will see multiple LGnn (Log Writer Worker) processes on multiprocessor systems, The LGWR (Log Writer) creates worker processes (slaves) to improve the performance of writing to the redo log. LGnn (Log Writer Worker) processes are not used when there is a SYNC standby destination. Possible processes include LG00LG99.

Here is a list of new background processes in Oracle Database 12.1:
MOS Note 1625912.1 – New Background

Continue reading...

ORA-20000 Unable to gather statistics concurrently: Resource Manager is not enabled
ORA-06512: at “SYS.DBMS_STATS”

We are running a great training this week in Munich, called TECH CHALLENGE for Oracle people only. And one group hit the below issue so I thought I should share this as it is very likely it will happen with the upgrade to Oracle Database 12c in your environment as well.

During the run of catctl.pl or DBUA you may get an error saying:

ORA-20000: Unable to gather statistics concurrently:
Resource Manager is not
enabled.

ORA-06512: at “SYS.DBMS_STATS”

Even worse, the upgrade will fail and exit in phase 68 of the parallel upgrade.
This issue is new with Oracle Database …

Continue reading...

RMAN Catalog requires Enterprise Edition (EE)
since Oracle Database 12.1.0.2

Credits go to Cameron Hodge, Malcom and Martin Mäs who all highlighted issues to me following my previous entry about RMAN – and sorry for any disappointment but I wasn’t aware of all these nice little things.

Ok, you’d upgrade your RMAN Catalog to be ready to backup/recover Oracle Database 12.1.0.2 databases and you see this error:

RMAN>
upgrade catalog;
error creating create_deleted_object_seq
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database:
ORA-00439:  feature not enabled: Partitioning

Now you start to wonder as your Catalog Database had been an Oracle Standard

Continue reading...

Parameter _rollback_segment_count can cause trouble

Just some weeks ago we’ve learned that setting the hidden underscore parameter:

_rollback_segment_count

may cause trouble during upgrade. This parameter is used in very rare cases to have under all circumstances and situations this specified number of UNDO’s online. Now during upgrade this may result in massive latch contention  – and there’s a patch available as well. Recommendation is to unset it during upgrade.

I don’t think that many people will hit this as I personally haven’t seen databases with this underscore in their init.ora or spfiles. So take this post more or less as a reminder …

Continue reading...

Ouch … again OCM

Another customer, another upgrade – and again from Oracle Database 11.1.0.7 to Oracle Database 11.2.0.3. And again an OCM error during upgrade which is not very obvious to relate to OCM once you see the errors during upgrade.

While monitoring the database during upgrade with tail -f on the alert.log we’ve spotted this one here:

ORA-604: error occurred at recursive SQL level 3
ORA-904: "XDB"."DBMS_CSX_INT"."GUIDTO32": invalid identifier
XDB SGA reset to NULL.

Looks like some issue with XDB. But actually it is related to OCM and it seems that something gets created in the wrong order. Currently DEV is working …

Continue reading...

Upgrade to 11.2.0.3 – OCM: ORA-12012 and ORA-29280

OCM is the Oracle Configuration Manager, a tool to proactively monitor your Oracle environment to provide this information to Oracle Software Support. As OCM is installed by default in many databases but is some sort of independent from the database’s version you won’t expect any issues during or after a database upgrade 😉

But after the upgrade from Oracle 11.1.0.7 to Oracle 11.2.0.3 on Exadata X2-2 one of my customers found the following error in the alert.log every 24 hours:

Errors in file /opt/oracle/diag/rdbms/db/trace/db_j001_26027.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", 
Continue reading...

Fundamental Oracle flaw revealed??? Update …!

Writing a blog has a nice effect on the side: it brings me in touch with people I’ve never met – but we have similar targets or interests. Today I’ve received a comment from Gökhan Atil about the Fundamental Oracle Flaw known also as the SCN issue.

And for sure I did visit Gökhan’s blog and I did watch his very interesting 5 minutes demonstration how to bring down a database with the SCN issue. Which is very good to know as I thought the database won’t come down because of this issue. But watch Gökhan’s video by …

Continue reading...