Register an Extract from the Admin Client

Registering an Extract is required for change synchronization for Oracle and PostgreSQL databases.

This topic describes registering an Extract from the Admin Client. You can also register an Extract from the web interface while creating the Extract. See Add an Online Extract to see the steps to register an Extract from the web interface.

In case you need to upgrade Oracle GoldenGate and the installation directory for the Extract process is different, you need to use the MIGRATE option with the REGISTER EXTRACT command in Admin Client. See REGISTER EXTRACT in the Command Line Interface Reference for Oracle GoldenGate to know more.

Register Extract for Oracle

Follow these instructions to register an Extract. Extract registration must be done prior to creating an Extract.

Ensure that you are connected with the database using the DBLOGIN command.

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
```
When running the CONNECT command, the command prompt changes from "not connected" to "https://servername:port deployment_name", as shown in the following example:
```
OGG (https://pdbeast.vcn.oracle.com:16000depl_east)>
```

Connect to the database using the DBLOGIN command:

OGG (https://remotehost:portoracle_source)> DBLOGIN USERIDALIAS alias

Register the Extract. The Extract names cannot be more than 8 alpha-numeric characters.
```
OGG (https://remotehost:portoracle_source)> REGISTER EXTRACT extname DATABASE
```

You can also register an Extract in the background while creating an Extract from the Oracle GoldenGate MA web interface. See Add an Online Extract for details.

Register Extract 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.

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: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.
```
REGISTER EXTRACT extname
```
To explicitly register Extract with the pgoutput plugin, you must specify the pgoutput plugin type:
```
REGISTER EXTRACT extname PGPLUGINTYPE pgoutput;
```

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

Guidelines and Limitations for PGOUTPUT Plugin Type

Guidelines when configuring PGOUTPUT plugin type

Follow these guidelines when setting the plugin type for PostgreSQL:

The publication(s) must be created before registering the Extract process.
Publication names must not contain special characters: comma (,), single quote ('), and double quotes (").
Multiple publication names can be specified as a comma-separated list within publication_names. For example:
```
TRANLOGOPTIONS STREAMINGOPTIONS (publication_names="my*123,my_publication2,123 pub,my_publication";)
```
For details, see TRANLOGOPTIONS STREAMINGOPTIONS parameter.
The final publication name in the list must end with a semicolon (;).

Limitations

The following restrictions apply while selecting a plugin type for PostgreSQL:

Timezone (TZ) environment variable: The WAL sender process always transmits commit timestamps in UTC.If the OGG session timezone differs from UTC, discrepancies may occur when positioning by timestamp in the CDC Extract process. To prevent this issue, users must set the environment variable TZ=UTC at the deployment layer. This limitation applies specifically to the pgoutput plugin.
Multiple truncates: Multiple truncate operations are only supported in pgoutput plugin. The test_decoding plugin type does not allow multiple truncates.
Tables with unique indexes but no Primary Key: If a table does not have primary keys but contains unique indexes, then the replica identity must use indexes or it must be set to FULL.
Publication Deletion Impact on CDC Extract: If a publication is deleted while the CDC Extract is still running, there is a risk of replication slot corruption, causing it to stop functioning. In such cases, the impacted replication slot is not usable, and recreating the same publication will not resolve the issue. The recommended solution is to create a new publication and associate it with a new replication slot to restore CDC functionality.
Tables eligible for publication: Only persistent base tables and partitioned tables can be included in a publication. The following object types cannot be part of a publication:
- Temporary tables
- Unlogged tables
- Foreign tables
- Materialized views
- Regular views
Bi-Directional Replication Support with pgoutput Plugin: When using the pgoutput plugin for bidirectional replication, the checkpoint table must be created and included in the publication list before starting the CDC Extract process. Additionally, the replication slot should be created only after the checkpoint table has been created and added to the publication.
Column-level publication limitation: Specifying a subset of columns for a table in a publication is not supported for CDC replication. You must include the entire table in the publication. Attempting to add only partial columns may lead to errors or data discrepancies in the CDC stream.