16 Instantiating Oracle GoldenGate with an Initial Load
This chapter describes running an initial data load to instantiate the replication environment.
This chapter includes the following sections:
Overview of the Initial-Load Procedure
Loading Data with a Database Utility
Loading Data with Oracle Data Pump
Loading Data from File to Replicat
Loading Data from File to Database Utility
Loading Data with an Oracle GoldenGate Direct Load
Loading Data with a Direct Bulk Load to SQL*Loader
Loading Data with Teradata Load Utilities
Overview of the Initial-Load Procedure
Loading Data with a Database Utility
Loading Data with Oracle Data Pump
Loading Data from File to Replicat
Loading Data from File to Database Utility
Loading Data with an Oracle GoldenGate Direct Load
Loading Data with a Direct Bulk Load to SQL*Loader
Loading Data with Teradata Load Utilities
16.1 Overview of the Initial-Load Procedure
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.
16.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.
-
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.
-
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.
16.1.2 Prerequisites for Initial Load
Verify that you meet the prerequisites for executing an initial load that are described in the following sections.
16.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.
16.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.
16.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
for more information.
16.1.2.4 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
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.
16.1.2.5 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
for more information about using
OBEY and using macros.
16.2 Loading Data with a Database Utility
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.
-
Make certain that you have addressed the requirements in
.
-
On the source and target systems, run GGSCI and start the Manager process.
START MANAGER
Note:
In a Windows cluster, start the Manager resource from the Cluster Administrator.
-
On the source system, start change extraction.
START EXTRACT group
Where:
group is the name of the Extract group.
-
(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 making the copy.
-
Wait until the copy is finished and record the time of completion.
-
View the Replicat parameter file to make certain that the
HANDLECOLLISIONS parameter is listed. If not, add the parameter to the file.
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..
-
On the target system, start change replication.
START REPLICAT group
Where:
group is the name of the Replicat group.
-
On the target system, issue the following command to verify the status of change replication.
-
Continue to issue the
INFO REPLICAT command until you have verified that change replication has posted all of the change data that was generated during the initial load. Reference the time of completion that you recorded. For example, if the copy stopped at 12:05, make sure change replication has 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.
16.3 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. From the process start point, Oracle GoldenGate maintains data synchronization. No initial-load Oracle GoldenGate processes are required for these methods.
16.3.1 Using Automatic Per Table Instantiation
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.
-
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.
-
Import your exported tables using Oracle data pump, which populates system tables and views with instantiation SCNs, as well as the specified table data.
-
Start Replicat using one of the following:
For Metadata Trail Replicats, set the
DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter in the Replicat parameter file to enable table-level instantiation filtering.
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 more information, see the
.
16.3.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:
-
Go to
-
Under
Sign In, select your language and then log in with your Oracle Single Sign-On (SSO).
-
On the
Dashboard, expand the
Knowledge Base heading.
-
Under
Enter Search Terms, paste or type the document ID of
1276058.1 and then click
Search.
-
In the search results, select
Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database [Article ID 1276058.1].
-
Click the link under
Attachments to open the article.
16.4 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.
To Load Data From File to Replicat
-
Make certain that you have addressed the requirements in
.
-
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.
-
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
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.*;
-
Enter any appropriate optional Extract parameters listed in the
.
-
Save and close the parameter file.
-
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
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.*;
-
Enter any appropriate optional Replicat parameters listed in the
.
-
Save and close the file.
-
View the Replicat parameter file to make certain that the
HANDLECOLLISIONS parameter is listed. If not, add the parameter to the file.
-
On the source system, start change extraction.
-
(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
-
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.
-
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.
-
Wait until the initial extraction is finished.
-
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.
-
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.
-
On the target system, start change replication.
-
On the target system, issue the following command to verify the status of change replication.
-
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.
16.5 Loading Data from File to Database Utility
To use a database bulk-load utility, you use an initial-load Extract to extract source records from the source tables and write them to an extract file in external ASCII format. The file can be read by Oracle's SQL*Loader, Microsoft's BCP, DTS, or SQL Server Integration Services (SSIS) utility, or IBM's Load Utility (
LOADUTIL).
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.
During the load, the change-synchronization groups extract and replicate incremental changes, which are then reconciled with the results of the load. As part of the load procedure, Oracle GoldenGate uses the initial-load Replicat to create run and control files required by the database utility.
Any data transformation must be performed by the initial-load Extract on the source system because the control files are generated dynamically and cannot be pre-configured with transformation rules.
To Load Data From File to Database Utility
-
Make certain to satisfy
"
".
-
On the source and target systems, run GGSCI and start Manager.
-
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
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 database utility.
SOURCEISTABLE
SOURCEDB mydb, USERIDALIAS ogg
RMTHOSTOPTIONS ny4387, MGRPORT 7888, ENCRYPT AES 192 KEYNAME mykey
ENCRYPTTRAIL AES192
FORMATASCII, SQLLOADER
RMTFILE /ggs/dirdat/initld, MEGABYTES 2, PURGE
TABLE hr.*;
TABLE sales.*;
-
Enter any appropriate optional Extract parameters listed in
.
-
Save and close the parameter file.
-
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
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 database utility.
GENLOADFILES sqlldr.tpl
USERIDALIAS ogg
EXTFILE /ggs/dirdat/initld
SOURCEDEFS /ggs/dirdef/source_defs
MAP hr.*, TARGET hr.*;
MAP sales.*, TARGET hr.*;
-
Enter any appropriate optional Replicat parameters listed in
.
-
Save and close the parameter file.
-
View the Replicat parameter file to make certain that the
HANDLECOLLISIONS parameter is listed. If not, add the parameter to the file.
-
On the source system, start change extraction.
-
(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
-
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.
-
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.
-
Wait until the initial extraction is finished.
-
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.
-
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.
-
Using the ASCII-formatted extract files and the run and control files created by the initial-load Replicat, load the data with the database utility.
-
Wait until the load into the target tables is complete.
-
On the target system, start change replication.
-
On the target system, issue the following command to verify the status of change replication.
-
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.
16.6 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.
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
-
Make certain to satisfy
"
".
-
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.
-
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.
-
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
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.*;
-
Enter any appropriate optional Extract parameters listed in
.
-
Save and close the file.
-
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.
-
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
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.*;
-
Enter any appropriate optional Replicat parameters listed in
.
-
Save and close the parameter file.
-
On the source system, start change extraction.
-
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
Note:
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 find out if the load is finished. Wait until the load is finished before going to the next step.
VIEW REPORT initial-load_Replicat
-
On the target system, start change replication.
-
On the target system, issue the following command to verify the status of change replication.
-
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.
16.7 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.
To Load Data With a Direct Bulk Load to SQL*Loader
-
Make certain that you have addressed the requirements in
"
".
-
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.
-
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
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.*;
-
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
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.*;
-
Enter any appropriate optional Replicat parameters listed in
.
-
Save and close the parameter file.
-
On the source system, start change extraction.
-
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.
-
On the target system, issue the following command to verify the status of change replication.
-
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.
16.8 Loading Data with Teradata Load Utilities
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
.
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
-
Create the scripts that are required by the utility.
-
Start the primary Extract group(s).
-
Start the data pump(s), if used.
-
Open the Replicat parameter file(s) for editing.
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..
-
Add the following parameters to the Replicat parameter file(s):
END RUNTIME
HANDLECOLLISIONS
-
END RUNTIME directs Replicat to terminate normally when it reads an Oracle GoldenGate trail record that has a timestamp that is the same as, or after, the time that Replicat was started.
-
HANDLECOLLISIONS directs Replicat to overwrite duplicate records and ignore missing ones, as a means of resolving errors that occur from collisions between transactional changes and the results of the copy.
-
Save and close the Replicat parameter file(s).
-
Start the load utility.
-
When the load completes on the target, start the Replicat process(es).
-
When each Replicat process stops, remove the
HANDLECOLLISIONS and
END RUNTIME parameters from the parameter file.
-
Restart the Replicat process(es). The two databases are now synchronized, and Oracle GoldenGate will keep them current through replication.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31374736/viewspace-2871506/，如需轉載，請註明出處，否則將追究法律責任。
