14 Instantiating Oracle GoldenGate with an Initial Load

This chapter describes running an initial data load to instantiate the replication environment.

The initial load can be done in Classic Architecture and in Microservices Architecture.

• Overview of the Initial-Load Procedure
  
• Initial Load in Classic Architecture
  In Classic Architecture you can load data using various options. The processes and steps do so, are described in this topic.

14.1 Overview of the Initial-Load Procedure

You can use Oracle GoldenGateto:

• Perform a standalone batch load to populate database tables for migration or other purposes.

• Load data into database tables as part of an initial synchronization run in preparation for change synchronization with Oracle GoldenGatee.

• Improving the Performance of an Initial Load
  
• Prerequisites for Initial Load
  

14.1.1 Improving the Performance of an Initial Load

For all initial load methods except those performed with a database utility, you can load large databases more quickly by using parallel Oracle GoldenGate processes. To use parallel processing, take the following steps.

1. Follow the directions in this chapter for creating an initial-load Extract and an initial-load Replicat for each set of parallel processes that you want to use.
2. With the TABLE and MAP parameters, specify a different set of tables for each pair of Extract-Replicat processes, or you can use the SQLPREDICATE option of TABLE to partition the rows of large tables among the different Extract processes.

For all initial load methods, testing has shown that using the TCPBUFSIZE option in the RMTHOST parameter produced three times faster throughput than loads performed without it. Do not use this parameter if the target system is NonStop.

14.1.2 Prerequisites for Initial Load

Verify that you meet the prerequisites for executing an initial load that are described in the following sections.

• Disable DDL Processing
  
• Prepare the Target Tables
  
• Configure the Manager Process
  
• Create a Data-definitions File
  
• Create Change-synchronization Groups
  
• Sharing Parameters between Process Groups
  

14.1.2.1 Disable DDL Processing

Before executing an initial load, disable DDL extraction and replication. DDL processing is controlled by the DDL parameter in the Extract and Replicat parameter files.

14.1.2.2 Prepare the Target Tables

The following are suggestions that can make the load go faster and help you to avoid errors.

• Data: Make certain that the target tables are empty. Otherwise, there may be duplicate-row errors or conflicts between existing rows and rows that are being loaded.

• Constraints: Disable foreign-key constraints and check constraints. Foreign-key constraints can cause errors, and check constraints can slow down the loading process. Constraints can be reactivated after the load concludes successfully.

• Indexes: Remove indexes from the target tables. Indexes are not necessary for inserts. They will slow down the loading process significantly. For each row that is inserted into a table, the database will update every index on that table. You can add back the indexes after the load is finished.

  Note:

  A primary index is required for all applications that access DB2 for z/OS target tables. You can delete all other indexes from the target tables, except for the primary index.

• Keys: For Oracle GoldenGate to reconcile the replicated incremental data changes with the results of the load, each target table must have a primary or unique key. If you cannot create a key through your application, use the KEYCOLS option of the TABLE and MAP parameters to specify columns as a substitute key for Oracle GoldenGate's purposes. A key helps identify which row to process. If you cannot create keys, the source database must be quiesced for the load.

14.1.2.3 Configure the Manager Process

On the source and target systems, configure and start a Manager process. One Manager can be used for the initial-load processes and the change-synchronization processes. See Configuring Manager and Network Communications for more information. For enhanced security, the target manager parameter file should have the following parameter for RMTTASK to access Replicat on target:

ACCESSRULE, PROG *, IPADDR *, ALLOW

14.1.2.4 Create a Data-definitions File

A data-definitions file is required if the source and target databases have dissimilar definitions. Oracle GoldenGate uses this file to convert the data to the format required by the target database.

14.1.2.5 Create Change-synchronization Groups

To prepare for the capture and replication of transactional changes during the initial load, create online Extract and Replicat groups. You will start these groups during the load procedure. See Configuring Online Change Synchronization for more information.

Note:

If the load is performed from a quiet source database and will not be followed by continuous change synchronization, you can omit these groups.

Do not start the Extract or Replicat groups until instructed to do so in the initial-load instructions. Change synchronization keeps track of transactional changes while the load is being applied, and then the target tables are reconciled with those changes.

Note:

The first time that Extract starts in a new Oracle GoldenGate configuration, any open transactions will be skipped. Only transactions that begin after Extract starts are captured.

14.1.2.6 Sharing Parameters between Process Groups

Some of the parameters that you use in a change-synchronization parameter file also are required in an initial-load Extract and initial-load Replicat parameter file. You can copy those parameters from one parameter file to another, or you can store them in a central file and use the OBEY parameter in each parameter file to retrieve them. Alternatively, you can create an Oracle GoldenGate macro for the shared parameters and then call the macro from each parameter file with the MACRO parameter.

See Getting Started with the Oracle GoldenGate Process Interfaces for more information about using OBEY and using macros.

14.2 Initial Load in Classic Architecture

In Classic Architecture you can load data using various options. The processes and steps do so, are described in this topic.

• Loading Data with Oracle Data Pump
  
• Loading Data from File to Replicat
  
• Loading Data with an Oracle GoldenGate Direct Load
  
• Loading Data with a Direct Bulk Load to SQL*Loader
  

14.2.1 Loading Data with Oracle Data Pump

This method uses the Oracle Data Pump utility to establish the target data. After you apply the copy to the target, you record the SCN at which the copy stopped. Transactions that were included in the copy are skipped to avoid collisions from integrity violations. With the data pump method, Replicat has the information about the consistent SCN from the export of each table. Replicat will ignore changes that belongs to transactions up to this SCN. Transactions after this SCN will be applied. No initial-load Oracle GoldenGate processes are required for these methods.

• Using Automatic Per Table Instantiation
  
• Using Oracle Data Pump Table Instantiation
  

14.2.1.1 Using Automatic Per Table Instantiation

On the Source Database

You can automatically instantiate per table CSN filtering for Oracle Database with Oracle data pump, which avoids having all of your tables at the same SCN.

1. Use ADD TRANDATA and ADD SCHEMATRANDATA. ADD TRANDATA/SCHEMATRANDATA.PREPARECSN automatically prepares the tables at the source so the Oracle data pump export dump file includes instantiation CSNs. Replicat uses the per table instantiation CSN set by the Oracle data pump (on import) to filter out trail records.

   Use INFO TRANDATA to make sure that your table is prepared for instantiation and at what point it was done. Here's a sample of the report file:

   2016-09-29 15:30:00 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema
         SCOTT

2. Stop Replicat on the target database.

3. Start Extract with the correct TABLE statement.

The EXPORT datapump option FLASHBACK_SCN is not needed as the tables have been prepared earlier.

On the Target Database

1. Import your exported tables using Oracle data pump, which populates system tables and views with instantiation SCNs, as well as the specified table data.

2. Start Replicat using one of the following:

   Set the DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter in the Replicat parameter file to enable table-level instantiation filtering.

   You can remove this parameter when replicat has processed all transactions beyond the instantiation SCN.

   For all other Replicats, set the DBOPTIONS source_dbase_name global_name parameter in the Replicat parameter file where global_name is the global name of the Oracle source database that the trail is coming from.

   Note:

   When the source has no DOMAIN, do not specify a DOMAIN for the downstream database.

   Replicat queries the instantiation SCN on any new mapping and filter records accordingly. For example, see the following report file output:

   2015-06-29 17:12:39 INFO OGG-10155 Oracle GoldenGate Delivery for Oracle, r1.prm:
   Instantiation CSN filtering is enabled on table SCOTT.EMP at CSN 1,851,797.

You can use other methods for instantiation instead of using the data pump to export and import tables also. One such method is using the create table as a select command or RMAN. It's steps are:

1. Use create table with an at SCN of parameter, using the following command:

   SET_INSTANTIATION_CSN SCN for object from global_name

   For example:

   SET_INSTANTIATION_CSN 1 FOR u1.t1 FROM DBS1.REGRESS.RDBMS.DEV.US.ORACLE.COM

2. If you want to remove the manual setting of the instantiation CSN later, you can use the following command:

   CLEAR_INSTANTIATION_CSN for object from global_name

14.2.1.2 Using Oracle Data Pump Table Instantiation

To perform instantiation with Oracle Data Pump, see My Oracle Support document 1276058.1. To obtain this document, do the following:

1. Go to http://support.oracle.com.
2. Under Sign In, select your language and then log in with your Oracle Single Sign-On (SSO).
3. On the Dashboard, expand the Knowledge Base heading.
4. Under Enter Search Terms, paste or type the document ID of 1276058.1 and then click Search.
5. In the search results, select Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database [Article ID 1276058.1].
6. Click the link under Attachments to open the article.

14.2.2 Loading Data from File to Replicat

To use Replicat to establish the target data, you use an initial-load Extract to extract source records from the source tables and write them to an extract file in canonical format. From the file, an initial-load Replicat loads the data using the database interface. During the load, the change-synchronization groups extract and replicate incremental changes, which are then reconciled with the results of the load.

During the load, the records are applied to the target database one record at a time, so this method is considerably slower than any of the other initial load methods. This method permits data transformation to be done on either the source or target system.

Description of the illustration initsyncreplicat.jpg

You can also use the Microservices Architecture to load data from file to Replicat. See Loading Data from File to Replicat in Microservices Architecture.

To Load Data From File to Replicat

1. Make certain that you have addressed the requirements in Prerequisites for Initial Load.
2. On the source and target systems, run GGSCI and start Manager.

   START MANAGER

   Note:

   In a Windows cluster, start the Manager resource from the Cluster Administrator.

3. On the source system, issue the following command to create an initial-load Extract parameter file.

   EDIT PARAMS initial-load_Extract
   

4. Enter the parameters listed in Table 14-* in the order shown, starting a new line for each parameter statement. The following is a sample initial-load Extract parameter file for loading data from file to Replicat.

   SOURCEISTABLE
   SOURCEDB mydb, USERIDALIAS ogg
   RMTHOSTOPTIONS ny4387, MGRPORT 7888, ENCRYPT AES 192 KEYNAME mykey
   ENCRYPTTRAIL AES192
   RMTFILE /ggs/dirdat/initld, MEGABYTES 2, PURGE
   TABLE hr.*;
   TABLE sales.*;
   

   Parameter	Description
   SOURCEISTABLE	Designates Extract as an initial load process extracting records directly from the source tables.
   SOURCEDB dsn [, USERIDALIAS alias, options | , USERID user, options]	Specifies database connection information. SOURCEDB specifies the source data source name (DSN). 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.
   ENCRYPTTRAIL algorithm	Encrypts the data in the remote file.
   RMTFILE path, [MEGABYTES n] path is the relative or fully qualified name of the file. MEGABYTES designates the size of each file.	Specifies the extract file to which the load data will be written. Oracle GoldenGate creates this file during the load. Checkpoints are not maintained with RMTFILE. Note that the size of an extract file cannot exceed 2GB.
   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 or catalog 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.

5. Enter any appropriate optional Extract parameters listed in the Reference for Oracle GoldenGate.
6. Save and close the parameter file.
7. On the target system, issue the following command to create an initial-load Replicat parameter file.

   EDIT PARAMS initial-load_Replicat
   

8. Enter the parameters listed in Table 14-1 in the order shown, starting a new line for each parameter statement. The following is a sample initial-load Replicat parameter file for loading data from file to Replicat.

   SPECIALRUN
   END RUNTIME
   TARGETDB mydb, USERIDALIAS ogg
   EXTFILE /ggs/dirdat/initld
   SOURCEDEFS /ggs/dirdef/source_defs
   MAP hr.*, TARGET hr.*;
   MAP sales.*, TARGET hr.*;
   

   Table 14-1 Initial-load Replicat parameters

   Parameter	Description
   SPECIALRUN	Implements the initial-load Replicat as a one-time run that does not use checkpoints.
   END RUNTIME	Directs the initial-load Replicat to terminate when the load is finished.
   TARGETDB dsn [, USERIDALIAS alias, options | , USERID user, options]	Specifies database connection information. TARGETDB specifies the target data source name (DSN). USERID and USERIDALIAS specify database credentials if required.
   EXTFILE path path is the relative or fully qualified name of the file.	Specifies the input extract file specified with the Extract parameter RMTFILE.
   {SOURCEDEFS file} | ASSUMETARGETDEFS Use SOURCEDEFS if the source and target tables have different definitions. Specify the relative or fully qualified name of 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. Enables the use of two-part names (schema.object) where three-part names otherwise would be required for those databases. You can use multiple instances of this parameter to specify different default containers or catalogs 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 or catalog 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 or catalog 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..

9. Enter any appropriate optional Replicat parameters listed in the Reference for Oracle GoldenGate.
10. Save and close the file.
11. View the Replicat parameter file to make certain that the HANDLECOLLISIONS parameter is listed. If not, add the parameter to the file.
12. On the source system, start change extraction.

    START EXTRACT group
    

13. (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]
    

14. (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
    

15. From the directory where Oracle GoldenGate is installed on the source system, start the initial-load Extract.

    UNIX and Linux:

    $ /GGS directory/extract paramfile dirprm/initial-load_Extract.prm reportfile path
    

    Windows:

    C:\> GGS directory\extract paramfile dirprm\initial-load_Extract.prm reportfile path
    

    Where:

    initial-load_Extract is the name of the initial-load Extract that you used when creating the parameter file, and path is the relative or fully qualified name of the Extract report file.

16. Verify the progress and results of the initial extraction by viewing the Extract report file using the operating system's standard method for viewing files.
17. Wait until the initial extraction is finished.
18. On the target system, start the initial-load Replicat.

    UNIX and Linux:

    $ /GGS directory/replicat paramfile dirprm/initial-load_Replicat.prm reportfile path
    

    Windows:

    C:\> GGS directory\replicat paramfile dirprm\initial-load_Replicat.prm reportfile path
    

    Where:

    initial-load_Replicat is the name of the initial-load Replicat that you used when creating the parameter file, and path is the relative or fully qualified name of the Replicat report file.

19. When the initial-load Replicat is finished running, verify the results by viewing the Replicat report file using the operating system's standard method for viewing files.
20. On the target system, start change replication.

    START REPLICAT group
    

21. On the target system, issue the following command to verify the status of change replication.

    INFO REPLICAT group
    

22. 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.
23. 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
    

24. 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.

25. Save and close the parameter file.

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

14.2.3 Loading Data with an Oracle GoldenGate Direct Load

To use an Oracle GoldenGate direct load, 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 started dynamically by the Manager process and does not require the use of a Collector process or file. The initial-load Replicat task delivers the load in large blocks to the target database. Transformation and mapping can be done by Extract, 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.

Description of the illustration initsync_dirload.jpg

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

To Load Data with an Oracle GoldenGate Direct Load

1. Make certain to satisfy "Prerequisites for Initial Load".
2. On the source and target systems, run GGSCI and start Manager.

   START MANAGER
   

   Note:

   In a Windows cluster, start the Manager resource from the Cluster Administrator.

3. On the source, 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.

4. On the source system, issue the following command to create an initial-load Extract parameter file.

   EDIT PARAMS initial-load_Extract
   

5. Enter the parameters listed in Table 14-2 in the order shown, starting a new line for each parameter statement. The following is a sample initial-load Extract parameter file for an Oracle GoldenGate direct load.

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

   Table 14-2 Initial-load Extract Parameters for Oracle GoldenGate Direct Load

   Parameter	Description
   EXTRACT initial-load_Extract	Specifies the initial-load Extract.
   SOURCEDB dsn [, USERIDALIAS alias, options | , USERID user, options]	Specifies database connection information. SOURCEDB specifies the source datasource name (DSN). See Reference for Oracle GoldenGate for more 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 database, the object name must include the name of the container or catalog unless SOURCECATALOG is used.
   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.

6. Enter any appropriate optional Extract parameters listed in Reference for Oracle GoldenGate.
7. Save and close the file.
8. On the target system, issue the following command to create the initial-load Replicat task.

   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 run, not a continuous process.

9. On the target system, issue the following command to create an initial-load Replicat parameter file.

   EDIT PARAMS initial-load_Replicat
   

10. Enter the parameters listed in Table 14-3 in the order shown, starting a new line for each parameter statement. The following is a sample initial-load Replicat parameter file for an Oracle GoldenGate direct load.

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

    Table 14-3 Initial-load Replicat parameters for Oracle GoldenGate Direct Load

    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 dsn | container] [, USERIDALIAS alias, options | , USERID user, options]	Specifies database connection information. TARGETDB specifies the target datasource name (DSN) or Oracle container. See Reference for Oracle GoldenGate for more information. USERID and USERIDALIAS specify database credentials if required.
    {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 . Enables the use of two-part names (schema.object) where three-part names otherwise would be required for those databases. You can use multiple instances of this parameter to specify different default containers or catalogs 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 or catalog 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 or catalog 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.

11. Enter any appropriate optional Replicat parameters listed in the Reference for Oracle GoldenGate.
12. Save and close the parameter file.
13. On the source system, start change extraction.

    START EXTRACT group
    

14. View the Replicat parameter file to make certain that the HANDLECOLLISIONS parameter is listed. If not, add the parameter to the file.
15. (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]
    

16. (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
    

17. On the source system, start the initial-load Extract.

    START EXTRACT initial-load_Extract

    Note:

    Do not start the initial-load Replicat. The Manager process starts it automatically and terminates it when the load is finished.

18. On the target system, issue the following command to find out if the load is finished. Wait until the load is finished before going to the next step.

    VIEW REPORT initial-load_Replicat
    

19. On the target system, start change replication.

    START REPLICAT group
    

20. On the target system, issue the following command to verify the status of change replication.

    INFO REPLICAT group
    

21. 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.
22. 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
    

23. 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.

24. Save and close the parameter file. From this point forward, Oracle GoldenGate continues to synchronize data changes.

14.2.4 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 the illustration initsync_dirbulk.jpg

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

1. Make certain that you have addressed the requirements in "Prerequisites for Initial Load".
2. Grant LOCK ANY TABLE to the Replicat database user on the target Oracle database.
3. On the source and target systems, run GGSCI and start Manager.

   START MANAGER
   

4. 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.

5. On the source system, issue the following command to create an initial-load Extract parameter file.

   EDIT PARAMS initial-load_Extract
   

6. Enter the parameters listed in Table 14-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 14-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.

7. Enter any appropriate optional parameters.
8. Save and close the file.
9. 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.

10. On the target system, issue the following command to create an initial-load Replicat parameter file.

    EDIT PARAMS initial-load_Replicat
    

11. Enter the parameters listed in Table 14-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 14-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.

12. Enter any appropriate optional Replicat parameters listed in Reference for Oracle GoldenGate.
13. Save and close the parameter file.
14. On the source system, start change extraction.

    START EXTRACT group
    

15. View the Replicat parameter file to make certain that the HANDLECOLLISIONS parameter is listed. If not, add the parameter to the file.
16. (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]
    

17. (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
    

18. 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.

19. 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
    

20. On the target system, start change replication.

    START REPLICAT group
    

21. On the target system, issue the following command to verify the status of change replication.

    INFO REPLICAT group
    

22. 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.
23. 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
    

24. 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..

25. Save and close the parameter file.

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