REGISTER EXTRACT

This command applies to Oracle Database and PostgreSQL.

Oracle database

For Oracle database, use REGISTER EXTRACT to register a primary Extract group with an Oracle database to:

Enable integrated capture mode
Specify options for integrated Extract from a multitenant container database

REGISTER EXTRACT is not valid for a data pump Extract.

To unregister an Extract group from the database, use the UNREGISTER EXTRACT command.

PostgreSQL

Using this command, a replication slot is created in the connected source database. This command ensures that the PostgreSQL database does not purge the transaction log until the replication slot is moved or removed. The REGISTER EXTRACT command must be run before running the ADD EXTRACT command. A database connection using DBLOGIN is required before registering the Extract.

From Oracle GoldenGate 21.3 release onward, it's not mandatory to enter the database_name.

Syntax

Oracle:

REGISTER EXTRACT group-name
          (  | DATABASE
          ( [ CONTAINER container-list |
              ADD   CONTAINER container-list |
              DROP  CONTAINER container-list ]  
            [ SCN   scn   ]  
            [ SHARE ( AUTOMATIC | group-name | NONE ) ]  
            [ [NO]OPTIMIZED ]  
          )

Container-list is a comma separated list of PDB names, for example (pdbeast, pdbwest); or wildcarded PDB names, for example (pdb* or pdb?); or both, for example (cdbnorth, pdb*). Supported wildcards are ? and *.

The OPTIMIZED option improves Extract fast startup. The default value is NOOPTIMIZED. The OPTIMIZED option only impacts an upstream non multitenant configuration.

PostgreSQL:

REGISTER EXTRACT Extract

or

REGISTER EXTRACT Extract with DATABASE database_name

A replication slot is created in the database for the given Extract group name.

group_name: The name of the Extract group that is to be registered. Do not use a wildcard.

DATABASE [

CONTAINER (container[, ...]) |

ADD CONTAINER (container[, ...]) |

DROP CONTAINER (container[, ...])

]

Without options, DATABASE enables integrated capture from a non-CDB database for the Extract group. In this mode, Extract integrates with the database logmining server to receive change data in the form of logical change records (LCR). Extract does not read the redo logs. Extract performs capture processing, transformation, and other requirements. The DML filtering is performed by the logmining server.

Before using REGISTER EXTRACT with DATABASE, use the DBLOGIN command for all Extracts with the privileges granted using the dbms_goldengate_auth.grant_admin_privilege procedure. If you have a downstream configuration, then you must also issue the MININGDBLOGIN command. If the source database you are registering is a CDB database and Extract will fetch data, then grant_admin_privilege must be called with the CONTAINER=>'ALL' parameter.

After using REGISTER EXTRACT, use ADD EXTRACT with the INTEGRATED TRANLOG option to create an Extract group of the same name.

CONTAINER (container[, ...])

Applies the registration to a list of one or more pluggable databases (containers) of a multitenant container database (CDB). Specify one or more pluggable databases as a comma-delimited list within parentheses, for example: CONTAINER (pdbeast, pdbwest). If you list the pluggable databases, they must exist in the database. You can also specify the pluggable databases using the wildcards * and ?. For example, CONTAINER (pdb*).

ADD CONTAINER (container[, ...])

Adds the specified pluggable database to an existing Extract capture configuration. Specify one or more pluggable databases as a comma-delimited list within parentheses or using the wildcards * and ?. For example: ADD CONTAINER (pdbeast, pdbwest). Before issuing REGISTER EXTRACT with this option, stop the Extract group.

For Oracle, adding containers at particular SCN on an existing Extract is not supported.

DROP CONTAINER (container[, ...])

Drops the specified pluggable database from an existing Extract capture configuration. Specify one or more pluggable databases as a comma-delimited list within parentheses or using the wildcards * and ?. For example,DROP CONTAINER (pdbeast, pdbwest).

Registering the Extract after running the drop container option, does not fully happen until the Extract has been started and it reads a committed transaction from a dropped pluggable database, which is greater than the Extract checkpoint SCN. Extract then fully drops the containers and shuts down with a message.

Before running REGISTER EXTRACT with this option, stop the Extract group.

SCN system_change_number

Registers Extract to begin capture at a specific system change number (SCN) in the past. Without this option, capture begins from the time that REGISTER EXTRACT is issued. The specified SCN must correspond to the begin SCN of a dictionary build operation in a log file. You can issue the following query to find all valid SCN values:

SELECT first_change#
   FROM v$archived_log 
   WHERE dictionary_begin = 'YES' AND 
      standby_dest = 'NO' AND
      name IS NOT NULL AND 
      status = 'A';

When used alone, the SCN value is the beginning SCN of the dictionary build operation in a log file.

When used in conjunction with SHARE AUTOMATIC or SHARE extract_name, then the specified SCN is the start_scn for the capture session and has the following restrictions:

Should be lesser than or equal to the current SCN.
Should be greater than the minimum (first SCN) of the existing captures.

{SHARE [

AUTOMATIC |

extract |

NONE]}

Registers the Extract to return to an existing LogMiner data dictionary build with a specified SCN creating a clone. This allows for faster creation of Extracts by leveraging existing dictionary builds.

SHARE cannot be used on a CDB.

The following commands are supported:

REGISTER EXTRACT extract database SCN #### SHARE AUTOMATIC 
REGISTER EXTRACT extract database SCN #### SHARE extract 
REGISTER EXTRACT extract database SHARE NONE 
REGISTER EXTRACT extract database SCN #### SHARE NONE

Or

REGISTER EXTRACT extract DATABASE SHARE NONE
REGISTER EXTRACT extract DATABASE SCN #### SHARE NONE

In contrast, the following commands are not supported in a downstream configuration:

REGISTER EXTRACT extract DATABASE SHARE AUTOMATIC
REGISTER EXTRACT extract DATABASE SHARE extract

AUTOMATIC: Clone from the existing closest capture. If no suitable clone candidate is found, then a new build is created.
extract: Clone from the capture session associated for the specified Extract. If this is not possible, then an error occurs the register does not complete.
NONE: Does not clone or create a new build; this is the default.

In a downstream configuration, the SHARE clause must be used in conjunction with the SCN clause when registering for Extract.

Examples

REGISTER EXTRACT exte LOGRETENTION

REGISTER EXTRACT exte DATABASE

REGISTER EXTRACT exte DATABASE CONTAINER (pdbeast, pdbwest, pdbsouth)

REGISTER EXTRACT exte DATABASE ADD CONTAINER (pdbnorth)

REGISTER EXTRACT exte DATABASE DROP CONTAINER (pdbnorth)

REGISTER EXTRACT exte DATABASE SCN 136589

The beginning SCN of the dictionary build is 136589.

REGISTER EXTRACT exte DATABASE SCN 67000 SHARE extw

The valid start SCN, 67000 in this case; it is not necessarily the current SCN.

REGISTER EXTRACT exte DATABASE CONTAINER (pdbeast, pdbeast, pdbsouth) SCN 136589

For PostgreSQL:

REGISTER EXTRACT exte WITH DATABASE hr