GoldenGate 不能 replicate create table with primary key

1l6r5d

不知為何, 請指教!

[GG1]

SQL> select * from tab;

no rows selected

SQL> create table test1 (id1 number primary key,id2 number);


Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST1                          TABLE

SQL>

[GG2]seem can're replicate with primary key table
GG2-> tail -f $GGS_HOME/ggserr.log

2011-10-17 23:29:53  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, rep1.prm:  DDL found, operation [create table test1 (id1 number primary key,id2 number)  (size 55)].
2011-10-17 23:29:53  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, rep1.prm:  DDL is of mapped scope, after mapping new operation [create table "WPAS"."TEST1" (id1 number primary key,id2 number)  (size 64)].
2011-10-17 23:29:53  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, rep1.prm:  DDL operation included [include all], optype [CREATE], objtype [TABLE], objowner [WPAS], objname [TEST1].
2011-10-17 23:29:54  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Setting current schema for DDL operation to [WPAS].
2011-10-17 23:29:54  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Executing DDL operation.
2011-10-17 23:29:56  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 table "WPAS"."TEST1" (id1 number primary key,id2 number)  /* GOLDENGATE_DDL_REPLICATION */], retry [1].

GG2->


[GG2]
SQL> select * from tab;

no rows selected

SQL>

[GG1]

SQL> create table t1 (id number, name varchar2(12));

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
TEST1                          TABLE


SQL>

[GG2]
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE

SQL>
      再者我嘗試 keycols 方法還是不行.
[GG1]

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE

SQL> alter table T1 add OGG_KEY_ID raw(16);

SQL> desc T1;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(12)

SQL> alter table T1 add OGG_KEY_ID raw(16);


Table altered.

SQL> alter table T1 modify OGG_KEY_ID default sys_guid();

Table altered.

SQL> insert into T1 (id, name) values(1,'USER1');

1 row created.

SQL> insert into T1 (id, name) values(2,'USER2');

1 row created.

SQL> select * from t1;

        ID NAME         OGG_KEY_ID
---------- ------------ --------------------------------
         1 USER1        AF8278819D8F0AAFE040A8C06A0111B3
         2 USER2        AF8278819D900AAFE040A8C06A0111B3

SQL>

[GG1]
GGSCI (GG1) 2> add trandata WPAS.T1, COLS (OGG_KEY_ID), nokey

[GG2]
SQL> alter table T1 add OGG_KEY_ID raw(16);

Table altered.

SQL> create unique index OGG_T1_UI on T1 (OGG_KEY_ID) logging online;

Index created.

SQL> desc T1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(12)
OGG_KEY_ID                                         RAW(16)

SQL>

[GG1]
GGSCI (GG1) 2> 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.*;

GGSCI (GG1) 3>

[GG2]
SQL> select * from t1;

no rows selected

SQL>

[GG1]
SQL> insert into T1 (id, name) values(3,'DBA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME         OGG_KEY_ID
---------- ------------ --------------------------------
         1 USER1        AF8278819D8F0AAFE040A8C06A0111B3
         2 USER2        AF8278819D900AAFE040A8C06A0111B3
         3 DBA          AF8278819D910AAFE040A8C06A0111B3

SQL> truncate t1;
truncate t1
          *
ERROR at line 1:
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword


SQL> trucate table t1;
SP2-0734: unknown command beginning "trucate ta..." - rest of line ignored.
SQL> delete * from t1;
delete * from t1
       *
ERROR at line 1:
ORA-00903: invalid table name


SQL> delete from t1 where ID=1;

1 row deleted.

SQL> delete from t1 where ID=2;

1 row deleted.

SQL> delete from t1 where ID=3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;


no rows selected

SQL>

[GG2]
SQL> select * from t1;

no rows selected

SQL>

[GG1]
GGSCI (GG1) 28> stop EXTRACT EXT1

Sending STOP request to EXTRACT EXT1 ...
Request processed.


GGSCI (GG1) 29> cleanup EXTRACT EXT1
ERROR: EXTRACT EXT1 is running and cannot be altered (1,2,No such file or directory).


GGSCI (GG1) 30> info all


Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:01:00


GGSCI (GG1) 31> 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.*;
table WPAS.T1, KEYCOLS(OGG_KEY_ID);

[GG2]
GGSCI (GG2) 22>  info all


Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:00


GGSCI (GG2) 23> stop REPLICAT REP1

Sending STOP request to REPLICAT REP1 ...
Request processed.


GGSCI (GG2) 24> cleanup REPLICAT REP1
Cleanup completed.


GGSCI (GG2) 25>

[GG1]
GGSCI (GG1) 35> info all


Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:03:03


GGSCI (GG1) 36> start EXTRACT EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (GG1) 37> info all


Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:03:20      00:00:06


GGSCI (GG1) 38>

[GG2]
GGSCI (GG2) 25> info all


Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP1        00:00:00      00:01:30


GGSCI (GG2) 26> start REPLICAT REP1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (GG2) 27> info all


Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:04


GGSCI (GG2) 28>