Oracle GoldenGate – Initial Load – File to Database Utility Method

INITIAL LOAD FILE TO DATABASE UTILITY
SOURCE DATABASE VERSION  11.2.0.3
TARGET DATABASE VERSION  11.2.0.4
ORACLE GOLDEN GATE VERSION  12.1.2.0.0
SOURCE DATABASE  GGDB1
TARGET DATABASE  GGDB2
SCHEMA USED  VEERA
TABLE NAME  MYTABLE

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,

./RUN_FILE.run

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

SQL> select count(1) from mytable;
COUNT(1)

———
0SQL> desc mytable
Name          Null?             Type
—————  ——————– —————————
ID               NOT NULL     NUMBER

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

 

ON SOURCE

[oracle@ogg1 ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 11 12:08:20 2015Copyright (c) 1982, 2011, Oracle. All rights reserved.SQL> conn veera/laser@ggdb1
Connected.
SQL> select * from tab;TNAME     TABTYPE  CLUSTERID
————–  ————— ——————
MYTABLE TABLESQL> select count(1) from mytable;
COUNT(1)
—————
10000000

Create the Initial Load Extract Process.

ADD EXTRACT EXTLOAD, SOURCEISTABLE
GGSCI (ogg1.localdomain) 6> ADD EXTRACT EXTLOAD, SOURCEISTABLE
EXTRACT added.
NOTE: Since it is a one time extract process, the data is extracted from the Source DATA and not from the Transaction Logs(Redo Logs or Archive logs). The Extract Process automatically understands that this is a Initial Load Process since the keyword SOURCEISTABLE is used.

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

EXTRACT EXTLOAD
SOURCEISTABLE
USERID ggadmin, password oracle
RMTHOST OGG2, MGRPORT 7809
FORMATASCII, SQLLOADER
RMTFILE /ogg/dirdat/MYTABLE.DAT
TABLE veera.MYTABLE;

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

FORMATASCII, DELIMITER ‘,’, NONAMES, PLACEHOLDERSNONAME – Excludes the column names
PLACEHOLDERS – Outputs a placeholder for a missing column.
GGSCI (ogg1.localdomain) 7> EDIT PARAMS EXTLOAD

Configure the parameters and save the file.

GGSCI (ogg1.localdomain) 8> VIEW PARAMS EXTLOADEXTRACT EXTLOAD
SOURCEISTABLE
USERID ggadmin, password oracle
RMTHOST OGG2, MGRPORT 7809
FORMATASCII, SQLLOADER
RMTFILE /ogg/dirdat/MYTABLE.DAT
TABLE veera.MYTABLE;

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

2015-01-18 16:04:12 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT EXTLOAD SOURCEISTABLE.
2015-01-18 16:05:10 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS EXTLOAD.

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,

GGSCI (ogg1.localdomain) 11> INFO ALL TASKSProgram Status Group Lag at Chkpt Time Since ChkptEXTRACT STOPPED EXTLOAD

 

ON TARGET

Create the Initial Replicat Process,

ADD REPLICAT REPLOAD
GGSCI (ogg2.localdomain) 3> ADD REPLICAT REPLOAD
REPLICAT added.

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

GGSCI (ogg2.localdomain) 4> EDIT PARAMS REPLOADREPLICAT REPLOAD
GENLOADFILES sqlldr.tpl
USERID ggadmin, password oracle
EXTFILE /ogg/dirdat/MYTABLE.DAT
ASSUMETARGETDEFS
MAP veera.MYTABLE, TARGET veera.MYTABLE;

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

Note: RUN and CONTROL files are already explained at starting itself. Please refer it.

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,

GGSCI (ogg2.localdomain) 5> VIEW PARAMS REPLOAD
REPLICAT REPLOAD
GENLOADFILES sqlldr.tpl
USERID ggadmin, password oracle
EXTFILE /ogg/dirdat/MYTABLE.DAT
ASSUMETARGETDEFS
MAP veera.MYTABLE, TARGET veera.MYTABLE;

GGSCI (ogg2.localdomain) 6> INFO ALL TASKSProgram Status Group Lag at Chkpt Time Since ChkptREPLICAT STOPPED REPLOAD

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

2015-01-18 16:27:03 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD REPLICAT REPLOAD.
2015-01-18 16:28:43 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS REPLOAD.

ON SOURCE

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

GGSCI (ogg1.localdomain) 14> START EXTRACT EXTLOADSending START request to MANAGER …
EXTRACT EXTLOAD starting

GGSCI (ogg1.localdomain) 15> info all tasksProgram Status Group Lag at Chkpt Time Since ChkptEXTRACT STARTING EXTLOAD

GGSCI (ogg1.localdomain) 32> info all tasksProgram Status Group Lag at Chkpt Time Since ChkptEXTRACT RUNNING EXTLOAD

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

GGSCI (ogg1.localdomain) 12> info all tasksProgram Status Group Lag at Chkpt Time Since ChkptEXTRACT STOPPED EXTLOAD

The below is registered in the ggserr.log,

2015-01-18 17:20:26 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, extload.prm: EXTRACT EXTLOAD started.
2015-01-18 17:20:26 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, extload.prm: Output file /ogg/dirdat/MYTABLE.DAT is using format ASCII.
2015-01-18 17:20:31 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, extload.prm: Socket buffer size set to 27985 (flush size 27985).
2015-01-18 17:20:57 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, extload.prm: EXTRACT EXTLOAD stopped normally.

The below is the report of EXTLOAD for reference,

2015-01-18 17:20:26 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:44:16Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. Starting at 2015-01-18 17:20:26
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri Feb 22 18:16:18 PST 2013, Release 2.6.39-400.17.1.el6uek.x86_64
Node: ogg1.localdomain
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 3279

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2015-01-18 17:20:26 INFO OGG-03059 Operating system character set identified as UTF-8.

2015-01-18 17:20:26 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT EXTLOAD
SOURCEISTABLE

2015-01-18 17:20:26 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
USERID ggadmin, password ******

2015-01-18 17:20:26 INFO OGG-03522 Setting session time zone to source database time zone ‘GMT’.
RMTHOST OGG2, MGRPORT 7809
FORMATASCII, SQLLOADER
RMTFILE /ogg/dirdat/MYTABLE.DAT
TABLE veera.MYTABLE;
Using the following key columns for source table VEERA.MYTABLE: ID.

2015-01-18 17:20:26 INFO OGG-01851 filecaching started: thread ID: 140325305710336.

2015-01-18 17:20:26 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/ogg/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 64G
CACHEPAGEOUTSIZE (default): 8M
PROCESS VM AVAIL FROM OS (min): 128G
CACHESIZEMAX (strict force to disk): 96G

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

Database Language and Character Set:
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “WE8MSWIN1252”

2015-01-18 17:20:26 INFO OGG-01478 Output file /ogg/dirdat/MYTABLE.DAT is using format ASCII.

2015-01-18 17:20:31 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).

Processing table VEERA.MYTABLE

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2015-01-18 17:20:57 (activity since 2015-01-18 17:20:26)

Output to /ogg/dirdat/MYTABLE.DAT:

From Table VEERA.MYTABLE:
# inserts: 10000000
# updates: 0
# deletes: 0
# discards: 0

REDO Log Statistics
Bytes parsed 0
Bytes output 540000000

 

ON TARGET

Start the Initial Load Replicat,

GGSCI (ogg2.localdomain) 5> start reploadSending START request to MANAGER …
REPLICAT REPLOAD starting

GGSCI (ogg2.localdomain) 6> info all tasksProgram Status Group Lag at Chkpt Time Since ChkptREPLICAT STOPPED REPLOAD

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

[oracle@ogg2 ~]$ cd /ogg/dirdat
[oracle@ogg2 dirdat]$ ls -lart
-rw-r—– 1 oracle oinstall 540000000 Jan 18 17:20 MYTABLE.dat
-rw-r–r– 1 oracle oinstall 1727 Jan 18 17:25 MYTABLE.log
-rwxr—– 1 oracle oinstall 69 Jan 18 18:46 MYTABLE.run
-rw-r—– 1 oracle oinstall 174 Jan 18 18:46 MYTABLE.ctl
drwxrwxr-x. 4 oracle oinstall 4096 Jan 18 18:47 .

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

[oracle@ogg2 vol1]$ cat MYTABLE.run
sqlldr userid=veera/laser control=MYTABLE log=MYTABLE direct=true
[oracle@ogg2 vol1]$
[oracle@ogg2 vol1]$ cat MYTABLE.ctl
unrecoverable
load data
infile MYTABLE.dat
truncate
into table MYTABLE
(
ID position(4:53)
defaultif (3)=’Y’
)

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

2015-01-18 17:21:45 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host [192.168.1.12]:19246 (START REPLICAT REPLOAD ).
2015-01-18 17:21:45 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT REPLOAD starting.
2015-01-18 17:21:45 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, repload.prm: REPLICAT REPLOAD starting.
2015-01-18 17:21:45 INFO OGG-03059 Oracle GoldenGate Delivery for Oracle, repload.prm: Operating system character set identified as UTF-8.
2015-01-18 17:21:45 INFO OGG-02695 Oracle GoldenGate Delivery for Oracle, repload.prm: ANSI SQL parameter syntax is used for parameter parsing.
2015-01-18 17:21:45 INFO OGG-06451 Oracle GoldenGate Delivery for Oracle, repload.prm: Triggers will be suppressed by default.

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

[oracle@ogg2 ogg]$ cat MYTABLE.run
sqlldr userid=ggadmin/oracle control=MYTABLE log=MYTABLE direct=true
[oracle@ogg2 ogg]$ sqlldr userid=ggadmin/oracle control=MYTABLE log=MYTABLE direct=trueSQL*Loader: Release 11.2.0.4.0 – Production on Sun Jan 18 17:15:09 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.SQL*Loader-941: Error during describe of table MYTABLE
ORA-04043: object MYTABLE does not exist

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

[oracle@ogg2 vol1]$ cat MYTABLE.ctl
unrecoverable
load data
infile MYTABLE.dat
truncate
into table veera.MYTABLE
(
ID position(4:53)
defaultif (3)=’Y’
)

[oracle@ogg2 vol1]$ ./MYTABLE.runSQL*Loader: Release 11.2.0.4.0 – Production on Sun Jan 18 19:00:56 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Load completed – logical record count 10000000.

[oracle@ogg2 ogg]$ sqlplus /nologSQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 18 17:25:05 2015Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> conn veera/laser@ggdb2
Connected.
SQL> select count(1) from mytable;

COUNT(1)
———-
0

SQL> select count(1) from mytable;

COUNT(1)
———-
0

SQL> select count(1) from mytable;

COUNT(1)
———-
10000000

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

GGSCI (ogg2.localdomain) 10> view report repload***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:50:41Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. Starting at 2015-01-18 18:46:04
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri Feb 22 18:16:18 PST 2013, Release 2.6.39-400.17.1.el6uek.x86_64
Node: ogg2.localdomain
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 4354

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2015-01-18 18:46:04 INFO OGG-03059 Operating system character set identified as UTF-8.

2015-01-18 18:46:04 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
REPLICAT REPLOAD
GENLOADFILES sqlldr.tpl
USERID ggadmin, password ******
EXTFILE /ogg/dirdat/MYTABLE.DAT
ASSUMETARGETDEFS
MAP veera.MYTABLE, TARGET veera.MYTABLE;
Using following columns in default map by name:
ID

File created for loader initiation: MYTABLE.run
File created for loader control: MYTABLE.ctl

2015-01-18 18:46:04 INFO OGG-06451 Triggers will be suppressed by default.
Load files generated successfully.

Note – The GGSCI prompt serial number will not be in sequence since many spelling and errors occured which I have not pasted here.

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.

 

 

 

 

 

 

 

 

 

 

 

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *