Wednesday, November 15, 2017

Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table

After upgrading single instance oracle restart setup to 12.2 from 11.2.0.4 following is observed in the alert log
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247
Checking in the $ORACLE_HOME/QOpatch/qopatch_log.log showed
KUP-05007:   Warning: Intra source concurrency disabled because the preprocessor option is being used.

Field Definitions for table OPATCH_XML_INV
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source:

    XML_INVENTORY                   CHAR (100000000)
      Terminated by "UIJSVTBOEIZBEFFQBL"
      Trim whitespace same as SQL Loader
KUP-04095: preprocessor command /opt/app/oracle/product/12.2.0/dbhome_1/QOpatch/qopiprep.bat encountered error "/opt/app/oracle/product/12.2.0/dbhome_1/QOpatch/qopiprep.bat: 
line 55: /opt/app/oracle/product/12.2.0/dbhome_1/rdbms/log/stout_std11g2.txt: Permission denied
As per 1602089.1 this is due to running the command with a different user who doesn't have permission on $ORACLE_HOME/QOpatch folder. But in this case it was not ture, the DB start was done by oracle user who has permissions on the QOPatch folder. Even then the same error was shown on alert log.



This is a role separate single instance setup and GI is owned by grid user. As mentioned in the password store post, in single instance setup with role separation has the problem of certain commands being run as grid user instead of oracle user. In this case the grid user doesn't have write permission on $ORACLE_HOME/rdbms/log folder which has 755 as the permission. That's the root cause of the issue. Change this log directory permission to 775 so that grid user is able to write to it. After this during the start up the full patch list is shown on the alert log.
2017-10-20T14:46:26.105603+01:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 26710464
Patch Description: Database Release Update : 12.2.0.1.171017 (26710464)
Patch Apply Time: 2017-10-20T13:05:21+01:00
Bugs Fixed: 14690846,17027695,17533661,19285025,19327292,19614243,20003668,
20324049,20620169,20736227,21159907,21186167,21981529,21985256,22072543,
22087683,22179537,22446455,22503297,22568728,22594071,22628825,22645009,
22654475,22729345,22898198,22950945,22981722,23026585,23035249,23055900,
23061453,23125560,23151677,23179662,23234232,23300142,23481673,23491861,
23499160,23521523,23527363,23548817,23581777,23599216,23665623,23730961,
23733981,23735292,23746128,23749454,24289874,24326846,24332831,24334708,
24336249,24341675,24368004,24376875,24376878,24385983,24421668,24425998,
24457597,24485161,24485174,24509056,24534401,24555417,24556967,24560906,
24573817,24578718,24578797,24589590,24609996,24624166,24642495,24655717,
24664211,24668398,24674955,24676172,24677696,24693290,24714096,24717183,
24735430,24737064,24737403,24744686,24792678,24796092,24811725,24812047,
24827228,24831514,24835919,24850622,24907917,24908321,24912588,24923215,
24929210,24938784,24942749,24960044,24968162,24976007,25029022,25034396,
...
...
The issue was only observed in single instance role separated setup. In role separated RAC configuration there wasn't any permission issues.

Useful metalink notes
Queryable Patch Inventory - Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table [ID 1602089.1]