I was playing with the query published by Syed Jaffar Hussain to list installed patches using DBMS_QOPATCH package that I described here http://dba24.pl/2016/07/12/how-to-list-the-db-patch-details-in-sql/.

Unfortunately I came across some problems and would like to show you investigation of one of those. The example below was run on Single Instance with Oracle Restart – Oracle 12.1.0.2 PSU April 2016 and with role separation (oracle owner for oracle software and grid owner for Grid Infrastructure)

Let’s run the query to check patches installed in the ORACLE_HOME

[oracle@server.dba24.pl tmp]$ sqlplus sys@CDB1 as sysdba

SYS@CDB1> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.*
  2    3      from a,
  4           xmltable('InventoryInstance/patches/*'
  5           passing a.patch_output
  6           columns
  7          patch_id number path 'patchID',
  8          patch_uid number path 'uniquePatchID',
        description varchar2(80) path 'patchDescription',
  9   10          sql_patch varchar2(8) path 'sqlPatch',
 11          rollbackable varchar2(8) path 'rollbackable'
 12      ) x;
with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
                  *
ERROR at line 1:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 1970
ORA-06512: at "SYS.DBMS_QOPATCH", line 1292

OOOpsss. I need to check what is in MOS on it.

Those two may be a good trace:
Queryable Patch Inventory – Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table (Doc ID 1602089.1)
Applied Patch Information Is Not Written To Alert.log On Oracle Restart Environment (Doc ID 2114690.1)

It seems like there may be something about the privileges of grid and oracle for the directory $ORACLE_HOME/QOPatch as the latter states there can be problems to read the inventory if the database has been started using srvct. Mine was started using sqlplus, so that’s not the case.

But, lets check the directory

[oracle@server.dba24.pl /]$ cd $ORACLE_HOME/QOPatch
[oracle@server.dba24.pl QOpatch]$ ls -altr
total 16
-r-xr-xr--  1 oracle oinstall 1372 Apr  7  2013 qopiprep.bat
-rw-r--r--  1 oracle oinstall  120 May 27 19:04 qopatch.log
drwxr-xr-x 72 oracle oinstall 4096 Jul 14 01:04 ..
drwxr-xr-x  2 oracle oinstall   65 Jul 14 19:52 .
-rw-r--r--  1 oracle oinstall 2127 Jul 14 19:54 qopatch_log.log

Looks quite ok and we can even see the qopatch_log.log has been modified. What is strange is that on RAC it runs without a problem, and the access rights are the same. I suppose it has been implemented in a different way on RAC comparing to Oracle Restart.

The note 2114690.1 suggests to change the directory privs to 775 (755 as of now). I am not convinced if this is going to help but want to try.

[oracle@server.dba24.pl tmp]$ chmod 775 /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/
[oracle@server.dba24.pl tmp]$ sqlplus sys@CDB1 as sysdba

SYS@CDB1> set linesize 500
SYS@CDB1> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.*
  2    3      from a,
  4           xmltable('InventoryInstance/patches/*'
  5           passing a.patch_output
  6           columns
  7          patch_id number path 'patchID',
  8          patch_uid number path 'uniquePatchID',
  9          description varchar2(80) path 'patchDescription',
 10          sql_patch varchar2(8) path 'sqlPatch',
 11          rollbackable varchar2(8) path 'rollbackable'
 12      ) x;

  PATCH_ID  PATCH_UID DESCRIPTION                                                                      SQL_PATC ROLLBACK
---------- ---------- -------------------------------------------------------------------------------- -------- --------
  18371441   19556146                                                                                  false    true
  22674709   20057886 Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)                  true     true
  22502555   19941482 OCW Patch Set Update : 12.1.0.2.160419 (22502555)                                false    true
  22291127   19694308 Database Patch Set Update : 12.1.0.2.160419 (22291127)                           true     true
  21948354   19553095 Database Patch Set Update : 12.1.0.2.160119 (21948354)                           true     true
  21359755   19194568 Database Patch Set Update : 12.1.0.2.5 (21359755)                                true     true
  20831110   18977826 Database Patch Set Update : 12.1.0.2.4 (20831110)                                true     true
  20299023   18703022 Database Patch Set Update : 12.1.0.2.3 (20299023)                                true     true
  19769480   18350083 Database Patch Set Update : 12.1.0.2.2 (19769480)                                true     true

9 rows selected.

Well, it helped, but why? The directory owner is oracle, I am starting database as oracle not as grid (has). It should be enough. Maybe it has something to do with the fact that that the connection is being made to the listener running as grid user, and the listener invokes the server process – it causes some privileges mismatch – not sure but let’s check.

First I revert the privileges to 755 (default) for the QOPatch directory and then try to connect locally avoiding using tns and a listener

[oracle@server.dba24.pl tmp]$ chmod 755 /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/
[oracle@server.dba24.pl tmp]$ sqlplus / as sysdba

SYS@CDB1> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2  select x.*
  3      from a,
  4           xmltable('InventoryInstance/patches/*'
         passing a.patch_output
  5    6           columns
  7          patch_id number path 'patchID',
  8          patch_uid number path 'uniquePatchID',
  9          description varchar2(80) path 'patchDescription',
 10          sql_patch varchar2(8) path 'sqlPatch',
 11          rollbackable varchar2(8) path 'rollbackable'
 12      ) x;

9 rows selected.

SYS@CDB1> set linesize 500
SYS@CDB1> /

  PATCH_ID  PATCH_UID DESCRIPTION                                                                      SQL_PATC ROLLBACK
---------- ---------- -------------------------------------------------------------------------------- -------- --------
  18371441   19556146                                                                                  false    true
  22674709   20057886 Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)                  true     true
  22502555   19941482 OCW Patch Set Update : 12.1.0.2.160419 (22502555)                                false    true
  22291127   19694308 Database Patch Set Update : 12.1.0.2.160419 (22291127)                           true     true
  21948354   19553095 Database Patch Set Update : 12.1.0.2.160119 (21948354)                           true     true
  21359755   19194568 Database Patch Set Update : 12.1.0.2.5 (21359755)                                true     true
  20831110   18977826 Database Patch Set Update : 12.1.0.2.4 (20831110)                                true     true
  20299023   18703022 Database Patch Set Update : 12.1.0.2.3 (20299023)                                true     true
  19769480   18350083 Database Patch Set Update : 12.1.0.2.2 (19769480)                                true     true

9 rows selected.

So my main suspect is listener and the way it ivokes oracle server process. Let’s investigate how it looks like both with local and listener connection.

Local connection without a listener

[oracle@server.dba24.pl tmp]$ sqlplus / as sysdba
SYS@CDB1> select spid from v$process where addr=(select paddr from v$session where sid=sys_context('USERENV','SID'));

SPID
------------------------
93407

[oracle@server.dba24.pl QOpatch]$  pidstat -p 93407 1
SYS@CDB1> host pidstat -p 93407 1
Linux 3.10.0-229.14.1.el7.x86_64 (server)     07/14/2016      _x86_64_        (16 CPU)

12:49:03 PM   UID       PID    %usr %system  %guest    %CPU   CPU  Command
12:49:04 PM  <strong>2001</strong>     93407    0.00    0.00    0.00    0.00    12  oracle_93407_cd

SYS@CDB1> host ps -eo euser,ruser,suser,fuser,pid | grep 93407
oracle   oracle   oracle   oracle    93407

Effective user is oracle, real user is oracle (uid 2001)

The same test with connection through the listener

[oracle@server.dba24.pl tmp]$ sqlplus sys@CDB1 as sysdba

SYS@CDB1> select spid from v$process where addr=(select paddr from v$session where sid=sys_context('USERENV','SID'));

SPID
------------------------
93039

SYS@CDB1> pidstat -p 93039 1
SYS@CDB1> host pidstat -p 93039 1
Linux 3.10.0-229.14.1.el7.x86_64 (server)     07/14/2016      _x86_64_        (16 CPU)

12:46:26 PM   UID       PID    %usr %system  %guest    %CPU   CPU  Command
12:46:27 PM  2002     93039    0.00    0.00    0.00    0.00     7  oracle_93039_cd

SYS@CDB1> host ps -eo euser,ruser,suser,fuser,pid | grep 93039
oracle   grid     oracle   oracle    93039

As you can see the real user is grid now (2002), so the connection through the listener invokes oracle process with as real user grid. Since the process is being invoked by running the oracle binaries with setuid set as follows:

[oracle@server.dba24.pl tmp]$ ls -l /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 311162767 May 27 09:54 /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

It shouldn’t have problems during writing anything to the $ORACLE_HOME/QOPatch directory. One of the proof is that the qopatch_log.log is being updated no matter what the real user is for the process.

I decided to investigate more with strace to check what happens – what kind of system calls to the filesystem fails. I will use strace and run the SQL command once again with already established local and listener connections

[root@server.dba24.pl tmp]$ strace -p93407 -ff -e trace=open,access,stat -o /tmp/straceLOCAL.log
[root@server.dba24.pl tmp]$ strace -p93039 -ff -e trace=open,access,stat -o /tmp/straceLISTENER.log

Most important lines for local connection:

open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat", O_RDONLY) = 3
open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/stout.txt", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
stat("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/xml_file.xml", 0x7f61f3b44f60) = -1 ENOENT (No such file or directory)
stat("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/xml_file.xml", 0x7f61f3b44f60) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/xml_file.xml", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 57
open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/xml_file.xml", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/xml_file.xml", O_RDONLY) = 3

Most important lines for listener connection:

open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat", O_RDONLY) = 3
open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/stout.txt", O_WRONLY|O_CREAT|O_APPEND, 0666) = -1 EACCES (Permission denied)
open("/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/xml_file.xml", O_WRONLY|O_CREAT|O_APPEND, 0666) = -1 EACCES (Permission denied)

It may seem that the /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat is run and it uses the the two files stout.txt and xml_file.xml as temporary files to service the oracle process request. Let’s take a look inside the qopiprep.bat

#!/bin/sh
#
# $Header: rdbms/src/client/tools/qpinv/qopiprep.bat /unix/2 2013/04/04 20:59:27 tbhukya Exp $
#
# qopiprep.bat
#
# Copyright (c) 2012, 2013, Oracle and/or its affiliates. All rights reserved.
#
#    NAME
#      qopiprep.bat - bat file for preprocessor
#
#    DESCRIPTION
#      bat file for external table preprocessor.
#
#    NOTES
#      .
#
#    MODIFIED   (MM/DD/YY)
#    tbhukya     04/03/13 - Bug 16226172 : Forward merge of fix 16076845
#    tbhukya     09/23/12 - Creation
#
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH

# Option: "-retry 0" avoids retries in case of locked inventory.

# Option: "-invPtrLoc" is required for non-central-inventory
# locations. $OPATCH_PREP_LSINV_OPTS which may set by users
# in the environment to configure special OPatch options
# ("-jdk" is another good candidate that may require configuration!).

# Option: "-all" gives information on all Oracle Homes
# installed in the central inventory.  With that information, the
# patches of non-RDBMS homes could be fetched.


$ORACLE_HOME/OPatch/opatch lsinventory -xml  $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $
ORACLE_HOME/QOpatch/stout.txt
`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`
echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`
rm $ORACLE_HOME/QOpatch/xml_file.xml
rm $ORACLE_HOME/QOpatch/stout.txt

The script creates the files and removes them at the end of the script.

Do you wonder what may be the cause of this behaviour when the script behaves in a different way while run as realuser grid or oracle. I think this is because scripts cannot aquire setuid privilege on the latests Linux versions as it was not too secure. Setuid applies only to the binaries. Even if effective user of the oracle server process is oracle it may be not enough for the script being invoked underneath.

Oracle suggests to set the privs to the 775 for the QOPatch directory to work properly and this is the easiest workaround as of now.


What do you think? Do you have better explanation?

Nice to read documents that helped me in performing the investigation:

About the author

 
maciej tokar
Maciej Tokar

An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU

 
View Maciej Tokar's profile on LinkedIn         logoDB4
LinkedIn Auto Publish Powered By : XYZScripts.com