(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+ 18.104.22.168 databases on RHEL 7.5 with Database Proactive Bundle Patch + OJVM PSU 22.214.171.124.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 126.96.36.199.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
REMOTEOWNER=SOME_SCHEMA instead of
NULL. Why ?
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
Let’s test this presumed behaviour :
- Create a database link named
create database link some_dblink connect to flora identified by flora using 'some_remote_db';
USER_OBJECTSon 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
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
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
delete from sys.obj$ where name = 'registry$sqlpatch' and owner# = 0 and remoteowner is not null ;
- Run datapatch again :
SQL Patching tool version 188.8.131.52.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 184.108.40.206.180717, Oracle JavaVM Component (JUL2018)) 27547374 (DATABASE BUNDLE PATCH 220.127.116.11.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
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
I opened a Service Request regarding this issue, I’ll let you know how it goes 🙂
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.
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).