Monday, January 20, 2014

OGG - Experiments

Installation:-
==============

1. GG Installation.


  a) GG home - eg /db/gg_trace/golden_gate --> untar the tar file ( tar -xvf software_file_name)

  b) ggsci

> create subdirs


2. Connect to the db and create the golden gate admin user.



SQL> create tablespace golden_gate datafile '/db/oracle/oradata/NSBLSIT12/golden_gate01.dbf' size 2g;

Tablespace created.

SQL> create user ggate identified by Gate_123 default tablespace golden_gate;

User created.

grant connect, resource to ggate;
grant dba to ggate;
grant select any dictionary to ggate;
grant insert any table, select any table, delete any table, update any table to ggate;
grant flashback any table to ggate;
grant execute on dbms_flashback to ggate;
grant execute on utl_file to ggate;
grant create any table to ggate;
grant create sequence to ggate;
grant drop any table to ggate;


SQL> alter database add supplemental log data (all) columns;

Database altered.



SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup

SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

SQL> @ddl_enable

SQL> @ddl_pin ggate



Solution: Grant these privieleges to ggate
=======================================================
1. Run package to grant Oracle Streams admin privilege.
exec dbms_streams_auth.grant_admin_privilege('ggate');
exec dbms_goldengate_auth.grant_admin_privilege('ggate');
2. Grant INSERT into logmnr_restart_ckpt$.
grant insert on system.logmnr_restart_ckpt$ to ggate;
3. Grant UPDATE on streams$_capture_process.
grant update on sys.streams$_capture_process to ggate;
4. Grant the 'become user' privilege.
grant become user to ggate;
=======================================================

Examples:-
---------


Server: 10.41.66.187 - Source
Server: 10.14.24.152 - Target

Source database : NPTLPVT
target database : NTESTSRV

GG owner - GGATE


-- Initial Load
--> Direct load -- Extract sends data directly to Replicat to apply using SQL
  (During the load, the records are applied to the target database one record at a time, so this
  method is considerably slower than any of the other initial load methods)

  Extract  Parameters (RMTHOST, RMTTASK REPLICAT, GROUP) / ADD EXTRACT GRP SOURCEISTABLE
  Replicat Parameters ADD REPLICAT GRP SPECIALRUN



--> Direct bulk load -- Replicat uses the Oracle SQL*Loader API.
 
  Extract  Parameters (RMTHOST, RMTTASK REPLICAT, GROUP) / ADD EXTRACT GRP SOURCEISTABLE
  Replicat Parameters (BULKLOAD / ADD REPLICAT GRP SPECIALRUN)



--> File to Replicat -- Extract writes to a file (Trail File) that Replicat applies using SQL.

  Extract  Parameters (RMTHOST, RMTFILE) / ADD EXTRACT GRP SOURCEISTABLE
  Replicat Parameters ADD REPLICAT GRP SPECIALRUN

--> File to database utility -- Extract writes to a file formatted for a DB bulk load utility.

  Extract parameters (RMTFILE, FORMATASCII SQLLOADER / ADD EXTRACT GRP SOURCEISTABLE)
  Replicat Parameters (GENLOADFILES) tO generate controlfile and runfile



Seting up the environment on source
-----------------------------------

--Setup the checkpoint table for whole instance.

GGSCI (S66CJ187) 2> dblogin userid ggate password Gate_123
Successfully logged into database.


GGSCI (S66CJ187) 9> ADD CHECKPOINTTABLE
ERROR: Missing checkpoint table specification.

GGSCI (S66CJ187) 10> EDIT PARAMS ./GLOBALS

GGSCHEMA GGATE
CHECKPOINTTABLE GGATE.CHKPTBL


GGSCI (S66CJ187) 2> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (GGATE.CHKPTBL)...

Successfully created checkpoint table GGATE.CHKPTBL.



--Setup the MGR Process

GGSCI (S66CJ187) 12> EDIT PARAMS MGR

PORT 7810
DYNAMICPORTLIST 7900-7950


GGSCI (S66CJ187) 13> START MGR

Manager started.


GGSCI (S66CJ187) 14> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


I) Load using initital load where the source and target table are having mismtach in columns.


SQL> desc GG_SOURCE.GG_SYNC_TBL1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(30)
 DETAILS                                            VARCHAR2(30)


-- Target table

SQL> desc GG_TARGET.GG_SYNC_TBL1_TGT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(30)




-- As we can see the source table GG_SYNC_TBL_1 has 3 columns and the target table GG_SYNC_TBL1_TGT
is having 2 columns, we can use the defgen (definition generator) utility to map this.




Create a parameter file first.

GGSCI (S66CJ187) 4> edit params defgen


DEFSFILE ./dirsql/GG_SYNC_TBL1.sql
USERID GGATE password Gate_123
TABLE  GG_SOURCE.GG_SYNC_TBL1;




$ ./defgen PARAMFILE ./dirprm/defgen.prm REPORTFILE ./dirrpt/defgen.rpt


A GG_SYNC_TBL1.sql is generated under dirsql directory which looks like

*+- Defgen version 2.0, Encoding hp-roman8
*
* Definitions created/modified  2014-01-15 16:31
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table GG_SOURCE.GG_SYNC_TBL1
Record length: 128
Syskey: 0
Columns: 3
EMPNO     64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
ENAME     64     30       56  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
DETAILS   64     30       92  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
End of definition


ftp/scp the GG_SYNC_TBL1.sql to the target server under the dirsql directory of GG software home.



Extract on the source side
--------------------------

GGSCI (S66CJ187) 20>

GGSCI (S66CJ187) 20> EDIT PARAMS EXT1


EXTRACT DIR_1
USERID GGATE, PASSWORD Gate_123
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTASK REPLICAT, GROUP DIR_R1
TABLE GG_SOURCE.GG_SYNC_TBL2;


GGSCI (S66CJ187) 20> ADD EXTRACT EXT1, SOURCEISTABLE




Replicat on the target side
---------------------------

-- Setup the environment (CHECKPOINTABLE and MGR Process as per above).


GGSCI (S24BF152) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (S24BF152) 2> EDIT PARAMS REP1


REPLICAT REP1
USERID GGATE, PASSWORD Gate_123
SOURCEDEFS ./dirsql/GG_SYNC_TBL1.sql
MAP GG_SOURCE.GG_SYNC_TBL1, TARGET GG_TARGET.GG_SYNC_TBL1_TGT, COLMAP (EMPNO=EMPNO ENAME=ENAME);


GGSCI (S24BF152) 3> ADD REPLICAT REP1, SPECIALRUN



Start the Loading
-----------------

On the source side start the Extract prcoess , since it is a initial load the replicat
will be started automatically on the target side.

GGSCI (S66CJ187) 2> START EXTRACT EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (S66CJ187) 9> INFO EXT1

EXTRACT    EXT1      Last Started 2014-01-16 08:10   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GG_SOURCE.GG_SYNC_TBL1
                     2014-01-16 08:10:45  Record 1
Task                 SOURCEISTABLE


GGSCI (S66CJ187) 10> !
INFO EXT1

EXTRACT    EXT1      Last Started 2014-01-16 08:10   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GG_SOURCE.GG_SYNC_TBL1
                     2014-01-16 08:10:46  Record 1000
Task                 SOURCEISTABLE

GGSCI (S66CJ187) 11> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


--as above 1000 records been loaded and then the extract process is stopped.

--Check the count on the target side..

SQL> select count(*) from gg_target.gg_sync_tbl1_tgt;

  COUNT(*)
----------
      1000



II) Change Data Capture.
------------------------

Source Extract Process
----------------------

GGSCI (S66CJ187) 4> EDIT PARAMS EXT1

EXTRACT EXT1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE@NPTLPVT,  PASSWORD GATE_123
EXTTRAIL ./dirdat/XO
DDL INCLUDE MAPPED EXCLUDE OBJTYPE TRIGGER
--TRANLOGOPTIONS DBLOGREADER # IF LOG FILES ARE STORED IN ASM (Supported 10.2.0.5 and higher and 11.2.0.2 & Higher Not 11gr1)
--TRANLOGOPTIONS ASMUSER SYS@, ASMPASSWORD # IF LOG FILES ARE STORED IN ASM (10g)
TABLE GG_SOURCE.GG_SYNC_TBL1;


GGSCI (S66CJ187) 5> ADD TRANDATA GG_SOURCE.GG_SYNC_TBL1

Logging of supplemental redo log data is already enabled for table GG_SOURCE.GG_SYNC_TBL1.


GGSCI (S66CJ187) 6> ADD EXTRACT EXT1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (S66CJ187) 8> ADD EXTTRAIL ./dirdat/XO, EXTRACT EXT1, MEGABYTES 20
EXTTRAIL added.

GGSCI (S66CJ187) 10> START EXTRACT EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (S66CJ187) 19> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:01


Source Pump Process
-------------------

GGSCI (S66CJ187) 20> EDIT PARAMS PMP1


EXTRACT PMP1
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTRAIL ./dirdat/XO
PASSTHRU
TABLE GG_SOURCE.GG_SYNC_TBL1;


GGSCI (S66CJ187) 23> ADD EXTRACT PMP1 , EXTTRAILSOURCE ./dirdat/XO
EXTRACT added.

GGSCI (S66CJ187) 25> ADD RMTTRAIL ./dirdat/XO , EXTRACT PMP1, MEGABYTES 20
RMTTRAIL added.

GGSCI (S66CJ187) 26> START EXTRACT PMP1

Sending START request to MANAGER ...
EXTRACT PMP1 starting


GGSCI (S66CJ187) 41> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:04
EXTRACT     RUNNING     PMP1        00:00:00      00:00:22



-- Check whether the file is transfered to the target server.

/app/oracle/golden_gate/dirdat> ls -lrt
total 0
-rw-rw-rw-   1 oracle     oinstall         0 Jan 16 09:38 XO000000


-- Add some records in the source table.

SQL> begin
  2     for i in 1001..2000 loop
  3             insert into gg_sync_tbl1 values (i, dbms_random.string('U',30), DBMS_RANDOM.STRING('U',30));
  4     end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from gg_source.gg_sync_tbl1;

  COUNT(*)
----------
      2000


-- Register the extract so that rman will not delete the archived log files if the data is not captured by
the extract process.

GGSCI (S66CJ187) 7> REGISTER EXTRACT EXT1 LOGRETENTION

2014-01-16 10:59:48  ERROR   OGG-01754  Cannot register or unregister EXTRACT EXT1 because the Extract is currently running. Stop the Extract and retry the command.


GGSCI (S66CJ187) 8> STOP EXTRACT EXT1

Sending STOP request to EXTRACT EXT1 ...
Request processed.


GGSCI (S66CJ187) 9> REGISTER EXTRACT EXT1 LOGRETENTION

2014-01-16 11:00:07  INFO    OGG-01749  Successfully registered EXTRACT EXT1 to start managing log retention at SCN 59178692.

GGSCI (S66CJ187) 11> START EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


Target Replicat process
-----------------------


GGSCI (S24BF152) 7> DELETE REPLICAT REP1
Deleted REPLICAT REP1.


GGSCI (S24BF152) 2> EDIT PARAMS REP1


REPLICAT REP1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE, PASSWORD Gate_123
SOURCEDEFS ./dirsql/GG_SYNC_TBL1.sql
DISCARDFILE ./dirrpt/REP1.dsc , append
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
MAP GG_SOURCE.GG_SYNC_TBL1, TARGET GG_TARGET.GG_SYNC_TBL1_TGT, COLMAP (EMPNO=EMPNO ENAME=ENAME);


GGSCI (S24BF152) 8> ADD REPLICAT REP1 , EXTTRAIL ./dirdat/XO
REPLICAT added.


GGSCI (S24BF152) 9> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP1        00:00:00      00:00:06


GGSCI (S24BF152) 10> START REPLICAT REP1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (S24BF152) 16> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:17:03      00:00:06


GGSCI (S24BF152) 17> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:04


-- Now there is no lag.. let check the count in the target.


SQL> select count(*) from gg_target.gg_sync_tbl1_tgt;

  COUNT(*)
----------
      2000




III) Initial Load using Direct Bulk load
------------------------------------------------

Source Table :- GG_SYNC_TBL2
Target Table :- GG_SYNC_TBL2


Source
------

SQL> DESC GG_SYNC_TBL2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                              VARCHAR2(30)
 DETAILS                                            VARCHAR2(30)



SQL> insert into GG_SYNC_TBL2 select * from GG_SYNC_TBL1;

2000 rows created.

SQL> commit;

Commit complete.


SQL> select count(*) from GG_SYNC_TBL2;

  COUNT(*)
----------
      2000



GGSCI (S66CJ187) 3> dblogin userid ggate password Gate_123
Successfully logged into database.



GGSCI (S66CJ187) 22> EDIT PARAMS DIR_1


EXTRACT DIR_1
USERID GGATE, PASSWORD Gate_123
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTASK REPLICAT, GROUP DIR_R1
TABLE GG_SOURCE.GG_SYNC_TBL2;


GGSCI (S66CJ187) 4> ADD EXTRACT DIR_1, SOURCEISTABLE
EXTRACT added.





Replicat Side
-------------


SQL> DESC GG_SYNC_TBL2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                              VARCHAR2(30)
 DETAILS                                            VARCHAR2(30)




GGSCI (S24BF152) 3> EDIT PARAMS DIR_R1


REPLICAT DIR_R1
USERID GGATE , PASSWORD Gate_123
ASSUMETARGETDEFS
BULKLOAD
MAP GG_SOURCE.GG_SYNC_TBL2, TARGET GG_TARGET.GG_SYNC_TBL2;



GGSCI (S24BF152) 8> ADD REPLICAT DIR_R1 SPECIALRUN
REPLICAT added.

-------

Start the extract from the Source side.


GGSCI (S66CJ187) 4> START EXTRACT DIR_1

Sending START request to MANAGER ...
EXTRACT DIR_1 starting


GGSCI (S66CJ187) 8> INFO DIR_1

EXTRACT    DIR_1     Last Started 2014-01-17 10:24   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GG_SOURCE.GG_SYNC_TBL2
                     2014-01-17 10:24:54  Record 2000
Task                 SOURCEISTABLE



Report file - of Replicat process.

==================================================================

Report at 2014-01-17 10:25:00 (activity since 2014-01-17 10:24:54)

From Table GG_SOURCE.GG_SYNC_TBL2 to GG_TARGET.GG_SYNC_TBL2:
       #                   inserts:      2000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


-- Lets check the count.

SQL> select count(*) from gg_target.gg_sync_tbl2;

  COUNT(*)
----------
      2000



IV) Usinig Logdump Utility.
---------------------------

Desc     Command Eg
------------------------------------------------------------------------------------------
Open a Trail File -  open Logdump 1 > open ./dirdat/XO000000
To view Trail file header -  fileheader on Logdump 2 > fileheader on
To view Record header -  ghdr on Logdump 3 > ghdr on
To view Column information -  detail on Logdump 4 > detail on
To View column values -  detail data Logdump 5 > detail data
To control the length of value  -  reclen Logdump 6 > reclen 300
To move to first records -  pos Logdump 7 > pos 0
To move to next record -  next or n Logdump 8 > next (or just n)
To move to the starting point
or good header -  scanforheader (sfh)  Logdump 9 > sfh
To move to previous good header -  sfh prev Logdump 10> sfh prev


Record header - (TransInd   :     .  (x00)

00 - Begining of the transaction.
01 - Middle   of the transaction.
02 - Last     of the transaction.
03 - Whole    of the transaction.


Count records in trail - count Logdump 61 > count



Using Filter Options:-
----------------------

Eg.

SQL> update gg_sync_tbl1 set ename = 'MANZOOR' where empno = 1678;

1 row updated.

SQL> commit

Commit complete.

SQL> begin
  2     for i in 2001..2500 loop
  3             insert into  gg_sync_tbl1 values (i,dbms_random.string('U',30), dbms_random.string('U',30));
  4             commit;
  5     end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> delete from gg_sync_tbl1 where ENAME = 'MANZOOR';

1 row deleted.

SQL> commit;

Commit complete.


-- Lets find out this delete tractions using filter option in trail file.


Logdump 175 >open ./dirdat/XO000000
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000000
Logdump 176 >GHDR ON
Logdump 177 >DETAIL ON
Logdump 178 >DETAIL DATA
Logdump 179 >FILEHEADER ON
Logdump 180 >FILTER INCLUDE FILENAME GG_SOURCE.GG_SYNC_TBL1 # Filter the table name
Logdump 181 >FILTER INCLUDE RECTYPE DELETE # Filter the delete operation
Logdump 182 >SHOW FILTER # Display the current filter condition.

Data filters are ENABLED

Include  Match ANY
 Rectypes   : Delete
 Filename-0 : GG_SOURCE.GG_SYNC_TBL1

Exclude  Match ANY

Logdump 183 >FILTER MATCH ALL # Instruciting to find the records which matches all the condition can use FILTER MATCH ANY for any of the one condition.
Logdump 184 >N # To start search (next)

Filtering suppressed   1001 records
Logdump 185 >NT # The record is not present in this trail file, to continue searching in next trail enter NT (next trail)
LogTrail /db/oracle/golden_gate/dirdat/XO000000 closed
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000001
Logdump 186 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    12  (x000c)   IO Time    : 2014/01/18 08:36:30.000.000
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193305728
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:36:30.000.000 Delete               Len    12 RBA 108695
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738                     | ........1678
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678


Filtering suppressed    503 records




## Now we have found the delete operation.


-------------------------------------------------------------------------------

Logging lodump session LOG TO LOG TO MYSESSIONLOG.TXT
Stop logging   session LOG STOP LOG STOP




Reverse Utitlity (Reversal of Transaction)
----------------------------------------------------------

1. Used to extract the before data and backout the changes.

2. It can use either trailfile or transaction logs (archive logs).

Restrictions
------------

1. Long and LOB columns are not reversed if before values are not stored in db.
2. XML TYPE tables are not supported.


Lets backout the happend from the below update statment... around (500 Transactions).


update gg_sync_tbl1 set ename = 'MANZOOR' where empno = 1678;


1) Get the time from and to backout the transaction.


Logdump 194 >open ./dirdat/XO000000
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000000
Logdump 195 >GHDR ON
Logdump 196 >DETAIL ON
Logdump 197 >DETAIL DATA
Logdump 198 >FILEHEADER ON
Logdump 199 >FILTER INCLUDE RECTYPE 15; FILTER INCLUDE FILENAME GG_SOURCE.GG_SYNC_TBL1; FILTER MATCH ALL
Logdump 200 >SHOW FILTER

Data filters are ENABLED

Include  Match ALL
 Rectypes   : FieldComp
 Filename-0 : GG_SOURCE.GG_SYNC_TBL1

Exclude  Match ANY

Logdump 201 >
Logdump 201 >n

Filtering suppressed   1001 records
Logdump 202 >nt
LogTrail /db/oracle/golden_gate/dirdat/XO000000 closed
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000001
Logdump 203 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    65  (x0041)   IO Time    : 2014/01/17 12:27:51.000.000
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 60374144
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/17 12:27:51.000.000 FieldComp            Len    65 RBA 1012
Name: GG_SOURCE.GG_SYNC_TBL1
After  Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738 0001 000b 0000 0007 | ........1678........
 4d41 4e5a 4f4f 5200 0200 2200 0000 1e52 4141 544c | MANZOOR..."....RAATL
 4647 564f 4258 534b 5044 5447 4842 4643 564d 4e44 | FGVOBXSKPDTGHBFCVMND
 4b56 5656 52                                      | KVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    11 (x000b)
 0000 0007 4d41 4e5a 4f4f 52                       | ....MANZOOR
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR




The Time of the  transaction is 2014/01/17 12:27:51.000.000
The time of last transaction is 2014/01/18 08:36:30.000.000

Lets backuout all the transaction happend between these time.



Process
-------

a) Create a parameter file for extract process.
b) Run the reverse utility.
c) Run replicat process.


Parameter file (Pre OGG 11.2 )


SPECIALRUN, TRANLOG
USERID GGATE Password Gate_123
BEGIN 2014-01-17 12:27:51
END   2014-01-18 08:36:30
GETUPDATEBEFORES
NOCOMPRESSDELETES
--RMTHOST 10.41.66.187  MGRPORT 7810  # IF the file has to be generated on remote system
--RMTFILE ./dirdat/REV.dat      # IF the file has to be generated on remote system
EXTFILE ./dirdat/REV.dat
TABLE GG_SOURCE.GG_SYNC_TBL1;


Run from os shell  ( ./extract paramfile ./dirprm/ext_1.prm)






Pameter file (From OGG 11.2)

GGSCI (S66CJ187) 9> EDIT PARAMS EXT_1


EXTRACT EXT_1
USERID GGATE Password Gate_123
END   2014-01-18 08:36:30
GETUPDATEBEFORES
NOCOMPRESSDELETES
--RMTHOST 10.14.24.152  MGRPORT 7810 # IF the trail file has to be generated on remote system
--RMTTRAIL ./dirdat/RE     # IF the trail file has to be generated on remote system
EXTTRAIL ./dirdat/RE
TABLE GG_SOURCE.GG_SYNC_TBL1;



GGSCI (S66CJ187) 10> ADD EXTRACT EXT_1, TRANLOG, BEGIN 2014-01-17 12:27:00
EXTRACT added.

GGSCI (S66CJ187) 13> ADD EXTTRAIL ./dirdat/RE , extract EXT_1
EXTTRAIL added.


GGSCI (S66CJ187) 14> START EXTRACT EXT_1

Sending START request to MANAGER ...
EXTRACT EXT_1 starting

GGSCI (S66CJ187) 27> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:01
EXTRACT     RUNNING     EXT_1       43:53:36      00:07:40
EXTRACT     RUNNING     PMP1        00:00:00      00:00:06


GGSCI (S66CJ187) 28> INFO EXT_1

EXTRACT    EXT_1     Last Started 2014-01-19 08:29   Status RUNNING
Checkpoint Lag       44:01:18 (updated 00:00:01 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-01-17 12:27:51  Seqno 180, RBA 60378112
                     SCN 0.0 (0)


GGSCI (S66CJ187) 30> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:10
EXTRACT     STOPPED     EXT_1       23:52:44      00:00:12
EXTRACT     RUNNING     PMP1        00:00:00      00:00:05


Once the extraction is completed the extract process is stopped.


--Below is the snap from the REPORT FILE.

From Table GG_SOURCE.GG_SYNC_TBL1:
       #                   inserts:       500
       #                   updates:         1
       #                   befores:         1
       #                   deletes:         1
       #                  discards:         0



--Lets review the trail file to find the first record, as per above the first record should be
update.

$ ls -lrt RE*
total 10816
-rw-rw-rw-   1 oracle     oinstall    109018 Jan 19 08:46 RE000000

Logdump 314 >open RE000000
Current LogTrail is /db/oracle/golden_gate/dirdat/RE000000
Logdump 315 >GHDR ON
Logdump 316 >DETAIL ON
Logdump 317 >DETAIL DATA
Logdump 318 >FILEHEADER ON
Logdump 319 >POS 0
Reading forward from RBA 0
Logdump 320 >N
Logdump 321 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    88  (x0058)   IO Time    : 2014/01/17 12:27:50.910.232
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 60374144
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/17 12:27:50.910.232 FieldComp            Len    88 RBA 954
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  b
 0000 0008 0000 0004 3136 3738 0001 0022 0000 001e | ........1678..."....
 4b59 4857 5055 5255 4d43 4551 5a53 544b 5954 5947 | KYHWPURUMCEQZSTKYTYG
 5a50 4e5a 4158 4e5a 424d 0002 0022 0000 001e 5241 | ZPNZAXNZBM..."....RA
 4154 4c46 4756 4f42 5853 4b50 4454 4748 4246 4356 | ATLFGVOBXSKPDTGHBFCV
 4d4e 444b 5656 5652                               | MNDKVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    34 (x0022)
 0000 001e 4b59 4857 5055 5255 4d43 4551 5a53 544b | ....KYHWPURUMCEQZSTK
 5954 5947 5a50 4e5a 4158 4e5a 424d                | YTYGZPNZAXNZBM
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR




--- We could see this is the BEFORE image of the update statement we required to rollback the update.

---Check the last statment in the trail..


Logdump 322 >POS LAST
Reading forward from RBA 109018
Logdump 323 >n
Logdump 324 >pos rev
Reading in reverse from RBA 109018
Logdump 325 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    65  (x0041)   IO Time    : 2014/01/18 08:36:30.019.919
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193305728
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:36:30.019.919 Delete               Len    65 RBA 108826
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738 0001 000b 0000 0007 | ........1678........
 4d41 4e5a 4f4f 5200 0200 2200 0000 1e52 4141 544c | MANZOOR..."....RAATL
 4647 564f 4258 534b 5044 5447 4842 4643 564d 4e44 | FGVOBXSKPDTGHBFCVMND
 4b56 5656 52                                      | KVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    11 (x000b)
 0000 0007 4d41 4e5a 4f4f 52                       | ....MANZOOR
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR




--- The statment is delete ...



-- Now the extract process extracted the transactions happend during the mentioned time period.
Now we need to reverse the operation to rollback, i.e. insert becomes deletes, delete become inserert,
update become update with before image. This can be done using the REVERSE utility.


Execute the below command to reverse the operation using reverse utility.


$ pwd
/db/oracle/golden_gate/dirdat

$ ls -lrt
total 10816
-rw-rw-rw-   1 oracle     oinstall    189979 Jan 16 11:00 XO000000
-rw-rw-rw-   1 oracle     oinstall   5103031 Jan 17 10:22 XT000000
-rw-rw-rw-   1 oracle     oinstall    108834 Jan 19 08:05 XO000001
-rw-rw-rw-   1 oracle     oinstall      1012 Jan 19 08:05 XO000002
-rw-rw-rw-   1 oracle     oinstall    109018 Jan 19 08:46 RE000000  <--- extracted="" file.="" p="" the="" this="">
$ cd ..

$ ./reverse ./dirdat/RE* ./dirdat/RV*

Oracle GoldenGate Dynamic Rollback
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
HP/UX, IA64, 64bit (optimized) on Apr 23 2012 23:15:02

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


*Warning* Source file contained Deletes which maybe compressed
Reversed ./dirdat/RE000000 to /db/oracle/golden_gate/dirdat/RV000000
Total Data Bytes             44218
  Avg Bytes/Record              87
Delete                           1
Insert                         500
FieldComp                        2
Before Images                    2
After Images                   501


$ cd dirdat

$ ls -lrt
total 11040
-rw-rw-rw-   1 oracle     oinstall    189979 Jan 16 11:00 XO000000
-rw-rw-rw-   1 oracle     oinstall   5103031 Jan 17 10:22 XT000000
-rw-rw-rw-   1 oracle     oinstall    108834 Jan 19 08:05 XO000001
-rw-rw-rw-   1 oracle     oinstall      1012 Jan 19 08:05 XO000002
-rw-rw-rw-   1 oracle     oinstall    109018 Jan 19 08:58 RE000000
-rw-r--r--   1 oracle     oinstall    109018 Jan 19 09:15 RV000000




-- Lets view the content of the trail file..


-- First Record..


Logdump 333 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    65  (x0041)   IO Time    : 2014/01/18 08:36:30.000.000
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193305728
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:36:30.000.000 Insert               Len    65 RBA 954
Name: GG_SOURCE.GG_SYNC_TBL1
After  Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738 0001 000b 0000 0007 | ........1678........
 4d41 4e5a 4f4f 5200 0200 2200 0000 1e52 4141 544c | MANZOOR..."....RAATL
 4647 564f 4258 534b 5044 5447 4842 4643 564d 4e44 | FGVOBXSKPDTGHBFCVMND
 4b56 5656 52                                      | KVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    11 (x000b)
 0000 0007 4d41 4e5a 4f4f 52                       | ....MANZOOR
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR



-- As we can see the delete reversed to insert.

-- Next record

Logdump 349 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    88  (x0058)   IO Time    : 2014/01/18 08:35:51.000.000
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193298448
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:35:51.000.000 Delete               Len    88 RBA 1146
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3235 3030 0001 0022 0000 001e | ........2500..."....
 4146 4a42 5558 524e 5052 424c 4d5a 4e49 5551 5345 | AFJBUXRNPRBLMZNIUQSE
 5149 4656 5056 594c 5943 0002 0022 0000 001e 5747 | QIFVPVYLYC..."....WG
 4e56 4a4c 4146 4a55 5347 5548 5954 4751 4e52 4248 | NVJLAFJUSGUHYTGQNRBH
 565a 5351 4148 4e54                               | VZSQAHNT
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3235 3030                               | ....2500
Column     1 (x0001), Len    34 (x0022)
 0000 001e 4146 4a42 5558 524e 5052 424c 4d5a 4e49 | ....AFJBUXRNPRBLMZNI
 5551 5345 5149 4656 5056 594c 5943                | UQSEQIFVPVYLYC
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5747 4e56 4a4c 4146 4a55 5347 5548 5954 | ....WGNVJLAFJUSGUHYT
 4751 4e52 4248 565a 5351 4148 4e54                | GQNRBHVZSQAHNT



-- Insert reversed to delete.



--Last record.

Logdump 352 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    88  (x0058)   IO Time    : 2014/01/17 12:27:51.000.000
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x02)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 60374144
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/17 12:27:51.000.000 FieldComp            Len    88 RBA 108802
Name: GG_SOURCE.GG_SYNC_TBL1
After  Image:                                             Partition 4   G  e
 0000 0008 0000 0004 3136 3738 0001 0022 0000 001e | ........1678..."....
 4b59 4857 5055 5255 4d43 4551 5a53 544b 5954 5947 | KYHWPURUMCEQZSTKYTYG
 5a50 4e5a 4158 4e5a 424d 0002 0022 0000 001e 5241 | ZPNZAXNZBM..."....RA
 4154 4c46 4756 4f42 5853 4b50 4454 4748 4246 4356 | ATLFGVOBXSKPDTGHBFCV
 4d4e 444b 5656 5652                               | MNDKVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    34 (x0022)
 0000 001e 4b59 4857 5055 5255 4d43 4551 5a53 544b | ....KYHWPURUMCEQZSTK
 5954 5947 5a50 4e5a 4158 4e5a 424d                | YTYGZPNZAXNZBM
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR



-- Update has been updated with old value.



-- Setup a replicat process to replicat these changes.


GGSCI (S66CJ187) 4> EDIT PARAMS REP_1


REPLICAT REP_1
END RUNTIME
USERID GGATE PASSWORD Gate_123
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rep_1.dsc, append
MAP GG_SOURCE.GG_SYNC_TBL1, TARGET GG_SOURCE.GG_SYNC_TBL1;


GGSCI (S66CJ187) 5> ADD REPLICAT REP_1, EXTTRAIL ./dirdat/RV
REPLICAT added.



GGSCI (S66CJ187) 6> START REPLICAT REP_1

Sending START request to MANAGER ...
REPLICAT REP_1 starting


GGSCI (S66CJ187) 10> INFO REP_1

REPLICAT   REP_1     Last Started 2014-01-19 09:27   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:36 ago)
Log Read Checkpoint  File ./dirdat/RV000000
                     2014-01-17 12:27:51.000000  RBA 109018




--- Extract from report file.


=============================================================
From Table GG_SOURCE.GG_SYNC_TBL1 to GG_SOURCE.GG_SYNC_TBL1:
       #                   inserts:         1
       #                   updates:         1
       #                   deletes:       500
       #                  discards:         0
=============================================================

As we can see from the snap above,

-- 500 insert becomes deletes
-- 1 delete becomes   insert
-- 1 update become update with before image.

-- Lets look at the db.


SQL> select count(*) from gg_source.gg_sync_tbl1;

  COUNT(*)
----------
      2000


- TRANLOG specifies the transaction log as the data source.
- GETUPDATEBEFORES is used to include before images of update records, which contain record details before an update (as opposed to after images).
- NOCOMPRESSDELETES causes Extract to send all column data to the output, instead of sending only the primary key. It enables deletes to be converted back to inserts.
- END RUNTIME causes the Extract or Replicat to terminate when it reaches process startup time.


======================================================================================================================================================================



V) Extract Process Defaults:-

a. Commited data only
b. Full images of inserts.
c. Only primary key and changed columns for update.
d. Only primary key for delete.
e. Only after image of update.

VI) Extract Parameter catagoreis

a) General
b) Processing Methods - When to start and end the process.
c) Database login
d) Selecting and Mapping data.
e) Routing Data
f) Formatting Data
g) Custom Processing  - Whether to invoke a user exit routine or a macro
h) Reporting
i) Error Handling - Contains records that cannot be processed and error handling for DDL extraction
j) Tuning  - Controls how long data is buffered before writing to a trail, and memory allocations.
k) Maintenance
l) Security


VII) Options on TRANSLOGOPTION.

a) TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT log_%t_%s_%r.arc   --> To specify differet format for archivelog.
b) TRANLOGOPTIONS ALTARCHIVELOGDEST /oradata/archive/log2 --> To specify differnet archive log location.
c) TRANLOGOPTIONS ARCHIVEDLOGONLY --> To specify gg to read only from the archive log (ALO)
d) TRANLOGOPTIONS EXCLUDEUSER ggsrep --> To execlude particular user from extract.
e) TRANLOGOPTIONS EXCLUDETRANS "ggs_repl" --> To specify the transaction name of the Replicat database user so that those transactions are not captured by Extract.


VIII) DBOPTIONS. (Database options).

a) SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS  - For Oracle databases to turn off triggers on the target database.
b) DEFERREFCONST -  Defer deferrable constraints.
c) FETCHBATCHSIZE

======================================================================================================================================================================


IX) Data Manipulation and Mapping
---------------------------------

a) Done by TABLE and MAP parameters.


Parameter

TABLE / MAP | Selects
WHERE | Table, Row,
FILTER |  Row operation, Range.


TABLE COLS | Columns
COLSEXCEPT |



Table selection -> MAP gg_source.gg_sync_tbl1, TARGET gg_target.gg_sync_tbl1_tgt;
Row   Selection -> WHERE (EMPNAME = "MANZOOR");
Operation Selection -> FILTER (ON UPDATE, ON DELETE, amount > 0);
Column Selection -> TABLE gg_source.gg_sync_tbl1 COLSEXCEPT (ENAME);


WHERE CLUASE
------------
-- Can appear on TABLE or MAP
-- Cannot use for arithmetic operation and refer to trail header and user token values.
-- Can perform evaluation as below.

Element Description Defined by Example
------------------- ------------------------ ---------------------
Columns COL_NAME ENAME
Comparision operator >, <, >=, <=, =, <> WHERE (ENAME = "MANZOOR")
Numeric values 38383.00 WHERE (EMPNO = 2)
Literal String "NAME", "CAR" WHERE (ENAME = "MANZOOR")
Field tests @NULL, @PRESENT, @ABSENT WHERE (ENAME = @NULL)  / WHERE ( EMPNO @PRESENT AND EMPNO <> @NULL)
Logical operator AND, OR WHERE ( EMPNO @PRESENT AND EMPNO <> @NULL)


FILTER CLAUSE
-------------
-- Used for complex include / exclude data selection.
-- Can deploy oracle GG built in function.
-- Can use multiple filter in one statement, if one filter fails the whole filter fails.
-- Include multiple option clause eg (ON INSERT / ON DELETE)
-- Raise a user defined error for error handling and processing. (FILTER RAISEERROR)

Eg.

FILTER ((PRODUCT_PRICE * PRODUCT_AMOUNT) > 10000); -- The following example includes rows where the price multiplied by the amount exceeds 10,000.
FILTER (@STRFIND(NAME, "JOE") > 0); -- The following example includes rows containing the string “JOE”:
FILTER (ON UPDATE, ON DELETE, @COMPUTE (PRODUCT_PRICE * PRODUCT_AMOUNT) > 10000); -- Executes the FILTER clause for both updates and deletes, but not inserts.



RANGE CLAUSE
------------
-- Used to divide the workload into mutiple randomly distributed data.
-- Same row will always processed by the same process group , it uses hash against the primary key or user defined columns.

Eg.

Replicat #1

MAP SALES.ACCOUNT,
TARGET SALES.ACCOUNT, FILTER (@RANGE (1,3));

Replicat #2
MAP SALES.ACCOUNT,
TARGET SALES.ACCOUNT, FILTER (@RANGE (2,3));

Replicat #3
MAP SALES.ACCOUNT,
TARGET SALES.ACCOUNT, FILTER (@RANGE (3,3));

Three Replicat processes, with each Replicat group processing one-third of the data in the GoldenGate trail based on the primary key.



TABLE SALES.ACCOUNT, FILTER (@RANGE (1,3,REP_ID));
TABLE SALES.ACCOUNT, FILTER (@RANGE (2,3,REP_ID));
TABLE SALES.ACCOUNT, FILTER (@RANGE (3,3,REP_ID));


Related by REP_ID, require three Replicats to handle the
transaction volumes.
By hashing the REP_ID column, related rows will always be processed to the same Replicat.



Building History
----------------

This is to record all the changes happend on the table, all the changes will be applied as an new insert using
INSERTALLRECORDS parameter.




This example uses special values to build a history of operations data:

INSERTALLRECORDS
MAP SALES.ACCOUNT, TARGET REPORT.ACCTHISTORY,
COLMAP (USEDEFAULTS,
TRAN_TIME = @GETENV("GGHEADER","COMMITTIMESTAMP"),
OP_TYPE = @GETENV("GGHEADER", "OPTYPE"),
BEFORE_AFTER_IND =
@GETENV("GGHEADER", "BEFOREAFTERINDICATOR"),
);



Oralce GG builtin Functions:-
---------------------------

FUNCTION  DEFINITION Example
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CASE   - Allows user to select a value depending on a series of value tests
EVAL - Allows a user to select a value depending on a series of independent tests
IF - Conditional checking AMT_COL = @IF (AMT > 0, AMT, 0)
COLSTAT - Tests whether a column value is missing, NULL, or invalid
COLTEST - Tests whether a column value is present, missing, NULL, or invalid
VALONEOF - STATE_COL = @IF (@VALONEOF ("CHENNAI","MADURAI"),"TN","OTHERS")
DATE - Returns date in variety of formats.
DATEDIFF - Returns diffenet between two date and times.
DATENOW - Returns current date.
COMPUTE - Returns the result of an arithmetic expression
NUMBIN - Convert binary string to number.
NUMSTR - Convert string to number.
STRCAT - Concatenates two or more strings. FULL_NAME = @STRCAT (LAST_NAME,",",FIRST_NAME);
STRCMP - Compares two strings to determine whether they are equal, or whether the first is less or greater than the second.
STREQ - Tests to see whether two strings are equal. Returns 1 for equal and 0 if not equal.
STREXT - Extracts selected characters from a string COUNTRY_CODE = @STREXT (PHONE, 1,2);
STRFIND - Finds the occurrence of a string within a string
STRLEN - Returns the length of a string.
STRLTRIM - Trims leading spaces in a column
STRNCAT - Concatenates one or more strings up to a specified number of characters per string
STRNCMP - Compares two strings up to a certain number of characters
STRNUM - Converts a number into a string, with justification and zerofill options
STRRTRIM - Trims trailing spaces in a column
STRSUB - Substitutes one string for another within a column
STRTRIM - Trims both leading and trailing spaces in a column
STRUP - Changes a string to uppercase
BINARY - Keeps source data in its original binary format in the target when source column is defined as character
BINTOHEX - Converts a binary string to a hexadecimal string
GETENV - Returns information on the GoldenGate environment, trail file header, trail record header, last replicated operation
 and lag. Can retrieve the commit timestamp in local time or GMT.
GETVAL - Extracts parameters from a stored procedure as input to a FILTER or COLMAP clause
HEXTOBIN - Converts a hexadecimal string to a binary string
HIGHVAL,LOWVAL  - Emulate COBOL functions that allow you to set a numeric limit on string or binary data types
RANGE - Divides a workload into multiple groups of data, while ensuring the same row will always be sent to the same process.
 RANGE uses a hash against primary key or user-defined columns.
TOKEN - Maps environmental values that are stored in the user token area to the target column.




SQLEXEC
-------

The SQLEXEC parameter extends GoldenGate capabilities by enabling Extract and Replicat to communicate with the
database through SQL queries or run stored procedures.
SQLEXEC also extends data integration beyond what can be done with GoldenGate functions.

A) Execute a stored procedure or SQL by query using the SQLEXEC clause of the TABLE or MAP parameter.
B) Use SQLEXEC at the root level (without input/output parameters) to call a stored procedure, run a SQL query, or issue a database command.

EG.

CREATE OR REPLACE PROCEDURE LOOKUP (CODE_PARAM IN VARCHAR2, DESC_PARAM OUT VARCHAR2) AS
BEGIN
SELECT DESC_COL INTO DESC_PARAM FROM LOOKUP_TABLE WHERE CODE_COL = CODE_PARAM;
END;

SYNTAX FOR SQLEXEC WITH STORED PRODCEDURE

SQLEXEC (SPNAME ,
[ID {PARAMS | NOPARAMS})


---------------------------------------------------------------------------------------


MAP HR.ACCOUNT, TARGET HR.NEWACCT, &
SQLEXEC (SPNAME lookup, PARAMS (code_param = account_code)), &
COLMAP (USEDEFAULTS, newacct_id = account_id, newacct_val = @GETVAL(lookup.desc_param));

---------------------------------------------------------------------------------------

Maps data from the ACCOUNT table to the NEWACCT table
When processing any rows from ACCOUNT, Extract  performs the LOOKUP stored procedure prior to executing the column map
Maps values returned in desc_param to the newacct_val column using the @GETVAL function.


the above result can also be achived by directly executing the query instead of stored procedure.

SYNTAX for SQLEXEC FOR EXECUTING THE QUERY.

SQLEXEC (ID , QUERY " ",
{PARAMS | NOPARAMS})

---------------------------------------------------------------------------------------
MAP HR.ACCOUNT, TARGET HR.NEWACCT, &
SQLEXEC (id lookup, &
query "select desc_param from lookup_table
where code_col = :code_param", &
PARAMS (code_param = account_code)), &
COLMAP (USEDEFAULTS, newacct_id = account_id,
newacct_val = @GETVAL(lookup.desc_param));
---------------------------------------------------------------------------------------


* When SQLEXEC is used as a stand-alone parameter statement in the Extract or Replicat parameter file, it can
execute a stored procedure, query, or database command.
* For these situations, SQLEXEC does not need to be tied to a specific table and can be used to perform general SQL operations.

SQLEXEC "EXEC SPNAME()"
SQLEXEC ""
SQLEXEC ""


Lets create an example using above method.


-- Source Tbl :-   SALES_MASTER
-- Target Tbls :-  SALES_INDIA, SALES_MALAYSIA,TOTAL_SALES_INDIA, TOTAL_SALES_MALAYSIA, EMP_DETAILS


Here we have one source table and it will get replicated to four target tables.


Source Table
------------

SQL> DESC GG_SOURCE.SALES_MASTER;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMNT                                          NUMBER
 COUNTRY                                            VARCHAR2(30)
 SALE_DATE                                          DATE





Target Tables:-
--------------


SQL> DESC GG_TARGET.SALES_INDIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMT                                           NUMBER
 SALE_DATE                                          DATE

SQL> DESC GG_TARGET.SALES_MALAYSIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMT                                           NUMBER
 SALE_DATE                                          DATE

SQL> DESC GG_TARGET.TOTAL_SALES_INDIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SALES_PROFIT                                       NUMBER
 SALES_DATE                                         DATE


SQL> DESC GG_TARGET.TOTAL_SALES_MALAYSIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SALES_PROFIT                                       NUMBER
 SALES_DATE                                         DATE

SQL> DESC GG_TARGET.EMP_DETAILS ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 EMP_NAME                                           VARCHAR2(64)
 SALES_COMMISION                                    NUMBER
 SALES_DATE                                         DATE



Setup the Extract Process.
--------------------------

GGSCI (S66CJ187) 8> EDIT PARAMS EXT_SALE


EXTRACT EXT_SALE
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE PASSWORD Gate_123
EXTTRAIL ./dirdat/SA
TABLE GG_SOURCE.SALES_MASTER;


GGSCI (S66CJ187) 11> ADD EXTRACT EXT_SALE , TRANLOG , BEGIN NOW
EXTRACT added.


GGSCI (S66CJ187) 12> ADD EXTTRAIL ./dirdat/SA, EXTRACT EXT_SALE
EXTTRAIL added.


GGSCI (S66CJ187) 13> START EXTRACT EXT_SALE

Sending START request to MANAGER ...
EXTRACT EXT_SALE starting

GGSCI (S66CJ187) 58> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:05
EXTRACT     RUNNING     EXT_SALE    00:00:00      00:00:04
EXTRACT     RUNNING     PMP1        00:00:00      00:00:08



Extract pump process
---------------------

GGSCI (S66CJ187) 71> EDIT PARAMS PMP_SALE


EXTRACT PMP_SALE
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTRAIL ./dirdat/SA
PASSTHRU
TABLE GG_SOURCE.SALES_MASTER;


GGSCI (S66CJ187) 60> ADD EXTRACT PMP_SALE, EXTTRAILSOURCE ./dirdat/SA
EXTRACT added.


GGSCI (S66CJ187) 61> ADD RMTTRAIL ./dirdat/SA, EXTRACT PMP_SALE
RMTTRAIL added.

GGSCI (S66CJ187) 63> START EXTRACT PMP_SALE

Sending START request to MANAGER ...
EXTRACT PMP_SALE starting


Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02
EXTRACT     RUNNING     EXT_SALE    00:00:00      00:00:10
EXTRACT     RUNNING     PMP1        00:00:00      00:00:07
EXTRACT     RUNNING     PMP_SALE    00:00:00      00:02:32



Replicat Process.
-----------------

-- Source Tbl :-   SALES_MASTER
-- Target Tbls :-  SALES_INDIA, SALES_MALAYSIA,TOTAL_SALES_INDIA, TOTAL_SALES_MALAYSIA, EMP_DETAILS


Lets create 5 Replicate process.

Process 1 :- RSAL_IND
---------------------

Source -

SQL> DESC GG_SOURCE.SALES_MASTER;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMNT                                          NUMBER
 COUNTRY                                            VARCHAR2(30)
 SALE_DATE                                          DATE





Target Tables:-
--------------


SQL> DESC GG_TARGET.SALES_INDIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMT                                           NUMBER
 SALE_DATE                                          DATE


a) Both the source and target columns are not same, lets use the defgen utility to create a source defintion.


GGSCI (S66CJ187) 1> EDIT PARAMS DEFGEN

DEFSFILE ./dirsql/sales_master.sql
USERID GGATE@NPTLPVT password Gate_123
TABLE  GG_SOURCE.SALES_MASTER;


$ defgen PARAMFILE ./dirprm/defgen.prm REPORTFILE ./dirrpt/defgen.rpt

$ cat sales_master.sql
*+- Defgen version 2.0, Encoding hp-roman8
*
* Definitions created/modified  2014-01-20 15:13
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table GG_SOURCE.SALES_MASTER
Record length: 262
Syskey: 0
Columns: 6
EMP_ID            64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
SOLD_PRODUCT      64     30       56  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
SOLD_PRODUCT_ID   64     50       92  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
SOLD_AMNT         64     50      148  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
COUNTRY           64     30      204  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
SALE_DATE        192     19      240  0  0 1 0     19     19     19 0 5 0 0 1    0 1 0
End of definition


-- scp the file to the target server.


SQL> DESC GG_TARGET.EMP_DETAILS ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 EMP_NAME                                           VARCHAR2(64)
 SALES_COMMISION                                    NUMBER
 SALES_DATE                                         DATE



SQL> create or replace procedure get_emp_name ( v_emp_id in number, v_emp_fir_name out varchar2, v_emp_sec_name out varchar2) as
    begin
       select EMP_FIRST_NAME, EMP_LAST_NAME into v_emp_fir_name , v_emp_sec_name from gg_target.emp_names where emp_id = v_emp_id;
    end;
    /


> EDIT PRAMS RSAL_IND

REPLICAT  RSAL_IND
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE Password Gate_123
SOURCEDEFS ./dirsql/sales_master.sql
DISCARDFILE ./dirrpt/RSAL_IND.dsc, append
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.SALES_INDIA, &
    COLMAP (USEDEFAULTS, SOLD_AMT = SOLD_AMNT) , &
    FILTER  ( @STRCMP(@STRUP(COUNTRY), "INDIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.SALES_MALAYSIA, &
    COLMAP  (USEDEFAULTS, SOLD_AMT = SOLD_AMNT), &
    FILTER  ( @STRCMP(@STRUP(COUNTRY), "MALAYSIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.TOTAL_SALES_INDIA, &
    COLMAP  (USEDEFAULTS, SALES_DATE = SALE_DATE, SALES_PROFIT = @COMPUTE (SOLD_AMNT * 0.1)), &
    FILTER  (@STRCMP(@STRUP(COUNTRY), "INDIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.TOTAL_SALES_MALAYSIA, &
    COLMAP  (USEDEFAULTS, SALES_DATE = SALE_DATE, SALES_PROFIT = @COMPUTE (SOLD_AMNT * 0.1)), &
    FILTER  (@STRCMP(@STRUP(COUNTRY), "MALAYSIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.EMP_DETAILS, &
    SQLEXEC  (SPNAME GET_EMP_NAME, PARAMS ( V_EMP_ID = EMP_ID)), &
    COLMAP   (USEDEFAULTS, SALES_DATE = SALE_DATE, EMP_NAME = @STRCAT (@GETVAL(GET_EMP_NAME.V_EMP_FIR_NAME), "," , @GETVAL (GET_EMP_NAME.V_EMP_SEC_NAME)), &
              SALES_COMMISION = @COMPUTE (SOLD_AMNT * 0.02));



> ADD REPLICAT RSAL_IND, EXTTRAIL ./dirdat/SA

> START REPLICAT RSAL_IND



-- Lets test this.. insert 3 records as below in source..



SQL> insert into sales_master values (1001,'SONY PC','2001',2000,'INDIA',SYSDATE);

1 row created.

SQL> insert into sales_master values (1002,'SONY TV',2002,1800,'MALAYSIA',SYSDATE);


1 row created.

SQL> SQL> insert into sales_master values (1003,'SONY IPAD',2003,1000,'india',SYSDATE);

1 row created.

SQL>  insert into sales_master values (1004,'SONY XBOX',2004,1500,'malaysia',sysdate);

1 row created.

SQL> commit;

Commit complete.


-- Below is the output on the five replicated tables.



SQL> select * from gg_target.emp_details;

    EMP_ID EMP_NAME                                                         SALES_COMMISION SALES_DAT
---------- ---------------------------------------------------------------- --------------- ---------
      1001 Manzoor,Ahamed                                                                40 20-JAN-14
      1002 Bhaskar,Suthradhar                                                            36 20-JAN-14
      1003 Ramesh,Ramasamy                                                               20 20-JAN-14
      1004 Jameel,Ahamed                                                                 30 20-JAN-14

SQL> select * from gg_target.sales_india;

    EMP_ID SOLD_PRODUCT                   SOLD_PRODUCT_ID   SOLD_AMT SALE_DATE
---------- ------------------------------ --------------- ---------- ---------
      1001 SONY PC                                   2001       2000 20-JAN-14
      1003 SONY IPAD                                 2003       1000 20-JAN-14

SQL> select * from gg_target.sales_malaysia;

    EMP_ID SOLD_PRODUCT                   SOLD_PRODUCT_ID   SOLD_AMT SALE_DATE
---------- ------------------------------ --------------- ---------- ---------
      1002 SONY TV                                   2002       1800 20-JAN-14
      1004 SONY XBOX                                 2004       1500 20-JAN-14

SQL> select * from gg_target.total_sales_india;

SOLD_PRODUCT                   SOLD_PRODUCT_ID SALES_PROFIT SALES_DAT
------------------------------ --------------- ------------ ---------
SONY PC                                   2001          200 20-JAN-14
SONY IPAD                                 2003          100 20-JAN-14

SQL> select * from gg_target.total_sales_malaysia;

SOLD_PRODUCT                   SOLD_PRODUCT_ID SALES_PROFIT SALES_DAT
------------------------------ --------------- ------------ ---------
SONY TV                                   2002          180 20-JAN-14
SONY XBOX                                 2004          150 20-JAN-14




============================================================================================================================

No comments:

Post a Comment