3 Configuring Initial Data Synchronization

You can configure this Extract process to read change records from a TMF audit trail, log trail, or flat file, and process them to an Oracle GoldenGate trail. This step is necessary before doing the initial load because it extracts and delivers ongoing changes to the source database, preserving data integrity for your business operations.

Instructions for configuring and running Extract can be found in "Planning the Configuration".

1. Create one Extract parameter file to read directly from each source database.
2. Use the NonStop text editor to set up an Extract parameter file to include the following information.

   • The SOURCEISFILE parameter to indicate that data should be retrieved directly from the table.

   • The format for the target file, usually FORMATASCII (for example, FORMATASCII, SQLLOADER or FORMATASCII, BCP).

   • If you are transmitting data to a system other than NonStop, or to a NonStop system over TCP/IP, include the name of the remote TCP/IP host and port number of the remote Collector.

   • The name of the local output file (EXTFILE) or the remote file (RMTFILE) to which the Extract program writes extract information. If you need to write to a series of trails, then add the MAXFILES 2 to the remote trail's parameter file. MAXFILES will append a six-digit trail sequence to the remote trail's file name.

   • The name of the file or table to extract (FILE or TABLE parameter).

   • Other optional parameters, including clauses for selecting records, column mapping, or data conversion.

3. Configure Replicat as a batch task, specifying the SPECIALRUN and BEGIN and END parameters.
4. Start the initial data load:

   TACL> RUN EXTRACT /IN parameter_file/
   TACL> RUN REPLICAT /IN parameter_file/
   

1. Configure Replicat on the target system, including HANDLECOLLISIONS and END in the parameter file. The END parameter is the time recorded for the completion of the Extract process.
2. Start Replicat with the START REPLICAT command.
3. When Replicat stops, remove the HANDLECOLLISIONS and END parameters.
4. Start Replicat for incremental data synchronization.

1. Define an Extract group:

   GGSCI> ADD EXTRACT group_name, SOURCEISFILE
   

2. Define a Replicat group:

   GGSCI> ADD REPLICAT group_name, SPECIALRUN
   

   Replicat is automatically started by Manager, at Extract's request.

3. Create the parameter files.

   For the Extract parameter file:

   EXTRACT INITEXT
   RMTHOST targethost, MGRPORT 7809
   RMTTASK REPLICAT, GROUP INITREP
   TABLE $DATA.MASTER.ACCOUNT, AUTOTRUNCATE;
   TABLE $DATA.MASTER.PRODUCT, AUTOTRUNCATE;
   TABLE $DATA.MASTER.CUSTOMER AUTOTRUNCATE;
   

   • AUTOTRUNCATE sends a PURGEDATA command to Replicat before any data is processed. This ensures the target is clean and ready to receive data.

     Note:

     Use AUTOTRUNCATE with extreme caution, as it causes all existing data to be purged from the target file. Refer to Reference for Oracle GoldenGate on HP NonStop Guardian for more information.

   • RMTHOST establishes the remote TCP/IP host and port number.

   • RMTTASK instructs Manager on the target system to start Replicat with the specified GROUP name.

   • The TABLE parameters identify the source tables.

   • Specify SOURCEISFILE in the parameter if you want to include a SOURCEISFILE option:

     • SELECTVIEW: Selects data from a specified SQL view in the file parameter. Without SELECTVIEW, Extract selects data from the base table of the view, then maps the base table columns to the view columns (This also occurs when processing audit trails and a view is specified.)

     • FASTUNLOAD: Processes the file or table several times faster than the default method. Records are written out in random order, rather than primary key order. FASTUNLOAD has no effect when an SQL view is specified. The file parameter option PARTITIONS can restrict the data retrieved to a certain subset of the file or table

     • FASTUNLOADSHARED: Allows a shared open of the source file or table. Use this only on files that are not receiving updates at the same time data is being extracted.

     For the Replicat parameter file:

     REPLICAT INITREP
     USERID GoldenUser, PASSWORD pass
     SOURCEDEFS $DATA.DIRDEF.SRCDEF
     MAP $DATA.MASTER.ACCOUNT, TARGET $DATA3.MASTER.ACCOUNT;
     MAP $DATA.MASTER.PRODUCT, TARGET $DATA3.MASTER.PRODUCT;
     MAP $DATA.MASTER.CUSTOMER, TARGET $DATA3.MASTER.CUSTOMER;
     

     In the above example:

     • USERID and PASSWORD are required to access the target database.

     • SOURCEDEFS identifies the file containing the source data definitions.

     • The MAP parameters map the source tables to the target tables, based on the data definitions in SOURCEDEFS.

4. Start Extract:

   GGSCI> START EXTRACT INITEXT
   

Wildcards can be used for the FILE and TABLE statements in direct load parameter files, but not for views.

Refer back to the example of an Extract group added with the SOURCEISFILE parameter in "To run direct load:". If the ACCOUNT, PRODUCT and CUSTOMER files are the only files on $DATA.MASTER, the Extract parameters could be changed to use wildcards. This use of wildcards is shown in the following direct load parameter file:

EXTRACT INITEXT
RMTHOST targethost, MGRPORT 7809
RMTTASK REPLICAT, GROUP INITREP
TABLE $DATA.MASTER.*, AUTOTRUNCATE;

1. Define an Extract group:

   GGSCI> ADD EXTRACT group_name, SOURCEISFILE
   

2. Define a Replicat group:

   GGSCI> ADD REPLICAT group_name, SPECIALRUN
   

   Replicat is automatically started by Manager, at Extract's request.

3. Create the Extract and Replicat parameter files.

   Following are the examples of sample direct bulk load parameter files.

   Sample Extract parameter file:

   EXTRACT INITEXT
   RMTHOST targethost, MGRPORT 7809
   RMTTASK REPLICAT, GROUP INITREP
   TABLE $DATA.MASTER.ACCOUNT;
   TABLE $DATA.MASTER.PRODUCT;
   TABLE $DATA.MASTER.CUSTOMER;
   

   • RMTHOST establishes the remote TCP/IP host and port number.

   • RMTTASK instructs Manager on the target system to start Replicat with the specified group name.

   • The TABLE parameters identify the source tables.

   Sample Replicat parameter file:

   REPLICAT INITREP
   USERID GoldenUser, PASSWORD pass
   BULKLOAD
   SOURCEDEFS /GGS/DIRDEF/SRCDEF
   MAP $DATA.MASTER.ACCOUNT, TARGET master.account;
   MAP $DATA.MASTER.PRODUCT, TARGET master.product;
   MAP $DATA.MASTER.CUSTOMER, TARGET master.customer;
   

   • USERID and PASSWORD are required to access the target database.

   • BULKLOAD tells Replicat that SQL*Loader will load the target tables.

   • SOURCEDEFS identifies the file containing the source data definitions.

   • The MAP parameters map the source tables to the target tables, based on the data definitions in SOURCEDEFS.

4. Start Extract:

   GGSCI> START EXTRACT group_name
   

Use ALTINPUT for direct file extraction. With ACI files, multiple files can be in use at one time. For example, processing can continue for Monday's file after midnight, while Tuesday's file is opened for new data. To handle a multiple file situation, run more than one Extract process for the file sequence. Use the ALTINPUT RANGE option to distribute the files across the processes so that Extract never processes two files in sequence. You can also use ALTINPUT to specify the access mode of the file open, and to move Extract to the next sequential file if an application has a file open that it is not updating.

By default, Extract looks for the next alphabetical file name. The file name must conform to a template for the file type, which defaults to predefined characteristics. You can also specify the template by parameter.

If the file type is ACITLF or ACIPTLF, the template is in the form $VOL.SUBVOL.XXYYMMDD, where XX is a two character prefix, followed by a six digit date.

If the file type is ACITLFX or ACIPTLFX, the template is in the form $VOL.SUBVOL.XMMDDNNN, where X is a one character prefix, followed by a month, day and three digit sequence number.

When specifying any of the ACI file types in the FILETYPE option, do not include the date or sequence number. The next file is the one following the current file in name order, and must also satisfy any RANGE criteria in the ALTINPUT parameter.

If the file type is ENTRY, you specify the template in the ALTINPUT parameter TEMPLATE option. NonStop wildcards are acceptable. For example, the template $DATA*.MYDAT.FL* processes files starting with FL residing on different $Data volumes.

When using FILETYPE ENTRY, specify the first file to process, not the file prefix. By default, the next file is the next file name to fit the template. As an alternative, you can use FILETYPE USENEXTMODIFIED. This option selects the next file modified after the current file that also fits the template.

Before moving to the next file in a sequence, Extract must process the entire contents of the current file. By default, Extract uses the following rules to determine that the current file has been exhausted and the next file is ready for processing.

• End-of-file was reached in current file at least five seconds earlier, and no new data has appeared since.

• No processes have the current file open for write access.

• The next file exists and has at least one record in it.

• The next file was modified after the current file.

You can modify these rules with the NOWAITNEXTMODIFIED, WAITNEXTRBA, and OPENTIMEOUT options for the ALTINPUT parameter.