oracle_12_goldengate_conf_schema [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Overview======
Using Datapump is the cleanest way to start or reset golden gate. So let's see how it is:


======Golden Gate Set-Up======
In that case we want to migrate a single schema and set up one way replication

  - Server: Source (schema: HR)
  - Server: Destination

=====Cleanup=====
Firstly, we will clean up the schema, by deleting everything

<sxh bash>
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';
</sxh>


=====Golden Gate Source Config=====
On the source we have to configure couple stuff:

====Setup Trend Data====
<sxh bash>
GGSCI (oelgg1.it.dbi-services.com) 1> dblogin userid username 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
</sxh>

====Parameters of the Extract====
<sxh bash>
EXTRACT extrcdc1
USERIDALIAS DB1 domain admin
EXTTRAIL ./dirdat/gg
LOGALLSUPCOLS
UPDATERECORDFORMAT compact
TABLE hr.*;
TABLEEXCLUDE HR.EMP_DETAILS_VIEW;
</sxh>

====Config Extract====
<sxh bash>
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
</sxh>

====Parameters Dump====
<sxh bash>
EXTRACT dppump1
PASSTHRU
RMTHOST oelgg2, MGRPORT 7809
RMTTRAIL ./dirdat/jj
TABLE hr.*;
TABLEEXCLUDE HR.EMP_DETAILS_VIEW;
</sxh>



====Config Dump====
<sxh bash>
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    
</sxh>


=====Golden Gate Target Config=====
Once this is working, let's go for the target system:



====Replicat Parameters====
<sxh bash>
GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 26> edit params rplcdd

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


====Config Replicat====
<sxh bash>
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
</sxh>

===On SourceServer===
A quick return to the source database, we have to export the schema:

====Create a base====
We have to create a consistent backup, therefore we have to take current SCN and execute expdp using that scn :)

<sxh bash>
SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
    2040227
  
 </sxh>


<sxh bash>
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
</sxh>

After you tansfer the file: "exphr.dmp" to the target server, import it :)

===On Target Server===
<sxh bash>
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

</sxh>

====Start Replicat====
So let's start the Replicat using the scn from the source server and the consistent SCN.

<sxh bash>
GGSCI (oelgg2.it.dbi-services.com) 6> start replicat REPLCDD, aftercsn 2040227
 
Sending START request to MANAGER ...
REPLICAT REPLCDD starting
</sxh>