Performing an initial load with GoldenGate (2) – expdp/impdp

The last post in this series outlined on how to do an initial load of the target database using the “file to replicat” method:

• Installing and patching Oracle GoldenGate 12c to the latest release
• Setting up a sample replication with GoldenGate
• Performing an initial load with GoldenGate (1) – file to replicat

In this post I’ll show how to do the initial load by exporting a schema from the source database, importing that into the target and then start the re-synchronization in GoldenGate.

The initial setup on the source and the target is almost the same. But first lets delete all objects of “HR” in the target database:

declare
  lv_statement varchar2(2000);
begin
  for r in ( select object_name,object_type from dba_objects where owner='HR' )
  loop
    case r.object_type
      when 'TABLE' then lv_statement := 'drop table hr.'||r.object_name||' cascade constraints';
      when 'VIEW' then lv_statement := 'drop view hr.'||r.object_name;
      when 'SEQUENCE' then lv_statement := 'drop sequence hr.'||r.object_name;
      when 'PROCEDURE' then lv_statement := 'drop procedure hr.'||r.object_name;
      else lv_statement := null;
    end case;
    begin
      dbms_output.put_line(lv_statement);
      execute immediate lv_statement;
    exception when others then null;
    end;
  end loop;
end;
/
select count(*) from dba_objects where owner='HR';

Exactly as in the last post lets setup the initial recording of transactions on the source and target databases. Not much explanation here as all the steps were already covered in the last post.

On the source:

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

GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 2> add trandata HR.*
GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 2> 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;

Next steps (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.

GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 10> edit params dppump1

Parameters:

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

Next steps (as in the last post)

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    

On the target system (as in the last post):

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 26> edit params rplcdd

Parameters:

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

Configure the replicat (as in the last post):

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

Record the SCN of the source database:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2040227

Having the scn we can now do an export of the “HR” schema on the source:

SQL> create or replace directory gg as '/var/tmp';

Directory created.

SQL> !expdp userid="' / as sysdba'" schemas=hr dumpfile=exphr.dmp logfile=exphr.log directory=gg flashback_scn=2040227

Transfer this to the target environment and do the import:

SQL> create or replace directory gg as '/var/tmp';

Directory created.

SQL> !impdp userid="' / as sysdba'" schemas=hr dumpfile=exphr.dmp logfile=imphr.log directory=gg

The rest is the same as in the last post. Start the replicat on the target:

GGSCI (oelgg2.it.dbi-services.com) 6> start replicat REPLCDD, aftercsn 2040227

Sending START request to MANAGER ...
REPLICAT REPLCDD starting

Do some modifications on the source:

SQL> update hr.countries set COUNTRY_NAME = 'Zimbabwe11' where COUNTRY_NAME = 'Zimbabwe10';

1 row updated.

SQL> commit;

And then check the modification on the target:

SQL> select * from hr.countries where country_name like '%Zimba%';

CO COUNTRY_NAME 			     REGION_ID
-- ---------------------------------------- ----------
ZW Zimbabwe11					     4

SQL> select instance_name from v$instance;

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

SQL> 

Quite easy if you get the basic steps for configuring the replicat and extracts right.
The next post will look into how you can suspend replication for end of day processing and restart it afterwards automatically.