26 Preparing the System for Oracle GoldenGate

This chapter contains the requirements for the system and database resources that support Oracle GoldenGate.

This chapter contains the following sections:

• Database Configuration
  
• Database User for Oracle GoldenGate Processes
  
• Preparing Tables for Processing
  
• Preparing the Transaction Logs
  

Database Configuration

The Extract process makes calls directly to the Sybase Replication API on a source Sybase server. The source database on this server must be configured as follows to support data capture by Oracle GoldenGate.

• Because Extract uses the Sybase LTM to read the Sybase transaction log, it cannot run against a database configured with Sybase Replication Server. Only one process at a time can reserve a context that allows it to read the transaction log on the same database.

• Sybase Multisite availability leverages the Sybase Replication Server to replicate transactions to a Warm Standby and a target subscription database. Oracle GoldenGate for Sybase cannot capture from the primary Warm Standby but can capture from the Multisite availability target subscription database because SAP Sybase Rep Server is not in control of the Transaction Log for that database.

• Set the DSQUERY variable to the server that contains the database that Oracle GoldenGate will be using.

• The Extract process must be permitted to manage the secondary log truncation point. For more information, see Initializing the Secondary Truncation Point.

• Configure the database page size to 4k, 8k,16k, 32k, or larger. To use the UPGRADECHECKPOINT table_name command to updated the checkpoint the target Sybase database, it must be configured to have a row size of more than 2K pages to be programmatically created; it will fail to upgrade the checkpoint if the target database is configured to be a 2K page size. This is valid only for Replicat.

Database User for Oracle GoldenGate Processes

Oracle GoldenGate requires a database user account. Create this account and assign privileges according to the following guidelines.

• To preserve the security of your data, and to monitor Oracle GoldenGate processing accurately, do not permit other users, applications, or processes to log on, or operate as, the Oracle GoldenGate database user.

• Create a database user that is dedicated to Oracle GoldenGate. It can be the same user for all of the Oracle GoldenGate processes that must connect to a database:

  • Extract (source database)

  • Replicat (target database)

  • DEFGEN utility (source or target database)

• The Extract process requires permission to access the source database. Do one of the following:

  • Grant System Administrator privileges.

  • Assign a user name with replication_role . The command to grant replication role is either:

    sp_role 'grant', replication_role, Extract_user

    Or

    use dbname grant role replication_role to Extract_user
    

  Note:

  Specific DDL or DML operations may require the use of both sa_role and replication_role.

• The Replicat process requires connect and DML privileges on the target database.

Preparing Tables for Processing

The following table attributes must be addressed in an Oracle GoldenGate environment.

Topics:

• Disabling Triggers and Cascade Constraints
  
• Assigning Row Identifiers
  
• Limiting Row Changes in Tables that do not Have a Key
  
• Replicating Encrypted Data
  

Disabling Triggers and Cascade Constraints

Disable triggers, cascade delete constraints, and cascade update constraints on target Sybase tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger or cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ.

1. A delete is issued for emp_src.

2. It cascades a delete to salary_src.

3. Oracle GoldenGate sends both deletes to the target.

4. The parent delete arrives first and is applied to emp_targ.

5. The parent delete cascades a delete to salary_targ.

6. The cascaded delete from salary_src is applied to salary_targ.

7. The row cannot be located because it was already deleted in step 5.

To configure Replicat to disable target triggers at the start of its database session, take the following steps:

1. Assign the Replicat user the replication role.
2. Add the following parameter statement to the root level of the Replicat parameter file.

   SQLEXEC "set triggers off"
   

Assigning Row Identifiers

Oracle GoldenGate requires some form of unique row identifier on source and target tables to locate the correct target rows for replicated updates and deletes.

Limiting Row Changes in Tables that do not Have a Key

If a target table has no primary key or unique key, duplicate rows can exist. It is possible for Oracle GoldenGate to update or delete too many rows in the target table, causing the source and target data to go out of synchronization without error messages to alert you. To limit the number of rows that are updated, use the DBOPTIONS parameter with the LIMITROWS option in the Replicat parameter file. LIMITROWS can increase the performance of Oracle GoldenGate on the target system because only one row is processed.

Replicating Encrypted Data

Oracle GoldenGate supports columns that are encrypted with a system-encrypted password, but not columns that are encrypted with a user-defined password. Check the tables from which you want to capture data against the following Oracle GoldenGate limitations:

• The table that contains the encrypted columns must have a primary or unique key.

• Columns that use encryption cannot be part of the primary key.

Encrypted columns are encrypted in the data files and in the log, so Extract must be configured to fetch the clear-text values from the database. To trigger this fetch, use the FETCHCOLS and FETCHMODCOLS[EXCEPT] options of the Extract TABLE parameter. FETCHCOLS forces a fetch of values that are not in the log, and FETCHMODCOLS or FETCHMODCOLS[EXCEPT] forces a fetch of values that are in the logs. Used together, these parameters ensure that the encrypted columns are always fetched from the database.

The following is an example of how to configure Extract to support the encryption. In this example, the encrypted column is cardnum.

TABLE ab.payments, FETCHCOLS (cardnum), FETCHMODCOLS (cardnum);

Preparing the Transaction Logs

To capture DML operations, Oracle GoldenGate reads the online logs. To ensure the continuity and integrity of Oracle GoldenGate processing, the logs must be configured as directed in the following sections:

Topics:

• Initializing the Secondary Truncation Point
  
• Sizing and Retaining the Logs
  
• Enabling Transaction Logging
  

Initializing the Secondary Truncation Point

Establish a secondary log truncation point on the source system prior to running the Oracle GoldenGate Extract process. Extract uses the secondary truncation point to identify data that remains to be processed.

To initialize the secondary truncation point, log on to the database as a user with sa_role privileges and then issue the following command:

dbcc settrunc( 'ltm', valid )

By default, Extract will manage the secondary truncation point once it is established. Do not permit Extract to be stopped any longer than necessary; otherwise the log could eventually fill up and the database will halt. The only way to resolve this problem is to disable the secondary truncation point and manage it outside of Oracle GoldenGate, and then purge the transaction log. Data not yet processed by Extract will be lost, and you will have to resynchronize the source and target data.

To control how the secondary truncation point is managed, use the TRANLOGOPTIONS parameter. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

Sizing and Retaining the Logs

Retain enough log data on the source system so that Extract can start again from its checkpoints after you stop it or there is an unplanned outage. Extract must have access to the log that contains the start of the oldest uncommitted unit of work, and all logs thereafter. To determine where the Extract checkpoints are, use the INFO EXTRACT command. For more information about INFO EXTRACT, see Reference for Oracle GoldenGate for Windows and UNIX .

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

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

• You might need to resynchronize the source and target tables, and then start the Oracle GoldenGate environment over again.

Make certain not to use backup or archive options that cause old archive files to be overwritten by new backups on the source system. New backups should be separate files with different names from older ones. This ensures that if Extract looks for a particular log, it will still exist, and it also ensures that the data is available in case it is needed for a support case.

Enabling Transaction Logging

Use the ADD TRANDATA command to mark each source table for replication. This command uses the Sybase sp_setreptable and sp_setrepcol system procedures. ADD TRANDATA is the recommended way to mark the tables, instead of using those procedures through the database interface, but the owner or the system administrator can use them if needed. For more information, see the Sybase documentation.

To mark tables for replication with ADD TRANDATA:

1. On the source system, run GGSCI from the Oracle GoldenGate directory.
2. Log into the database from GGSCI.

   DBLOGIN SOURCEDB database USERID user PASSWORD xxx
   

   Where:

   • database is the name of the database.

   • user is the database owner or the system administrator. You will be prompted for the password. This command has encryption options for the password. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

   • xxx is the password for the associated user.

3. Issue the ADD TRANDATA command for each table to be marked.

   ADD TRANDATA SCHEMA.TABLE LOBSNEVER | LOBSALWAYS | LOBSALWAYSNOINDEX | LOBSIFCHANGED
   

   Where:

   • LOBSNEVER | LOBSALWAYS | LOBSALWAYSNOINDEX | LOBSIFCHANGED control whether LOB data is never propagated, only propagated if changed (the default), or always propagated. The ADD TRANDATA command will overwrite the LOB replication setting that is currently set for the table.

Note:

Some ADD TRANDATA options enable the ALWAYS_REPLICATE option of sp_setrepcol. If a LOB column contains a NULL value, and then another column in the table gets updated (but not the LOB), that LOB will not be captured even though ALWAYS_REPLICATE is enabled.