Database Upgrade Failed Due to ORA-04045: errors during recompilation/revalidation of DDL_TRIGGER

 Database Upgrade Failed Due to ORA-04045: errors during recompilation/revalidation of DDL_TRIGGER (Doc ID 2423932.1)

SYMPTOMS

Oracle Database Upgrade Failed with below Error :- 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ORA-04045: errors during recompilation/revalidation of LOG_AND_BLOCK_DDL_TRIGGER

ORA-01031: insufficient privileges

ORA-06512: at “SYS.DBMS_UTILITY”,line 1283

ORA-06512: at line 20

LOG_AND_BLOCK_DDL_TRIGGER ORA-01031: insufficient privileges

ORA-06512 : at “SYS.DBMS_UTILITY”, line 1283

ORA-06512: at line 20

ORA-01031: insufficient privileges ORA-06512: at “SYS.DBMS_UTILITY”, line 1283

ORA-06512: at line 20

ORA-06512: at “SYS.DBMS_UTILITY”, line 1283 ORA-06512: at line 20

ORA-06512: at line 20

 

  

CHANGES

 Database Upgrade from 11.2.0.4 to 12.2.0.1

CAUSE

Privileges Issue for system User

05:58:36 SQL> declare

05:58:36 2 cursor ora_dict_synonyms is

05:58:36 3 select o.object_id from dba_objects o

05:58:36 4 where o.owner = ‘PUBLIC’

05:58:36 5 and o.object_type = ‘SYNONYM’

05:58:36 6 and o.object_name like ‘ORA_%’;

05:58:36 7

05:58:36 8 cursor ddl_triggers is

05:58:36 9 select o.object_id from dba_triggers t, dba_objects o

05:58:36 10 where t.owner = o.owner and t.trigger_name = o.object_name

05:58:36 11 and o.object_type = ‘TRIGGER’

05:58:36 12 and (t.triggering_event like ‘%ALTER%’ or

05:58:36 13 t.triggering_event like ‘ÝL%’);

05:58:36 14 begin

05:58:36 15 for s in ora_dict_synonyms loop

05:58:36 16 dbms_utility.validate(s.object_id);

05:58:36 17 end loop;

05:58:36 18

05:58:36 19 for t in ddl_triggers loop

05:58:36 20 dbms_utility.validate(t.object_id);

05:58:36 21 end loop;

05:58:36 22 end;

05:58:36 23 /

declare

*

ERROR at line 1:

ORA-04045: errors during recompilation/revalidation of  DDL_TRIGGER

ORA-01031: insufficient privileges

ORA-06512: at “SYS.DBMS_UTILITY”, line 1283

ORA-06512: at line 20

 

SOLUTION

 

The upgrade documents precisely states below in the section "Requirements and recommendations for Source database"

"Disable any custom triggers that would fire before/after DDL and enable them after the upgrade is complete."

It is strongly recommended to abide by the pre-upgrade tasks as mentioned in the upgrade document.

 

TRIGGERS Should be disabled Prior to Upgrade.

1. We should disable all the triggers before staring the upgrade

To Disable :-

~~~~~~~~~~~~~~

SQL> SELECT ‘ALTER TRIGGER ‘ || owner || ‘.’ || trigger_name || ‘ DISABLE;’ “ALTER Statement” FROM dba_triggers where TRIGGER_TYPE in (‘BEFORE EVENT’,’AFTER EVENT’,’AFTER STATEMENT’);

Example :-

alter trigger XXXXXXXXX disable;

To Enable:-

~~~~~~~~~~~~~

SQL> SELECT ‘ALTER TRIGGER ‘ || owner || ‘.’ || trigger_name || ‘ ENABLE;’ “ALTER Statement” FROM dba_triggers where TRIGGER_TYPE in (‘BEFORE EVENT’,’AFTER EVENT’,’AFTER STATEMENT’);

Example :- 

alter trigger XXXXXXXXX ENABLE;

Or

2. Provide below Privileged

SQL> GRANT ADMINISTER DATABASE TRIGGER to <USER>;

Prior to upgrade to restart the Upgrade .

or you can retry to continue the upgrade

Refer also (Doc ID 2423932.1) for more information