goldengate ddl enable，ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid

环境： 数据库版本11.2.0.3.11

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb  5 2013 11:44:28

 

执行ddl_setup.sql报错很多表不存在

执行任何ddl语句会出现如下错误

 

SQL> grant GGS_GGSUSER_ROLE to ggate;

grant GGS_GGSUSER_ROLE to ggate

*

ERROR at line 1:

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

 

通过ddl_disable.sql禁止触发器后正常，怀疑ogg的ddl脚本有问题，因为是迁移，尝试将ogg用户impdp过来，依然不行，

 

 

有文档说11.2.0.4需要启用数据库参数对ogg进行支持。

https://community.oracle.com/thread/3616272

 

 

3.3 Enabling Oracle GoldenGate in the Database

 

The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for an Oracle 11.2.0.4 database. This is required for all modes of Extract and Replicat.

To enable Oracle GoldenGate, set the following database initialization parameter. All instances in Oracle RAC must have the same setting.

ENABLE_GOLDENGATE_REPLICATION=true

For more information about this parameter, see Oracle Database Reference.

怀疑是我数据库PSU版本过新导致，遂升级goldengate版本至  Version 11.2.1.0.26 19362036 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140909.0853_FBO

 

解决

 

 

DDL IGNORE TABLE

-----------------------------------

FAILED: Table does not exist

  

DDL IGNORE LOG TABLE

-----------------------------------

FAILED: Table does not exist

  

DDLAUX  PACKAGE STATUS:

 

 

Line/pos                                 Error

---------------------------------------- -----------------------------------------------------------------

No errors                                No errors

 

 DDLAUX PACKAGE BODY STATUS:

  

Line/pos                                 Error

---------------------------------------- ------------------------------------

 FAILED: Table does not exist

  

DDL HISTORY TABLE(1)

-----------------------------------

FAILED: Table does not exist

 

DDL DUMP TABLES

-----------------------------------

FAILED: Table does not exist

 

 

DDL DUMP COLUMNS

-----------------------------------

FAILED: Table does not exist

 

 DDL DUMP LOG GROUPS

-----------------------------------

FAILED: Table does not exist

  

DDL DUMP PARTITIONS

-----------------------------------

FAILED: Table does not exist

 

 DDL DUMP PRIMARY KEYS

  

DDL TRIGGER RUNNING STATUS

------------------------------------------------------------------------------------------------------------------------

ENABLED

FROM "GGATE" ."GGS_SETUP"

              *

ERROR at line 2:

ORA-00942: table or view does not exist

 

 

FROM "GGATE" ."GGS_SETUP"

              *

ERROR at line 2:

ORA-00942: table or view does not exist

  

FROM "GGATE" ."GGS_SETUP"

              *

ERROR at line 2:

ORA-00942: table or view does not exist

 

*********   so obviously an error above----

 

SQL> grant GGS_GGSUSER_ROLE to ggate;

grant GGS_GGSUSER_ROLE to ggate

*

ERROR at line 1:

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation