Guenadi N Jilevski's Oracle BLOG

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

Configuring Oracle GoldenGate OGG 11gR2 local integrated capture and using OGG for mapping and transformations

Configuring Oracle GoldenGate OGG 11gR2 local integrated capture and using OGG for mapping and transformations

In the article you will have a look at an OGG configuration example for local integrated capture and discover how to deploy some of the powerful OGG features related to data mapping and transformation. Starting from OGG 11gR2 Oracle implemented an integrated capture, in addition to the already existing transaction log capture now a day called classic capture, only from Oracle RDBMS data sources by integrating the OGG extract with the database logmining server. OGG mapping and transformation is a feature that works with both classic and integrated capture enabling complex transactions to be replicated across heterogeneous environments. I will cover each topic separately in the following sub-sections.

  • Example of local integrated capture using OGG 11gR2
  • Example of mapping and transformation using OGG 11gR2

The article is based on OGG 11gR2 environment configuration previously described here.

Example of local integrated capture using OGG

The integrated capture mode introduced in OGG 11gR2 benefits from the integration with the existing Oracle Streams API. In integrated capture mode, the OGG extracts interact with the Oracle logmining server to capture data changes as a logical change records (LCR). The database where the Oracle logmining server is running must be 11.2.0.3 with patch as per MOS Doc ID 1411356.1 for integrated capture mode to handle almost all objects and data types otherwise there are restrictions. My source test database RACD and my target database RACDB are both 11.2.0.3 and patched as per the requirements. The integrated capture can be as follows depending on where the logminig server is running.

  • Local integrated capture – logminig server is running on the database where the transactions are executed and captured
  • Downstream integrated capture – logminig server is running on a different database from the database where the transactions are executed. The database running the transactions is configured to ship the redo and archivelogs to the mining database.

Useful Oracle sources are Oracle Installation and Setup Guide from the official Oracle OGG documentation accessible here and MOS notes (Best Practice – Oracle GoldenGate 11gr2 integrated extract and Oracle Data Guard – Switchover/Fail-over Operations Version 1.1 Document ID: 1436913.1 and Oracle GoldenGate Best Practices: Configuring Downstream Integrated Extract Document ID: 1485620.1).

Integrated capture mode in OGG 11gR2 enables you to process Oracle database objects and data types that cannot be processed in the classing capture mode. For a detailed list of Oracle database objects and data types that can be processed in integrated/classic capture mode look at Oracle Installation and Setup Guide.

In this section I will show an example of configuring a local integrated capture. In a separate blog post I will show an example of a downstream integrated capture.

The following tables will be created on both source and target databases for illustration of OGG mapping and transformation while replicating data from the source database to the target database.

The source objects are in test3 schema in RACD database. The target objects are in the test3_t schema in the RACDB database. I will configure extract extint on the source and replicat repint on the target. The tables listed below will be created on both source and target.

create table customer ( NID varchar2(10) primary key ,

Name varchar2(400),

Address_street varchar2(400),

Adress_city varchar2(50),

Tel varchar2(4),

state_code char(2),

     zip char(9)

);

create table customer_detail (

customer_id number(10) primary key,

First_name varchar2(40),

Last_name varchar2(40),

NID varchar2(10),

Tel_country varchar2(3),

Tel_area varchar2(3),

Tel_number varchar2(8),

Country varchar2(30),

Address_street varchar2(400),

address_city varchar2(50),

Tel_c_code varchar2(4),

State_name varchar2(50),

        zip1 varchar2(5),

zip2 varchar2(5) );

create table customer_detail1 (

customer_id number(10) primary key,

First_name varchar2(40),

Last_name varchar2(40),

NID varchar2(10),

Tel_country varchar2(3),

Tel_area varchar2(3),

Tel_number varchar2(8),

Country varchar2(30),

Address_street varchar2(400),

address_city varchar2(50),

Tel_c_code varchar2(4),

State_name varchar2(50),

        zip1 varchar2(5),

zip2 varchar2(5) );

This section will provide an overview of the steps to configure a local integrated extract. The steps are executed in order as follows:

  1. Grant permissions to the user (for local integrated capture one user will suffice although you can use two dedicated users).
  2. While logged in with dblogin add trandata for the source tables.
  3. Register extract for integrated capture while logged in with dblogin
  4. Create an extract for integrated capture
  5. Create a source and target definition file using DEFGEN.
  6. Create a parameter file for the extract using local integrated capture
  7. Create a trail for the extract
  8. Create a replicat
  9. Start OGG extract and replicat
  10. Troubleshooting tips/tricks for extract
  1. Grant permissions to the extract user

I am using the same OGG_EXTRACT user for extraction as defined here for OGG to interact to both source Oracle database and logmining server running on the source database. Grant privileges that required for the user to interact with the Oracle logmining server by executing the following SQL statement while logged in to the source database using as sysdba.

SQL> exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>’ogg_extract’, privilege_type=>’capture’,grant_select_privileges=>true, do_grants=>TRUE);

SQL>

PL/SQL procedure successfully completed.

SQL>

  1. Add trandata on the source database tables that will be replicated.

    GGSCI (raclinux1.gj.com) 6> dblogin userid ogg_extract, password ogg_extract

    Successfully logged into database.

    GGSCI (raclinux1.gj.com) 8> add trandata test3.state

    Logging of supplemental redo data enabled for table TEST3.STATE.

    GGSCI (raclinux1.gj.com) 9> add trandata test3.customer

    Logging of supplemental redo data enabled for table TEST3.CUSTOMER.

    GGSCI (raclinux1.gj.com) 10> add trandata test3.customer_detail

    Logging of supplemental redo data enabled for table TEST3.CUSTOMER_DETAIL.

    GGSCI (raclinux1.gj.com) 11>

    GGSCI (raclinux1.gj.com) 28> add schematrandata test3

    2012-10-25 13:29:20 INFO OGG-01788 SCHEMATRANDATA has been added on schema test3.

    GGSCI (raclinux1.gj.com) 29>

  2. Register the extract for integrated capture

    GGSCI (raclinux1.gj.com) 29> dblogin userid ogg_extract, password ogg_extract

    Successfully logged into database.

    GGSCI (raclinux1.gj.com) 30> register extract extint database

    2012-10-25 13:31:09 WARNING OGG-02064 Oracle compatibility version 11.2.0.0.0 has limited datatype support for integrated capture. Version 11.2.0.3 required for full support.

    2012-10-25 13:32:26 INFO OGG-02003 Extract EXTINT successfully registered with database at SCN 4629827.

    GGSCI (raclinux1.gj.com) 31>

  3. Create a source and a target definition files using DEFGEN utility

    Create a parameter file for generating the source definition file on the source system.

    GGSCI (raclinux1.gj.com) 4> view params defgensource

    defsfile ./dirdef/source_int.def, purge

    userid ddl_ogg, password ddl_ogg

    table test3.state;

    table test3.customer;

    table test3.customer_detail;

    GGSCI (raclinux1.gj.com) 5>

    Run defgen utility from OGG installation directory on the source system

    ./defgen paramfile ./dirprm/defgensource.prm

    Create parameter file for generating the target definition file on the target system.

    GGSCI (raclinux1.gj.com) 5> view params defgentarget

    defsfile ./dirdef/target_int.def, purge

    userid ddl_ogg, password ddl_ogg

    table test3_t.state;

    table test3_t.customer;

    table test3_t.customer_detail;

    table test3_t.customer_detail1;

    GGSCI (raclinux1.gj.com) 6>

    Run defgen utility from the OGG installation directory on the target system.

    ./defgen paramfile ./dirprm/defgentarget.prm

    Copy the source definition file to the target. Copy the target definition file to the source.

  4. Create the extract

    From the Reference Guide you will see that in case or RAC database with local integrated capture you should not specify threads. In practice you will not be allowed to do so.

    GGSCI (raclinux1.gj.com) 33> add extract extint, integrated tranlog, begin now, threads 2

    ERROR: THREADS is not allowed for INTEGRATED extract.

    GGSCI (raclinux1.gj.com) 34> add extract extint, integrated tranlog, begin now, threads 1

    ERROR: THREADS is not allowed for INTEGRATED extract.

    GGSCI (raclinux1.gj.com) 35> add extract extint, integrated tranlog, begin now

    EXTRACT added.

    GGSCI (raclinux1.gj.com) 36>

  5. Create the parameter file for extract and replicat

    The extract and replicat parameter files are in the appendix. Here I will list the parameter required to set local integrated capture extract.

    TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 700, parallelism 3)

    userid ogg_extract, password ogg_extrac

  6. Create a trail

    Create a remote trail from GGSCI.

    GGSCI (raclinux1.gj.com) 36> add rmttrail ./dirdat/2x , extract extint, megabytes 20

    RMTTRAIL added.

    GGSCI (raclinux1.gj.com) 37>

  7. Create a replicat

    Look at the appendix for the replicat parameter file. Create a replicat from GGSCI.

    GGSCI (raclinux1.gj.com) 8> add replicat repint, exttrail ./dirdat/2x

    REPLICAT added.

    GGSCI (raclinux1.gj.com) 9>

  8. Start OGG extract and replicat

    Start extract and replicat as usual using the OGG start extract and start replicat commands.

    GGSCI (raclinux1.gj.com) 6> start extract extint

    Sending START request to MANAGER …

    EXTRACT EXTINT starting

    GGSCI (raclinux1.gj.com) 7>

    GGSCI (raclinux1.gj.com) 7> start replicat repint

    Sending START request to MANAGER …

    REPLICAT REPINT starting

    GGSCI (raclinux1.gj.com) 8>

    Note the difference between integrated extract extint compared to a classic extract extt1.

    GGSCI (raclinux1.gj.com) 11> info extract extint

    EXTRACT EXTINT Last Started 2012-10-25 15:37 Status RUNNING

    Checkpoint Lag 00:00:00 (updated 01:45:42 ago)

    Log Read Checkpoint Oracle Integrated Redo Logs

    2012-10-25 13:55:43

    SCN 0.0 (0)

    GGSCI (raclinux1.gj.com) 12>

    GGSCI (raclinux1.gj.com) 12> info extract extt1

    EXTRACT EXTT1 Last Started 2012-10-25 15:37 Status RUNNING

    Checkpoint Lag 00:00:00 (updated 110:37:09 ago)

    Log Read Checkpoint Oracle Redo Logs

    2012-10-21 01:05:14 Thread 1, Seqno 276, RBA 9411088

    SCN 0.4572497 (4572497)

    Log Read Checkpoint Oracle Redo Logs

    2012-10-13 15:48:49 Thread 2, Seqno 0, RBA 0

    SCN 0.0 (0)

    GGSCI (raclinux1.gj.com) 13>

  9. Troubleshooting tips and tricks

    Initially, without proper configuration I faced the errors specified in the appendix. The following helped to work around the problems

    Commented –tranlogoptions asmuser sys@ASM, asmpassword sys1 in order to resolve

    OGG-02000 Ignoring option ASMUSER because it is incompatible with an integrated capture configuration and

    OGG-02000 Ignoring option ASMPASSWORD because it is incompatible with an integrated capture configuration.

    Configured the database with the following parameters and added OGG parameter TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 700, parallelism 3). Note that the source database is 11.2.0.3.

    alter system set memory_max_target=3g scope=spfile sid=’*’;

    alter system set memory_target=3g scope=spfile sid=’*’;

    alter system set streams_pool_size=1g scope=spfile sid=’*’;

    alter system set compatible=’11.2.0.3.0′ scope=spfile sid=’*’;

    This helped avoiding the following errors

    2012-10-25 16:29:57 WARNING OGG-00869 ORA-00600: internal error code, arguments: [KGHFRE3], [0x08AB26040],

    [], [], [], [], [], [], [], [], [], []

    ORA-26914: Unable to communicate with GoldenGate capture process “OGG$CAP_EXTINT” from outbound server “OGG$EXTINT”.

    2012-10-25 16:29:04 WARNING OGG-02045 Database does not have streams_pool_size initialization parameter configured.

    2012-10-25 16:29:02 WARNING OGG-02064 Oracle compatibility version 11.2.0.0.0 has limited datatype support for integrated capture. Version 11.2.0.3 required for full support.

    2012-10-25 17:30:27 WARNING OGG-02051 Not enough database memory to service Extract in integrated capture

    mode.

    2012-10-25 17:32:52 WARNING OGG-01223 TCP/IP error 111 (Connection refused).

Example of mapping and transformation using OGG 11gR2

OGG mapping and transformation features enable replication across dissimilar heterogeneous table structures. OGG SQLEXEC allows execution of SQL on both extract and replicat sites. In this case SQLEXEC will be used for table lookups. OGG allows mapping to be done on the source system using the extract TABLE parameter or on the target system using the replicat MAP parameter. For all mappings on the target using COLMAP and transformations use a source definition file specified with the SOURCEDEFS parameter. For all mappings on the source using COLMAP and transformations use a target definition file specified with the TARGETDEFS parameter. Source and target tables are identical if:

  • Tables have same column names
  • Columns of the names are of the same type
  • Column order is the same in source and target columns

If source and target tables are not identical use the SOURCEDEFS/TARGETDEFS parameters in extract/replicat parameter file and specify data definition files generated with defgen see the 1.4.

I will illustrate it using the following tables.

SQL> desc customer

Name Null? Type

—————————————– ——– —————————-

NID NOT NULL VARCHAR2(10)

NAME VARCHAR2(400)

ADDRESS_STREET VARCHAR2(400)

ADRESS_CITY VARCHAR2(50)

TEL VARCHAR2(40)

STATE_CODE VARCHAR2(3)

ZIP VARCHAR2(12)

SQL>

SQL> desc customer_detail

Name Null? Type

—————————————– ——– —————————-

CUSTOMER_ID NOT NULL NUMBER(10)

FIRST_NAME VARCHAR2(40)

LAST_NAME VARCHAR2(40)

NID VARCHAR2(10)

TEL_COUNTRY VARCHAR2(3)

TEL_AREA VARCHAR2(3)

TEL_NUMBER VARCHAR2(8)

COUNTRY VARCHAR2(30)

ADDRESS_STREET VARCHAR2(400)

ADDRESS_CITY VARCHAR2(50)

TEL_C_CODE VARCHAR2(4)

STATE_NAME VARCHAR2(50)

ZIP1 VARCHAR2(5)

ZIP2 VARCHAR2(5)

SQL>

SQL> desc state

Name Null? Type

—————————————– ——– —————————-

STATE_ID NOT NULL CHAR(2)

STATE_DESC VARCHAR2(50)

STATE_CAPITAL VARCHAR2(50)

SQL>

In the example state table is replicated as it is. Customer table from the source is mapped to the customer_detail and customer_detail1 on the target.

The mappings and the transformations from customer to customer_detail[1] are as follows:

Source table column Target Table column Transformation
CUSTOMER_ID NID Make it a number
FIRST_NAME NAME Extracts the first name
LAST_NAME NAME Extracts the surname
NID NID None. NID=NID
TEL_COUNTRY TEL +1 if state_code is in US
TEL_AREA TEL Extracts first 3 characters from TEL
TEL_NUMBER TEL Extracts from 4th position to the 10th position from TEL
COUNTRY STATE_CODE USA if state_code is in USA
ADDRESS_STREET ADDRESS_STREET NONE
ADDRESS_CITY ADRESS_CITY NONE
TEL_C_CODE STATE_CODE +1 if state_code is in US
STATE_NAME STATE_CODE Gets STATE_DESK using SQLEXEC lookup using state table
ZIP1 ZIP Extracts first 5 characters from ZIP
ZIP2 ZIP Extracts from 6th position to the 10th position from ZIP

The logical mapping above is implemented at OGG level using the underlined parameters from the extract extint and replicat repint parameter files shown bellow with abbreviations for the sake of clarity. Note that there are two mappings:

  • customer -> customer_detail : mapping is done in the replicat
  • customer -> customer_detail1 : mapping is done in the extract and replicat. This is an illustration how to do mapping and transformation on the extract in case the target is Tandem/NSK /HP SQL/MX based system.

The complete extract and replicat parameter files are in the appendix.

GGSCI (raclinux1.gj.com) 1> view params extint

extract extint

SETENV (ORACLE_SID = “RACD1”)

–tranlogoptions asmuser sys@ASM, asmpassword sys1

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 700, parallelism 3)

targetdefs ./dirdef/target_int.def

userid ogg_extract, password ogg_extract

rmthost raclinux1, mgrport 7809

rmttrail ./dirdat/2x

table test3.state;

table test3.customer;

table test3.customer_detail;

table test3.customer target test3_t.customer_detail1,

sqlexec( id statelookup, query ” select state_desc from test3.state where state_id = :state_code” , PARAMS(state_code=state_code)),

colmap(

usedefaults,

customer_id = @NUMSTR(NID),

First_name = @STREXT(Name,1,@STRFIND(Name,” “)),

Last_name = @STREXT(Name,@STRFIND(Name,” “),@STRLEN(NAme)),

NID = NID,

Tel_country = @IF (@VALONEOF(state_code,”AL”,”AK”, … ), “+1”, “+44”),

Tel_area = @STREXT (Tel, 1, 3),

Tel_number = @STREXT (Tel, 4, 10),

country = @IF (@VALONEOF(state_code,”AL”,”AK”, … ), “USA”, “UK”),

Tel_c_code =@IF (@VALONEOF(state_code,”AL”,”AK”, ” …), “+1”, “+44”),

State_name = @GETVAL(statelookup.state_desc),

zip1 = @strext(zip,1,5),

zip2 = @strext(zip,6,10));;

GGSCI (raclinux1.gj.com) 2> view params repint

replicat repint

–reperror(default,ignore)

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

sourcedefs ./dirdef/source_int.def

discardfile ./dirrpt/repint.dsc, purge

map test3.state, target test3_3.state;

map test3.customer, target test3.customer;

map test3.customer, target test3_t.customer_detail1,

sqlexec( id statelookup1, query ” select state_desc from test3_t.state where state_id = :state_code” , PARAMS(state_code=state_code)),

colmap(

usedefaults,

ADDRESS_CITY = ADRESS_CITY,

customer_id = @NUMSTR(NID),

First_name = @STREXT(Name,1,@STRFIND(Name,” “)),

Last_name = @STREXT(Name,@STRFIND(Name,” “),@STRLEN(NAme)),

NID = NID,

Tel_country = @IF (@VALONEOF(state_code,”AL”,”AK”, … ), “+1”, “+44”),

Tel_area = @STREXT (Tel, 1, 3),

Tel_number = @STREXT (Tel, 4, 10),

country = @IF (@VALONEOF(state_code,”AL”,”AK”, …), “USA”, “UK”),

Tel_c_code =@IF (@VALONEOF(state_code,”AL”,”AK”, …”), “+1”, “+44”),

State_name = @GETVAL(statelookup1.state_desc),

zip1 = @strext(zip,1,5),

zip2 = @strext(zip,6,10));

map test3.customer, target test3_t.customer_detail,

sqlexec( id statelookup, query ” select state_desc from test3_t.state where state_id = :state_code” , PARAMS(state_code=state_code)),

colmap(

usedefaults,

ADDRESS_CITY = ADRESS_CITY,

customer_id = @NUMSTR(NID),

First_name = @STREXT(Name,1,@STRFIND(Name,” “)),

Last_name = @STREXT(Name,@STRFIND(Name,” “),@STRLEN(NAme)),

NID = NID,

Tel_country = @IF (@VALONEOF(state_code,”AL”,”AK”, …), “+1”, “+44”),

Tel_area = @STREXT (Tel, 1, 3),

Tel_number = @STREXT (Tel, 4, 10),

country = @IF (@VALONEOF(state_code,”AL”,”AK”, …), “USA”, “UK”),

Tel_c_code =@IF (@VALONEOF(state_code,”AL”,”AK”, …), “+1”, “+44”),

State_name = @GETVAL(statelookup.state_desc),

zip1 = @strext(zip,1,5),

zip2 = @strext(zip,6,10));

Let’s test it.

Insert into test3.customer and observe the transformations on the target.

insert into test3.customer values(‘222222222′,’John Smith’,’93 Some street’,’Dallas’,’5555555555′,’TX’,’75209-5555′);

insert into test3.customer values(‘333333333′,’Alejandro García’, ’46 Some street’,’Wayne’,’6666666666′,’NJ’,’07470-5555′);

insert into test3.customer values(‘444444444′,’Frank Meyer’,’56 Some street’,’Los Angeles’,’7777777777′,’CA’,’90018-5555′);

commit;

on the target we have the following result:

SQL> select * from customer_detail;

CUSTOMER_ID FIRST_NAME LAST_NAME NID TEL TEL TEL_NUMB COUNTR ADDRESS_STREET ADDRESS_CI TEL_ STATE_NAME ZIP1 ZIP2

———– ——————– ——————– ———- — — ——– —— —————– ———- —- ———- —– —–

222222222 John Smith 222222222 +1 555 5555555 USA 93 Some street Dallas +1 Texas 75209 -5555

333333333 Alejandro Garc??a 333333333 +1 666 6666666 USA 46 Some street Wayne +1 New Jersey 07470 -5555

444444444 Frank Meyer 444444444 +1 777 7777777 USA 56 Some street Los Angele +1 California 90018 -5555

s

SQL> select * from customer_detail1;

CUSTOMER_ID FIRST_NAME LAST_NAME NID TEL TEL TEL_NUMB COUNTR ADDRESS_STREET ADDRESS_CI TEL_ STATE_NAME ZIP1 ZIP2

———– ——————– ——————– ———- — — ——– —— —————– ———- —- ———- —– —–

222222222 John Smith 222222222 +1 555 5555555 USA 93 Some street Dallas +1 Texas 75209 -5555

333333333 Alejandro Garc??a 333333333 +1 666 6666666 USA 46 Some street Wayne +1 New Jersey 07470 -5555

444444444 Frank Meyer 444444444 +1 777 7777777 USA 56 Some street Los Angele +1 California 90018 -5555

s

SQL>

Perform an update on the source

update test3.customer set TEL=’8888888888′ where NID=’222222222′;

Verify the result on the target

SQL> select * from customer_detail;

CUSTOMER_ID FIRST_NAME LAST_NAME NID TEL TEL TEL_NUMB COUNTR ADDRESS_STREET ADDRESS_CI TEL_ STATE_NAME ZIP1 ZIP2

———– ——————– ——————– ———- — — ——– —— —————– ———- —- ———- —– —–

222222222 John Smith 222222222 +1 888 8888888 USA 93 Some street Dallas +1 Texas 75209 -5555

333333333 Alejandro Garc??a 333333333 +1 666 6666666 USA 46 Some street Wayne +1 New Jersey 07470 -5555

444444444 Frank Meyer 444444444 +1 777 7777777 USA 56 Some street Los Angele +1 California 90018 -5555

s

SQL> select * from customer_detail1;

CUSTOMER_ID FIRST_NAME LAST_NAME NID TEL TEL TEL_NUMB COUNTR ADDRESS_STREET ADDRESS_CI TEL_ STATE_NAME ZIP1 ZIP2

———– ——————– ——————– ———- — — ——– —— —————– ———- —- ———- —– —–

222222222 John Smith 222222222 +1 888 8888888 USA 93 Some street Dallas +1 Texas 75209 -5555

333333333 Alejandro Garc??a 333333333 +1 666 6666666 USA 46 Some street Wayne +1 New Jersey 07470 -5555

444444444 Frank Meyer 444444444 +1 777 7777777 USA 56 Some street Los Angele +1 California 90018 -5555

s

SQL>

Delete a record from the source

delete from test3.customer where NID=’222222222′;

Observe the result on the target

SQL> select * from customer_detail;

CUSTOMER_ID FIRST_NAME LAST_NAME NID TEL TEL TEL_NUMB COUNTR ADDRESS_STREET ADDRESS_CI TEL_ STATE_NAME ZIP1 ZIP2

———– ——————– ——————– ———- — — ——– —— —————– ———- —- ———- —– —–

333333333 Alejandro Garc??a 333333333 +1 666 6666666 USA 46 Some street Wayne +1 New Jersey 07470 -5555

444444444 Frank Meyer 444444444 +1 777 7777777 USA 56 Some street Los Angele +1 California 90018 -5555

s

SQL> select * from customer_detail;

CUSTOMER_ID FIRST_NAME LAST_NAME NID TEL TEL TEL_NUMB COUNTR ADDRESS_STREET ADDRESS_CI TEL_ STATE_NAME ZIP1 ZIP2

———– ——————– ——————– ———- — — ——– —— —————– ———- —- ———- —– —–

333333333 Alejandro Garc??a 333333333 +1 666 6666666 USA 46 Some street Wayne +1 New Jersey 07470 -5555

444444444 Frank Meyer 444444444 +1 777 7777777 USA 56 Some street Los Angele +1 California 90018 -5555

s

SQL>

The test concluded that the replication is successful from the source to the target and that OGG performs the mapping and transformation as expected. Note that on the target you have ‘Garc??a’ instead of ‘Alejandro García’ due to character conversion.

Summary

In the article you had a look at the steps to configure and troubleshoot OGG using local integrated capture extract. You had a glimpse at an example utilizing OGG SQLEXEC and OGG functions for mapping and data transformation for replication across dissimilar table structures.

Appendix

GGSCI (raclinux1.gj.com) 1> view params extint

extract extint

SETENV (ORACLE_SID = “RACD1”)

–tranlogoptions asmuser sys@ASM, asmpassword sys1

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 700, parallelism 3)

targetdefs ./dirdef/target_int.def

userid ogg_extract, password ogg_extract

rmthost raclinux1, mgrport 7809

rmttrail ./dirdat/2x

table test3.state;

table test3.customer;

table test3.customer_detail;

table test3.customer target test3_t.customer_detail1,

sqlexec( id statelookup, query ” select state_desc from test3.state where state_id = :state_code” , PARAMS(state_code=state_code)),

colmap(

usedefaults,

customer_id = @NUMSTR(NID),

First_name = @STREXT(Name,1,@STRFIND(Name,” “)),

Last_name = @STREXT(Name,@STRFIND(Name,” “),@STRLEN(NAme)),

NID = NID,

Tel_country = @IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”,

“KY”, “LA”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA

“, “RI”, “SC”, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “+1”, “+44”),

Tel_area = @STREXT (Tel, 1, 3),

Tel_number = @STREXT (Tel, 4, 10),

country = @IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “KY”

, “LA”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”,

“RI”, “SC”, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “USA”, “UK”),

Tel_c_code =@IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “K

Y”, “LA”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”,

“RI”, “SC”, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “+1”, “+44”),

State_name = @GETVAL(statelookup.state_desc),

zip1 = @strext(zip,1,5),

zip2 = @strext(zip,6,10));;

GGSCI (raclinux1.gj.com) 2> view params repint

replicat repint

–reperror(default,ignore)

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

sourcedefs ./dirdef/source_int.def

discardfile ./dirrpt/repint.dsc, purge

map test3.state, target test3_3.state;

map test3.customer, target test3.customer;

map test3.customer, target test3_t.customer_detail1,

sqlexec( id statelookup1, query ” select state_desc from test3_t.state where state_id = :state_code” , PARAMS(state_code=state_code)),

colmap(

usedefaults,

ADDRESS_CITY = ADRESS_CITY,

customer_id = @NUMSTR(NID),

First_name = @STREXT(Name,1,@STRFIND(Name,” “)),

Last_name = @STREXT(Name,@STRFIND(Name,” “),@STRLEN(NAme)),

NID = NID,

Tel_country = @IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “KY”,

“LA”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”, “RI”,

“SC”, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “+1”, “+44”),

Tel_area = @STREXT (Tel, 1, 3),

Tel_number = @STREXT (Tel, 4, 10),

country = @IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “KY”, “L

A”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”, “RI”, “SC”

, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “USA”, “UK”),

Tel_c_code =@IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “KY”,

“LA”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”, “RI”, “S

C”, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “+1”, “+44”),

State_name = @GETVAL(statelookup1.state_desc),

zip1 = @strext(zip,1,5),

zip2 = @strext(zip,6,10));

map test3.customer, target test3_t.customer_detail,

sqlexec( id statelookup, query ” select state_desc from test3_t.state where state_id = :state_code” , PARAMS(state_code=state_code)),

colmap(

usedefaults,

ADDRESS_CITY = ADRESS_CITY,

customer_id = @NUMSTR(NID),

First_name = @STREXT(Name,1,@STRFIND(Name,” “)),

Last_name = @STREXT(Name,@STRFIND(Name,” “),@STRLEN(NAme)),

NID = NID,

Tel_country = @IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “KY”,

“LA”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”, “RI”,

“SC”, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “+1”, “+44”),

Tel_area = @STREXT (Tel, 1, 3),

Tel_number = @STREXT (Tel, 4, 10),

country = @IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “KY”, “L

A”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”, “RI”, “SC”

, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “USA”, “UK”),

Tel_c_code =@IF (@VALONEOF(state_code,”AL”,”AK”, “AZ”, “AR”, “CA”, “CO”, “CT”, “DE”, “FL”, “GA”, “HI”, “ID”, “IL”, “IN”, “IA”, “KS”, “KY”,

“LA”, “ME”, “MD”, “MA”, “MI”, “MN”, “MS”, “MO”, “MT”, “NE”, “NV”, “NH”, “NJ”, “NM”, “NY”, “NC”, “ND”, “OH”, “OK”, “OR”, “PA”, “RI”, “S

C”, “SD”, “TN”, “TX”, “UT”, “VT”, “VA”, “WA”, “WV”, “WI”, “WY”), “+1”, “+44”),

State_name = @GETVAL(statelookup.state_desc),

zip1 = @strext(zip,1,5),

zip2 = @strext(zip,6,10));

GGSCI (raclinux1.gj.com) 3>

Errors resolved during the configuration

2012-10-25 16:29:02 INFO OGG-02000 Ignoring option ASMUSER because it is incompatible with an integrate

d capture configuration.

2012-10-25 16:29:02 INFO OGG-02000 Ignoring option ASMPASSWORD because it is incompatible with an integ

rated capture configuration.

2012-10-25 16:29:02 WARNING OGG-02064 Oracle compatibility version 11.2.0.0.0 has limited datatype support

for integrated capture. Version 11.2.0.3 required for full support.

2012-10-25 16:29:04 WARNING OGG-02045 Database does not have streams_pool_size initialization parameter co

nfigured.

2012-10-25 16:29:18 INFO OGG-02036 Integrated capture successfully attached to logmining server OGG$EXT

INT.

2012-10-25 16:29:30 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).

2012-10-25 16:29:30 INFO OGG-01055 Recovery initialization completed for target file ./dirdat/2×000004,

at RBA 1097.

2012-10-25 16:29:30 INFO OGG-01478 Output file ./dirdat/2x is using format RELEASE 11.2.

2012-10-25 16:29:30 INFO OGG-01026 Rolling over remote file ./dirdat/2×000005.

2012-10-25 16:29:30 INFO OGG-01053 Recovery completed for target file ./dirdat/2×000005, at RBA 1036.

2012-10-25 16:29:30 INFO OGG-01057 Recovery completed for all targets.

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

** Run Time Messages **

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

2012-10-25 16:29:57 WARNING OGG-00869 ORA-00600: internal error code, arguments: [KGHFRE3], [0x08AB26040],

[], [], [], [], [], [], [], [], [], []

ORA-26914: Unable to communicate with GoldenGate capture process “OGG$CAP_EXTINT” from outbound server “OGG$

EXTINT”.

References

  1. Best Practice – Oracle GoldenGate 11gr2 integrated extract and Oracle Data Guard – Switchover/Fail-over Operations Version 1.1 Document ID: 1436913.1
  2. Oracle GoldenGate Best Practices: Configuring Downstream Integrated Extract Document ID: 1485620.1
  3. Oracle® GoldenGate Oracle Installation and Setup Guide Release 11.2.1 E35957-01 Oracle Installation and Setup Guide

  4. OGG Administrator’s Guide
  5. OGG Reference Guide


October 26, 2012 - Posted by | oracle

1 Comment »

  1. […] online from here or download Oracle GoldenGate OGG 11gR2 local integrated […]

    Pingback by Configuring Oracle GoldenGate OGG 11gR2 local integrated capture and using OGG for mapping and transformations – DOWNLOAD « Guenadi N Jilevski's Oracle BLOG | October 26, 2012 | 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: