Replicat abending with database error 1403

Replicat abending with database error 1403() during the initial startup during a compressed update.

This error can be seen very frequently on a production environment when implementing Goldengate for the first time.

During the instantiation of the target database, if there is any open transaction which is yet to be committed, the extract is started with "begin now" and then the export/rman backup is started. This open transaction could be missed out by either of the processes and when this open transaction is committed at a later point of time and an update/delete is fired against this transaction which is captured by the extract, guess what happens?

Replicat would not be able to find the row for doing the latter transaction.

There is a way to avoid this situation by altering the extract to the oldest long-pending transaction from gv$transaction/v$transaction. Use the below to find:

SQL> select min(START_TIME) from gv$transaction;

MIN(START_TIME)
--------------------
12/15/11 20:53:54

Alter the extract with the begin timestamp to process the oldest open transaction:
# delete the existing extract and pump trails
# create a new trail sequence for extract and point the pump to read the new trail sequence

# ggsci> alter EXTRACT BEGIN 2011-12-15 20:53:54
# ggsci> alter EXTRACT BEGIN 2011-12-15 20:53:54, threads

# alter the replicat to the new trail sequence and start the processes.

Ofcourse remember to use HANDLECOLLISIONS, until you pass by the time your ORIGINAL TRAIL was processed until.

And for NEXT TIME make sure you run your GV$TRANSACTION sql above. And create the extract process to start capturing from the time you get from GV$TRANSACTION.

Replicat Abended Due To Oracle Error ORA-12899

PROBLEM STATEMENT:

The report file contains:

2011-10-24 05:48:25 WARNING OGG-00869 Aborting BATCHSQL transaction. Database error 12899 (ORA-12899: value too large for column "SECS5"."ARDELETEDACCTHOLDERAUTH"."HI
NTQ" (actual: 137, maximum: 128)).
2011-10-24 05:48:25 WARNING OGG-01137 BATCHSQL suspended, continuing in normal mode.
2011-10-24 05:48:25 WARNING OGG-01003 Repositioning to rba 388304405 in seqno 362.
2011-10-24 05:48:26 WARNING OGG-00869 OCI Error ORA-12899: value too large for column "SECS5"."ARDELETEDACCTHOLDERAUTH"."HINTQ" (actual: 137, maximum: 128) (status =
12899), SQL ARDELETEDACCTHOLDERAUTH" ("BANKID","RANGEID","ACTIVE","CARDNUMBER","CARDHOLDERNAME","DATEEXPIRED","SHOPPE
RID","SECRETCODE","CHIPSECRET","HINTQ","HINTA>.

PROBLEM AREAS:

1. You have a different NLS_LANG on the source and target system.

or

2. You are using replicat parameter "REPLACEBADCHAR SPACE"

SOLUTION:

Change/add the NLS_LANG param on the replicat to match what is specified on the extract. Make sure to add NLS_LANG parameter is added before the USERID and PASSWORD fields to connect to the database.

In the extract parameter file the NLS_LANG parameter has been set as follows

setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

Example

setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

userid xxxx password yyyy

2. If you are using replicat parameter "REPLACEBADCHAR SPACE"

Another cause for ora-12899 is use of replicat parameter "REPLACEBADCHAR SPACE" when processing multiple byte (multibyte)data. the parameter should not be used for multi-byte data.

For the second cause, do not use "REPLACEBADCHAR SPACE" when processing multi-byte data.