Problem with datapatch, sqlpatch_bootstrap.sql and obj$

(Picture by Hermes Rivera, via Unsplash)

EDIT : This bug has finally been fixed by patch 28809007 and should be included with release 20.1 🙂 !

This blog post describes a very specific problem I encountered while using datapatch.

I recently patched 400+ 12.1.0.2 databases on RHEL 7.5 with Database Proactive Bundle Patch + OJVM PSU 12.1.0.2.180717 as described in this previous blog post.

Everything went very well on all databases, except one, maybe one of the most critical databases because it is a repository centralizing information about all databases in our ecosystem.
Datapatch would not work properly and would output the following error :

SQL Patching tool version 12.1.0.2.0 Production on Mon Oct  8 15:25:07 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Connecting to database...OK
Bootstrapping registry and package to current versions...done
  Error in bootstrap log /ccv/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27945_2018_10_08_15_25_07/bootstrap1_SOMEDB.log:
    Error at line 31: ORA-01427: single-row subquery returns more than one row

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/ccv/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27945_2018_10_08_15_25_07/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Mon Oct  8 15:25:09 2018

My colleague Dirk Geppert found out that the error came from this query in $ORACLE_HOME/sqlpatch/sqlpatch_bootstrap.sql at line 286 :

select obj#
from obj$
where name = 'REGISTRY$SQLPATCH'
and owner# = 0 ;

Actually this is a subquery, and it is expected to return only 1 row :

select type#
into current_type
from col$
where obj# = (select obj#
              from obj$
              where name = 'REGISTRY$SQLPATCH'
              and owner# = 0)
and name = 'BUNDLE_DATA';

But in our case, we have … 271 rows, with CTIME=2016-12-20 and REMOTEOWNER=SOME_SCHEMA instead of NULL. Why ?

This obj$ table is populated with way more rows than we thought.

After a lot of research, my colleague came across MOS note 1294034.1, and figured out that this table was populated whenever an object was queried through a database link within a procedure.
Maybe we had an old administration job that queried REGISTRY$SQLPATCH on several databases through database links ? Exactly … This is what we had back in the days, even though this job has been modified to request DBA_REGISTRY_SQLPATCH instead.

Let’s test this presumed behaviour :

• Create a database link named SOME_DBLINK :

create database link some_dblink connect to flora identified by flora using 'some_remote_db';

• Query USER_OBJECTS on another database through this database link :

declare
  i     integer;
begin
  select count(*) into i
  from user_objects@some_dblink ;
end;
/

• Check in obj$ to see if a new record has been inserted :

select name,remoteowner,ctime
from sys.obj$
where ctime>sysdate-1/24
order by ctime desc;

NAME            REMOTEOWNER  CTIME
--------------------------------------
USER_OBJECTS    PUBLIC       08-OCT-18
USER_OBJECTS    SYS          08-OCT-18
USER_OBJECTS    FLORA        08-OCT-18

• Now, let’s try with an inexistant table on the target database :

declare
  i     integer;
begin
  select count(*) into i
  from inexistant_table@some_dblink ;
end;
/

Error starting at line : 1 in command -
declare
  i     integer;
begin
  select count(*) into i
  from inexistant_table@some_dblink ;
end;
Error report -
ORA-06550: line 5, column 8:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

• It generates an error of course, but let’s check in obj$ again :

select name,remoteowner,ctime
from sys.obj$
where ctime>sysdate-1/24
order by ctime desc;

NAME              REMOTEOWNER  CTIME
----------------------------------------
INEXISTANT_TABLE  FLORA        08-OCT-18
INEXISTANT_TABLE  PUBLIC       08-OCT-18
USER_OBJECTS      SYS          08-OCT-18
USER_OBJECTS      PUBLIC       08-OCT-18
USER_OBJECTS      FLORA        08-OCT-18

The record with INEXISTANT_TABLE is still inserted in obj$ … So I can assume if we query REGISTRY$SQLPATCH, it will be recorded as well 😉

• Let’s try this :

declare
  numroles     integer;
begin
  select count(*) into numroles
  from sys.registry$sqlpatch@some_dblink ;
end;
/

• And check in obj$ :

select name,remoteowner,ctime
from sys.obj$
where ctime>sysdate-1/24
order by ctime desc;

NAME               REMOTEOWNER  CTIME
-----------------------------------------
REGISTRY$SQLPATCH  SYS          08-OCT-18
INEXISTANT_TABLE   PUBLIC       08-OCT-18
INEXISTANT_TABLE   FLORA        08-OCT-18
USER_OBJECTS       PUBLIC       08-OCT-18
USER_OBJECTS       SYS          08-OCT-18
USER_OBJECTS       FLORA        08-OCT-18

At this stage, we understood why the query from $ORACLE_HOME/sqlpatch/sqlpatch_bootstrap.sql, called by datapatch, returned an error.
So we decided to try the following steps on a test database …

Please never ever ever ever do this on a production database without proper instructions from Oracle.

• Delete the following lines in obj$ :

delete
from sys.obj$
where
name = 'registry$sqlpatch'
and owner# = 0
and remoteowner is not null ;

• Run datapatch again :

SQL Patching tool version 12.1.0.2.0 Production on Mon Oct  8 15:49:27 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    27923320 (Database PSU 12.1.0.2.180717, Oracle JavaVM Component (JUL2018))
    27547374 (DATABASE BUNDLE PATCH 12.1.0.2.180717)

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...done
SQL Patching tool complete on Mon Oct  8 15:51:24 2018

And now it works !

Maybe this query used by datapatch through sqlpatch_bootstrap.sql should have an additional filter in order to return only local objects in obj$ ? But modifying this query in sqlpatch_bootstrap.sql is not sufficient : other routines invoked by the patching process make similar calls, resulting in the same multi-/one-row problem caused by the entries in obj$.

In any case, the “delete-solution” above (which is actually not a solution at all) is not a good one : After deleting the rows in obj$, a new row cannot be created again using the same procedure. It suggests that an entry in some other table is maybe created first, which then creates the row in obj$ and when the obj$ row is deleted, a subsequent attempt to create it again does not succeed, because some other table indicates that it has been done before and already exists. The check is not done in obj$.

I opened a Service Request regarding this issue, I’ll let you know how it goes 🙂