处理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.



      本文转自hbxztc 51CTO博客,原文链接:http://blog.51cto.com/hbxztc/1873706,如需转载请自行联系原作者





相关文章
|
6月前
|
SQL Oracle 关系型数据库
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database (
88 2
|
Oracle 关系型数据库
Creating ASMSNMP User reports ORA-15306
In this Document  Symptoms   Cause   Solution Applies to: Oracle Server - Enterprise Edition - Version: 11.
1365 0
|
SQL 关系型数据库 Oracle
ORA-01466: unable to read data - table definition has changed
1. Oracle建议我们等待大约5分钟之后再进行flashback query新创建的表,否则可能会碰到这个错误ORA-01466: unable to read data - table definition has changed.
1788 0
|
Oracle 关系型数据库
Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (DOC ID 2335265.1)
Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (DOC ID 2335265.
2804 0
|
Oracle 关系型数据库