The initial load can be done in Classic Architecture and in Microservices Architecture.
You can use Oracle GoldenGate to:
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 GoldenGate.
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.
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.
Verify that you meet the prerequisites for executing an initial load that are described in the following sections.
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.
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.
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.
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. See Associating Replicated Data with Metadata for more information.
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.
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.
In Classic Architecture you can load data using various options. The processes and steps do so, are described in this topic.
To use a database copy utility to establish the target data, you start a change-synchronization Extract group to extract ongoing data changes while the database utility makes and applies a static copy of the data. When the copy is finished, you start the change-synchronization Replicat group to re-synchronize rows that were changed while the copy was being applied. From that point forward, both Extract and Replicat continue running to maintain data synchronization. This method does not involve any special initial-load Extract or Replicat processes.
Note:
The objects and data types being loaded in this method must be supported by Oracle GoldenGate for your database and also by the database utility that is being used. For items that are supported for your database, see the Oracle GoldenGate installation and configuration documentation for that database. For items that are supported by the database utility, see the database vendor's documentation.
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. From the process start point, Oracle GoldenGate maintains data synchronization. No initial-load Oracle GoldenGate processes are required for these methods.
You can automatically instantiate per table CSN filtering for Oracle Database with Oracle Data Pump, which avoids having all of your the tables at same SCN.
On the Source Database
Use ADD TRANDATA
and ADD SCHEMATRANDATA
to automatically prepare your tables.
Use INFO TRANDATA
to make sure that your table is prepared for instantiation and at what point it was done.
Stop Replicat on the target database.
Start Extract with the correct TABLE
statement.
EXPORT
your tables using Oracle data pump, which automatically generates import actions to set instantiation SCN at the target upon import.
On the Target Database
For more information, see the Reference for Oracle GoldenGate for Windows and UNIX.
To perform instantiation with Oracle Data Pump, see My Oracle Support document 1276058.1. To obtain this document, do the following:
1276058.1
and then click Search.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.
To Load Data From File to Replicat
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.
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
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.
To Load Data With a Direct Bulk Load to SQL*Loader
The preferred methods for synchronizing two Teradata databases is to use any of the Teradata data load utilities. The recommended utility is MultiLoad.
This procedure requires Extract and Replicat change-synchronization groups to be available and properly configured for Teradata replication. For more information, see Configuring Online Change Synchronization.
If you are using multiple Extract and Replicat groups, perform each step for all of them as appropriate.
To Load Data With a Teradata Load Utility