資料庫升級後goldengate報錯，ORA-04045

 

ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

Linux平臺，將資料庫從10.2.0.1.0升級到10.2.0.5.0後報錯： ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

試圖重新編譯該package和package body，均報錯：

SQL> alter package GGATE.DDLREPLICA compile package;

alter package GGATE.DDLREPLICA compile package

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit

SQL> alter package GGATE.DDLREPLICA compile body;

alter package GGATE.DDLREPLICA compile body

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit

SQL> alter package GGATE.DDLREPLICA compile;

alter package GGATE.DDLREPLICA compile

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04045: errors during recompilation/revalidation of GGATE.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called:

"GGATE.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "GGATE.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit

在metlink上找到一篇文件，原來是資料庫上部署了goldenGate 基於ddl的複製，升級前需要先disable 觸發器，然後升級，最後enable 觸發器；

如果沒有按照此步驟來操作並且已經報錯了，可以重新執行相關指令碼，建立包和包體。

附：

Do I Need To Disable The GoldenGate DDL Trigger Before An Oracle DB Upgrade or PSU patching? [ID 971222.1]

In this Document
  
  

Oracle GoldenGate - Version: 4.0.0 and later   [Release: 4.0.0 and later ]
Information in this document applies to any platform.

Issue:
If you are performing any Oracle database upgrades, for example CPU/PSU patch upgrade, and you are currently capturing Oracle DDL operations with GoldenGate, disable the GoldenGate DDL trigger before the upgrade and then enable it after the upgrade. Use the ddl_disable and ddl_enable scripts in the GoldenGate installation directory for this purpose.

Solution Description:
Patches and upgrades can modify Oracle???s internal tables and views. This can cause stored procedures that call them to be invalidated. All dependent objects are invalidated as well.

The ALTER COMPILE is DDL, and because parts of the DDL package are invalidated, it cannot execute. Because Oracle patches and upgrades are generally NOT replicated by GoldenGate (replication is stopped, then upgrades or patches are done on both source and target), there should be no gaps. Also, because upgrades and patches generally operate on Oracle-reserved objects (which GoldenGate filters out), there should not be any patch-related DDL operations that are replicated, even if for some reason replication is not stopped during the patch.

To apply the Oracle patch:

Before applying a database patch to an environment that has an active GoldenGate DDL trigger, follow this procedure:

1. Log into SQL*Plus as sysdba and execute the ddl_disable script.

2. Apply the Oracle patch.

3. If you are still on the same major Oracle version (for example, you went from 9.2.0.6 to 9.2.0.8), enable the trigger again by executing the ddl_enable script as sysdba. If you are upgrading to a newer major version (for example, from 10.1.x to 10.2.x), download and install a new GoldenGate version before re-enabling the DDL trigger.

If you don't follow the procedure of disabling the DDL trigger before upgrade or patching, after the Database version upgrade or CPU/PSU patch complete, you will run into various issues with GoldenGate DDL replication package, typical error messages looks like below

OCI error (4045-ORA-04045: errors during recompilation/revalidation of PUBLIC.ALL_TAB_COLUMNS 

ORA-04064: not executed, invalidated  ORA-04064: not executed, invalidated package body "GGS_DDL.DDLREPLICATION" ORA-06508: PL/SQL: could not find program unit being called: "GGS_DDL.DDLREPLICATION" ORA-06512: at line 837 ORA-04064: not executed, invalidated package body "GGS_DDL.DDLREPLICATION" ORA-06508: PL/SQL: could not find program unit being called: "GGS_DDL.DDLREPLICATION" ORA-06508: PL/SQL: could not find program unit being called: "GGS_DDL.DDLREPLICATION" ORA-06512: at line 837 ORA-04045: errors during recompilation/revalidation of GGS_DDL.DDLREPLICATION ORA-04064: not executed, invalidated  ORA-04064: not executed, invalidated package body "GGS_DDL.DDLREPLICATION" ORA-06508: PL/SQL: could not find program unit being called: "GGS_DDL.DDLREPLICATION" ORA-06512: at line 837 ORA-04064: not executed, invalidated package body "GGS_DDL.DDLREPLICATION" ORA-06508: PL/SQL: could not find program unit being called: "GGS_DDL.DDLREPLI) initializing query to obtain NUMBER precision (table: 'xxxx.yy', column: 'Cc'), try to fix this issue in order to avoid possible fatal error.

If that happens, you have to reinstall the DDL replication package.  Please see detail on how to install DDL replication package in the installation guide.