Before Adding an Extract

Register an Extract

Valid for Oracle and PostgreSQL.

Registering an Extract is needed for Oracle and PostgreSQL databases.

Topics:

Registering Extract for Oracle

Follow these instructions to register an Extract. Extract registration must be done prior to creating an Extract. See REGISTER EXTRACT in the Command Line Interface Reference for Oracle GoldenGate for more information.

Using the Admin Client, connect to the deployment, then connect to the credential alias for the source database.

CONNECT https://remotehost:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password

OGG (https://remotehost:16000oracle_source)> DBLOGIN USERIDALIAS alias

Register the Extract, which internally creates a replication slot for the Extract. Extract names cannot be more than 8 alpha-numeric characters.
```
OGG (https://remotehost:16000oracle_source)> REGISTER EXTRACT extname
```

You can also register an Extract from the Oracle GoldenGate MA web interface. See Add a Primary Extract.

Registering Extract in Microservices Architecture for PostgreSQL

An Extract for PostgreSQL must be registered with the database and be granted a reserved replication slot. Replication slots are allocated through the database configuration setting max_replication_slots and can be configured as discussed in Database Configuration.

Follow these instructions to register an Extract. Extract registration must be done prior to creating an Extract. See REGISTER EXTRACT in the Command Line Interface Reference for Oracle GoldenGate for more information.

Using the Admin Client, connect to the deployment, then connect to the credential alias for the source database.

OGG> CONNECT https://remotehost:srvmgrport DEPLOYMENT
          deployment_name AS deployment_user PASSWORD deployment_password

OGG (https://remotehost:16000postgresql_source)> DBLOGIN USERIDALIAS alias

Register the Extract, which internally creates a replication slot for the Extract. Extract names cannot be more than 8 alpha-numeric characters.
```
OGG (https://remotehost:16000postgresql_source)> REGISTER EXTRACT extname
```

You can also register an Extract from the Oracle GoldenGate MA web interface. See Add a Primary Extract.

Access the Configurations Page

Configure connections to the database from Oracle GoldenGate by setting up database user credentials from the Configurations page of the Administration Service left-navigation pane.

See Add Database Credentials for steps to create credentials for the database and test the connection. You can set up database credentials to set up connections to multiple databases, as required by the Extract and Replicat processes.

Add Database Credentials

You must have a working database credential for your Extract and Replicat processes.

Launch the Administration Service interface and log in.
Click Configuration from the Application Navigation pane.
Click the plus sign (+) sign next to Credentials.

Enter the following details in the displayed fields:

Database Credential Options	Description
Credential Domain	Specify a domain name to which the database credential is associated. For example, "OracleGoldenGate" is the default domain name, incase you don't specify a domain name.
Credential Alias	This is the alias for your database credential.
User ID	This is the username of the database user. For Oracle database, if you use the EZconnect syntax to connect to the database, then you can specify the value in this field in the following manner: `dbusername@hostname:port/service_name` `dbusername` is the database user name. `hostname` or IP address of the server where the database is running. `port` is the port number for connecting to the database server. Usually, this value is 1521. `service_name` is the name of the service provided in the tnsnames.ora file for the database connection.
Password	Password used by database user to log in to the database.

Click Submit.
Click the Connect to database icon to test that the connection is working correctly. If the connection is successful, the Connect to database icon turns blue. You'll also see sections to set up checkpoint and heartbeat tables after the connection is successful.

Enable TRANDATA

Valid for Oracle and Non-Oracle databases.

Depending on the source database, supplemental logging must be enabled. This can be done at the table, schema, or global (database) level.

You can skip ADD TRANDATA in case of initial load without CDC.

Topics:

Oracle: Enable TRANDATA or SCHEMATRANDATA

Valid for Oracle.

Depending on the source database, supplemental logging must be enabled. This can be done at the table, schema, or global (database) level.

To enable supplemental logging at the table and schema level, on Configuration page:

Select the Table or Schema option as required and click plus sign to add.
Enter the name of the table for which you need to set up supplemental logging. Make sure to enter the full table name with schema name, such as, schema.table1. You can also use wildcard instead of specific table name.
Select the Add TRANDATA Information in the background? option as required.
Click Submit.

You can also use the commands ADD TRANDATA and ADD SCHEMATRANDATA for setting up trandata and schema level trandata. For details, see ADD TRANDATA and ADD SCHEMATRANDATA. You can skip ADD TRANDATA in case of initial load without CDC.

Db2 z/OS: Enable Change Capture

Follow these steps to configure Db2 to log data changes in the expanded format that is supplied by the DATA CAPTURE CHANGES feature of the CREATE TABLE and ALTER TABLE commands. This format provides Oracle GoldenGate with the entire before and after images of rows that are changed with update statements.

From the Oracle GoldenGate directory, start the Admin Client.

Log on to Db2 as a user that has ALTER TABLE privileges.

DBLOGIN SOURCEDB DSN, USERID user[, PASSWORD password][, encryption_options]

Issue the following command. where table is the fully qualified name of the table. You can use a wildcard to specify multiple table names but not owner names.
```
ADD TRANDATA table
```
By default, ADD TRANDATA issues the following command:
```
ALTER TABLE name DATA CAPTURE CHANGES;
```

SQL Server: Enable Supplemental Logging and Other Features

A database user must issue the ADD TRANDATA command to enable supplemental logging on the source database in an Oracle GoldenGate configuration. A database login command (DBLOGIN) is issued from the command line interface before ADD TRANDATA is issued.

The database user that enables TRANDATA must have sysadmin rights.

Extract can run with dbowner permissions. However, you also need sysadmin rights to issue the ADD/ALTER/ DELETE/INFO HEARTBEATTABLE commands, or to create the Oracle GoldenGate CDC Cleanup job using the ogg_cdc_cleanup_setup.bat batch file.

Add Heartbeat Table

Heartbeat tables are used to monitor lag throughout the data replication cycle. Automatic heartbeats are sent from each source database into the replication streams, by updating the records in a heartbeat seed table and a heartbeat table, and constructing a heartbeat history table. Each of the replication processes in the replication path process these heartbeat records and update the information in them. These heartbeat records are inserted or updated into the heartbeat table at the target databases.

To create the heartbeat table, you have to follow these steps on the source and target system:

Note:

Creating the heartbeat table is optional but is recommended.

From the Administration Service, select Configuration from the navigation pane.
Select the + sign next to the Heartbeat section of the Database tab. You'll need to enter the values for the heartbeat frequency, retention time, and purge frequency.

Here are the steps to add a heartbeat table from the Admin Client:

Launch the Admin Client from the command line.

Connect to the deployment from the Admin Client.

CONNECT https://remotehost:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password

Here's an example:

CONNECT https://remotehost:16000 DEPLOYMENT ggdep_postgres AS ggadmin PASSWORD P@ssWord

Connect to the source and target databases using the DBLOGIN USERIDALIAS command. The following example shows the connection to the source database with credential alias ggeast:
```
(https://remotehost:16000 ggdep_postgres)> DBLOGIN USERIDALIAS ggeast
```

Add the heartbeat table:

(https://remotehost:16000 ggdep_postgres)> ADD HEARTBEATTABLE

Optionally, for a target only database, one that is used for unidirectional replication only, you can include the TARGETONLY option which will not create a heartbeat record update function.

See ADD HEARTBEATTABLE for details about command options.

Topics:

Create the Oracle GoldenGate CDC Cleanup Task

For SQL Server users, there is a requirement to create Oracle GoldenGate CDC Cleanup tasks before adding an Extract. You can do so by performing the steps in Details of the Oracle GoldenGate CDC Cleanup Process.

Running the Heartbeat Update and Purge Function for PostgreSQL

Oracle GoldenGate for PostgreSQL supports a heartbeat table configuration, with some limitations regarding the update and purge tasks.

The heartbeat table and associated functions are created from the ADD HEARTBEATTABLE command, however for PostgreSQL, there is no automatic scheduler to call the functions.

One main function controls both the heartbeat record update and the heartbeat history table purge functions. The default settings for both of these features are 60 seconds for the update frequency and 1 day for the history record purge, which deletes all records older than 30 days by default.

To call the main heartbeat record function, users should create an operating system level job that executes

“select ggschema.gg_hb_job_run();”

. When this function is called, it will take into account the update frequency settings and history record purge settings and use those values regardless of the scheduler settings for the function call.

For example, users can create a Cron Job with the following syntax, and have it run every minute.

*****PGPASSWORD="gguserpasswd" psql -U gguser -d dbname -h remotehost -p 5432 -c "select ggschema.gg_hb_job_run();" >/dev/null
 2>&1

Windows Task Scheduler, pgAdmin, or pg_cron are other programs that could be used to schedule the function call.

Add a Checkpoint Table

You can view the checkpoint table within the checkpoint section. In case you want to add a checkpoint table for the target system:

Click the plus sign to enable adding a checkpoint table.
Add the checkpoint table name in the format
```
table.checkpoint_table_name
```
.
Click Submit. After the checkpoint is created, you'll be able to see in the list of checkpoint tables.

To perform this task from the command line, see ADD CHECKPOINTTABLE in the Command Line Interface Reference for Oracle GoldenGate.