OGG-01061 Invalid CSN value length(0) from data source during recovery in goldengate

 OGG-01061 Invalid CSN value length(0) from data source during recovery.

Causes:

The logCSN number is in the first record of each transaction in trail file.  If for some reason, the pump is stopped/abended at a record that is in the middle of a transaction, it will not have logCSN number, and the pump may abned with above error.

Solution:

A workaround is to find the record in local trail that is related to the last record processed by replicat.  Then the pump may be positioned to the next local record (presumably a starting record for a transaction) and start it.  If the record cannot be identifed (e.g., there is no information about last applied record at replicat), the pump may be positioned  to the begining of an earlier transaction, but this may introduce duplicate reocrds or missing some records.

steps to fix this issue:

step 1: find the EXTTRAILSOURCE path 

info P_FRD03

EXTRACT    P_FRED03  Last Started 2023-06-20 11:59   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  File /opt/app/pxxxx3/acfs/ggs/ggs01/trails/A8/f3000000

                     First Record  RBA 0

-

step 2: get the available file from the location 

ls -lart /opt/app/pxxxx3/ggs/11.2.1.0.3/dirdat/f3*

output will be:

-rw-rw-rw- 1 ggs oinstall 97090686 Jul  7 05:03 /opt/app/pxxx4/ggs/11.2.1.0.3/dirdat/f3000299

step 3: apply the recent / latest file (Note: data lose will be there, so resyncing is required later.) get teh last 3 digit from that trail f3000299 file, which is 299.

stop P_FRD03

delete P_FRD03

ADD EXTRACT P_FRD03, EXTTRAILSOURCE /opt/app/pxxxx3/ggs/11.2.1.0.3/dirdat/f3

ADD RMTTRAIL /opt/app/puuuu23/acfs/ggs/ggs01/trails/A8/d3, EXTRACT P_FRD03

---

ALTER EXTRACT P_FRD03, EXTSEQNO 299, EXTRBA 0

start P_FRD03

info P_FRD03

EXTRACT    P_FRD03 Last Started 2023-06-20 12:44   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  File /opt/app/pxxxx3/ggs/11.2.1.0.3/dirdat/f1067546

                     2023-07-07 04:00:16.000000  RBA 13416936

Conclusion: 

if RBA has generated any value, then your pump is working fine.

How to extract the data from informix db

 Step 1: login into db server and connect to db server.

Step 2: select the database and click info option to list all the tables in db.

Step 3: prepare shell script as below

vi data_extract.sh

dbaccess - - <<SQLSTMT

database <db name>;

unload to tablename.csv delimiter '|' select * from tablename;

SQLSTMT

#Save and close it

#example:

dbaccess - - <<SQLSTMT

database IBM;

unload to frm.csv delimiter '|' select * from frm;

unload to b2b.csv delimiter '|' select * from b2b;

unload to b2b_in.csv delimiter '|' select * from b2b_in;

unload to bays.csv delimiter '|' select * from bays;

unload to cef_ddm_inv.csv delimiter '|' select * from cef_ddm_inv;

SQLSTMT

#Save and close it

Step 4: chmod 775 data_extract.sh

Step 5: execute the file

./data_extract.sh