Goldengate - Sequence number for trail file has exceeded the maximum threshold

Sequence number for Pump trail file has exceeded the maximum threshold

In 11g, trail file sequence has naming convention of 6 digits. So the sequence number can range from 000000 to 999999.

Once the trail file reaches maximum number, Golden gate process abends.

Once the goldengate process abends, you can see following error in Goldengate error log file ggserr.log

-- Goldengate Error

2017-03-17 11:02:02  ERROR   OGG-06498  Oracle GoldenGate Capture for Oracle, pmpaj.prm:  The sequence number 998999 for output trail file '/u02/ggs/trail/tr' has exceeded the maximum threshold (998999).  Please consult Oracle Knowledge Management Doc ID 1559048.1 for further actions.

2017-03-17 11:02:02  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, pmpaj.prm:  PROCESS ABENDING.

2017-03-17 11:02:11  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, extaj.prm:  Rolling over remote file /u02/ggs/trail/tr998998.

2017-03-17 11:02:11  ERROR   OGG-06498  Oracle GoldenGate Capture for Oracle, extaj.prm:  The sequence number 998999 for output trail file '/u02/ggs/trail/tr' has exceeded the maximum threshold (998999).  Please consult Oracle Knowledge Management Doc ID 1559048.1 for further actions.

2017-03-17 11:02:11  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, extaj.prm:  PROCESS ABENDING.

Extract Server

delete RMTTRAIL /u02/ggs/trail/tr, extract PMPAJ

add RMTTRAIL /u02/ggs/trail/ts, extract PMPAJ

edit param PMPAJ

ADD EXTRACT EXTAJ , TRANLOG, BEGIN NOW

ADD EXTTRAIL /u02/ggs/trail/ts, EXTRACT EXTAJ

-- Commands executed can be noticed in ggserr.log

2017-03-17 11:47:37  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): delete RMTTRAIL /u02/ggs/trail/tr  extract PMPAJ.

2017-03-17 11:47:46  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add RMTTRAIL /u02/ggs/trail/ts  extract PMPAJ.

2017-03-17 11:47:56  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit param PMPAJ.

2017-03-17 11:53:02  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): alter extract EXTAJ BEGIN 2017-03-17 11:02:00.

2017-03-17 11:53:04  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.

2017-03-17 11:53:08  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start EXTAJ.

2017-03-17 11:53:08  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, extaj.prm:  Positioning to begin time Mar 17, 2017 11:02:00 AM.

2017-03-17 11:53:09  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT EXTPEG starting.

Replicat Server

stop replicat RPRAJ

alter replicat RPRAJ, exttrail /u02/ggs/trail/ts

start replicat RPRAJ

GGSCI (dclnppgdb1) 27> INFO RPRAJ

REPLICAT   RPRPAJ  Last Started 2017-03-17 11:55   Status RUNNING

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

Log Read Checkpoint  File /u02/ggs/trail/ts000000

                     First Record  RBA 0

We can set bash script to monitor trail file sequence threshold limit on extract and replicat server.

In next post I am posting bash script seq_monitor.sh to monitor trail file sequence threshold limit.

In 12c Oracle has a new feature regarding Trail File Sequence Length :-

Nine Digit Trail File Sequence Length

In 12.2, the default is to create trail files with 9 digit sequence numbers instead of the earlier 6 digit sequence. This now will allow 1000 times more files per trail – basically 1 billion files per trail!.

We can upgrade existing trail files from 6 to 9 digit sequence numbers using a utility called convchk and there is also backward compatibility support for existing 6 digit sequences using a GLOBAL parameter called TRAIL_SEQLEN_6D.