GoldenGate checkpoint table and flashback database

Picture the scenario…Replicat is running your transactions into your target database happily, and then you decide that you need to FLASHBACK DATABASE, or perform a restore.

You would expect Replicat to abend almost immediately after restarting due to some kind of missing record error, right? Well – as long as you are using the optional checkpoint table, you’re actually quite well protected.

GoldenGate keeps track of which transactions it has replicated by two methods. First, and always, it writes to a checkpoint file in the dirchk/ directory. This file must exist – if it gets removed then GoldenGate loses track of your process. Optionally, you can also specify a checkpoint table when creating a replicat process. This can be specified globally, or each process can have its own checkpoint table.

The advantage of the checkpoint table in our scenario, is that this table also gets flashed back along with our data. This means that when replicat is started, it does read the checkpoint file but it gets the recovery point from the checkpoint table as a matter of preference. This means that it’ll start reapplying all of the transactions which occurred since the point which you flashed the database back to.

This really does prove a benefit in retaining a number of days of trail files on your target database, in the event that you do need to perform recovery.

Example follows.

First we create some test data on the source –

SOURCE_DB> insert into test_table values (1,'Before restore point created');

1 row created.

SOURCE_DB> commit;

Commit complete.

SOURCE_DB> insert into test_table values (2,'Another before restore point');

1 row created.

SOURCE_DB> commit;

Commit complete.

Now lets check that it replicated to the target –

TARGET_DB> select * from test_table;

      COL1 COL2
---------- --------------------------------------------------
         1 Before restore point created
         2 Another before restore point

Now on the target, lets create a restore point using flashback database.

TARGET_DB> create restore point MY_RESTORE_POINT guarantee flashback database;

Restore point created.

Now on the source, we’re going to insert some data to be replicated to the target database after the point where the restore point was created.

SOURCE_DB> insert into test_table values (3,'After restore point created');

1 row created.

SOURCE_DB> commit;

Commit complete.

Just check that it replicated –

TARGET_DB> select * from test_table;

      COL1 COL2
---------- --------------------------------------------------
         1 Before restore point created
         2 Another before restore point
         3 After restore point created

Now on the target, we’re going to flashback the database. The database will return to its state when we created the restore point (with two rows in the target table).

TARGET_DB> startup force mount
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2227032 bytes
Variable Size            1090520232 bytes
Database Buffers          553648128 bytes
Redo Buffers                7122944 bytes
Database mounted.
TARGET_DB> flashback database to restore point MY_RESTORE_POINT;

Flashback complete.

TARGET_DB> alter database open resetlogs;

Database altered.

Lets first check the contents of the table (it should be back to how it was before the restore point was created)

TARGET_DB> select * from test_table;

      COL1 COL2
---------- --------------------------------------------------
         1 Before restore point created
         2 Another before restore point

Now lets check what state Replicat is in on the target.

GGSCI (gg2) 1> info *

REPLICAT   RP01   Last Started 2012-10-12 14:56   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:00:33 ago)
Log Read Checkpoint  File ./dirdat/ta000035
                     2012-10-12 14:58:11.000907  RBA 2898271

It has abended because we shut down the database to flash it back. When we restart it, if we were just using checkpoint files, it will think that is has already applied row 3 to the target database. It will start with nothing to do, and will sit waiting for further records from the source. In our case, because we have all trail files still on disk and a checkpoint table, it will think that it has applied row 1 and 2 but row 3 will still need to be applied.

Lets start it

GGSCI (gg2) 2> start replicat RP01

Sending START request to MANAGER ...
REPLICAT RP01 starting


GGSCI (gg2) 3> info *

REPLICAT   RP01   Last Started 2012-10-12 15:08   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  File ./dirdat/ta000035
                     2012-10-12 14:58:11.000907  RBA 2898271

Now lets check the data.

TARGET_DB> select * from test_table;

      COL1 COL2
---------- --------------------------------------------------
         1 Before restore point created
         2 Another before restore point
         3 After restore point created

It has picked up where it left off when the restore point was created.

I think this shows it really is worth using flashback database while you’re setting up GoldenGate, using checkpoint tables, and retaining quite a few days of trail files on disk. It is all too easy to introduce data problems with GoldenGate and using this method its just as easy to fix.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • gjilevski  On October 12, 2012 at 4:49 PM

    Hi,

    Awesome!

    Indeed, this is very useful information related to using OGG with flashback database feature! Thank you for the post.

    I do have a small clarification referring to the statement “Optionally, you can also specify a checkpoint table when creating an extract or replicat.”

    I guess you might want to make is clear that a checkpoint table is only for a replicat.

    Regards,

    • matthewdba  On October 12, 2012 at 5:30 PM

      Hi! You’re right of course – I’ve edited to make that distinction. Thanks.

  • Jamsher  On March 20, 2013 at 4:13 AM

    Thanks for such wonderful information.

Trackbacks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: