Monday Jun 20, 2016

Minor Upgrade? Going from 11.2.0.1 to 11.2.0.4?

My Belgium friend Phillipe Fierens raised a great question on Twitter last week and dropped me an email again after having a discussion with his client:

For Phillipe and myself the answer is pretty clear and straight forward:

There is no minor upgrade anymore since every (patch set) release is a full release and since new parameters, parameter values, features and whatever appears even in patch sets. 

But the following discussion on Twitter with comments from all sides made me think about why people would honestly declare going for instance from 11.2.0.1 to 11.2.0.3 as a minor upgrade whereas going to 12.1.0.2 is seen as a major upgrade?

Let me summarize why I completely disagree - and actually Dom Giles nailed it:

  • Since Oracle Database 11.2.0.1 we deliver patch sets as a full release
  • A patch set can contain not only new parameters or parameter values but may occasionally also contain new features (typical examples in Oracle 11.2.0.4 are the new value for optimizer_dynamic_sampling=11 or the DBMS_REDACT package)
  • Therefore you will have to do exactly the same amount of testing, regardless of going to Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 - it is ZERO difference in the tests, the time, the effort, the manpower ...
    .

You don't believe me? Then please check MOS Note:1962125.1 (Oracle Database - Overview of Database Patch Delivery Methods). Scroll down a bit to Testing Recommendations By Patch Type and see the rightmost column of this table headlined "Patch Set Release":

I hope this clarifies it all.

There is no "minor" patch set upgrade anymore. Even though I would love to tell you that going from 11.2.0.3 to 11.2.0.4 is just a minor hop it will require exactly the same testing and evaluation work then going to Oracle Database 12.1.0.2.

But going to Oracle Database 12.1.0.2 will mean that you are under Premier Support until end of June 2018 - whereas Free Extended Support for Oracle Database 11.2.0.4 will end in May 2017.

--Mike

.

Wednesday May 04, 2016

Are BPs. PSUs and Proactive BPs cumulative?

Are Bundle Patches (BPs) and Patch Set Updates (PSUs) cumulative?

That is a question sounding trivial to many people but actually it does get asked quite often. And sometimes I forget to mention this during the workshops - and luckily usually somebody asks the question reminding me to explain it.

Yes, Bundle Patches and Patch Set Updates (and of course Proactive Bundle Patches and Critical/Security Patch Updates (CPUs/SPUs) are all cumulative.

You'll find this mentioned in the first paragraph of MOS Note: 854428.1 - Patch Set Updates for Oracle Products:


...

Interesting note on the side:
I would have expected this important piece of information in MOS Note:1962125.1 - Oracle Database - Overview of Database Patch Delivery Methods but I couldn't find it. So it's no wonder why people ask such a trivial question ... [irony!]  
.

Two simple examples:

  • You have the October 2015 PSU applied
  • You'd like to apply the April 2016 PSU on top
    • Then you don't need the January 2016 PSU as it is included in the April 2016 PSU already
      .
  • You never applied a Procative Bundle Patch
  • You'd like to apply the April 2016 Proactive Bundle because a guy recommended it on an Oracle blog - and actually MOS notes mention it as well as highly recommended
    • You don't need to apply anything beforehand.
      The April 2016 Proactive BP has all the fixes from all previous BPs included on top of Oracle Database 12.1.0.2.0
      .

Further Information?

You'll find recent related postings on this blog here: 

 --Mike
.

Tuesday May 03, 2016

Can I apply a BP on top of a PSU? Or vice versa?

This question was in my inbox this morning raised by a customer via a colleague. 

Our feeling said:
Yes, but you'll have to deinstall the PSU first before applying the BP.

Having a feeling is one thing, knowing the truth is better. And as I have so much fun by applying PSUs and BPs back and forth since two weeks I thought "let's give it a try". So here we go ...
.

Apply a BP on top of a PSU

This is my starting point - Oracle Database 12.1.0.2 with PSU October 2015, the last state in my VBox environment since I experiment with parameters and packages at the moment.

And this is the result when trying to apply the BP from April 2016 on top: 

[CDB2] oracle@localhost:/media/sf_CTEMP/22899531/22899531/22806133
$ opatch apply
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0.2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2016-05-03_10-26-37AM_1.log

Verifying environment and performing prerequisite checks...

Conflicts/Supersets for each patch are:

Sub-Patch : 20243804

        Bug Conflict with Sub-Patch 21359755
        Conflicting bugs are:
        18966843, 19468991, 19032777, 19587324

[..]


Following patches have conflicts: [   21359755   22806133 ]
Refer to My Oracle Support Note 1299688.1 for instructions on resolving patch conflicts.

UtilSession failed: Patch 21359755 is a composite patch which could not be rollback.

Log file location: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2016-05-03_10-26-37AM_1.log

OPatch failed with error code 73


Ok, our suspicion was correct.

There are a lot of conflicts - I have to remove the PSU first. And of course the recommended conflict check would have given me the same result. Furthermore I think I have read this a while back in one of the central notes about PSUs and BPs in MOS as well.
.

Removing the PSU from October 2015 first

Removing a PSU or BP is very simple and straight forward (and well described in the ReadMe.html coming coming with the patch).

[CDB2] oracle@localhost:/media/sf_CTEMP/21359755/21359755
$ opatch rollback -id 21359755
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/12.1.0.2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0

Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/21359755_May_03_2016_10_41_54/rollback2016-05-03_10-41-54AM_1.log

Patches will be rolled back in the following order:
   21359755   20831110   20299023   19769480
The following patch(es) will be rolled back: 21359755  20831110  20299023  19769480
Sub-patches of a composite series are being rolled back. The system will be returned to a state where all subpatches are rolled back.

[..]

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.1.0.2')

Is the local system ready for patching? [y|n]
y

User Responded with: Y
Rolling back patch 21359755...

[..]

RollbackSession removing interim patch '19769480' from inventory Log file location: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/21359755_May_03_2016_10_41_54/rollback2016-05-03_10-41-54AM_1.log OPatch succeeded.

Apply the BP from April 2016

I don't want to repeat myself as I wrote already about this positive experience a few days ago:

Oracle Database BP April 2016 applied successfully (Apr 21, 2016)
https://blogs.oracle.com/UPGRADE/entry/oracle_database_bp_april16_applied

Further Information?

You'll find recent related postings on this blog here:

--Mike

Tuesday Apr 26, 2016

MOS Note 1454618.1: Quick Reference to Database PSUs, CPUs, BPs and Patchsets

Sometimes my mouse arm gets tired by clicking myself through MOS notes just to download a specific PSU or BP - and as I experiment a lot with PSUs and BPs right now I clicked a lot in the past days and nights. 

Usually I'd start with either MOS Note:161818.1 - then click on the release link (e.g. 12.1.0.x) in the left-most column, then into the Availability and Known Issues not (e.g. MOS Note:1683799.1 for Oracle Database 12.1.0.2.0) and then select the most recent patch from the list of Current Recommended Patches.

Even though we all agree that you should be ideally always on the most recent BP (or at least PSU) there may be situations where you need to access an older PSU or BP or just the CPU.
So what if you need for instance the October 2015 PSU?
This is obviously not linked from the above note.

My usual click routine guides me via the Critical Patch Updates and Security Alerts page, from there via the particular release date (e.g. October 2015) to the Database Server Version (e.g. linked from here is MOS Note:2037108.1) and then to the patch number I'm searching for.

Just learned a few days ago by accident that there's a MOS Note which I have on my favorites since then:

containing all this information - even for 8.1.7.4 patches if you still need them ;-)

After listing the base release first followed by the available patch sets it offers you the links to the PSUs, CPUs and BPs - and if you are looking for the patches containing the OJVM you'll find them by scrolling down towards the end of the note as well in a separate section. 

*** Don't get puzzled by the note's title - it lists the Bundle Patches for Windows only inside, not the general link to all BPs. Myself and a colleague left already feedback for the note owner to add the BP links (or Proactive Bundle Patch links) as well *** 

MOS Note Patches PSUs CPUs SPUs BPs

In fact a very useful MOS Note.
.

--Mike

Thursday Apr 21, 2016

Oracle April 2016 PSU and Proactive BPs are there

Hurray, it's Patching Day!

Sounds a bit like D-Day ;-) But April 19, 2016 the most recent April PSUs (Patch Set Updates) and BPs (Bundle Patches) got released.

Find all the necessary information with the below links: 

The important change in the April PSU/BP release:
The database patch for "Engineered Systems and Database In-Memory 12.1.0.2" luckily got renamed into "Proactive Bundle Patch 12.1.0.2". That is not only a rebranding but it should express that we would like to encourage you to apply the Bundle Patches
instead of the PSUs. Simple reason is that the BPs will contain optimizer fixes. 

In the MOS Note: 2102148.1 (Patch Set Update and Critical Patch Update April 2016 Availability Document) you'll find a section 3.1.4 linking to the database patches

This is the recommended one for Oracle Database 12.1.0.2:

  • Database Proactive Bundle Patch 12.1.0.2.160419 (Apr2016) Patch 22899531,

But right now it is available for Linux-x86-64, zLinux and Intel Solaris only. Not sure when the others will get released. Please find links to the regular PSUs and other ports and releases such as 11.2.0.4 and Windows etc in the above MOS Note: 2102148.1.

This is the list of fixes included in this Bundle Patch:

And don't worry about the name - I found out yesterday that not all MOS Notes have adopted the new naming convention to rename "Bundle Patches for Engineeered Systems and DB In-Memory" which was very misleading anyway into the new "Proactive Bundle Patches" naming. This may take a few additional days I'd guess ...

I will download it right now and patch my HOL environment.

And as usual don't forget the most recent version of opatch (Patch 6880880).

opatch download MOS

.

--Mike 
.


Tuesday Feb 02, 2016

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

pflaster.jpgSince 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 Time : 24-JUN-2015 06:19:23
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_apply_ORCL_CDBRO
OT_2015Jun24_06_18_09.log
        Status : SUCCESS

Patch Id : 20299023
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description : Database Patch Set Update : 12.1.0.2.3 (20299023)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20299023/18703022/
                  20299023_apply_ORCL_CDBRO
OT_2015Jun24_06_18_11.log
        Status : SUCCESS

Patch Id : 20848415
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_apply_ORCL_CDBRO
OT_2015Jun24_06_18_15.log
        Status : SUCCESS

Patch Id : 20848415
        Action : ROLLBACK
        Action Time : 24-JUN-2015 06:52:31
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_rollback_ORCL_CD
BROOT_2015Jun24_06_52_29.log
        Status : SUCCESS

Patch Id : 20618595
        Action : APPLY
        Action Time : 24-JUN-2015 13:52:13
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_apply_ORCL_CDBRO
OT_2015Jun24_13_52_12.log
        Status : SUCCESS

Patch Id : 20618595
        Action : ROLLBACK
        Action Time : 24-JUN-2015 14:37:11
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_rollback_ORCL_CD
BROOT_2015Jun24_14_37_10.log
        Status : SUCCESS

Patch Id : 20415564
        Action : ROLLBACK
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_rollback_MIKEDB_
CDBROOT_2016Jan27_17_42_16.log
        Status : SUCCESS

Patch Id : 21555660
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/
                  21555660_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_17.log
        Status : SUCCESS

Patch Id : 21359755
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database Patch Set Update : 12.1.0.2.5 (21359755)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/
                  21359755_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_18.log
        Status : SUCCESS

Patch Id : 21962590
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21962590/19426224/
                  21962590_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_21.log
        Status : SUCCESS

PL/SQL procedure successfully completed.
.

Where's my home and inventory?

SQL> set pagesize 0

SQL> set long 1000000 

SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Home and Inventory
-------------------------------------------------------------

Oracle Home     : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory    
    : 
/u01/app/oraInventory


Has a specific patch been applied?

Lets check for the latest PSU. 

SQL> select xmltransform(dbms_qopatch.is_patch_installed('21359755'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;

Patch installed?
-------------------------------------------------------

Patch Information:
         21359755:   applied on 2015-10-22T21:48:17Z

.

What's tracked in my inventory?

The equivalent of opatch lsinventory -detail ...

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual; 

Oracle Querayable Patch Interface 1.0
----------------------------------------------------------------
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory
----------------------------------------------------------------

Installed Top-level Products (1):
                                    12.1.0.2.0
Installed Products ( 135)
                               ...

.

Additional Information and Patches

If you need more helpful examples you may check this excellent blog post by Simon Pane (Pythian):

And credits to Martin Berger for sending me this important information:

Just in case there are multiple DBs running from the same O_H, and someone      
queries dbms_qopatch.get_opatch_lsinventory automated from all DBs (as in       
automated monitoring/reporting scripts) I'd recommend Patch 20599273 -          
otherwise there might be strange XM errors due to race conditions. 

.

--Mike 

Wednesday Jan 20, 2016

Oracle January 2016 CPU PSU BP available now - BE AWARE OF CHANGES IN PATCH NUMBERING

Last night the PSUs and BPs for January 2016 have been made available for download on support.oracle.com.

Oracle Critical Patch Update Advisory - January 2016

http://www.oracle.com/technetwork/topics/security/cpujan2016-2367955.html 

It contains 248 security fixes across all products and platforms. And of course important non-security fixes - and that's why we recommend to apply the PSUs (or the BPs in case you are on Exadata or an Oracle In-Memory user) as soon as possible. 

Change in Patch Numbering

Please be aware that as of November 2015 there's been a change in patch numbering introduced which most of you may not be aware of. A database PSU was named 12.1.0.2.5 before (or I used to call it 12.1.0.2.PSU5 before to make clear that a PSU and not a BP has been applied). But the new notation will change the 5th digit to a 6-digit-number to include the date. See MOS Note:2061926.1 for details.

Example:

  • Before: Oracle Database 12c PSU October 2015 ... 12.1.0.2.5
  • Now: Oracle Database 12c PSU January 2016 ... 12.1.0.2.160119 

More Information? 

--Mike

Tuesday Dec 22, 2015

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

DBaaS Oracle Cloud

What happened so far on my Journey to the Cloud?

I haven't stopped my journey if you wonder about the inactivity for two weeks. I just had to learn a bit as my pretty naive approach caused me some trouble. Based on my last experience I had to find out why APEX left so many leftovers when I removed it the friendly way. And I wanted to patch my environment to a higher version of PSUs as - learning experience - a new deployment does not get deployed with the most recent PSU applied from scratch.  So there's still plenty of stuff to do.
.

Patching my DBaaS database with the most recent PSU

If you've got frustrated in the past by yourself while applying a PSU then please read on - I guarantee you'll smile more than once ...

First of all what is my expectation from a cloud environment?
Yes, Push A Button.

Well ... with the July 2015 PSU (12.1.0.2.4) this worked just fine even though it took a bit of time to download the PSU. But it got applied flawlessly by just pushing a button.

PSU Choice

But we have December 2015 already. So I would like to apply the Oct 2015 PSU (12.1.0.2.5) to my environment. And it turns out that currently this is the only one getting offered in the DBaaS environment to be applied. 

First step: Execute the precheck

See the results ...

Oh, it failed. But why? It does not tell me anything about the reason why it had failed. Was it no space left? Or a patch conflict? Or something else? No idea as Error [1] isn't very useful.  

And what's next? Live without the PSU even though I'm preaching since ages in our workshops that you MUST apply PSUs on a regular basis? Ok, so I have to sort this out.
,

Let's find it out

I did login to my environment via SSH. Then:

cd /u01

Ah, and there's a psu.n subdirectory - that looks promising. And there's a file conflictlog in it - that does look even more promising. 

It tells me:

Invoking prereq "checkconflictagainstohwithdetail"

ZOP-47: The patch(es) has supersets with other patches installed in the Oracle Home (or) among themselves.

ZOP-40: The patch(es) has conflicts with other patches installed in the Oracle Home (or) among themselves.

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:
There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
21359755, 21627366

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
20281121

Whole composite patch Conflicts/Supersets are:
Composite Patch : 21359755

        Conflict with 21627366
        Bug Superset of 20281121

Detail Conflicts/Supersets for each patch are:

Sub-Patch : 21359755
        Conflict with 21627366
        Conflict details:
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kzan.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kspt.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kcb.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kcrfw.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kokt.o

        Bug Superset of 20281121
        Super set bugs are:
        20281121

Patch failed with error code 1

Ah, my friend Error [1] again. Now all is clear, isn't it? There's a conflict as the previous installation deployed in the cloud does seem to have gotten some extra treatments - which is good in one way but bad in another as I will have to solve this now. The Cloud Console does not offer me anything to solve this.
.

Luckily ...

I'm subscribed to our internal cloud mailing lists. And other people are way more smarter than I am so I found an email linking an explanation in the official documentation (Known Issues for the Database Cloud As A Service). There a quite a few known issues and it's very useful to have such a document. And here we go with the solution to my problem:

Ok, I have two options, one in the graphical interface, the other one on the command line. I'll go with the first option as this is meant to be Push A Button style and not type on the command line.

So first I click on PATCH in the hamburger menu: 

And then I chose the FORCE option.
May the force be with me.

The alternative would have been on the command line using the dbpatchm subcommand of the dbaascli utility:

Before applying the patch, set the value of the ignore_patch_conflict key to 1 in the /var/opt/oracle/patch/dbpatchm.cfg patching configuration file; for example:

ignore_patch_conflict=1
.

Et voilà ...

I took a bit ...

Actually a bit over 30 minutes ... but finally ...

The most recent PSU 12.1.0.2.5 from October 2015 has been applied to my DBaaS Cloud installation. It wasn't that complicated - if I had known upfront to hit the magic "FORCE" button ;-)

Finally let's check:

COLUMN PATCH_ID FORMAT 99999999
COLUMN PATCH_UID FORMAT 99999999
COLUMN VERSION FORMAT A12
COLUMN STATUS A12
COLUMN DESCRIPTION A30

select  PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION from DBA_REGISTRY_SQLPATCH order by  BUNDLE_SERIES;


.

Remove APEX from my database and install it into my PDB

In my previous journey removing APEX from my cloud database didn't work quite well. I had leftovers afterwards, mainly from the Multitenant Self Service Provisioning APEX application (owner: C##PDBSS) and from the Database-As-A-Service-Monitor. 

Removing  Multitenant Self Service Provisioning Application

From some internal email conversation (and from the readme.pdf included in the download of the Multitenant Self Service Provisioning Application) I learned that there's a pdbss_remove.sql. And a find showed me the unusual location in the cloud deployment:

/var/opt/oracle/log/pdbss/pdbss

So first of all I connected to my CDB$ROOT and started the removal script:

sqlplus / as sysdba
spool /tmp/pdbss_remove.log
@/var/opt/oracle/log/pdbss/pdbss/pdbss_remove.sql

It took 1:45 minutes to complete. Spool off is not necessary as the script ends SQL*Plus.

Then I started the APEX removal script from the $ORACLE_HOME/apex subdirectory:

cd $ORACLE_HOME/apex
sqlplus / as sysdba

@apxremov_con.sql

Well ... but again something seemed to fail as I end up with a good bunch of invalid objects. 

First I did recompile:

@?/rdbms/admin/utlrp.sql 

But even then those 3 objects kept INVALID after the removal.

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

OWNER         OBJECT_NAME
------------- --------------------------
SYS           WWV_DBMS_SQL
FLOWS_FILES   WWV_BIU_FLOW_FILE_OBJECTS
APEX_040200   APEX$_WS_ROWS_T1

And furthermore a good number of APEX user schema did not get removed as well.

CON_ID USERNAME
------ --------------------------
     1 APEX_REST_PUBLIC_USER
     1 APEX_PUBLIC_USER
     1 APEX_LISTENER
     1 APEX_040200
     3 APEX_REST_PUBLIC_USER
     3 APEX_PUBLIC_USER
     3 APEX_LISTENER
     3 APEX_040200

8 rows selected.

Will update this blog post as soon as I have more news about how to remove APEX flawless from the cloud deployment. The issue is under investigation.

So better don't remove APEX at the moment from the DBaaS cloud deployment. 

--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 Aug 18, 2015

Ouch, this hurts: bug 17325413 - patch BEFORE upgrade!

PatchI really don't want to turn this blog into something making our database look bad. But in this case it is really necessary as it is VERY UNUSUAL that we recommend to patch the database BEFORE upgrade


Just for clarification:

The following topic will affect all databases between 11.1.0.6 and 11.2.0.4.1 - those (and only those) need to be patched BEFORE upgrade. The topic is fixed in 12.1.0.2 but as it gets introduced with the BEFORE upgrade database version you'll have to apply the fix before upgrade. The inclusion of the fix in 12.1.0.2 means only that the misbehavior won't happen there again. But as it is a meta data dictionary corruption you'll have to apply the fix before as otherwise it will break during or after the upgrade.


First of all, thanks to Ehtiram Hasanov (cleverbridge AG) and Oliver Pyka (http://www.pyka.de/) for highlighting this to me. And sorry for hitting this issue ...

Symptoms:

After upgrading to Oracle Database 12.1.0.2 you'll get one of the below errors when trying to read data: 

  • ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB4A26] [Address not mapped to object] []
  • ORA-12899 / ORA-607
  • ORA-600 [kdmv_check_row_2:IMCU row has wrong contents]
  • ORA-600 [kddummy_blkchk]
  • ORA-600 [kdBlkCheckError]
  • ORA-600 [klaprs_12]
  • ORA-600 [13013]
  • ORA-600 [17182] 

Analysis:

Basically this happens when you try to drop a column with a DEFAULT value and a NOT NULL definition - it ends up with dropped column data being written to disk leading to block corruptions. This causes problems for generating undo which cannot be applied; a ROLLBACK fails.

If you need more information please look up this MOS Note about
Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption

Versions being affected:

  • These versions require to be patched BEFORE upgrade:
    • Oracle Database 11.2.0.3.9 and above (may happen with earlier PSUs as well)
      Solution: Apply the fix 17325413  on top - see below
    • Oracle Database 11.2.0.4.0 and 11.2.0.4.1 
      Solution: Apply the most recent PSU
  • These versions can get you the issue if you haven't patched BEFORE upgrade:
    • Oracle Database 12.1.0.1
    • Oracle Database 12.1.0.2

Workaround and/or Fix:

The MOS Note about Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption explains the workaround WHEN you hit this issues. 

As a precaution you will have to make sure that you applied one of those fixes BEFORE upgrading to Oracle Database 12.1.0.2. as the fix for Bug 17325413 is included in all those mentioned below (list is taken from above MOS Note as well).

The best way to avoid this is really to apply the patch (or the PSU/BP including the patch) before upgrading.

The issue has been mentioned in "Oracle 11.2.0.4 - Known Issues and Alerts" (MOS Note:1562139.1)  under "Issues Introduced":

Issues introduced

But that does jump into your eye as a thing you need to fix before upgrade.
We'll see if we can get the issue added to the 12c MOS Notes as "Upgrade Issues".

--Mike 

Monday Apr 20, 2015

Oracle PSU and BP April 2015 is available

As of April 14, 2015:

The April 2015 PSU/BP is available!

Here's the most important information:

Please find below the links to the Recommended Patches and Patch Numbers for each of your database releases:

12.1.0.2
 Availability and Known issues for 12.1.0.2      Note:1683799.1
12.1.0.1
 Availability and Known issues for 12.1.0.1      Note:1565082.1 
11.2.0.4
 Availability and Known issues for 11.2.0.4      Note:1562139.1
 
11.2.0.3
 Availability and Known issues for 11.2.0.3      Note:1348336.1

11.1.0.7
 List of fixes included in 11.1.0.7              Note:601739.1

-Mike

Monday Mar 09, 2015

Applying a PSU or BP to a Single-/Multitenant Environment

I have already explained in broad details a while ago how to:

But one may miss the steps for applying a PSU (Patch Set Update) or BP (Bundled Patch) to a Single-/Multitenant Environment. At first everything will work just the same if you choose the Everything-at-Once strategy as datapatch will adjust all the required things regardless of being executed in a stand-alone or a singe/Multitenant environment.

But what happens if you apply a PSU or a BP to one of your Multitenant environments and want to move PDBs one after another (or a few at the same time) to the new environment?
Or revert a PSU by plugging out from a CDB with the PSU inside - and plug it back into a CDB with a lower version or no PSU at all? 

First step - Check Plug In Compatibility 

Before you can even start your unplug/plug operation you should always perform the plugin check. This is divided in two simple steps:

  1. Create the XML description file for your PDB in CDB_SOURCE
    exec DBMS_PDB.DESCRIBE ('/home/oracle/PDB1_unplug.xml', 'PDB1');
  2. Run the plug in check in CDB_DEST
    begin
      if DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/PDB1_unplug.xml','PDB1') then  
      DBMS_OUTPUT.PUT_LINE('No violations found - you can relax');
    else
      DBMS_OUTPUT.PUT_LINE('Violations found - check PDB_PLUG_IN_VIOLATIONS');
    end if;
    end;
    /

No Plugin Violations?

Then please follow the procedure described in:
http://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_one_at_a
without the upgrade part as you don't need to upgrade anything in this case of course. 


Higher patch in CDB_DEST than in CDB_SOURCE?

Then run this query:

select TYPE,MESSAGE,ACTION from PDB_PLUG_IN_VIOLATIONS where NAME='PDB1';

It will tell you to execute datapatch:

TYPE    MESSAGE
------  ----------------------------------------------------------------------------
ERROR   PSU bundle patch 1 (PSU Patch 4711): Installed in the CDB but not in the PDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Lower patch in CDB_DEST than in CDB_SOURCE?

Now this becomes a bit more tricky. See the output of PDB_PLUG_IN_VIOLATIONS:

TYPE  MESSAGE
----- ----------------------------------------------------------------------------
ERROR PSU bundle patch 1 (PSU Patch 4711): Installed in the PDB but not in the CDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Huh? Install???
What does this mean? Should I install now the current CDB/PDB's PSU into my target environment before being able to step down? 

Actually I think this message is misleading. And when you look into the MyOracle Support Note describing this under scenario 3 (MOS Note:1935365.1 - Multitenant Unplug/Plug Best Practices) you'll see that the author silently assumed as well that is is more likely that you'll remove the patch from the PDB. 

But how do you remove changes which came in with datapatch from within a PDB only?

You will need to run datapatch -rollback on the affected PDBs only:

$> datapatch -rollback <patch id> –force [–bundle_series] -pdbs <pdb1,pdb2,...,pdbn>

For further information see:

--Mike 

Thursday Jul 24, 2014

Why "We'll wait for the 2nd release!" is a misconception ...

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.

I'm probably not the only person who would love to see if we'd remove this "first" and "second" release tags. This has become obsolete with Oracle 11.1. We were telling customers officially that this is the brand new fantastic Oracle Database 11g release. But in fact it was - from the coding perspective - more or less a very stable 10g. In my workshops I did call it Oracle 10.3 with a marketing sticker on it. And as far as I can see the customer's I've had helped with going live on Oracle Database 11.1.0.7 were quite happy. Some really large shops still work with this release today with hundreds of databases in production.

But honestly most of the changes got introduced with Oracle Database 11.2. And not for a small number of customers this meant waiting for the first patch set (which since then has become a full release). Plenty of people went live with Oracle Database 11.2.0.2. But a lot of the remarkable changes got introduced not in Oracle 11.1 but in Oracle 11.2. Just remember things such as the move from Clusterware to Grid Infrastructure. But also minor things such as DEFERRED_SEGMENT_CREATION and plenty of optimizer news.

Now with Oracle Database 12c and the first patch set (full release) 12.1.0.2 I have heard this again - and I see it on the mailing list as well once a week:

"When will Oracle 12.2 be available?"

Well, that's the misconception. It's true, Oracle Database 12.1.0.2 has new features and extensions. But it has also many fixes over the already very stable Oracle 12.1.0.1. Why should anybody wait for Oracle 12.2 now? Because it's supposed to be THE SECOND release? Forget this - this is thinking from the old days.
We are in year 2014 now.

And then spend a minute to look closer to the Support Policy.

  • Oracle Database 11.2 will go out of Premier Support in 6 months. Yes!!! 6 months
  • And correct, we'll give everybody on Oracle 11.2.0.4 more than one full year of Extended Support for free
  • For Oracle 11.2.0.3 Extended Support will end 27-AUG-2015

That means if you plan to stay on Oracle 11.2.0.3/4 for a longer period you'll either have to calculate 20% extra of your support fee for the 2nd year of Extended Support. Or you prefer to "hope".

I can't tell you when Oracle 12.2 will be available - and I don't care. Usually people wait for the first patch set anyways which gets releases based on experience from the past 3 databases releases roughly a year and a bit after the initial release. Just do the math and you'll see where you end up with this strategy.

My recommendations are:

  • Stop thinking about THE SECOND release
  • Evaluate Oracle Database 12.1.0.2 now - not tomorrow
  • Look at the Support Policy - you need to start your upgrades as soon as possible

PLEASE READ ON:

https://blogs.oracle.com/UPGRADE/entry/no_extra_fee_for_extended

-Mike 

Tuesday Jan 28, 2014

MS Windows Bundle Patches - a VERY different story

I like Upgrade Workshops for a very different reason:
Hopefully people feed back with questions and information I wasn't aware off before. So for myself these workshops are also a learning experience. And thanks to a customer in Singapore (Thanks a lot, Hanh!!) I learned something about Oracle's Bundle Patches for MS Windows.

Customer asked about a specific issue when using RMAN Duplicate command in 12c failing with a nice ORA-600 [KSRPCSEXEC_1]. This got logged a while ago as bug16883554 - and it will be fixed in database patch set 12.1.0.2. But this customer asked for an earlier delivery.

So after a quick check of the SR and the bug it was clear that this patch will be also included for the MS Windows platform into the Bundle Patch 12.1.0.1.4 on Win.
Well, my first thought: Ouch! That will be the July 2014 PSU.

But in fact it isn't meant here. So I learned that on Windows patching runs on a different schedule and notation. 

MOS Note:161549.1 will tell you all the details about:
Oracle Database, Networking and Grid Agent Patches for Microsoft Platforms

64-Bit x64 Patches :

Patch

Patch Location Bug Fix List Notes
12.1.0.1.4 Target date End January 2014
12.1.0.1.3 Bug:17977915 Patch:17977915 Requires 12.1.0.1.0

-Mike 

Tuesday Aug 04, 2009

Patch your $OH before you upgrade

[Read More]
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