4 Preparing the Transaction Logs for Oracle GoldenGate

This section contains information that helps you configure database settings and supplemental logging to support capture of source transaction data by Oracle GoldenGate.

This chapter includes the following sections:

4.1 Setting the Database to Full Recovery Model

Oracle GoldenGate requires a SQL Server source database to be set to the full recovery model.

To verify or set the recovery model:

  1. Connect to the SQL Server instance with SQL Server Management Studio for SQL Server.

  2. Expand the Databases folder.

  3. Right-click the source database, and then select Properties.

  4. Select the Options tab.

  5. Under Recovery, set Model to Full if not already.

  6. If the database was in Simple recovery or never had a Full database backup, take a Full database backup before starting Extract. See Section 4.6, "Making a Full Database Backup Before You Start Oracle GoldenGate."

  7. Click OK.

4.2 Backing Up the Transaction Log

Depending on how you configure the management of the secondary truncation-point (see Section 4.5), the Extract process may occasionally require access to a log backup on the source system. This happens when the required log records are no longer available in the online log and have been moved to log backups.

Oracle GoldenGate requires the log backup files on a source system to meet the following conditions:

  • The log backup file must remain in the original location where the backup was made. If the backup is moved, see the ALTARCHIVELOGDEST option in the TRANLOGOPTIONS section of the Reference for Oracle GoldenGate for Windows and UNIX to configure this option. This parameter enables Oracle GoldenGate to search for the backup file in a different location, but must only be used when the backup files are not in the original location.

  • The backup must be made to a DISK device.

  • The backup must be a native SQL Server backup made by issuing the BACKUP LOG command (or the corresponding GUI command) and can be compressed using the native SQL Server compression features using the native SQL server compression feature (SQL Server 2012 only). Third-party log backup tools are not supported.

  • Do not overwrite backup files to the same name as old ones.

  • Striped log backups are not supported.

For optimal performance of the Extract process, do the following:

  • Make only one log backup per backup file.

4.3 Retaining the Log Backups

Retain enough log backups so that if you stop Extract or there is an unplanned outage, Extract can start again from its checkpoints. Extract must have access to the data in the transaction log or a log backup that contains the start of the oldest uncommitted unit of work, and all log backups thereafter.

If data that Extract needs during processing is not retained, either in online logs or in the backups, one of the following corrective actions might be required:

  • Alter Extract to capture from a later point in time for which log data is available (and accept possible data loss on the target).

  • Resynchronize the source and target tables, and then start the Oracle GoldenGate environment over again.

To enable Extract to maintain without user intervention the truncation point so that it always has the logs it needs, see Section 4.5.3, "Keeping the Secondary Truncation Point in Sync With Extract."

To determine where the Extract checkpoints are, use the INFO EXTRACT command. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

4.4 Enabling Supplemental Logging

These instructions apply to new installations of Oracle GoldenGate for all supported SQL Server versions. You will enable supplemental logging with the ADD TRANDATA command so that Extract can capture the information that is required to reconstruct SQL operations on the target. This is more information than what SQL Server logs by default.

ADD TRANDATA must be issued for all tables that will be replicated with Oracle GoldenGate. ADD TRANDATA enables Change Data Capture (CDC) and creates a minimal Change Data Capture on the specified table.

  • Oracle GoldenGate does not use the CDC tables other than as necessary to enable supplemental logging.

  • As part of enabling CDC, SQL Server creates two jobs per database: dbname_capture and dbname_cleanup. The dbname_capture job adjusts the secondary truncation point and captures data from the log to store in the CDC tables. The dbname_cleanup job ages and deletes data captured by CDC.

  • Using the TRANLOGOPTIONS parameter with the MANAGESECONDARYTRUNCATIONPOINT or ACTIVEMANAGESECONDARYTRUNCATIONPOINT option for Extract removes the dbname_capture job, preventing the overhead of the job loading the CDC tables.

  • The alternative (using TRANLOGOPTIONS with NOMANAGESECONDARYTRUNCATIONPOINT) requires the SQL Server Agent to be running and requires the dbname_capture and dbname_cleanup jobs to be retained. You will probably need to adjust the dbname_cleanup data retention period if the default of three days is not acceptable for storage concerns.

  • For more information on TRANLOGOPTIONS, seeSection 4.5, "Managing the Secondary Truncation Point."

To enable supplemental logging:

This procedure requires a database user who is a member of the SQL Server System Administrators (sysadmin) role.

  1. On the source system, run GGSCI.

  2. Issue the following command to log into the database.



    • SOURCEDB DSN is the name of the SQL Server data source.

    • USERID user is the Extract login and PASSWORD password is the password that is required if Extract uses SQL Server authentication. Alternatively, USERIDALIAS alias is the alias for the credentials if they are stored in a credentials store. If using DBLOGIN with a DSN that is using Integrated Windows authentication, the connection to the database for the GGSCI session will be that of the user running GGSCI. In order to issue ADD TRANDATA or DELETE TRANDATA, this user must be a member of the SQL Server sysadmin server role.

  3. In GGSCI, issue the following command for each table that is, or will be, in the Extract configuration. You can use a wildcard to specify multiple table names.

    ADD TRANDATA owner.table

4.5 Managing the Secondary Truncation Point

When you enable supplemental logging with the ADD TRANDATA command for at least one table in a SQL Server database, a secondary truncation point is created in the transaction log that has to be moved for log space to be released as needed, following subsequent log backups. Use the TRANLOGOPTIONS parameter to control how the secondary truncation point is managed. This is a required parameter and it gives you the following options for managing the secondary truncation point:

4.5.1 Oracle GoldenGate Manages the Secondary Truncation Point

Use TRANLOGOPTIONS with the MANAGESECONDARYTRUNCATIONPOINT option if Extract will not be running concurrently (for the same source database) with SQL Server transactional replication and/or CDC that is configured for applications other than Oracle GoldenGate.


Using TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT for Extract when either SQL Server transactional replication and/or CDC for applications other than Oracle GoldenGate are running at the same time causes the SQL Server log reader agent or CDC capture job to fail.

MANAGESECONDARYTRUNCATIONPOINT has the following advantages:

  • Extract manages the secondary truncation point. Extract moves the secondary truncation point forward at a defined interval by issuing the following T-SQL statement:

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

  • The logs are routinely truncated to conserve storage space, but Extract is able to capture from the transaction log backups to capture data it did not process by the time of the truncation point. This ability requires the logs to be backed up only with a native SQL Server backup utility. Third-party backup software cannot be used in this scenario.

MANAGESECONDARYTRUNCATIONPOINT also routinely checks and deletes the CDC capture job. This prevents change data from being collected for all of the tables in the database, resulting in:

  • better performance by Extract

  • less storage space used by captured data in the CDC tables

  • fewer records in the transaction log

If Extract manages the secondary truncation point but is stopped for a longer period of time than the log backup frequency, Extract cannot free up the data in the transaction log after a log backup. This causes the transaction log to grow. To allow space to be freed from the log after backups, you need to explicitly mark the data as 'distributed'. Before starting this Extract group again, retain all of the log backups that contain data that still needs to be processed by performing the following steps:

  1. To determine the log-read checkpoint that shows the oldest log that Extract needs, use the INFO EXTRACT command with the SHOWCH option in GGSCI.

  2. Either manually or from within a SQL Server Agent job, move the secondary truncation point by issuing the following T-SQL command against the source database. Run this command approximately every minute until you start Extract.

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
  3. Stop and disable the SQL Server Agent job or disconnect the query session prior to restarting Extract.

4.5.2 SQL Server Manages the Secondary Truncation Point

Use TRANLOGOPTIONS with the NOMANAGESECONDARYTRUNCATIONPOINT option if Extract will run concurrently (for the same source database) with SQL Server transactional replication and/or CDC that is configured for applications other than Oracle GoldenGate. SQL Server will manage the secondary truncation point.

4.5.3 Keeping the Secondary Truncation Point in Sync With Extract

Use TRANLOGOPTIONS with the ACTIVESECONDARYTRUNCATIONPOINT option to keep the secondary truncation point in sync with Extract. The advantage of this method is that you can use third-party transaction-log backup software without concern that Extract may need to access the log backups to retrieve unprocessed transaction data.

ACTIVESECONDARYTRUNCATIONPOINT prevents the truncation of the active log until Extract is finished capturing the data that is marked for truncation. Because no online data is ever missed, Extract is never required to read the backup logs.

Using ACTIVESECONDARYTRUNCATIONPOINT may cause larger log files if Extract has any significant lag. This happens because transaction log backups will not immediately truncate the logs. Truncation occurs only after Extract has finished capturing the data. Thus, you may need to adjust your storage parameters to accommodate the increased log size.

If you use this option, only one Extract group can be allowed to capture from the source database. Using multiple Extract groups risks severe data loss, and this condition can be resolved only by a full reinstantiation of the target data.

4.6 Making a Full Database Backup Before You Start Oracle GoldenGate

These instructions are for making a full database backup according to the Oracle GoldenGate requirements listed in System Requirements and Preinstallation Instructions.

To make a full backup of the database:

  1. Configure Oracle GoldenGate to your requirements, and then return to this procedure when you are ready to begin initial synchronization and start change replication. To configure Oracle GoldenGate, see Administering Oracle GoldenGate for Windows and UNIX.

  2. Connect to the SQL Server instance with SQL Server Management Studio.

  3. Expand the Databases folder.

  4. Right click the source database name, and then select All Tasks, then Backup Database.

  5. Select Database - Complete. This option makes a full database backup and ensures that no transaction information is lost when Oracle GoldenGate starts.

  6. Under Destination, click Add to specify the backup file name and location.

  7. Click OK. The backup file is added to the Destination list box in the SQL Server Backup dialog box.

  8. Click OK to start the backup.