ORA-04064: not executed, invalidated package body “GOLDENGATE.DDLREPLICATION” 解决

在处理OGG相关的表时由于操作失误造成如下错误：ADMIN@bl1> create table t as select * from dba_jobs;create table t as select * from dba_jobs *ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-04045: errors during recompilation/revalidation ofGOLDENGATE.DDLREPLICATIONORA-04064: not executed, invalidatedORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"ORA-06508: PL/SQL: could not find program unit being called:"GOLDENGATE.DDLREPLICATION"ORA-06512: at line 60ORA-06508: PL/SQL: could not find program unit being called:"GOLDENGATE.DDLREPLICATION"ORA-06512: at line 60ADMIN@bl1> alter package GOLDENGATE.DDLREPLICATION COMPILE BODY ;alter package GOLDENGATE.DDLREPLICATION COMPILE BODY*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-04045: errors during recompilation/revalidation ofGOLDENGATE.DDLREPLICATIONORA-04064: not executed, invalidatedORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"ORA-06508: PL/SQL: could not find program unit being called:"GOLDENGATE.DDLREPLICATION"ORA-06512: at line 60ORA-06508: PL/SQL: could not find program unit being called:"GOLDENGATE.DDLREPLICATION"ORA-06512: at line 60 处理方法：重新删除并重建OGG的DDL支持。SYS@bl1> @ddl_disableTrigger altered.SYS@bl1> @ddl_remove.sqlDDL replication removal script.WARNING: this script removes all DDL replication objects and data.You will be prompted for the name of a schema for the Oracle GoldenGate database objects.NOTE: The schema must be created prior to running this script.Enter Oracle GoldenGate schema name:goldengateWorking, please wait ...Spooling to file ddl_remove_spool.txtScript complete.SYS@bl1> @marker_remove.sqlMarker removal script.WARNING: this script removes all marker objects and data.You will be prompted for the name of a schema for the Oracle GoldenGate database objects.NOTE: The schema must be created prior to running this script.Enter Oracle GoldenGate schema name:goldengatePL/SQL procedure successfully completed.Sequence dropped.Table dropped.Script complete.SYS@bl1> @marker_setup.sqlMarker setup scriptYou will be prompted for the name of a schema for the Oracle GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter Oracle GoldenGate schema name:goldengateMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATEMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OKScript complete.SYS@bl1> @ddl_setup.sqlOracle GoldenGate DDL Replication setup scriptVerifying that current user has privileges to install DDL Replication...You will be prompted for the name of a schema for the Oracle GoldenGate database objects.NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter Oracle GoldenGate schema name:goldengateWorking, please wait ...Spooling to file ddl_setup_spool.txtChecking for sessions that are holding locks on Oracle Golden Gate metadata tables ...Check complete.WARNING: Tablespace GOLDENGATE does not have AUTOEXTEND enabled.Using GOLDENGATE as a Oracle GoldenGate schema name.Working, please wait ...DDL replication setup script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATECLEAR_TRACE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsCREATE_TRACE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsTRACE_PUT_LINE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsINITIAL_SETUP STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsDDLREPLICATION PACKAGE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsDDL IGNORE TABLE-----------------------------------OKDDL IGNORE LOG TABLE-----------------------------------OKDDLAUX PACKAGE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsDDLAUX PACKAGE BODY STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsSYS.DDLCTXINFO PACKAGE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsSYS.DDLCTXINFO PACKAGE BODY STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsDDL HISTORY TABLE-----------------------------------OKDDL HISTORY TABLE（1）-----------------------------------OKDDL DUMP TABLES-----------------------------------OKDDL DUMP COLUMNS-----------------------------------OKDDL DUMP LOG GROUPS-----------------------------------OKDDL DUMP PARTITIONS-----------------------------------OKDDL DUMP PRIMARY KEYS-----------------------------------OKDDL SEQUENCE-----------------------------------OKGGS_TEMP_COLS-----------------------------------OKGGS_TEMP_UK-----------------------------------OKDDL TRIGGER CODE STATUS:Line/pos Error-------------------- -----------------------------------------------------------------No errors No errorsDDL TRIGGER INSTALL STATUS-----------------------------------OKDDL TRIGGER RUNNING STATUS----------------------------------------------------------------------ENABLEDSTAYMETADATA IN TRIGGER----------------------------------------------------------------------OFFDDL TRIGGER SQL TRACING----------------------------------------------------------------------0DDL TRIGGER TRACE LEVEL----------------------------------------------------------------------0LOCATION OF DDL TRACE FILE------------------------------------------------------------------------------------------------------------------------/opt/oracle/app/admin/bl1/udump/ggs_ddl_trace.logAnalyzing installation status...STATUS OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------SUCCESSFUL installation of DDL Replication software componentsScript complete.SYS@bl1> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. （Do not run the script.）You will be prompted for the name of a schema for the GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter GoldenGate schema name:goldengateWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:GRANT GGS_GGSUSER_ROLE TO <loggedUser>where <loggedUser> is the user assigned to the GoldenGate processes.SYS@bl1> GRANT GGS_GGSUSER_ROLE TO goldengate;Grant succeeded.SYS@bl1> @ddl_enableTrigger altered.???多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址