Today I discovered that primary key indexes do not need to be unique. This makes a mess for checking supplemental logging for GoldenGate.

Here is the example:

SQL> create table t(c1 number, c2 number);
Table created.


SQL> create index i on t(c1,c2);
Index created.


SQL> alter table t add primary key (c1,c2) using index i;
Table altered.


SQL> select uniqueness from dba_indexes where table_name=’T';
UNIQUENES
———
NONUNIQUE


SQL>  select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_name=’T';
CONSTRAINT_NAME                C
—————————— -
SYS_C001632047                 P


SQL> insert into t values (1,2);
1 row created.


SQL> commit;
Commit complete.


SQL> insert into t values (1,2);
insert into t values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C001632047) violated


SQL> alter table t drop constraint SYS_C001632047;
Table altered.


SQL> insert into t values (1,2);
1 row created.


SQL> commit;
Commit complete.


SQL> alter table t add primary key (c1,c2) using index i;
alter table t add primary key (c1,c2) using index i
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.SYS_C001632048) – primary key violated


SQL> alter table t add primary key (c1,c2) using index i novalidate;
Table altered.


SQL>  select uniqueness from dba_indexes where table_name=’T';
UNIQUENES
———
NONUNIQUE


SQL> select * from t;
        C1         C2
———- ———-
         1          2
         1          2


SQL> select VALIDATED from dba_constraints where table_name=’T';
VALIDATED
————-
NOT VALIDATED


SQL> insert into t values (1,2);
insert into t values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C001632049) violated


SQL> drop table t;
Table dropped.

I use Goldengate on classic capture mode to replicate several RAC One Node databases to an external system.
Since the first time there was a relocation of the RAC One Node database, there are two redo log threads (because the DB was temporarily a 2-node RAC).

Now the current thread# has changed and GoldenGate complaints that he cannot access anymore to the redologs.

What was necessary to do was:

1. Check which is the current thread:

SQL> select thread# from v$log where status=’CURRENT’; –> current=1

3. Recreate the extract with:

delete extract e_xx
add extract e_xx tranlog threads 2 begin now
add exttrail /oraacfs/goldengate/gg/dirdat/e_xx/xx extract e_xx

4. Check with
info e_xx
that the RBA for one of the threads increases with the time.

All this should not be needed when running in integrated capture mode.