Documentation
Advanced Search


Administering Oracle GoldenGate for Windows and UNIX

14 Configuring Online Change Synchronization

This chapter describes how to configure online change synchronization.

This chapter includes the following sections:

14.1 Overview of Online Change Synchronization

Online change synchronization extracts and replicates data changes continuously to maintain a near real-time target database. The number of Extract and Replicat processes and trails that you will need depends on the replication topology that you want to deploy and the process mode that you will be using.

For detailed information about deploying specific replication topologies, see:

You may need to configure multiple Replicat processes if you are replicating between Oracle multitenant container databases or SQL/MX databases that contain multiple catalogs. One Replicat process can apply to one, and only one, container or catalog. In this case, you must configure a Replicat process for each target container or catalog. To do so, you specify the appropriate target container or catalog with the TARGETDB parameter in each Replicat parameter file. See Installing and Configuring Oracle GoldenGate for Oracle Database and Installing and Configuring Oracle GoldenGate for NonStop SQL/MX for guidelines when capturing from, and applying to, these databases.

You may need to configure multiple process groups to achieve a certain performance level. For example, you may want to keep lag below a certain threshold. Lag is the difference between when changes are made within your source applications and when those changes are applied to the target database.

Oracle GoldenGate supports up to 5,000 concurrent Extract and Replicat groups per instance of Oracle GoldenGate Manager. At the supported level, all groups can be controlled and viewed in full with GGSCI commands such as the INFO and STATUS commands. Oracle GoldenGate recommends keeping the number of Extract and Replicat groups (combined) at the default level of 300 or below in order to manage your environment effectively.

See Section 19, "Tuning the Performance of Oracle GoldenGate" for more information about configuring Oracle GoldenGate for best performance.

14.1.1 Initial Synchronization

After you configure your change-synchronization groups and trails following the directions in this chapter, see Chapter 16, "Instantiating Oracle GoldenGate with an Initial Load" to prepare the target tables for synchronization. An initial load takes a copy of entire source tables, transforms the data if necessary, and applies it to the target tables so that the movement of transaction data begins from a synchronized state. The first time that you start change synchronization should be during the initial synchronization process. Change synchronization keeps track of ongoing transactional changes while the load is being applied.

14.2 Choosing Names for Processes and Files

It is helpful to develop consistent naming conventions for the Oracle GoldenGate processes and files before you start configuration steps. Choosing meaningful names helps you differentiate among multiple processes and files in displays, error logs, and external monitoring programs. In addition, it accommodates the naming of additional processes and files later, as your environment changes or expands.This section contains instructions for:

14.2.1 Naming Conventions for Processes

When specifying a process or group name, follow these rules.

  • For the following types of processes, you can use up to eight characters, including non-alphanumeric characters such as the underscore (_):

    • Online Extract group

    • Initial-load Extract

    • Online Replicat group created in classic (non-coordinated) mode

    • Online Replicat group created in integrated mode (Oracle only)

  • For a coordinated Replicat process group, you can use up to five characters, including non-alphanumeric characters such as the underscore (_). Internally, a three-character thread ID is appended to the base name for each thread that is created based on the MAXTHREADS option of the ADD REPLICAT command. The resulting names cannot be duplicated for any other Replicat group. For example, if a coordinated Replicat group named fin is created with a MAXTHREADS of 50 threads, the resulting thread names could span from fin000 through fin050, assuming those are the IDs specified in the MAP statements. Thus, no other Replicat group can be named fin000 through fin0050. See the following rule for more information.

  • You can include a number in a group name, but it is not recommended that a name end in any numerals. Understand that using a numeric value at the end of a group name (such as fin1) can cause duplicate report file names and errors, because the writing process appends a number to the end of the group name when generating a report. In addition, ending a group name with numeric values is not recommended when running Replicat in coordinated mode. Because numeric thread IDs are appended to a group name internally, if the base group name also ends in a number it can make the output of informational commands more complicated to analyze. Thread names could be confused with the names of other Replicat groups if the numeric appendages satisfy wildcards. Duplicate report file names also can occur. It may be more practical to put a numeric value at the beginning of a group name, such as 1_fin, 1fin, and so forth.

  • Any character can be used in the name of a process, so long as the character set of the local operating system supports it, and the operating system allows that character to be in a file name. This is because a group is identified by its associated checkpoint file and parameter file.

  • The following characters are not allowed in the name of a process:

    {\ / : * ? " < > | }
    
  • On HP UX, Linux, and Solaris, it is possible to create a file name with a colon (:) or an asterisk (*), although it is not recommended.

  • In general, process names and parameter file names are not case-sensitive within Oracle GoldenGate. For example, finance, Finance, and FINANCE are all considered to be the same. However, on Linux, the process name (and its parameter file name if explicitly defined in the ADD command) must be all uppercase or all lowercase. Mixed-case names specified for processes and parameter files will result in errors when starting the process.

  • Use only one word for a name.

  • Do not use the word "port" as the full name for a process or parameter file. However, the string "port" can be part of a name.

14.2.2 Choosing File Names

Captured data must be processed into a series of files called a trail, where it is stored for processing by the next Oracle GoldenGate process downstream. The basic configuration is:

  • A local trail on the source system

  • A remote trail on the target system

The actual trail name contains only two characters, such as ./dirdat/tr. Oracle GoldenGate appends this name with a six-digit sequence number whenever a new file is created, such as ./dirdat/aa000002. It is recommended that you establish naming conventions for trails, because they are linked to Oracle GoldenGate processes and may need to be identified for the purposes of troubleshooting.

On Windows systems, if the name of any directory in the trail path name begins with a number, the path must be specified with forward slashes, not backward slashes, when listing the trail in a parameter file. For more information, see Section 4.5.1, "Specifying Filesystem Path Names in Parameter Files on Windows Systems."

See Section 1.2.4, "Overview of Trails" for more information about Oracle GoldenGate trails.

14.3 Creating a Checkpoint Table

Replicat maintains checkpoints that provide a known position in the trail from which to start after an expected or unexpected shutdown. To store a record of its checkpoints, Replicat uses a checkpoint table in the target database. This enables the Replicat checkpoint to be included within the Replicat transaction itself, to ensure that a transaction will only be applied once, even if there is a failure of the Replicat process or the database process. The checkpoint table remains small because rows are deleted when no longer needed, and it does not affect database performance. Appendix E, "About Checkpoints" for more information about the checkpoint table.

14.3.1 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 table(s) 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. See "To Specify a Default Checkpoint Table in the GLOBALS File" for 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 Windows and UNIX 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 | catalog] 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, and catalog is the name of the catalog if installing into a SQL/MX 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. | catalog.]owner.table
    

    Where:

    [catalog. | container.]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) or the name of the catalog if the database is SQL/MX.

  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.

14.3.2 Adjusting for Coordinated Replicat in Oracle RAC

If the Replicat for which you are creating a checkpoint table will run in an Oracle RAC configuration, it is recommended that you increase the PCTFREE attribute of the Replicat checkpoint table to as high a value as possible, as high as 90 if possible. This accommodates the more frequent checkpointing that is inherent in coordinated processing. This change must be made before starting the Replicat group for the first time. See Section 14.7 for more information about coordinated Replicat.

14.4 Creating an Online Extract Group

To create an online Extract group, run GGSCI on the source system and issue the ADD EXTRACT command. Separate all command arguments with a comma. There are two syntax forms:

Syntax to Create a Regular, Passive, or Data Pump Extract Group

ADD EXTRACT group
{, datasource}
{, BEGIN start_point} | {position_point}
[, PASSIVE]
[, THREADS n]
[, PARAMS pathname]
[, REPORT pathname]
[, DESC 'description']

Where:

  • group is the name of the Extract group. A group name is required. See Section 14.2.1, "Naming Conventions for Processes" for group naming conventions.

  • datasource is required to specify the source of the data to be extracted. Use one of the following:

    • TRANLOG specifies the transaction log as the data source. Use for all databases except Teradata. When using this option for Oracle Enterprise Edition, you must issue the DBLOGIN command as the Extract database user (or a user with the same privileges) before using ADD EXTRACT (and also before issuing DELETE EXTRACT to remove an Extract group).

      Use the bsds option for DB2 running on z/OS to specify the Bootstrap Data Set file name of the transaction log. See Reference for Oracle GoldenGate for Windows and UNIX for the correct syntax to use for your database.

    • INTEGRATED TRANLOG specifies that this Extract will operate in integrated capture mode to receive logical change records (LCR) from an Oracle database logmining server. This parameter applies only to Oracle databases. For additional options and information on using integrated capture, see Installing and Configuring Oracle GoldenGate for Oracle Database.

    • VAM specifies that the Extract API known as the Vendor Access Module (VAM) will interface with the Teradata Access Module (TAM). Use for Teradata databases. See Installing and Configuring Oracle GoldenGate for Teradata for more information.

    • VAMTRAILSOURCE VAM trail name to specify a VAM trail. Use for Teradata extraction in maximum protection mode to create a VAM-sort Extract group. See Installing and Configuring Oracle GoldenGate for Teradata for more information.

    • EXTTRAILSOURCE trail name to specify the relative or fully qualified name of a local trail. Use to create a data pump. A data pump can be used with any Oracle GoldenGate extraction method.

  • BEGIN start_point defines an online Extract group by establishing an initial checkpoint and start point for processing. Transactions started before this point are discarded. Use one of the following:

    • NOW to begin extracting changes that are timestamped at the point when the ADD EXTRACT command is executed to create the group or, for an Oracle Extract in integrated mode, from the time the group is registered with the REGISTER EXTRACT command. Do not use NOW for a data pump Extract unless you want to bypass any data that was captured to the Oracle GoldenGate trail prior to the ADD EXTRACT statement.

    • YYYY-MM-DD HH:MM[:SS[.CCCCCC ]] as the format for specifying an exact timestamp as the begin point. Use a begin point that is later than the time at which replication or logging was enabled.

      Note:

      Do not use the BEGIN parameter for a Teradata source.

  • position_point specifies a specific position within a specific transaction log file at which to start processing. For the specific syntax to use for your database, see ADD EXTRACT in Reference for Oracle GoldenGate for Windows and UNIX.

  • PASSIVE indicates that the group is a passive Extract. When using PASSIVE, you must also use an alias Extract. See Section 11, "Configuring Oracle GoldenGate Security" for more information. This option can appear in any order among other ADD EXTRACT options.

  • THREADS n is required only if Extract is operating in classic capture mode in an Oracle Real Application Cluster (RAC). It specifies the number of redo log threads being used by the cluster.

  • PARAMS pathname is required if the parameter file for this group will be stored in a location other than the dirprm sub-directory of the Oracle GoldenGate directory. Specify the fully qualified name. The default location is recommended.

  • REPORT pathname is required if the process report for this group will be stored in a location other than the dirrpt sub-directory of the Oracle GoldenGate directory. Specify the fully qualified name. The default location is recommended.

  • DESC 'description' specifies a description of the group.

Syntax to Create an Alias Extract Group

ADD EXTRACT group
, RMTHOST {host | IP address}
, {MGRPORT port} | {PORT port}
[, RMTNAME name]
[, DESC 'description']

Where:

  • RMTHOST identifies this group as an alias Extract and specifies either the DNS name of the remote host or its IP address.

  • MGRPORT specifies the port on the remote system where Manager is running. Use this option when using a dynamic Collector.

  • PORT specifies a static Collector port. Use instead of MGRPORT only if running a static Collector.

  • RMTNAME specifies the passive Extract name, if different from that of the alias Extract.

  • DESC 'description' specifies a description of the group.

Example 14-1 Adding an Extract Group for Log-based Capture

This example creates an Extract group named finance. Extraction starts with records generated at the time when the group was created.

ADD EXTRACT finance, TRANLOG, BEGIN NOW

Example 14-2 Adding a Primary Extract Group for Teradata

This example creates an Extract group named finance that performs in either Teradata maximum performance or Teradata maximum protection mode. No BEGIN point is used for Teradata sources.

ADD EXTRACT finance, VAM

Example 14-3 Adding a VAM-sort Extract Group for Teradata

This example creates a VAM-sort Extract group named finance. The process reads from VAM trail /ggs/dirdat/vt.

ADD EXTRACT finance, VAMTRAILSOURCE /ggs/dirdat/vt

Example 14-4 Adding a Data-pump Extract Group

This example creates a data-pump Extract group named finance. It reads from the Oracle GoldenGate trail c:\ggs\dirdat\lt.

ADD EXTRACT finance, EXTTRAILSOURCE c:\ggs\dirdat\lt

Example 14-5 Adding a Passive Extract Group

This example creates a passive Extract group named finance. Extraction starts with records generated at the time when the group was created. Because this group is marked as passive, an alias Extract on the target will initiate connections to this Extract.

ADD EXTRACT finance, TRANLOG, BEGIN NOW, PASSIVE

Example 14-6 Adding a Passive Data-pump Extract Group

This example creates a data-pump Extract group named finance. This is a passive data pump Extract that reads from the Oracle GoldenGate trail c:\ggs\dirdat\lt. Because this data pump is marked as passive, an alias Extract on the target will initiate connections to it.

ADD EXTRACT finance, EXTTRAILSOURCE c:\ggs\dirdat\lt, PASSIVE

Example 14-7 Adding an Alias Extract Group

This example creates an alias Extract group named alias.

ADD EXTRACT alias, RMTHOST sysA, MGRPORT 7800, RMTNAME finance

Example 14-8 Adding a Primary Extract in Integrated Mode for Oracle

This example creates an Extract in integrated capture mode for an Oracle source database and sets the start point to the time when the Extract group is registered with the Oracle database by means of the REGISTER EXTRACT command. Integrated capture is available only for an Oracle database.

ADD EXTRACT finance INTEGRATED TRANLOG, BEGIN NOW 

14.5 Creating a Trail

After data has been extracted, it must be processed into one or more trails, where it is stored for processing by another Oracle GoldenGate process. A trail is a sequence of files that are created and aged as needed. Processes that read a trail are:

  • VAM-sort Extract: Extracts from a local trail that is created as a VAM trail (for Teradata source databases). For more information, see Installing and Configuring Oracle GoldenGate for Teradata

  • Data-pump Extract: Extracts data from a local trail for further processing, if needed, and transfers it to the target system.

  • Replicat: Reads a trail to apply change data to the target database.

You can create more than one trail to separate the data of different tables or applications, or to satisfy the requirements of a specific replication topology, such as a cascading topology. You link tables specified with a TABLE statement to a trail specified with an EXTTRAIL or RMTTRAIL parameter statement in the Extract parameter file. See Section 1.2.4, "Overview of Trails" for conceptual information about Oracle GoldenGate trails.

14.5.1 Assigning Storage for Oracle GoldenGate Trails

In a typical configuration, there is at least one trail on the source system and one on the target system. Allocate enough disk space to allow for the following:

  • The primary Extract process captures transactional data from the source database and writes it to the local trail. A data-pump Extract reads that trail and then transfers the data over the network to a remote trail on the target. If the network fails, the data pump fails but the primary Extract continues to process data to the local trail. There must be enough disk space to contain the data accumulation, or the primary Extract will abend.

  • For a trail at the target location, provide enough disk space to handle data accumulation according to the purge rules set with the PURGEOLDEXTRACTS parameter. Even with PURGEOLDEXTRACTS in use, data will always accumulate on the target because it is transferred across the network faster than it can be applied to the target database.

To prevent trail activity from interfering with business applications, assign a separate disk or file system to contain the trail files. Trail files can reside on drives that are local to the Oracle GoldenGate installation, or they can reside on NAS or SAN devices. In an Oracle cluster, they can reside on ASM or DBFS storage.

14.5.2 Estimating Space for the Trails

The following are guidelines for estimating the amount of disk space that will be required to store Oracle GoldenGate trail data.

  1. Estimate the longest time that the network could be unavailable. Plan to store enough data to withstand the longest possible outage, because otherwise you will need to resynchronize the source and target data if the outage outlasts disk capacity.

  2. Estimate how much transaction log volume your business applications generate in one hour.

  3. Use the following formula to calculate the required disk space.

    [log volume in one hour] x [number of hours downtime] x .4 = trail disk space

    This equation uses a multiplier of 40 percent because only about 40 percent of the data in a transaction log is needed by Oracle GoldenGate.

    Note:

    This formula is a conservative estimate, and you should run tests once you have configured Oracle GoldenGate to determine exactly how much space you need.

14.5.3 Adding a Trail

When you create, or add, a trail, you do not physically create any files on disk. The files are created automatically by an Extract process. Rather, you specify the name of the trail and associate it with the Extract group that writes to it.

To add a trail, issue the following command in GGSCI on the source system.

ADD {RMTTRAIL | EXTTRAIL} pathname, EXTRACT group
[, MEGABYTES n]

Where:

  • RMTTRAIL specifies a trail on a remote system.

  • EXTTRAIL specifies a trail on the local system.

    • EXTTRAIL cannot be used for an Extract in PASSIVE mode.

    • EXTTRAIL must be used to specify a local trail that is read by a data pump or to specify a VAM trail that is linked to a primary Extract which interacts with a Teradata Access Module (TAM). For more information about the Teradata configuration, see Installing and Configuring Oracle GoldenGate for Teradata

  • pathname is the relative or fully qualified name of the trail, including a two-character name that can be any two alphanumeric characters, for example c:\ggs\dirdat\rt. Oracle GoldenGate appends a serial number to each trail file as it is created during processing. Typically, trails are stored in the dirdat sub-directory of the Oracle GoldenGate directory.

  • EXTRACT group specifies the name of the Extract group that writes to this trail. Only one Extract group can write to a trail.

  • MEGABYTES n is an optional argument with which you can set the size, in megabytes, of each trail file (default is 100).

Example 14-9 VAM Trail

This example creates a VAM trail named /ggs/dirdat/vt for Extract group extvam.

ADD EXTTRAIL /ggs/dirdat/vt, EXTRACT extvam

Example 14-10 Local Trail

This example creates a local trail named /ggs/dirdat/lt for Extract group ext.

ADD EXTTRAIL /ggs/dirdat/lt, EXTRACT ext

Example 14-11 Creating a Trail

This example creates a trail named c:\ggs\dirdat\rt for Extract group finance, with each file sized at approximately 50 megabytes.

ADD RMTTRAIL c:\ggs\dirdat\rt, EXTRACT finance, MEGABYTES 200

14.6 Creating a Parameter File for Online Extraction

Follow these instructions to create a parameter file for an online Extract group. A parameter file is not required for an alias Extract group. See Chapter 11, "Configuring Oracle GoldenGate Security" for more information about using an alias Extract group.

  1. In GGSCI on the source system, issue the following command.

    EDIT PARAMS name
    

    Where:

    name is either the name of the Extract group that you created with the ADD EXTRACT command or the fully qualified name of the parameter file if you defined an alternate location when you created the group.

  2. Enter the parameters in Table 14-1 in the order shown, starting a new line for each parameter statement. Some parameters apply only for certain configurations.

    Table 14-1 Online Change-extraction Parameters

    Parameter Description
    EXTRACT group
    
    • group is the name of the Extract group that you created with the ADD EXTRACT command.

    Configures Extract as an online process with checkpoints.

    [SOURCEDB dsn | container | catalog]
    [, USERIDALIAS alias options |
    , USERID user, options]
    

    Specifies database connection information.

    SOURCEDB specifies the source datasource name (DSN) or SQL/MX catalog. See Reference for Oracle GoldenGate for Windows and UNIX for more information.

    USERID and USERIDALIAS specify database credentials if required. See Chapter 11, "Configuring Oracle GoldenGate Security" for more information about security options.

    The database connection can be omitted if the group is a data pump on an intermediary system that does not have a database. In this case, there can be no column mapping or conversion performed.

    RMTHOST host,
    MGRPORT port,
    [, ENCRYPT algorithm KEYNAME key_name]
    

    Specifies the target system, the port where Manager is running, and optional encryption of data across TCP/IP. Only required when sending data over IP to a remote system (if ADD RMTTRAIL was used to create the trail). Not required if the trail is on the local system (if ADD EXTTRAIL was used). See Chapter 11, "Configuring Oracle GoldenGate Security" for more information about security options.

    Not valid for a primary Extract group that interfaces with a Teradata Access Module and writes to a VAM trail. For more information, see Installing and Configuring Oracle GoldenGate for Teradata.

    Not valid for a passive Extract group.

    ENCRYPTTRAIL algorithm
    

    Encrypts all trails that are specified after this entry. See Chapter 11, "Configuring Oracle GoldenGate Security" for more information about security options.

    DECRYPTTRAIL

    (For a data pump) Decrypts the data in the input trail. Use only if the data pump must process the data before writing it to the output trail. See Chapter 11, "Configuring Oracle GoldenGate Security" for more information about security options.

    RMTTRAIL pathname |
    EXTTRAIL pathname
    
    • Use RMTTRAIL to specify the relative or fully qualified name of a remote trail created with the ADD RMTTRAIL command.

    • Use EXTTRAIL to specify the relative or fully qualified name of a local trail created with the ADD EXTTRAIL command (to be read by a data pump or VAM-sort Extract).

    Specifies a trail. If specifying multiple trails, follow each designation with the appropriate TABLE statements.

    EXTTRAIL is not valid for a passive Extract group.

    If trails or files will be of different versions, use the FORMAT option of RMTTRAIL or EXTTRAIL. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

    DSOPTIONS {
    COMMITTEDTRANLOG, RESTARTAPPEND |
    CREATETRANLOG |
    SORTTRANLOG
    }
    

    Valid only for Teradata extraction.

    • Use COMMITTEDTRANLOG, RESTART APPEND to indicate that Extract is receiving fully committed data in Teradata maximum performance mode. RESTARTAPPEND appends data to the end of the Oracle GoldenGate trail rather than rewriting data from a previous run.

    • Use CREATETRANLOG to direct Extract to create and write to a local VAM trail in Teradata maximum protection mode. Use for a primary Extract group that interfaces with the Teradata Access Module.

    • Use SORTTRANLOG to cause Extract to read from a local VAM trail and sort the data in commit order in maximum protection mode. Use only for a VAM-sort Extract group.

    For more information about the Teradata configuration, see Installing and Configuring Oracle GoldenGate for Teradata.

    LOGALLSUPCOLS

    Use when using integrated Replicat for an Oracle target, or when using Conflict Detection and Resolution (CDR) support. Writes the before images of scheduling columns to the trail. (Scheduling columns are primary key, unique index, and foreign key columns.) See Installing and Configuring Oracle GoldenGate for Oracle Database for more information on configuring integrated Replicat. See Reference for Oracle GoldenGate for Windows and UNIX for more information about LOGALLSUPCOLS.

    VAM library,
    PARAMS ('param'
    [, 'param'] [, ...])
    

    Valid only for an Extract group that interfaces with a Teradata Access Module. Supplies the name of the library and parameters that must be passed to the Oracle GoldenGate API, such as the name of the TAM initialization file and the program that interacts with the library as the callback library.

    Example:

    VAM vam.dll, PARAMS ('inifile', 'vamerge1.ini', 'callbacklib', 'extract.exe')
    

    See Installing and Configuring Oracle GoldenGate for Teradata for more information about Teradata VAM and TAM.

    PASSTHRU | NOPASSTHU
    

    (For a data pump) Specifies whether or not subsequent TABLE specifications will use normal or pass-through processing. If a data pump is only being used to pass data from the local system to a remote trail, without performing filtering, mapping, or conversion, you can run it in pass-through mode. In this mode, the Extract process bypasses its normal queries for table definitions from the database or a definitions file and can be used on a system with or without a database.

    SOURCECATALOG

    Specifies a default container in an Oracle multitenant container database or a default SQL/MX catalog for subsequent TABLE or SEQUENCE statements. Enables the use of two-part names (schema.object) where three-part names otherwise would be required for those databases. You can use multiple instances of this parameter to specify different default containers or catalogs for different sets of TABLE or SEQUENCE parameters.

    SEQUENCE [container.]owner.sequence;
    

    Specifies the fully qualified name of an Oracle sequence to capture. Include the container name if the database is a multitenant container database (CDB).

    TABLE [container. | catalog.]owner.object;
    

    Specifies the fully qualified name of an object or a fully qualified wildcarded specification for multiple objects. If the database is an Oracle multitenant container database or SQL/MX, the object name must include the name of the container or catalog unless SOURCECATALOG is used. See Section 4.5, "Specifying Object Names in Oracle GoldenGate Input" for guidelines for specifying object names in parameter files.

    CATALOGEXCLUDE

    SCHEMAEXCLUDE

    TABLEEXCLUDE

    EXCLUDEWILDCARDOBJECTSONLY

    Parameters that can be used in conjunction with one another to exclude specific objects from a wildcard specification in the associated TABLE statement. See Reference for Oracle GoldenGate for Windows and UNIX for details.


  3. Enter any appropriate optional Extract parameters listed in the Reference for Oracle GoldenGate for Windows and UNIX.

  4. Save and close the parameter file.

14.7 Creating an Online Replicat Group

Before creating a Replicat group, you should evaluate which of the Replicat modes is appropriate for your environment: classic mode (also known as nonintegrated mode in Oracle environments), coordinated mode, and integrated mode.

Section 14.7.1, "About Classic Replicat Mode"

Section 14.7.2, "About Coordinated Replicat Mode"

Section 14.7.3, "About Integrated Replicat Mode"

14.7.1 About Classic Replicat Mode

In classic mode, Replicat is a single-threaded process that uses standard SQL to apply data to the target tables. In this mode, Replicat operates as follows:

  • Reads the Oracle GoldenGate trail.

  • Performs data filtering, mapping, and conversion.

  • Constructs SQL statements that represent source database DML or DDL transactions (in committed order).

  • Applies the SQL to the target through the SQL interface that is supported for the given target database, such as ODBC or the native database interface.

Figure 14-1 Classic Replicat

Description of Figure 14-1 follows
Description of "Figure 14-1 Classic Replicat"

As shown in Figure 14-1, you can apply transactions in parallel with a classic Replicat, but only by partitioning the workload across multiple Replicat processes. A parameter file must be created for each Replicat.

To determine whether to use classic mode for any objects, you must determine whether the objects in one Replicat group will ever have dependencies on objects in any other Replicat group, transactional or otherwise. Not all workloads can be partitioned across multiple Replicat groups and still preserve the original transaction atomicity. For example, tables for which the workload routinely updates the primary key cannot easily be partitioned in this manner. DDL replication (if supported for the database) is not viable in this mode, nor is the use of some SQLEXEC or EVENTACTIONS features that base their actions on a specific record.

If your tables do not have any foreign- key dependencies or updates to primary keys, classic mode may be suitable. Classic mode requires less overhead than coordinated mode.

For more information about using parallel Replicat groups, see Chapter 19, "Tuning the Performance of Oracle GoldenGate".

14.7.2 About Coordinated Replicat Mode

In coordinated mode, Replicat operates as follows:

  • Reads the Oracle GoldenGate trail.

  • Performs data filtering, mapping, and conversion.

  • Constructs SQL statements that represent source database DML or DDL transactions (in committed order).

  • Applies the SQL to the target through the SQL interface that is supported for the given target database, such as ODBC or the native database interface.

The difference between classic mode and coordinated mode is that Replicat is multi-threaded in coordinated mode. Within a single Replicat instance, multiple threads read the trail independently and apply transactions in parallel. Each thread handles all of the filtering, mapping, conversion, SQL construction, and error handling for its assigned workload. A coordinator thread coordinates the transactions across threads to account for dependencies among the threads.

Coordinated Replicat allows for user-defined partitioning of the workload so as to apply high volume transactions concurrently. In addition, it automatically coordinates the execution of transactions that require coordination, such as DDL, and primary key updates with THREADRANGE partitioning. Such a transaction is executed as one transaction in the target with full synchronization: it waits until all prior transactions are applied first, and all transactions after this barrier transaction have to wait until this barrier transaction is applied.

Only one parameter file is required for a coordinated Replicat, regardless of the number of threads. You use the THREAD or THREADRANGE option in the MAP statement to specify which threads process the transactions for those objects, and you specify the maximum number of threads when you create the Replicat group.

Figure 14-2 Coordinated Replicat

Description of Figure 14-2 follows
Description of "Figure 14-2 Coordinated Replicat"

14.7.2.1 About Barrier Transactions

Barrier transactions are managed automatically in a coordinated Replicat configuration. Barrier transactions are transactions that require coordination across threads. Examples include DDL statements, transactions that include updates to primary keys, and certain EVENTACTIONS actions.

Optionally, you can force other transactions to be treated like a barrier transaction through the use of the COORDINATED keyword in a MAP statement. One use case for this would be force a SQLEXEC to be executed in a manner similar to a serial execution. This could be beneficial if the results can become ambiguous unless the state of the target is consistent across all transactions.

14.7.2.2 How Barrier Transactions are Processed

All threads converge and wait at the start of a barrier transaction. The barrier transaction is suspended until the other threads reach its start position. If any threads were already processing part of the barrier transaction, those threads perform a rollback. Grouped transactions, such as those controlled by the BATCHSQL or GROUPTRANSOPS parameters, are also rolled back and then reapplied until they reach the start of the barrier transaction.

All of the threads converge and wait at the start of the next transaction after the barrier transaction as well. The two synchronization points, before and after the barrier transaction, ensure that metadata operations and EVENTACTIONS actions all occur in the proper order relevant to the data operations.

Once the threads are synchronized at the start of the barrier transaction, the barrier transaction is processed serially by the thread that has the lowest thread ID among all of the threads specified in the MAP statements, and then parallel processing across threads is resumed. You can force barrier transactions to be processed through a specific thread, which is always thread 0, by specifying the USEDEDICATEDCOORDINATIONTHREAD parameter in the Replicat parameter file.

14.7.2.3 About the Global Watermark

A clean shutdown of a coordinated Replicat ensures that all threads stop at the same transaction boundary in the trail, known as the global watermark. This is defined as the synchronized point where all records before this position were either committed or ignored by all of their respective threads. If a clean shutdown is not possible, you can use the SYNCHRONIZE REPLICAT command to return all of the threads to the position of the thread that made the most recent checkpoint. See Section 20.12.3, "Synchronizing threads after an Unclean Stop" for more information about recovering a coordinated Replicat group.

Note:

Coordinated Replicat is an online process only. Do not use it to perform initial loads.

14.7.3 About Integrated Replicat Mode

In integrated mode, available for Oracle databases of version 11.2.0.4 or later, Replicat leverages the apply processing functionality that is available within the target Oracle database. In this mode, Replicat reads the trail, constructs logical change records that represent source DML or DDL transactions, and transmits these records to an inbound server in the Oracle target database. The inbound server applies the data to the target database.

For more information about using integrated Replicat, see Installing and Configuring Oracle GoldenGate for Oracle Database.

Note:

Integrated Replicat is an online process only. Do not use it to perform initial loads.

14.7.4 Understanding Replicat Processing in Relation to Parameter Changes

Changes to the object specifications in the Replicat configuration cannot be made to affect transactions that are already applied, but only for those not yet applied. This is an important consideration when using coordinated or integrated Replicat.

For a Replicat in classic mode, the boundary between applied and non-applied transactions is a clean one, because transactions are applied serially. For a coordinated or integrated Replicat, however, there is no single point in the trail that marks applied and unapplied transactions, because transactions are being applied asynchronously in parallel.

In coordinated or integrated modes, there are a low watermark, below which all transactions were applied, and a high watermark above which no transactions were applied. In between those boundaries there may be transactions that may or may not have been applied, depending on the progress of individual threads. As a result, if Replicat is forced changes to object specifications in the Replicat configuration may be reflected unevenly in the target after Replicat is restarted. Examples of parameter changes for which this applies are changes to MAP mappings, FILTER clauses, and EXCLUDE parameters.

Changes to the Replicat configuration should not be made after Replicat abends or is forcibly terminated. Replicat should be allowed to recover to its last checkpoint after startup. For coordinated Replicat, you can follow the administrative procedures in Section 20.12, "Administering a Coordinated Replicat Configuration.". Once the recovery is complete, Replicat can be shut down gracefully with the STOP REPLICAT command, and then you can make the changes to the object specifications.

14.7.5 Creating the Replicat Group

To create an online Replicat group, run GGSCI on the target system and issue the ADD REPLICAT command. Separate all command arguments with a comma.

ADD REPLICAT group, EXTTRAIL path
[, {INTEGRATED | COORDINATED [MAXTHREADS number]}]
[, BEGIN start_point | , EXTSEQNO seqno, EXTRBA rba]
[, CHECKPOINTTABLE owner.table]
[, NODBCHECKPOINT]
[, PARAMS path]
[, REPORT path]

Where:

  • group is the name of the Replicat group. A group name is required. See Section 14.2.1, "Naming Conventions for Processes" for Oracle GoldenGate naming conventions.

  • EXTTRAIL path is the relative or fully qualified name of the trail that you defined with the ADD RMTTRAIL command.

  • INTEGRATED specified that this Replicat group will operate in integrated mode. This mode is available for Oracle databases. For more information about using Replicat in integrated mode, see Installing and Configuring Oracle GoldenGate for Oracle Database.

  • COORDINATED specifies that this Replicat group will operate in coordinated mode. MAXTHREADS specifies the maximum number of threads allowed for this group. Valid values are from 1 through 500. MAXTHREADS is optional. The default number of threads without MAXTHREADS is 25.

    Note:

    Each Replicat thread is considered a Replicat group in the context of the MAXGROUPS parameter. MAXGROUPS controls the maximum number of process groups allowed in the Oracle GoldenGate instance. MAXTHREADS plus the number of other process groups in the Oracle GoldenGate instance must not exceed the value set with MAXGROUPS (default is 300).

  • BEGIN start_point defines an online Replicat group by establishing an initial checkpoint and start point for processing. Use one of the following:

    • NOW to begin replicating changes timestamped at the point when the ADD REPLICAT command is executed to create the group.

    • YYYY-MM-DD HH:MM[:SS[.CCCCCC]] as the format for specifying an exact timestamp as the begin point.

  • EXTSEQNO seqno, EXTRBA rba specifies the sequence number of the file in a trail in which to begin reading data and the relative byte address within that file. By default, processing begins at the beginning of a trail unless this option is used. For the sequence number, specify the number, but not any zeroes used for padding. For example, if the trail file is c:\ggs\dirdat\aa000026, specify EXTSEQNO 26. Contact Oracle Support before using this option.

  • CHECKPOINTTABLE owner.table specifies the owner and name of a checkpoint table other than the default specified in the GLOBALS file. To use this argument, you must add the checkpoint table to the database with the ADD CHECKPOINTTABLE command (see Section 14.3, "Creating a Checkpoint Table").

  • NODBCHECKPOINT specifies that this Replicat group will not use a checkpoint table.

  • PARAMS path is required if the parameter file for this group will be stored in a location other than the dirprm sub-directory of the Oracle GoldenGate directory. Specify the fully qualified name. The default location is recommended.

  • REPORT path is required if the process report for this group will be stored in a location other than the dirrpt sub-directory of the Oracle GoldenGate directory. Specify the fully qualified name. The default location is recommended.

Example 14-12 Creating an Online Replicat Group

This example creates an online Replicat group named finance and specifies a trail of c:\ggs\dirdat\rt. The parameter file is stored in the alternate location of \ggs\params, and the report file is stored in its default location.

ADD REPLICAT finance, EXTTRAIL c:\ggs\dirdat\rt, PARAMS \ggs\params

14.8 Creating a Parameter File for Online Replication

Follow these instructions to create a parameter file for an online Replicat group.

  1. In GGSCI on the target system, issue the following command.

    EDIT PARAMS name
    

    Where:

    name is either the name of the Replicat group that you created with the ADD REPLICAT command or the fully qualified name of the parameter file if you defined an alternate location when you created the group.

  2. Enter the parameters listed in Table 14-2 in the order shown, starting a new line for each parameter statement.

Table 14-2 Online Change-replication Parameters

Parameter Description
REPLICAT group
  • group is the name of the Replicat group that you created with the ADD REPLICAT command.

Configures Replicat as an online process with checkpoints.

{SOURCEDEFS path} |
ASSUMETARGETDEFS
  • Use SOURCEDEFS if the source and target tables have different definitions. Specify the source data-definitions file generated by DEFGEN. See Chapter 13, "Associating Replicated Data with Metadata," for more information.

  • Use ASSUMETARGETDEFS if the source and target tables have the same definitions.

Specifies how to interpret data definitions.

For Oracle databases that use multi-byte character sets, you must use SOURCEDEFS (with a DEFGEN-generated definitions file) if the source semantics setting is in bytes and the target is in characters. This is required even when the source and target data definitions are identical. See Chapter 13, "Associating Replicated Data with Metadata," for more information.

[DEFERAPPLYINTERVAL n unit]
  • n is a numeric value for the amount of time to delay before applying transactions. Minimum is set by the EOFDELAY parameter. Maximum is seven days.

  • unit can be:

    S | SEC | SECS | SECOND | SECONDS | MIN | MINS | MINUTE | MINUTES | HOUR | HOURS | DAY | DAYS

Optional. Specifies an amount of time for Replicat to wait before applying its transactions to the target system.

[TARGETDB dsn | container | catalog]
[, USERIDALIAS alias options |
, USERID user, options]

Specifies database connection information.

TARGETDB specifies the target datasource name (DSN) or SQL/MX catalog. See Reference for Oracle GoldenGate for Windows and UNIX for more information.

USERID and USERIDALIAS specify database credentials if required. See Chapter 11, "Configuring Oracle GoldenGate Security" for more information about security options.

HANDLECOLLISIONS

Specifies collision handling. Use only if you are performing an initial load concurrently with starting online processing and the source database will remain active during the load. HANDLECOLLISIONS resolves the results of the copy with the ongoing replicated transactional changes. It resolves insert operations for which the row already exists and update and delete operations for which the row does not exist. It can be used globally for all MAP statements in a parameter file or within a MAP statement, or both.

SOURCECATALOG

Specifies a default container in a source Oracle multitenant container database or a source SQL/MX catalog for subsequent MAP statements. Enables the use of two-part names (schema.object) where three-part names otherwise would be required for those databases. You can use multiple instances of this parameter to specify different default containers or catalogs for different sets of MAP parameters.

MAP [container. | catalog.]owner.object,
TARGET owner.object[, DEF template]
[THREAD (thread_ID)]
[THREADRANGE (thread_range[, column_list])]
[COORDINATED]
;

Specifies a relationship between a source object or objects and a target object or objects. MAP specifies the source object, and TARGET specifies the target object.

For the source object, specify the fully qualified name of the object or a fully qualified wildcarded specification for multiple objects. For an Oracle multitenant container database or a SQL/MX database, the source object name must include the name of the container or catalog unless SOURCECATALOG is used.

For the target object, specify only the owner.object components of the name, regardless of the type of database. Replicat can only connect to one Oracle container or SQL/MX catalog. Use a separate Replicat process for each container or catalog to which you want to apply data.

See Section 4.5, "Specifying Object Names in Oracle GoldenGate Input" for guidelines for specifying object names in parameter files.

The THREAD, THREADRANGE, and COORDINATED options are valid for Replicat when in coordinated mode. They enable you to partition the workload to one or more specific Replicat threads. See Reference for Oracle GoldenGate for Windows and UNIX for syntax and usage.

The DEF option specifies a definitions template. See Section 13, "Associating Replicated Data with Metadata" for more information about data definitions. See Reference for Oracle GoldenGate for Windows and UNIX for more information and options for the MAP parameter.

CATALOGEXCLUDE

SCHEMAEXCLUDE

MAPEXCLUDE

EXCLUDEWILDCARDOBJECTSONLY

Parameters that can be used in conjunction with one another to exclude specific source objects from a wildcard specification in the associated MAP statement. See Reference for Oracle GoldenGate for Windows and UNIX for details.


  1. Enter any appropriate optional Replicat parameters listed in Reference for Oracle GoldenGate for Windows and UNIX .

  2. Save and close the file.

Note:

If using integrated Replicat for Oracle, see Section 14.7.4, "Understanding Replicat Processing in Relation to Parameter Changes" for important information about making configuration changes to Replicat once processing is started.

Close Window

Table of Contents

Administering Oracle GoldenGate for Windows and UNIX

Expand | Collapse