I have already written about RAC/Grid Infrastructure related patching of 12.1.0.1.0 to 12.1.0.1.1, aka the October 2013 PSU for the database.
This post is a follow-up for pure RDBMS-only installations. I initially thought it wasn’t worth blogging about it (and hence the lag between the posts) but I came across an interesting post-apply step that is required for the databases: datapatch.
This is a new tool to run post the Patch Set Update installation against the non-CDB.
UPDATE: There are known problems with the tool for PSU 1 and PSU 2 Click here for a reference to MOS, an explanation and fix.
Here is the reference output for a non-CDB:
[oracle@server1 17027533]$ . oraenv ORACLE_SID = [ora11] ? NCDB The Oracle base remains unchanged with value /u01/app/oracle [oracle@server1 17027533]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 3 23:06:14 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2288872 bytes Variable Size 905970456 bytes Database Buffers 687865856 bytes Redo Buffers 7286784 bytes Database mounted. Database opened. SQL> select cdb from v$database; CDB --- NO SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@server1 17027533]$ cd /u01/app/oracle/product/12.1.0.1/dbhome_1/OPatch/ [oracle@server1 OPatch]$ ./datapatch -verbose SQL Patching tool version 12.1.0.1.0 on Fri Jan 3 23:06:38 2014 Copyright (c) 2013, Oracle. All rights reserved. Connecting to database...OK Determining current state... Currently installed SQL Patches: Currently installed C Patches: 17027533 Nothing to roll back The following patches will be applied: 17027533 Adding patches to installation queue... Installing patches... Validating logfiles... Patch 17027533 apply: SUCCESS logfile: /u01/app/oracle/product/12.1.0.1/dbhome_1/sqlpatch/17027533/ 17027533_apply_NCDB_NCDB_2014Jan03_23_06_45.log (no errors) SQL Patching tool complete on Fri Jan 3 23:06:52 2014 [oracle@server1 OPatch]$
Interesting tool! Even more so if you look at the file, which calls “sqlpatch”-a utility I haven’t come across yet. It’s also very convenient that it reports “no errors” from the patch application!
[oracle@server1 OPatch]$ tail -n3 datapatch # Call sqlpatch to do the real work $ORACLE_HOME/sqlpatch/sqlpatch $@
Tracing down sqlpatch I found the following note on Metalink, which reminded me of a feature I read about but didn’t have time to test: Oracle Database 12.1 : FAQ on Queryable Patch Inventory (Doc ID 1530108.1). With this at my disposal I can finally query the database for installed patches!
SQL> desc DBMS_QOPATCH FUNCTION ADD_OINV_JOB RETURNS BOOLEAN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NNAME VARCHAR2 IN INAME VARCHAR2 IN PROCEDURE CONFIG_OINV_JOBS FUNCTION DROP_OINV_JOB RETURNS BOOLEAN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NNAME VARCHAR2 IN INAME VARCHAR2 IN FUNCTION GET_OPATCH_BUGS RETURNS XMLTYPE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PNUM VARCHAR2 IN DEFAULT FUNCTION GET_OPATCH_COUNT RETURNS XMLTYPE FUNCTION GET_OPATCH_DATA RETURNS XMLTYPE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PNUM VARCHAR2 IN FUNCTION GET_OPATCH_FILES RETURNS XMLTYPE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PNUM VARCHAR2 IN FUNCTION GET_OPATCH_INSTALL_INFO RETURNS XMLTYPE FUNCTION GET_OPATCH_LIST RETURNS XMLTYPE FUNCTION GET_OPATCH_LSINVENTORY RETURNS XMLTYPE FUNCTION GET_OPATCH_OLAYS RETURNS XMLTYPE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PNUM VARCHAR2 IN FUNCTION GET_OPATCH_PREQS RETURNS XMLTYPE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PNUM VARCHAR2 IN FUNCTION GET_OPATCH_XSLT RETURNS XMLTYPE FUNCTION GET_PENDING_ACTIVITY RETURNS XMLTYPE PROCEDURE GET_SQLPATCH_STATUS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PNUM VARCHAR2 IN DEFAULT FUNCTION IS_PATCH_INSTALLED RETURNS XMLTYPE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PNUM VARCHAR2 IN PROCEDURE OPATCH_INV_REFRESH_JOB PROCEDURE OPATCH_RUN_JOB FUNCTION PATCH_CONFLICT_DETECTION RETURNS XMLTYPE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAME VARCHAR2 IN PROCEDURE REFRESH_OPATCH_DATA PROCEDURE REPLACE_DIRS_INT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PF_ID NUMBER IN PROCEDURE REPLACE_LOGSCRPT_DIRS PROCEDURE SET_CURRENT_OPINST Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NODE_NAME VARCHAR2 IN DEFAULT INST_NAME VARCHAR2 IN DEFAULT PROCEDURE SET_DEBUG Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DEBUG BOOLEAN IN
Now to see which patches are present in the ORACLE_HOME my database is started from I can use this query:
SQL> select xmltransform(dbms_qopatch.get_opatch_list(), dbms_qopatch.GET_OPATCH_XSLT()) from dual ; Patch Details: Patch(sqlpatch) 17027533: applied on 2014-01-03T23:02:36Z Unique Patch ID: 16677152 Patch Description: Database Patch Set Update : 12.1.0.1.1 (17027533) Created on : 27 Sep 2013, 05:30:33 hrs PST8PDT Files Touched:
With a similar syntax I can perform a lot more, I can even check if a given patch is installed or list overlay patches etc: cool stuff we didn’t have before. Although I should add that not every single feature provided meaningful output, there is still some work to be done before it’s really, really useful.
Debugging post-patch script application
For the more curious reader you can trace sqlpatch by using the -debug flag. I found it in the perl code which ultimately is executed.
Martins Blog 