Thursday, December 23, 2010

Post 25 of series,Configure discard file,exception table and stored procedure in goldengate replication with Tutorial

For the Gurmukhs, the Way is obvious. At the Lord's Door, they face no obstructions. They praise the Lord's Name, they keep the Naam in their minds, and they remain attached to the Love of the Naam. The Unstruck Celestial Music vibrates for them at the Lord's Door, and they are honored at the True Door.


Index to all the posts of Gurpartap Singh's Blog


Lets configure discardfile and then convert to Exception table and then will use a stored procedure from within goldengate replication. It depends what you want to use but I like exception table as it gives output in a oracle tabular form and so easy to isolate particular set
of sqls by just using simple sql commands.


Lets create simple tables and data to do this type of replication.

create table employee (
emp_id number,
firstname varchar2(20),
lastname varchar2(20)
);

alter table employee add constraint PK_employee primary key (emp_id);

create table location (
location_id number,
province varchar2(20),
city varchar2(20)
);

alter table location add constraint pk_location primary key (location_id);

Add to to goldengate replication use following:

DBLOGIN USERID goldengate, PASSWORD goldengate
ADD TRANDATA goldengate.employee
ADD TRANDATA goldengate.location


ADD EXTRACT etest, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/test/t1, EXTRACT etest


EDIT PARAMS etest


EXTRACT etest
USERID goldengate@simarsv1, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/test/t1
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
TABLE gurpartap.employee;
TABLE gurpartap.location;



ptest
ADD EXTRACT ptest, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/test/p1

ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/ts, EXTRACT ptest

EDIT PARAMS ptest

EXTRACT ptest
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/ts
PASSTHRU
TABLE gurpartap.employee;
TABLE gurpartap.location;




In logs its:


SQL> conn goldengate/goldengate
Connected.
SQL> create table employee (
emp_id number,
firstname varchar2(20),
lastname varchar2(20)
);
2 3 4 5
Table created.

SQL> alter table employee add constraint PK_employee primary key (emp_id);

Table altered.

SQL> create table location (
location_id number,
province varchar2(20),
city varchar2(20)
); 2 3 4 5

Table created.

SQL> alter table location add constraint pk_location primary key (location_id);

Table altered.

SQL>




GGSCI (rac1.rac.meditate.com) 7> DBLOGIN USERID goldengate, PASSWORD goldengate
Successfully logged into database.


GGSCI (rac1.rac.meditate.com) 10> ADD TRANDATA goldengate.employee

Logging of supplemental redo data enabled for table GOLDENGATE.EMPLOYEE.

GGSCI (rac1.rac.meditate.com) 11> ADD TRANDATA GOLDENGATE.location

Logging of supplemental redo data enabled for table GOLDENGATE.LOCATION.

GGSCI (rac1.rac.meditate.com) 12>



--------

GGSCI (rac1.rac.meditate.com) 1> ADD EXTRACT etest, TRANLOG, BEGIN NOW
EXTRACT added.


GGSCI (rac1.rac.meditate.com) 2> ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl, EXTRACT etest
EXTTRAIL added.


GGSCI (rac1.rac.meditate.com) 3> EDIT PARAMS etest

EXTRACT etest
SETENV (ORACLE_SID=SIMAR1)
USERID goldengate@simarsv1, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/test/t1
TABLE goldengate.employee;
TABLE goldengate.location;


GGSCI (rac1.rac.meditate.com) 4> ADD EXTRACT ptest, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/test/t1
EXTRACT added.


GGSCI (rac1.rac.meditate.com) 5> ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/ts, EXTRACT ptest
RMTTRAIL added.


GGSCI (rac1.rac.meditate.com) 6> EDIT PARAMS ptest



EXTRACT ptest
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/ts
PASSTHRU
TABLE goldengate.employee;
TABLE goldengate.location;



Now lets start the extracts as:


GGSCI (rac1.rac.meditate.com) 4> start extract etest

Sending START request to MANAGER ...
EXTRACT ETEST starting


GGSCI (rac1.rac.meditate.com) 5> start extract ptest

Sending START request to MANAGER ...
EXTRACT PTEST starting


GGSCI (rac1.rac.meditate.com) 6>



Now lets create the definition file as:


cd /u01/app/oracle/product/goldengate/dirprm
vi testplace.prm

DEFSFILE /u01/app/oracle/product/goldengate/dirdat/test
USERID goldengate@simarsv1, PASSWORD AACAAAAAAAAAAALAJAVCJBLGSBEABDQIAAVAUCVJUHDGSFAJ, ENCRYPTKEY default
TABLE goldengate.employee;
TABLE goldengate.location;

cd /u01/app/oracle/product/goldengate
./defgen paramfile dirprm/testplace.prm


Output is as:

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ cd /u01/app/oracle/product/goldengate/dirprm

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate/dirprm
$ vi testplace.prm

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate/dirprm
$ cd /u01/app/oracle/product/goldengate

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ ./defgen paramfile dirprm/testplace.prm

***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 18 2009 00:09:13

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


Starting at 2010-12-13 23:54:16
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri Oct 29 14:21:16 EDT 2010, Release 2.6.18-194.26.1.el5
Node: rac1.rac.meditate.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: 9422

***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/app/oracle/product/goldengate/dirdat/test
USERID goldengate@simarsv1, PASSWORD ************************************************* ENCRYPTKEY default
TABLE goldengate.employee;
Retrieving definition for GOLDENGATE.EMPLOYEE
TABLE goldengate.location;
Retrieving definition for GOLDENGATE.LOCATION



Definitions generated for 2 tables in /u01/app/oracle/product/goldengate/dirdat/dirdat/test


oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$

Now scp it to the target as:

scp /u01/app/oracle/product/goldengate/dirdat/dirdat/test goldengate:/u01/app/oracle/product/goldengate/dirdat/dirdat/test


Output is as:

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ scp /u01/app/oracle/product/goldengate/dirdat/dirdat/test goldengate:/u01/app/oracle/product/goldengate/dirdat/dirdat/test

Password:
test 100% 1237 1.2KB/s 00:00

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$

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



Now in target lets create the data as:


create table remployee (
emp_id number,
firstname varchar2(20),
lastname varchar2(20)
);

alter table remployee add constraint PK_remployee primary key (emp_id);

create table rlocation (
location_id number,
province varchar2(20),
city varchar2(20)
);

alter table rlocation add constraint pk_rlocation primary key (location_id);


Output is as:

SQL> conn goldengate/goldengate
Connected.
SQL> create table remployee (
emp_id number,
firstname varchar2(20),
lastname varchar2(20)
);
2 3 4 5
Table created.

SQL> alter table remployee add constraint PK_remployee primary key (emp_id);

Table altered.

SQL> create table rlocation (
location_id number,
province varchar2(20),
city varchar2(20)
); 2 3 4 5

Table created.

SQL> alter table rlocation add constraint pk_rlocation primary key (location_id);


Table altered.

SQL> SQL>





Now Goldengate stuff is as:


ADD REPLICAT rtest, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/ts




EDIT PARAMS rtest

REPLICAT rtest
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/test
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/test, purge
DISCARDROLLOVER ON sunday
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
REPERROR (DEFAULT, DISCARD)
GETINSERTS
GETUPDATES
GETDELETES
MAP goldengate.employee, TARGET goldengate.remployee;
MAP goldengate.location, TARGET goldengate.rlocation;



Output is as:
GGSCI (goldengate) 1> ADD REPLICAT rtest, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/ts
REPLICAT added.


GGSCI (sedm1610) 2>
GGSCI (sedm1610) 4> start replicat RTEST

Sending START request to MANAGER ...
REPLICAT RTEST starting


GGSCI (sedm1610) 5>




TEST 1:
On source:
select * from employee;
select * from location;
insert into employee values (1,'GURPARTAP','SINGH');
insert into location values (1,'BRITISH COLUMBIA','WHISTLER');
commit;
select * from employee;
select * from location;

On target:
These will be replicated as:
select * from remployee;
select * from rlocation;


Results as expected:

On source:

SQL> select * from employee;
select * from location;
no rows selected

SQL>

no rows selected

SQL>
SQL> insert into employee values (1,'GURPARTAP','SINGH');

1 row created.

SQL> insert into location values (1,'BRITISH COLUMBIA','WHISTLER');

1 row created.

SQL> commit:
2
SQL> commit;

Commit complete.

SQL> select * from employee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH

1 row selected.

SQL> select * from location;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER

1 row selected.

SQL>


On target:

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH

1 row selected.

SQL> select * from rlocation;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER

1 row selected.

SQL>




TEST2:
On target:
select * from remployee;
insert into remployee values (2,'SIMAR','SINGH');
commit;
select * from remployee;

On source:
select * from employee;
select * from location;
insert into employee values (2,'SIMAR','SINGH');
insert into location values (2,'BRITISH COLUMBIA','VANCOUVER');
commit;
select * from employee;
select * from location;


On target:
These will be replicated as:
select * from remployee;
select * from rlocation;


Results are not as expected:

on target:

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH

1 row selected.

SQL> insert into remployee values (2,'SIMAR','SINGH');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
2 SIMAR SINGH
1 GURPARTAP SINGH

2 rows selected.

SQL>

On source:

SQL> insert into employee values (2,'SIMAR','SINGH');

1 row created.

SQL> insert into location values (2,'BRITISH COLUMBIA','VANCOUVER');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH
2 SIMAR SINGH

2 rows selected.

SQL> select * from location;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER
2 BRITISH COLUMBIA VANCOUVER

2 rows selected.

SQL>


On target:

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
2 SIMAR SINGH
1 GURPARTAP SINGH

2 rows selected.

SQL> select * from rlocation;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER
2 BRITISH COLUMBIA VANCOUVER

2 rows selected.

SQL>


The discard file shows:

oracle : goldengate : @goldengate : /u01/app/oracle/product/goldengate
$ cat /u01/app/oracle/product/goldengate/dirdat/distest
Oracle GoldenGate Delivery for Oracle process started, group RTEST discard file opened: 2010-12-14 20:19:22

Current time: 2010-12-14 20:28:47

OCI Error ORA-00001: unique constraint (GOLDENGATE.PK_REMPLOYEE) violated (status = 1), SQL
Operation failed at seqno 2 rba 971
Discarding record on action DISCARD on error 1
Problem replicating GOLDENGATE.EMPLOYEE to GOLDENGATE.REMPLOYEE
Error (1) occurred with insert record (target format)...
*
EMP_ID = 2
FIRSTNAME = SIMAR
LASTNAME = SINGH
*

oracle : goldengate : @goldengate : /u01/app/oracle/product/goldengate
$



Note that even though we inserted 2 rows in two different tables in source in one single transaction but on target the transaction doesn't fail even though we have one of the row already existing, Goldengate inserts the other row in the other table and writes the existing row
to the discard file.





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

Now lets use the exception table instead of discard file as:

-- handle exception
MAP goldengate.employee, TARGET goldengate.employee_exception, EXCEPTIONSONLY, &
INSERTALLRECORDS , &
COLMAP (USEDEFAULTS, &
SOURCE_TABLE = @GETENV ("GGHEADER", "TABLENAME"), &
TARGET_TABLE = "GOLDENGATE.REMPLOYEE", &
DML_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), &
DBERRNUM = @GETENV("LASTERR", "DBERRNUM"), &
DBERRMSG = @GETENV("LASTERR", "DBERRMSG"));

-- handle exception
MAP GOLDENGATE.location, TARGET GOLDENGATE.location_exception, EXCEPTIONSONLY, &
INSERTALLRECORDS , &
COLMAP (USEDEFAULTS, &
SOURCE_TABLE = @GETENV ("GGHEADER", "TABLENAME"), &
TARGET_TABLE = "GOLDENGATE.RLOCATION", &
DML_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), &
DBERRNUM = @GETENV("LASTERR", "DBERRNUM"), &
DBERRMSG = @GETENV("LASTERR", "DBERRMSG"));


So, new parameter file looks like:

oracle : goldengate : @goldengate : /u01/app/oracle/product/goldengate/dirprm
$ cat rtest.prm
REPLICAT rtest
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/test
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/distest, purge
DISCARDROLLOVER ON sunday
--- Please note here goldengate is the name of the target machine, target database and taget database user_id.
USERID goldengate@goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
REPERROR (DEFAULT, EXCEPTION)
GETINSERTS
GETUPDATES
GETDELETES
MAP gurpartap.employee, TARGET gurpartap.remployee;
-- handle exception
MAP gurpartap.employee, TARGET gurpartap.employee_exception, EXCEPTIONSONLY, &
INSERTALLRECORDS , &
COLMAP (USEDEFAULTS, &
SOURCE_TABLE = @GETENV ("GGHEADER", "TABLENAME"), &
TARGET_TABLE = "GURPARTAP.REMPLOYEE", &
DML_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), &
DBERRNUM = @GETENV("LASTERR", "DBERRNUM"), &
DBERRMSG = @GETENV("LASTERR", "DBERRMSG"));
-- new
REPERROR (DEFAULT, EXCEPTION)
GETINSERTS
GETUPDATES
GETDELETES
MAP gurpartap.location, TARGET gurpartap.rlocation;
-- handle exception
MAP gurpartap.location, TARGET gurpartap.location_exception, EXCEPTIONSONLY, &
INSERTALLRECORDS , &
COLMAP (USEDEFAULTS, &
SOURCE_TABLE = @GETENV ("GGHEADER", "TABLENAME"), &
TARGET_TABLE = "GURPARTAP.RLOCATION", &
DML_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), &
DBERRNUM = @GETENV("LASTERR", "DBERRNUM"), &
DBERRMSG = @GETENV("LASTERR", "DBERRMSG"));

and we need to create the exception tables as:

create table GURPARTAP.LOCATION_EXCEPTION (
LOCATION_ID number,
PROVINCE varchar2(20),
CITY varchar2(20),
SOURCE_TABLE varchar2(100),
TARGET_TABLE varchar2(100),
DML_DATE date,
OPTYPE varchar2(20),
DBERRNUM number,
DBERRMSG VARCHAR2(4000)
);



create table GURPARTAP.EMPLOYEE_EXCEPTION (
emp_id number,
firstname varchar2(20),
lastname varchar2(20),
SOURCE_TABLE varchar2(100),
TARGET_TABLE varchar2(100),
DML_DATE date,
OPTYPE varchar2(20),
DBERRNUM number,
DBERRMSG VARCHAR2(4000)
);

Output is as:

SQL> show user
USER is "GURPARTAP"
SQL> create table GURPARTAP.EMPLOYEE_EXCEPTION (
2 emp_id number,
3 firstname varchar2(20),
4 lastname varchar2(20),
5 SOURCE_TABLE varchar2(100),
6 TARGET_TABLE varchar2(100),
7 DML_DATE date,
8 OPTYPE varchar2(20),
9 DBERRNUM number,
10 DBERRMSG VARCHAR2(4000)
11 );

Table created.

SQL> desc GURPARTAP.rlocation
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
LOCATION_ID NOT NULL NUMBER
PROVINCE VARCHAR2(20)
CITY VARCHAR2(20)

SQL> create table GURPARTAP.LOCATION_EXCEPTION (
2 LOCATION_ID number,
3 PROVINCE varchar2(20),
4 CITY varchar2(20),
5 SOURCE_TABLE varchar2(100),
6 TARGET_TABLE varchar2(100),
7 DML_DATE date,
8 OPTYPE varchar2(20),
9 DBERRNUM number,
10 DBERRMSG VARCHAR2(4000)
11 );

Table created.

SQL> select * from rlocation;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER

1 row selected.

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH

1 row selected.

SQL>






Lets repeat test 2 again:

Before that I stopped both the extract and the replicat and truncated all the 4 tables i.e
gurpartap.employee,gurpartap.location in source and gurpartap.rlocation,gurpartap.remployee in target and restarted the extracts and
replicates with begin now option as:
alter extract etest,begin now
alter extract ptest,begin now
start extract etest
start extract ptest
alter replicat rtest,begin now
start replicat rtest





TEST2:
On target:
select * from remployee;
insert into remployee values (2,'SIMAR','SINGH');
commit;
select * from remployee;

On source:
select * from employee;
select * from location;
insert into employee values (2,'SIMAR','SINGH');
insert into location values (2,'BRITISH COLUMBIA','VANCOUVER');
commit;
select * from employee;
select * from location;


On target:
These will be replicated as:
select * from remployee;
select * from rlocation;

Results are same as earlier:
Even though one row failed and written to the exception table,
it replicated the other row. Both the rows were inserted in the same
transaction on source.



Here is the output:

On target:
SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH

1 row selected.

SQL> insert into remployee values (2,'SIMAR','SINGH');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH
2 SIMAR SINGH

2 rows selected.

SQL>


On source:
SQL> insert into employee values (2,'SIMAR','SINGH');

1 row created.

SQL> insert into location values (2,'BRITISH COLUMBIA','VANCOUVER');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH
2 SIMAR SINGH

2 rows selected.

SQL> select * from location;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER
2 BRITISH COLUMBIA VANCOUVER

2 rows selected.

SQL>


On target:


SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH
2 SIMAR SINGH

2 rows selected.

SQL> select * from rlocation;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER
2 BRITISH COLUMBIA VANCOUVER

2 rows selected.


And it wrote that failed entry in the exceptions table as:



SQL> select * from GURPARTAP.EMPLOYEE_EXCEPTION ;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
SOURCE_TABLE
----------------------------------------------------------------------------------------------------
TARGET_TABLE DML_DATE
---------------------------------------------------------------------------------------------------- ---------
OPTYPE DBERRNUM
-------------------- ----------
DBERRMSG
------------------------------------------------------------------------------------------------------------------------
2 SIMAR SINGH
GURPARTAP.EMPLOYEE
GURPARTAP.REMPLOYEE 15-DEC-10
INSERT 1
OCI Error ORA-00001: unique constraint (GURPARTAP.PK_REMPLOYEE) violated (status = 1), SQL


1 row selected.

SQL>




Now we will create a stored procedure to let it insert in another table i.e. the third table in the tagert.

I will create a third table in the target now as:

create table start_date (
emp_id number,
start_date date
);


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

CREATE OR REPLACE PROCEDURE ckeck_insert (emp_id NUMBER) AS
BEGIN
insert into start_date values (emp_id,sysdate);
commit;
END ckeck_insert;
/


Then to test if its working use:

CALL ckeck_insert (3);



Output is as:

SQL> create table start_date (
emp_id number,
start_date date
);

2 3 4
Table created.

SQL> SQL> show user
USER is "GURPARTAP"
SQL> CREATE OR REPLACE PROCEDURE ckeck_insert (emp_id NUMBER) AS
BEGIN
insert into start_date values (emp_id,sysdate);
commit;
END ckeck_insert;
/
2 3 4 5 6
Procedure created.

SQL> CALL ckeck_insert (3);

Call completed.

SQL> select * from start_date;

EMP_ID START_DAT
---------- ---------
3 15-DEC-10

1 row selected.

SQL>


Now, I will truncate this table.

SQL> truncate table start_date;

Table truncated.

SQL> select * from start_date;

no rows selected

SQL>


Please note the following in below parameter file:

sqlexec (spname gurpartap.ckeck_insert , id ckeck_insert1, &
params(EMP_ID=@IF (@COLTEST(emp_id, MISSING), @COLSTAT(NULL), emp_id)), ERROR RAISE, TRACE ALL);

spname -> is the name of the procedure you want to call.
id ckeck_insert1 -> is required of you are calling this procedure more than one time andin that case id should be differrent in every case like ckeck_insert2 for second.
params -> here we are passing paramter to the procedure and name of teh parameter is emp_id. Please note the name of teh parameter being passed here should be same as the variable name defined in the procedure. Other option is COLTEST and i.e. if the emp_id value is missing then take the null value otherwise take the value of emp_id.


Now I will update the replicat parameter file as:

REPLICAT rtest
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/test
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/distest, purge
DISCARDROLLOVER ON sunday
--- Please note here goldengate is teh name of the target machine, target database and taget database user_id.
USERID goldengate@goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
REPERROR (DEFAULT, EXCEPTION)
GETINSERTS
GETUPDATES
GETDELETES
MAP gurpartap.employee, TARGET gurpartap.remployee, &
sqlexec (spname gurpartap.ckeck_insert , id ckeck_insert1, &
params(EMP_ID=@IF (@COLTEST(emp_id, MISSING), @COLSTAT(NULL), emp_id)), ERROR RAISE, TRACE ALL);
-- handle exception
MAP gurpartap.employee, TARGET gurpartap.employee_exception, EXCEPTIONSONLY, &
INSERTALLRECORDS , &
COLMAP (USEDEFAULTS, &
SOURCE_TABLE = @GETENV ("GGHEADER", "TABLENAME"), &
TARGET_TABLE = "GURPARTAP.REMPLOYEE", &
DML_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), &
DBERRNUM = @GETENV("LASTERR", "DBERRNUM"), &
DBERRMSG = @GETENV("LASTERR", "DBERRMSG"));
-- new
REPERROR (DEFAULT, EXCEPTION)
GETINSERTS
GETUPDATES
GETDELETES
MAP gurpartap.location, TARGET gurpartap.rlocation;
-- handle exception
MAP gurpartap.location, TARGET gurpartap.location_exception, EXCEPTIONSONLY, &
INSERTALLRECORDS , &
COLMAP (USEDEFAULTS, &
SOURCE_TABLE = @GETENV ("GGHEADER", "TABLENAME"), &
TARGET_TABLE = "GURPARTAP.RLOCATION", &
DML_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), &
DBERRNUM = @GETENV("LASTERR", "DBERRNUM"), &
DBERRMSG = @GETENV("LASTERR", "DBERRMSG"));




TEST1:




Lets repeat test 2 again:

Before that I stopped both the extract and the replicat and truncated all the 4 tables i.e
gurpartap.employee,gurpartap.location in source and gurpartap.rlocation,gurpartap.remployee in taeget and restarted the extracts and
replicats with begin now option as:
alter extract etest,begin now
alter extract ptest,begin now
start extract etest
start extract ptest
alter replicat rtest,begin now
start replicat rtest





TEST2:
On target:
select * from remployee;
insert into remployee values (2,'GURPARTAP','SINGH');
commit;
select * from remployee;

On source:
select * from employee;
select * from location;
insert into employee values (2,'SIMAR','SINGH');
insert into location values (2,'BRITISH COLUMBIA','VANCOUVER');
commit;
select * from employee;
select * from location;


On target:
These will be replicated as:
select * from remployee;
select * from rlocation;




Results are :
Even though one row failed and written to the exception table,
it replicated the other row. Both the rows were inserted in the same
transaction on source.



Here is the output:

On target :

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH

1 row selected.

SQL> insert into remployee values (2,'SIMAR','SINGH');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH
2 SIMAR SINGH

2 rows selected.

SQL>


On source:

SQL> insert into employee values (2,'SIMAR','SINGH');

1 row created.

SQL> insert into location values (2,'BRITISH COLUMBIA','VANCOUVER');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH
2 SIMAR SINGH

2 rows selected.

SQL> select * from location;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER
2 BRITISH COLUMBIA VANCOUVER

2 rows selected.

SQL>


On target:


SQL> select * from remployee;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
1 GURPARTAP SINGH
2 SIMAR SINGH

2 rows selected.

SQL> select * from rlocation;

LOCATION_ID PROVINCE CITY
----------- -------------------- --------------------
1 BRITISH COLUMBIA WHISTLER
2 BRITISH COLUMBIA VANCOUVER

2 rows selected.


And it wrote that failed entry in the exceptions table as:



SQL> select * from GURPARTAP.EMPLOYEE_EXCEPTION ;

EMP_ID FIRSTNAME LASTNAME
---------- -------------------- --------------------
SOURCE_TABLE
----------------------------------------------------------------------------------------------------
TARGET_TABLE DML_DATE
---------------------------------------------------------------------------------------------------- ---------
OPTYPE DBERRNUM
-------------------- ----------
DBERRMSG
------------------------------------------------------------------------------------------------------------------------
2 SIMAR SINGH
GURPARTAP.EMPLOYEE
GURPARTAP.REMPLOYEE 15-DEC-10
INSERT 1
OCI Error ORA-00001: unique constraint (GURPARTAP.PK_REMPLOYEE) violated (status = 1), SQL


1 row selected.

SQL>





SQL> select * from START_DATE;

EMP_ID START_DAT
---------- ---------
1 15-DEC-10
2 15-DEC-10

2 rows selected.

SQL>

Next we will create the primary key index on the table where procedure is inserting i.e. START_DATE and see how the inserts and updates will behave.

O Siblings of Destiny, one who becomes Gurmukh and understands this is very rare. To act without understanding is to lose the treasure of this human life. 1 Pause Those who have tasted it, enjoy its flavor; without tasting it, they wander in doubt, lost and deceived. The True
Name is the Ambrosial Nectar; no one can describe it. Drinking it in, one becomes honorable, absorbed in the Perfect Word of the Shabad.




No comments:

Post a Comment