Manzoor's Blog - Stuffs on Oracle Database Administration: January 2014

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

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

Manzoor's Blog - Stuffs on Oracle Database Administration

Monday, January 20, 2014

OGG - Experiments

Followers

Blog Archive