Monday, November 16, 2015

ERROR OGG-01028 Formatting error on: table name + Error converting timestamp with timezone from Oracle to ASCII format for column

2015-11-16 17:51:18  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  EXTRACT EXTRACT1 started.
2015-11-16 17:51:18  INFO    OGG-01055  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery initialization completed for target file /usr/app/datab1/ggs/trails/a1000007, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Output file /usr/app/datab1/ggs/trails/a1 is using format RELEASE 11.2.
2015-11-16 17:51:18  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Rolling over remote file /usr/app/datab1/ggs/trails/a1000007.
2015-11-16 17:51:18  INFO    OGG-01053  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for target file /usr/app/datab1/ggs/trails/a1000008, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01057  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for all targets.
2015-11-16 17:51:18  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Position of first record processed Sequence 17507, RBA 21008, SCN 3332.2582756465, Nov 16, 2015 5:32:16 PM.
2015-11-16 17:51:19  INFO    OGG-01020  Oracle GoldenGate Delivery for Oracle, rccp004a.prm:  Processed extract process RESTART_ABEND record at seq 8, rba 1081 (aborted 0 records).
2015-11-16 17:51:21  ERROR   OGG-01028  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Formatting error on: table name CSI_CAET.ERROR_HIT, rowid AAAYnTAAGAAACM/AAA, XID 1.1.2642, position (Seqno 17507, RBA 21616). Error converting timestamp with timezone from Oracle to ASCII format for column LAST_UPDATE_DATE.
2015-11-16 17:51:21  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  PROCESS ABENDING.


GGSCI (Sever2) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     ABENDED     EXTRACT1    00:14:10      00:58:53    


GGSCI (Sever2) 2> edit params EXTRACT1

Solution :
Oracle reported bug 14053498 for the above error {GoldenGate Extract Abends: OGG-01028 Formatting Error on: table name xxxx.xxxx rowid , XID nnnn.nn.nnnn, Position (Seqno n, RBA n) (Doc ID 1558791.1)}

Another Solution

Check the database characterset

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY


Add below two parameters to the extract prm file

SETENV (NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1") -- Please make sure you have the correct characterset from the above output
TRANLOGOPTIONS INCLUDEREGIONID

GGSCI (Sever2) 4> start EXTRACT1

Sending START request to MANAGER ...
EXTRACT EXTRACT1 starting


GGSCI (Sever2) 5> info EXTRACT1

EXTRACT    EXTRACT1  Last Started 2015-11-16 18:50   Status RUNNING
Checkpoint Lag       01:13:36 (updated 00:00:01 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-16 17:37:08  Seqno 17507, RBA 176876032
                     SCN 3332.2582759675 (14313413789947)


1 comment:

  1. Thanks...helped me

    -Satya
    http://satya-dba.blogspot.com/2012/02/ggsci-goldengate-command-interpreter.html

    ReplyDelete