Loading Data with a Direct Bulk Load to SQL*Loader

To use Oracle's SQL*Loader utility to establish the target data, you run an Oracle GoldenGate initial-load Extract to extract the source records and send them directly to an initial-load Replicat task. A task is a process that is started dynamically by the Manager process and does not require the use of a Collector process or file. The initial-load Replicat task interfaces with the API of 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 extract and replicate incremental changes, which are then reconciled with the results of the load.

To control which port is used by Replicat, and to speed up the search and bind process, use the DYNAMICPORTLIST parameter in the Manager parameter file. Manager passes the list of port numbers that are specified with this parameter to the Replicat task process. Replicat first searches for a port from this list, and only if no ports are available from the list does Replicat begin scanning in ascending order from the default Manager port number until it finds an available port.

This method supports standard character, numeric, and datetime data types, as well as CLOB, NCLOB, BLOB, LONG, XML, and user-defined datatypes (UDT) embedded with the following attributes: CHAR, NCHAR, VARCHAR, NVARCHAR, RAW, NUMBER, DATE, FLOAT, TIMESTAMP, CLOB, BLOB, XML, and UDT. VARRAYS are not supported. Character sets are converted between source and target where applicable.

This method supports Oracle internal tables, but does not convert between the source and target character sets during the load.

Description of initsync_dirbulk.jpg follows

Description of the illustration initsync_dirbulk.jpg

To Load Data With a Direct Bulk Load to SQL*Loader

Make certain that you have addressed the requirements in "Prerequisites for Initial Load".
Grant LOCK ANY TABLE to the Replicat database user on the target Oracle database.
On the source and target systems, run GGSCI and start Manager.
```
START MANAGER
```
On the source system, issue the following command to 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 designates Extract as an initial-load process that reads complete records directly from the source tables. Do not use any of the other ADD EXTRACT service options or datasource arguments.
On the source system, issue the following command to create an initial-load Extract parameter file.
```
EDIT PARAMS initial-load_Extract
```

Enter the parameters listed in Table 7-4 in the order shown, starting a new line for each parameter statement. The following is a sample initial-load Extract parameter file for a direct bulk load to SQL*Loader.

EXTRACT initext
SOURCEDB mydb, USERIDALIAS ogg
RMTHOSTOPTIONS ny4387, MGRPORT 7888, ENCRYPT AES 192 KEYNAME mykey
RMTTASK REPLICAT, GROUP initrep
TABLE hr.*;
TABLE sales.*;

Table 7-4 Initial-load Extract Parameters for a Direct Bulk Load to SQL*Loader

Parameter	Description
EXTRACT `initial-load_Extract`	Specifies the initial-load Extract.
[, USERIDALIAS `alias, options` \| , USERID `user`, `options`]	Specifies database connection information. `USERID` and `USERIDALIAS` specify database credentials if required.
RMTHOSTOPTIONS 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` `initial-load_Replicat` is the name of the initial-load Replicat group.	Directs Manager on the target system to dynamically start the initial-load Replicat as a one-time task.
TABLE [`container`.]`owner`.`object`;	Specifies the fully qualified name of an object or a fully qualified wildcarded specification for multiple objects. If the database is an Oracle multitenant container database, the object name must include the name of the container unless `SOURCECATALOG` is used. See Specifying Object Names in Oracle GoldenGate Input for guidelines for specifying object names in parameter files.
`CATALOGEXCLUDE` `SCHEMAEXCLUDE` `TABLEEXCLUDE` `EXCLUDEWILDCARDOBJECTSONLY`	Parameters that can be used in conjunction with one another to exclude specific objects from a wildcard specification in the associated `TABLE` statement. See Reference for Oracle GoldenGate for details.

Enter any appropriate optional parameters.
Save and close the file.
On the target system, issue the following command to 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.
On the target system, issue the following command to create an initial-load Replicat parameter file.
```
EDIT PARAMS initial-load_Replicat
```

Enter the parameters listed in Table 7-5 in the order shown, starting a new line for each parameter statement. The following is a sample initial-load Replicat parameter file for a direct load to SQL*Loader.

REPLICAT initrep
USERIDALIAS ogg
BULKLOAD
SOURCEDEFS /ggs/dirdef/source_defs
MAP hr.*, TARGET hr.*;
MAP sales.*, TARGET hr.*;

Table 7-5 Initial-load Replicat Parameters for Direct Load to SQL*Loader

Parameter	Description
REPLICAT `initial-load_Replicat`	Specifies the initial-load Replicat task to be started by Manager. Use the name that you specified when you created the initial-load Replicat.
[TARGETDB `container`] [, USERIDALIAS `alias, options` \| , USERID `user`, `options`]	Specifies database connection information. `TARGETDB` specifies the target Oracle container. See Reference for Oracle GoldenGate for more information. `USERID` and `USERIDALIAS` specify database credentials if required.
BULKLOAD	Directs Replicat to interface directly with the Oracle SQL*Loader interface. See Reference for Oracle GoldenGate for more information.
{SOURCEDEFS `full_pathname`} \| ASSUMETARGETDEFS Use `SOURCEDEFS` if the source and target tables have different definitions. Specify the source-definitions file generated by DEFGEN. Use `ASSUMETARGETDEFS` if the source and target tables have the same definitions.	Specifies how to interpret data definitions.
`SOURCECATALOG`	Specifies a default source Oracle container for subsequent `MAP` statements. Enables the use of two-part names (`schema.object`) where three-part names otherwise would be required. You can use multiple instances of this parameter to specify different default containers for different sets of `MAP` parameters.
MAP [`container`.]`owner`.`object`, TARGET `owner`.`object`[, DEF `template`] ;	Specifies a relationship between a source object or objects and a target object or objects. `MAP` specifies the source object, and `TARGET` specifies the target object. For the source object, specify the fully qualified name of the object or a fully qualified wildcarded specification for multiple objects. For an Oracle multitenant container database, the source object name must include the name of the container unless `SOURCECATALOG` is used. For the target object, specify only the `owner.object` components of the name, regardless of the database. Replicat can only connect to one Oracle container. Use a separate Replicat process for each container to which you want to load data. See Specifying Object Names in Oracle GoldenGate Input for guidelines for specifying object names in parameter files. The `DEF` option specifies a definitions template.
`CATALOGEXCLUDE` `SCHEMAEXCLUDE` `MAPEXCLUDE` `EXCLUDEWILDCARDOBJECTSONLY`	Parameters that can be used in conjunction with one another to exclude specific source objects from a wildcard specification in the associated `MAP` statement. See Reference for Oracle GoldenGate for details.

Enter any appropriate optional Replicat parameters listed in Reference for Oracle GoldenGate.
Save and close the parameter file.
On the source system, start change extraction.
```
START EXTRACT group
```
View the Replicat parameter file to make certain that the HANDLECOLLISIONS parameter is listed. If not, add the parameter to the file.
(Oracle, if replicating sequences) Issue the DBLOGIN command as the user who has EXECUTE privilege on update.Sequence.
```
GGSCI> DBLOGIN USERID DBLOGINuser, PASSWORD password [encryption_options]
```
(Oracle, if replicating sequences) Issue the following command to update each source sequence and generate redo. From the redo, Replicat performs initial synchronization of the sequences on the target. You can use an asterisk wildcard for any or all characters in the name of a sequence (but not the owner).
```
FLUSH SEQUENCE owner.sequence
```
On the source system, start the initial-load Extract.
```
START EXTRACT initial-load_Extract
```
Caution:

Do not start the initial-load Replicat. The Manager process starts it automatically and terminates it when the load is finished.
On the target system, issue the following command to determine when the load is finished. Wait until the load is finished before proceeding to the next step.
```
VIEW REPORT initial-load_Extract
```
On the target system, start change replication.
```
START REPLICAT group
```
On the target system, issue the following command to verify the status of change replication.
```
INFO REPLICAT group
```
Continue to issue the INFO REPLICAT command until you have verified that Replicat posted all of the change data that was generated during the initial load. For example, if the initial-load Extract stopped at 12:05, make sure Replicat posted data up to that point.
On the target system, issue the following command to turn off the HANDLECOLLISIONS parameter and disable the initial-load error handling.
```
SEND REPLICAT group, NOHANDLECOLLISIONS
```
On the target system, edit the Replicat parameter file to remove the HANDLECOLLISIONS parameter. This prevents HANDLECOLLISIONS from being enabled again the next time Replicat starts.

Caution:

Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside GGSCI if this is the case; otherwise, the contents may become corrupted..
Save and close the parameter file.

From this point forward, Oracle GoldenGate continues to synchronize data changes.