For clarification:
The following blog post applies to upgrades to Oracle 12.1 done by DBUA only whenever a SPU/PSU/BP is installed into the 12.1 home prior to the upgrade (which I’d highly recommend as patching before upgrade saves you headache after upgrade!).
Update [Dec 10, 2015]:
The required patch is included in 12.1.0.2BP13 but not 12.1.0.2.PSU5 (Oct 2015) – it will be included in the 12.1.0.2.PSU6 (Jan 2016) and all following PSUs.
Update:
For Oracle Database 12.2 and newer please see:
https://mikedietrichde.com/2017/05/25/dbca-execute-datapatch-oracle-database-12-2/
Two customers independently reported last week that they have doubts on DBUA’s ability to apply the required SQL changes associated with CPU/SPU or PSU.
First of all, let me tell you that this is not an issue when you do a command line upgrade to Oracle Database 12c with catctl.pl – you’ll only need to take care when using the DBUA.
One claimed that this feature alongside with datapatch.pl had been announced a while back:
Oracle Premier Support – Oracle Database Support News Issue November, 2014 Volume 46 (Doc ID 1954478.1)
Which Patching Tools uses Datapatch ?
- Opatchauto
- OPatchAuto calls datapatch automatically to complete post patch actions upon installation of the binary patch and restart of the database.
- Enterprise Manager Cloud Control
- Starting version 12.1 EMCC now calls datapatch to complete post patch actions upon any 12c or later database restart
- Upgrade
- Catctl.pl and DBUA now call Datapatch during the upgrade process
- OPatch
- Datapatch integration with OPatch is not possible as OPatch is executed when the database is down and datapatch requires the database to be open to complete its activity.
The other customer provided all the logfiles – and I print the important logs only with the interesting part marked in RED:
========================================== Contents of catupgrd_datapatch_upgrade.log ========================================== SQL Patching tool version 12.2.0.0.0 on Tue Jul 14 13:10:39 2015 Copyright (c) 2014, Oracle. All rights reserved. Connecting to database...OK Bootstrapping registry and package to current versions...done Determining current state...done Current state of SQL patches: Patch 19282028 (Database PSU 12.1.0.2.1, Oracle JavaVM Component (): Installed in the binary registry only Bundle series PSU: ID 1 in the binary registry and not installed in the SQL registry Adding patches to installation queue and performing prereq checks... Installation queue: Nothing to roll back Nothing to apply SQL Patching tool complete on Tue Jul 14 13:10:57 2015 ============================================= Contents of sqlpatch_catcon__catcon_22773.lst ============================================= catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! catconInit: start logging catcon output at 2015-07-14 13:10:39 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Ok, so it seems to be true that DBUA did not apply the post upgrade SQL changes associated with the most recent PSU.
Now digging a bit deeper we could solve the puzzle.
The DBUA uses the “catctl.pl -x” option executing catuppst.sql (the post upgrade script) in a separate step whereas on the command line catctl.pl will execute catuppst.sql by default (tracked with bug19990037). The DBUA uses catcon.pl instead to execute catuppst.sql. In previous releases this was not an issue as catbundle.sql got automatically executed as part of catuppst.sql. But as datapatch.pl is a PERL script, and a PERL script cannot be run from within a SQL script, catuppst.sql can no longer call the post-patching activities. The DBUA in 12.1.0.2 misses this action as a separate task.
Summary and Solution
DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:
cd $ORACLE_HOME/OPatch
./datapatch -verbose
And again, this is only necessary when you used the DBUA for a database upgrade. This step is not required for the command line upgrade. This will be fixed in an upcoming release of the DBUA.
If you are in doubt whether the DBUA or the command line upgrade had been used, unfortunately you won’t find any indication inside the database. But look into $ORACLE_BASE/cfgtoollogs/dbua/logs – if the “dbua” directory exists, the DBUA had been used. If not than the command line upgrade had been processed.
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/ - DBUA 12.2 and datapatch.pl (March 25, 2017)
https://mikedietrichde.com/2017/05/25/dbca-execute-datapatch-oracle-database-12-2/
–Mike
Hi,
Is this also relevant for database version upgrades with DBUA or for applying PSU/CPU’s only?
Regards,
Markus
Markus,
I updated the post – it is relevant to every upgrade to Oracle 12.1 with DBUA as soon as there’s a SPU/PSU/BP installed into the target home before upgrade (which I’d highly recommend as patching before upgrade saves headache after upgrade).
Cheers
Mike
Hi Mike ,
How to know which tool used for upgrade
to 12.1.0.2 ?
Thanks.
Kais
Good question, Kais 🙂
Inside the DB you won’t get any indication whether the DBUA or catctl.pl directly have been used. But the log files will tell you.
Look into:
$ORACLE_BASE/cfgtoollogs/dbua/logs
If this directory ("dbua") is there the DBUA had been used.
Cheers
Mike
Hi Mike,
I would like also to share some info on this subject.
During upgrade tests which I performed, the new Db home was v. of 12.1.0.2 + PSU4(July, 2015). After upgrade was completed the stauts of the database query showed that datapatch run partially. The PSU was a combo PSU(Db and Java VM components) and the post patch actions related to Java VM were implemented, but related to DB PSU part were not. simple query of dba_registry_sqlpatch showed this. I think the reason was that, JVM PSU post patch steps require the db to be opened in upgrade mode, that is why dbua is obliged to run it. but for implementing DB PSU post patch part, we should run datapatch after the upgrade. The readme note of corresponding Db and Java VM PSU’s also says that. for upgraded databases, there are no post patch steps needed for Java VM patch. But for DB PSU patch , they do required.
Regards,
Ehtiram
Mike,
I need help. I have been tasked with creating a script that will upgrade an Oracle 11.2.0.3 database to 12.1.0.2 on Windows.
Below is an outline of what I am doing:
1.Pre-upgrade steps
2.Backup database
3.Switch the DB home to Oracle 12
4.Upgrade DB (using catctl.pl)
5.DBAU moved DB files using RMAN (I would just shut it down and move the files in the OS)
6.Recreate control file
7.Post upgrade steps (using catcon.pl)
So far, I have not been able to run catctl.pl successfully. Here is the error:
Serial Phase #: 0 Files: 1 next_proc: total processes (4) != number of li
ve processes (3); giving up
Oracle docs, blogs, and the Oracle community all provide different parameters for calling catclt.pl. I even ran DBUA and checked its logs to see how it ran catctl.pl and got yet another set of parameters.
Help?
JimW
Jim,
I’ll come back later to you – waiting on feedback from a colleague.
Cheers
Mike
Jim,
this is from my colleague Joe:
—
This usually means that a SQLPlus processes has died. Phase 0 is where we do a lot of checking to make sure you can do the upgrade. If there is an error in catupstr.sql will terminate the SQL process and will cause this to happen in catctl.pl. catcon.pm will recognized that one process has gone away and terminate. Tell them to check his log files to see what is going on. You may see and error like the following in catupgrd0.log.
ERROR at line 1:
ORA-01722: invalid number
Documentation should be in the log file on what to do about it like. Documentation would look something like this.
DOC
#######################################################################
#######################################################################
The following error is generated if (1) the old release uses a time
zone file version newer than the one shipped with the new oracle
release and (2) the new oracle home has not been patched yet:
SELECT TO_NUMBER(‘MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME’)
*
ERROR at line 1:
ORA-01722: invalid number
o Action:
Shutdown database ("alter system checkpoint" and then "shutdown abort").
Patch new ORACLE_HOME to the same time zone file version as used
in the old ORACLE_HOME.
#######################################################################
#######################################################################
#
—
Could you check the logs please?
Cheers
Mike
Exactly the same problem we had today. We upgraded to 12.1.0.1.5 (12.1 software pre-patched with PSU5), but got ORA-600 bug that is supposedly fixed in PSU5.
On a further investigation, we’ve noticed PSU wasn’t listed in registry$sqlpatch, so we ran
./datapatch -verbose
and that fixed it.
Regards,
Tanja
Tanja,
sorry for this inconvenience – I know that this is really a terrible issue.
Thanks for your comment!!!
Cheers
Mike
Tanja,
patch is included in the 12.1.0.2BP13 but not 12.1.0.2.PSU5 (Oct) – it will be included in the 12.1.0.2.PSU6 (Jan 2016) and all following PSUs. Too late for you but I’ll update the above blog post as well with this information.
Mike
Hi,
Just wanted to let you know, that I have just done an upgrade with dbua (using silent upgrade) to an oracle home with the Jan 2016 PSU and datapatch info was still missing in upgraded database.
So it seems that the patch was not included in the Jan 2016 PSU bundle:
[oracle@inframan ~]$ opatch lspatches
21948354;Database Patch Set Update : 12.1.0.2.160119 (21948354)
OPatch succeeded.
SQL> select
patch_id,
patch_uid,
version, status,
description
from
dba_registry_sqlpatch
order
by bundle_series
;
2 3 4 5 6 7 8 9 10
no rows selected
Hi Mike
I have applied PSU 22291141 to my Oracle 12c DB version 12.1.0.2.0 using opatch apply but am having problems running datapatch, I get the error:
Queryable inventory could not determine the current opatch status.
Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’
and when running qopatch queries such as the following I get these errors:
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_QOPATCH", line 1293
no rows selected
SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_QOPATCH", line 1293
no rows selected
SQL> select dbms_qopatch.GET_OPATCH_BUGS from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_QOPATCH", line 285
ORA-06512: at line 1
I have been unable to find a solution as to what is the problem here, have you come across these issues at all and could you please advise any possible solution?
@Catherine
Catherine,
does this help you:
Oracle Support Document 20284155.8 (Bug 20284155 – Datapatch fails as it cannot determine the current opatch status) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=20284155.8
Otherwise please check with an SR with Oracle Support as there are several issues with datapatch as far as I can see.
Cheers
Mike
Hi Mike
I actually solved it based on another one of your blogs, looks like I had the wrong version of OPatch!! Thanks anyway for the reply!
Catherine
Hi Mike,
I have the following issue. Database is running in 12.1.0.2.4; next to this we installed the PSU Jul2016 12c release. I want to apply the PSU on the database. Restart database in the PSU release and apply the psu:
datapatch -apply 23054246 (Database PSU) -bundle_series -force -verbose.
The result is that the apply ends with errors due to the fact that DVSYS is not installed. We are not using DataVault but due to this the datapatch ends in error and in the dba_registry_sqlpatch the status is "ended with errors".
I already created SR3-13523140392, resulted in spin off 3*13506939601 but I did not get any usefull feedback till now….
So apparently the datapatch script is trying to patch the DVSYS schema, but if not present, it results in an error.
PSU has been applied to the rest of the db, but final result is 100% clean.
Any idea?
Thanks – I checked your SRs and there are fundamental incorrect assumptions in there: DVSYS is NOT present if you haven’t installed DV. None of my databases has DVSYS.
Datapatch shouldn’t be an issue as for bundle patches we have the component information from bundledata.xml,
so we have always been able to only install if the component was ok in the registry.
If I get your case right (but I haven’t seen this by myself – and I patched my environments in July with the PSU (11.2.0.4) and the BP (12.1.0.2) you have no DV or DVSYS but datapatch -verbose fails.
I can’t solve this – you’ll have to work with Support until this is fixed and you have a final confirmation.
Cheers
Mike
Marvin,
thanks for the hint – and I wasn’t aware of the new " -skip_upgrade_check" flag.
I can only assume that the UPGRADE mode requirements comes in because of the OJVM patch included in the fix applied.
And I can only recommend you to file an SR so these things get logged.
Cheers
Mike
Hey Mike,
I’m upgrading from 12.1 to 12.2 now and have the same question. The even more complex scenario is that I also have JVM in the database. I installed clean 12.2, and originally thought about installing the latest RU + OJVM patches and then upgrade the database. According to this DBUA won’t run datapatch (or maybe it’s fixed in 12.2), in any case, because I need to install both RU (27105253 in my case) and OJVM (27001739) and both ask for datapatch script, I suspect they overwrite each other’s script. I will install the RU and upgrade the database and then install the OJVM, but I wish Oracle would solve that, or at least document this somehow.
Thanks
Liron
Hi Mike,
Just a quick update, it seems that DBUA 12.2 runs datapatch. After the upgrade I saw that the DBUA executed the datapatch for the RU (in the logs and in the database). Then I installed the OJVM patch and executed the datapatch for it.
Liron
Hi Liron,
sorry for the late reply – I was out-of-office the past week.
And yes, since the patch level I mentioned in the blog post (and this includes Oracle 12.2) the DBUA does run datapatch.pl (and the same applies to DBCA as well). The problem existed only in Oracle 12.1.0.2 with no patch or a patch level below the mentioned one. And generally for DBCA in Oracle 12.1.0.2 (it didn’t execute datapatch).
Regarding RU+OJVM no worries – the sql scripts are the ones which matter – and datapatch executes them regardless of which patch you install first.
Cheers,
Mike
We have 12.1.0.2 databases running on Windows 2008 /2012 servers.
While applying PSUs, datapatch returns NOTHING To APPLY.
DO we need some specific permissions to run datapatch on Windows
Sunil,
at first I believe you had applied Bundle Patches and not PSUs. But regardless of it, there were issues with datapatch not rolling back things correctly. Actually my ref customer, Universal Investment, had a similar issue with some 2017 Bundles – and I saw it with my own eyes.
PLEASE log an SR for it. Support needs to know about this and may have a solution (manual treatment of the patch views in the database).
Thanks,
Mike
Hello Sunil,
we have 12102 and 12201 both on Windows Server 2012R2. datapatch ends with
Nothing to roll back
Nothing to apply for CDB and PDBs. Oracle-Support is struggling with our SR since the beginning of february.
No solution so far. Until now i found out that “%ORACLE_HOME%\QOPatch\qopiprep.bat” does not execute even with full rights for everybody. As a consequence of this
SYS@SID> select * from OPATCH_XML_INV; brings –> no rows selected
Applocker is not responsible for the blocking of the bat-file and we are currently inspecting our GPOs and local user rigths to find out what prevents qopiprep.bat from executing.
I’d like to know if you could solve your problem and what it was all about.
On a standalone Windows10-notebook with no Domain-GPOs both oracle-versions work without any error.
The whole (very annoying) case prevents us from upgrading our production-systems.
Thanks, Michael
Michael,
After working with Microsoft, we found that
Oracle database datapatch is not compatible with Windows Software Restriction Policy (SRP). The work around is to remove such policies from the registry.
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH; LIBPATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export DYLD_LIBRARY_PATH; /u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > /home/oracle//catupgrd_datapatch_upgrade.log 2> /home/oracle//catupgrd_datapatch_upgrade.err
returned from sqlpatch
Time: 49s
Serial Phase #:66 Files: 1 Time: 111s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH; LIBPATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export DYLD_LIBRARY_PATH; /u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > /home/oracle//catupgrd_datapatch_normal.log 2> /home/oracle//catupgrd_datapatch_normal.err
returned from sqlpatch
Time: 13s
Serial Phase #:70 Files: 1 Time: 25s
Serial Phase #:71 Files: 1 Time: 0s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 3s
Grand Total Time: 3101s
LOG FILES: (catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:51m:41s]
below error in catupgrd.log
stderr from running datapatch to install upgrade SQL patches and PSUs:
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_25952838.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
stdout from running datapatch to install non-upgrade SQL patches and PSUs:
SQL Patching tool version 12.2.0.0.0 on Tue Sep 3 11:07:19 2019
Copyright (c) 2014, Oracle. All rights reserved.
Connecting to database…Database connect failed with:
ORA-01089: immediate shutdown or close in progress – no operations are permitted (DBD ERROR: OCISessionBegin)
Please refer to MOS Note 1609718.1 for information on how to resolve the above errors.
SQL Patching tool complete on Tue Sep 3 11:07:19 2019
stderr from running datapatch to install non-upgrade SQL patches and PSUs:
Can’t call method “prepare” on an undefined value at /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pm line 741.
–however upgrade is successful none of the components are invalid but the registry$history doesn’t show the upgrade status.
Prajesh,
have you checked MOS (the internal page)?
Does your destination have the most recent BP installed?
Cheers,
Mike
Thanks Mike
After applying the latest july’19 BP problem is fixed.
Regards
Parjesh Dutt
One Query Mike
I believe now no need to execute #65 and #69 again.
Please confirm,
Regards
Parjesh
Parjesh,
you can run “datapatch -verbose” manually from the $ORACLE_HOME/OPatch directory – it will tell you if additional changes are necessary.
That’s enough 🙂
Cheers,
Mike