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.
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 126.96.36.199.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
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.
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.
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