Oracle GoldenGate – Initial Load – File to Database Utility Method

In this method, the Initial Load extract process extracts the records from the Source tables and writes them to an extract file in ASCII format. This file can be read by the Oracle’s SQL*Loader, Microsoft’s BCP, DTS or SQL Server Integration Services (SSIS) utility etc.

One thing should be taken in to considerations. During this Initial Load, you can also enable the Change Synchronization Extract and Datapump process to record the Online or Incremental changes which happens during the Initial Load and later it can be merged or reconciled with the results of the Initial Load.

In this File to Database Utility Initial Load technique, Oracle GoldenGate uses the initial-load Replicat to create run and control files required by the database utility.

CONTROLFILES – This parameter contains the load parameters based upon the templates. In this File to Database Utility Oracle GoldenGate uses the default templates for SQL*Loader, BCP, SQL Server Integration Services (SSIS) etc., It is also possible to modify the templates according to our requirement or new templates can also be created.

RUN FILES – The RUN file contains the input parameters for starting the Initial Load. In Unix Environment, we can run the file as below,

ON TARGET

As I mentioned in my earlier postings, For starting the Initial Load, The structure of the tables should be exported from the Source and Imported to the Target database.
This can be done using the EXPDP / IMPDP utility available in the Oracle.

So in this example I have already exported the Schema VEERA from Source and IMPORTED to the Target System (only the structures).

So in Target Table there are no rows inserted. Only the Structure is created.

ON SOURCE

Create the Initial Load Extract Process.

Configure the Extract process for the Initial Load. The parameters are below,

SOURCEISTABLE – This parameter insists GoldenGate Extract process to extract the data directly from the Source Tables instead from redo logs.

RMTFILE – This is the Extract file on the remote system. The extract process writes the extracted data to this file where then the Replicat process reads these files and applies to the Target system.

FORMATASCII SQLLOADER or BCP – This parameter directs the output to be formatted as ASCII text format. instead of putting it in the usual GoldenGate Canonical format. This parameter with SQLLOADER option produces ASCII format files. In this method the SQL*Loader controlfiles are created dynamically on the target database to load the data.

The parameter FORMATASCII should be placed before the parameter RMTFILE, else the process ignores the parameter FORMATASCII and the usual canonical trail files will be created.We can also use the Delimiters as an option with the FORMATASCII parameterif we need to separate the contents with a comma delimited format. The below is the example of how to specify the parameter with the Delimiter option.,

Configure the parameters and save the file.

In the ggserr.log file you could see the below.,

To see the Normal or Regular Extract, Datapump or Replicat processes the command is INFO ALL. But in this command the Extract or Replicat processes which are configured for INITIAL LOAD will not be displayed.

To display INITIAL LOAD Processes,

ON TARGET

Create the Initial Replicat Process,

Configure the Replicat process for the Initial Load. The parameters are below,

GENLOADFILES – This parameter generates the RUN and CONTROL files for the database utility dynamically.

EXTFILE – The Filename present in the Remote system with location. This contains the data which are written by the Extract process for Synchronization of the Target database. Here the EXTFILE generates the DAT file to be processed by the SQL*Loader to load the data to the Target Database.

ASSUMETARGETDEFS – This parameter tells the Replicat Process that the Structure of the Source Tables are same as the Target Table.

To view the Parameters,

In the ggserr.log file you could see the below,

ON SOURCE

Now Start the Initial Load Extract Process from the SOURCE SYSTEM,

Wait until the Initial Extraction completes. Once it is finished, start the Initial Load Replicat which is on the Target System.

The below is registered in the ggserr.log,

The below is the report of EXTLOAD for reference,

ON TARGET

Start the Initial Load Replicat,

Here Replicat only creates the RUN and CONTROL files. So it runs for nano seconds and stops normally, since it is only for one small table.

We can see the run and control files created in the Target system from where the data is loaded to the Target table by the SQL*Loader utility. Now manually we have to execute the sql*loader script which nothing but the “MYTABLE.run”.

The below shows the contents of the run and control files,

In ggserr.log file you could see the below.,

When I tried to execute the MYTABLE.run file I ended up with the below error.,

Later I came to know that the error was due to the username, the loader was trying to load the table as “ggadmin” user and the owner of the table was “veera” which was not mentioned in the controlfile.
I changed it in the controlfile to veera.MYTABLE (earlier it was without owner name).

The below is the report of the Replicat Parameter REPLOAD taken for reference,

As I mentioned earlier in this post, we should start the Change Synchronization Extract processes before starting the Initial Load Extract process. After the Initial Load is completed, you can start the Change Synchronization Replicat process to apply the Incremental Changes to the Target Database.