Wednesday Dec 16, 2015

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 container database and finally upgrade it
  • And of course you can play around with the entire environment
    .

What we've did for your convenience? 

  • Switched to a new PDF viewer which allows better copy&paste - see the instructions document.
    Unfortunately we are not allowed to distribute the relatively old version of Adobe PDF Reader for Linux. 
  • Applied the most recent PSUs to the 11.2.0.4 and 12.1.0.2 homes
  • Fixed some minor things
  • Updated the HOL Instructions
  • Added an "aritificial" environment variable $OH12 to shorten typing
  • And some other tiny little things ...
  • And the lab is packaged with Oracle VBox 5.0.10 including the matching VBox Extensions which caused us trouble with the previous lab environment which was settled on Vbox 4.3 - so we hope that now all will go well.
    .

Where can you download the lab and the instructions?

All credits to Roy!!!

Thanks
--Mike

Wednesday Dec 09, 2015

UKOUG Tech 2015 - Summary & Slides

My first UKOUG Conference is over.

Actually it was over yesterday already for me as I had to head back to Germany. I would have loved to stay a bit longer and pick more of the many excellent presentations. I visited a few - and all of them were great. It was a tough choice as there were too many interesting things going on at the same time. Only finding the right room was sometimes a bit of a challenge ;-)

And I would like to have met more and talked to more people. But time was very limited. I didn't see anything from Birmingham except the foot path from the hotel to the venue and return. Plus an excellent Indian restaurant (thanks again to the folks of Pythian for inviting me - that was a wonderful dinner).

And of course thanks to everybody who attended my 3 talks.

As promised here are the slides to download:

Finally a very special Thank You to the organizers of the UKOUG 2015 Conference - it was an excellent event!

Hope to be back again in 2016!

--Mike

Tuesday Dec 08, 2015

MOS Note:884522.1 - New preupgrd.sql available

Whenever you attempt to upgrade an Oracle database to Oracle Database 12.1.0.2 please get the most recent version of preupgrd.sql, the preupgrade check script, (including the utluppkg.sql package) from MOS Note:884522.1 first.

MOS Note 884522.1
How to Download and Run Oracle's Database Pre-Upgrade Utility

preupgrd.sql

The new version has many improvements over the previous one - and it is more than 2 years "younger" then the one you'll get with a standard installation of Oracle Database 12.1.0.2. It will give you also good advice when you are planning a migration. 

--Mike

Friday Dec 04, 2015

Switch on/off + Remove - Journey to the Oracle Cloud - II

DBaaS Oracle Cloud

What happened so far on my Journey to the Cloud?

Day 2 of my Cloud exploration journey.

In my blog posts about my DBaaS Cloud journey I won't explain a lot about how to login to the Oracle Cloud or how to setup a simple database as others have done this already including the SSH key topic:

But let me show you some findings I've had when logging in via SSH to my DBaaS environment. In the following article I will describe how to:

  • Switch off Flashback Database
  • Clean up expired Backups and Archivelogs
  • Clean up traces and set the retention policy
  • Remove APEX from the CDB$ROOT
.

Flashback Database is ON by default

After playing around with my DBaaS environment for a few days all got stuck. The database hung. A quick look into the alert.log revealed the simple reason: my database had no space anymore on the Flash Recovery Area mount point. "Why that?" I did ask myself.

Well,

SQL> select FLASHBACK_ON from V$DATABASE;
YES

told me the answer. By default FLASHBACK DATABASE is ON. Great idea but for playground-mode you may or may not switch it OFF. Furthermore in my (developers) environment the logs will be written to /u03/app/oracle/flash_recovery_area/flashback (DB_RECOVERY_FILE_DEST) - and DB_RECOVERY_FILE_DEST_SIZE is defined at only 7.5GB. That got consumed quickly.  

You may switch it OFF if you don't need FLASHBACK DATABASE at the moment. Or check at least increase the DB_RECOVERY_FILE_DEST_SIZE.

SQL> alter database flashback off; 
SQL> exit
$  rm /u03/app/oracle/flash_recovery_area/flashback/*

And just for the records:
You don't need to have FLASHBACK DATABASE switched ON as a feature in order to use GUARANTEED RESTORE points (which is one of the best things - I used it almost daily before I "destroy" something in my databases).

Quick check:

SQL> select FLASHBACK_ON from V$DATABASE;
NO

SQL> create restore point DESTROY_ME guarantee flashback database;

If you'd check now:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

Don't forget to drop your GUARANTEED RESTORE POINT later on. 

SQL> drop restore point DESTROY_ME; 

.

Clean up your expired BACKUPS and ARCHIVE LOGS 

Every database should be run in archivelog mode - no doubt. But if you are short on disk space for the archive logs you may need to clean up from time to time. Furthermore archivelog mode is necessary in order to support certain actions in Oracle Multitenant.

Keep in mind:
Backups and Archivelogs don't clean up themselves.

Clean up expired backups:

$ rman target /
RMAN> crosscheck backup;
RMAN> delete noprompt obsolete;
RMAN> delete expired backup;

Clean up archivelogs: 

$ rman target /
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;


Clean up Trace and Log Files and set the Retention

The default retention for traces has been set to standard values in the cloud as well. You may change this to get a more frequent purging of traces. The interface to tweak settings is ADRCI. Find a nice ADRCI Survival Guide for DBAs here written by my colleague Uwe Hesse, 

adrci> show control

To solve DIA-48448 you'll have to set the correct diag directory you want to look at:

adrci> show homes
ADR Homes:
diag/rdbms/mike/MIKE
diag/tnslsnr/slc01qnv/listener
diag/tnslsnr/MIKEVM/listener

adrci> set home diag/rdbms/mike/MIKE

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/mike/MIKE:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
4162796853           720                  8760 2015-09-17 13:51:04.699486 +00:00        2015-11-30 17:53:33.062134 +00:00                                                 1                    2                    82                   1                    2015-09-17 13:51:04.699486 +00:00
1 rows fetched

This means SHORTP_POLICY and LONGP_POLICY are set to 30 days and 365 days (~1 year). 

I'd usually change it (that is pretty up to your personal preferences) and purge whatever is older than a few days for a start.

adrci> set control (SHORTP_POLICY=96, LONGP_POLICY=720)

switching the short term policy to 4 days, the long term policy (alert.log, incidents) to 30 days. And finally cleaning up also older traces for a fresh start:

adrci> purge -age 24

This tool a while (~ 10 minutes) in my environment.
.

Remove Oracle Application Express (APEX) from the CDB$ROOT

As I wrote a few weeks ago Oracle APEX is a fantastic - and unfortunately still the most undervalued - Oracle tool. The only real treatment you should do to your Cloud database first is removing APEX from the CDB$ROOT before either plugging something in or creating pluggable database. If you have created pluggable databases (PDBs) already please make sure that APEX is not used in any of them as removing APEX from the CDB$ROOT will remove it from all existing PDBs as well.

See: https://blogs.oracle.com/UPGRADE/entry/apex_in_pdb_does_not 
on Why and How to remove Oracle APEX from the CDB$ROOT.

In short:

  1. Make sure to change into the local $ORACLE_HOME/apex directory first before starting SQL*Plus:
    cd $ORACLE_HOME/apex

  2. Connect to your CDB$ROOT:
    sqlplus / as sysdba

  3. Run the "Remove APEX from the CDB$ROOT" script:
    SQL> @apxremov_con.sql

  4. Recompile to get rid of any invalid objects left over by the removal script:
    SQL> @?/rdbms/admin/utlrp.sql

That sounds simple, doesn't it?

Well, but my journey ends here for today as I'm getting 28 invalid objects (which don't compile) afterwards - and plenty of nice ORA-600 errors in the alert.log looking fantastically similar to Data Dictionary corruptions. I think my Part 3 of the journey will be a patching cycle. 

ORA-00600: internal error code, arguments: [ktsircinfo_num1], [1], [4], [71210]
ORA-00600: internal error code, arguments: [6711], [4228381], [1], [4228380], [3
]

A look into the list of INVALID objects give me this result - from the Cloud documentation I see that C##PDBMGR is a self-provisioning user env:

The Oracle Pluggable Database Self-Service Provisioning application provides an interface to Oracle Database 12c Multitenant option and allows for the provisioning of Pluggable Databases (PDBs). You can perform PDB operations including create, clone, open/close, plug/unplug, and drop. Additionally, you can grant others access to the application, giving them rights to create and manage their own PDBs within the quota limits you set.

So maybe it is not a good idea to remove APEX from the CDB$ROOT in our Oracle DBaaS Cloud environment? I will find out next week ...

SQL> column object_type heading "Type" Format a15
SQL> column object_name heading "Name" format a30
SQL> column owner heading "Owner" format a20
SQL> set pagesize 100
SQL> select owner, object_type, object_name from dba_objects where status='INVALID';

Owner                Type            Name
-------------------- --------------- ------------------------------
SYS                  PACKAGE BODY    WWV_DBMS_SQL
FLOWS_FILES          TRIGGER         WWV_BIU_FLOW_FILE_OBJECTS
APEX_040200          TRIGGER         APEX$_WS_ROWS_T1
C##DBAAS_MONITOR     FUNCTION        DBM_IS_CDB
C##PDBMGR            PACKAGE BODY    PDBSS_PDB_MGR
C##PDBMGR            TRIGGER         PDBSS_APPLICATION_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_ERROR_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_PREFERENCES_BIU
C##PDBMGR            TRIGGER         PDBSS_RESERVED_DB_NAMES_BIU
C##PDBMGR            TRIGGER         PDBSS_DB_SIZES_BIU
C##PDBMGR            TRIGGER         PDBSS_RESOURCE_PLANS_BIU
C##PDBMGR            TRIGGER         PDBSS_USERS_BIU
C##PDBMGR            TRIGGER         PDBSS_USER_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_ACCESS_REQUESTS_BIU
C##PDBMGR            TRIGGER         PDBSS_DATABASES_BIU
C##PDBMGR            TRIGGER         PDBSS_DATABASE_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_DATABASE_ADMINS_BIU
C##PDBMGR            TRIGGER         BIU_PDBSS_EMAIL_TEMP_TYPES
C##PDBMGR            TRIGGER         BIU_PDBSS_EMAIL_TEMPLATES
C##PDBMGR            PACKAGE         PDBSS_FILTER_FW
C##PDBMGR            PACKAGE         PDBSS_FW
C##PDBMGR            TRIGGER         PDBSS_HISTORY_BIU
C##PDBMGR            TRIGGER         PDBSS_NOTIF_BIU
C##PDBMGR            TRIGGER         PDBSS_TZ_PREF_BIU
C##PDBMGR            PACKAGE BODY    PDBSS
C##PDBMGR            PACKAGE BODY    PDBSS_FILTER_FW
C##PDBMGR            PACKAGE BODY    PDBSS_FW
C##PDBMGR            PACKAGE BODY    PDBSS_EMAIL_API

28 rows selected.

I certainly can drop the user C##PDBMGR - but removing the Database Monitor user C##DBAAS_MONITOR may cause some trouble as the DBaaS Monitor won't work anymore. And furthermore still three APEX objects remain INVALID.

I'll update you as soon as I have my environment patched and refreshed sometime next week after UKOUG Conference.

--Mike

Thursday Dec 03, 2015

Push a Button - Journey to the Oracle Cloud - I

DBaaS Oracle Cloud

What happened so far on my Journey to the Cloud?

Isn't that a great marketing picture/poster?

But the real question is:

  • How do you move a database to the Oracle Cloud?
  • Is it easy or complicated?
  • What is your benefit?
  • Which techniques work, which don't?
  • And what should you switch on/off or tweak?

I'm not (yet) a cloud expert. But my goal for upcoming posts in the next weeks will be to show you my naive approach of moving databases to the Oracle Cloud and get some true benefit from it.

--Mike

Wednesday Dec 02, 2015

What or who is JOX? And what happens if ...

Are you worried because of the title of this blog post? Don't worry ... it will be about the Oracle database and not about miraculous additives.

Months back I wrote about how to remove options from the Oracle database's kernel by either using chopt or linking options off:

Recently a very interesting case was logged in relation to a bug:
A customer had linked off the JOX option from the database kernel.
.

What is JOX? 

Well, do a quick search for "JOX" in the Oracle Documentation and you'll get "zero results" (or you may get results in the 8.1 and 9.0 documentation which does not help here). So our documentation doesn't want to tell you further details on "JOX". But once you know that this is the acronym for the JavaVM JIT Compiler then we'll get more results leading directly to this spfile parameter:

The default in my environments is always TRUE. It enables or disables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine (OracleJVM) environment.

And from here a link guides us into the Java Documentation explaining a bit more details about the JIT in case you are interested. 
.

Why am I writing this?

In theory you can unlink the JOX option - and compile oracle without the JIT:

$ cd $ORACLE_HOME/rdbms/lib
$ /usr/bin/
make -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/ins_rdbms.mk jox_off ioracle

But let me add two things

  • We recently had a bug just happening because JOX was unlinked - all our tests have JOX linked in
  • As you can control the availability of JOX via the above initialization parameter I don't see a deeper reason to unlink JOX
  • And based on the feedback of Marcelo Ochoa (thanks Marcelo!!!) you'll encounter a performance dip in some components such as interMedia, XMLDB etc as code will now be interpreted, and not JIT compiled.
  • Furthermore based on the feedback of my former colleague, Norbert Debes, unlinking JOX may cause also trouble with the upgrade to Oracle Database 12c
My conclusion: There's no real reason to take JOX out of the RDBMS kernel. Please leave it linked in.


How do you find out if JOX is linked in?

You can't query the database for JOX as it is not mentioned in V$OPTION or DBA_REGISTRY. But you can find it out easily on the OS level: 

$ cd $ORACLE_HOME/rdbms/lib
$ ar -t libknlopt.a | grep -c jox.o

If the result is "1" it means "JOX installed", and "0"  means it is not installed.

Credits to Byron Motta from our Database Upgrade team! 

Further information about JIT?

--Mike

Tuesday Dec 01, 2015

UKOUG Tech 2015 - 3 Talks - Upgrades & Consolidation

UKOUG 2015- I'ma  aSpeaker

UKOUG Tech 2015 Conference will begin on Monday, Dec 7, 2015. But there will be some activities on Sunday as well.  

It will be my first time at UKOUG's conference. It will happen in Birmingham/UK in the ICC.

Looking forward to meet great customers, experts and colleagues.

I'm happy to be invited to deliver 3 talks:

Hope to meet you there!

--Mike

Monday Nov 30, 2015

VBox 5.0.10 crash issues with our Hands-On-Lab

Milano - Nov 2015 (c) Mike Dietrich

I've ran two Hands-On-Workshops with customers and partners in Italy last week in Milano where we used our well known and thousands-of-times proven Hand-On-Lab environment

But this time some people failed while running the lab with random corruptions either shutting down the entire VM while running - or displaying file corruptions in the spfile - or other issues.

The common thing in all cases: People had VBox 5.0.10 downloaded and installed right before the workshop.

Of course they've did it - as I'm tempted too since weeks. Every time I start VBox on my PC Oracle Virtual Box asks me: 

Even though the screenshot is German you know what it offers me:
Download and Install Virtual Box 5.0.10.

Actually the current issue reminds me a lot on what I have experienced in 2014 in an Upgrade Hands-On Workshop in Vienna, Austria. 20 Oracle partners came together for two days for a Hands-On Upgrade/Migrate/Consolidate training. And 6 or 7 had random issues with their Virtual Box images. Corruptions. Failing upgrades at random phases. No patterns.

Only until somebody figured out via a Google search that at the same time other people started reporting similar behavior with their own VBox images using the brand new version of Virtual Box. It turned out that this newest version of Oracle Virtual Box 4.3 (I think it was 26) had exactly such issues. Everybody else in our room - including myself - running a version a few weeks older had no issues at all.

When we exchanged the affected installations the next morning replacing it (if I remember correctly: 4.3.24) all went fine for the rest of the workshop.

I won't say that VBox 5.0.10 is bad as I lack evidence, reproducible test cases, bugs. 

But I follow other people's Twitter and Facebook messages. And it seems to be that the PERL problem I did report a few days back:

is not he only issue with VBox images build in version 4 - and now running (more or less) on VBox 5.0.10.
.

Please see also:

--Mike 

Monday Nov 23, 2015

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 host ACL will be created implicitly. If the host ACL already exists, the ACE will be appended to the existing ACL.

(both paragraphs taken from MOS Note: 2078710.1)

What happens during/after upgrade?

  • Existing network ACLs will be migrated from XDB in Oracle 11g to Real Application Security in Oracle 12c.
    All privileges of the existing ACLs will be preserved
  • Existing ACLs will be renamed
  • Mapping between the old / new names is reflected in DBA_ACL_NAME_MAP.

    .

Issues before/during Database Upgrade?

First of all the current preupgrd.sql does not warn you correctly if such ACLs exist. This fix gets added to the preupgrd.sql. But you'll need to download the most recent version from MOS Note 884522.1. The one from January 2015 does not have it yet. But this is addressed and will be implemented soon.

Here's an issue which happened to one of my very experienced colleagues from Oracle Consulting in an upgrade project:

"Customer had network ACLs defined and Privileges (resolve,connect) granted for several hosts to several DB users in 11.2.0.3.

With the first DB, we observed the ACL renaming as you described it, but, much worse: 4 out of 9 privileges granted were completely gone away after the upgrade performed by DBUA (to 12.1.0.2.4). We then were able to evaluate the missing privileges and re-grant them again. Warned by that, for the next databases to be upgraded, we copied all the 11.2.0.3 content of the DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES to helper tables in order to be able to restore lost privileges (which was a good idea, as in one of the databases, only 87 out of 240 formerly existing privileges survived the upgrade)." 

Solution?

Check for existing Network ACLs before the upgrade or get the most recent preupgrd.sql once it contains the check.

Preserve the existing network ACLs and privileges (DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES) in a intermediate staging table to have the possibility to restore them afterwards in case the automatic migration fails or does not happen.

If you encounter a situation where your Network ACLs don't get migrated correctly, disappear and/or don't exist in the mapping table DBA_ACL_NAME_MAP afterwards please open an SR and let Oracle Support check. There are known issues with mappings and migrations not done correctly (find some bugs below) so needs to be verified if you have hit a known issue or encountered a new one.

More Information?

--Mike

.

Thursday Nov 19, 2015

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 would like to write about. I came across an pitfall in the past days twice.

Think of having a local APEX (Application Express) application in a standalone database or already inside a PDB. You'd like to plug it into a CDB, regardless if this CDB is local or in the Oracle DBaaS Cloud. But your current APEX is on a lower version than the APEX inside the target CDB.

Let's assume you'd like to migrate this standalone database or PDB over into the cloud. But as soon as you plug it in - even though you'll have Oracle Database 12.1.0.2 in both sites locally and in the cloud, you can't open your PDB in read/write mode. It will open RESTRICTED only.

Then you'll check PDB_PLUG_IN_VIOLATIONS and get a plug in error:

"APEX mismatch: PDB installed version 4.2.5.00.08 CDB installed version 5.0.0.00.31" 

Reason is simply that an Oracle Database 12.1.0.2 standard deployment will contain APEX 4.2.0 whereas the Oracle Cloud deployment in this scenario uses the more recent APEX version 5.0 already.

Having APEX installed into the CDB$ROOT was not a great idea. Actually it will cause you a lot of trouble and does not make much sense in the idea of Multitenant concept.

Oracle APEX Multitenant

I see why it has been done this way as not having APEX in the CDB$ROOT would have meant not having it in the PDB$SEED either - and all further provisioned PDBs would not have had APEX deployed by default. In this case every PDB would have required a separate APEX installation - but then you'd get the freedom of different APEX versions within the same Multitenant environment. And way more freedom during unplug/plug. But I'll elaborate this further down below.

Different APEX versions in Multitenant

First of all check your current APEX version with this query:

SQL> select COMP_ID, VERSION, STATUS from CDB_REGISTRY where COMP_ID='APEX' order by CON_ID;

COMP_ID  VERSION      STATUS
-------- ------------ -------
APEX     4.2.5.00.08  VALID

Find out in which container APEX is currently installed into:

SQL> select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;

COMP_NAME                   VERSION   NAME      CON_ID
--------------------------- --------- --------- ------
Oracle Application Express  4.2.5.00  CDB$ROOT       1

Then choose the best solution for your environment to avoid trouble with APEX in a Multitenant environment.

Scenario

Migrate a PDB with containing an APEX 4.2 deployment into another CDB with APEX 5.0 already installed in the CDB$ROOT container (the standard deployment in the Oracle DBaaS Cloud).

Two Solutions

Basically I see two potential solutions:

  1. Remove APEX from the CDB$ROOT before plug in your PDB with a different APEX version
  2. Upgrade your APEX installation in the PDB after plugging it into the CDB to match the CDB's APEX version

Solution 1 - Remove APEX from the CDB$ROOT 

I'd prefer Solution 1 as this will give you way more freedom. It will even allow you to have different APEX versions in the same Multitenant environment in different PDBs. But you'll have to do this steps right after creation of the container database - or before you worked with APEX in any of your PDBs as the following steps will remove APEX from all your PDBs!!!! If you already have APEX applications inside one of your PDBs you must export them before the APEX removal. Keep in mind that the "application export" facility in APEX 4 does not carry your image files automatically (APEX 5 is smarter).

Remove APEX from CDB$ROOT will remove it from all PDBs as well

The Oracle Documentation describes the path pretty straight forward. 

  1. Make sure to change into the local $ORACLE_HOME/apex directory first before starting SQL*Plus as otherwise the removal process won't work and error out - I'd assume that path variables are not carried on correctly. 
    cd $ORACLE_HOME/apex

  2. Connect to your CDB$ROOT:
    sqlplus / as sysdba

  3. Run the "Remove APEX from the CDB$ROOT" script:
    SQL> @apxremov_con.sql

  4. Check afterwards if APEX has been removed - also check for invalid objects. If necessary recompile. 
    In my tests two objects were INVALID after the removal but could be easily fixed
    (this part is not mentioned in the documentation):

    SQL> select COMP_ID, STATUS from DBA_REGISTRY where COMP_ID='APEX';
    no rows selected

    SQL> select object_name, status from dba_objects where status='INVALID';

    OBJECT_NAME                      STATUS
    -------------------------------- -------------
    APEX_PKG_APP_INSTALL_LOG         INVALID
    APEX_SPATIAL                     INVALID

    SQL> @?/rdbms/admin/utlrp.sql

    SQL> select object_name, status from dba_objects where status='INVALID';
    no rows selected

  5. Once you have done this your PDB containing the APEX application should not give you APEX errors anymore upon plugin as there's no mismatch anymore between CDB$ROOT's and the PDB's APEX.

  6. Just in the case - independently of my example - you'd like to install APEX now locally into a PDB then you'll have to use the apexins.sql script:

    cd $ORACLE_HOME/apex
    sqlplus / as sysdba
    SQL> alter session set container=PDB1;
    SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/


    Be aware - this step may run a long time (in my VBox image it took over 22 minutes to complete).

    timing for: Complete Installation
    Elapsed: 00:22:16.29


    Check the existence of the component finally:

    SQL> select comp_id, status , con_id from cdb_registry where comp_id='APEX';

    COMP_ID   STATUS  CON_ID
    --------- ------- -------
    APEX      VALID         3

Solution 2 - Upgrade APEX in the PDB after plugin

Even though I'd prefer solution 1 there may be some cases where you'll have to deal with Solution 2 and upgrade APEX after plugin. One simple reason could be that somebody uses APEX already in another PDB and you can't export and preserve it for later import - then you better don't follow Solution 1 then as it will force you to test your backup/restore strategy concept soon after ... 

  1. Run apexins.sql (from the APEX 5.0 installation - $ORACLE_HOME/apex) only in this particular PDB after plugin. You'll have to use catcon.pl

  2. The documentation https://docs.oracle.com/cd/E59726_01/install.50/e39144/db_pluggable.htm#HTMIG29441
    may not be correct mentioning all parameters without the --p option. Jason Straub corrected it:

    host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apexins -c '<PDB_name>' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/

    whereas &OH_HOME represents the full path to the Oracle home.


More Scenarios 

If you look at some of the potential scenarios you may see when trying to move PDBs around including APEX applications it is pretty obvious why having APEX only locally inside each PDB is a wise move. It will give you way more flexibility, less trouble, actually less upgrades (and APEX upgrades can take a while) - and simply less headache.

Assume that my CDB1 and CDB2 below are at the same database version - having different database versions will require upgrade/downgrade tasks in addition. 

APEX installed commonly within CDB$ROOT

  • CDB1 has APEX 4.2 in CDB$ROOT
    CDB2 has APEX 5.0 in CDB$ROOT
    • Unplug a PDB from CDB1 and want to plug it into CDB2 but you'll get an error in PDB_PLUG_IN_VIOLATIONS:
      “APEX mismatch: PDB installed version 4.2.5.00.08 CDB installed version 5.0.0.00.31”
    • That is actually the simple case as you'd only have to upgrade APEX inside the PDB:
      host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apexins -c '<PDB_name>' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/

  • CDB1 has APEX 5.0 in CDB$ROOT
    CDB2 has APEX 4.2 in CDB$ROOT
    • You unplug a PDB from CDB1 and want to plug it into CDB2 - but you can't actually plug in.
    • As APEX can't be downgraded to 4.2 in your PDB you'll have to upgrade APEX in the CDB2 to 5.0 first
    • Then you can plugin your PDB into CDB2
  • CDB1 has APEX in CDB$ROOT
    CDB2 has NO APEX in CDB$ROOT
    • Unplug a PDB with an APEX application from CDB1 and want to plug it into CDB2- fails
    • You will need to install APEX into CDB2's CDB$ROOT before being able to plug in the PDB
      • And it has to be the same version of APEX as used in CDB1

APEX installed locally within the PDB only

  • CDB1 has NO APEX in CDB$ROOT
    CDB2 has APEX  in CDB$ROOT
    • Unplug a PDB from CDB1 (having APEX locally in the PDB only) and want to plug it into CDB2 - works if ...
      • the PDBs local APEX version match the CDB2's common APEX version
      • If they don't match ...
        • PDB has APEX 4.2
          CDB2 has APEX 5.0
          Works, but you will need to upgrade APEX in the PDB after plugin
        • PDB has APEX 5.0
          CDB2 has APEX 4.2
          Fails, and you'll have to upgrade CDB2s common APEX 4.2 to 5.0 first before being able to plugin.

  • CDB1 has NO APEX in CDB$ROOT
    CDB2 has NO APEX  in CDB$ROOT
    • That is actually the best case as you don't have to care.
      • The only thing you will need to be aware of is that you'll need to patch your ?/apex home in case that PDB has a higher APEX version already than the one we ship by default 

More Information

You can find more information via these links:

And Finally ...

I'd recommend to NEVER have APEX in any CDB$ROOT. I see the advantage of having it as a new PDB will always have APEX provisioned with it. That is convenient, no doubt. But honestly if you really need APEX in most or all of your PDBs then create your own PDB$MASTER and provision from this one instead of using the PDB$SEED. In all other cases it's by far easier to install APEX inside the PDB when you really need it. Even with the penalty that it requires some extra 10 or more minutes until it is ready to go.

Given also the fact that APEX upgrade may take a while as well (see this blog post: https://blogs.oracle.com/UPGRADE/entry/save_upgrade_downtime_upgrade_apex  ) there's another advantage which personally I consider way more important:

Having APEX locally in the PDB only offers you way more freedom and flexibility. All the complicated scenarios come up only when either the source has no APEX in the CDB$ROOT (APEX locally in the PDB) but the target has any version (most likely a different one) or the target has no APEX in the CDB$ROOT but the source had it. And even more fun, both CDBs have APEX in the CDB$ROOT but at different versions.

This can be totally avoided by NEVER having APEX installed in your CDB$ROOT.

Which means also for the Oracle Cloud DBaaS databases:
Remove APEX from the CDB$ROOT and install it into your PDB before really starting up doing APEX stuff inside. APEX is such cool piece of software - and it's free - and powerful - and easy!!!

And (thanks to Jason again for this hint) if you look into the APEX 5.0 Installation Guide you'll find this tip:

Oracle recommends removing Oracle Application Express from the root container database for the majority of use cases, except for hosting companies or installations where all pluggable databases (PDBs) utilize Oracle Application Express and they all need to run the exact same release and patch set of Oracle Application Express. To learn more, see "Installing Application Express into Different PDBs."


--Mike

Monday Nov 16, 2015

DOAG Conference 2015 - My Talks

Guess how I realize that it's just 4 weeks and a bit towards Christmas?

When DOAG (German Oracle User Group) Conference is happening in my hometown Nürnberg in mid November every year. And this year's DOAG may be my 14th DOAG Conference as far as I remember.

Really looking forward to is as it is THE SPOT to meet with many German customers, some of them good friends, many many familiar faces, the DOAG leaders who all do a fantastic job - and I'll have the chance to see a lot of great presentations and learn a lot from experts, customers and my dear colleagues.

Only real downside? I'll have to board a train tomorrow early in the morning to be at the conference center right in time as my two appearance as a speaker will happen tomorrow only: 

Looking forward to see you there!

--Mike

UPDATE: _rowsets_enabled in Oracle Database 12c

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 why one of the bugs had as potential workaround hash_join_enabled=false (and please don't use this as a w/a!!!).

Different Workarounds 

  • Set
    event = "10055 trace name context forever, level 2097152"
    in your spfile. This will disable rowsets only for the specific situation where the problem happens.

    An important comment from Angela if you intend to set this event via an ALTER SESSION command:
    "Note that if the event is set with an alter session, it will not take effect unless the query or queries are recompiled, such as by changing the query text (by adding spaces or comments) or by flushing the shared pool."

  • Set
    _rowsets_enable=false
    in your spfile. But this will switch off the entire feature, not only the particular situation where the problem happens.

  • Apply the fix for
    bug 22173980 WRONG RESULTS WHEN "_ROWSETS_ENABLED" = TRUE
    (as of Nov 16, 2015 in regression testing and not available yet) as soon as it is available.
    .

Update (Dec 2, 2015)

Thanks to Christian Ballweg from Optiz Consulting in Germany letting me know that the patch is available for download:

Bugs Resolved by This Patch
22173980 
WRONG RESULTS (NUMBER OF ROWS) WHEN "_ROWSETS_ENABLED" = TRUE

.

Thanks again to the Optimizer folks for their quick reaction!!!

--Mike
.

Thursday Nov 12, 2015

Oracle VirtualBox 5.0.x - Segmentation Fault in PERL

Please see also:

 


 

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 started with pid=21, OS id=9828
Starting background process MMNL
Tue Nov 10 20:39:47 2015
MMNL started with pid=22, OS id=9832
Stopping Emon pool
Tue Nov 10 20:39:47 2015
ALTER SYSTEM enable restricted session;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET resource_manager_plan= SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Resource Manager disabled during database migration
replication_dependency_tracking turned off (no async multimaster replication found)
AQ Processes can not start in restrict mode
Starting background process CJQ0
Tue Nov 10 20:39:47 2015
CJQ0 started with pid=27, OS id=9836
Completed: ALTER DATABASE OPEN MIGRATE 

We checked several other things - and then I came across this tweet by Martin Klier yesterday:

and started to search a bit

I have no 100% proof for the actual reason but several people seem to have issues with SEGMENTATION FAULTs in Oracle's PERL ($ORACLE_HOME/perl/bin/perl) when using Oracle VirtualBox 5.0.x - and according to VitualBox Forum that seems to happen with the most recent VBox 5.0.10 as well.

The "funny" thing is that all works perfectly well in VBox 4.3.x ...  

It reminds me a lot on the reoccuring VBox bug with my German keyboard not allowing me to type in the | (pipe) character which requires to press "ALT GR" +  "<" keys together.  

--Mike

.

Tuesday Nov 10, 2015

Switch off "_rowsets_enabled" in Oracle Database 12c

Please find a recent update here:

 


 

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 Jonathan's posting) because of the row sets feature in Oracle 12.1 allowing faster initial processing of query results.

You'll find this in the query information (marked in bold red):

Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "K"."KONTO_ID"[NUMBER,22]
   2 - "KWP"."WAEHRUNG"[VARCHAR2,20], "SV"."SONDERVERMOEGEN_ID"[NUMBER,22],
       "K"."KONTO_ID"[NUMBER,22]
   3 - "KP"."KONTO_FK"[NUMBER,22], "KWP"."WAEHRUNG"[VARCHAR2,20],
       "SV"."SONDERVERMOEGEN_ID"[NUMBER,22]
   4 - (#keys=1) "KP"."KONTO_FK"[NUMBER,22],
       "KP"."SONDERVERMOEGEN_FK"[NUMBER,22], "KWP"."WAEHRUNG"[VARCHAR2,20]
   5 - (rowset=256) "KP"."KONTOPOSITION_ID"[NUMBER,22],
       "KP"."SONDERVERMOEGEN_FK"[NUMBER,22], "KP"."KONTO_FK"[NUMBER,22]
   6 - (rowset=256) "KWP"."KONTOPOSITION_FK"[NUMBER,22],
       "KWP"."WAEHRUNG"[VARCHAR2,20]
   7 - "SV"."SONDERVERMOEGEN_ID"[NUMBER,22]
   8 - "K"."KONTO_ID"[NUMBER,22] 


Roy did some analysis yesterday as well (and now our Optimizer Support Experts look into this case to check whether any other wrong query result bugs are related to this topic). Because there are some open WRONG RESULTS bugs in this area, some of which were filed within the past week, we'd recommend to switch this feature off at the moment.  For instance these bugs are related to this misbehavior:

Recommendation - Updated (Nov 15, 2015)

Please read the update blog post here:


--Mike

Friday Nov 06, 2015

Oracle Database Release Status - MOS Note:742060.1

Almost every day I get at least one email by somebody asking things such as ...

  • What is the current status of Oracle 11.2.0.3? 
  • Is Oracle 11.2.0.4 Standard Edition One under Extended Support's treatment?
  • Do I get bug fixes for Oracle 12.1.0.1?
  • And is it really true that I don't get bug fixes for Oracle 11.2.0.3 anymore???

All valid questions - and all of them get answered within this reference note called:

Why am I writing this?

Simply because the Lifetime Support Policy Brochure only has now a link to a central MOS note added - but no explanatory text such as "Full Extended Support coverage will only apply to the terminal release". And the link is added in the right bottom corner underneath the table on (real) page 6 (the document's pagination doesn't count pages 1 and 2 as 1 and 2 for whatever reason). 

MOS Note:742060.1 has now an updated chart displaying clearly the Support Periods:

Oracle Release Chart

And a nice overview table clearly showing the Patching End Dates as well:

Oracle Patching End Dates for the Database

MOS Note:742060.1 is clearly THE REFERENCE in terms of questions such as:

  • How long will I get bug fixes for release X?
  • When is Premier Support ending for release Y?
  • Do I get Waived Extended Support for release Z?
.
--Mike
.

Monday Nov 02, 2015

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 Oracle 12.1.0.2 the MRP (Managed Recovery Process) on the physical standby database is failing with:

ORA-600 [kdBlkCheckError], [5]

Restoring the data file does not solve the issue.

This happens if the source table has OLTP Compression enabled, and this database has been upgraded to Oracle Database 12.1.0.2

This corruption on compressed tables will happen during media recovery while applying redo generated for those tables when the source database was running on the Oracle 11g software version.

This also applies to Oracle 12c upgraded physical standby databases when recovering redo that was generated on 11g.

The latter is actually the issue Don must have had encountered here.
.

Which versions are affected?

This happens only in Oracle Database 12.1.0.1 and Oracle Database 12.1.0.2. The issue is fixed in the MAIN code line in Oracle 12.2. You can detect tables with OLTP Compression with these queries:

select owner, table_name, compression, compress_for  
from   dba_tables
where  compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

select table_owner, table_name, compression, compress_for  
from   dba_tab_partitions
where  compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

select table_owner, table_name, compression, compress_for  
from   dba_tab_subpartitions
where  compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

Workaround/Solution

Uncompress the table would work - but is not a pleasant idea.

Install the fix for Bug 21923026 in the new Oracle 12c home BEFORE upgrading the database

Issue got filed as bug 21682261 but has been replaced by the patch for bug 21923026. It should be applied to Oracle 12.1.0.2 before upgrade. If you hit the issue on a physical standby then apply the patch to the standby's home first, then refresh the affected data file by RMAN with a copy of this file from production - and restart MRP again.

Patch 21923026 is available on top of a plain Oracle 12.1.0.2 installation but also on top of several PSUs and BPs. Please check the RELEASE selection of the patch download for bug 21923026
.

Further Information

  • Bug 21682261 
    ORA-600 [KDBLKCHECKERROR]..[6126] OR [6110] FOR COMPRESS BLOCK DURING RECOVERY
    (non published bug - therefore no link added)

--Mike


Friday Oct 30, 2015

OOW 2015 - Upgrade Hundreds and Thousands

Uhh .. OOW15 is finally over.

Lots of work - tons of great input - and so many excellent discussions with customers, users and Oracle ACE's.

Just in case you'd like to download our slides please find the in the Slides Download Center to your right.

Thanks again - and hope to see you again next year!

--Mike

 

Tuesday Oct 27, 2015

OOW 2015 - Upgrade and Migrate to Oracle 12c Talk - Live and Uncensored - Get the Slides

Thank You, Thank You, Thank You!!!

Roy and I were extremely happy yesterday as the room was full. Totally sold out. I've read on Twitter that some people wanted to go in and weren't allowed to join as the room capacity was reached.

If you want to get the few slides for our first talk:

And thanks again - you were an awesome audience! And we are looking forward to see you in one of our two other talks:.

  • Our 2nd talk will happen on Wednesday at 12:15pm (skip the unhealthy lunch).
    We'll contrast some nasty things with some very good and detailed customer examples included in it. Real world examples, no artificial lab exercises. Againuncensored (mostly <img src=" title=";-)" style="border: none;" /> ) and just from first hand experience.

    How to Upgrade Hundreds or Thousands of Databases in a Reasonable Amount of Time [CON8375]
    Mike Dietrich, Master Product Manager, Oracle
    Roy Swonger, Sr Director, Software Development, Oracle

    Wednesday, Oct 28, 12:15 p.m. | Moscone South—102
    .
    .
  • The 3rd talk of our group is the Data Pump  Performance Tips and Tricks talk delivered by Data Pump experts from Development.

    Deep Dive: More Oracle Data Pump Performance Tips and Tricks [CON8376]
    Dean Gagne, Consulting Member of Technical Staff, Oracle
    Jim Stenoish, Senior Director, Software Development, Oracle

    Thursday, Oct 29, 9:30 a.m. | Moscone South—305
    .
    .

--Mike

Monday Oct 26, 2015

OOW 2015 - if you've missed our Upgrade Hands On Lab

Ready to kick off with the first of our four Hands-On-Labs at OOW15 at the Nikko Hotel. Currently it's calm before the storm  ...

OOW 2015

But just in case you didn't grab a seat at one of the labs then please come to our talk today (Monday!) at 1:30pm in Moscone South 102 as we'll show you the core parts of the lab - and you'll be able to download it later on from the blog and run it by yourself.

Download the lab via this link:

--Mike

Oracle Database 12.2 - just if you weren't aware

News from the keynote on Sunday, Oct. 25, 2015 at OOW 2015:

Availability of Oracle Database 12.2 ...

So still a while to go ;-)

--Mike

Friday Oct 23, 2015

OOW 2015 - Upgrade and Migration Talks

Oracle Open World 2015
will kick off in a day and a bit.
And still some work to do ;-)

A few things I would like to mention:

  • Our four HOLs are all overbooked already with many people on the waiting lists. You can always come by and wait in line at the Nikko Hotel's Golden Gate lab room as some people don't show up. But no guarantee. What I would recommend to you in case you are interested in the lab but didn't get a seat:

    Come to our talk instead on Monday at 1:30pm as Roy and I will demonstrate LIVE and UNCENSORED parts of the lab. Then you'll download it from the blog (find it in the Slides Download Center) and try it out by yourself afterwards: Hands On Lab Upgrade, Migrate, Consolidate to 12c

    Upgrade and Migrate to Oracle Database 12c: Live and Uncensored! [CON6777]
    Mike Dietrich, Master Product Manager, Oracle
    Roy Swonger, Sr Director, Software Development, Oracle

    Monday, Oct 26, 1:30 p.m. | Moscone South—102
    .
    .
  • Our 2nd talk will happen on Wednesday at 12:15pm (skip the unhealthy lunch).
    We'll contrast some nasty things with some very good and detailed customer examples included in it. Real world examples, no artificial lab exercises. Again uncensored (mostly ;-) ) and just from first hand experience.

    How to Upgrade Hundreds or Thousands of Databases in a Reasonable Amount of Time [CON8375]
    Mike Dietrich, Master Product Manager, Oracle
    Roy Swonger, Sr Director, Software Development, Oracle

    Wednesday, Oct 28, 12:15 p.m. | Moscone South—102
    .
    .
  • The 3rd talk of our group is the Data Pump  Performance Tips and Tricks talk delivered by Data Pump experts from Development.

    Deep Dive: More Oracle Data Pump Performance Tips and Tricks [CON8376]
    Dean Gagne, Consulting Member of Technical Staff, Oracle
    Jim Stenoish, Senior Director, Software Development, Oracle

    Thursday, Oct 29, 9:30 a.m. | Moscone South—305
    .
    .
  • And the 4th talk I'd like to highlight is the Database Upgrade 12c - Oracle Support talk from Support Architect Roderick Manalac.

    Oracle Database 12c Upgrade: Tools and Best Practices from Oracle Support [CON8664]
    Roderick Manalac, Architect - Database Support, Oracle
    Thursday, Oct 29, 12:00 p.m. | Moscone South—305
    .
    .

Ship

CU soon :-)

--Mike

Thursday Oct 22, 2015

PSU (and CPU/SPU) October 2015 got released

October 21, 2015 - Oracle released the October 2015 SPU/CPU, PSU and BPs. 

See:

For Oracle Database 12.1.0.2 and Oracle Grid Infrastructure access the PSUs from here - if you have only a single instance database you can safely take the Combo patch as you'll get downtime anyways - but for customers running RAC you need to evaluate the OJVM component as this will incur downtime which the database-only patch does not require as it can be applied rolling.

Patch Set Updates

Document Description Rolling RAC Patch Download
Note:21555660.8 Oracle JavaVM Component 12.1.0.2.5 Database PSU (Oct 2015) (OJVM PSU) No Patch:21555660
Note:21520444.8 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 DB PSU (Oct 2015) Part Patch:21520444

No patch found at the moment [Mike]
Note:21359755.8 12.1.0.2.5 (Oct 2015) Database Patch Set Update (DB PSU) Yes Patch:21359755

Grid Infrastructure

Document Description Rolling RAC Patch Download
Note:21523260.8 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 GI PSU (Oct 2015) Part Patch:21523260
Note:21523234.8 12.1.0.2.5 (Oct 2015) Grid Infrastructure Patch Set Update (GI PSU) Yes Patch:21523234


And be aware to patch your DBaaS Cloud databases as well - this is not done automatically ;-)

--Mike

.

Tuesday Oct 20, 2015

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 undocumented then) in Oracle Database 12.1.0.2 to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and it's child cursors after the parent cursor has been obsoleted N times.

You can control this behavior by altering the value for:

alter system set "_kks_obsolete_dump_threshold" = N;

Possible value range is 0..8 whereas 0 means: switch the obsolete cursor dump off completely, and other values (N) defined the number of invalidations after which the cursor will be dumped.

The default in Oracle Database 12.1.0.2 is 1 meaning this will happen after every single invalidation

Just be aware that the underlying cursor sharing problem needs to be investigated - always.
If you have cursor sharing issues you may set this parameter higher therefore not every invalidation causes a dump, then investigate and solve the issue, and finally switch the parameter to 0 once the issue is taken care of. 

Please be aware that switching the parameter to 0 will lead to a lack of diagnostics information in case of cursor invalidations.

--Mike

Saturday Oct 17, 2015

Extended Support Fee for Oracle 11.2.0.4 waived until May 31, 2017 - Extended Support until Dec 2020

Friday, Oct 16, 2015, Oracle announced that the Extended Support for Oracle Database 11.2.0.4 will be waived until May 31, 2017. After this period of Waived Extendend Support, Extended Support for Oracle Database 11.2.0.4 will be offered until end of December 2020.

This information can be found here:

Something to mention:

  • There's no bug fixing support for Oracle Database 11.2.0.3 or below available anymore
  • If you'll compare the effort to upgrade to Oracle Database 11.2.0.4 to Oracle 12.1.0.2 you'll have to do the exact same amount of work. tests etc - no difference


    .
  • End of Premier Support means that Oracle will not offer fixes for new issues unless an extra-cost offer called Extended Support has been purchased. But you'll still get Sustaining Support until you'll stop working on the release. For a description what terms such as Premier Support, Extended Support and Sustaining Support mean please refer to the Lifetime Support Policy.
    Premier Support for Oracle Database 11.2 ended on Jan-31, 2015
    .
  • For Oracle Database 11.2.0.4 - the Terminal (final) Patch Set for Oracle Database 11.2 - Oracle will waive (no extra cost, no action required) Extended Support to every customer with a valid support contract until May 31, 2017.
    You can extend the Extended Support for Oracle Database 11.2.0.4 until 31-DEC-2020 - but then at the usual extra cost. 
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.3 Oracle has waived the Extended Support, but as Extended Support for Oracle Database 11.2.0.3 ended on 27-AUG-2015 no bug fixes are available (including PSUs, SPUs and BPs) anymore.
    There's no bug fixing support for Oracle Database 11.2.0.3 anymore.
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.2 there's no Extended Support offering available.
    There's no bug fixing support for Oracle Database 11.2.0.2 anymore.
    This information can be found in MOS Note:742060.1.
    ..
  • For Oracle Database 11.2.0.1 there's no Extended Support offering available.
    There's no bug fixing support for Oracle Database 11.2.0.1 anymore.
    This information can be found in MOS Note:742060.1.
    .
--Mike

Friday Oct 16, 2015

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 highlighting this issue to me. I really benefit from such emails as I can learn a lot from those - and furthermore distribute information to many other tech folks as well.

Actually Marcel updated me as a follow up to me recent blog post:

describing an issue which will require to apply a patch before upgrade if you are not on Oracle 11.2.0.4.2 or higher - and the below issue is related to this one but requires a patch AFTER upgrade, regardless what your source version was.

Issue

Marcel let me know about an issue they saw with their upgrade from Oracle 11.2.0.3 to Oracle 12.1.0.2 after the upgrade causing a core dump and potential table metadata corruption when you'll ADD a column to a table with a DEFAULT and ENABLE NOVALIDATE. This issue gets treated as bug 20880215 (ORA-7445 [QCSISCOLINFRO()+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE)
.

Test Case

Very simple test case done by Marcel (thanks again!) - I could verify it within 30 seconds. 

create table dummy(code varchar2(5), text varchar2(30));
insert into dummy(code, text) values ('CD1','Hello World');
commit;
select * from dummy;
alter table dummy ADD condition varchar2(3) DEFAULT 'YES' not null enable novalidate;
select * from dummy;
:

Result

First of all the client (in my case SQL*Plus) will disconnect with the meaningless ORA-3113. So lets have a look into the alert.log for more useful information:

Wed Oct 14 15:06:59 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDB0046, qcsIsColInFro()+358] [flags: 0x0, count: 1]
Errors in file /oradata/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_2424.trc  (incident=3433) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB0046] [Address not mapped to object] []
Incident details in: /oradata/diag/rdbms/cdb2/CDB2/incident/incdir_3433/CDB2_ora_2424_i3433.trc

Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

Wed Oct 14 15:07:08 2015
Dumping diagnostic data in directory=[cdmp_20151014150708], requested by (instance=1, osid=2424), summary=[incident=3433].

So we've got a core dump. And whatever we query now from the table involving the column "condition" we'll get the same error.
.

Solution

Apply the fix for bug 20880215 (ORA-7445 [QCSISCOLINFRO+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE). You may have to request the fix for your platform if it hasn't been done yet. 
.

Further Information

A request for inclusion into the next Bundle Patches had been filed as well.
.

--Mike 

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« June 2016
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
17
18
19
21
22
23
24
25
26
28
29
30
  
       
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers