LATEST TOPICS

GoldenGate: Setting trail files auto purge and purging trails on demand

Introduction

In today’s post, I will discuss about purging old trail files for a GoldenGate replication setup. GoldenGate generates two set of trail files. The first set is generated by the EXTRACT process after processing the transactions from source database redo log files. The other set is generated when the source trail files are transferred to the target site for processing by the REPLICAT process.

By default, these trail files are not purged on completion of processing. We need to instruct GoldenGate to purge these files upon processing to avoid any disk space issue which may eventually lead to a broken replication. To facilitate the purging of trail files, GoldenGate provides a parameter called PURGEOLDEXTRACTS which can be configured either in the MANAGER parameter file or in EXTRACT and REPLICAT parameter files respectively.

It is recommended to configure the PURGEOLDEXTRACTS parameter in the MANAGER parameter file rather than configuring it individually for EXTRACT or REPLICAT, as that would facilitate a centralized control for managing trail files.

The syntax for PURGEOLDEXTRACTS parameter is as follows.

----//
----// syntax for PURGEOLDEXTRACTS parameter //----
----//
PURGEOLDEXTRACTS trail [, USECHECKPOINTS | NOUSECHECKPOINT ] [, MINKEEP_rule ] 

—- Where

trail Trail files to purge. A relative or fully qualified name can be used
USECHECKPOINTS Allows purging of trail files according to any MINKEEP_rule once the EXTARCT and REPLICAT process are done with processing the data as indicated by respective checkpoint tables. If the trails are not yet processed by GoldenGate and MINKEEP_rule is already exceeded, it will not purge the trails until GG process the trails. This is the default option for PURGEOLDEXTRACTS
NOUSECHECKPOINT Allows purging of trail files without considering the checkpoint tables. Not recommended as that could lead to purging of trail files which are not yet processed.
MINKEEP_rule MINKEEP_rule allows to define a threshold on how many or how long the trail files to be retained. If MINKEEP_rule is not defined, GG will retain only one trail file by default. The possible values are
—- MINKEEPHOURS n : Retains an inactive trail file for the specified number of hours
—- MINKEEPDAYS n : Retains an inactive trail file for the specified number of days
—- MINKEEPFILES n: Retains at least n trail files including the active trails.

Lets go through a real time example to understand more about this purging process.

Demonstration

In the following example, we have GoldenGate configured for source database orplab01a and target database orplab01b.

##----
##---- Source (orplab01a)
##----
GGSCI (mylab01) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     D1_lab01a     00:00:00      00:00:01
EXTRACT     RUNNING     E1_lab01a     00:00:00      00:00:05
REPLICAT    RUNNING     R1_lab01a     00:00:03      00:00:06


##----
##---- Target (orplab01b)
##----
GGSCI (mylab02) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     D1_lab01b     00:00:00      00:00:09
EXTRACT     RUNNING     E1_lab01b     00:00:00      00:00:03
REPLICAT    RUNNING     R1_lab01b     00:00:00      00:00:06

The trail files are located on “/ggsdata” file system on both source and target servers. Currently the source trail file system utilization is reported as follows.

##----
##---- Trail file system utilization reaching threshold
##----
ggs@mylab01:/ggsdata/dirdat/orplab01a: df -h .
Filesystem            Size  Used Avail Use% Mounted on
clnnt-lab-f0013:/vol/orplab01a/ggsdata
                       30G   27G  4.0G  87% /ggsdata

Upon checking detailed usage, we identified that most of the space is consumed by the trail files from REPLICAT process r1_lab01a.

##----
##---- space consumed by REPLICAT trail files 
##----					   
ggs@mylab01:/ggsdata/dirdat/orplab01a: du -sch *
4.3M    e1_lab01a
27G     r1_lab01a
27G     total

While checking for the oldest trail file for the REPLICAT process, we found out that GoldenGate has retained trail from last two days.

##----
##---- Current system time
##----
ggs@mylab01:/ggshome/11.2/dirprm: date
Wed Dec  2 08:22:01 CST 2015					 

##----
##---- Oldest available trail file
##----
ggs@mylab01:/ggshome/11.2: ls -lrt /ggsdata/dirdat/orplab01a/r1_lab01a/rt* | head -2
-rw-r----- 1 ggs ggs 199999706 Nov 30 12:09 /ggsdata/dirdat/orplab01a/r1_lab01a/rt001117
-rw-r----- 1 ggs ggs   4155268 Nov 30 13:48 /ggsdata/dirdat/orplab01a/r1_lab01a/rt001118

##----
##---- Latest trail files
##----
ggs@mylab01:/ggshome/11.2: ls -lrt /ggsdata/dirdat/orplab01a/r1_lab01a/rt* | tail -2
-rw-r----- 1 ggs ggs 199999706 Dec  2 06:13 /ggsdata/dirdat/orplab01a/r1_lab01a/rt001256
-rw-r----- 1 ggs ggs   4155268 Dec  2 07:34 /ggsdata/dirdat/orplab01a/r1_lab01a/rt001257

However, when we checked the status of REPLICAT process on source site, we could notice GoldenGate has already processed those trail files (as there were no lags reported as per the earlier output) and is currently processing the latest trail file.

##----
##---- status of REPLICAT process 
##----
GGSCI (mylab01) 2> info R1_lab01a

REPLICAT   R1_lab01a   Last Started 2015-12-02 05:03   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /ggsdata/dirdat/orplab01a/r1_lab01a/rt001257
                     2015-12-02 07:37:39.994140  RBA 77790038

With everything cross-checked, we have ensured that the old trail files are already processed and can be purged. Now the question is why it was not purged automatically.
Well, that depends on whether PURGEOLDEXTRACTS is defined in the respective parameter file or not.

Lets take a look at the MANAGER parameter file to find out whether PURGEOLDEXTRACTS is defined or not.

					 
##----
##---- GG MANAGER parameter file
##----
ggs@mylab01:/ggshome/11.2/dirprm: cat mgr.prm
--------------------------------------------------------------------------------
PORT 7809
DYNAMICPORTLIST 7810-7830
USERID ggs@orplab01a, PASSWORD AACAAAAAAAAAAAQAEDLBBBTBABVCGBJEAHNJODPJEJYIJDGE , &
ENCRYPTKEY DEFAULT
AUTOSTART ER *
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15

-- Purge old extract entries.
PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/e1_lab01a/et* , USECHECKPOINTS, MINKEEPHOURS 72
PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/r1_lab01a/rt* , USECHECKPOINTS, MINKEEPHOURS 72

As we can see, PURGEOLDEXTRACTS is defined in the MANAGER parameter file. Then, why the trail files were not purged automatically?

Lets carefully examine the PURGEOLDEXTRACTS settings defined in the parameter file. The settings defined in MANAGER parameter file are as follows

PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/e1_lab01a/et* , USECHECKPOINTS, MINKEEPHOURS 72
PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/r1_lab01a/rt* , USECHECKPOINTS, MINKEEPHOURS 72

If we MAP these settings with the PURGEOLDEXTRACTS syntax

----//
----// syntax for PURGEOLDEXTRACTS parameter //----
PURGEOLDEXTRACTS trail [, USECHECKPOINTS | NOUSECHECKPOINT ] [, MINKEEP_rule ]

It is clear that, we have defined to purge trail files using the CHECKPOINT method and want to retain the INACTIVE trail files for 72 hours (MINKEEPHOURS 72). This is why we can see the oldest trail file is from Nov 30 12:09.

ggs@mylab01:/ggshome/11.2: ls -lrt /ggsdata/dirdat/orplab01a/r1_lab01a/rt* | head -1
-rw-r----- 1 ggs ggs 199999706 Nov 30 12:09 /ggsdata/dirdat/orplab01a/r1_lab01a/rt001117

Now, considering the current trail generation rate, it looks like keeping 72 hours of trail logs would not be a good option given the space available on the file system. We need to immediately purge the old trails to make room for upcoming trail files.

There are two options to purge the old trail files. Either we can manually purge them using OS commands (rm) or we can instruct GoldenGate to purge those files for us. Purging manually through OS commands is bit risky as there is a potential risk to accidentally delete an ACTIVE trail file. The best option is to have GoldenGate purge the old trail files for us.

To do that, we need to modify the existing PURGEOLDEXTRACTS settings in MANAGER parameter file as shown below.

##----
##---- Modified GG MANAGER parameter file
##----
ggs@mylab01:/ggshome/11.2/dirprm: cat mgr.prm
--------------------------------------------------------------------------------
PORT 7809
DYNAMICPORTLIST 7810-7830
USERID ggs@orplab01a, PASSWORD AACAAAAAAAAAAAQAEDLBBBTBABVCGBJEAHNJODPJEJYIJDGE , &
ENCRYPTKEY DEFAULT
AUTOSTART ER *
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15

-- Purge old extract entries.
PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/e1_lab01a/et* , USECHECKPOINTS, MINKEEPHOURS 24
PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/r1_lab01a/rt* , USECHECKPOINTS, MINKEEPHOURS 24

We have modified the PURGEOLDEXTRACTS settings to keep trail files only for last 24 hours. This will enable us to purge 48 hours of old trail files. We need to refresh the MANAGER process for the modification to take effect as show below.

##----
##---- refreshing GG MANAGER for parameter changes to take effect
##----
GGSCI (mylab01) 3> refresh mgr

Sending REFRESH request to MANAGER ...
Mgr Params Updated

Once the MANAGER process is refreshed and the new settings are in effect, GoldenGate will purge the old trail files according to new PURGEOLDEXTRACTS settings. The purge process start in every 10 minutes of default interval unless explicitly changed using CHECKMINUTES parameter. When the purging initiates, we can observe entries similar to the following in the ggserror.log file.

2015-12-02 07:51:19  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.205.132.25:54441 (REFRESH).
2015-12-02 07:51:19  WARNING OGG-00959  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/r1_lab01a/et* , USECHECKPOINTS, MINKEEPHOURS 72 (MINKEEPFILES option not used.).
2015-12-02 07:51:19  WARNING OGG-00959  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS /ggsdata/dirdat/orplab01a/r1_lab01a/rt* , USECHECKPOINTS, MINKEEPHOURS 24 (MINKEEPFILES option not used.).
2015-12-02 07:54:31  INFO    OGG-00957  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggsdata/dirdat/orplab01a/r1_lab01a/rt001117, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 1257 > 1117.
2015-12-02 07:54:31  INFO    OGG-00957  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggsdata/dirdat/orplab01a/r1_lab01a/rt001118, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 1257 > 1118.
2015-12-02 07:54:31  INFO    OGG-00957  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggsdata/dirdat/orplab01a/r1_lab01a/rt001119, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 1257 > 1119.

Once the purge process is completed, we could see the old trails are purged and the oldest trail file is from the window defined with the latest MINKEEP_rule (24 hours).

##----
##---- Oldest trail, post GG purge job
##----
ggs@mylab01:/ggshome/11.2/dirprm: ls -lrt /ggsdata/dirdat/orplab01a/r1_lab01a/rt* | head -2
-rw-r----- 1 ggs ggs 199999438 Dec  1 09:02 /ggsdata/dirdat/orplab01a/r1_lab01a/rt001181
-rw-r----- 1 ggs ggs 199999814 Dec  1 09:03 /ggsdata/dirdat/orplab01a/r1_lab01a/rt001182

Further, the space utilization also came down to 48% from 87%.

##----
##---- Space utilization after purge job
##----
ggs@mylab01:/ggshome/11.2/dirprm: df -h /ggsdata/
Filesystem            Size  Used Avail Use% Mounted on
clnnt-lab-f0013:/vol/orplab01a/ggsdata
					   30G   15G   16G  48% /ggsdata


ggs@mylab01:/ggsdata/dirdat/orplab01a: du -sch *
4.3M    e1_lab01a
15G     r1_lab01a
15G     total

If we want, we can revert back the PURGEOLDEXTRACTS settings using the same process outlined in the above section.

Conclusion

In this article, we have learned about purging old trail files for a GoldenGate setup. We have also explored, how we can dynamically change the PURGEOLDEXTRACTS settings to reclaim space consumed by old trail files.

It is recommended to always define PURGEOLDEXTRACTS parameter with USECHECKPOINTS (default) option as that protect ACTIVE trails from unwanted deletion.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed