Oracle 12c

Drop public synonym XMLCONCAT prior to upgrade

Posted on August 2, 2018 by Mike.Dietrich Flaws and Pitfalls 0

Thanks to my team mates, Cindy and Hector, who alerted me on this newly detected issue. When your database has been upgraded from release to release, it could happen that a public synonym XMLCONCAT exists. And before you upgrade to Oracle 12.2.01 or Oracle 18c you must drop this public synonym to avoid upgrade errors.

Drop public synonym XMLCONCAT prior to upgrade

The public synonym XMLCONCAT in pre-9.2.0.2 Oracle databases pointed to a PL/SQL function of the same name. In 9.2.0.2, this PL/SQL function was changed to a C function.The public synonym was no longer needed nor pointing to a …

Don’t drop the AUDSYS user before upgrading

Posted on February 27, 2018 by Mike.Dietrich Auditing 4

Don't drop the AUDSYS user before upgrading This is a very interesting case Martin Berger sent to me a week ago. A colleague and him were basically wondering if they should follow the advice in the Oracle 12.2 upgrade guide and drop the AUDSYS user, or if they better don’t drop the AUDSYS user before upgrading.

What is AUDSYS meant for?

In short, it is the auditing user schema for Oracle 12c and especially Unified Auditing. You’ll find a bit more information regarding Unified Auditing on this blog:

utlrp.sql – How to decrease resource consumption with utlprp.sql

Posted on October 11, 2017 by Mike.Dietrich Best Practice 4

utlrp.sql - How to decrease resource consumption with utlprp.sql Just being back from Oracle Open World 2017 I’m working on the open topics, the homework, the follow-up activities. And I took several notes during OOW when customers asked me about things I’ve seen before.

One topic I wanted to write about a long time ago already: utlrp.sql – How to decrease resource consumption with utlprp.sql.

Where it started

Actually it’s all Uwe’s fault (Uwe Kirchhoff is one of my best and oldest friends at Oracle and one of the best ACS guys I can think of). Uwe told me this story about a customer case he …

Björn Bolltoft’s Blog: Oracle Database Managebility

Posted on August 24, 2017 by Mike.Dietrich Real Application Testing 0

I know, there are so many blogs out there. Some are good and helpful, others are driven by marketing messages only. But I believe this one will become a really good one in the Oracle universe: Björn Bolltoft’s Blog: Oracle Database Manageability.

Björn Bolltoft’s Blog: Oracle Database Managebility

First of all, let me say some words about Björn. I know Björn now for over 10 years. But he works even longer than I for Oracle. Björn joined Oracle Sweden in 1995. I bumped into Björn at our second Tech Challenge training. This is a hard-core training for geeks, …

Always create databases as CUSTOM databases

Posted on July 11, 2017 by Mike.Dietrich DBCA 9

I’m giving this advice to customers for many years:
When you create a fresh Oracle database ALWAYS create databases as CUSTOM databases. Via template or not doesn’t matter.

Always create CUSTOMER databases In the DBCA better use your own and wisely configured template rather than the prebuilt (we used to call them: seed) databases. The seed databases for “Data Warehouse” and “General Purpose or Transaction Processing” have all options and components included. I use them for testing from time to time.

As the upgrade duration mainly depends on the number of installed components I’d recommend to choose the components to install, and …

Why does the upgrade of PDB$SEED always take longer?

Posted on July 10, 2017 by Mike.Dietrich Single-/Multitenant Upgrade / Migration 0

Upgrading the PDB$SEED takes always longer than any other container.
What is the reason for an upgrade of the PDB$SEED taking so much longer in a real world environment?

How does an upgrade with Oracle Multitenant work?

Upgrading the PDB$SEED takes always longer I have written several blog posts in the past about the different techniques to upgrade an Oracle Multitenant environment:

The interesting case happens in the “Everything At Once“. The PDB$SEED takes significantly longer to upgrade than any other container.

Upgrading the PDB$SEED takes always longer?

Quick recap: The PDB$SEED is the (I call it) blueprint …

Can you apply Bundle Patches to SE2 databases?

Posted on June 15, 2017 by Mike.Dietrich Patch Recommendation SE2 - Standard Edition 2 0

Can you apply Bundle Patches (BP) to Standard Edition (SE2) databases?

This question got raised by Adam (thanks!) via the comments section of the blog. But a few days later I read it on the internal mailing lists as well. Adam was referring to this sentence in the BP READMEs since October 2015:

“In this document Oracle Database Home refers to Enterprise Edition. Standard Edition Database software installs should install Database PSU.”

Why was this sentence added?

There were issues with enabling functionality on SE2 databases by accident with a Proactive Bundle Patch – something which got fixed and …

Default Changes SPFILE Parameters – Oracle 12.2

Posted on January 18, 2017 by Mike.Dietrich Oracle Database 12.2.0.1 Parameters 9

This is the 4th posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.

Roy and I did a comparison between default parameter settings in Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 vs Oracle Database 12.2.0.1. And some changes are quite interesting – of course the memory driven parameters are left out in this list.

Color RED marks a change between releases.
The databases were …

How to find out if a PSU has been applied? DBMS_QOPATCH

Posted on February 2, 2016 by Mike.Dietrich Patch Recommendation 10

Since we change the PSU and BP patch numbering from Oracle Database 12.1.0.2.PSU6 to 12,1,0,2,160119 it is almost impossible to distinguish from the patch name only if you have applied a PSU or a BP.

But:
In Oracle Database 12c there’s a package available which is very useful to query plenty of information about patches from within the database: DBMS_QOPATCH.

Here are a few helpful examples which I created by checking in our DBaaS Cloud database.

Which patches have been applied (or rolled back)?

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 20415564
        Action : APPLY
        Action

Grid Infrastructure Management Repository (GIMR) – Datapatch?

Posted on August 5, 2015 by Mike.Dietrich ASM / RAC / GI Patch Recommendation 0

I have blogged about the Grid Infrastructure Management Repository (GIMR) a while back:

Grid Infrastructure Management Repository (GIMR) database now mandatory in Oracle GI 12.1.0.2

And Markus Michalewicz, our Director of Product Management, Oracle Real Application Clusters (RAC), has published a very interesting and helpful insight article about GIMR on July 30, 2015. Read it here:
https://www.linkedin.com/pulse/how-handle-oracle-gimr-markus-michalewicz

Since Oracle Database 12.1.0.2 the GIMR database will be created by default – and it is a single tenant database having a CDB$ROOT and one active PDB.

Recently the question came up if – in the likely event of applying a PSU …

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

Posted on July 20, 2015 by Mike.Dietrich DBUA Patch Recommendation 33

For clarification:
The following blog post applies to upgrades to Oracle 12.1 done by DBUA only whenever a SPU/PSU/BP is installed into the 12.1 home prior to the upgrade (which I’d highly recommend as patching before upgrade saves you headache after upgrade!).

Update [Dec 10, 2015]:
The required patch is included in 12.1.0.2BP13 but not 12.1.0.2.PSU5 (Oct 2015) – it will be included in the 12.1.0.2.PSU6 (Jan 2016) and all following PSUs.

Update:
For Oracle Database 12.2 and newer please see:
https://mikedietrichde.com/2017/05/25/dbca-execute-datapatch-oracle-database-12-2/

Two customers independently reported last week that they have doubts on DBUA’s ability to apply the required SQL …

Webcast for ISVs/Partners on Apr 21, 2015, 3pm CET
Why Upgrade to Oracle Database 12c?

Posted on April 16, 2015 by Mike.Dietrich Workshop 0

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 for ISVs and you will learn:

Why you need to upgrade to Oracle Database 12.1.0.2
How to ensure that your applications are ready for Oracle Database 12c
How to evaluate and test all enhancements of the upgrade process plus other new features
Best practices to upgrade and migrate successfully

Grid Infrastructure PSU Jan 2015 – Am I too intolerant?

Posted on February 18, 2015 by Mike.Dietrich ASM / RAC / GI Patch Recommendation 6

Germans are not only known for being precise and timely – but sometimes also for being too direct. Well, Roy could tell you stories … and I always honor his politeness 🙂

Enough about stereotypes. I work with a customer at the moment on their 12c upgrades. And I did recommend the most recent PSU (Patch Set Updates) for their Grid Infrastructure environments running Oracle Restart. Same of course for the database homes but this blog post will just name some findings I’ve had the other night when trying to apply the January 2015 GI PSU to my …

How to migrate to Unified Auditing?

Posted on February 3, 2015 by Mike.Dietrich Auditing 11

Lock

What is Unified Auditing and is it on by default?

Unified Auditing is the new auditing facility since Oracle Database 12c. But the “old” auditing is still working. And there are a few things to mention if you’d like to make the right choice. I have written some things about it a while ago but as I discovered yesterday my previous blog post (https://blogs.oracle.com/UPGRADE/entry/unified_auditing_is_it_on) doesn’t satisfy all my needs.

The initial motivation to move towards the new Unified Audit trail is audit performance. The audit records will be written into the read-only table AUDSYS in SYSAUX tablespace. …

Happy New Year, Complete with New Upgrade Workshops!

Posted on December 30, 2014 by Roy.Swonger Workshop 4

Oracle Database 12c Upgrade Workshop banner

With the New Year arriving in a couple of days, it is a good time to flip the page on the calendar and make plans to attend an Oracle Database Upgrade workshop! The first two workshops will be:

Denver on Wednesday, January 7 (register here)

Chicago on Thursday, January 8 (register here)

I hope to see you (and you…and you too!) at one of these events next week!…

New AQ Background Architecture in Oracle 12c

Posted on September 22, 2014 by Mike.Dietrich Oracle Database 12c 0

Advanced Queing

I know a some really high profile customers making heavy use of Oracle Advanced Queuing. Now in Oracle Database 12c there’s a change in the background architecture of AQ. The doc describes it more clearly than I ever could:

New AQ 3-tier-design of AQ’s background architecture

As far as I can see there shouldn’t be any backwards compatibility issues. But (as always) I’d be happy to hear, read and learn about your experiences 🙂

And see a list of changes, additions, enhancements and new features of AQ here:

New AQ Features in Oracle Database 12.1.0.2
- JMS Streaming

Oracle Database Documentation – From Past to Present

Posted on September 16, 2014 by Mike.Dietrich General 1

Yesterday one of my colleagues, well known Data Guard Guru Larry “Murphy” Carpenter, sent out an internal email with links to older and recent Oracle Database Documentation. Reason why you should use the below links: Some sites in the www have stored copies of the doc – but some may represent not the most recent state or may not be complete. So stay with the official Oracle docs only:

Oracle 8.0.6
http://www.oracle.com/technetwork/documentation/oracle8-090636.html
Oracle 8.1.7
http://www.oracle.com/pls/tahiti/homepage
Oracle 9.0.1
http://www.oracle.com/pls/db901/homepage
Oracle 9.2.0
http://www.oracle.com/pls/db92/homepage
Oracle Database 10.1.0
http://docs.oracle.com/cd/B12037_01/index.htm
Oracle Database 10.2.0
http://www.oracle.com/pls/db102/homepage
Oracle Database 11.1.0
http://docs.oracle.com/cd/B28359_01/index.htm
Oracle Database 11.2.0
http://docs.oracle.com/cd/E11882_01/index.htm
Oracle

Unified Auditing – is it ON or OFF in Oracle 12c?

Posted on September 2, 2014 by Mike.Dietrich Auditing 21

Don’t trust our slides – only believe what you’ve verified by yourself 😉

Actually one of our slides gives a parameter recommendation to set AUDIT_TRAIL since Oracle 11g explicitly to the value you want as otherwise it may switch to “DB” and you may not be aware of it. In conjunction with this setting we explain the new Oracle Database 12c feature Unified Auditing – which is not linked into the kernel and therefore should be off.

Should be … well … thanks to Marco Patzwahl who asked me why he still has over 100 audit records in V$UNIFIED_AUDIT_TRAIL? …

Automatic Maintenance Jobs in every PDB? New SPM Evolve Advisor Task in Oracle 12.1.0.2

Posted on August 22, 2014 by Mike.Dietrich Single-/Multitenant 8

A customer checking out our slides from the OTN Tour in August 2014 asked me a finicky question the other day:

“According to the documentation the Automatic SQL Tuning Advisor maintenance task gets executed only within the CDB$ROOT, but not within each PDB – but the slides are not clear here. So what is the truth?”

Ok, that’s good question. In my understanding all tasks will get executed within each PDB – that’s why we recommend (based on experience) to break up the default maintenance windows when using Oracle Multitenant. Otherwise all PDBs will have the same maintenance windows, and …

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

Posted on August 14, 2014 by Mike.Dietrich Flaws and Pitfalls RMAN 26

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 …

RMAN Catalog Upgrade to Oracle 12.1.0.2

Posted on August 11, 2014 by Mike.Dietrich Flaws and Pitfalls RMAN 15

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.

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

Upgrade PDBs – Everything At Once (Full CDB Upgrade)

Posted on August 6, 2014 by Mike.Dietrich Single-/Multitenant 18

As referred to it before there are two techniques to upgrade an Oracle Multitenant environment:

In this post I will explain the method of “Everything At Once” and describe all the steps. The benefit of this approach is simplicity and ease of maintenance. In an upgrade workshop in Melbourne earlier this year a DBA from Germany came by in one of the breaks explaining that he takes care on over 100 developer databases – and it would ease his life a lot if he could …

New version of the UPGRADE 12c SLIDE DECK available

Posted on August 5, 2014 by Mike.Dietrich Slides 3

Oh … it took a while … but we’ve tried to include a bit of the new Oracle Database 12.1.0.2 stuff into the slides. And we had to refresh them to the new template which looks nice but is a bit strange to handle 😉

So please find the new revised slide deck about:
Upgrade, Migrate & Consolidate to Oracle Database 12c

ready for download.

-Roy & Mike…

New (some undocumented) Parameters in Oracle 12.1.0.2

Posted on August 1, 2014 by Mike.Dietrich Oracle Database 12c Parameters 0

Every release offers some surprises – even to myself 😉

Right now Roy and I are in the final steps to refresh our big slide deck to the new layout, but more important, to have Oracle 12.1.0.2 information included as well (were necessary). So I did my usual “compare parameters” query between releases – getting unusual surprises this time.

This is the list of new parameters introduced with the patch set Oracle Database 12.1.0.2. Where applicable I have added the link to the doc.

But as you may recognize not all of them are explained in the doc 😉

DBFIPS_140

Why “We’ll wait for the 2nd release!” is a misconception …

Posted on July 24, 2014 by Mike.Dietrich Oracle Database 12.2.0.1 Oracle Database 12c 23

Oh, how often have I heard this phrase:

“We’ll wait for the second release!”

And sometimes it makes me really anxious and angry at the same time when I hear that.

Anxious because it means that somebody has no strategy for the database upgrades/migrations and is just postponing necessary tasks to sometime in the future. Easy deal but not very clever.

Angry because this is a way of thinking from the 90s/00s when Oracle had this “10.1”, then later “10.2” strategy in database releases. But we are in 2014 now. And things have changed. Changed a lot in fact.…

Oracle 12c

Drop public synonym XMLCONCAT prior to upgrade

What is AUDSYS meant for?

Where it started

Björn Bolltoft’s Blog: Oracle Database Managebility

How does an upgrade with Oracle Multitenant work?

Upgrading the PDB$SEED takes always longer?

Can you apply Bundle Patches (BP) to Standard Edition (SE2) databases?

Why was this sentence added?

Which patches have been applied (or rolled back)?

What is Unified Auditing and is it on by default?

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Recent Posts

Follow me on Twitter