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

		修改时间 15-APR-2011 类型 HOWTO 状态 PUBLISHED

In this Document
Solution
References

Applies to:

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

Solution

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.

References

显示相关信息 相关的

产品

Middleware > Data Integration > GoldenGate > Oracle GoldenGate

关键字

CPU; DATABASE UPGRADE; DDL; GOLDENGATE; PATCH; PSU; TRIGGER; UPGRADE

错误

ORA-4064; ORA-4050; ORA-6508; ORA-6512; ORA-4045; 04050 ERROR

[@more@]

ITPub博客

oracle db 应用patch时，goldengate需要注意的地方ID 971222.1

Applies to:

Solution

References

最新文章