Oracle Database 12.1.0.2

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

Recently a customer raised a question whether there are differences between the Automatic Statistics Gathering job and a manual creation of stats via the GATHER_SCHEMA_STATS procedure.

The results in performance were quite interesting. Performance after an upgrade from Oracle Database 11.2.0.3 to Oracle Database 11.2.0.4 was not good when the automatic stats job got used. But performance changed significantly to the better when schema stats were created with the downside of taking more resources during the gathering.

Is the Automatic Stats Gathering job enabled?

That question can be answered quite easily. There’s a very good MOS Note:1233203.1 – FAQ: Automatic

Continue reading...

New PREUPGRD.SQL is available for Upgrades to 12c

üreupgrd.sql

As of today a new version of our upgrade tool preupgrd.sql (including the package utluppkg.sql) for upgrades to Oracle Database 12.1.0.2 is available as download from MOS:

Download it and exchange the existing preupgrd.sql and utluppkg.sql in your current Oracle 12.1.0.2 ?/rdbms/admin directory.

–Mike

Continue reading...

SuSE SLES 12 certified with Oracle Database 12.1.0.2

Puh … I’ve got many mails over several months asking about the current status of certification of SuSE SLES12 for Oracle Database 12.1.0.2. It took a while – and I believe it was not in our hands. But anyhow … finally …

See Release Notes for additional package requirements
Minimum kernel version: 3.12.49-11-default
Mininum PATCHLEVEL: 1

Additional Notes

  • Edit CV_ASSUME_DISTID=SUSE11 parameter in database/stage/cvu/cv/admin/cvu_config & grid/stage/cvu/cv/admin/cvu_config
  • Apply Patch 20737462 to address CVU issues relating to lack of reference data
  • Install libcap1 (libcap2 libraries are installed by default); i.e. libcap1-1.10-59.61.x86_64
Continue reading...

Clean up APEX – Journey to the Cloud IV

What happened so far on my Journey to the Cloud?

DBaaS Oracle Cloud

Today’s journey: Cleanup APEX removal leftovers 

When you read my “Patch” blog post from Dec 22, 2015 you’ll see

Continue reading...

New Webcast “Why Upgrade to Oracle 12c” available

In December 2015 I did a recording for a webcast about “Why Upgrade to Oracle Database 12c” refreshing the content from a few months back.

Webcast “Why Upgrade to Oracle Database 12c”

Oracle’s latest generation of database technology, Oracle Database 12.1.0.2, has some impressive new features. It offers great potential for a fast upgrade, simple migrations and consolidation, making more efficient use of hardware and delivering major improvements in management efficiency.

Join our webcast on Upgrading to Oracle Database 12c and you will learn:

  • Why you still need to upgrade to Oracle Database 12.1.0.2
  • How to ensure that your applications
Continue reading...

RMAN NOOPEN Clause DUPLICATE FOR UPGRADE

In Oracle Database 12c, the RMAN “DUPLICATE” command has new “NOOPEN” option which clones the database but does not open the auxiliary instance. This feature can be used to duplicate a database to higher version.

MOS Note: 2022820.1
Upgrade to 12c database using RMAN Duplicate with NOOPEN clause

The note contains also a full example on how to duplicate the production database as a standby, and upgrade it.

–Mike…

Continue reading...

Patch, patch, patch – Journey to the Cloud – III

What happened so far on my Journey to the Cloud?

DBaaS Oracle Cloud

I haven’t stopped my journey if you wonder about the inactivity for two weeks. I just had to learn …

Continue reading...

New version of our Hands-On Lab available for download “Upgrade/Migrate/Consolidate to Oracle 12c” HOL

Yes! Finally …

Roy did all the hard work and patched all our environments with the most recent PSUs. And he managed also to upload our Hands-On Lab not only internally to retriever (Oracle internally) but also to OTN.

What you’ll be able to do?

  • Upgrade an 11.2.0.4 database UPGR to Oracle Database 12.1.0.2
  • Plug in UPGR into the CDB2, an Oracle Database 12.1.0.2 Container database, and make it a PDB
  • Migrate an 11.2.0.4 database FTEX into another PDB using the Full Transportable Export/Import feature
  • Unplug an Oracle Database 12.1.0.1 PDB and plug it into an Oracle Database 12.1.0.2
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...

Why you should remove APEX from the CDB$ROOT

Upgrade Blog posts about Oracle Application Express:


Oracle APEX (Application Express) is great piece of software. But it gets installed by default into the container database’s CDB$ROOT unless you’d customized your CDB creation via scripts in Oracle 12.1.0.2. See:

on how to customize a Single/Multitenant Database with less options.

But that is not the topic I …

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

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

Where do these large trace files come from in Oracle 12c?

Just had an observation about very large trace files on one of my customers I’m working with at the moment. When I write “very” I mean “VERY” as some grew over 10GB within a few hours.

Growth - Trace Files - (c) Mike Dietrich

The files contained a ton of such messages:

----- Cursor Obsoletion Dump sql_id=5p8a9d4017bq3 -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=00007FFB8AD45CB0 phd=00007FFB8AD45CB0

After doing a bit of research I came across this document and an explanation:

MOS Note:1955319.1;
Huge Trace Files Created Containing “—– Cursor Obsoletion Dump sql_id=%s —–“

Well, we introduced an Enhancement – via an unpublished bug (and I’d guess it is …

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

New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing.

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) – and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter.

  • Oracle White Paper: Best Practices for Gathering Statistics –
Continue reading...

New version of the UPGRADE 12c SLIDE DECK available

Good news – finally we publish a new version of our big upgrade/migrate/consolidate slide deck:

And don’t be scared – we reached now the 600 slide count 😉

New topics included (and subject to more extension within the next weeks) are:

  • Why you seriously can’t wait for Oracle Database 12.2.0.2
  • Unicode Migration with DMU
  • Migrate into the Cloud 
  • Single Tenant
  • RMAN incremental backups speeding up Full Transportable Export/Import

and many more …

Sorry that we didn’t keep track in the “Change Log” at the end of the deck. But there were …

Continue reading...

SE2 – Some questions, some answers …

Oracle Standard Edition SE2 12.1.0.2*** Updated on Nov 6  to reflect 11.2.0.4 changes***

There were a lot of questions regarding Oracle Database Standard Edition 12.1.0.2 SE2 in the past days. See below for further blog posts on SE2.

And please find the official Oracle SE2 “brief” document here giving you a good overview on SE2:
http://www.oracle.com/us/products/database/oracle-db-se2-brief-2680836.pdf

I’ll try to summarize some topics I’m able to answer by myself. And please don’t expect me to assist with license or license migration questions – that is far beyond my scope or knowledge or responsibility, and you may please contact your Oracle sales person regarding these topics …

Continue reading...

MOS Download for Oracle Database 12.1.0.2 including SE2

Please find all our articles about Oracle Database Standard Edition 12.1.0.2 SE2:

 

You have 3 options to download Oracle Database Enterprise Edition 12.1.0.2 and Oracle Database Standard Edition 12.1.0.2. SE2.

Regarding OTN and eDelivery nothing …

Continue reading...

Oracle Database 12.1.0.2 SE2 – Support and Patches for Oracle Database 12.1.0.1 SE/SE1

Oracle SE2

Please find all our articles about Oracle Database Standard Edition 12.1.0.2 SE2:

 

Some questions regarding support for Oracle Database Standard Edition 12.1.0.1 (SE/SE1) came up in the past days.

Most of those are answered already in MOS Note 2027072.1 (Oracle Database 12c Standard Edition 2
(12.1.0.2) )

How long will

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

Oracle Databases and Microsoft Windows 10

Please find a more current blog post about certification of Oracle Database 12c on MS Win 10 here:

MS Windows 10


If you have upgraded already or plan to upgrade to Microsoft Windows 10 in the near (or later) future, you may find this Statement Of Direction by Oracle regarding Microsoft Windows 10 certification quite useful:

http://www.oracle.com/technetwork/database/windows/whatsnew/oracle-db-win10-sod-2626195.pdf

In summary:

  • Oracle plans to certify Oracle Database 12.1.0.2 by October 2015
  • Oracle plans to certify the next major version of the database as well on MS Win 10 – 64bit

Addition [Oct, 22, 2015]:

  • Oracle VirtualBox 5.0.8 released
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...

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