Create a Parameter File for Extract

Follow these instructions to create a parameter file for an Extract.

  1. (MySQL only) When running Oracle GoldenGate for MySQL on a Windows server and on a Linux server (for OGG version lower than 23.10.x) which is on a remote host from the database server, ensure that their time zones are the same and if not, use the SETENV(TZ) parameter within the Extract and set it to the time zone of the database server.

    For TZ format and more details, refer to the SETENV parameter in the Parameters and Functions Reference Guide.

    See the following links for reference:

    https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/tzset?view=msvc-170#remarks

    https://www.gnu.org/software/libc/manual/html_node/TZ-Variable.html

  2. On the source system, issue the following command:

    EDIT PARAMS extract_name

    Where:

    extract_name is either the name of the Extract 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.

  3. Enter the parameters in the order shown in the following table, starting a new line for each parameter statement. Some parameters apply only for certain configurations.

    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.
    [, USERIDALIAS alias options ]

    See USERIDALIAS, to specify database credentials.

    ENCRYPTTRAIL algorithm

    Encrypts all trails that are specified after this entry.
    SOURCECATALOG

    Specifies a default container in an Oracle multitenant container database 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.

    TABLE [container. | catalog.]owner.object | schema.object | library/file | library/file(member);

    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, the object name must include the name of the container or catalog unless SOURCECATALOG is used.

    • schema is the schema name or a wildcarded set of schemas.

    • object is the table name, or a wildcarded set of tables.

    • library is the IBM i library name or a wildcarded set of libraries.

    • file is the IBM i physical file name or a wildcarded set of physical files.

    • member is the IBM i physical file member name or a wildcarded set of member names. When using the IBM i native name format (library/file with optional member) the only valid wildcards are a name with at least one valid character followed by a trailing asterisk (*) or *ALL which matches any name.

    Note:

    The member name is optional, and must be provided if the member names are required to be written in the trail as part of the object name. Without member names all members in a physical file be implicitly merged as a single object in the trail.

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

    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.

  4. Enter any appropriate optional Extract parameters listed in the Oracle GoldenGate Parameters.

  5. Save and close the parameter file.

The following sample Extract parameter file explains various configuration parameters and options for Extract:

ADD EXTRACT extract_name
{, datasource} 
{, BEGIN start_point} | {position_point} 
[, PARAMS pathname] 
[, REPORT pathname] 
[, DESC 'description']

  • extract_name is the name of the Extract group. A group name is required.

  • 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. 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 z/OS to specify the Bootstrap Data Set file name of the transaction log.

    • 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.

    • EXTTRAILSOURCE trail_name to specify the relative or fully qualified name of a local trail.

  • 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 Extract in integrated mode, from the time the group is registered with the REGISTER EXTRACT command. Extract needs to be registered for Oracle and PostgreSQL databases only.

      Timestamp: 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.

      The following example shows the repositioning of Extract using a specific timestamp:
      OGG (http://localhost:11000 ggeast as pdb1@east.oracle.com) 95> dblogin useridalias ggma

Successfully logged into database PDB1.
      OGG (http://localhost:11000 ggeast as ggma@ggeast/PDB1) 96> alter extract exte , begin 2024-05-03T03:48:00Z

2024-05-03T03:50:49Z  INFO    OGG-08100  Extract exte I/O position is altered and reposition to older date and time position 2024-05-03 03:48:00.000000 current date and time position 2024-05-03 03:49:04.000000. 
Duplicate transactions are filtered out. Perform output trail ETROLLOVER if duplicate transaction output is desired, or Extract configuration was updated.
2024-05-03T03:50:49Z  INFO    OGG-08100  Extract altered.

  • 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.

  • 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.

Additional Parameter Options for Extract

Learn about additional parameters that may be required for your Extract configuration.

Extract uses a database logmining server in the mining database to mine the redo stream of the source database. You can set parameters that are specific to the logmining server by using the TRANLOGOPTIONS parameter with the INTEGRATEDPARAMS option in the Extract parameter file.

Note:

For detailed information and usage guidance for these parameters, see the "DBMS_CAPTURE_ADM" section in Oracle Database PL/SQL Packages and Types Reference.

The following parameters can be set with INTEGRATEDPARAMS:

  • CAPTURE_IDKEY_OBJECTS: Controls the capture of objects that can be supported by FETCH. The default for Oracle GoldenGate is Y (capture ID key logical change records).

  • DOWNSTREAM_REAL_TIME_MINE: Controls whether the logmining server operates as a real-time downstream capture process or as an archived-log downstream capture process. The default is N (archived-log mode). Specify this parameter to use real-time capture in a downstream logmining server configuration. For more information on establishing a downstream mining configuration, see Downstream Extract for Oracle GoldenGate Deployment.

  • INLINE_LOB_OPTIMIZATION: Controls whether LOBs that can be processed inline (such as small LOBs) are included in the LCR directly, rather than sending LOB chunk LCRs. The default for Oracle GoldenGate is Y (Yes).

  • MAX_SGA_SIZE: Controls the amount of shared memory used by the logmining server. The shared memory is obtained from the streams pool of the SGA. The default is 1 GB.

  • PARALLELISM: Controls the number of processes used by the logmining server. The default is 2. For Oracle Standard Edition, this must be set to 1.

  • TRACE_LEVEL: Controls the level of tracing for the Extract logmining server. For use only with guidance from Oracle Support. The default for Oracle GoldenGate is 0 (no tracing).

  • WRITE_ALERT_LOG: Controls whether the Extract logmining server writes messages to the Oracle alert log. The default for Oracle GoldenGate is Y (Yes).

See Managing Server Resources.