Add Extracts

Learn about adding different type of Extract(s), depending on the specific requirement, and database used with Oracle GoldenGate.

Add a Primary Extract

Set up database credentials to create and run Extract using the steps in Add Database Credentials.

Now, you're ready to add an Extract for your deployment.

From the Overview page of the Administration Service, click the + sign next to Extracts.
Choose the type of Extract to create and click Next.

Note:
To learn about creating initial load Extract, see About Instantiating with Initial Load Extract. You can also create a Change Data Capture (CDC) Extract for MySQL and SQL Server databases. See Add a Change Data Capture (CDC) Extract.

Provide the required information designated with an asterisk (*). Here's a description of the options in the different sections for the Add Extract screen:

Option	Description	Database
Basic Information Section
Process Name	Name of the Extract process. The name of the Extract process can be up to 8 characters.	All databases
Description	Description of the Extract process being created.	All databases
Intent	Describes the purpose of creating the Extract. The default option is Unidirectional. Other options are High Availability, Disaster Recovery, N-Way, which are informational only.	All databases
Begin	Used to set the beginning location in the redo or transaction log from which the Extract will start to capture data. Available options are Now, Custom Time, CSN or Position in Log, and EOF depending on the supported database.	All databases
Trail Name	A two character trail name.	All databases
Trail Subdirectory, Size, Sequence, and Offset	You can further configure the trail details.	All databases
Remote	Enable this option if the Extract trail is remote. For Oracle databases, enable this option if the Extract trail is to be written directly to a remote Oracle GoldenGate Classic installation. For MySQL, setting this option enables the `TRANLOGOPTIONS ALTLOGDEST REMOTE` parameter to support a remote Extract, and is not related to trails.	Oracle, MySQL
Registration Information Section
CSN	Commit Sequence Number (CSN) value	Oracle
Share	Choose the method to share the LogMiner data dictionary. Options are: Automatic: This option allows the system to choose the method for sharing the dictionary . None: Choosing this option, will not allow the dictionary to be shared. Extract: Choose this option to allow sharing the LogMiner dictionary for specific Extract.	Oracle
Optimized	Enable this option to optimize the Extract registration.	Oracle
Downstream Capture	Enable this option to set up a downstream Extract for log mining.	Oracle
Register Only	Use this option to just register the Extract and not add the Extract. The registration creates the replication slot when you register the Extract or use the Register Only option.	PostgreSQL
Source Database Credential
Create new credential	If you haven't set up your database login credentials, you can create and save the database login credentials from here.	All
Credential Domain	Create a domain for the database.	All
Credential Alias	Specify a credential for the database login.	All
User ID	Specify a user name for logging into the database.	All
Password, Verify Password	Enter the password used to login to the database and reenter the password to verify.	All
Credential Domain	Saves the credential user under the specified domain name. Enables the same alias to be used by multiple Oracle GoldenGate installations that use the same credential store. The default domain is Oracle GoldenGate.	All databases
Credential Alias	Specifies an alias for the user name. Use this option if you do not want the user name to be in a parameter file or command. If `ALIAS` is not used, the alias defaults to the user name, which then must be used in parameter files and commands where a login is required. You can create multiple entries for a user, each with a different alias, by using the `ADD USER` option with `ALIAS` .	All databases
Downstream Mining
Mining Credential Domain	Domain name of the downstream mining database.	Oracle
Mining Credential Alias	Alias for the mining downstream database.	Oracle
No UserID	Enable this option if there is no source database connection. Selecting this option enables the ADG fetch options.	Oracle
ADG Fetch Credential Domain	Domain name for the ADG fetch database.	Oracle
ADG Fetch Credential Alias	Domain alias for the ADG fetch database.	Oracle

(Optional) Enter the encryption profile description. If you have not created an encryption profile, then the Local Wallet profile would be selected, by default.
1. Select the profile name from the list box. You can select the Local Wallet or a custom profile.
2. Select the encryption profile type from the list box.
3. Specify the masterkey for the encryption profile. This option doesn't exist with SQL Server.

This is an optional step. Enter the Managed Options while creating all types of Extract processes. See Configure Managed Processes.

The following table provides these options:

Option	Description
Profile Name	Provides the name of the autostart and autorestart profile. You can select the default or custom options. If you have already created a profile, then you can select that profile also. If you select the Custom option, then you can set up a new profile from this section itself.
Critical to deployment health	(Oracle only) Enable this option if the profile is critical for the deployment health.Note:This option only appears while creating the Extract or Replicat and not when you set up the managed processes in the Profiles page.
Auto Start	Enables autostart for the process.
Startup Delay	Time to wait in seconds before starting the process
Auto Restart	Configures how to restart the process if it terminates
Max Retries	Specify the maximum number of retries to try to start the process
Retry Delay	Delay time in trying to start the process
Retries Window	The duration interval to try to start the process
Restart on Failure only	If true the task is only restarted if it fails.
Disable Task After Retries Exhausted	If true then the task is disabled after exhausting all attempts to restart the process.

Click Next.
You can edit the parameter file in the text area to list the table details that you are interested in capturing. For example, table source.table1;.
You can select Register Extract in the background to register the Extract in the background asynchronously. This option is required for Oracle and PostgreSQL databases. See Register an Extract.
Click Create and Run to create and start the Extract. If you select Create, the Extract is created but you need to start it using the Extract drop-down on the Overview page.

You are returned to the Overview page of the Administration Service. Select the Action list if you want to look at the Extract details such as process information, checkpoint, statistics, parameters, and report.

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 Downstream Database Mining.
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.

Add a Change Data Capture (CDC) Extract

These steps configure a CDC Extract to capture transactional data from a source database.

CDC Extract is available with SQL Server and PostgreSQL databases.

Note:

One Extract per database is generally sufficient, but multiple Extracts are allowed if the replication slots are available.

Using the Admin Client, or REST API client on the source system, create the Extract parameter file. EDIT PARAMS extname
In this sample, extname is the name of the primary Extract and matches the name of the Extract that was registered with the database in the previous steps.

To learn about using Oracle GoldenGate Microservices to perform this task, see Add a Primary Extract.

Enter the Extract parameters in the order shown, starting a new line for each parameter statement. Sample basic parameters for Extract for Microservices installations:

EXTRACT extname
SOURCEDB dsn_name 
USERIDALIAS alias
EXTTRAIL ep
GETTRUNCATES
TABLE schema.*;

Parameter	Description
`EXTRACT extname`	`extname` is the name of the Extract and cannot be more than 8 alpha-numeric characters in length. For more information, see extract in Reference for Oracle GoldenGate.
`SOURCEDB dsn_name`	Specifies the name of the database connection DSN.
`USERIDALIAS alias`	Specifies the alias of the database login credential of the user that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store.
`EXTTRAIL trailname`	Specifies a two character, local trail to which the primary Extract writes captured data.
`GETTRUNCATES`	Optional parameter but needed in order to capture truncation operations.
`TABLE schema.object`; or `TABLE schema.*`;	Specifies the database object for which to capture data. `TABLE` specifies a table or a wildcarded set of tables. `schema` is the schema name or a wildcarded set of schemas. object is the table or sequence name, or a wildcarded set of those objects. * is a wildcard for all tables in the schema. Terminate the parameter statement with a semi-colon. To exclude a name from a wildcard specification, use the `SCHEMAEXCLUDE`, `TABLEEXCLUDE`, and `EXCLUDEWILDCARDOBJECTSONLY` parameters as appropriate. Note: If the schema of tables to be captured from is the same as the schema in `GGSCHEMA` of the `GLOBALS` file, which is not recommended, then you cannot use schema.* in the `TABLE` statement.

Enter any optional Extract parameters that are recommended for your configuration. You can edit this file at any point before starting processing by using the EDIT PARAMS command.
Save and close the file.
Add the Extract and its associated trail file.

Topics:

PostgreSQL: Change Data Capture (CDC) Extract

The Oracle GoldenGate Extract process for PostgreSQL receives logical records from the PostgreSQL test_decoding database plugin and writes them in commit order into trail files for downstream consumption by a Replicat.

SQL Server: Change Data Capture (CDC) Extract

See CDC Capture Method Operational Considerations for operational considerations when adding a CDC Extract for SQL Server.

Add Online Extract Groups

You can use the MA web interface or the Admin Client command line interface to set up Extract groups in these forms. This section describes the options and parameters used with the ADD EXTRACT command.

Topics:

Add an Extract Group

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

Where:

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

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

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.

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.
`[SOURCEDB dsn \| container \| catalog] [, USERIDALIAS alias options \| , USERID user, options]`	Specifies database connection information. `SOURCEDB` specifies the source data source name (DSN). See for more information. `USERID` and `USERIDALIAS` specify database credentials if required.
`RMTHOSTOPTIONS 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). Not valid for a passive Extract group.
`ENCRYPTTRAIL algorithm`	Encrypts all trails that are specified after this entry.
`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 `LOGALLSUPCOLS` in Reference for Oracle GoldenGate.
`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.
`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, the object name must include the name of the container or catalog unless `SOURCECATALOG` is used. See 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.

Enter any appropriate optional Extract parameters listed in the Oracle GoldenGate Parameters.
Save and close the parameter file.

Extract Actions

Extract actions include tasks like monitoring details for the Extract, checkpoint details, DDL/DML statistics, cache manager statistics, and other details.

Use the Action button to start or stop the Extract or view and manage its details. When you select the Action, Details option for an Extract, you can perform the following tasks for it.

When you change the status, the list options change accordingly. As status changes, the icons change to indicate the current and final status. The events are added to the Critical Events table. Additionally, progress pop-up notifications appear at the bottom of the page.

Topics:

Access Extract Details

From the Extract section of the Administration Service Overview page, click Action, Details for the specific Extract to view its details. The following tabs are displayed:

Process Information:

The status of the selected Extract process including the type, credentials, and trail details including trail name, trail subdirectory, trail sequence, and trail size.
Checkpoint:

The checkpoint log name, path, timestamp, sequence, and offset value. You can monitor the input details, such as when starting, at recovery, and the current state. The checkpoint output values display the current checkpoint details.
Statistics:

The active replication maps along with replication statistics based on the process type. You sort the lost to view the entire statistical data, daily, or hourly basis.
Cache Manager Statistics:

Access the global statistics and object pool statistics information for the Extract process from this page.
Parameters:

The parameters configured when the process was added. You can edit the parameters by clicking the pencil icon. Make sure that you apply your changes.
Report:

A detailed report of the process including parameter settings and a log of the transactions. You could copy the report text and save it to a file so that you can share or archive it.

Start or Stop Extract

From the Administration Service Overview page, click Action, Start/Stop option. If the Extract is in abended state, it displays with a yellow icon. A green icon indicates that the Extract is running and a red icon indicates Extract is in stopped state.

Delete Extract

To delete an Extract:

Stop the Extract using the Actions, Stop option from the Extract section of the Administration Service Overview page.
Click Delete to remove the Extract.

Note:

The Delete option appears only when the Extract is in Stopped state.