REGISTER EXTRACT

This command applies to Oracle Database and PostgreSQL.

Oracle database

This command registers a primary Extract group to enable integrated capture mode and specify options for integrated Extract from a multitenant container database

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

Syntax:

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.

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

Valid for Oracle.

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 USERIDALIAS 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 from dictionary dumps at CDB level: 

SELECT first_change#
   FROM v$archived_log 
   WHERE dictionary_begin = 'YES' AND 
      standby_dest = 'NO' AND
      name IS NOT NULL AND 
      status = 'A';
However, this query does not work when registering a per-PDB Extract. To identify start SCN of available dictionary dumps for a specific PDB, we can follow these steps:

  1. Connect to the PDB.

  2. Run the show con_id command to get the PDB identifier.

  3. Run the following query to list all available dictionary dumps in descending order:
    SELECT DATE_OF_BUILD, START_SCN FROM dba_logmnr_dictionary_buildlog WHERE CONTAINER_ID = PDB identifier ORDER BY DATE_OF_BUILD DESC;

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]}

Valie for Oracle.

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.

PostgreSQL

This command creates a replication slot in the connected source database and 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.

You must connect to the PostgreSQL database instance using DBLOGIN USERIDALIAS before registering the Extract.

You can also register an Extract from the Oracle GoldenGate Administration Service web interface. For details, see Register Extract for PostgreSQL in the Oracle GoldenGate Microservices Documentation.

You can also set up streaming options for PostgreSQL, using the TRANLOGOPTIONS STREAMINGOPTIONS parameter, to supply parameters during replication startup. For details, see TRANLOGOPTIONS,

Note:

Starting with Oracle GoldenGate 21c (21.3) release onward, it's not mandatory to enter the database_name.

Syntax:

REGISTER EXTRACT group_name

or

REGISTER EXTRACT group_name PGPLUGINTYPE pgoutput;

Note:

To explicitly register with the pgoutput plugin, you need to specify the pgoutput plugin type in the command. If you do not select pgoutput plugin, then the default plugin type, test_decoding, is set.
or
REGISTER EXTRACT group_name MIGRATE replication_slot_name
or
REGISTER EXTRACT group_name with DATABASE database_name

or

group_name

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

MIGRATE replication_slot_name

Valid for PostgreSQL.

In case of Oracle GoldenGate upgrade, where the installation directory is different for the Extract processes, REGISTER EXTRACT with the MIGRATE option is required.

The replication_slot_name is the name of any existing replication slot present in the database. This command creates a new replication slot by copying the LSN information present in the original slot replication_slot_name. The copied replication slot starts from the same LSN as the original one.

Note:

The original replication slot must be present in the same database from where we want to create a new slot.
PGPLUGINTYPE test_decoding | pgoutput

Valid for PostgreSQL.

This option has been added to allow selecting the logical decoding plugin for PostgreSQL. You can select test_decoding or pgoutput. By default, the CDC Extract registers using the test_decoding plugin type. So, if you don't specify the pgplugintype as pgoutput, then test_decoding will be used automatically.

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 with the plugin type specified as PGOUTOPUT:
REGISTER EXTRACT exte PLUGINTYPE pgoutput WITH DATABASE hr