Oracle Database 12.1.0.2

Data Pump 12.1.0.2 – Wrong Dump File Version – ORA-39142

Data Pump 12.1.0.2 - Wrong Dump File Version - ORA-39142 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 12.1.0.2 only when you are on a certain patch level. I summarize the issue here under Data Pump 12.1.0.2 – 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 …

Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2

Posted on January 18, 2018 by Mike.Dietrich Optimizer Patch Recommendation 0

I blogged about the Adaptive Features fixes in the past several times. But following some of the comments readers had I believe there’s some additional info for Adaptive Feature Fixes in Oracle 12.1.0.2 necessary.

What happened so far?

We delivered the most important fixes not only for adaptive features but only for dynamic sampling and some other things with the Database Bundle Patch in October 2017 for Oracle Database 12.1.0.2. The fixes got delivered on MS Windows a bit earlier.

September 2017 releases of preupgrade.jar and preupgrd.sql are available

Posted on September 11, 2017 by Mike.Dietrich Upgrade / Migration 7

On a regular basis the Database Upgrade Team releases new and improved versions of the preupgrade tools via a MyOracle Support note. And it’s time again: the September 2017 releases of preupgrade.jar and preupgrd.sql are available for download.

September 2017 releases of preupgrade.jar and preupgrd.sql are available

Please ALWAYS download the most recent versions of preupgrade.jar for upgrades to Oracle Database 12.2 and preupgrd.sql (including utluppkg.sql) for Oracle Database 12.1.0.2 upgrade from MOS Note: 884522.1. The version you’ll download is way newer than the version you’ll get with the base installation.

preupgrade.jar – Rel.7 – September 2017

Please …

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-19c

Posted on July 29, 2017 by Mike.Dietrich Removal 22

Oracle Label Security (OLS) clean up in Oracle Database 11.2-19c is almost as simple and straight forward as removing OWM or APEX. You can do it online without downtime. But in older releases a relink operation did cause downtime. Oracle Label Security is an extra cost option. More information is available on oracle.com.

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-19c

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2 Oracle Label Security is dependent on Oracle Database Vault (DV). In case DV is present you must remove it first if you plan to remove Oracle Label Security.

Before you start removing anything from …

Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2

Posted on July 27, 2017 by Mike.Dietrich APEX Removal 17

Oracle APEX (Application Express) clean up in Oracle Database 11.2-12.2 is fairly simple and straight forward. With the upgrade to Oracle Database 12.2.0.1 Oracle APEX does not get upgraded automatically anymore. If necessary you should upgrade Oracle APEX upfront of afterwards. But please do keep an eye on the APEX certification with Oracle Database 12.2.0.1 to avoid any issues:

Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2

Before you start removing anything from your database please …

Remove and Clean Up Components from Oracle Database 11.2 – 19c

Posted on July 26, 2017 by Mike.Dietrich Removal 28

Remove and Clean Up Components - Oracle 11.2 - 12.2

I will start a series of blog posts on how to remove and clean up components from Oracle Database 11.2 – 19c.

Precaution

This series of blog posts is not meant to recommend the removal of any options from an Oracle Database. It is not meant to create any sort of negativeness on any of the components or options. It’s is only meant to give you some advice and guideline in the case you’ll need to remove something.

The OUI (Oracle Universal Installer) installed everything on disk – and there’s a very good reason to do this. The purpose of …

Customer Success: Swiss Mobiliar Insurance

Posted on July 3, 2017 by Mike.Dietrich Customer Success 1

In late 2014 I’ve got asked if I’d like to work with Swiss Mobiliar, the oldest Switzerland insurer, on their upgrade project to move almost 300 databases from Oracle 11.2.0.3 to 12.1.0.2.

Read the full story about Swiss Mobiliar’s experience as an Oracle upgrade reference customer and why they will move to Oracle 12.2 here:
https://mobiliardbblog.wordpress.com/2017/06/28/to-12-or-not-to-12-2-or-why-mobiliar-is-one-of-the-first-to-move-to-12-2/

Swiss Mobiliar – Insurance – 300 databases to Oracle 12c

How do such projects start?

Usually we ask the customer upfront to sign a non-binding reference agreement with us. This ensures that we can support a customer in such a project before, during …

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

Posted on May 4, 2017 by Mike.Dietrich Behavior Change Oracle Database 12.2.0.1 Parameters 13

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1.

As per documentation in Oracle Database 12.1 it got defined as:

“PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“.

Furthermore the algorithm for its setting got described as:

“By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.”

Default

Unified Auditing – Performance Improvements in Oracle 12.1.0.2

Posted on April 19, 2017 by Mike.Dietrich Auditing Performance Tuning Security 14

Unified Auditing got introduced in Oracle Database 12.1.

Unified Auditing - Performance Improvements in Oracle 12.1 The downsides of the “old” auditing facilities became obvious when too many users had activities or transactions at the same time leading to audit records being written into AUD$. Contention was a typical issue. The same thing happened when too many users tried to login at the same time. Furthermore protecting the auditing information required Database Vault as there was no default protection available.

This – and some other things – should be remedied by Unified Auditing which is available since Oracle Database 12c. It gets enabled in sort of a “mixed …

Download Oracle Database 12.1.0.2 or older versions from the Oracle Software Cloud

Posted on April 6, 2017 by Mike.Dietrich General 3

I did blog a few weeks back about the download of older versions of the Oracle Database as OTN offers you only the most recent ones.

In case you miss the downloads of Oracle Database 12.1.0.2 on OTN

Looked pretty simply, huh?

Well, here’s quick “how to” to access older Oracle Software downloads from eDelivery aka Oracle Software Cloud.

Login to https://edelivery.oracle.com/, the Oracle Software Cloud
Start typing “Oracle Database Enter” into the Search by field. It will display you the option below – mark it with a click:
Search Box in the Oracle Software Cloud
Then use

In case you miss the downloads of Oracle Database 12.1.0.2 on OTN

Posted on March 9, 2017 by Mike.Dietrich General Oracle Database 12c 5

Somebody asked me the other day why the download options for Oracle Database 12.1.0.2 for Linux and Solaris disappeared from OTN?

The same will happen with the other platforms as soon as Oracle Database 12.2.0.1 is available on-premises for them as well.

You can always download Oracle Database 12.1.0.2 and previous releases from:

Oracle Software Cloud (eDelivery)

If you can’t locate the older release you are looking for please see this blog post I did in addition:

Download Oracle Database 12.1.0.2 or older versions from the Oracle Software Cloud

–Mike…

Install components in Multitenant ALWAYS with catcon.pl

Posted on March 2, 2017 by Mike.Dietrich Single-/Multitenant 11

I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post.

Upfront information – 2026

Dear Reader,

this blog post is from 2017 when Multitenant was relatively new, and when not all tools were really comfortably aligned yet.

Nowadays, almost 10 years later, I typically use DBCA to add missing options to PDBs. Since I wrote this blog post in 2017, I actually did not use a manual method anymore. And I did not verify the procedures again due to time constraints.

My …

New SPFILE parameters in Oracle Database 12.1.0.2 with July 2016 (and newer) PSU/BP

Posted on February 1, 2017 by Mike.Dietrich Behavior Change Oracle Database 12c Parameters 7

New Parameters in Oracle Database 12.1.0.2 with July 2016 PSU/BP

By following an internal discussion and checking parameter changes between Patch Set Updates (PSU) and Proactive Bundle Patches (BP) I learned that we introduced two new SPFILE parameters in Oracle Database 12.1.0.2 with the July PSU and BP. One is documented in the patch readme, the other one can be found right now only in the Oracle Database 12.2.0.1 manual:

ALLOW_GROUP_ACCESS_TO_SGA

ENCRYPT_NEW_TABLESPACES

The Oracle 12.2 documentation about ALLOW_GROUP_ACCESS_TO_SGA, the parameter which appears not in the Oracle 12.1 documentation right now, says:

ALLOW_GROUP_ACCESS_TO_SGA controls group access to shared memory

Release Dates Oracle Database 12.2.0.1 on-prem – Extended Support Waiving for Oracle 11.2.0.4 / 12.1.0.2

Posted on January 27, 2017 by Mike.Dietrich Lifetime Support Policy Oracle Database 12.2.0.1 27

Yesterday night the most important MOS Note:742060.1 got updated with the planned release date for Oracle Database 12.2.0.1 on-premises.

In addition, the dates for Waived Extended Support for Oracle Database 11.2.0.4 and 12.1.0.2 got extended as well.

Please see:

MOS Note: 742060.1: Release Schedule of Current Database Releases

In summary:

Oracle Database 12.2.0.1 for Exadata and SuperCluster is supposed to be released soon.
Oracle Database 12.2.0.1 for Intel Linux x86 and Solaris platforms (SPARC and Intel) is supposed to be released soon after the Exadata/SuperCluster releaseFOR EXACT DATES PLEASE SEE MOS Note: 742060.1.
The Free (Waived) Extended

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

Posted on November 23, 2016 by Mike.Dietrich Optimizer Oracle Database 12c Parameters 10

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS. And the key trigger to cure potential and known performance issues in Oracle Database 12.1.0.2 is enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

For more information please see:

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

But Oracle Database 12.2 on-premises is not out yet – so what should you do when upgrading to Oracle Database 12.1 – or struggling with some of the “adaptive” features in Oracle 12.1?

MOS Note: 2187449.1
Oracle Highly Recommended Adaptive Feature Configuration Parameter Settings for 12.1.0.2.0

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

Posted on November 22, 2016 by Mike.Dietrich Optimizer Oracle Database 12.2.0.1 Parameters 10

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

Nigel Bayliss, our “Optimizer” Product Manager has blogged about it already with way more detailed insights.

https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the

But as Oracle Database 12.2 on-premises is not available how should you deal with this feature in Oracle Database 12.1?

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

…

_rowsets_enabled – Apply patch and use the default

Posted on October 11, 2016 by Mike.Dietrich Flaws and Pitfalls Optimizer Oracle Database 12c Parameters Patch Recommendation 2

I while back I blogged about issues with “rowsets“, a new Oracle 12c feature which unfortunately had two known wrong result (WQR) bugs:

Switch off “_rowsets_enabled” in Oracle Database 12c

UPDATE: _rowsets_enabled in Oracle Database 12c

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

Global Temporary Tables – Change in Oracle 12c

Posted on July 7, 2016 by Mike.Dietrich Behavior Change Optimizer Oracle Database 12c 0

A few weeks back I was copied on an email conversation about a important change with Global Temporary Tables (GTT) in Oracle Database 12c.

Something you need to be aware of when using GTTs in Oracle Database 12.1.0.2:
Prior to this release GTTs shared statistics between sessions. Statistics were SHARED between different sessions. Now since Oracle Database 12c this is different by default – statistics on GTTs are visible to the SESSION only.

This can be changed and altered of course. And there are pros and cons. But as I’m not an optimizer expert I’m glad that …

Upgrade to Oracle Database 12c: We don’t insist :-)

Posted on April 29, 2016 by Mike.Dietrich Oracle Database 12c Upgrade / Migration 10

It’s so funny – for years I discuss with customers minimal downtime upgrade strategies back and forth, up and down. I saw DBAs really hunting to save a few seconds potential downtime – and I always take this serious as there is usually a real reason behind that. Just to learn a few days later by repeating experience that my work Windows7 laptop goes down to apply upgrades … and this takes looooooooong …. sometimes it takes a lengthy +30 minutes of quiet time. Whereas my OL6 VBox image applies upgrades in the background and needs a simple restart to …

Incremental Statistics Collection in Oracle 12.1.0.2 – Upgrade Pitfalls

Posted on April 27, 2016 by Mike.Dietrich Flaws and Pitfalls Incremental Stats 3

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

Incremental Statistics Collection improved in Oracle 12c

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 …

Incremental Statistics Collection in Oracle 12.1.0.2 – A True Story

Database Upgrade

Important to know is …

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

Posted on April 19, 2016 by Mike.Dietrich Flaws and Pitfalls RMAN 4

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

DBUA and Read-Only Tablespaces – Things to Know II

Posted on March 30, 2016 by Mike.Dietrich DBUA Flaws and Pitfalls Oracle Database 12c 3

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 …

GC Freelist Session Waits causing slowness and hangs

Posted on March 22, 2016 by Mike.Dietrich Behavior Change Flaws and Pitfalls Oracle Database 12c Parameters Patch Recommendation 0

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 …

Parameter Recommendations for Oracle Database 12c – Part II

Posted on March 8, 2016 by Mike.Dietrich Behavior Change Oracle Database 12c Parameters Patch Recommendation 6

Preface

Again, please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle

Parameter Recommendations for Oracle Database 12c – Part I

Posted on March 4, 2016 by Mike.Dietrich Oracle Database 12c Parameters Patch Recommendation 5

Preface

Please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

We strongly recommend SQL Performance Analyzer to …

Oracle Database 12.1.0.2

When does it happen?

What happened so far?

September 2017 releases of preupgrade.jar and preupgrd.sql are available

preupgrade.jar – Rel.7 – September 2017

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-19c

Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2

Precaution

How do such projects start?

Default

Unified Auditing got introduced in Oracle Database 12.1.

Upfront information – 2026

New Parameters in Oracle Database 12.1.0.2 with July 2016 PSU/BP

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

What does “rowsets” actually mean?

Database Upgrade

Failed RMAN Catalog Upgrade from 11.2.0.4 to 12.1.0.2

But one piece after another.

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

Preface

Preface

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Recent Posts