Current Support Status for Oracle Database Releases

Yesterday a former colleague from the UK pointed me to a very old blog post about the fact that no patches will be produced for Oracle 11.2.0.3 anymore. Which is a true fact. But it reminded me that it may be time for an overview on the current support status for Oracle database releases as of October 2018. Current Support Status for Oracle Database Releases First of all, the single source of truth in case you’d like to look up support dates and patching periods is MOS Note:742060.1. Please always check this note first. Our Support Lifetime Policy brochure does…

How long can you provision database version X in the Cloud?

That’s a question I have gotten several times already: How long can you provision database version X in the Cloud? Meaning, is there a fixed date when you’ll be unable to provision an instance containing Oracle Database 12.1.0.2 for instance? How long can you provision database version X in the Cloud? Please find the updated blog post here: How long can you provision database version X in the Cloud? – NEW –Mike

Patching my databases with the July 2018 PSU, BP and RU

As usual I download the patch bundles  and apply them to our Hands-On Lab environment as quickly as possible. First of all for the simple reason that I don’t want to trap into issues which are fixed already. Second for the reason that I always tell you: YOU MUST PATCH! But if I’m saying this over and over again, then I can’t have my own environments unpatched. And at third, because I want to learn if anything is not working correctly or has changed (see below). Of course my tiny single instance environments are not comparable to your critical production…

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 Oracle…

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: Unified Auditing – is it ON or OFF in Oracle 12.1.0.2? Unified Auditing – is it ON or OFF…

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

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 find the…

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

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 Value Change in Oracle Database…

Unified Auditing – Performance Improvements in Oracle 12.1.0.2

Unified Auditing got introduced in Oracle Database 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…

GC Freelist Session Waits causing slowness and hangs

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 waits…

Parameter Recommendations for Oracle Database 12c – Part II

See also: Parameter Recommendations for Oracle Database 12c – Part I Time for a new round on Parameter Recommendations for Oracle Database 12.1.0.2. The focus of this blog post settles on very well known parameters with interesting behavior. This can be a behavior change or simply something we’d like to point out. And even if you still work on Oracle Database 11g some of the below recommendations may apply to your environment as well. Preface Again, 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…

Parameter Recommendations for Oracle Database 12c – Part I

See also: Parameter Recommendations for Oracle Database 12c – Part II A few weeks ago we’ve published some parameter recommendations including several underscores but based on an internal discussion (still ongoing) we decided to remove this entry and split up the tasks. The optimizer team will take over parts of it and I’ll post an update as soon as something is published. 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 verify the effect of any of those…

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

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

Please find all our articles about Oracle Database Standard Edition 12.1.0.2 SE2: Sept 17, 2015: SE2 – Some questions, some answers Sept 8, 2015: MOS Download for Oracle SE2 Sept 7, 2015: Support and Patches for Oracle 12.1.0.1 SE/SE1 Sept 3, 2015: Can you have Multitenant with Oracle SE2? Sept 1, 2015: Oracle Standard Edition SE2 available for download   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 Oracle Database 12.1.0.1…

Upgrade downtime credited to APEX

What do you think when you see this post-upgrade result? Oracle Database 12.1 Post-Upgrade Status Tool 08-07-2015 15:08:26 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.1.0.2.0 00:19:26 JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:10:52 Oracle Workspace Manager VALID 12.1.0.2.0 00:01:52 OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:34 OLAP Catalog OPTION OFF 11.2.0.3.0 00:00:00 Oracle OLAP API VALID 12.1.0.2.0 00:00:42 Oracle XDK VALID 12.1.0.2.0 00:01:07 Oracle Text VALID 12.1.0.2.0 00:01:36 Oracle XML Database VALID 12.1.0.2.0 00:03:55 Oracle Database Java Packages VALID 12.1.0.2.0 00:00:22 Oracle Multimedia VALID 12.1.0.2.0 00:03:57 Spatial UPGRADED 12.1.0.2.0 00:08:56 Oracle Application Express VALID 4.2.5.00.08…

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 LG00–LG99. Here is a list of new background processes in Oracle Database 12.1: MOS Note 1625912.1 – New Background Processes In 12c  For deep dive information about how…

Some Data Pump issues: DBMS_DATAPUMP Import via NETWORK_LINK fails + STATUS parameter giving bad performance

One of my dear Oracle ACS colleagues (Danke Thomas!) highlighted this issue to me as one of his lead customers hit this pitfall a week ago. . DBMS_DATAPUMP Import Over NETWORK_LINK fails with ORA-39126 / ORA-31600 Symptoms are: KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE] ORA-31600: invalid input value IN (‘VIEWS_AS_TABLES/TABLE_DATA’) for parameter VALUE in function SET_FILTER This can be cured with the patch for bug19501000 –  but this patch can conflict with:Bug 18793246  EXPDP slow showing base object lookup during datapump export causes full table scan per object and therefore may require a merge patch – patch 21253883 is the one to go with in…

New version of the BIG 12c SLIDE DECK available

I’m not on vacation right now. I’m just very busy traveling between customer onsite visits, customer meetings and workshops, internal and external workshops, testing sessions … and so on … A lot of stuff has been stacked up in my inbox regarding changes in Oracle 12c, changed behavior – and thanks for your inputs. I will blog on it as soon as I have understood the issue and the solution – so plenty of stuff should come in the next weeks 😉 Just end of last week Roy and I uploaded a new version of our BIG slide deck –…

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…

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 Now you start to wonder as your Catalog Database had been an Oracle Standard…

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.  Even though you may not have ever heard before about Virtual Private Catalogs you need…

New (some undocumented) Parameters in Oracle 12.1.0.2

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…

Oracle Database 12.1.0.2 is available!!!

Oracle Database 12.1.0.2 is available for download now. It is the most comprehensive patch set we’ve ever built. Look up the list of inclusions – and it includes the new marquee feature OracleDatabase In-Memory. Three things important to mention: It is a full release – no need to get Oracle Database 12.1.0.1 Even though it is a patch set it will be available on OTN and eDelivery – and Oracle Database 12.1.0.1 will be removed instantly For now it is an Enterprise Edition install only – SE and SE One may follow later. Please see MOS Note:1905806.1 for further details….

New interesting White Paper:
Oracle Multitenant Efficiency Study

There’s a new White Paper available interesting for those who’d like to learn a bit more about the efficiency and the overhead of Oracle Multitenant in a fairly large environment: http://www.oracle.com/technetwork/database/multitenant/learn-more/oraclemultitenantt5-8-final-2185108.pdf –Mike