Flaws and Pitfalls

MOS Note:136697.1 – New HCHECK.SQL for Oracle Database 12c

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 11.2.0.2 to 11.2.0.3 on a large RAC cluster they’ve had to cancel the attempt and revert to the previous state.

Reason

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

Continue reading...

Incremental Statistics Collection in Oracle 12.1.0.2 – Upgrade Pitfalls

A while back I blogged already about Incremental Statistics collection in Oracle Database 12.1.0.2:

And you’ll find more information in our documentation and in posts by our optimizer folks:

And  you may read on this follow-up blog post about a related real world customer example …

Database Upgrade

Important to know is …

Continue reading...

RMAN Catalog Upgrade fails – ORA-02296 – error creating modify_ts_pdbinc_key_not_null

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 11.2.0.4 to 12.1.0.2

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

DROP PLUGGABLE DATABASE – things you need to know

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.

Actually only the meta information in the controlfile or the RMAN catalog will be deleted. But archive

Continue reading...

DBUA and Read-Only Tablespaces – Things to Know II

Related Blog Posts:


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

Disable Transparent Hugepages on SLES11, RHEL6, RHEL7, OL6, OL7 and UEK2 Kernels

This blog post is not related to database upgrades and migrations. But still I think it is very useful for many customers operating on modern Linux systems.

Recommendation 

Support just published an ALERT strongly recommending to disable Transparent Hugepages on Linux systems. And the below information does not apply to RAC systems only but also to single instance environments.

Which Linux Distrubutions/Kernels are affected? 

  • SLES11
  • RHEL6 and RHEL7
  • OL6 and OL7
  • UEK2 Kernels

What are the Issues? 

I’m quoting MOS Note: 1557478.1 (ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7 and UEK2 Kernels):

Because Transparent HugePages are

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

DBUA and Read-Only Tablespaces – Things to Know – I

Related Blog Posts:


Some people prefer the manual upgrade on the command line, others prefer the graphical tool Database Upgrade Assistant (DBUA).

DBUA and Read-Only Tablespaces 

The DBUA offers you an option of setting your non-Oracle tablespaces read-only during the upgrade.

DBUA Read Only 1

What the option doesn’t tell you …

Continue reading...

Query on ALL_SYNONYMS is slow in Oracle Database 12c

A customer (Thanks Stefano!) alerted me on this issue during a workshop and I did some further investigation. Basic headline is:

Query on ALL_SYNONYMS is very slow in Oracle Database 12.1.0.2 compared to 11g.

The workaround for this  21324443: SLOW QUERY IN 12C ON ALL_SYNONYMS. is:

dbms_stats.gather_table_stats('SYS','OBJ$',estimate_percent
=>100, method_opt => 'for columns flags size 1, spare3 size 254, type# size 254');

and I see that at least 8 other customers opened SRs hitting the same issue.
Does the workaround suit you in any way?

The bug had no progress since it was opened. If you are seeking progress …

Continue reading...

Network ACLs and Database Upgrade to Oracle 12c

What has been changed in Oracle Database 12c with Network ACLs?

Starting from 12c, network access control in the Oracle database is implemented using Real Application Security access control lists (ACLs). Existing 11g network ACLs in XDB will be migrated. Existing procedures and functions of the DBMS_NETWORK_ACL_ADMIN PL/SQL package and catalog views have been deprecated and replaced with new equivalents

In 12c, a network privilege can be granted by appending an access control entry (ACE) to a host ACL using DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE.  If you append an ACE to a host that has no existing host ACL, a new …

Continue reading...

UPDATE: _rowsets_enabled in Oracle Database 12c

Please find a recent update here:


Last week I did post this entry with a strong recommendation to disable _rowsets_enabled in Oracle Database 12.1.0.2:

Today I can give you an update, more insight information and better workarounds.

Credits go to our DWH and Optimizer people (thanks to Hermann, Angela, Nigel and Mohammed).

When is the problem happening?

When a hash join operation receives rowsets from its right input but then produces one row at a time as output. This explains …

Continue reading...

Oracle VirtualBox 5.0.x – Segmentation Fault in PERL

Please see as well:


 

Yesterday and the day before I’ve exchanged several emails with Ana who downloaded our Hands-On-Lab from here:

after OOW15, encountering a SEGMENTATION FAULT when trying to start the database upgrade with catctl.pl:

$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

Segmentation fault 

Very strange thing … 

The database is in upgrade mode (checked this in the alert.log) and there are no strange things mentioned anywhere. Plus hundreds of people have run and completed our lab so far.

Tue Nov 10 20:39:47 2015
MMON 
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 21923026 – patch Oracle 12c Home before upgrading if you have OLTP Compression in 11g

Patch

Actually I’d consider this as a real serious issue which may affect many customers with larger deployments using the Advanced Compression Option’s OLTP Compression.

I came across it as Don Seiler full of anger twittered some bad words about Oracle Database 12.1.0.2.. We’ve exchanged a good number of emails – and I could see the issues Don and his colleagues got while assisting a customer to go live on Oracle Database 12c.

The most interesting one happened on the physical standby after the primary got upgraded. The MRP failed with an ORA-600.

Issue

After upgrading from Oracle 11.2.0.3 to …

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

DBUA displays wrong RMAN backup for restore – Oracle 12.1.0.2

Related Blog Posts:


If you are using the Database Upgrade Assistant (DBUA) to upgrade your database to Oracle Database 12.1.0.2 be aware when you choose to potentially restore your database from a existing backup in case of an error during the upgrade.

First of all I would always stop DBUA …

Continue reading...

DBMS_STATS. GATHER_DICTIONARY_STATS fails with ORA-20001 ORA-6502 ORA-6512 – Concurrent Stats

I really have to say “Thank you very much” to the people out there alerting me about issues I haven’t seen before. Just in the past week I’ve got to learn about three issues which are related to the upgrade – and I haven’t seen before. Please don’t expect me always to follow up with the progress of your SRs or the related bugs. But your experiences are such an important source for me – so thanks again!

The most recent issue (thanks to Bernd Tuba from MM Warburg) …

Symptom

execute dbms_stats.gather_dictionary_stats

ORA-06502: PL/SQL: numeric or value 
Continue reading...

Invalid Table Data before Upgrade to 12.1.0.2?

You plan to upgrade your database(s) to Oracle Database 12.1.0.2,?

You did run the preupgrd.sql including the preupgrade package in your current database already?

But in the preupgrade.log you’ll see the following ERROR:

ERROR: --> Invalid Oracle supplied table data found in your database.

     Invalid data can be seen prior to the database upgrade
     or during PDB plug in.  This table data must be made
     valid BEFORE upgrade or plug in.

   - To fix the data, load the Preupgrade package and execute
     the fixup routine.
     For plug in, execute the fix up routine in the PDB.

    @?/rdbms/admin/utluppkg.sql
SET SERVEROUTPUT 
Continue reading...

Ouch, this hurts: bug 17325413 – patch BEFORE upgrade!

PatchI really don’t want to turn this blog into something making our database look bad. But in this case it is really necessary as it is VERY UNUSUAL that we recommend to patch the database BEFORE upgrade.


Just for clarification:

The following topic will affect all databases between 11.1.0.6 and 11.2.0.4.1 – those (and only those) need to be patched BEFORE upgrade. The topic is fixed in 12.1.0.2 but as it gets introduced with the BEFORE upgrade database version you’ll have to apply the fix before upgrade. The inclusion of the fix in 12.1.0.2 means only that the

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

Things to consider BEFORE upgrading to Oracle 12.1.0.2 to AVOID poor performance and wrong results

Finally it got published on MyOracle Support (MOS) portal – one of the most helpful notes when we prep for an upgrade or a migration – and potentially the one with the longest title in MOS:

MOS Note: 2034610.1
Things to consider BEFORE upgrading to Oracle Database 12.1.0.2 to AVOID poor performance and wrong results 

Avoid Poor Performance and Wrong Results when upgrading to 12.1.0.2

One important note regarding these “Things to Consider” notes:
They will only list issues where either a fix or a proven workaround is available. They don’t list all open issues nor do they recommend every given underscore which may cure one or the other misbehavior.…

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

Oracle 12.1.0.2 – Security Behavior Change with non-SYSDBA Triggers

Oracle Database SecuritySometimes things get revealed at unexpected occasions. This one happened during a recent customer upgrade to Oracle Database 12c with a 3rd party geospatioanl application installed (ESRI).

At the very end of the upgrade the customer saw many ORA-1031 (insufficient privileges) errors and it seemed to be that nothing was working correctly anymore.

This happened during the run of catupend.sql. The following code path in  catupend.sql causes the error.

cursor ddl_triggers
is
select o.object_id from dba_triggers t, dba_objects o
where t.owner = o.owner and t.trigger_name = o.object_name and o.object_type = 'TRIGGER'
and (t.triggering_event like '%ALTER%' or t.triggering_event like '%DDL%');
Continue reading...

Is it always the Optimizer? Should you disable Group By Elimination in 12c?

I wouldn’t say it’s always the optimizer – but sometimes one or two tiny little things are broken making it necessary to turn off new functionality for a while.

Please don’t misinterpret this posting!
As far as I see (and I really do work with customers!) I’d state the Oracle Database 12.1.0.2 Optimizer is more stable, faster and predictable compared to 11.2.0.x. Our Optimizer Architects and Developers have done a great job. But with all the complexity involved sometimes it takes a few fixes or incarnations until a great new feature really matures. The Group-By-Elimination feature in Oracle Database …

Continue reading...