Configuration of Oracle Golden Gate for Initial Load

Configuration of Oracle Golden Gate for Initial Load

Inital load is a process of extracting data records from the source database and loading those records onto the target database. Initial load is a data migration process that is performed only once.

In my previous blog, How to install and configure Oracle Golden Gate – Initial Setup I covered the basics of installation and setup of Oracle Golden Gate 12c on both source and target Oracle database 12c servers.
In this blog, I continue with the same server topology and I will illustrate a very simple example of initial load.

A. Topology

As in my previous blog I have got the same topology:

  • Oracle VirtualBox 5.0.2
  • Oracle Linux 6.7
  • Oracle Database 12c – 12.1.0.2
  • Oracle Golden Gate 12c – 12.1.2.1

The source database is srcdb and the target database is stgdb.

By now I consider only the databases: dbsrc and stgdb. The database dbtgt will be used in a future blog about Oracle Data Integrator.

B.- Preparing the source and target databases

1. On source and target databases we create an user and a table owned by this user.

Log on source and target databases as sys or system and create the user acando:

create tablespace acando_data logging
datafile '/db0/oracle/oradata/dbsrc/acando_data_01.dbf'
size 10M reuse autoextend on next 5M maxsize unlimited
extent management local
segment space management auto;

Here I have changed the datafile to '/db0/oracle/oradata/stgdb/acando_data_01.dbf'
on the target database.

create user acando identified by acando
default tablespace acando_data
temporary tablespace temp;

grant dba to acando;

2. Now log on source and target databases as acando and create a table:

create table ogg_table
( id number
, str varchar2(20)
);
alter table ogg_table add constraint t_pk primary key(id);

Now on source database as acando populate the table ogg_table with 1000 rows:

begin
  for i in 1..1000 loop
    insert into ogg_table(id,str)
    values(i, dbms_random.string('U',10));
    if mod(i, 1000) = 0 then
      commit;
    end if;
  end loop;
end;
/

In a more complex situation, with several large tables having foreign keys, triggers, check constraints and indexes, it’s important to disable those on target database tables to prevent errors and to speed up performance.

I don’t populate the table ogg_table on target database. The table will be populated by Oracle Golden Gate to demonstrate the initial load.

At this point we have:

  • Source database dbsrc – 1000 rows on table acando.ogg_table
  • Target database stgdb – 0 rows on table acando.ogg_table

C. Preparing Oracle Golden Gate for initial load

1. We make sure the source and target servers can see each other on the network. We check out the /etc/hosts file on both servers and we test:

     a. On source server: $ ping tgtdb
     b. On target server: $ ping srcdb
If the ping fails then check out the network configuration on the servers.

2. On source server we create the initial data extract process.

Log on source server as oracle and go to Oracle Golde Gate directory and start golden gate:

$ ./ggsci

Check out the manager process. If it’s down then start it up:

ggsci > info mgr
ggsci > start mgr

Then we create the process:

ggsci> add extract load1, sourceistable

Note the parameter sourceistable is used above. This is an initial load and the data is extracted once. At this point there are no transactions performed on this table. The data is extracted directly from the table, not from the redo logs.

3. On source server we create a parameter file for the extract process load1.

ggsci > edit params load1

then we add the following parameters:

extract load1
userid ogg_user, password oracle
rmthost tgtdb, mgrport 7809
rmttask replicat, group load2
table acando.ogg_table;

Where:

  • extract is the extract process created previously: load1
  • userid/password of the Oracle Golden Gate user on source database: ogg_user/oracle
  • rmthost/mgrport is the name of the target server/port number 
  • rmttask is the process of loading the data on target server: load2
  • table is the table we created to be processed

Save the parameter file created above.

4. On target server we create the initial load process load2.

Log on target server tgtdb as oracle and create the process for loading the data:

$ ./ggsci

ggsci > info mgr
if manager is down we start it up.

ggsci > add replicat load2, specialrun
the parameter specialrun is used here since the process is one time loading.

5. On target server we create the parameter file for the loading process load2.

ggsci > edit params load2

Then we add the following parameters:

replicat load2
userid ogg_user, password oracle
assumetargetdefs
map acando.ogg_table, target acando.ogg_table;

Where:

  • extract is the loading process created previously: load2
  • userid/password of the Oracle Golden Gate user on target database: ogg_user/oracle
  • assumetargetdefs: The source table structure matches the target table structure
  • map: To indicate that the name of table on target may be different from source

Save the parameter file created above.

Note that on both servers: source and target the parameter files created above reside on folder:
$ <ORACLE_GOLDEN_GATE_HOME>/dirprm
In this case:
$ /db0/oracle/product/12.1.2.1/ogg_1/dirprm

6. Start the extract and loading processes.

Remember that the table acando.ogg_table contains 1000 rows on source database and the same table is empty on target database.
After we start the extract/loading processes we expect that the table acando.ogg_table will contain 1000 rows on target database.

We start the extract process load1 on source server. The loading process or replicat load2 does not need to be started up manually. It should be started up automatically as the extract process load1 is started up.

Log on source server as oracle and run the Oracle Golden Gate interface:

$ ./ggsci

ggsci > start extract load1

Sending START request to MANAGER ...
EXTRACT LOAD1 starting

ggsci > info extract load1

EXTRACT    LOAD1     Last Started 2015-09-02 12:43   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table ACANDO.OGG_TABLE
                     2015-09-02 12:43:15  Record 1000
Task                 SOURCEISTABLE

Then we check the result on target database stgdb.
Log on target database as user acando and run:

select * from ogg_table order by id;

1 000 rows selected

7. Check out the errors log file in order to get familiarized with OGG:

Log on source server as oracle. Go to Oracle Golden Gate Home:

$ cd $ORACLE_GG_HOME

$ ls –ltr

$ more ggserr.log

Then log on target server and check out the error log file on the OGG home:

Blog coming up: About change replication using Oracle Golden Gate.  This is for instance a table is applied some DML operations on source database.  Then the same changes are reflected on the target database.

Sources:

*Photo: "GoldenGateBridge-001" by Rich Niewiroski Jr. - http://www.projectrich.com/gallery. Licensed under CC BY 2.5 via Wikimedia Commons

 

Om bloggeren:
Gabriel is currently working as Senior Consultant at Acando Norway.Gabriel has a Master of Science from the University in Oslo and he has more than 17 years of experience with Oracle technology, specially with Oracle database and development tools.»

comments powered by Disqus