A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing ‘datapatch’ (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:
For DBUA, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl – as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch’s execution):
But afterwards I have learned that things are worse.
The same behavior is true when you create a database.
Not a typo.
You create a fresh database with DBCA (Database Configuration Assistant), you are a honest customer, you have followed our advice and applied the most recent PSU (or SPU or BP) into your Oracle Home. You don’t even deploy one of the preconfigured databases but use the CREATE CUSTOM DATABASE option of DBCA. And the database will run from the patched home – but the SQL changes haven’t been applied to it.
Reason?
DBCA does not call ‘datapatch’ for database changes.
I consider this even worse than the DBUA behavior as the person who upgrades a database in most cases is aware of the future home. But the person who either deploys a new database or asks for one to be deployed is often not identical with the person who did patch the homes.
And there’s no warning displayed yet nor (afaik) is there a MOS note available talking about it.
How do you fix the issue?
After creating a new database make sure to run:
cd $ORACLE_HOME/OPatch ./datapatch -verbose
and double check with DBA_REGISTRY_SQLPATCH view:
select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION from DBA_REGISTRY_SQLPATCH order by BUNDLE_SERIES;
Actually to be 100% you may find some patch information in DBA_REGISTRY_SQLPATCH showing that the JAVAVM patch has been applied in case you’ve installed the Combo version of the PSU. But you’ll miss the database changes.
Important Note
This misbehavior applies to all databases in Oracle Database 12.1.0.2, regardless of which PSU or BP you are using as unless for the DBUA the issue has not been fixed. Please be aware!
Related Blog Posts datapatch.pl:
- No OS authentication? Then datapatch.pl will fail (Sep 29, 2015)
https://mikedietrichde.com/2015/09/29/no-os-authentication-datapatch-will-fail-in-every-upgrade/ - DBCA 12c and “datapatch.pl” – things to know (Aug 17, 2015)
https://mikedietrichde.com/2015/08/17/dbca-12c-and-datapatch-pl-things-to-know/ - DBUA 12c and “datapatch.pl” – things to know (Jul 20, 2015)
https://mikedietrichde.com/2015/07/20/dbua-12c-and-datapatch-pl-things-to-know/
Oracle Database 12.2.0.1
Please see:
–Mike
Addition – Sept 18, 2015:
Please be aware that the same thing happens on ODA (Oracle Database Appliance) with the oakcli. This will be fixed in the version 12.1.2.5 (ODA/oakcli)
Hello Mike,
Executing "datapatch" after a new Oracle 12c DB creation was also the conclusion of a SR which I created in December 2014 (FYI, SR 3-9964155761). During resolution of this SR the Oracle support engineer also stated that "datapatch" should be executed separately after a new DB was created.
He also told that this behavior is due to 2 unpublished bugs. He could not tell me by when this behavior would be fixed (= no longer executing "datapatch" seperately after creation of a new 12c DB) as he stated the following : "As of now, it has been not decided in which version this will be fixed. Once done, this information will be updated in the PSU readmes."
Within this SR it was also stated that "datapatch" does not need be executed seperately after a manual DB upgrade to Oracle 12c as it would be executed automatically as part of the upgrade script catctl.pl.
I really find it a pity that this very important information for all Oracle customers is not put somewhere in a MOS note (ex. "Note 1683799.1 : 12.1.0.2 Patch Set – Availability and Known Issues" or "Note 1609718.1 : Datapatch Known Issues").
Luckily there are still people like you with this fantastic blog in order to spread the word ;-).
Greetings,
Chris
Chris,
I appologize that this isn’t in a MOS Note yet. But my colleagues are working on this – a MOS Note should be available soon.
And sorry for this inconvenience – I just learned this as well a few weeks ago.
You are right – this does not happen with the command line upgrade with catctl.pl – but it does happen in any case where a graph tool is involved as the change from catpatch.sql or catbundle.sql being purely SQL scripts to the PERL mechanism of datapatch came too late for the GUI folks to embed something to call PERL scripts as well.
This all will be fixed in the next major release of Oracle (12c Release 2).
Thanks
Mike
I would rather check the applied patches in the dba_registry_sqlpatch view. I am not sure if the DBA_SQL_PATCHES will have information about the applied patch.
Cheers,
Gleb
Thanks Gleb!!!
In 12c DBUA notes, this information has been added
Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)
Hello Mike,
nice to hear about this, our upgrade was six months ago…
Can I start datapatch against a running production database, or is a downtime needed?
Thanks
Sven
Sven,
datapatch does not require downtime.
Mike
Actually, command line upgrades are affected as well, if you do not use OS authentication. Apparently, datapatch is not able to execute in non-OS authentication mode. See MOS note 1635007.1.
Daniel,
that is a very interesting information.
Let me do a bit of investigation and I’ll come back to you soon.
Cheers
Mike
Daniel,
thanks again – your comment was really helpful!!!
Please see:
https://blogs.oracle.com/UPGRADE/entry/no_os_authentication_datapatch_will
There’s a solution (or actually two) available. But regardless of the solution it’s very good to know about it.
Cheers
Mike
Hello Mike,
In your post of 09/09/2015 you answered that "datapatch" does not require downtime on the question from Sven who asked whether datapatch can be executed on a running production database, or whether a downtime is needed. In fact the database might need to be started in "upgrade" mode as one of the post-SQL patches to be applied by "datapatch" might require this (ex. in case of a Patch Set Update). In this case there will thus be a small downtime needed for the running production database as in order to be able to execute "datapatch" it needs to be stopped and restarted in "upgrade" mode which also means that only users with the privilege "restricted session" can connect to the DB. This is described on Metalink in 1585822.1 – Datapatch: Database 12c Post Patch SQL Automation.
Greetings,
Chris
Hello Mike,
In your post of 09/09/2015 you answered that "datapatch" does not require downtime on the question from Sven who asked whether datapatch can be executed on a running production database, or whether a downtime is needed. In fact the database might need to be started in "upgrade" mode as one of the post-SQL patches to be applied by "datapatch" might require this (ex. in case of a Patch Set Update). In this case there will thus be a small downtime needed for the running production database as in order to be able to execute "datapatch" it needs to be stopped and restarted in "upgrade" mode which also means that only users with the privilege "restricted session" can connect to the DB. This is described on Metalink in 1585822.1 – Datapatch: Database 12c Post Patch SQL Automation.
Greetings,
Chris
Hi Chris,
you are referring to this note:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1585822.1
It says:
"If a patch requires upgrade mode startup, datapatch enforces that via a pre-req check. To overcome this error, DB/PDB needs to be re-started in Upgrade/Migrate mode to complete patching. Please note that when there are a number of simultaneous patch applications pending, datapatch will fail the upgrade mode pre-req check if any of the pending patches requires upgrade mode startup. Any patches not requiring upgrade mode can be installed by invoking datapatch with the –apply bug # option "
But this is valid for the OJVM patches only, not for the regular database patches as they are always rolling applicable. For this topic (OJVM) I can refer to this blog post:
https://blogs.oracle.com/security/entry/october_2014_critical_patch_update
So basically my answer to Sven was directed to database-only patches.
Cheers 🙂
Mike
Hi Mike,
thanks for your heads up!
When I ran ./datapatch -verbose against a previously created database using RDBBMS 12.1.0.2.0 + "Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)" + "Database Patch Set Update : 12.1.0.2.4 (20831110)" + Sub-patch 20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)" + Sub-patch 19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)" its output told me that I had to start the database in UPGRADE mode, which means (afaik) that the database has to be shutdown’d and restarted with UPGRADE flag. That would incur downtime, wouldn’t it?
Best regards,
Andre
Andre,
yes this is expected because of the OJVM patch included. "Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)"
If you’d like to avoid this restart then please use the PSU *without* the OJVM.
Oracle Support Document 20831110.8 (Bug 20831110 – 12.1.0.2.4 (Jul 2015) Database Patch Set Update (DB PSU)) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=20831110.8
is the same PSU but *without* the OJVM patch – and thus "rolling installable" – it requires no restart of the database nor does it require "upgrade mode".
Hope this helps – thanks
Mike
Hi Mike,
On september 18th you mention that this also applies to oakcli on the ODA’s, but that it is fixed with 12.1.2.5.
I have updated multiple ODA’s to that version but cannot see that the patches have been applied on the databases. The query you mention:
select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
from DBA_REGISTRY_SQLPATCH
order by BUNDLE_SERIES;
Returns no rows.
If I’m correct it means that I still should run:
cd $ORACLE_HOME/OPatch
./datapatch -verbose
That probably means that patches AFTER applying ODA Patch 12.1.2.5.0 will not have this issue anymore?
Regards and thank you for your great work on database upgrades. It saves me time again and again.
Patrick
Patrick,
it should be fixed with 12.1.0.2.5 – but Roy and I have seen other issues with datapatch not updating the patch information correctly.
I hate to say this but please refer to Oracle Support via MOS as they need to know if something is not updated correctly in the patch view.
Thanks
Mike
Hi,
I can confirm that this is fixed now in version 12.1.0.2.6 of the ODA software, since below is the result after creating a database using the oakcli tool
SQL> l
1 select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
2 from DBA_REGISTRY_SQLPATCH
3* order by BUNDLE_SERIES
SQL> /
PATCH_ID PATCH_UID VERSION STATUS
———- ———- ——————– —————
DESCRIPTION
——————————————————————————–
21948354 19553095 12.1.0.2 SUCCESS
Database Patch Set Update : 12.1.0.2.160119 (21948354)
SQL>
Thanks a lot!!!
Hi Mike,
what if installed the RDBMS binaries then applied the lastest PSU before creating any database , do i still need to run datapatch ?
Regards,
Sam
Sam,
yes you’ll have to do this.
Cheers
Mike
Hi Mike,
as things seem to have changed with Oracle 12c Release 2, maybe it’s time for an update of this post (or a new post 🙂 )
Regards
Markus
Hi Markus,
I haven’t tried it with 12.2.0.1’s DBCA yet. Will post an update as soon as the first BP is available for 12.2. 😉
Thanks for the reminder,
Mike