Binds not going trough... - GoldenGate

Hi,
I have managed to do the initial load of the tables, but updates are not working.
Running inside same server with two 10.2.0.4 -instances with GG 11 on AIX.
Error message after starting replicat:
2010-08-26 18:37:33 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, erprep.prm: REPLICAT ERPREP starting.
2010-08-26 18:37:33 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, erprep.prm: REPLICAT ERPREP started.
2010-08-26 18:37:33 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, erprep.prm: Aborted grouped transaction on 'ERPLN6A.TTTPMC000000', Database error 100 (retrieving bi
nd info for query).
2010-08-26 18:37:33 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, erprep.prm: Repositioning to rba 988 in seqno 8.
2010-08-26 18:37:33 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, erprep.prm: SQL error 1403 mapping ERPLN6A.TTTPMC000000 to ERPLN6A.TTTPMC000000.
2010-08-26 18:37:33 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, erprep.prm: Repositioning to rba 988 in seqno 8.
2010-08-26 18:37:33 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, erprep.prm: Error mapping from ERPLN6A.TTTPMC000000 to ERPLN6A.TTTPMC000000.
2010-08-26 18:37:33 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, erprep.prm: PROCESS ABENDING.
It's exactly what message is saying...no binds found for update. Checked with trace level 12. First bind is empty, should be 1. Another one does not exist, because it's NVARCHAR2.
UPDATE "ERPLN6A"."TTTPMC000000" SET "T$PEXP" = :a1 WHERE "T$SERN" = :b0
END OF STMT
PARSE #4:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=66531500852162
BINDS #4:
kkscoacd
Bind#0
oacdty=01 mxl=2000(400) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=02 csi=2000 siz=2032 off=0
kxsbbbfp=1104905a8 bln=2000 avl=34 flg=05
value=""
Bind#1
oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=21 fl2=1000000 frm=01 csi=873 siz=0 off=2000
kxsbbbfp=110490d78 bln=32 avl=00 flg=01
******
Grants and supplemental things looks ok. From where I can check that extract is doing it right?
Trails seem to be ok, because replicat is reading it. And when reading last extract trail -file, it shows changes there (binary stuff).
GGSCI (fihel3-x0002) 4> view params erpln6a
EXTRACT erpln6a
USERID ggate#erpln6a, PASSWORD xxx
RMTHOST localhost, MGRPORT 7809
rmttrail /temppi/erprep/ogg/target/dirdat/lt
TABLE ERPLN6A.TTTPMC000000;
GGSCI (fihel3-x0002) 5> view params erprep
replicat erprep
ASSUMETARGETDEFS
USERID ggate#erprep, password xxxx
MAP ERPLN6A.TTTPMC000000, TARGET ERPLN6A.TTTPMC000000 ;
******* 

Hey,
Did you try with HANDLECOLLISIONS in replicat params or with APPLYNOOPUPDATES
replicat erprep
ASSUMETARGETDEFS
USERID ggate#erprep, password xxxx
HANDLECOLLISIONS
APPLYNOOPUPDATES
MAP ERPLN6A.TTTPMC000000, TARGET ERPLN6A.TTTPMC000000 ;
Did you check if the triggers are dropped in the target table and also constraints. 

Thanks,
I thought not to try with these options, because I can't see any error messages anywhere about possible problems.
Table has only one row...
HANDLECOLLISIONS will allow updates, but I can't understand why it needs this. There are constraints in source -table. Is this illegal situation?
CREATE TABLE ERPLN6A.TTTPMC000000
(
T$SERN NUMBER NOT NULL,
T$PISD NVARCHAR2(200) NOT NULL,
T$PUND NVARCHAR2(200) NOT NULL,
T$WARN NUMBER NOT NULL,
T$IDNT NUMBER NOT NULL,
T$PEXP NVARCHAR2(200) NOT NULL,
T$PEXM NVARCHAR2(200) NOT NULL,
T$CPUB NUMBER NOT NULL,
T$PPUB NVARCHAR2(200) NOT NULL,
T$REFCNTD NUMBER NOT NULL,
T$REFCNTU NUMBER NOT NULL,
SUPPLEMENTAL LOG GROUP GGS_TTTPMC000000_938783 (T$SERN) ALWAYS
)
TABLESPACE C000DAT
PCTUSED 0
PCTFREE 10
INITRANS 3
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX ERPLN6A.TTTPMC000000$IDX1 ON ERPLN6A.TTTPMC000000
(T$SERN)
LOGGING
TABLESPACE C000IDX
PCTFREE 10
INITRANS 3
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL; 

Hi Cameron.
I just ran a quick test using a smaller version of your table using OGG 10.4 RedHat 5 and it worked. You might want to check the trail file using logdump found in the OGG installation directory. If it's not in the trail then something's up with your extract.
Here's what I did:
CREATE TABLE TTTPMC000000
(
T$SERN NUMBER NOT NULL,
T$PEXP NVARCHAR2(200) NOT NULL,
SUPPLEMENTAL LOG GROUP GGS_TTTPMC000000_938783 (T$SERN) ALWAYS
)
/
CREATE UNIQUE INDEX TTTPMC000000$IDX1 ON TTTPMC000000
(T$SERN)
/
SQL> desc TTTPMC000000
Name Null? Type
----------------------------------------- -------- ----------------------------
T$SERN NOT NULL NUMBER
T$PEXP NOT NULL NVARCHAR2(200)
SQL> insert into SOURCE.TTTPMC000000 values (1,'abc');
1 row created.
SQL> commit;
Commit complete.
SQL> update SOURCE.TTTPMC000000 set T$PEXP = 'def';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from SOURCE.TTTPMC000000;
T$SERN T$PEXP
---------- ------------
1 def
SQL> select * from TARGET.TTTPMC000000;
T$SERN T$PEXP
---------- ------------
1 def
cd $OGG_HOME
./logdump
Logdump 4266> cd dirdat/local
Logdump 4268> ghdr on
Logdump 4269> detail on
Logdump 4272> open ab000000
Logdump 4282 >pos 1110
Reading forward from RBA 1110
Logdump 4283 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 23 (x0017) IO Time : 2010/09/08 20:58:36.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 517 AuditPos : 14063120
Continued : N (x00) RecCount : 1 (x01)
2010/09/08 20:58:36.000.000 FieldComp Len 23 RBA 1110
Name: SOURCE.TTTPMC000000
After Image: Partition 4 G s
0000 0005 0000 0001 3100 0100 0a00 0000 0600 6400 | ........1.........d.
6500 66 | e.f
Column 0 (x0000), Len 5 (x0005)
Column 1 (x0001), Len 10 (x000a)
Hope this helps,
-joe 

info <<Extract Name>> , showch will show from
Startup Checkpoint / Recovery Checkpoint / Current Checkpoint .
Below example - Startup Checkpoint is from redolog.
Recovery Checkpoint / Current Checkpoint is reading from archivelog.
GGSCI (sglpdusab01a) 28> info ESGSPK1,showch
EXTRACT ESGSPK1 Last Started 2010-09-15 16:07 Status RUNNING
Checkpoint Lag 00:01:50 (updated 00:00:03 ago)
Log Read Checkpoint Oracle Redo Logs
2010-09-15 16:05:47 Seqno 1800, RBA 12113936
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Redo Log
Startup Checkpoint (starting position in the data source):
Sequence #: 1800
RBA: 12113936
Timestamp: 2010-09-15 16:05:47.000000
Redo File: /u02/oradata/UOSGTOYO/redo03.log
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Sequence #: 1800
RBA: 12113936
Timestamp: 2010-09-15 16:05:47.000000
Redo File: /ora_bkup/flash_recovery_area/UOSGTOYO/archivelog/2010_09_15/o1_mf_1_1800_690znz0x_.arc
Current Checkpoint (position of last record read in the data source):
Sequence #: 1800
RBA: 12113936
Timestamp: 2010-09-15 16:05:47.000000
Redo File: /ora_bkup/flash_recovery_area/UOSGTOYO/archivelog/2010_09_15/o1_mf_1_1800_690znz0x_.arc
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 4
RBA: 1008
Timestamp: 2010-09-15 16:07:37.377204
Extract Trail: /u02/ggsdata/out/cc
Header:
Version = 2
Record Source = A
Type = 4
# Input Checkpoints = 1
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2010-09-15 16:07:37
Last Update Time = 2010-09-15 16:07:37
Stop Status = A
Last Result = 0
GGSCI (sglpdusab01a) 29> 

User3726509,
My apologies for not seeing it, should it be obvious, but without at least a cursory set of verbs and nouns you make it rather difficult to dicerpher your meaning and intention. How does output from your extract "showch" assist in answering the current question?
Thanks,
-joe

Related

Golgen Gate 11g, Error mapping

Hi to everyone
I have a problem with Golden Gate, I am configuring online change synchronization, can you help me please ? to find out the problem
This is my configuration:
—-Target—
VMware Fedora 16
Oracle 11g R2
2012-04-27 10:03:56 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep1.prm: REPLICAT REP1 started.
2012-04-27 10:03:58 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep1.prm: Aborted grouped transaction on ‘SH.PRUEBA_GOLDENGATE’, Database error 1403 ().
2012-04-27 10:03:58 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep1.prm: Repositioning to rba 16669 in seqno 0.
2012-04-27 10:03:58 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep1.prm: SQL error 1403 mapping SCOTT.PRUEBA_GOLDENGATE to SH.PRUEBA_GOLDENGATE.
2012-04-27 10:03:58 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep1.prm: Repositioning to rba 16669 in seqno 0.
2012-04-27 10:03:58 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep1.prm: Error mapping from SCOTT.PRUEBA_GOLDENGATE to SH.PRUEBA_GOLDENGATE.
2012-04-27 10:03:58 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep1.prm: PROCESS ABENDING.
REPLICAT rep1
ASSUMETARGETDEFS
USERID goldengate, PASSWORD oracle1
MAP scott.prueba_goldengate, TARGET SH.prueba_goldengate;
GGSCI (infected) 2> info replicat rep1
REPLICAT REP1 Last Started 2012-04-27 10:03 Status ABENDED
Checkpoint Lag 15:53:33 (updated 00:14:58 ago)
Log Read Checkpoint File /u01/app/goldenGate/dirdat/rt000000
2012-04-26 18:10:25.013655 RBA 16669
GGSCI (infected) 3>
Prompt Table PRUEBA_GOLDENGATE;
CREATE TABLE SH.PRUEBA_GOLDENGATE
(
ID NUMBER,
NOMBRE VARCHAR2(20 BYTE),
DES VARCHAR2(20 BYTE)
)
TABLESPACE TBS_DBA_DAT_TRX
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Prompt Index PRUEBA_GOLDENGATE_PK;
CREATE UNIQUE INDEX SH.PRUEBA_GOLDENGATE_PK ON SH.PRUEBA_GOLDENGATE
(ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Prompt Non-Foreign Key Constraints on Table PRUEBA_GOLDENGATE;
ALTER TABLE SH.PRUEBA_GOLDENGATE ADD (
CONSTRAINT PRUEBA_GOLDENGATE_PK
PRIMARY KEY
(ID));
COUNT(*)
0
—— SOURCE ———-
VMware Fedora 16
Oracle 11g R2
EXTRACT ext1
USERID goldengate, PASSWORD oracle1
RMTHOST 192.168.239.139, MGRPORT 7809
RMTTRAIL /u01/app/goldenGate/dirdat/rt
TABLE scott.prueba_goldengate;
GGSCI (infected) 38> info extract ext1
EXTRACT EXT1 Last Started 2012-04-27 10:03 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2012-04-27 10:19:33 Seqno 28, RBA 25782272
GGSCI (infected) 39>
2012-04-27 10:03:32 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, ext1.prm: Recovery initialization completed for target file /u01/app/goldenGate/dirdat/rt000005, at RBA 32405.
2012-04-27 10:03:32 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, ext1.prm: Output file /u01/app/goldenGate/dirdat/rt is using format RELEASE 10.4/11.1.
2012-04-27 10:03:32 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, ext1.prm: Rolling over remote file /u01/app/goldenGate/dirdat/rt000006.
2012-04-27 10:03:32 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, ext1.prm: Recovery completed for target file /u01/app/goldenGate/dirdat/rt000006, at RBA 989.
2012-04-27 10:03:32 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, ext1.prm: Recovery completed for all targets.
2012-04-27 10:03:32 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, ext1.prm: Position of first record processed Sequence 28, RBA 25070608, SCN 0.1219399, Apr 27, 2012 10:00:31 AM.
2012-04-27 10:19:33 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS ext1.
CREATE TABLE SCOTT.PRUEBA_GOLDENGATE
(
ID NUMBER,
NOMBRE VARCHAR2(20 BYTE),
DES VARCHAR2(20 BYTE)
)
TABLESPACE TBS_DBA_DAT_TRX
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Prompt Index PRUEBA_GOLDENGATE_PK;
CREATE UNIQUE INDEX SCOTT.PRUEBA_GOLDENGATE_PK ON SCOTT.PRUEBA_GOLDENGATE
(ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Prompt Non-Foreign Key Constraints on Table PRUEBA_GOLDENGATE;
ALTER TABLE SCOTT.PRUEBA_GOLDENGATE ADD (
CONSTRAINT PRUEBA_GOLDENGATE_PK
PRIMARY KEY
(ID)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
));
COUNT(*)
100 
SQL Error 1403 is ROW NOT FOUND
What is the DML you are trying to do?
Check that 
1403() simply means that the row on which you are trying to do DML upon is not there in the target database. You haven't used any discard file. Include the parameter for discard file in the replicat param file and restart it. It will dump the rejected row in the discard file. Then you can investigate further. 
amardeep.sidhu wrote:
1403() simply means that the row on which you are trying to do DML upon is not there in the target database. You haven't used any discard file. Include the parameter for discard file in the replicat param file and restart it. It will dump the rejected row in the discard file. Then you can investigate further.Else you can use logdump ;) 
Else you can use logdump ;)Haha...discard file is simple...ASCII stuff ;) 
HI everyone
Thank you for your help, I am going to try discard file
Before I execute some inserts, update and truncate.
I will let you know the result
Kind regars ! 
Hi everyone
I try this:
------ 1.- Discarfile
-- Rep Params
REPLICAT rep1
ASSUMETARGETDEFS
USERID goldengate, PASSWORD oracle1
DISCARDFILE discartFileReplicatRep1.err, append
MAP scott.prueba_goldengate, TARGET SH.prueba_goldengate;
--- DiscardFIle
Aborting transaction on /u01/app/goldenGate/dirdat/rt beginning at seqno 0 rba 17008
error at seqno 0 rba 17008
Problem replicating SCOTT.PRUEBA_GOLDENGATE to SH.PRUEBA_GOLDENGATE
Record not found
Mapping problem with compressed key update record (target format)...
*
ID = 99
ID = 8000
NOMBRE = NULL
DES = NULL
*
----------- 2.- Use ./logdump to skip 4 transactions
--- After Skip
GGSCI (infected) 33> info replicat rep1
REPLICAT REP1 Last Started 2012-04-27 16:25 Status ABENDED
Checkpoint Lag 22:11:28 (updated 00:00:19 ago)
Log Read Checkpoint File /u01/app/goldenGate/dirdat/rt000000
2012-04-26 18:14:14.013577 RBA 17008
Logdump 8 >open /u01/app/goldenGate/dirdat/rt000000
Current LogTrail is /u01/app/goldenGate/dirdat/rt000000
Logdump 9 >position 17008
Reading forward from RBA 17008
Logdump 10 >next
2012/04/26 18:14:14.013.577 GGSPKUpdate Len 56 RBA 17008
Name: SCOTT.PRUEBA_GOLDENGATE
After Image: Partition 4 G e
001a 0000 0006 0000 0002 3939 0001 0004 ffff 0000 | ..........99........
0002 0004 ffff 0000 0000 0008 0000 0004 3830 3030 | ................8000
0001 0004 ffff 0000 0002 0004 ffff 0000 | ................
Logdump 11 >next
Logdump 12 >next
Logdump 13 >next
Logdump 14 >exit
---------------- 3.- Restart Replicat, Extract and Both managers
I found a new problem:
OCI Error ORA-00001: unique constraint (SH.PRUEBA_GOLDENGATE_PK) violated (status = 1), SQL <UPDATE "SH"."PRUEBA_GOLDENGATE" SET "ID" = :a1,"NOMBRE" = :a2,"DES" = :a3 WHERE "ID" = :b0>
Aborting transaction on /u01/app/goldenGate/dirdat/rt beginning at seqno 0 rba 17008
error at seqno 0 rba 17008
Problem replicating SCOTT.PRUEBA_GOLDENGATE to SH.PRUEBA_GOLDENGATE
Mapping problem with compressed key update record (target format)...
*
ID = 99
ID = 8000
NOMBRE = NULL
DES = NULL
--- Source
COUNT(*)
101
---- target
Manually I insert this two rows
ID,NOMBRE,DES
8000,8000 Hola Mundo,8000 Oracle Oracle
99,99Hola Mundo,99Oracle Oracle
What do you think, what is the best solution ?? can i skipp all transactions ?? or should I start from scratch ??
Kind regards !!
Edited by: user11237143 on 27/04/2012 03:15 PM 
Can you post the SQL? Is replication working both ways?
Since this is a test, clean both tables , alter all the processes to begin from now onwards have everything up and running and try it once. See how that goes 
Hi
Honestly I don’t have de SQL, when I finish the configuration I execute some insert, update and truncate, to test the synchronization, after that I found the first problem.
The replication is only one way.
aah ok, i wil try this, clean tables and alter process. Sorry how can I alter the process to begin from now onwards ??
thank you !
kind regars 
If this is the first time u run into 1403 in your configured GG environment that has been running for a while, you can try to manually fix it by looking at the data from discard file/trail file. You can also try INSERTMISSINGUPDATES but there are a couple of cavets you need to pay attention.
If you just set this up and bumped into this error, then most likely you didnt have a clean target database to begin with. Might as well just re-instantiate the target and re-try the trail files. 
Just to mention that skipping transactions means loosing data. I hope you area already aware of that.
The most likely reason for such errors is improper initial load. Use SCN method to do the initial load and avoid the use of HANDLECOLLISIONS. You will hit the minimum number of errors. 
From GGSCI prompt you do:
ALTER <PROCESS_NAME> BEGIN NOW;Edited by: N K on 30-abr-2012 5:42 
Hi to Everyone
Thank you for your help, I am going to try to alter the process and I will tell you later what happen
Kind regards 
Hi NK
I did what you tell me, I clean both tables and start from Now, and now everything it is ok
I am going to keep working with GG
Thank you to everyone
Kind Regards 
user11237143 wrote:
Hi NK
I did what you tell me, I clean both tables and start from Now, and now everything it is ok
I am going to keep working with GG
Thank you to everyone
Kind RegardsGlad to hear, it clearly was data issue!

Invalid character value for cast specification

Hi All , I replicat from oracle to ms-sql . the relication process abende with below error .Oure OGG release is 12.2.01  however the table struvure between source"oracle" and sql "tarf=get" like below TargetCREATE TABLE [dbo].[RD_CCB_bills]( [ACCT_ID] [varchar](10) NULL, [CCBBILL_ID] [varchar](10) NULL, [DUE_AMOUNT] [decimal](18, 2) NULL, [CIS_DIVISION] [varchar](5) NULL, [BATCH_NO] [int] NULL, [CRE_DT] [datetime] NULL, [IS_PROCESSED] [bit] NULL) ON [PRIMARY]GO SourceCREATE TABLE "GGATE"."CM_SADAD_CUST_DEBT_UPLOAD"    ( "ACCT_ID" CHAR(10 BYTE) DEFAULT ' ' NOT NULL ENABLE, "CCBBILL_ID" CHAR(12 BYTE) DEFAULT ' ' NOT NULL ENABLE, "DUE_AMOUNT" NUMBER(17,3) DEFAULT 0 NOT NULL ENABLE, "CIS_DIVISION" CHAR(4 BYTE) DEFAULT ' ' NOT NULL ENABLE, "BATCH_NO" NUMBER(8,0) DEFAULT 0 NOT NULL ENABLE, "CRE_DT" DATE, "IS_PROCESSED" CHAR(1 BYTE) DEFAULT '', PRIMARY KEY ("ACCT_ID") 2018-05-09 10:06:32  WARNING OGG-01003  Oracle GoldenGate Delivery for SQL Server, CCBSA01.prm:  Repositioning to rba 404801265 in seqno 0.2018-05-09 10:07:01  INFO    OGG-01021  Oracle GoldenGate Delivery for SQL Server, CCBSA01.prm:  Command received from GGSCI: STATS.2018-05-09 10:09:20  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for SQL Server:  GGSCI command (omohei): info all.2018-05-09 10:09:25  INFO    OGG-01971  Oracle GoldenGate Delivery for SQL Server, CCBSA01.prm:  The previous message, 'INFO OGG-01021', repeated 1 times.2018-05-09 10:09:25  WARNING OGG-00869  Oracle GoldenGate Delivery for SQL Server, CCBSA01.prm: Parameter #: 1 Data Type: 129 DB Part: 7 Length: 10 Max Length: 10 Status: 6 Precision: 10 Scale: 0 Data OverflowNative Error: 0, 0State: 0, 22018Class: 0Source: Line Number: 0Description: Invalid character value for cast specification.2018-05-09 10:09:25  WARNING OGG-01154  Oracle GoldenGate Delivery for SQL Server, CCBSA01.prm:  SQL error -2147217887 mapping GGATE.CM_SADAD_CUST_DEBT_UPLOAD to dbo.RD_CCB_bills [SQL error -2147217887 (0x80040e21)]Parameter #: 1 Data Type: 129 DB Part: 7 Length: 10 Max Length: 10 Status: 6 Precision: 10 Scale: 0 Data OverflowNative Error: 0, 0State: 0, 22018Class: 0
Hi , Can you please share the full report file. Was there any discard generated. Regards,Veera
Hi Osama, This is a Bug and it got fixed OGG 12c. Are you using any tokens in here? Please share the replicat process report file. Bug 21439875 : Replicat abended with ERROR OGG-01296 Error mapping SQL Server Replicat Abended With Invalid character value for cast specification (Doc ID 2082074.1) Regards,Veera

MySQL to Oracle unidirectional replication - no records were replicated

Hello,
I am trying to setup MySQL to Oracle unidirectional replication. The MySQL Extract is functioning. Using Logdump, I see the expected txn (1 update) in the extract trail. The Oracle Replicat is running, with no errors logged, but no records are replicated. Could someone provide guidance / advice on how I should go about troubleshooting this?
Here are the details of my setup:
Environment:
OEL 2.6.18-164.el5 #1 SMP
Oracle GoldenGate Command Interpreter for MySQL Version 11.1.1.0.3 Build 001
Oracle GoldenGate Command Interpreter for Oracle Version 11.1.1.0.0 Build 078
MySQL 5.5.8
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Everything installed on a single Linux machine.
Source = MySQL
extract .prm
EXTRACT EMSQ01
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
SOURCEDB gg_source, USERID gg_source_user, PASSWORD oracle
RMTHOST 127.0.0.1, MGRPORT 15002
RMTTRAIL ./dirdat/et
TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/EDRSR34P1-bin.index
TABLE gg_source.TCUSTMER;
TABLE gg_source.TCUSTORD;
Target = Oracle
replicat.prm
REPLICAT RORA01
USERID gguser, PASSWORD oracle_4U
HANDLECOLLISIONS
SOURCEDEFS ./dirdef/source.def
DISCARDFILE ./dirrpt/RORA01.DSC, PURGE
MAP gg_source.TCUSTMER, TARGET gguser.tcustmer;
MAP gg_source.TCUSTORD, TARGET gguser.tcustord;
Extract Report
2011-05-12 15:01:03 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).
2011-05-12 15:01:03 INFO OGG-01055 Recovery initialization completed for target file .
/dirdat/et000002, at RBA 944.
2011-05-12 15:01:03 INFO OGG-01478 Output file ./dirdat/et is using format RELEASE 10.
4/11.1.
2011-05-12 15:01:03 INFO OGG-01026 Rolling over remote file ./dirdat/et000003.
2011-05-12 15:01:03 INFO OGG-01053 Recovery completed for target file ./dirdat/et00000
3, at RBA 883.
2011-05-12 15:01:03 INFO OGG-01057 Recovery completed for all targets.
2011-05-12 15:01:03 INFO OGG-00182 VAM API running in single-threaded mode.
2011-05-12 15:01:03 INFO OGG-01513 Positioning to Log Number: 4
Record Offset: 3220.
2011-05-12 15:01:03 INFO OGG-01516 Positioned to LogNum - 4
Record Offset - 3220, May 12, 2011 2:44:08 PM.
***********************************************************************
** Run Time Messages **
***********************************************************************
2011-05-12 15:02:59 INFO OGG-01517 Position of first record processed Log Number: 4
Record Offset: 3293, May 12, 2011 3:02:58 PM.
TABLE resolved (entry gg_source.TCUSTMER):
TABLE gg_source.TCUSTMER;
Using the following key columns for source table gg_source.TCUSTMER: CUST_CODE.
2011-05-12 15:03:16 INFO OGG-01021 Command received from GGSCI: REPORT.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2011-05-12 15:03:16 (activity since 2011-05-12 15:02:59)
Output to ./dirdat/et:
From Table gg_source.TCUSTMER:
# inserts: 0
# updates: 1
# deletes: 0
# discards: 0
***********************************************************************
** Run Time Warnings **
***********************************************************************
Replicat Report
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file ./dirdat/et000001 at 2011-05-12 14:56:34
Switching to next trail file ./dirdat/et000002 at 2011-05-12 14:56:34 due to EOF, with current
RBA 2837
Opened trail file ./dirdat/et000002 at 2011-05-12 14:56:34
Processed extract process graceful restart record at seq 2, rba 883.
Switching to next trail file ./dirdat/et000003 at 2011-05-12 15:01:04 due to EOF, with current
RBA 944
Opened trail file ./dirdat/et000003 at 2011-05-12 15:01:04
Processed extract process graceful restart record at seq 3, rba 883.
2011-05-12 15:03:33 INFO OGG-01021 Command received from GGSCI: REPORT.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : ./dirdat/et000003
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 23 (x0017) IO Time : 2011-05-12 15:02:58.000096
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 3437 AuditPos : 4
Continued : N (x00) RecCount : 1 (x01)
2011-05-12 15:02:58.000096 FieldComp Len 23 RBA 944
Name: gg_source.TCUSTMER
___________________________________________________________________
Reading ./dirdat/et000003, current RBA 1092, 0 records
Report at 2011-05-12 15:03:33 (activity since 2011-05-12 14:56:34)
No records were replicated.
***********************************************************************
** Run Time Warnings **
***********************************************************************
Thanks in advance for any insight!
Jenny 
With help from Glenn Goodrich and Joe Debuzna, the issue has been resolved.
MySQL is case sensitive. So, by adding a single set of double-quotes around the "source_db.table_name" (entered in the same case as defined in the MySQL database) in the Replicat .prm file for the TABLE - MAP parameters, the Replicat process now applies the transactions captured in the trail and records are replicated!

Replicat not moving to next record

Replicat not moving to next record... Even i tried with the next position with the logdump, it wont moving..
GGSCI (sdfhlc) 3> send REPR2C1, status
Sending STATUS request to REPLICAT REPR2C1 ...
Current status: At EOF
Sequence #: 3516
RBA: 145512984
_250 records in current transaction_
GGSCI (sdfhlc) 8> stop REPR2C1
Sending STOP request to REPLICAT REPR2C1 ...
STOP request pending end-of-transaction (250 records so far)..
After doing the changes
GGSCI (sdfhlc) 13> send REPR2C1, status
Sending STATUS request to REPLICAT REPR2C1 ...
Current status: At EOF
Sequence #: 3516
RBA: 145512984
_249 records in current transaction_
How to make the replicat lag current 
More update
GGSCI (sdfhlc) 16> info REPR2C1, showch
REPLICAT REPR2C1 Last Started 2013-03-19 14:43 Status RUNNING
Checkpoint Lag 105:11:57 (updated 00:30:44 ago)
Log Read Checkpoint File /gger2/ggs/ora11/dirdat/e2003516
2013-03-15 05:29:22.998388 RBA 140768876
Current Checkpoint Detail:
Read Checkpoint #1
GGS Log Trail
Startup Checkpoint (starting position in the data source):
Sequence #: 3516
RBA: 140768876
Timestamp: 2013-03-15 05:29:22.998388
Extract Trail: /gger2/ggs/ora11/dirdat/e2
Current Checkpoint (position of last record read in the data source):
Sequence #: 3516
RBA: 140768876
Timestamp: 2013-03-15 05:29:22.998388
Extract Trail: /gger2/ggs/ora11/dirdat/e2
CSN state information:
CRC: DC-B-BA-54
CSN: Not available
Header:
Version = 2
Record Source = A
Type = 1
# Input Checkpoints = 1
# Output Checkpoints = 0
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 0
Transaction Integrity = -1
Task Type = 0
Status:
Start Time = 2013-03-19 14:43:23
Last Update Time = 2013-03-19 14:41:20
Stop Status = A
Last Result = 0 
Looks like the Replicat is at the last record in the trail, based on At EOF. Is something probiting the Replicat from writing new records into the target database? Is the file system full, etc? 
I was got like this proble,, i check trail with rba, if the RBA in trail at last record move to next trail with Alter Statement..
I hope can help
riyas

OGG-01296  Error mapping from SENDER.KAM1 to RECEIVER.KAM1.

Hi All,
I'm having table kam. # source .
CREATE TABLE KAM1
(
NAME VARCHAR2(10 BYTE),
CITY VARCHAR2(10 BYTE),
DON NUMBER,
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Three columns are add on target.
CREATE TABLE KAM1
(
NAME VARCHAR2(10 BYTE),
CITY VARCHAR2(10 BYTE),
DON NUMBER,
S_JOB_ID NUMBER(19) DEFAULT null,
S_REPLICATED_DATE DATE,
S_EXTRACT_IND NUMBER(5),
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
My Extract file is
extract group
extract ext1
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="GGATEONE")
connection to database
userid ggate, password qwerty
hostname and port for trail
rmthost 10.76.135.83, mgrport 7809
path and name for trail
rmttrail /u02/ggate/dirdat/l1
--DDL support
ddl include mapped objname sender.kam1;
--DML
table sender.kam1;
Replicat file is :-
Replicat group
replicat rep1
--source and target definitions
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="GGATEONE")
ASSUMETARGETDEFS
target database login
userid ggate, password qwerty
file for dicarded transaction
discardfile /u02/ggate/discard/rep1_discard.txt, append, megabytes 10
--ddl support
DDL
Specify table mapping -
map sender.kam1, target receiver.kam1, COLMAP ( USEDEFAULTS , S_REPLICATED_DATE = #GETENV("GGHEADER","COMMITTIMESTAMP") , S_EXTRACT_IND = 1);
Insert operations are replicating . But updates are failing.
Supplemental logging is enabled both for DB and Table level on both source and target.
Please help. 
Could you please run the report and check the Operation types
or type the send <<process_name>> stats 
Following is the out put of report
GGSCI (node1.ad.*****.com) 155> view report rep1
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Oracle 11g on Oct 7 2011 15:40:15
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-06-06 11:46:00
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Jan 21 08:28:30 EST 2009, Release 2.6.18-128.el5PAE
Node: node1.ad.*****.com
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 2968
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
Replicat group
replicat rep1
--source and target definitions
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1" )
Set environment variable (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
SETENV (ORACLE_SID="GGATEONE")
Set environment variable (ORACLE_SID=GGATEONE)
ASSUMETARGETDEFS
target database login
userid ggate, password ******
file for dicarded transaction
discardfile /u02/ggate/discard/rep1_discard.txt, append, megabytes 10
--ddl support
DDL
Specify table mapping -
map sender.kam1, target receiver.kam1, COLMAP ( USEDEFAULTS , S_REPLICATED_DATE = #GETENV("GGHEADER","COMMITTIMESTAMP") , S_EXTRACT_IND = 1);
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 512M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1G
CACHESIZEMAX (strict force to disk): 881M
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
Opened trail file /u02/ggate/dirdat/l1000002 at 2013-06-06 11:46:00
2013-06-06 11:46:00 INFO OGG-01014 Positioning with begin time: Jun 6, 2013 11:45:56 AM, starting record time: Jun 6, 2013 11:44:02 AM at extseqno 2, extrba 2958.
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file /u02/ggate/dirdat/l1000002 at 2013-06-06 11:46:00
Switching to next trail file /u02/ggate/dirdat/l1000003 at 2013-06-06 11:46:04 due to EOF, with current RBA 2958
Opened trail file /u02/ggate/dirdat/l1000003 at 2013-06-06 11:46:04
Processed extract process graceful restart record at seq 3, rba 992.
MAP resolved (entry SENDER.KAM1):
map SENDER.KAM1, target receiver.kam1, COLMAP ( USEDEFAULTS , S_REPLICATED_DATE = #GETENV("GGHEADER","COMMITTIMESTAMP") , S_EXTRACT_IND = 1);
2013-06-06 11:46:14 WARNING OGG-00869 No unique key is defined for table KAM1. All viable columns will be used to represent the key, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.
Using the following default columns with matching names:
NAME=NAME, CITY=CITY, DON=DON, S_JOB_ID=S_JOB_ID, S_REPLICATED_DATE=S_REPLICATED_DATE, S_EXTRACT_IND=S_EXTRACT_IND
Using the following key columns for target table RECEIVER.KAM1: NAME, CITY, DON, S_JOB_ID, S_REPLICATED_DATE, S_EXTRACT_IND.
2013-06-06 11:46:35 WARNING OGG-01431 Aborted grouped transaction on 'RECEIVER.KAM1', Mapping error.
2013-06-06 11:46:35 WARNING OGG-01003 Repositioning to rba 1196 in seqno 3.
2013-06-06 11:46:35 WARNING OGG-01151 Error mapping from SENDER.KAM1 to RECEIVER.KAM1.
2013-06-06 11:46:35 WARNING OGG-01003 Repositioning to rba 1196 in seqno 3.
Source Context :
SourceModule : [er.main]
SourceID : [scratch/mmar/view_storage/mmar_111112_tier3/oggcore/OpenSys/src/app/er/rep.c]
SourceFunction : [take_rep_err_action]
SourceLine : [16148]
ThreadBacktrace : [8] elements
: [u02/ggate/replicat(CMessageContext::AddThreadContext()+0x26) [0x8215846]]
: [u02/ggate/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x817) [0x820bf97]]
: [/u02/ggate/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, DBString<777> const&, CMessageFactory::MessageDisp
osition)+0x88) [0x81ee5d8]]
: [u02/ggate/replicat [0x841dc6f]]
: [u02/ggate/replicat [0x84f989c]]
: [u02/ggate/replicat(main+0x826) [0x8137206]]
: [lib/libc.so.6(__libc_start_main+0xdc) [0xb3be8c]]
: [u02/ggate/replicat(__gxx_personality_v0+0x1b9) [0x8113711]]
2013-06-06 11:46:35 ERROR OGG-01296 Error mapping from SENDER.KAM1 to RECEIVER.KAM1.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : /u02/ggate/dirdat/l1000003
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 63 (x003f) IO Time : 2013-06-06 11:46:32.000090
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 310 AuditPos : 252944
Continued : N (x00) RecCount : 1 (x01)
2013-06-06 11:46:32.000090 GGSKeyFieldComp Len 63 RBA 1196
Name: SENDER.KAM1
___________________________________________________________________
Reading /u02/ggate/dirdat/l1000003, current RBA 1196, 1 records
Report at 2013-06-06 11:46:35 (activity since 2013-06-06 11:46:14)
From Table SENDER.KAM1 to RECEIVER.KAM1:
# inserts: 1
# updates: 0
# deletes: 0
# discards: 1
DDL replication statistics:
Operations: 0
Mapped operations: 0
Unmapped operations: 0
Other operations: 0
Excluded operations: 0
Errors: 0
Retried errors: 0
Discarded errors: 0
Ignored errors: 0
Last log location read:
FILE: /u02/ggate/dirdat/l1000003
SEQNO: 3
RBA: 1196
TIMESTAMP: 2013-06-06 11:46:32.000090
EOF: NO
READERR: 0
2013-06-06 11:46:35 ERROR OGG-01668 PROCESS ABENDING. 
A couple of things to mention here.
First your tables have no unique keys on them, no PK or UI, and they are actually different between source and target. OGG builds a pseudo key out of the entire row in the absence of a declared PK or UI, so for the source, the key is all the columns (name, city, don) and for the target the key becomes all of the target columns, including the ones that are only in the target and not in the source.
So when an Update or Delete comes through from the source, there's no values for those last few columns in the target to satisfy the WHERE condition because those columns don't exist on the source.
If you don't want an actual PK or UI on the source and target tables, use the KEYCOLS in the Replicat to tell the Replicat the key columns are only of those in the source. Like this:
map sender.kam1, target receiver.kam1, KEYCOLS (name,city,don), COLMAP ( USEDEFAULTS , S_REPLICATED_DATE = #GETENV("GGHEADER","COMMITTIMESTAMP") , S_EXTRACT_IND = 1);
I might be wrong on the order of KEYCOLS in the map statement, so look it up in the docs to verify the syntax.
Second concern, since your source and target are different structures, you should use SOURCEDEFS instead of ASSUMETARGETDEFS, and will need a DEFSFILE from the source and placed in the target. It may work sometimes with ASSUMETARGETDEFS if the columns are in the same position between source and target for those 3 columns that are the same, but if they get out of order then you will see some problems for sure.
Finally, your Extract is writing to a remote host and not a local trail. If this is for testing purposes that's alright, but for production you don't want the Extract doing the job of a Pump, as any network outage will bring down your Extract as well.

Categories

Resources