Martins Blog

Trying to explain complex things in simple terms

Interesting post-install steps for Oct 2013 12.1.0.1.1 PSU

Posted by Martin Bach on January 3, 2014

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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,239 other followers

%d bloggers like this: