Configuring a Direct Bulk Load to SQL*Loader

With this method, you configure and run an Oracle GoldenGate initial-load Extract to extract complete source records and send them directly to an initial-load Replicat task. The initial-load Replicat task communicates with SQL*Loader to load data as a direct-path bulk load. Data mapping and transformation can be done by either the initial-load Extract or initial-load Replicat, or both. During the load, the change-synchronization groups that you configured in Configuring Primary Extract and Configuring Oracle GoldenGate Replicat replicate incremental changes, which are then reconciled with the results of the load.

The following diagram shows configuring a direct bulk load to SQL*Loader.

Description of the illustration init-sync-dirbulk.png

Limitations:

• This method does not support extraction of LOB or LONG data. As an alternative, see Performing Instantiation From an Input File to SQL*Loader.

• This method does not support materialized views that contain LOBs, regardless of their size. It also does not support data encryption.

To Configure a Direct Bulk Load to SQL*Loader

1. Grant LOCK ANY TABLE to the Replicat database user on the target Oracle Database.
2. On the source and target systems, run GGSCI.
3. Start Manager on both systems.

   START MANAGER
   

4. On the source system, create the initial-load Extract.

   ADD EXTRACT initial-load_Extract, SOURCEISTABLE
   

   Where:

   • initial-load_Extract is the name of the initial-load Extract, up to eight characters.

   • SOURCEISTABLE directs Extract to read complete records directly from the source tables.

5. On the source system, create the initial-load Extract parameter file.

   EDIT PARAMS initial-load_Extract
   

6. Enter the initial-load Extract parameters in the order shown, starting a new line for each parameter statement. This example shows a three-part table name associated with a multitenant container database.

   EXTRACT initext
   USERIDALIAS tiger1
   RMTHOST fin1, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2
   RMTTASK replicat, GROUP initrep
   TABLE hq.hr.*;

   Parameter	Description
   EXTRACT initial-load_Extract	Specifies the name of the initial-load Extract, as stated with ADD EXTRACT.
   USERIDALIAS alias	Specifies the alias of the database login credential that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store. For more information, see Establishing Oracle GoldenGate Credentials
   RMTHOST hostname, MGRPORT portnumber[, ENCRYPT algorithm KEYNAME keyname]	Specifies the target system, the port where Manager is running, and optional encryption of data across TCP/IP.
   RMTTASK REPLICAT, GROUP initial-load_Replicat	Specifies the process type (must be REPLICAT) and the name of the initial-load Replicat. Directs Manager on the target system to dynamically start the initial-load Replicat as a one-time task.
   TABLE [container.]schema.table;	Specifies the tables to capture. container is the name of the pluggable database, if this is a multitenant container database. You can use the SOURCECATALOG parameter to specify a default pluggable database instead of using three-part names. schema is the schema name. table is the table name.

7. Save and close the file.
8. On the target system, create the initial-load Replicat.

   ADD REPLICAT initial-load Replicat, SPECIALRUN
   

   Where:

   • initial-load Replicat is the name of the initial-load Replicat task.

   • SPECIALRUN identifies the initial-load Replicat as a one-time task, not a continuous process.

9. On the target system, create the initial-load Replicat parameter file.

   EDIT PARAMS initial-load Replicat
   

10. Enter the initial-load Replicat parameters in the order shown, starting a new line for each parameter statement. This example shows a three-part source table name associated with a multitenant container database.

    REPLICAT initrep
    USERIDALIAS tiger2
    BULKLOAD
    ASSUMETARGETDEFS
    MAP hq.hr.*, TARGET hr2.*;
    

    Parameter	Description
    REPLICAT initial-load Replicat	Specifies the name of the initial-load Replicat task, as stated with ADD REPLICAT.
    USERIDALIAS alias	Specifies the alias of the database login credential that is assigned to Replicat. This credential must exist in the Oracle GoldenGate credential store.
    BULKLOAD	Directs Replicat to interface directly with the Oracle SQL*Loader interface.
    ASSUMETARGETDEFS	Assumes the source and target tables are identical, including semantics. If source and target definitions are different, you must create and specify a source-definitions file that both the change-synchronization and initial-load processes will use.
    MAP [container.]schema.table, TARGET schema.table;	Specifies a relationship between a source and target table or tables. If the source is a multitenant container database, container is the name of the pluggable database that contains the source objects specified with this MAP statement. You can use the SOURCECATALOG parameter to specify a default source pluggable database instead of using three-part names. schema is the schema name. table is the table name.