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:
- Installing and patching Oracle GoldenGate 12c to the latest release
- Setting up a sample replication with 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 ggadmin@DB1) 7> dblogin useridalias DB1 domain admin Successfully logged into database. GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 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 ggadmin@DB1) 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 ggadmin@DB1) 5> register extract extrcdc1 database Extract EXTRCDC1 successfully registered with database at SCN 1863433. GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 6> add extract extrcdc1, integrated tranlog, begin now EXTRACT added. GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 7> add exttrail ./dirdat/gg, extract extrcdc1, megabytes 5 EXTTRAIL added.
Configure the datapump:
GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 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 ggadmin@DB1) 12> add extract dppump1, exttrailsource ./dirdat/gg EXTRACT added. GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 13> add rmttrail ./dirdat/jj, extract dppump1, megabytes 5 RMTTRAIL added. GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 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 ggadmin@DB1) 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 ggadmin@DB2) 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 ggadmin@DB2) 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 ggadmin@DB1) 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 ggadmin@DB1) 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 ggadmin@DB1) 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 ggadmin@DB2) 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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)