sql> set long 90000
SQL> select dbms_metadata.get_ddl ('TABLE','NSP_FCC_GUI_LOG','NSPDBA') from dual;
CREATE TABLE "WORLD"."INDIA"
( "ID_NUMBER" NUMBER(12,0) CONSTRAINT "NN_ID" NOT NULL ENABLE,
"C_8" VARCHAR2(8),
"NSTATE" VARCHAR2(6),
"STRF" VARCHAR2(2),
"FCIND" VARCHAR2(12),
"STATID" VARCHAR2(8),
"UPDT_TS" DATE,
CONSTRAINT "NN_ID1" PRIMARY KEY ("ID_NUMBER")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "IND_WORLD" ENABLE,
SUPPLEMENTAL LOG GROUP "GGS_1030618" ("ID_NUMBER", "C_8", "NSTATE", "STRF", "FCIND", "STATID", "UPDT_TS") ALWAYS
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "IND_WORLD"
When we look at DDL of the table, we are logging all columns in the table
Before adding primary key to the table drop the existing group on the table as follows
SQL> Alter table world.india drop supplemental log group GGS_1030618;
Table altered.
Syntax: alter table schema.tablename drop supplemental log group <group-name>;
Dropped Supplemental log-group successfully
After adding primary key constraint to the column "ID_NUMBER" to WORLD.INDIA table add supplemental logging from goldengate command line utility ggsci
ggsci> dblogin userid ggsuser password ggdwd
ggsci> add trandata world.india
Logging of supplemental redo data enabled for table WORLD.INDIA.
check Supplemental logging enabled for new changes on tables
GGSCI (tnc1) 7> info trandata WORLD.INDIA.
Logging of supplemental redo log data is enabled for table WORLD.INDIA..
Columns supplementally logged for table WORLD.INDIA:ID_NUMBER.
when ever there is change in other columns of the table WORLD.INDIA. Along with changed columns,
column "ID_NUMBER" will be logged into online redo
Note:
When we add primary key column to the table, performance of replicat which is working on target table will be increased
When ever we re-add supplemental logging, bounce the goldengate process associated with table. When we bounce the goldengate process, new code is taken into the memory
--Nikhil Tatineni--
--GoldenGate--
