Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Configuring datapump extract in Oracle GoldenGate (OGG) 11.2

Configuring datapump extract in Oracle GoldenGate (OGG) 11.2

In the article you will have a look at an example of how to configure an additional datapump extract in OGG. How are OGG installation performed and basic classic CDC extract and replicat configured and verified is described here.

The article will cover the following topics

  • What is a data pump extract
  • Example of setting a data pump extract
  1. What is a data pump extract

With a single extract and corresponding replicats configuration as discussed previously here
the extract was responsible for capturing the changes from the transaction log on the source system, it was a classic extract, and shipping it to a remote trail on the target system via TCP/IP. The replicat(s) were reading from the local trail files and applying the changes to the target database(s).

Data pump extract is an optional extract that is created and started on the source system. Witch a data pump extract present the processing changes as follows. The primary extract still captures the changes from the transaction log of the primary database but writes it to a local trail file on the source system, instead of to a remote trail on the target system as without a data pump extract. Data pump extract reads data from the local trail on the source system and sends it to the remote trail on the target system using TCP/IP. The replicat in both cases, with or without a data pump, reads data from the local trail on the target and applies it to the target database.

The advantage offered by data pump extract is that

  • Network failure between source and target systems are tolerated without primary extract abending, in case of network failure, by having data on a trail on the source system and a dedicated data pump extract for data shipment to the target system
  • Data pump extract can perform extra processing on the data such as filtering, mapping etc and pass the data processed to the extract. The data pump can also operate in a passthru mode where only transmits the data as it is. Data pump extract does not require a log on to the source database.
  1. Example of setting a data pump extract

On the source system I have sales schema in RACD database. On the target system I have sales schema in RACDB database. In the sales schema on both sites there are two tables named sales and cust.

create table sales

(

product_id number(20) primary key,

product varchar2(50),

channel_id number,

cust_id number,

amount_sold number(10,2),

time_id date)

partition by list (channel_id)

(partition c0 values (0),

partition c1 values (1),

partition c2 values (2),

partition c3 values (3),

partition c4 values (4)

);

create table cust

(

cust_id     number(20) primary key,

name varchar2(50),

address varchar2(4000),

bank_account number,

state varchar2(2),

country varchar2(50));

I am populating the source with the following statements. The sales table is with 1 Mil records and cust table is with 20K records.

insert /*+ append */ into sales

select

rownum,

‘Something ||mod(rownum,100000) as product,

mod(rownum,5) as channel_id,

mod(rownum,1000) as cust_id ,

5000 as amount_sold,

to_date

(’01.’ || lpad(to_char(mod(rownum,12)+1),2,’0′) || ‘.2010′ ,’dd.mm.yyyy’)

as time_id

from dual connect by level<=1e6;

insert /*+ append */ into cust

select

rownum,

‘Customer ‘||mod(rownum,100000) as name,

‘Address ‘||mod(rownum,100000) as address,

mod(rownum,5) as bank_account,

‘CA’ as state ,

‘Country ‘||mod(rownum,10) as country

from dual connect by level<=20000;

The architecture will be summarized as follows:

Source Target
Schema/Table sales.salessales.cust sales.salessales.cust
Database RACD RACDB
DB users ddl_oggogg_extract

ogg_replicat

ddl_oggogg_extract

ogg_replicat

 

Extract   groups extsale
Extract   groups pumpsale
Replicat   groups repsale
Replicat   groups repcust
Extrail ./dirdat/zz
Rmttrail ./dirdat/yy

Setting a replication between sites requires

  • Install OGG on both sites and configure managers – ( see the OGG install )
  • Set CDC – start CDC extract
  • Perform an initial dataload (OGG based is discussed in the article)
  • Start data apply – start the replicat to apply changes captured by CDC
  • Set NOHANDLECOLLISIONS on the target replicats

Note. Special thanks to Chris Tijerina who pointed out that HANDLECOLLISIONS (HC) is required only if using an OGG for the initial dataload. However, if you use SCN based exp(dp)/imp(dp) or rman to build a point in time copy on the target from the source DB than you can skip setting the HC. For Oracle source and target database imp(dp)/expdp) pump or rman is much faster way to initially move the data than OGG native methods. OGG native initial data load methods are very valuable in heterogeneous data transfers. I will blog about this no HC topic later but the idea is as follows:

  • Start the primary extract getting CDC from the source database;
  • Make sure that there is no long running transaction. Query until none or kill the transaction.
  • Query the database for the SQL and write the SCN down
  • Make sure that exp/imp or rman restore on the target system is done
  • Use the following OGG syntax to start the replicat on the target system: start replicat afterscn <the SCN you got from source earlier>

When replicats start they will parse through the trail files and will start applying the data with next SCN right after the SCN for the imp(dp)/exp(dp) or rman restore or duplicate.

For CDC capture from Oracle source database you will need to perform the following steps in an orderly manner.

  1. Add supplemental logging at database level

SQL> alter database add supplemental log data;

Database altered.

SQL>

  1. Add supplemental logging at table level

GGSCI (raclinux1.gj.com) 34> dblogin userid ddl_ogg

Password:

Successfully logged into database.

GGSCI (raclinux1.gj.com) 35>

GGSCI (raclinux1.gj.com) 36> add trandata sales.sales

Logging of supplemental redo data enabled for table SALES.SALES.

GGSCI (raclinux1.gj.com) 37> add trandata sales.cust

Logging of supplemental redo data enabled for table SALES.CUST.

GGSCI (raclinux1.gj.com) 38>

  1. Add CDC extract extsale, create parameter file for extsale extract, add ext trail ./dir.dat/zz for extsale extract and start extract extsale

GGSCI (raclinux1.gj.com) 53> view param extsale

extract extsale

tranlogoptions asmuser sys@ASM, asmpassword sys1

–checkparams

–NODYNAMICRESOLUTION

userid ogg_extract, password ogg_extract

exttrail ./dirdat/zz

table sales.sales;

table sales.cust;

GGSCI (raclinux1.gj.com) 54>

GGSCI (raclinux1.gj.com) 47> add extract extsale, tranlog, begin now, threads 2

EXTRACT added.

GGSCI (raclinux1.gj.com) 48>

GGSCI (raclinux1.gj.com) 54> add exttrail ./dirdat/zz, extract extsale megabytes 500

EXTTRAIL added.

GGSCI (raclinux1.gj.com) 55>

GGSCI (raclinux1.gj.com) 58> start extract extsale

Sending START request to MANAGER …

EXTRACT EXTSALE starting

GGSCI (raclinux1.gj.com) 59> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXTCDC 00:00:00 00:00:09

EXTRACT RUNNING EXTSALE 00:00:00 00:09:16

REPLICAT RUNNING REPCDC1 00:00:00 00:00:08

REPLICAT RUNNING REPCDC2 00:00:00 00:00:06

REPLICAT RUNNING REPCDC3 00:00:00 00:00:04

GGSCI (raclinux1.gj.com) 60>

  1. Add a pump extract (pumpsale) and start it

GGSCI (raclinux1.gj.com) 63> view params pumpsale

extract pumpsale

passthru

rmthost raclinux1, mgrport 7809

rmttrail ./dirdat/yy

table sales.sales;

table sales.cust;

GGSCI (raclinux1.gj.com) 64>

GGSCI (raclinux1.gj.com) 64> add extract pumpsale, exttrailsource ./dirdat/zz

EXTRACT added.

GGSCI (raclinux1.gj.com) 65>

GGSCI (raclinux1.gj.com) 66> add rmttrail ./dirdat/yy, extract pumpsale megabytes 500

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 67>

GGSCI (raclinux1.gj.com) 67> start extract pumpsale

Sending START request to MANAGER …

EXTRACT PUMPSALE starting

GGSCI (raclinux1.gj.com) 68> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXTCDC 00:00:00 00:00:02

EXTRACT RUNNING EXTSALE 00:00:00 00:00:02

EXTRACT RUNNING PUMPSALE 00:00:00 00:02:48

REPLICAT RUNNING REPCDC1 00:00:00 00:00:06

REPLICAT RUNNING REPCDC2 00:00:00 00:00:05

REPLICAT RUNNING REPCDC3 00:00:00 00:00:03

GGSCI (raclinux1.gj.com) 69>

e. Create replicats resale and repcust. DO NOT START the replicats before completing initial data load

GGSCI (raclinux1.gj.com) 71> view params repsale

replicat repsale

userid ogg_replicat, password ogg_replicat

handlecollisions

assumetargetdefs

discarfile ./dirrpt/sale.dsc, append

map sales.sales, target sales.sales;

GGSCI (raclinux1.gj.com) 72>

GGSCI (raclinux1.gj.com) 73> view params repcust

replicat repcust

userid ogg_replicat, password ogg_replicat

handlecollisions

assumetargetdefs

discarfile ./dirrpt/sale.dsc, append

map sales.cust, target sales.cust;

GGSCI (raclinux1.gj.com) 74>

GGSCI (raclinux1.gj.com) 74> add replicat repsale, exttrail ./dirdat/yy

REPLICAT added.

GGSCI (raclinux1.gj.com) 75> add replicat repcust, exttrail ./dirdat/yy

REPLICAT added.

GGSCI (raclinux1.gj.com) 76>

  1. Initial dataload ( for details see here). Create a task extract and a replicat with the following parameters and options for data collection (SOURCEISTABLE) and no checkpoint operation (SPACIALRUN).Start only the extract but not the replicat and wait for the completion.

GGSCI (raclinux1.gj.com) 8> view params initexts

extract initexts

SETENV (ORACLE_SID = “RACD1”)

—tranlogoptions asmuser sys@ASM, asmpassword sys1

userid ogg_extract, password ogg_extract

rmthost raclinux1, mgrport 7809

rmttask replicat, group initreps

table sales.sales;

table sales.cust;

GGSCI (raclinux1.gj.com) 9> add extract initexts, sourceistable

EXTRACT added.

GGSCI (raclinux1.gj.com) 10>

GGSCI (raclinux1.gj.com) 8> view params initreps

replicat initreps

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

assumetargetdefs

discardfile ./dirrpt/sales_schema.dsc, purge

map sales.sales, target sales.sales;

map sales.cust, target sales.cust;

GGSCI (raclinux1.gj.com) 9>

GGSCI (raclinux1.gj.com) 9> add replicat initreps, specialrun

REPLICAT added.

GGSCI (raclinux1.gj.com) 10>

GGSCI (raclinux1.gj.com) 10> start extract initexts

Sending START request to MANAGER …

EXTRACT INITEXTS starting

GGSCI (raclinux1.gj.com) 11>

GGSCI (raclinux1.gj.com) 40> view report initexts

2012-07-08 04:50:42 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************

Oracle GoldenGate Capture for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16

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

Starting at 2012-07-08 04:50:42

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Thu Sep 3 04:15:13 EDT 2009, Release 2.6.18-164.el5

Node: raclinux1.gj.com

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 14866

Description:

***********************************************************************

** Running with the following parameters **

***********************************************************************

2012-07-08 04:50:42 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

extract initexts

SETENV (ORACLE_SID = “RACD1”)

Set environment variable (ORACLE_SID=RACD1)

userid ogg_extract, password ***********

2012-07-08 04:50:42 INFO OGG-03500 WARNING: NLS_LANG environment variable does not match database character set, or not set. U

sing database character set value of AL32UTF8.

rmthost raclinux1, mgrport 7809

rmttask replicat, group initreps

table sales.sales;

Using the following key columns for source table SALES.SALES: PRODUCT_ID.

table sales.cust;

Using the following key columns for source table SALES.CUST: CUST_ID.

2012-07-08 04:50:43 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u02/stage_ogg112_ora11/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 64G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 128G

CACHESIZEMAX (strict force to disk): 96G

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

Database Language and Character Set:

NLS_LANG = “.AL32UTF8”

NLS_LANGUAGE = “AMERICAN”

NLS_TERRITORY = “AMERICA”

NLS_CHARACTERSET = “AL32UTF8”

Processing table SALES.SALES

Processing table SALES.CUST

***********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2012-07-08 10:32:26 (activity since 2012-07-08 04:50:42)

Output to initreps:

From Table SALES.SALES:

# inserts: 1000000

# updates: 0

# deletes: 0

# discards: 0

From Table SALES.CUST:

# inserts: 20000

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 175694478

GGSCI (raclinux1.gj.com) 41>

GGSCI (raclinux1.gj.com) 44> view report initreps

***********************************************************************

Oracle GoldenGate Delivery for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:48:07

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

Starting at 2012-07-08 04:50:47

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Thu Sep 3 04:15:13 EDT 2009, Release 2.6.18-164.el5

Node: raclinux1.gj.com

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 14879

Description:

***********************************************************************

** Running with the following parameters **

***********************************************************************

2012-07-08 04:50:57 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

replicat initreps

SETENV (ORACLE_SID = “RACDB1”)

Set environment variable (ORACLE_SID=RACDB1)

userid ogg_replicat, password ************

2012-07-08 04:50:57 INFO OGG-03501 WARNING: NLS_LANG environment variable is invalid or not set. Using operating system character

set value of AL32UTF8.

assumetargetdefs

discardfile ./dirrpt/sales_schema.dsc, purge

map sales.sales, target sales.sales;

map sales.cust, target sales.cust;

2012-07-08 04:50:58 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u02/stage_ogg112_ora11/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 2G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 4G

CACHESIZEMAX (strict force to disk): 3.41G

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

Database Language and Character Set:

NLS_LANG = “.AL32UTF8”

NLS_LANGUAGE = “AMERICAN”

NLS_TERRITORY = “AMERICA”

NLS_CHARACTERSET = “AL32UTF8”

***********************************************************************

** Run Time Messages **

***********************************************************************

MAP resolved (entry sales.sales):

map “SALES”.”SALES”, target sales.sales;

Using following columns in default map by name:

PRODUCT_ID, PRODUCT, CHANNEL_ID, CUST_ID, AMOUNT_SOLD, TIME_ID

Using the following key columns for target table SALES.SALES: PRODUCT_ID.

MAP resolved (entry sales.cust):

map “SALES”.”CUST”, target sales.cust;

Using following columns in default map by name:

CUST_ID, NAME, ADDRESS, BANK_ACCOUNT, STATE, COUNTRY

Using the following key columns for target table SALES.CUST: CUST_ID.

***********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2012-07-08 10:32:31 (activity since 2012-07-08 04:51:01)

From Table SALES.SALES to SALES.SALES:

# inserts: 1000000

# updates: 0

# deletes: 0

# discards: 0

From Table SALES.CUST to SALES.CUST:

# inserts: 20000

# updates: 0

# deletes: 0

# discards: 0

CACHE OBJECT MANAGER statistics

CACHE MANAGER VM USAGE

vm current = 0 vm anon queues = 0

vm anon in use = 0 vm file = 0

vm used max = 0 ==> CACHE BALANCED

CACHE CONFIGURATION

cache size = 2G cache force paging = 3.41G

buffer min = 64K buffer highwater = 8M

pageout eligible size = 8M

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

RUNTIME STATS FOR SUPERPOOL

CACHE Transaction Stats

trans active = 0 max concurrent = 0

non-zero total = 0 trans total = 0

CACHE File Caching

disk current = 0 disk total = 0

disk caching = 0 file cached = 0

file retrieves = 0

CACHE MANAGEMENT

buffer links = 0 anon gets = 0

forced unmaps = 0 cnnbl try = 0

cached out = 0 force out = 0

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

Cached Transaction Size Distribution

0: 0

< 4K: 0

4K: 0 0 | 16K: 0 0

64K: 0 0 | 256K: 0 0

1M: 0 0 | 4M: 0 0

16M: 0 0 | 64M: 0 0

256M: 0 0 | 1G: 0 0

4G: 0 0 | 16G: 0 0

64G: 0 0 | 256G: 0 0

1T: 0 0 | 4T: 0 0

16T: 0 0 | 64T: 0 0

256T: 0 0 |1024T: 0 0

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

CUMULATIVE STATS FOR SUPERPOOL

CACHE Transaction Stats

trans active = 0 max concurrent = 0

non-zero total = 0 trans total = 0

CACHE File Caching

disk current = 0 disk total = 0

disk caching = 0 file cached = 0

file retrieves = 0

CACHE MANAGEMENT

buffer links = 0 anon gets = 0

forced unmaps = 0 cnnbl try = 0

cached out = 0 force out = 0

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

Cached Transaction Size Distribution

0: 0

< 4K: 0

4K: 0 0 | 16K: 0 0

64K: 0 0 | 256K: 0 0

1M: 0 0 | 4M: 0 0

16M: 0 0 | 64M: 0 0

256M: 0 0 | 1G: 0 0

4G: 0 0 | 16G: 0 0

64G: 0 0 | 256G: 0 0

1T: 0 0 | 4T: 0 0

16T: 0 0 | 64T: 0 0

256T: 0 0 |1024T: 0 0

QUEUE Statistics:

num queues = 15 default index = 0

cur len = 0 max len = 0

q vm current = 0 vm max = 0

q hits = 0 q misses = 0

queue size q hits curlen maxlen cannibalized

0 64K 0 0 0 0

1 128K 0 0 0 0

2 256K 0 0 0 0

3 512K 0 0 0 0

4 1M 0 0 0 0

5 2M 0 0 0 0

6 4M 0 0 0 0

7 8M 0 0 0 0

8 16M 0 0 0 0

9 32M 0 0 0 0

10 64M 0 0 0 0

11 128M 0 0 0 0

12 256M 0 0 0 0

13 512M 0 0 0 0

14 1G 0 0 0 0

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

RUNTIME STATS FOR CACHE POOL #0

POOL INFO group: initreps id: p14879_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

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

CUMULATIVE STATS FOR CACHE POOL #0

POOL INFO group: initreps id: p14879_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

QUEUE Statistics:

num queues = 15 default index = 0

cur len = 0 max len = 0

q vm current = 0 vm max = 0

q hits = 0 q misses = 0

queue size q hits curlen maxlen cannibalized

0 64K 0 0 0 0

1 128K 0 0 0 0

2 256K 0 0 0 0

3 512K 0 0 0 0

4 1M 0 0 0 0

5 2M 0 0 0 0

6 4M 0 0 0 0

7 8M 0 0 0 0

8 16M 0 0 0 0

9 32M 0 0 0 0

10 64M 0 0 0 0

11 128M 0 0 0 0

12 256M 0 0 0 0

13 512M 0 0 0 0

14 1G 0 0 0 0

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

RUNTIME STATS FOR CACHE POOL #0

POOL INFO group: initreps id: p14879_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

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

CUMULATIVE STATS FOR CACHE POOL #0

POOL INFO group: initreps id: p14879_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

GGSCI (raclinux1.gj.com) 45>

  1. Start all replicates (repsale and repcust)

    ggsci>start replicat repsale

    ggsci>start replicat repcust

  2. Verify that OGG setup capture data from source and replicate it to the target tables.

    I am going to perform a transaction on each of the tables.

    Let’s change all 20000 records on cust source table and see how OGG behaves and propagates the data from source database to the target database.

    update cust set country=’USA’;

    commit;

    Check the stats on extract extsale, extract pumpsale and replicat resale to verify the architecture and that transactions are processed as expected.

    GGSCI (raclinux1.gj.com) 68> stats extract extsale

    Sending STATS request to EXTRACT EXTSALE …

    Start of Statistics at 2012-07-08 14:43:23.

    Output to ./dirdat/zz:

    Extracting from SALES.CUST to SALES.CUST:

    *** Total statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Daily statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Hourly statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Latest statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 69>

    GGSCI (raclinux1.gj.com) 72> stats extract pumpsale

    Sending STATS request to EXTRACT PUMPSALE …

    Start of Statistics at 2012-07-08 14:43:54.

    Output to ./dirdat/yy:

    Extracting from SALES.CUST to SALES.CUST:

    *** Total statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Daily statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Hourly statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Latest statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 73>

    GGSCI (raclinux1.gj.com) 75> stats replicat repcust

    Sending STATS request to REPLICAT REPCUST …

    Start of Statistics at 2012-07-08 14:46:11.

    Replicating from SALES.CUST to SALES.CUST:

    *** Total statistics since 2012-07-08 14:43:32 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Daily statistics since 2012-07-08 14:43:32 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Hourly statistics since 2012-07-08 14:43:32 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Latest statistics since 2012-07-08 14:43:32 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 76>

    Let change some (999K) records on sales table on the source database and see how OGG behaves and propagates the data.

    SQL> update sales set amount_sold=20000 where cust_id between 1 and 2000;

    999000 rows updated.

    SQL>

    SQL> commit;

    Commit complete.

    SQL>

    Check the stats on the involved extract and replicat groups and verify that the changes are processed by the extract and replicat.

    GGSCI (raclinux1.gj.com) 73> stats extract extsale

    Sending STATS request to EXTRACT EXTSALE …

    Start of Statistics at 2012-07-08 15:23:39.

    Output to ./dirdat/zz:

    Extracting from SALES.CUST to SALES.CUST:

    *** Total statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 40000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 40000.00

    *** Daily statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 40000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 40000.00

    *** Hourly statistics since 2012-07-08 15:00:00 ***

    No database operations have been performed.

    *** Latest statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 40000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 40000.00

    Extracting from SALES.SALES to SALES.SALES:

    *** Total statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Daily statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Hourly statistics since 2012-07-08 15:00:00 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Latest statistics since 2012-07-08 14:22:31 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 74>

    GGSCI (raclinux1.gj.com) 78> stats extract pumpsale

    Sending STATS request to EXTRACT PUMPSALE …

    Start of Statistics at 2012-07-08 15:24:31.

    Output to ./dirdat/yy:

    Extracting from SALES.CUST to SALES.CUST:

    *** Total statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Daily statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    *** Hourly statistics since 2012-07-08 15:00:00 ***

    No database operations have been performed.

    *** Latest statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 20000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 20000.00

    Extracting from SALES.SALES to SALES.SALES:

    *** Total statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Daily statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Hourly statistics since 2012-07-08 15:00:00 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Latest statistics since 2012-07-08 14:43:31 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 79>

    GGSCI (raclinux1.gj.com) 84> stats replicat repsale

    Sending STATS request to REPLICAT REPSALE …

    Start of Statistics at 2012-07-08 15:44:30.

    Replicating from SALES.SALES to SALES.SALES:

    *** Total statistics since 2012-07-08 15:01:52 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Daily statistics since 2012-07-08 15:01:52 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Hourly statistics since 2012-07-08 15:01:52 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    *** Latest statistics since 2012-07-08 15:01:52 ***

    Total inserts 0.00

    Total updates 999000.00

    Total deletes 0.00

    Total discards 0.00

    Total operations 999000.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 85>

  3. Send NOHANDLECOLLISIONS and modify accordingly the respective parameter files.

    GGSCI (raclinux1.gj.com) 85> send replicat repsale, nohandlecollisions

    Sending NOHANDLECOLLISIONS request to REPLICAT REPSALE …

    REPSALE No tables found matching * to set NOHANDLECOLLISIONS

    .

    GGSCI (raclinux1.gj.com) 86> send replicat repcust, nohandlecollisions

    Sending NOHANDLECOLLISIONS request to REPLICAT REPCUST …

    REPCUST No tables found matching * to set NOHANDLECOLLISIONS

    .

    GGSCI (raclinux1.gj.com) 87>

    GGSCI (raclinux1.gj.com) 91> view param repsale

    replicat repsale

    SETENV (ORACLE_SID = “RACDB1”)

    userid ogg_replicat, password ogg_replicat

    –handlecollisions

    assumetargetdefs

    discardfile ./dirrpt/sale.dsc, append

    map sales.sales, target sales.sales;

    GGSCI (raclinux1.gj.com) 92> view param repcust

    replicat repcust

    SETENV (ORACLE_SID = “RACDB1”)

    userid ogg_replicat, password ogg_replicat

    –handlecollisions

    assumetargetdefs

    discardfile ./dirrpt/sale.dsc, append

    map sales.cust, target sales.cust;

    GGSCI (raclinux1.gj.com) 93>

  4. This concludes testing and verifying that pump extract operates properly

Summary:

In the article you had a look at the OGG feature data pump extract. You looked at an example of setting a data pump extract and methodology for verifying that configuration works properly.


July 9, 2012 - Posted by | oracle

3 Comments »

  1. […] online from here or download Configuring datapump in Oracle GoldenGate 0.000000 0.000000 Share […]

    Pingback by Configuring datapump extract in Oracle GoldenGate (OGG) 11.2 –Download « Guenadi N Jilevski's Oracle BLOG | July 9, 2012 | Reply

  2. Hi there, just wanted to mention, I enjoyed this article.
    It was funny. Keep on posting!

    Comment by winrar password remover | July 3, 2013 | Reply

  3. Could you please explain more about pump processes: what process to write trail file into remote target? Pump process in source system or collector process in target system?
    Tks.

    Comment by bihome | April 14, 2015 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: