Re: GGATE not replicating a new table - sanity check

Did you enable supplemental logging and/or force logging at the database level? This will guarantee that all object have supplemental logging enabled even if the object has a nologging option enabled. If you are adding a table to the schema, then adding it to replication ADD SCHEMATRANDATA once ran on the schema, you don’t have to do anything to the table to enable supplemental logging. If you ran ADD TRANDATA on each individual table, then you will have to run the same on the new table to ensure that all columns needed are captured.

You can find more information on SCHEMATRANDATA and TRANDATA at my blog (https://www.dbasolved.com/2016/08/to-trandata-or-to-schematrandata-that-is-the-goldengate-questions-of-the-day/) to get more understanding.

From you approach, it looks like you are stopping replication to add the table. Why not build a separate replication stream (extract/replicat) for the single table get the table replicating and in sync. Then you could merge in easier once the table is at the same SCN as the CDC stream?

Thanks

Bobby

From: "oracle-l-bounce_at_freelists.org" <oracle-l-bounce_at_freelists.org> on behalf of Vanita Sharma Tyagi <dba.vanitasharma_at_gmail.com> Reply-To: "dba.vanitasharma_at_gmail.com" <dba.vanitasharma_at_gmail.com> Date: Saturday, April 4, 2020 at 5:54 AM To: "Harvey.Jefferson_at_acxiom.com" <Harvey.Jefferson_at_acxiom.com> Cc: "andrew.kerber_at_gmail.com" <andrew.kerber_at_gmail.com>, Chris Taylor <christopherdtaylor1994_at_gmail.com>, ORACLE-L <oracle-l_at_freelists.org> Subject: Re: GGATE not replicating a new table - sanity check

I had a similar issue long back and as I remember I had enabled supplemental loggin for the table at db level as well as using add trandata to fix the issue .

On Fri, Jun 28, 2019, 20:05 Harvey Jefferson <Harvey.Jefferson_at_acxiom.com<mailto:Harvey.Jefferson_at_acxiom.com>> wrote: This is a question. If you still want the info you requested after checking this then I will send them. Isn’t the supplemental logging set using like what we previously did for gm_wdata and gmmeta?

               ADD SCHEMATRANDATA GM_WDATA ALLCOLS
               ADD SCHEMATRANDATA GMMETA ALLCOLS

This is me checking it today:
GGSCI (corvette.corp.acxiom.net<http://corvette.corp.acxiom.net> as service_gg_at_gmdwhp) 2> INFO SCHEMATRANDATA GMMETA

2019-06-28 09:27:19 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "GMMETA".

2019-06-28 09:27:19 INFO OGG-01981 Schema level supplemental logging is enabled on schema "GMMETA" for all columns.

2019-06-28 09:27:19 INFO OGG-10462 Schema "GMMETA" have 59 prepared tables for instantiation.

GGSCI (corvette.corp.acxiom.net<http://corvette.corp.acxiom.net> as service_gg_at_gmdwhp) 3> INFO SCHEMATRANDATA GM_WDATA

2019-06-28 09:27:35 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "GM_WDATA".

2019-06-28 09:27:35 INFO OGG-01981 Schema level supplemental logging is enabled on schema "GM_WDATA" for all columns.

2019-06-28 09:27:35 INFO OGG-10462 Schema "GM_WDATA" have 1,472 prepared tables for instantiation.

GGSCI (corvette.corp.acxiom.net<http://corvette.corp.acxiom.net> as service_gg_at_gmdwhp) 4> INFO SCHEMATRANDATA GM_SDATA

2019-06-28 09:28:00 INFO OGG-01786 Schema level supplemental logging is disabled on schema "GM_SDATA".

2019-06-28 09:28:00 INFO OGG-10462 Schema "GM_SDATA" have 654 prepared tables for instantiation.

GGSCI (corvette.corp.acxiom.net<http://corvette.corp.acxiom.net> as service_gg_at_gmdwhp) 5>

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Andrew Kerber Sent: Tuesday, April 16, 2019 11:59 AM
To: Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>> Cc: ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: GGATE not replicating a new table - sanity check

Can you show the full extract, pump, and replicate parameter files? Its hard to track this down without the full text.

On Tue, Apr 16, 2019 at 10:32 AM Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>> wrote: List,

I've got an interesting issue with a table I'm trying to add to Golden Gate (oracle to oracle). It should be pretty simple, but its refusing to replicate and there's no errors or warnings.

Can I get a sanity check on these steps? I'm clearly overlooking something but I can't find it.

*I've redacted Server names and schema names.*

One thing I'm seeing on the Extract is messages like this which seem unusual compared to the other tables:

2019-04-08T14:01:03.047-0400 INFO OGG-02262 Oracle GoldenGate Capture for Oracle, pmp_opt.prm: Passthru wildcard MAP (TABLE) resolved (entry SCHEMA.*): TABLE "SCHEMA"."INVOICE_TABLE".

Steps:

1. Stop both the Extract & the Replicats
   • SOURCE_SERV: GGSCI> stop EXT* ;
   • SOURCE_SERV: GGSCI> stop PMP* ;
   • TARGET_SERV: GGSCI> stop REP* ;
2. Edit the EXTRACT parameter file and add the new table{s) on SOURCE_SERV ext_all.prm: TABLE SCHEMA.INVOICE_TABLE ;
3. Add supplemental logging on SOURCE for new tables (TRANDATA logging) GGSCI> dblogin userid GG_USER, password " password " ; GGSCI> add trandata SCHEMA.INVOICE_TABLE ;
4. Start Extracts and Pump processes on SOURCE_SERV GGSCI> start EXT* ; GGSCI> start PMP* ;
5. Get current SCN on SOURCE db SQL> select current_scn from v$database; CURRENT_SCN

   ---

   1263790785581
6. Create an expdp parameter file for expdp on SOURCE - include SCN from above userid="/ as sysdba" directory=DUMP_DIR dumpfile=exp_ggate_%U.dmp logfile=exp_ggate.log parallel=8 tables=SCHEMA.INVOICE_TABLE flashback_scn=1263790785581 status=30
7. Start the export on SOURCE using the parameter file just created $ expdp parfile=ggate_exp.par
8. Copy the dumpfiles to TARGET server
9. Create an impdp parameter file on TARGET server ## Make sure to include EXCLUDE=REF_CONSTRAINT userid="/ as sysdba" directory=DUMP_DIR dumpfile=exp_ggate_%U.dmp logfile=imp_ggate.log parallel=8 tables=SCHEMA.INVOICE_TABLE status=30 EXCLUDE=REF_CONSTRAINT
10. Execute the import on TARGET server
11. Edit the Parameter file for the REPLICAT to include the new table AND the SCN (CSN) we captured in step 5 ## Use Double Quotes around Transaction & CSN keywords MAP SCHEMA.INVOICE_TABLE, TARGET SCHEMA.INVOICE_TABLE, FILTER ( _at_GETENV ("TRANSACTION", "CSN") > 1263790785581);
12. After import completes, start the REPLICATs
13. Monitor for data changes in the SOURCE - once data changes in SOURCE, verify modified data appears in TARGET
14. After confirming Replicats are working and data is replicating for new table, stop the REPLICATs and remove the FILTER line
15. Restart Replicats after removing FILTER

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************

--
http://www.freelists.org/webpage/oracle-l