ORA-04045: errors during recompilation/revalidation of GG_USER.DDLREPLICATION

DB version: 11.2.0.4

OS:  RHEL 6

GG version :  11.2.1.0.3

For any DDL operation performed in the database I’m hitting the below error

EX:

SQL> ALTER TABLE USER.TEST MODIFY (emp_id VARCHAR2(100) );

ALTER TABLE USER.TEST MODIFY (emp_id VARCHAR2(100) )

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04045: errors during recompilation/revalidation of GGS_ADMIN.DDLREPLICATION

ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-06512: at line 1100

ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-06512: at line 977

ORA-04045: errors during recompilation/revalidation of GGS_ADMIN.DDLREPLICATION

ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-06512: at line 1100

ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-04045: errors during recompilation/revalidation of GGS_ADMIN.DDLREPLICATION

ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-06512: at line 1100

ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGS_ADMIN.DDLREPLICATION"

And I see there are few invalid objects in the database in which I see goldengate package too

SQL> select count(*) from  dba_objects where status='INVALID';

COUNT(*)

----------

        35

SQL> select owner,object_name,object_type,status from dba_objects where object_name='DDLREPLICATION';

OWNER                                 OBJECT_NAME            OBJECT_TYPE                                               STATUS

--------------------------------- ----------------------    -----------------------------------------------         ----------------------

GGS_ADMIN                         DDLREPLICATION         PACKAGE BODY                                         INVALID

GGS_ADMIN                         DDLREPLICATION         PACKAGE                                                     VALID

Tried to run the @?/rdbms/admin/utlrp.sql” package but same error .

Solution:

DISABLE “GGS_DDL_TRIGGER_BEFORE” trigger and run the utlrp script to make objects validate and then enable back the DDL trigger

SQL> select trigger_name,status,action_type from dba_triggers where owner='SYS' and trigger_name='GGS_DDL_TRIGGER_BEFORE';

TRIGGER_NAME                      STATUS                   ACTION_TYPE

--------------------------------- ------------------------ ---------------------------------

GGS_DDL_TRIGGER_BEFORE            ENABLED                  PL/SQL

SQL> alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable ;

Trigger altered.

SQL> select trigger_name,status,action_type from dba_triggers where owner='SYS' and trigger_name='GGS_DDL_TRIGGER_BEFORE';

TRIGGER_NAME                      STATUS                   ACTION_TYPE

--------------------------------- ------------------------ ---------------------------------

GGS_DDL_TRIGGER_BEFORE            DISABLED      PL/SQL

SQL> @?/rdbms/admin/utlrp.sql

SQL> select owner,object_name,object_type,status from dba_objects where object_name='DDLREPLICATION';

OWNER                             OBJECT_NAME            OBJECT_TYPE                                               STATUS

--------------------------------- ---------------------- ------------------------------------------------   -----------------------------

GGS_ADMIN                         DDLREPLICATION         PACKAGE BODY                                          VALID

GGS_ADMIN                         DDLREPLICATION         PACKAGE                                                   VALID

SQL> alter trigger sys.GGS_DDL_TRIGGER_BEFORE enable ;

Trigger altered.

SQL> select trigger_name,status,action_type from dba_triggers where owner='SYS' and trigger_name='GGS_DDL_TRIGGER_BEFORE';

TRIGGER_NAME                      STATUS                   ACTION_TYPE

--------------------------------- ------------------------ ---------------------------------

GGS_DDL_TRIGGER_BEFORE            ENABLED                  PL/SQL

Now I can do all my DDL operations. J

If you still see the same error then again disable the TRIGGER and reinstall the DDL replication package

NOTE:  same thing applies when we do any patching work or running any scripts (catupgrd,catproc,catuppst,utlrp,etc.,)

Refer:

  Do I Need To Disable The GoldenGate DDL Trigger Before An Oracle DB Upgrade or PSU patching? (Doc ID 971222.1)