處理ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"

在處理OGG相關(guān)的表時(shí)由于操作失誤造成如下錯(cuò)誤：

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.