Tuesday, November 11, 2014

WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm


Issue/Problem,

2014-11-11 13:06:58 WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm: Waiting at EOF on input trail file /s01/ggsdata/dirdat/ig000035, which is not marked as complete; but succeeding trail file /s01/ggsdata/dirdat/ig000036 exists. If ALTER ETROLLOVER has been performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader. 

Reason for this problem

There has been a ETROLLOVER command issued on either Source Extract or Pump, due to which GG has moved to the new trail on the source Extract or Pump. While moving to the new trail file on the source environment, Golden Gate doesn’t make EOF comment or transaction mark on the old trail file. On the replicat site, GG will be waiting for the data or EOF message to come for the current trail from the source environment but another trail file started to write on the replicate site and replicat won’t be knowing it.

We have to manually move the trail file, else it will be staying on the current position.

Step involved in moving to the next trail

#1 Checking the status of current replicat

GGSCI (c959bhfengdnp) 11> info r0000031  

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000035
                     First Record  RBA 12629

When you check at the OS level, we can see 3 more trail files created and replicate won’t be knowing it and replicat will be staying on the sequence number 35.

GGSCI (c959bhfengdnp) 12> exit
ggs@bhuggs1:/s01/11.2: ls -lrt  /s01/ggsdata/dirdat/ig*

-rw-r----- 1 ggs ggs     3523 Nov  5 11:50 /s01/ggsdata/dirdat/ig000026
-rw-r----- 1 ggs ggs     3015 Nov  5 20:44 /s01/ggsdata/dirdat/ig000027
-rw-r----- 1 ggs ggs     1288 Nov  5 20:56 /s01/ggsdata/dirdat/ig000028
-rw-r----- 1 ggs ggs     1422 Nov  5 21:27 /s01/ggsdata/dirdat/ig000029
-rw-r----- 1 ggs ggs     1288 Nov  5 21:34 /s01/ggsdata/dirdat/ig000030
-rw-r----- 1 ggs ggs 99999978 Nov  5 23:01 /s01/ggsdata/dirdat/ig000031
-rw-r----- 1 ggs ggs 99999929 Nov  5 23:05 /s01/ggsdata/dirdat/ig000032
-rw-r----- 1 ggs ggs 99999843 Nov  5 23:09 /s01/ggsdata/dirdat/ig000033
-rw-r----- 1 ggs ggs 60431010 Nov  8 11:35 /s01/ggsdata/dirdat/ig000034
-rw-r----- 1 ggs ggs    12629 Nov 10 14:00 /s01/ggsdata/dirdat/ig000035
-rw-r----- 1 ggs ggs     1560 Nov 10 16:12 /s01/ggsdata/dirdat/ig000036
-rw-r----- 1 ggs ggs     3859 Nov 11 01:58 /s01/ggsdata/dirdat/ig000037
-rw-r----- 1 ggs ggs    17533 Nov 11 14:37 /s01/ggsdata/dirdat/ig000038

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.27 19591627 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_141006.1156_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct  6 2014 16:45:49
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.


GGSCI (c959bhfengdnp) 1> info r0000031 

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:13 ago)
Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000035
                     First Record  RBA 12629

#2 To check whether GG has updated all the transaction in the current trail file;

To check whether any transaction left (or) it has reached the EOF; to check that we will be using send <REPLICAT NAME> logend, if the output is “YES” then it means it has reached the EOF if not then you have to use
'grouptransops 1 & maxtransops 1' in the parameter file of replicat and let it run, see that the replicat has applied all the records in the trail which is currently reading and check again the logend command and this should result in YES.  Once you get Yes then you need to remove the parameter 'grouptransops 1 & maxtransops 1' from the Replicat file(if you are not clear, please don’t use it).

GGSCI (c959bhfengdnp) 2> send r0000031 logend

Sending LOGEND request to REPLICAT R0000031 ...
YES.

#3 Stop the Replicat to change the Sequence number of the trail file.

GGSCI (c959bhfengdnp) 3> stop r0000031

Sending STOP request to REPLICAT R0000031 ...
Request processed.

#4 Change the Sequence number of the trail file and RBA

GGSCI (c959bhfengdnp) 4> alter REPLICAT R0000031, EXTSEQNO 36,  EXTRBA 0
REPLICAT altered.

#5 Just check the current state of the Replicat before starting it

GGSCI (c959bhfengdnp) 1> info r0000031 

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:13 ago)
Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000036
                     First Record  RBA 0

#6 start the Replicat

GGSCI (c959bhfengdnp) 5> start REPLICAT R0000031


Sending START request to MANAGER ...
REPLICAT R0000031 starting

#7 checking the status of the replicat

GGSCI (c959bhfengdnp) 6> info REPLICAT R0000031
REPLICAT   R0000031  Last Started 2014-11-11 14:53   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000038
                     2014-11-11 14:52:42.769737  RBA 17852


It has reached end of the trail file and it is waiting for the information from the source.


Happy Reading :)