Manzoor's Blog - Stuffs on Oracle Database Administration

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

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

Monday, January 13, 2014

ASSM and MSSM - Example

MSSM & ASSM (Manual & Automatic Segment Space Management)
---------------------------------------------------------

MSSM
----

a) The free space are maintained by the freelist.
b) Can define the PCTFREE AND PCTUSED parameter.

ASSM
----

a) The free space are mainitained by the bitmaps
b) Can define only the pctfree parameter.

Data blcok format.

A data block will contain the below divisions

a) Header
- The header contains general block information, such as the block address and the type of segment (for example, data or index).
b) Table directory
- This portion of the data block contains information about the table having rows in this block.
c) Row directory
- This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

the above 3 are collectively called as block overhead.

d) Free Space

- Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value).

In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries.
A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block.
The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.

e) Row data
- This portion of the data block contains table or index data. Rows can span blocks.

Eg:-

SQL> create tablespace TEST_ASSM datafile '/oracle/oradata/NTESTSRV/test_assm01.dbf' size 500m extent management local autoallocate segment space management auto;

Tablespace created.

SQL> create tablespace TEST_MSSM datafile '/oracle/oradata/NTESTSRV/test_mssm01.dbf' size 500m extent management local autoallocate segment space management manual;

Tablespace created.

SQL> alter user test_user1 quota unlimited on TEST_ASSM;

User altered.

SQL> alter user test_user1 quota unlimited on TEST_MSSM;

User altered.

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name in ('TEST_ASSM','TEST_MSSM');

TABLESPACE_NAME SEGMEN
------------------------- ------
TEST_ASSM AUTO
TEST_MSSM MANUAL

SQL> CONN TEST_USER1

SQL> CREATE TABLE "TEST_USER1"."TEST_MSSM_TBL"
( "EMPNO" NUMBER,
"ENAME" VARCHAR2(30),
"EAGE" NUMBER,
"GENDER" VARCHAR2(6),
"DEPT" VARCHAR2(30)
) PCTFREE 10 PCTUSED 50 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST_MSSM"
/

Table created.

SQL> CREATE TABLE "TEST_USER1"."TEST_ASSM_TBL"
( "EMPNO" NUMBER,
"ENAME" VARCHAR2(30),
"EAGE" NUMBER,
"GENDER" VARCHAR2(6),
"DEPT" VARCHAR2(30)
) PCTFREE 10 PCTUSED 50 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST_ASSM"
/

Table created.

-- In the above example we have created two tables, one table has been stored in the tablespace which used ASSM and another
on the tablespace which user MSSM.

We have specified the same value for block level and extent levels.

Let see the below data.

SQL> select table_name, PCT_FREE,PCT_USED, INITIAL_EXTENT, FREELISTS , tablespace_name from dba_tables where table_name in ('TEST_MSSM_TBL','TEST_ASSM_TBL');

TABLE_NAME PCT_FREE PCT_USED INITIAL_EXTENT FREELISTS TABLESPACE_NAME
------------------------------ ---------- ---------- -------------- ---------- -------------------------
TEST_ASSM_TBL 10 65536 TEST_ASSM
TEST_MSSM_TBL 10 50 65536 1 TEST_MSSM

As we can see from the above output the PCT_USED and FREELIST values are blank for TEST_ASSM_TBL i.e. the table which been stored on the
tablespace which uses ASSM.

MSSM
----
From the above we can say that the MSSM used the freelists to hold/manage the free blocks which can be used for the furture inserts, also
the PCT_USED parameter is used to define the percent of data that can hold in a block. Here we have mentioned as 50% which means
the block will be listed in the freelist when the data in that particular block is less than 50%. When it reaches the 50% the block address
will be removed from the freelist and this block will not be further used for the inserts. If the percent of data becomes less than
50% because of the deletes then again the block address will be added/listed in the freelist and will be used by the future inserts.

ASSM
---
The segments which are managed by automatic segmenet space management will be using the bitmaps to hold the details of available blocks
instead of freelists, also you cannot alter the pct_used value which means the data can grow upto the pct which is 100-pct_free,
which includes overheads and rowdata. Thus the block level fragmentation on these segments are highly avoided when you use the ASSM.

PCT_FREE - The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.
in the above example we have specified 10 for the PCT_FREE parameter which means 10% on each blcok will be reserved for the furture updates.

PCT_USED - The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.
After a data block is filled to the limit determined by PCTUSED, Oracle Database considers the block unavailable for the insertion of new rows until the
percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for
updates to rows already contained in the data block.

Row Chaining and Migrating

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit
into one data block when it is first inserted. In this case, Oracle Database stores the data for the row in a chain of data blocks (one or more)
reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining
in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the
block's free space is already completely filled. In this case, Oracle Database migrates the data for the entire row to a new data block, assuming
the entire row can fit in a new block. Oracle Database preserves the original row piece of a migrated row to point to the new block containing
the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle Database must scan more than one data block to
retrieve the information for the row.

Lets continue the example..

SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1;

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_MSSM_TBL 0 9 8

Here we can say the table has been allocated with 1 extent and it has 8 blocks.

Lets alter the table to hold more than 8k of data in one row..

SQL> alter table test_assm_tbl add (details varchar2(4000), full_details varchar2(4000));

Table altered.

SQL> alter table test_mssm_tbl add (details varchar2(4000), full_details varchar2(4000));

Table altered.

SQL> desc test_assm_tbl;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER
ENAME VARCHAR2(30)
EAGE NUMBER
GENDER VARCHAR2(6)
DEPT VARCHAR2(30)
DETAILS VARCHAR2(4000)
FULL_DETAILS VARCHAR2(4000)

SQL> desc test_mssm_tbl;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER
ENAME VARCHAR2(30)
EAGE NUMBER
GENDER VARCHAR2(6)
DEPT VARCHAR2(30)
DETAILS VARCHAR2(4000)
FULL_DETAILS VARCHAR2(4000)

-- Now we have added to more columns with varchar2(4000).

Lets insert some values.

SQL> begin
for i in 1..1000 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',4000));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',4000));
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

-- Now we have inserted 1000 rows in each table.. lets see the block utilization for both the tables.

SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_ASSM_TBL 2 25 8
TEST_ASSM_TBL 3 33 8
TEST_ASSM_TBL 4 41 8
TEST_ASSM_TBL 5 49 8
TEST_ASSM_TBL 6 57 8
TEST_ASSM_TBL 7 65 8
TEST_ASSM_TBL 8 73 8
TEST_ASSM_TBL 9 81 8
TEST_ASSM_TBL 10 89 8
TEST_ASSM_TBL 11 97 8
TEST_ASSM_TBL 12 105 8
TEST_ASSM_TBL 13 113 8
TEST_ASSM_TBL 14 121 8
TEST_ASSM_TBL 15 129 8
TEST_ASSM_TBL 16 137 128
TEST_ASSM_TBL 17 265 128
TEST_ASSM_TBL 18 393 128
TEST_ASSM_TBL 19 521 128
TEST_ASSM_TBL 20 649 128
TEST_ASSM_TBL 21 777 128
TEST_ASSM_TBL 22 905 128
TEST_ASSM_TBL 23 1033 128
TEST_ASSM_TBL 24 1161 128
TEST_ASSM_TBL 25 1289 128
TEST_ASSM_TBL 26 1417 128
TEST_ASSM_TBL 27 1545 128
TEST_ASSM_TBL 28 1673 128
TEST_ASSM_TBL 29 1801 128
TEST_ASSM_TBL 30 1929 128
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
TEST_MSSM_TBL 2 25 8
TEST_MSSM_TBL 3 33 8
TEST_MSSM_TBL 4 41 8
TEST_MSSM_TBL 5 49 8
TEST_MSSM_TBL 6 57 8
TEST_MSSM_TBL 7 65 8
TEST_MSSM_TBL 8 73 8
TEST_MSSM_TBL 9 81 8
TEST_MSSM_TBL 10 89 8
TEST_MSSM_TBL 11 97 8
TEST_MSSM_TBL 12 105 8
TEST_MSSM_TBL 13 113 8
TEST_MSSM_TBL 14 121 8
TEST_MSSM_TBL 15 129 8
TEST_MSSM_TBL 16 137 128
TEST_MSSM_TBL 17 265 128
TEST_MSSM_TBL 18 393 128
TEST_MSSM_TBL 19 521 128
TEST_MSSM_TBL 20 649 128
TEST_MSSM_TBL 21 777 128
TEST_MSSM_TBL 22 905 128
TEST_MSSM_TBL 23 1033 128
TEST_MSSM_TBL 24 1161 128
TEST_MSSM_TBL 25 1289 128
TEST_MSSM_TBL 26 1417 128
TEST_MSSM_TBL 27 1545 128
TEST_MSSM_TBL 28 1673 128
TEST_MSSM_TBL 29 1801 128
TEST_MSSM_TBL 30 1929 128

-- Around 30 extents has been used, also since the tablespace extement managment is local and auto allocate the size of the
extents been automatically dedicded by oracle as we can see from the extent id 16 the no. of blocks shows 128.

SQL> select segment_name, bytes/1024/1024 "MB" from dba_segments where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');

SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
TEST_ASSM_TBL 16
TEST_MSSM_TBL 16

The current size of each segment is 16 MB.

SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_ASSM_TBL');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_MSSM_TBL');

PL/SQL procedure successfully completed.

SQL> select table_name, avg_row_len/1024 "K", (avg_row_len*num_rows)/1024/1024 "M" from dba_tables where table_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');

TABLE_NAME K M
------------------------------ ---------- ----------
TEST_ASSM_TBL 7.85546875 7.6713562
TEST_MSSM_TBL 7.85546875 7.6713562

The avg size of 1 record is 7.8 KB, and actual space required to store 1000 records of size 7.6 KB rows is 7.67 MB, but the
segment size shows 16 MB, which is more than 50% , why is that so?

As discussed earlier, in each block 10% space will be allocated for free space. Lets calculate..

SQL> select segment_name, sum(blocks) "Total Blocks", sum(blocks)*8192/1024/1024 "Blocks Size in MB" from dba_extents where segment_name
in ('TEST_ASSM_TBL','TEST_MSSM_TBL') group by segment_name;

SEGMENT_NAME Total Blocks Blocks Size in MB
--------------------------------------------------------------------------------- ------------ -----------------
TEST_MSSM_TBL 2048 16
TEST_ASSM_TBL 2048 16

So 10% on 16MB is 1.6 MB, so 7.67 MB + 1.6 MB = 9.27 MB,

16 mb - 9.27 mb = 6.73

Still we could see 6.73 mb of high space usage. Lets find out..

Ok..

Lets see how many empty blocks are there...

the ROWID_BLOCK_NUMBER function is used to return just the block number in the table.
Lets display the data for the first 10 rows.

SQL> select rowid, dbms_rowid.rowid_block_number(rowid) from test_assm_tbl where empno <= 10;

ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAN3vAAHAAAAAOAAA 14
AAAN3vAAHAAAAAQAAA 16
AAAN3vAAHAAAAARAAA 17
AAAN3vAAHAAAAASAAA 18
AAAN3vAAHAAAAAVAAA 21
AAAN3vAAHAAAAAXAAA 23
AAAN3vAAHAAAAAbAAA 27
AAAN3vAAHAAAAAcAAA 28
AAAN3vAAHAAAAAeAAA 30
AAAN3vAAHAAAAAgAAA 32

10 rows selected.

SQL> select rowid, dbms_rowid.rowid_block_number(rowid) from test_mssm_tbl where empno <= 10;

ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAN3wAAIAAAAALAAA 11
AAAN3wAAIAAAAANAAA 13
AAAN3wAAIAAAAAPAAA 15
AAAN3wAAIAAAAARAAA 17
AAAN3wAAIAAAAATAAA 19
AAAN3wAAIAAAAAVAAA 21
AAAN3wAAIAAAAAXAAA 23
AAAN3wAAIAAAAAZAAA 25
AAAN3wAAIAAAAAbAAA 27
AAAN3wAAIAAAAAdAAA 29

10 rows selected.

As from the above we could see one row is placed in two blocks...

eg .. the rowid AAAN3wAAIAAAAALAAA shows the block number as 11, the subsequence rowid shows as 13,
which means the data of rowid AAAN3wAAIAAAAALAAA is not enough to fit in to one blocks and been
placed into two blocks.

The size of one row is 7.85 kb which cannot be fit in to one block hence oracle used two blocks to store 1 rec.
Which is the chanined rows.. lets see..

SQL > create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);

SQL> analyze table test_assm_tbl list chained rows;

Table analyzed.

SQL> analyze table test_mssm_tbl list chained rows;

Table analyzed.

SQL> select table_name, count(*) from chained_rows group by table_name;

TABLE_NAME COUNT(*)
------------------------------ ----------
TEST_MSSM_TBL 1000
TEST_ASSM_TBL 1000

--Yes...as we could see it is beacuse of the chanied rows, this is mainly becuase of the size of 1 row which is 7.85 kb,

-- lets delete all the rows now..

SQL> delete from TEST_MSSM_TBL ;

1000 rows deleted.

SQL> delete from TEST_ASSM_TBL;

1000 rows deleted.

SQL> commit;

Commit complete.

-- Here we have delete all the rows now the freelists will be updated for MSSM and bitmaps been upated for ASSM.

Lets insert data which can fit in 1 block.

SQL> begin
for i in 1..1000 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',3000));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',3000));
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_ASSM_TBL');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_MSSM_TBL');

PL/SQL procedure successfully completed.

SQL> select table_name, avg_row_len/1024 "K", (avg_row_len*num_rows)/1024/1024 "M" from dba_tables where table_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');

TABLE_NAME K M
------------------------------ ---------- ----------
TEST_ASSM_TBL 6.87890625 6.71768188
TEST_MSSM_TBL 6.87890625 6.71768188

SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_ASSM_TBL 2 25 8
TEST_ASSM_TBL 3 33 8
TEST_ASSM_TBL 4 41 8
TEST_ASSM_TBL 5 49 8
TEST_ASSM_TBL 6 57 8
TEST_ASSM_TBL 7 65 8
TEST_ASSM_TBL 8 73 8
TEST_ASSM_TBL 9 81 8
TEST_ASSM_TBL 10 89 8
TEST_ASSM_TBL 11 97 8
TEST_ASSM_TBL 12 105 8
TEST_ASSM_TBL 13 113 8
TEST_ASSM_TBL 14 121 8
TEST_ASSM_TBL 15 129 8
TEST_ASSM_TBL 16 137 128
TEST_ASSM_TBL 17 265 128
TEST_ASSM_TBL 18 393 128
TEST_ASSM_TBL 19 521 128
TEST_ASSM_TBL 20 649 128
TEST_ASSM_TBL 21 777 128
TEST_ASSM_TBL 22 905 128
TEST_ASSM_TBL 23 1033 128
TEST_ASSM_TBL 24 1161 128
TEST_ASSM_TBL 25 1289 128
TEST_ASSM_TBL 26 1417 128
TEST_ASSM_TBL 27 1545 128
TEST_ASSM_TBL 28 1673 128
TEST_ASSM_TBL 29 1801 128
TEST_ASSM_TBL 30 1929 128
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
TEST_MSSM_TBL 2 25 8
TEST_MSSM_TBL 3 33 8
TEST_MSSM_TBL 4 41 8
TEST_MSSM_TBL 5 49 8
TEST_MSSM_TBL 6 57 8
TEST_MSSM_TBL 7 65 8
TEST_MSSM_TBL 8 73 8
TEST_MSSM_TBL 9 81 8
TEST_MSSM_TBL 10 89 8
TEST_MSSM_TBL 11 97 8
TEST_MSSM_TBL 12 105 8
TEST_MSSM_TBL 13 113 8
TEST_MSSM_TBL 14 121 8
TEST_MSSM_TBL 15 129 8
TEST_MSSM_TBL 16 137 128
TEST_MSSM_TBL 17 265 128
TEST_MSSM_TBL 18 393 128
TEST_MSSM_TBL 19 521 128
TEST_MSSM_TBL 20 649 128
TEST_MSSM_TBL 21 777 128
TEST_MSSM_TBL 22 905 128
TEST_MSSM_TBL 23 1033 128
TEST_MSSM_TBL 24 1161 128
TEST_MSSM_TBL 25 1289 128
TEST_MSSM_TBL 26 1417 128
TEST_MSSM_TBL 27 1545 128
TEST_MSSM_TBL 28 1673 128
TEST_MSSM_TBL 29 1801 128
TEST_MSSM_TBL 30 1929 128

62 rows selected.

-- so no new extents has been allocated, all the space reclaimed by the delete has been used.

SQL> truncate table chained_rows;

Table truncated.

SQL> analyze table test_assm_tbl list chained rows;

Table analyzed.

SQL> analyze table test_mssm_tbl list chained rows;

Table analyzed.

SQL> select table_name, count(*) from chained_rows group by table_name;

no rows selected

-- Now we could see there are no any chained rows as the once record is fit into one block.

Lets see the use of assm..

SQL> truncate table TEST_ASSM_TBL;

Table truncated.

SQL> truncate table TEST_MSSM_TBL;

Table truncated.

begin
for i in 10..60 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
end loop;
commit;
end;
/

SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_MSSM_TBL 0 9 8

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 8
13 8
14 8
15 8
16 8
17 8
20 3

7 rows selected.

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 8
11 8
12 8
13 8
14 8
15 8
16 3

-- Around 8 records are stored in one block. Let delete 3 row from each block.

-- Now could see around 2 rows are stored in 1 block. Lets try to delete one rec from each block.

begin
for i in 10..60 loop
if i mod 3 = 0 then
delete from test_assm_tbl where empno = i;
delete from test_mssm_tbl where empno = i;
end if;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 5
13 6
14 5
15 5
16 6
17 5
20 2

7 rows selected.

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 6
11 5
12 5
13 6
14 5
15 5
16 2

7 rows selected.

Now the blocks will be fragmented becuase of the delete operation. Lets add some data.

begin
for i in 10..60 loop
if i mod 3 = 0 then
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
end if;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 5
13 6
14 5
15 8
16 8
17 8
18 8
19 1
20 2

9 rows selected.

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 6
11 5
12 5
13 6
14 5
15 5
16 8
17 8
18 3

9 rows selected.

-- As we can see from the above in test_assm_tbl (The segment managed by ASSM) the new rows inserted in the old blocks i.e. even if we have
some space in the block it will used by the inserts.

But for the table test_mssm_tbl (Manual Segment space Management) the new rows are not inserted in the old blocks instead those are inserted
in the new blocks, it is because we have specified by pct_used parameter to 50, since already the block is holding some value which is
greater than the 50 % hence these blocks are removed from the freelists. Thus ASSM help in avoiding the fragmention.

Manzoor's Blog - Stuffs on Oracle Database Administration

Monday, January 20, 2014

OGG - Experiments

Monday, January 13, 2014

ASSM and MSSM - Example

Followers

Blog Archive