Infrastructure at your Service

Daniel Westermann

Performing an initial load with GoldenGate (1) – file to replicat

The first post in this series outlined on how to install and patch Oracle GoldenGate to the latest release.The second post explained how to create a sample replication with Oracle GoldenGate:

In this post I’ll look how to do an initial load using GoldenGate by using the “File to replicat” method (several other methods are available).

To start from scratch I deleted all the configurations done in the last post.

This time the goal is to initially load all the tables of the HR schema from DB1 to DB2 and start the replication afterwards. The default HR schema contains seven tables:

SQL> select table_name from dba_tables where owner = 'HR' order by 1;

TABLE_NAME
-------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS

7 rows selected.

To have only the table definitions and no data in the HR schema I’ll delete all records:

declare
  lv_statement varchar2(2000);
begin
  for r in ( select c.CONSTRAINT_NAME, c.TABLE_NAME
               from dba_constraints c
                  , dba_tables t
              where c.owner = 'HR'
                and t.table_name = c.table_name
                and t.owner = 'HR'
                and c.constraint_type != 'P'
           )
  loop
    lv_statement := 'alter table hr.'||r.TABLE_NAME||' disable constraint '||r.CONSTRAINT_NAME;
    execute immediate lv_statement;
  end loop;
  for r in ( select table_name
               from dba_tables
              where owner = 'HR'
           )
  loop
    execute immediate 'delete hr.'||r.table_name;
  end loop;
/
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
	 0

SQL> select count(*) from hr.jobs;

  COUNT(*)
----------
	 0

Note that I’ll leave the constraints disabled to avoid any issues when GoldenGate populates the tables later.

As in the last post we need to execute the ADD TRANDATA command:

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 7> dblogin useridalias DB1 domain admin
Successfully logged into database.

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 8> add trandata HR.*

Logging of supplemental redo data enabled for table HR.COUNTRIES.
TRANDATA for scheduling columns has been added on table 'HR.COUNTRIES'.
Logging of supplemental redo data enabled for table HR.DEPARTMENTS.
TRANDATA for scheduling columns has been added on table 'HR.DEPARTMENTS'.
Logging of supplemental redo data enabled for table HR.EMPLOYEES.
TRANDATA for scheduling columns has been added on table 'HR.EMPLOYEES'.
Logging of supplemental redo data enabled for table HR.JOBS.
TRANDATA for scheduling columns has been added on table 'HR.JOBS'.
Logging of supplemental redo data enabled for table HR.JOB_HISTORY.
TRANDATA for scheduling columns has been added on table 'HR.JOB_HISTORY'.
Logging of supplemental redo data enabled for table HR.LOCATIONS.
TRANDATA for scheduling columns has been added on table 'HR.LOCATIONS'.
Logging of supplemental redo data enabled for table HR.REGIONS.
TRANDATA for scheduling columns has been added on table 'HR.REGIONS'.
GGSCI (oelgg1.it.dbi-services.com as [email protected]) 9> 

In real life environments there will probably be ongoing transactions while the initial load of the target database happens. To be able to re-synchronize these changes we need to configure change data capture.

On the source system, as usual, we’ll need an extract:

GGSCI (oelgg1.it.dbi-services.com) 3> edit params extrcdc1

The parameters are:

EXTRACT extrcdc1
USERIDALIAS DB1 domain admin
EXTTRAIL ./dirdat/gg
LOGALLSUPCOLS
UPDATERECORDFORMAT compact
TABLE hr.*;
TABLEEXCLUDE HR.EMP_DETAILS_VIEW;

The remaining steps on the source database are the same as in the last post:

GGSCI (oelgg1.it.dbi-services.com) 4> dblogin useridalias DB1 domain admin

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 5> register extract extrcdc1 database
Extract EXTRCDC1 successfully registered with database at SCN 1863433.

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 6> add extract extrcdc1, integrated tranlog, begin now
EXTRACT added.

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 7> add exttrail ./dirdat/gg, extract extrcdc1, megabytes 5
EXTTRAIL added.

Configure the datapump:

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 10> edit params dppump1

The parameters are:

EXTRACT dppump1
PASSTHRU
RMTHOST oelgg2, MGRPORT 7809
RMTTRAIL ./dirdat/jj
TABLE hr.*;
TABLEEXCLUDE HR.EMP_DETAILS_VIEW;

Start the datapump:

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 12> add extract dppump1, exttrailsource ./dirdat/gg
EXTRACT added.

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 13> add rmttrail ./dirdat/jj, extract dppump1, megabytes 5
RMTTRAIL added.

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 23> start extract *

Sending START request to MANAGER ...
EXTRACT DPPUMP1 starting

Sending START request to MANAGER ...
EXTRACT EXTRCDC1 starting

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 24> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPPUMP1     00:00:00      00:00:02    
EXTRACT     RUNNING     EXTRCDC1    00:00:07      00:00:07    

The next step if to configure the change delivery on the target system:

GGSCI (oelgg2.it.dbi-services.com as [email protected]) 26> edit params rplcdd

The parameters are:

REPLICAT replcdd
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/replccd.dsc, purge
USERIDALIAS DB2 domain admin
MAP hr.*, TARGET hr.*;

Configure the replicat:

GGSCI (oelgg2.it.dbi-services.com) 1> dblogin useridalias DB2 domain admin
Successfully logged into database.

GGSCI (oelgg2.it.dbi-services.com as [email protected]) 2> add replicat replcdd, integrated, exttrail ./dirdat/jj
REPLICAT (Integrated) added.

We will not start the replicat right now as we wan to do the initial load before.

Now it is time to get the current scn of the source database:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1909670

The next step is to create the extract process parameter file:

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 10> edit params INITLOAD

The parameters for the file are:

EXTRACT initload
SOURCEISTABLE
USERIDALIAS DB1 domain admin
RMTHOST oelgg2, MGRPORT 7809
RMTFILE ./dirdat/initld, MEGABYTES 2, PURGE
TABLE HR.*, SQLPREDICATE 'AS OF SCN 1909670';
TABLEEXCLUDE HR.EMP_DETAILS_VIEW;

Lets create the extract group:

GGSCI (oelgg1.it.dbi-services.com) 2> add extract initload, sourceistable
EXTRACT added.

The sourceistable parameter tells GoldenGate to extract the data directly from the tables for the initial load.

Now we can start the extract process:

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 30> start extract initload

Sending START request to MANAGER ...
EXTRACT INITLOAD starting

This will start the extract and stop automatically once completed. You can get a report on what happened with:

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 36> view report initload

On the target system add the replicat:

GGSCI (oelgg2.it.dbi-services.com) 3> add replicat rload1, specialrun

The parameters for rload1 are:

REPLICAT rload1	 	 
USERIDALIAS DB2 domain admin	 	 
SPECIALRUN	 	 
END RUNTIME	 	
ASSUMETARGETDEFS	 	 
EXTFILE ./dirdat/initld	 	 
MAP hr.*, TARGET hr.*;	 	 
TABLEEXCLUDE HR.EMP_DETAILS_VIEW

The “SPECIALRUN” and “END RUNTIME” tell GoldenGate that this is a one time batch task.
The “TABLEEXCLUDE” parameter excludes the View “HR.EMP_DETAILS_VIEW” as we do not want to get the view populated.

Lets see if we can load the data on the target system:

GGSCI (oelgg2.it.dbi-services.com) 3> start replicat rload1

It is advisable to tail the GoldenGate log in a separate session while the load is running. If everything is fine it should look similar to this:

...
2015-09-03 11:14:17  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.JOB_HISTORY: EMPLOYEE_ID, START_DATE.
2015-09-03 11:14:17  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Wildcard MAP resolved (entry hr.*): MAP "HR"."LOCATIONS", TARGET hr."LOCATIONS".
2015-09-03 11:14:17  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using following columns in default map by name: LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID.
2015-09-03 11:14:17  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.LOCATIONS: LOCATION_ID.
2015-09-03 11:14:17  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Wildcard MAP resolved (entry hr.*): MAP "HR"."REGIONS", TARGET hr."REGIONS".
2015-09-03 11:14:18  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using following columns in default map by name: REGION_ID, REGION_NAME.
2015-09-03 11:14:18  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.REGIONS: REGION_ID.
2015-09-03 11:14:18  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, rload1.prm:  REPLICAT RLOAD1 stopped normally.

Lets check if the data is really there:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

Looks fine. The data is now available up to SCN 1909670. Now we need to make sure that the data after this SCN will be synchronized. Before starting the synchronization lets update some data in the source database:

SQL> update hr.countries set COUNTRY_NAME = 'Zimbabwe2' where COUNTRY_NAME = 'Zimbabwe';

1 row updated.

SQL> commit;

Commit complete.

Time to start the replicat on the target database:

GGSCI (oelgg2.it.dbi-services.com as [email protected]) 18> start replicat replcdd, aftercsn 1909670

Sending START request to MANAGER ...
REPLICAT REPLCDD starting

GGSCI (oelgg2.it.dbi-services.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPLCDD     00:00:00      00:00:06    

If everything works as expected we should see the row we updated just before:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

SQL> select * from hr.countries where COUNTRY_NAME like '%Zimbabwe%';

CO COUNTRY_NAME 			     REGION_ID
-- ---------------------------------------- ----------
ZW Zimbabwe2					     4

Works like a charm. In the next post I’ll look into how to do the same but populating the target database with expdp/impdp instead of the GoldenGate file to replicat method.

4 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure