Today I stumbled about a very strange error while applying a release update to a 19c database. Datapatch fails with an ORA-27476 error. In this blog post, I want to show how to get rid of this error and how to identify objects in the database which may cause it.
Table of Contents
Error
First, let us start with the error message that caused datapatch to fail – I removed some lines. Please have a look at the highlighted lines.
$> $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.13.0.0.0 Production on Sat Jan 29 23:05:21 2022
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_131379_2022_01_29_23_05_21/sqlpatch_invocation.log
Connecting to database...OK
...
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 33192793 apply (pdb PDB01): WITH ERRORS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_CDB01_PDB01_2022Jan29_23_05_56.log (errors)
-> Error at line 76: script rdbms/admin/backport_files/bug_33109335_apply.sql
- ORA-27476: "APP_ADMIN"."MAINJOB" does not exist
- ORA-06512: at "SYS.DBMS_ISCHED", line 3229
- ORA-06512: at "SYS.DBMS_SCHEDULER", line 2966
- ORA-06512: at line 1
- ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
- ORA-06512: at line 23
- ORA-06512: at line 23
Please refer to MOS Note 1609718.1 and/or the invocation log
/u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_131379_2022_01_29_23_05_21/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Sat Jan 29 23:07:39 2022
Analysis
A scheduler job is causing the issue. Let us check the scheduler jobs of the user in the PDB.
SQL> SELECT job_name, job_type, enabled FROM dba_scheduler_jobs WHERE owner = 'APP_ADMIN' ORDER BY job_name;
JOB_NAME JOB_TYPE ENABL
------------------------------ ---------------- -----
DBMS_JOB$_121 PLSQL_BLOCK TRUE
MainJob PLSQL_BLOCK TRUE
Oh great, someone created a scheduler job with a mix of the upper and lower case. And of course, when datapatch tries to find the job MAINJOB it cannot find it.
Solution
The easiest way to fix this issue is to rename or recreate the scheduler job. I will use the rename approach to show how to do this.
Scheduler jobs can be renamed using the RENAME command. I don’t have the password of the APP_ADMIN user, so I will use my SYS user.
SQL> ALTER SESSION SET CURRENT_SCHEMA = APP_ADMIN;
SQL> RENAME "MainJob" TO "MAINJOB";
RENAME "MainJob" TO "MAINJOB"
*
ERROR at line 1:
ORA-03001: unimplemented feature
Unfortunately, the RENAME command does only work when a direct connection to the owner of the object is established.
To work around this issue a PL/SQL procedure is created.
SQL> CREATE OR REPLACE PROCEDURE rename_job (
p_old_name IN VARCHAR2,
p_new_name IN VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE 'RENAME "' || p_old_name || '" TO "' || p_new_name || '"';
END;
/
Now rename the job. No schema switch is required. Just run it with the SYS user.
SQL> EXEC RENAME_JOB(p_old_name => 'MainJob', p_new_name => 'MAINJOB');
After the job was renamed, the datapatch operation succeeded.
...
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 33192793 apply (pdb PDB01): SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_CDB01PDB01_2022Jan29_23_24_41.log (no errors)
SQL Patching tool complete on Sat Jan 29 23:26:04 2022
Oracle Bug 33540746 – Datapatch fails error: – ORA-20001: comma-separated list invalid near h/s/st() (Doc ID 33540746.8) addresses a slighty different issue, but the provided solution should also fix the above issue. This bug fix adds double quotation marks to the rdbms/admin/backport_files/bug_33109335_apply.sql script.
Identify Issues
Use the following query to identify scheduler jobs with lower case characters.
SQL> SELECT owner, job_name FROM dba_scheduler_jobs WHERE REGEXP_LIKE(job_name, '[a-z]+');
OWNER JOB_NAME
------------------------------ ------------------------------
APP_ADMIN MainJob