Datapatch fails with ORA-27476

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.

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

References

Leave a Reply

Your email address will not be published.