Flaws and Pitfalls

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

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

Incremental Statistics Collection improved in Oracle 12c

Traveling right now through Asia. It was Beijing for 32 hours, Toyko for 24 hours – and now we are running an internal 2-day workshop with colleagues from Korea, New Zealand, India and some other countries in Seoul. And yesterday I had the pleasure to listen to Tom Kyte to his optimizer talk at the OTN Conference in Tokyo. And I learned a lot – as always when having the chance to listen to Tom, Graham Wood and the other great experts.

Incremental Statistics Collection improved in Oracle 12c

Oracle Database 11.1 offered a great new feature: Incremental Statistics Collection which helped a lot to …

Continue reading...

Sleeping Beauties – Upgrade to 11.2.0.4 can be slow

A customer from the US did contact me past week via LinkedIn and raised a question:

“Is it expected that my patch set upgrade from Oracle 11.2.0.3 to Oracle 11.2.0.4 takes over 3 hours?”

Of course, no – this is not expected.

This is the upgrade stats gathered post upgrade with utlu112s.sql:

SQL> @?/rdbms/admin/utlu112s.sql ; .
Oracle Database 11.2 Post-Upgrade Status Tool
10-31-2014 10:05:29
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server
. VALID 11.2.0.4.0 02:46:19

JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:08:34
[..]
Final Actions
. 00:00:00
Total Upgrade Time: 03:06:47

No, this …

Continue reading...

Maintenance Windows is too small? Autotask Jobs fail

Maintenance Windows is too small? Autotask Jobs fail

Since Oracle Database 10g we have automatic maintenance jobs in the database running. Since Oracle Database 11g (and the same applies to 12c) we have:

  • Weekday Jobs being able to get executed from 10pm until 2am on MON-FRI
  • Weekend Jobs being able to get executed from 6am until 2am on SAT-SUN

A support colleague contacted me a while ago asking why at his customer the jobs don’t start anymore as they have set the duration for weekdays to 15 minutes. You may adjust the window generally by yourself with:

  • exec DBMS_SCHEDULER.SET_ATTRIBUTE(‘MONDAY_WINDOW’,’DURATION’,numtodsinterval(2,’hour’));

But in this particular case only changing the default

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

RMAN Catalog requires Enterprise Edition (EE)<BR>since Oracle Database 12.1.0.2Now you start to wonder as your Catalog Database had been an Oracle Standard

Continue reading...

RMAN Catalog Upgrade to Oracle 12.1.0.2

It sounds so simple – but in this specific case a bit of doc reading is required as upgrading the RMAN catalog to handle Oracle 12.1.0.2 databases is not as trivial as in the past.

Thanks to a German customer and a friendly colleague from Sales Consulting in Stuttgart I have learned my RMAN lesson for Oracle Database 12.1.0.2 this week. A simple “upgrade catalog” is not the correct step once you’d like to handle backups of Oracle 12.1.0.2 databases in your current catalog schema. RMAN Catalog Upgrade to Oracle 12.1.0.2

Even though you may not have ever heard before about Virtual Private

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

Upgrade to 11.2.0.3 - OCM: ORA-12012 and ORA-29280OCM 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...