Wednesday, August 5, 2015

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)

4 comments:

Unknown said...

thanks a lot

Nikhil said...

http://oracle-dba-help.blogspot.in/

Unknown said...

Hi sir..i tried to run any operation on my sql prompt like create table..i will give an a error like this....
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYSTEM.DDL_LOG_TRIG
ORA-01031: insufficient privileges

Unknown said...

pls provede sollution for that

Post a Comment

Auto Scroll Stop Scroll