4.1.4.3. Creating the Destination Schema

When replicating from Oracle to Oracle, the schema of the two tables should match, or at least be compatible, if you are filtering or renaming tables. If the schema on the source Oracle server is available, it should be used to generate the schema on the destination server.

Tables should be created on the slave with the following caveats:

  • Drop triggers from all tables. Triggers are not automatically disabled by Tungsten Replicator, and leaving them enabled may create data drift, duplicate keys and other errors. Triggers can be disabled on a table using:

    SQL> ALTER TABLE sales DISABLE ALL TRIGGERS
  • Remove foreign key constraints. Because data is replicated based on the window of changes provided for each CDC block, and the order of the individual operations may not be identical. This can lead to foreign key constraints failing, even though the source database updates were processed correctly.

If the schema is not separately available, the schema information can be extracted within sqlplus, either by display the table definition using the DESC command:

SQL> desc SALES.sample;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID					   NOT NULL NUMBER(38)
 MSG						    CHAR(80)

Or by extracting the information from the database metadata:

SQL> select dbms_metadata.get_ddl( 'TABLE','SAMPLE','SALES') from dual;

DBMS_METADATA.GET_DDL('TABLE','SAMPLE','SALES')
--------------------------------------------------------------------------------

  CREATE TABLE "SALES"."SAMPLE"
   (	"ID" NUMBER(*,0),
	"MSG" CHAR(80),
	 PRIMARY KEY ("ID")
  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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SALES_PUB"  ENABLE,
	 SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,

DBMS_METADATA.GET_DDL('TABLE','SAMPLE','SALES')
--------------------------------------------------------------------------------
	 SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS,
	 SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,
	 SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SALES"

Note that the information may be truncated due to the configuration only displaying a subset of the generated LONG datatype used to display the information. The command:

SQL> set long 10000;

Will increase the displayed length to 10,000 characters.