Options for Creating the Checkpoint Table

The checkpoint table can reside in a schema of your choice. Use one that is dedicated to Oracle GoldenGate if possible.

More than one instance of Oracle GoldenGate (multiple installations) can use the same checkpoint table. Oracle GoldenGate keeps track of the checkpoints, even if Replicat group names are the same in different instances.

More than one checkpoint table can be used as needed. For example, you can use different ones for different Replicat groups.

You can install your checkpoint tables in these ways:

  • You can specify a default checkpoint table in the GLOBALS file. New Replicat groups created with the ADD REPLICAT command will use this table automatically, without requiring any special instructions.

  • You can provide specific checkpoint table instructions when you create any given Replicat group with the ADD REPLICAT command:

    • To use a specific checkpoint table for a group, use the CHECKPOINTTABLE argument of ADD REPLICAT. This checkpoint table overrides any default specification in the GLOBALS file. If using only one Replicat group, you can use this command and skip creating the GLOBALS file altogether.

    • To omit using a checkpoint table for a group, use the NODBCHECKPOINT argument of ADD REPLICAT. Without a checkpoint table, Replicat still maintains checkpoints in a checkpoint file on disk, but you introduce the risk of data inconsistency.

However you implement the checkpoint table, you must create it in the target database prior to using the ADD REPLICAT command.

To Add a Checkpoint Table to the Target Database

The following steps, which create the checkpoint table through GGSCI, can be bypassed by running the chkpt_db_create.sql script instead, where db is an abbreviation of the database type. By using the script, you can specify custom storage or other attributes. Do not change the names or attributes of the columns in this table.

  1. From the Oracle GoldenGate directory, run GGSCI and issue the DBLOGIN command to log into the database. The user issuing this command must have CREATE TABLE permissions. See Reference for Oracle GoldenGate for the correct syntax to use for your database.

  2. In GGSCI, issue the following command to add the checkpoint table to the database.

    ADD CHECKPOINTTABLE container owner.table

    Where:

    owner.table is the owner and name of the table, container is the name of a PDB if installing into an Oracle multitenant container database. The owner and name can be omitted if you are using this table as the default checkpoint table and this table is specified with CHECKPOINTTABLE in the GLOBALS file. The name of this table must not exceed the maximum length permitted by the database for object names. The checkpoint table name cannot contain any special characters, such as quotes, backslash, pound sign, and so forth.

To Specify a Default Checkpoint Table in the GLOBALS File

This procedure specifies a global name for all checkpoint tables in the Oracle GoldenGate instance. You can override this name for any given Replicat group by specifying a different checkpoint table when you create the Replicat group.

  1. Create a GLOBALS file (or edit the existing one, if applicable). The file name must be all capital letters on UNIX or Linux systems, without a file extension, and must reside in the root Oracle GoldenGate directory. You can use an ASCII text editor to create the file, making certain to observe the preceding naming conventions, or you can use GGSCI to create and save it with the correct name and location automatically. When using GGSCI, use the following command, typing GLOBALS in upper case.
    EDIT PARAMS ./GLOBALS
  2. Enter the following parameter:
    CHECKPOINTTABLE container.owner.table

    Where:

    catalog.owner.table is the fully qualified name of the default checkpoint table, including the name of the container if the database is an Oracle multitenant container database (CDB).

  3. Note the name of the table, then save and close the GLOBALS file. Make certain the file was created in the root Oracle GoldenGate directory. If there is a file extension, remove it.