Golden Gate



goldengate-12c-installation-for-oracle-db

goldengate-12c-initial-configuration

 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 SQL> create tablespace ggs_data datafile ‘/u02/oradata/gavin/ggs_data01.dbf’ size 200m;
SQL> create user ggs_owner identified by ggs_owner
     default tablespace ggs_data
     temporary tablespace temp;
SQL> grant connect,resource to ggs_owner;
SQL> grant select any dictionary, select any table to ggs_owner;
SQL> grant create table to ggs_owner;
SQL> grant flashback any table to ggs_owner;
SQL> grant execute on dbms_flashback to ggs_owner;
SQL> grant execute on utl_file to ggs_owner;
ggsci

 > CREATE SUBDIRS

 > DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Source and target : Configuring the Manager process
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 1>EDIT PARAMS MGR

 PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /app/oracle/product/gg/dirdat/ex, USECHECKPOINTS
2> start manager

 > info manager

 > view report mgr



Target :  Create the GoldenGate Checkpoint table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 > EDIT PARAMS ./GLOBALS  
GGSCHEMA GGS_OWNER  
CHECKPOINTTABLE GGS_OWNER.CHKPTAB 


 or

 > DBLOGIN USERID ggs_owner, PASSWORD ggs_owner     
> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB  
Source  : Create the Online Extract Group  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 1 > ADD EXTRACT ext1, TRANLOG, BEGIN NOW   -- Now :  the current timestamp

  help add extract

 On error :> alter extract ext1 begin now
2> ADD RMTTRAIL /app/oracle/product/gg/dirdat/rt, EXTRACT ext1   -- extract group ext1 crates remote trail at host2
3 > EDIT PARAMS ext1    -- Create a parameter file for the online Extract group ext1 

 EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTRAIL /app/oracle/product/gg/dirdat/rt
TABLE scott.emp;
Target : Create the online Replicat group
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 0> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner     
> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB  

 1> ADD REPLICAT rep1, EXTTRAIL /app/oracle/product/gg/dirdat/rt , CHECKPOINTTABLE GGS_OWNER.CHKPTAB

2> EDIT PARAMS rep1 -- Create a parameter file for the online Replicat group, rep1

 REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.emp, TARGET sh.emp;
Start extract , replicate and test replication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
source> ggsci

 start extract ext1

  STATUS EXTRACT ext1
 INFO EXTRACT ext1
 INFO EXTRACT ext1 detail

 info all 
view ggsevt 
target> ggsci  
--> START REPLICAT rep1

 STATUS REPLICAT rep1
 INFO REPLICAT rep1
 INFO REPLICAT rep1 detail

 -->start replicat rep1, aftercsn 401678  
--> info all 

CONFIGURING DATA PUMP PROCESS :


When Data Pump is configured, the Extract process writes to a local trail and from here Data Pump 
will read the trail and write the data over the network to the remote trail located on the target system.


Source : Create Extract process , local trail and  Data Pump group
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> ADD EXTRACT ext1, TRANLOG, BEGIN NOW

> ADD EXTTRAIL /app/oracle/product/gg/dirdat/lt, EXTRACT ext1

> ADD EXTRACT dpump, EXTTRAILSOURCE /app/oracle/product/gg/dirdat/lt


Source : Create parameter file for Primary Extract and Data Pump group
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> EDIT PARAMS ext1

EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
EXTTRAIL /app/oracle/product/gg/dirdat/lt
TABLE MONITOR.WORK_PLAN;

> EDIT PARAMS dpump
“/app/oracle/product/gg/dirprm/dpump.prm” [New file]
EXTRACT dpump
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST redhat346, MGRPORT 7809
RMTTRAIL /app/oracle/product/gg/dirdat/rt
PASSTHRU
TABLE MONITOR.WORK_PLAN;

 Source : Specify the location of the remote trail on the target system
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> ADD RMTTRAIL /app/oracle/product/gg/dirdat/rt, EXTRACT dpump





Target : Create the Replicat group and parameter file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> ADD REPLICAT rep1, EXTTRAIL /app/oracle/product/gg/dirdat/rt

EDIT PARAMS rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP MONITOR.WORK_PLAN, TARGET MONITOR.WORK_PLAN;

Start Extract, Data Pump process on source and replicate on target
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> START EXTRACT ext1
> START EXTRACT dpump
> info extract ext1
> INFO EXTRACT dpump

> START REPLICAT rep1
> STATUS REPLICAT rep1

~~~~~~~~~~~~~~~~~~~ End ~~~~~~~~~~~~~~~~~~~~~~~~~~~

Performing initial data load :


 Source : Create load and parameter file for replicate group
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 --> ADD EXTRACT load1, SOURCEISTABLE
> EDIT PARAMS load1
EXTRACT load1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST , MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE sh.products;

 Target : Create load and parameter file for replicate group
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 --> ADD REPLICAT load2, SPECIALRUN
> EDIT PARAMS load2
REPLICAT load2
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP sh.customers, TARGET sh.customers;

 Source : Start Extract and the table on target
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 START EXTRACT load1
> info extract load1



 For Parallel processing :

  1. Stop all replicat

  2. Add following replicates    Note : 
   ADD REPLICAT rep2, EXTTRAIL /app/oracle/product/gg/dirdat/rt
  ADD REPLICAT rep3, EXTTRAIL /app/oracle/product/gg/dirdat/rt 
   ADD REPLICAT rep4, EXTTRAIL /app/oracle/product/gg/dirdat/rt 


   EDIT PARAMS rep1

 REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.emp, TARGET sh.emp, FILTER (@RANGE (1,4));

   EDIT PARAMS rep2

 REPLICAT rep2
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.emp, TARGET sh.emp, FILTER (@RANGE (2,4));

   EDIT PARAMS rep3

 REPLICAT rep3
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.emp, TARGET sh.emp, FILTER (@RANGE (3,4));

   EDIT PARAMS rep4

 REPLICAT rep4
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.emp, TARGET sh.emp, FILTER (@RANGE (4,4));

 4.Check the Log Read Checkpoint and RBA number

  info replicat rep*,detail     : Note the Log Read Checkpoint and RBA number

 alter replicat rep2, extseq < LRC no.>
alter replicat rep3, extseq < LRC no.>
alter replicat rep4, extseq < LRC no.>

 alter replicat rep2, extrba < RBA no.>
alter replicat rep3, extrba < RBA no.>
alter replicat rep4, extrba < RBA no.>




 5. Start the extract and replicate :
    stop extract ext1
    start extract ext1
    start replicat rep*






configuring DDL synchronization
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If 10g , alter system set recyclebin=off scope=spfile;  and bounce database

 spool /tmp/gg_setup.rtf
@$GGATE/marker_setup.sql
    Enter GoldenGate schema name:ggate
@$GGATE/ddl_setup.sql -- For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
    Enter mode of installation:INITIALSETUP

 @$GGATE/role_setup.sql
    grant GGS_GGSUSER_ROLE to ggs_owner;
@$GGATE/ddl_enable.sql

 @$GGATE/ddl_pin GGS_OWNER
exit;

 Enable additional logging at the table level
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
> ADD TRANDATA scott.emp

Enable DDL synchronization
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> EDIT PARAM EXT1

 EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST 10.53.100.100, MGRPORT 7809
RMTTRAIL /app/oracle/product/gg/dirdat/rt
DDL INCLUDE MAPPED
TABLE scott.emp;
Commands :
ALTER EXTRACT [name], ETROLLOVER 
SEND EXTRACT [pump_name], LOGEND 
ALTER EXTRACT [pump_name], EXTSEQNO ##### EXTRBA 0 

 SEND REPLICAT [name], LOGEND 
SEND REPLICAT [name], REPORT

 ?? GROUPTRANSOPS 1
?? MAXTRANSOPS 1

 ALTER REPLICAT [name], EXTSEQNO ###### EXTRBA 0 

 GGSCI > info ext1 showch


 GGSCI > send ext1 showtrans
Other : testing to restart all over again
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> kill REPLICAT rep1

 >  stop REPLICAT rep1

 truncate table

  DELETE RMTTRAIL  REP1;
Alter extract ext1 , TRANLOG, BEGIN NOW   -- start all over again

 or

 DELETE EXTRACT ext1

 ADD EXTRACT ext1, TRANLOG, BEGIN NOW
--- start all over again  ---

 alter extract pumpextractname, etrollover

 …on the source, and on the target…

 stop replicat replicatname
alter replicat replicatname, extseqno 1
start replicat replicatname
-----

 stop extract ext1
ALTER EXTRACT ext1, ETROLLOVER 
  2012-08-26 22:44:45  INFO    OGG-01520  Rollover performed
ALTER EXTRACT ext1, EXTSEQNO 1520 EXTRBA 0 
start extract ext1
send replicat rep1, logend
stop replicat rep1
ALTER REPLICAT rep1, EXTSEQNO 1520 EXTRBA 0 
start replicat rep1
----

 DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

 ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB

 EDIT PARAMS ext1
ADD REPLICAT rep1, EXTTRAIL /app/oracle/product/gg/dirdat/rt , CHECKPOINTTABLE GGS_OWNER.CHKPTAB  -- XX

  1> start extract ext1
ERROR: EXTRACT EXT1 does not exist.

 ADD EXTRACT ext1, TRANLOG, BEGIN NOW 

 Alter extract ext1,TRANLOG, BEGIN NOW

 START REPLICAT REP1,aftercsn 10394714172497
Other Links :

 goldengate-hands-on-in-one-hour.html

 goldengate-command-interpreter.html

 havawala-goldengate-091741.html

 http://gavinsoorma.com-initial-data-load/

 http://gssdba-step-by-step

 skipping-a-transaction-in-goldengate

 how-to-skip-orphan-transactions-in.html

 deciphercorp.wordpress.com/category/oracle-goldengate/

 Positioning a Read of Extract/Replicat Trail File or Oracle Redo Log

Defining Multiple Replicats to Increase GoldenGatePerformance


Subscribe to: Posts (Atom)