Oracle 11g

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.

Some weeks ago a customer upgraded its databases from 10.2 to 11.2.0.3.

Some time after this change there were GoldenGate errors:

2013-02-03 10:11:55  ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  Fatal error executing DDL replication: error [Error code [922], ORA-00922: missing or invalid option, SQL create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_U], no error handler present.
2013-02-03 10:14:55  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  DDL found, operation [create table “XXXXXX”.DBMS_TABCOMP_TEMP_CMP organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab  (size 209)].
2013-02-03 10:14:55  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  DDL is of mapped scope, after mapping new operation [create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab  (size 211)].


After some investigation we found out this is due to the new “feature” Compression Advisor, from Oracle 11.2 (How Does Compression Advisor Work? [1284972.1]).


As it does “create table as select” (CTAS) which GoldenGate does not yet support well, the replicat abended.


There is already one Metalink note on this issue: DDL / Remap Schema : ORA-942 Connected With DBMS_TABCOMP_TEMP_CMP & DBMS_TABCOMP_TEMP_UNCMP [ID 1505178.1].


So we added to the extract parameter files:
TABLEEXCLUDE xxxxx.DBMS_TABCOMP_TEMP*