Problem with datapatch, sqlpatch_bootstrap.sql and obj$

hermes-rivera-471873-unsplash

(Picture by Hermes Rivera, via Unsplash)

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 šŸ™‚

Advertisements

2 thoughts on “Problem with datapatch, sqlpatch_bootstrap.sql and obj$

  1. Hi Flora,
    Very good investigation. You can link your post in the SR and they have all analysis already done.
    By the way, I hope that they will completely review their datapatch queries. Supposing that (owner#,name) is unique is completely wrong. You discovered the remoteowner must be included. There’s also the namespace to include because many object types can have the same name as an existing table.
    Cheers,
    Franck.

    Like

    1. Hi Franck šŸ™‚
      Thank you for your inputs ! I included my blog post in the SR šŸ˜‰
      Yes, I suppose several queries and procedures must be revised, as we encountered cascading erros when we modified only the first obvious query in the SQL script (for troubleshooting purposes of course).

      Like

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s