This discussion is archived
9 Replies Latest reply: Nov 12, 2012 7:44 AM by satrap RSS

Extract & Replicat Parameters for whole Schema replication (all DDL & DML).

LazyDBA11g Newbie
Currently Being Moderated
Hi Gurus,

I have implemented uni-directional replication from oracle to oracle for a production environment without using datapump. included parameters for DDL & DML replication in both extract & replicat.

replication is successful for new tables etc. But its failing for old tables in target DB which was loaded using expdp/impdp before starting replication.

(throwing the WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, SQL error 1403 mapping. >> during business hours)

There are over 2000 tables in existing production source DB. So my questions as below:

1. Do I need to add trandata for each tables in existing source DB ?
2. Do I need to add additional parameters in extract process for extracting redo from these existing tables in source DB ?
3. Do I need to add additional parameters in replicat process for enabling replication for these existing tables in target DB ?

My existing extract & replicat process are as below:
GGSCI 10> edit params xxx

--GETTRUNCATES
-----
-----
DYNAMICRESOLUTION
--DDL support
ddl include mapped objname abc.*;
--DML
table abc.*;
GGSCI  9> edit params yyy

--ddl support
DDL
--Specify table mapping---
map abc.*, target abc.*;
thanks in advance.

Regards,

Edited by: LazyDBA11g on Nov 7, 2012 9:06 PM
  • 1. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    Artem Khisamiev Newbie
    Currently Being Moderated
    1. Do I need to add trandata for each tables in existing source DB ?
    Yes, you have to switch on supplemental logging for all tables being replicated or for the whole instance.
    I've found two articles in MOS -
    EXTRACT CAPTURES NULL VALUE FOR DEFAULT NOT NULL COLUMN CAUSING REPLICAT TO ABEND [ID 1331178.1]
    Replicat Abended With OGG-01296 After Initial Load and Adding a New Table to Replication. [ID 1471770.1]
    where mentioned very similar errors and that the supplemental logging is mandatory.
    2. Do I need to add additional parameters in extract process for extracting redo from these existing tables in source DB ?
    3. Do I need to add additional parameters in replicat process for enabling replication for these existing tables in target DB ?
    No, you shouldn't. My parameters for replicator and extractor resemble yours, except DDL clause --
    instead yours --
    --DDL support
    ddl include mapped objname urbanlive.*;
    I have --
    DDL
    and my ddls as much as my dmls are replicated without any problems.
    Don't forget about initial load when you initialise you environment. You can capture on-going transaction while you are proceeding initial load and you have to apply those transaction by starting replicators after initial load completes.
  • 2. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    LazyDBA11g Newbie
    Currently Being Moderated
    Hi Artem,

    Thanks a lot for your response. I still have sone confusion regarding table mapping having PK/UK.

    suppose , I have a table TEST having pk on its coloumn ID.

    Do I need to put below parameter in my extract process ? (to avoid WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, : SQL error 1403 mapping)
    table abc.TEST, KEYCOLS(ID);      
    and also should I put below parameter in replicat process ?
    MAP abc.TEST, TARGET abc.TEST, KEYCOLS(ID);
    Now my questions are:

    1. What parameters needs to be added in extract / replicat for replication of table TEST ?
    2. How to map tables not having pk/uk ?

    Warm Regards,

    Edited by: LazyDBA11g on Nov 7, 2012 9:12 PM
  • 3. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    Artem Khisamiev Newbie
    Currently Being Moderated
    From [url http://docs.oracle.com/cd/E15881_01/doc.104/gg_wux_admin_v104.pdf]Oracle GoldenGate Administration guide --
    >
    Keys
    For accurate detection of conflicts, all records must have a unique, not-null identifier. If
    possible, create a primary key. If that is not possible, use a unique key or create a
    substitute key with a KEYCOLS option of the MAP and TABLE parameters. In the absence of a
    unique identifier, Oracle GoldenGate uses all of the columns that are valid in a WHERE
    clause, but this will degrade performance if the table contains numerous columns.
    To maintain data integrity and prevent errors, the key that you use for any given table
    must:
    ● contain the same columns in all of the databases where that table resides.
    ● contain the same values in each set of corresponding rows across the databases.
    >
    I think this would be enough --
    table URBANLIVE.TEST
    MAP URBANLIVE.TEST, TARGET URBANLIVE.TEST
    with pk and supplemental logging on URBANLIVE.TEST.
  • 4. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    LazyDBA11g Newbie
    Currently Being Moderated
    Hi Artem,

    I have set the TABLE & MAP parameters but still getting the below errors:
    2012-11-05 10:43:37  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle,:  OCI Error ORA-00001: unique constraint  violated (status = 1), SQL <INSERT INTO
    2012-11-05 10:43:37  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle,:  Aborted grouped transaction on , Database error 1 (ORA-00001: unique constraint 
    2012-11-05 10:43:37  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, :  Repositioning to rba 25725 in seqno 1048.
    2012-11-05 10:43:37  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle,   SQL error 1 mapping OCI Error ORA-00001: unique constraint
    2012-11-05 10:43:37  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, :  Repositioning to rba 25725 in seqno 1048.
    2012-11-05 10:43:37  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle,:  Error mapping from
    2012-11-05 10:43:37  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, :  PROCESS ABENDING.
    Regards,

    Edited by: LazyDBA11g on Nov 7, 2012 9:16 PM
  • 5. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    Artem Khisamiev Newbie
    Currently Being Moderated
    Correct me if I'm not right --
    It seems to me that you try insert row with key that not exists in source table.
    But somehow the key already exists in target table and your insert violate unique key constraint.
    It is the evidence of unsynchronisation of source and target table.

    You can start replicator with skipping last transaction by --
    START REPLICAT <group name> SKIPTRANSACTION

    And try to insert a row with key which really not exists in source table, nor in target table.
  • 6. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    LazyDBA11g Newbie
    Currently Being Moderated
    I added the parameter REPERROR (1, DISCARD) to resolve the OCI Error ORA-00001: unique constraint. But I am still getting below error.
    2012-11-07 10:40:11  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, :  No unique key is defined for table aaa. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
    2012-11-07 10:40:12  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, :  No unique key is defined for table aaa. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
    2012-11-07 10:40:15  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, :  No unique key is defined for table bbb. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
    2012-11-07 10:40:15  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, :  No unique key is defined for table bbb. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
    2012-11-07 10:40:16  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, :  Aborted grouped transaction on abc.bbb, Database error 100 (retrieving bind info for query).
    2012-11-07 10:40:16  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, :  Repositioning to rba 17466 in seqno 1384.
    2012-11-07 10:40:16  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, :  SQL error 1403 mapping 
    2012-11-07 10:40:16  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle,:  Repositioning to rba 20104 in seqno 1384.
    2012-11-07 10:40:16  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, :  Error mapping 
    2012-11-07 10:40:16  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle,:  PROCESS ABENDING.
    Let me know if something is missing in above steps or what needs to be done now .

    Regards,

    Edited by: LazyDBA11g on Nov 19, 2012 10:43 PM
  • 7. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    AnnamalaiA Journeyer
    Currently Being Moderated
    Hi,

    What is your database and OGG version which is currently using ?

    Did you add the supplmental log data directly on that tables, like

    alter table TESTGG.REPTABLE add supplemental log data (all) columns;
  • 8. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    LazyDBA11g Newbie
    Currently Being Moderated
    Hi Annamalai,

    DB version and OGG version are as below:

    Oracle RAC 11.2.0.1.0
    Oracle GoldenGate 11g Release 1 (11.1.1.0.0)

    I did enabled the supplemental loggong.
    SQL> alter database add supplemental log data; 
    SQL> ALTER SYSTEM SWITCH LOGFILE;
    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; --verify
     
    SUPPLEME
    --------
    YES
    and also enabled supplemental loggoing for tables.
    ggsci> add trandata abc.*   
    Thanks in advance.

    Regards,

    Edited by: LazyDBA11g on Nov 19, 2012 10:43 PM
  • 9. Re: Extract & Replicat Parameters for whole Schema replication (all DDL & DML).
    satrap Journeyer
    Currently Being Moderated
    Table aaa and bbb do not have a PK or UK that GG can use to update the records.Use APPLYNOOPUPDATES in your replicat param file to force the update to be applied using all columns in both the SET and WHERE clause.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points