处理ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLIC..

321231255

处理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 1
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-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 60
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 60
ADMIN@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 1
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-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 60
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 60

处理方法：重新删除并重建OGG的DDL支持。
SYS@bl1> @ddl_disable
Trigger altered.
SYS@bl1> @ddl_remove.sql
DDL 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:goldengate
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script complete.
SYS@bl1> @marker_remove.sql
Marker 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:goldengate
PL/SQL procedure successfully completed.
Sequence dropped.
Table dropped.
Script complete.
SYS@bl1> @marker_setup.sql
Marker setup script
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.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SYS@bl1> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying 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:goldengate
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking 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 GOLDENGATE
CLEAR_TRACE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
CREATE_TRACE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
TRACE_PUT_LINE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
INITIAL_SETUP STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX  PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
SYS.DDLCTXINFO  PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
SYS.DDLCTXINFO  PACKAGE BODY STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/opt/oracle/app/admin/bl1/udump/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SYS@bl1> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To 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:goldengate
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant 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_enable
Trigger altered.