Goldengate 不能 replicate to trigger

1l6r5d

請指教!

--################################
GGSCI (GG2) 5> edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID GGS_OWNER, PASSWORD abc123
discardfile /u01/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
ddl include all;
DDLOPTIONS REPORT

DDLERROR DEFAULT IGNORE RETRYOP
SQLEXEC "ALTER SESSION SET COMMIT_WRITE = NOWAIT"
MAP WPAS.*, TARGET WPAS.*;

--################################
GGSCI (GG1) 5> EDIT PARAMS ext1

EXTRACT ext1
USERID GGS_OWNER, PASSWORD abc123
rmthost 192.168.1.107, mgrport 7809
rmttrail /u01/oracle/product/gg/dirdat/lt
ddl include all;
DDLOPTIONS ADDTRANDATA
table WPAS.*;

--################################
以下是我 create trigger 遇到的問題

[GG1]
SQL> select TRIGGER_NAME from user_triggers;

no rows selected

SQL> create or replace trigger test
before update on t2
for each row
begin
     insert into t3 values (2, 'T2');
end;  2    3    4    5    6
  7  /

Trigger created.

SQL> select count(*) from user_triggers;

  COUNT(*)
----------
         1


SQL> select TRIGGER_NAME from user_triggers;

TRIGGER_NAME
------------------------------
TEST

SQL>

[GG2]
SQL> select count(*) from user_triggers;

  COUNT(*)
----------
         0


SQL> select TRIGGER_NAME from user_triggers;

no rows selected

SQL>

GG2-> tail -f /u01/oracle/product/gg/ggserr.log
2011-10-16 03:52:00  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Restoring current schema for DDL operation to [GGS_OWNER].
2011-10-16 03:52:31  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, rep1.prm:  DDL found, operation [create or replace trigger test before update on t2 for each row begin      insert into t3 values (2, 'T2'); end;  (size 113)].
2011-10-16 03:52:31  INFO    OGG-00480  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Derived object name [WPAS.TEST] mapped to [WPAS.TEST].
2011-10-16 03:52:31  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, rep1.prm:  DDL is of mapped scope, after mapping new operation [create or replace trigger "WPAS"."TEST" before update on "WPAS"."T2" for each row begin      insert into t3 values (2, 'T2'); end;  (size 131)].
2011-10-16 03:52:32  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Setting current schema for DDL operation to [WPAS].
2011-10-16 03:52:32  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Executing DDL operation.
2011-10-16 03:52:33  INFO    OGG-00495  Oracle GoldenGate Delivery for Oracle, rep1.prm:  DDL error ignored for next retry: error code [DEFAULT], filter [include all (default)], error text [Error code [1031], ORA-01031: insufficient privileges, SQL create or replace trigger "WPAS"."TEST" before update on "WPAS"."T2" for each row begin      insert into t3 values (2, 'T2'); end;  /* GOLDENGATE_DDL_REPLICATION */], retry [1].
2011-10-16 03:52:33  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Setting current schema for DDL operation to [WPAS].
2011-10-16 03:52:33  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Executing DDL operation trying again due to RETRYOP parameter.
2011-10-16 03:52:33  INFO    OGG-00492  Oracle GoldenGate Delivery for Oracle, rep1.prm:  DDL error ignored: error code [DEFAULT], filter [include all (default)], error text [Error code [1031], ORA-01031: insufficient privileges, SQL create or replace trigger "WPAS"."TEST" before update on "WPAS"."T2" for each row begin      insert into t3 values (2, 'T2'); end;  /* GOLDENGATE_DDL_REPLICATION */].
2011-10-16 03:52:33  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Restoring current schema for DDL operation to [GGS_OWNER].

kevinlin.ora

ORA-01031: insufficient privileges，检查一下吧

1l6r5d

謝謝你回覆, 但係 replicate note 上用 WPAS 係能夠 create same trigger.

kevinlin.ora

应该是Oracle GoldenGate Delivery相关的数据库用户与权限。
在日志中可以看到进行了set current schema操作：
Setting current schema for DDL operation to [WPAS]. ---- set current schema后，并不意谓着具有这个schema的相应权限
-----------

SQL> create user testddl identified by test;

User created.

SQL> grant connect to testddl;

Grant succeeded.

SQL> conn testddl/test
Connected.

SQL> alter session set current_schema=test;

Session altered.

SQL> create table test.testxxx (id number);
create table test.testxxx (id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges