Post 23 of series,Configure more complex goldengate replication with Tutorial

You are the Primal Being, the Most Wonderful Creator. There is no other as Great as You. Age after age, You are the One.
Forever and ever, You are the One. You never change, O Creator Lord. Everything happens according to Your Will. You
Yourself accomplish all that occurs. You Yourself created the entire universe, and having fashioned it, You Yourself shall
destroy it all.



Index of all the posts of Gurpartap Singh's Blog


Lets create more data for a little bit more complex replication.

In source in schema meditate create the following table.

create table place_at_time_of_death (
place_id number,
supercluster varchar2(30),
galaxy varchar2(30)
);



ALTER TABLE place_at_time_of_death
add CONSTRAINT place_id_pk PRIMARY KEY (place_id);



create sequence place_id_seq
increment by 1
start with 1
nomaxvalue
cache 20
;


alter table place_at_time_of_death add HUMAN_ID NUMBER;
alter table place_at_time_of_death add FIRSTNAME VARCHAR2(30);
alter table place_at_time_of_death add LASTNAME VARCHAR2(20);



Create this table on target in schema CONSCIOUSNESS :

create table location_at_time_of_death (
record_seq_no number,
place_id number,
supercluster varchar2(30),
galaxy varchar2(30)
);


ALTER TABLE location_at_time_of_death
add CONSTRAINT place_id_pk PRIMARY KEY (place_id);


create sequence location_at_time_of_death_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
CACHE 20
;



alter table location_at_time_of_death add PERSON_ID NUMBER;
alter table location_at_time_of_death add INITIALNAME VARCHAR2(30);
alter table location_at_time_of_death add ENDNAME VARCHAR2(20);
alter table location_at_time_of_death add life_resulted_in VARCHAR2(100);

Output is as:


Source:

SQL> show user
USER is "MEDITATE"
SQL> create table place_at_time_of_death (
place_id number,
supercluster varchar2(30),
galaxy varchar2(30)
);
2 3 4 5
Table created.

SQL>


SQL> ALTER TABLE place_at_time_of_death
add CONSTRAINT place_id_pk PRIMARY KEY (place_id); 2

Table altered.


SQL> create sequence place_id_seq
increment by 1
start with 1
nomaxvalue
cache 20
; 2 3 4 5 6

Sequence created.



SQL> show user
USER is "MEDITATE"
SQL> alter table place_at_time_of_death add HUMAN_ID NUMBER;
alter table place_at_time_of_death add FIRSTNAME VARCHAR2(30);
alter table place_at_time_of_death add LASTNAME VARCHAR2(20);


Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>

SQL>


Target:



SQL> create table location_at_time_of_death (
record_seq_no number,
place_id number,
supercluster varchar2(30),
galaxy varchar2(30)
);

2 3 4 5 6
Table created.

SQL> SQL>
ALTER TABLE location_at_time_of_death
add CONSTRAINT place_id_pk PRIMARY KEY (place_id);

SQL> 2
Table altered.

SQL>

SQL> SQL> create sequence location_at_time_of_death_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
CACHE 20; 2 3 4 5

Sequence created.

SQL>


SQL> alter table location_at_time_of_death add PERSON_ID NUMBER;
alter table location_at_time_of_death add INITIALNAME VARCHAR2(30);
alter table location_at_time_of_death add ENDNAME VARCHAR2(20);

Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
SQL> alter table location_at_time_of_death add life_resulted_in VARCHAR2(100);

Table altered.

SQL>





Now, configure goldengate with ddl replication as:

On source do the following:

Enable supplemental logging at table level.
DBLOGIN USERID meditate, PASSWORD meditate
ADD TRANDATA meditate.place_at_time_of_death



Output as:

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

GGSCI (rac1.rac.meditate.com) 16> ADD TRANDATA meditate.place_at_time_of_death

Logging of supplemental redo data enabled for table MEDITATE.PLACE_AT_TIME_OF_DEATH.

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



In LOGS:

2010-11-18 21:48:31 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.place_at_time_of_death.





Add extract as:
eplace
ADD EXTRACT eplace, TRANLOG, BEGIN NOW, threads 3

ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl, EXTRACT eplace


EDIT PARAMS eplace


EXTRACT eplace
USERID goldengate@simarsv1, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl
-- DDL INCLUDE MAPPED
TABLE meditate.place_at_time_of_death;

Please note I am commenting line DDL INCLUDE MAPPED as it doesn't work when we use SOURCEDEFS parameter in replicat
i.e. when definition is different in source and target.


pplace
ADD EXTRACT pplace, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/pl

ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl, EXTRACT pplace

EDIT PARAMS pplace

EXTRACT pplace
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl
PASSTHRU
TABLE meditate.place_at_time_of_death;




Output is as:

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

GGSCI (rac1.rac.meditate.com) 16> ADD TRANDATA meditate.place_at_time_of_death

Logging of supplemental redo data enabled for table MEDITATE.PLACE_AT_TIME_OF_DEATH.

GGSCI (rac1.rac.meditate.com) 17> ADD EXTRACT eplace, TRANLOG, BEGIN NOW, threads 3
EXTRACT added.


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


GGSCI (rac1.rac.meditate.com) 19> EDIT PARAMS eplace

EXTRACT eplace
USERID goldengate@simarsv1, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl
--DDL INCLUDE MAPPED
TABLE meditate.place_at_time_of_death;


GGSCI (rac1.rac.meditate.com) 20> ADD EXTRACT pplace, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/pl
EXTRACT added.


GGSCI (rac1.rac.meditate.com) 21> ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl, EXTRACT pplace
RMTTRAIL added.


GGSCI (rac1.rac.meditate.com) 22> EDIT PARAMS pplace

EXTRACT pplace
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl
PASSTHRU
TABLE meditate.place_at_time_of_death;




GGSCI (rac1.rac.meditate.com) 23>
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$






In logs it looks like:

2010-11-18 21:48:31 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.place_at_time_of_death.
2010-11-18 21:50:02 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT eplace TRANLOG, BEGIN NOW, threads 3.
2010-11-18 21:50:13 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl EXTRACT eplace.
2010-11-18 21:50:26 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS eplace.
2010-11-18 21:51:16 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT pplace EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/pl.
2010-11-18 21:51:26 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl EXTRACT pplace.
2010-11-18 21:51:38 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS pplace.

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

Create definition file here and scp to the target side.

I will create a file defeplace.prm under directroy $GOLDEN_GATE_INSALL/dirprm/defeplace.prm on source as:

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

DEFSFILE /u01/app/oracle/product/goldengate/dirdat/defeplace
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TABLE meditate.place_at_time_of_death;

and then execute:

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


then scp this file to source as:
scp /u01/app/oracle/product/goldengate/dirdat/defeplace goldengate:/u01/app/oracle/product/goldengate/dirdat/defeplace

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

Output is as:

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

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ ./defgen paramfile dirprm/defeplace.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-11-25 02:52:07
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Thu Sep 3 03:28:30 EDT 2009, Release 2.6.18-164.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: 21136

***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/app/oracle/product/goldengate/dirdat/defeplace
USERID goldengate, PASSWORD ************************************************* ENCRYPTKEY default
TABLE meditate.place_at_time_of_death;
Retrieving definition for MEDITATE.PLACE_AT_TIME_OF_DEATH



Definitions generated for 1 tables in /u01/app/oracle/product/goldengate/dirdat/defeplace


oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ scp /u01/app/oracle/product/goldengate/dirdat/defeplace goldengate:/u01/app/oracle/product/goldengate/dirdat/defeplace
oracle@goldengate's password:
defeplace 100% 1162 1.1KB/s 00:00

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

We will be using the following goldengate parameters in the parameter file.


Using EXCEPTIONSONLY
Use EXCEPTIONSONLY in an exceptions MAP statement intended for error handling. It causes
the MAP statement in which it is specified to be executed only if an error occurs for the last
record processed in the preceding MAP statement.
To use EXCEPTIONSONLY, set the REPERROR parameter to EXCEPTION for the error to be handled
as an exception. Make certain that the exceptions MAP statement specifies the same SOURCE
table as in the MAP statement for which the error is anticipated. Set the TARGET table to the
name of the exceptions table. For more information about REPERROR, see page 259.
The exceptions MAP statement must follow the MAP statement for which the error is
anticipated. For more information about using an exceptions MAP statement, see the
GoldenGate for Windows and UNIX Administrator Guide.
Syntax MAP , TARGET , EXCEPTIONSONLY



REPERROR
Valid for Replicat
Use the REPERROR parameter to control how Replicat responds to errors. You can use one
REPERROR statement to handle most errors in a default manner, while using one or more
other REPERROR statements to handle specific errors differently. For example, you can ignore
duplicate-record errors but abort processing in all other cases.
In the syntax shown, note that the , specification must be within
parentheses.

This parameter has many options but I will discuss following 2 here only:

DISCARD Log the error to the discard file but continue processing the
transaction and subsequent transactions.

EXCEPTION Handle the error as an exception. In anticipation of possible errors,
you can create an exceptions MAP statement that executes only after
an error. Use that MAP statement, for example, to map columns
from a failed update statement into a “missing update” table. In the
parameter file, specify the exceptions MAP sta



DISCARDFILE
Valid for Extract and Replicat
Use the DISCARDFILE parameter to generate a discard file to which GoldenGate can log
records that it cannot process. Records can be discarded for several reasons. For example,
a record is discarded if the underlying table structure changed since the record was written
to the trail. You can use the discard file to help you identify the cause of processing errors.
Each entry in the discard file contains the discarded record buffer and an error code
indicating the reason. GoldenGate creates the specified discard file in the dirrpt subdirectory
of the GoldenGate installation directory. You can view it with a text editor or by
using the following command in GGSCI.
VIEW REPORT
Where: is the fully qualified name of the discard file.
To prevent having to perform manual maintenance of discard files, use either the PURGE or
APPEND option. Otherwise, you must specify a different discard file name before starting
each process run, because GoldenGate will not write to an existing discard file.



DISCARDROLLOVER
Valid for Extract and Replicat
Use the DISCARDROLLOVER parameter to set a schedule for aging discard files. For long or
continuous runs, setting an aging schedule prevents the discard file from filling up and
causing the process to abend, and it provides a predictable set of archives that can be
included in your archiving routine.
When the DISCARDROLLOVER age point is reached, a new discard file is created, and old files
are renamed in the format of ., where:
? is the name of the Extract or Replicat group
? is a number that gets incremented by one each time a new file is created, for
example: myext0.dsc, myext1.dsc, myext2.dsc, and so forth.
You can specify a time of day, a day of the week, or both. Specifying just a time of day (AT
option) without a day of the week (ON option) generates a discard file at the specified time
every day.



ALLOWDUPTARGETMAP | NOALLOWDUPTARGETMAP
Valid for Replicat
Use the ALLOWDUPTARGETMAP and NOALLOWDUPTARGETMAP parameters to control whether or
not duplicate MAP statements for the same source and target objects are accepted in a
parameter file.



Add the replicat like following on the targer server, please note here we are replicating just the inserts here
we will modify the parameter file to replicate deletes and updates later in the tutorial:

ADD REPLICAT rplace, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl


EDIT PARAMS rplace

REPLICAT rplace
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/defeplace
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/location_at_time_of_death_err, purge
DISCARDROLLOVER ON sunday
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
REPERROR (DEFAULT, DISCARD)
GETINSERTS
IGNOREDELETES
IGNOREUPDATES
MAP meditate.place_at_time_of_death, TARGET CONSCIOUSNESS.location_at_time_of_death, &
sqlexec (id record_seq_lookup, query "select CONSCIOUSNESS.location_at_time_of_death_seq.NEXTVAL record_seq from dual", noparams), &
sqlexec (id life_resulted_in_lookup, query "select deed_result from CONSCIOUSNESS.RESULTS &
where deed_id = (select DEED_ID_AT_DEATH from CONSCIOUSNESS.TENURE where &
PERSON_ID=(:humanid_topass) and INITIALNAME=:fist and ENDNAME=:lastname_topass)", &
params(humanid_topass=HUMAN_ID,fist=FIRSTNAME,lastname_topass=LASTNAME), ERROR RAISE, TRACE ALL), &
COLMAP (RECORD_SEQ_NO=@getval (record_seq_lookup.record_seq), &
PLACE_ID=PLACE_ID, &
SUPERCLUSTER=SUPERCLUSTER, &
GALAXY=GALAXY, &
PERSON_ID=HUMAN_ID, &
INITIALNAME=FIRSTNAME, &
ENDNAME=LASTNAME, &
LIFE_RESULTED_IN=@getval (life_resulted_in_lookup.deed_result)), &
KEYCOLS (PLACE_ID) ;



Explanation for few parameters that we are using here:
In following block:

"sqlexec (id record_seq_lookup, query "select CONSCIOUSNESS.location_at_time_of_death_seq.NEXTVAL record_seq from dual", noparams), &"

"id" declares the alias
"record_seq_lookup" is alias name of the block and can be anything.
"query" is the query that will be executed
"record_seq" is the alias for "CONSCIOUSNESS.location_at_time_of_death_seq.NEXTVAL" and can be anything
i.e whatever is returned will be stored in "record_seq" and when we want to retrive teh value in later part of teh parameter file
reference it as record_seq_lookup.record_seq
i.e block_alias_name.alias_name
"noparams" means we are not passing any parameters to this sql being executed.


and in the explanation for teh following block is :

" sqlexec (id life_resulted_in_lookup, query "select deed_result from CONSCIOUSNESS.RESULTS &
where deed_id = (select DEED_ID_AT_DEATH from CONSCIOUSNESS.TENURE where &
PERSON_ID=(:humanid_topass) and INITIALNAME=:fist and ENDNAME=:lastname_topass)", &
params(humanid_topass=HUMAN_ID,fist=FIRSTNAME,lastname_topass=LASTNAME), ERROR RAISE, TRACE ALL), & "

Only thing here is we are passing parameters to the sql and they are:
:humanid_topass
:fist
:lastname_topass

the values for them are
humanid_topass=HUMAN_ID
fist=FIRSTNAME
lastname_topass=LASTNAME






Now lets start the replicat as:

start replicat rplace


Output is as:

GGSCI (goldengate) 14> ADD REPLICAT rplace, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl
REPLICAT added.


GGSCI (goldengate) 15> EDIT PARAMS rplace



Output in logs is as:

2010-11-25 02:23:54 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD REPLICAT rplace EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/pl.
2010-11-25 02:24:19 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS rplace.
2010-11-25 02:26:00 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start replicat rplace.
2010-11-25 02:26:00 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.169 (START REPLICAT RPLACE ).
2010-11-25 02:26:00 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RPLACE starting.
2010-11-25 02:26:00 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rplace.prm: REPLICAT RPLACE starting.
2010-11-25 02:26:01 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rplace.prm: REPLICAT RPLACE started.




Now lets start the extract on the source side as:

start extract EPLACE
start extract PPLACE


Output is as:

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

Sending START request to MANAGER ...
EXTRACT EPLACE starting


GGSCI (rac1.rac.meditate.com) 6> start extract PPLACE

Sending START request to MANAGER ...
EXTRACT PPLACE starting


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




In source, output in logs is as:

2010-11-25 03:12:30 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract EPLACE.
2010-11-25 03:12:30 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT EPLACE ).
2010-11-25 03:12:30 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EPLACE starting.
2010-11-25 03:12:30 GGS INFO 310 Oracle GoldenGate Capture for Oracle, eplace.prm: EXTRACT EPLACE starting.
2010-11-25 03:15:01 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, eplace.prm: Default thread stack size: 33554432.
2010-11-25 03:15:21 GGS INFO 310 Oracle GoldenGate Capture for Oracle, eplace.prm: EXTRACT EPLACE started.
2010-11-25 03:15:21 GGS INFO 112 Oracle GoldenGate Capture for Oracle, eplace.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/pl000000, at RBA 0 (file not opened).
2010-11-25 03:15:21 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, eplace.prm: Output file /u01/app/oracle/product/goldengate/dirdat/pl is using format RELEASE 10.4.
2010-11-25 03:15:36 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract PPLACE.
2010-11-25 03:15:37 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT PPLACE ).
2010-11-25 03:15:37 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PPLACE starting.
2010-11-25 03:15:37 GGS INFO 310 Oracle GoldenGate Capture for Oracle, pplace.prm: EXTRACT PPLACE starting.
2010-11-25 03:15:37 GGS INFO 310 Oracle GoldenGate Capture for Oracle, pplace.prm: EXTRACT PPLACE started.
2010-11-25 03:15:43 GGS INFO 406 Oracle GoldenGate Capture for Oracle, pplace.prm: Socket buffer size set to 27985 (flush size 27985).
2010-11-25 03:15:57 GGS INFO 112 Oracle GoldenGate Capture for Oracle, pplace.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/pl000000, at RBA 0 (file not opened).
2010-11-25 03:15:57 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, pplace.prm: Output file /u01/app/oracle/product/goldengate/dirdat/pl is using format RELEASE 10.4.



On target we have following in logs:

2010-11-25 02:43:40 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host 192.168.1.176 (START SERVER CPU -1 PRI -1 PARAMS ).
2010-11-25 02:43:40 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started collector process (Port 7848).
2010-11-25 02:43:40 GGS INFO 373 Oracle GoldenGate Collector, port 7848: Waiting for connection (started dynamically).
2010-11-25 02:43:59 GGS INFO 373 Oracle GoldenGate Collector: Connected to 192.168.1.176:61301.
2010-11-25 02:43:59 GGS INFO 370 Oracle GoldenGate Collector, port 7848: Opening /u01/app/oracle/product/goldengate/dirdat/pl000000 (byte -1, current EOF 0).






Now we will test the replication:

On source side we have:

SQL> desc place_AT_TIME_OF_DEATH
Name Null? Type
----------------------------------------- -------- ----------------------------
PLACE_ID NOT NULL NUMBER
SUPERCLUSTER VARCHAR2(30)
GALAXY VARCHAR2(30)
HUMAN_ID NUMBER
FIRSTNAME VARCHAR2(30)
LASTNAME VARCHAR2(20)

SQL> insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'COMA',3,'AB','CD');

1 row created.

SQL> insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'COMA',3,'MN','OP');

1 row created.

SQL> insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'COMA',3,'QR','ST');

1 row created.

SQL> insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'COMA',2,'IJ','KL');

1 row created.

SQL> commit;

Commit complete.

SQL>


On target side we got:

Before insert on source:

SQL> select * from LOCATION_AT_TIME_OF_DEATH;

no rows selected



After insert on source its replicated to target as:


SQL> SQL> set line 300
SQL> l
1* select * from LOCATION_AT_TIME_OF_DEATH
SQL> /

RECORD_SEQ_NO PLACE_ID SUPERCLUSTER GALAXY PERSON_ID INITIALNAME ENDNAME LIFE_RESULTED_IN
------------- ---------- ------------------------------ ------------------------------ ---------- ------------------------------ -------------------- ----------------------------------------------------------------------------------------------------
2 7 COMA 3 AB CD Becomes snake in next incarnations
3 8 COMA 3 MN OP Becomes prostitute in next incarnations
4 9 COMA 3 QR ST Liberated
5 10 COMA 2 IJ KL Becomes pig in next incarnations

4 rows selected.

SQL>


Now, I will change the DDL of the table on the source and target databases to have more data recorded in this table to do little bit more
complex replication.
Before changing the DDL, I will stop extract and replicat processes for this table on both source and target.

On source
stop extract eplace
stop extract pplace

Output on source is as:

GGSCI (rac1.rac.meditate.com) 42> stop extract EPLACE

Sending STOP request to EXTRACT EPLACE ...
Request processed.


GGSCI (rac1.rac.meditate.com) 43> stop extract PPLACE

Sending STOP request to EXTRACT PPLACE ...
Request processed.


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



On target
stop replicat RPLACE

Output on target is as:

GGSCI (goldengate) 101> stop replicat RPLACE

Sending STOP request to REPLICAT RPLACE ...
Request processed.


GGSCI (goldengate) 102>



Now on source do the following under schmea meditate:

desc place_at_time_of_death
alter table place_at_time_of_death add star varchar2(30);
alter table place_at_time_of_death add planet varchar2(30);
alter table place_at_time_of_death add race varchar2(30);
alter table place_at_time_of_death add continent varchar2(30);
alter table place_at_time_of_death add province varchar2(30);
alter table place_at_time_of_death add city varchar2(30);
desc place_at_time_of_death


Output on source is as:

SQL> desc place_at_time_of_death
Name Null? Type
----------------------------------------- -------- ----------------------------
PLACE_ID NOT NULL NUMBER
SUPERCLUSTER VARCHAR2(30)
GALAXY VARCHAR2(30)
HUMAN_ID NUMBER
FIRSTNAME VARCHAR2(30)
LASTNAME VARCHAR2(20)

SQL> alter table place_at_time_of_death add star varchar2(30);


Table altered.

SQL> SQL> alter table place_at_time_of_death add planet varchar2(30);

Table altered.

SQL> alter table place_at_time_of_death add race varchar2(30);

Table altered.

SQL> alter table place_at_time_of_death add continent varchar2(30);

Table altered.

SQL> alter table place_at_time_of_death add province varchar2(30);

Table altered.

SQL> alter table place_at_time_of_death add city varchar2(30);

Table altered.

SQL> desc place_at_time_of_death
Name Null? Type
----------------------------------------- -------- ----------------------------
PLACE_ID NOT NULL NUMBER
SUPERCLUSTER VARCHAR2(30)
GALAXY VARCHAR2(30)
HUMAN_ID NUMBER
FIRSTNAME VARCHAR2(30)
LASTNAME VARCHAR2(20)
STAR VARCHAR2(30)
PLANET VARCHAR2(30)
RACE VARCHAR2(30)
CONTINENT VARCHAR2(30)
PROVINCE VARCHAR2(30)
CITY VARCHAR2(30)

SQL>



and now do the following on target under CONSCIOUSNESS:

desc location_at_time_of_death
alter table location_at_time_of_death add star varchar2(30);
alter table location_at_time_of_death add planet varchar2(30);
alter table location_at_time_of_death add race varchar2(30);
alter table location_at_time_of_death add continent varchar2(30);
alter table location_at_time_of_death add province varchar2(30);
alter table location_at_time_of_death add city varchar2(30);
desc location_at_time_of_death


Output on target is as:
SQL> show user
USER is "CONSCIOUSNESS"
SQL> desc LOCATION_AT_TIME_OF_DEATH
Name Null? Type
----------------------------- -------- --------------------
RECORD_SEQ_NO NUMBER
PLACE_ID NOT NULL NUMBER
SUPERCLUSTER VARCHAR2(30)
GALAXY VARCHAR2(30)
PERSON_ID NUMBER
INITIALNAME VARCHAR2(30)
ENDNAME VARCHAR2(20)
LIFE_RESULTED_IN VARCHAR2(100)

SQL> alter table location_at_time_of_death add star varchar2(30);

Table altered.

SQL> alter table location_at_time_of_death add planet varchar2(30);

Table altered.

SQL> alter table location_at_time_of_death add race varchar2(30);

Table altered.

SQL> alter table location_at_time_of_death add continent varchar2(30);

Table altered.

SQL> alter table location_at_time_of_death add province varchar2(30);

Table altered.

SQL> alter table location_at_time_of_death add city varchar2(30);

Table altered.

SQL> desc location_at_time_of_death
Name Null? Type
----------------------------- -------- --------------------
RECORD_SEQ_NO NUMBER
PLACE_ID NOT NULL NUMBER
SUPERCLUSTER VARCHAR2(30)
GALAXY VARCHAR2(30)
PERSON_ID NUMBER
INITIALNAME VARCHAR2(30)
ENDNAME VARCHAR2(20)
LIFE_RESULTED_IN VARCHAR2(100)
STAR VARCHAR2(30)
PLANET VARCHAR2(30)
RACE VARCHAR2(30)
CONTINENT VARCHAR2(30)
PROVINCE VARCHAR2(30)
CITY VARCHAR2(30)

SQL>



Now we will need to create the definition file on source again and scp to target as:

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

DEFSFILE /u01/app/oracle/product/goldengate/dirdat/defeplace
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TABLE meditate.place_at_time_of_death;

and then execute:

mv /u01/app/oracle/product/goldengate/dirdat/defeplace /u01/app/oracle/product/goldengate/dirdat/defeplace.case1
cd /u01/app/oracle/product/goldengate
./defgen paramfile dirprm/defeplace.prm


then scp this file to source as:
scp /u01/app/oracle/product/goldengate/dirdat/defeplace goldengate:/u01/app/oracle/product/goldengate/dirdat/defeplace


Output is as:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ mv /u01/app/oracle/product/goldengate/dirdat/defeplace /u01/app/oracle/product/goldengate/dirdat/defeplace.case1

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

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ ./defgen paramfile dirprm/defeplace.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-11-26 00:34:43
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Thu Sep 3 03:28:30 EDT 2009, Release 2.6.18-164.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: 7598

***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/app/oracle/product/goldengate/dirdat/defeplace
USERID goldengate, PASSWORD ************************************************* ENCRYPTKEY default
TABLE meditate.place_at_time_of_death;
Retrieving definition for MEDITATE.PLACE_AT_TIME_OF_DEATH



Definitions generated for 1 tables in /u01/app/oracle/product/goldengate/dirdat/defeplace


oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ scp /u01/app/oracle/product/goldengate/dirdat/defeplace goldengate:/u01/app/oracle/product/goldengate/dirdat/defeplace
oracle@goldengate's password:
defeplace 100% 1667 1.6KB/s 00:00

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



Now lets fix our golden gate parameter file for replicat rplace on source so that we can still replicate the inserts:

EDIT PARAMS rplace

REPLICAT rplace
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/defeplace
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/location_at_time_of_death_err, purge
DISCARDROLLOVER ON sunday
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
REPERROR (DEFAULT, DISCARD)
GETINSERTS
IGNOREDELETES
IGNOREUPDATES
MAP meditate.place_at_time_of_death, TARGET CONSCIOUSNESS.location_at_time_of_death, &
sqlexec (id record_seq_lookup, query "select CONSCIOUSNESS.location_at_time_of_death_seq.NEXTVAL record_seq from dual", noparams), &
sqlexec (id life_resulted_in_lookup, query "select deed_result from CONSCIOUSNESS.RESULTS &
where deed_id = (select DEED_ID_AT_DEATH from CONSCIOUSNESS.TENURE where &
PERSON_ID=(:humanid_topass) and INITIALNAME=:fist and ENDNAME=:lastname_topass)", &
params(humanid_topass=HUMAN_ID,fist=FIRSTNAME,lastname_topass=LASTNAME), ERROR RAISE, TRACE ALL), &
COLMAP (RECORD_SEQ_NO=@getval (record_seq_lookup.record_seq), &
PLACE_ID=PLACE_ID, &
SUPERCLUSTER=SUPERCLUSTER, &
GALAXY=GALAXY, &
PERSON_ID=HUMAN_ID, &
INITIALNAME=FIRSTNAME, &
ENDNAME=LASTNAME, &
LIFE_RESULTED_IN=@getval (life_resulted_in_lookup.deed_result), &
STAR=STAR, &
PLANET=PLANET, &
RACE=RACE, &
CONTINENT=CONTINENT, &
PROVINCE=PROVINCE, &
CITY=CITY), &
KEYCOLS (PLACE_ID) ;


Now lets start the extraction and replication processes on both source and target as:

On source:
start extract eplace
start extract pplace


Output is as:

GGSCI (rac1.rac.meditate.com) 52> start extract eplace

Sending START request to MANAGER ...
EXTRACT EPLACE starting


GGSCI (rac1.rac.meditate.com) 53> start extract pplace

Sending START request to MANAGER ...
EXTRACT PPLACE starting


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



On target
start replicat rplace



Output is as:

GGSCI (goldengate) 102> start replicat rplace

Sending START request to MANAGER ...
REPLICAT RPLACE starting


GGSCI (goldengate) 103>



Now lets do some inserts to see if our replication is still working:


On source we have:
SQL> insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'SHAPLEY',1,'AB','CD');

1 row created.

SQL> commit;

Commit complete.

SQL>





On target we have:

1* select count(*) from location_at_time_of_death
SQL> /

COUNT(*)
----------
5

SQL>


Lets insert the other records as:

On source as:

insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'CENTAURUS',1,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'SCULPTOR',1,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'HYDA',1,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'PERSEUS-PISCES',1,'QR','ST');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'LEO',2,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'URSA MAJOR',2,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'PISCES-CETUS',2,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'PAVO INDUS',2,'QR','ST');

Output on source is as:
SQL> insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'CENTAURUS',1,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'SCULPTOR',1,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'HYDA',1,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'PERSEUS-PISCES',1,'QR','ST');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'LEO',2,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'URSA MAJOR',2,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'PISCES-CETUS',2,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'PAVO INDUS',2,'QR','ST');


1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL> SQL> commit ;

Commit complete.

SQL> select count(*) from place_at_time_of_death;

COUNT(*)
----------
13

SQL>



On target we have:
SQL> l
1* select count(*) from location_at_time_of_death
SQL> /

COUNT(*)
----------
13

SQL>



So, our replication is working.





On source do following:
select count(*) from life;
insert into life values (4,'AB','CD',SYSDATE - 23725,65,1);
insert into life values (4,'EF','GH',SYSDATE - 23725,65,1);
insert into life values (4,'IJ','KL',SYSDATE - 23725,65,2);
insert into life values (4,'MN','OP',SYSDATE - 23725,65,2);
insert into life values (4,'QR','ST',SYSDATE - 23725,65,3);
insert into life values (5,'AB','CD',SYSDATE - 23725,65,3);
insert into life values (5,'EF','GH',SYSDATE - 23725,65,4);
insert into life values (5,'IJ','KL',SYSDATE - 23725,65,4);
insert into life values (5,'MN','OP',SYSDATE - 23725,65,5);
insert into life values (5,'QR','ST',SYSDATE - 23725,65,5);
insert into life values (6,'AB','CD',SYSDATE - 23725,65,2);
insert into life values (6,'EF','GH',SYSDATE - 23725,65,2);
insert into life values (6,'IJ','KL',SYSDATE - 23725,65,3);
insert into life values (6,'MN','OP',SYSDATE - 23725,65,3);
insert into life values (6,'QR','ST',SYSDATE - 23725,65,4);
insert into life values (7,'AB','CD',SYSDATE - 23725,65,2);
insert into life values (7,'EF','GH',SYSDATE - 23725,65,2);
insert into life values (7,'IJ','KL',SYSDATE - 23725,65,2);
insert into life values (7,'MN','OP',SYSDATE - 23725,65,2);
insert into life values (7,'QR','ST',SYSDATE - 23725,65,2);
insert into life values (8,'AB','CD',SYSDATE - 23725,65,2);
insert into life values (8,'EF','GH',SYSDATE - 23725,65,5);
insert into life values (8,'IJ','KL',SYSDATE - 23725,65,5);
insert into life values (8,'MN','OP',SYSDATE - 23725,65,5);
insert into life values (8,'QR','ST',SYSDATE - 23725,65,5);
select count(*) from life;


Output is as:

SQL> select count(*) from life;
insert into life values (4,'AB','CD',SYSDATE - 23725,65,1);
insert into life values (4,'EF','GH',SYSDATE - 23725,65,1);
insert into life values (4,'IJ','KL',SYSDATE - 23725,65,2);
insert into life values (4,'MN','OP',SYSDATE - 23725,65,2);
insert into life values (4,'QR','ST',SYSDATE - 23725,65,3);
insert into life values (5,'AB','CD',SYSDATE - 23725,65,3);
insert into life values (5,'EF','GH',SYSDATE - 23725,65,4);
insert into life values (5,'IJ','KL',SYSDATE - 23725,65,4);
insert into life values (5,'MN','OP',SYSDATE - 23725,65,5);
insert into life values (5,'QR','ST',SYSDATE - 23725,65,5);
insert into life values (6,'AB','CD',SYSDATE - 23725,65,2);
insert into life values (6,'EF','GH',SYSDATE - 23725,65,2);
insert into life values (6,'IJ','KL',SYSDATE - 23725,65,3);
insert into life values (6,'MN','OP',SYSDATE - 23725,65,3);
insert into life values (6,'QR','ST',SYSDATE - 23725,65,4);
insert into life values (7,'AB','CD',SYSDATE - 23725,65,2);
insert into life values (7,'EF','GH',SYSDATE - 23725,65,2);
insert into life values (7,'IJ','KL',SYSDATE - 23725,65,2);
insert into life values (7,'MN','OP',SYSDATE - 23725,65,2);
insert into life values (7,'QR','ST',SYSDATE - 23725,65,2);
insert into life values (8,'AB','CD',SYSDATE - 23725,65,2);

insert into life values (8,'EF','GH',SYSDATE - 23725,65,5);
insert into life values (8,'IJ','KL',SYSDATE - 23725,65,5);
insert into life values (8,'MN','OP',SYSDATE - 23725,65,5);
insert into life values (8,'QR','ST',SYSDATE - 23725,65,5);
select count(*) from life;
COUNT(*)
----------
15

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
COUNT(*)
----------
40

SQL> commit;

Commit complete.

SQL>




On target
select count(*) from tenure;

SQL> l
1* select count(*) from tenure
SQL> /

COUNT(*)
----------
40

SQL>


Lets do few more on source as:

insert into life values (9,'AB','CD',SYSDATE - 23725,65,2);
insert into life values (9,'EF','GH',SYSDATE - 23725,65,5);
insert into life values (9,'IJ','KL',SYSDATE - 23725,65,5);
insert into life values (9,'MN','OP',SYSDATE - 23725,65,5);
insert into life values (9,'QR','ST',SYSDATE - 23725,65,5);

Output is:

SQL> insert into life values (9,'AB','CD',SYSDATE - 23725,65,2);
insert into life values (9,'EF','GH',SYSDATE - 23725,65,5);
insert into life values (9,'IJ','KL',SYSDATE - 23725,65,5);
insert into life values (9,'MN','OP',SYSDATE - 23725,65,5);
insert into life values (9,'QR','ST',SYSDATE - 23725,65,5);

1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL> commit;

Commit complete.

SQL>




and on target its:

SQL> select count(*) from tenure;

COUNT(*)
----------
45

SQL>






Now on source do the following:

insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'SEXTANS',3,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'COLUMBA',3,'IJ','KL');


insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'HOROLOGUM',4,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'CAPRICORNOUS',4,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'HERCULES',4,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'BOOTES',4,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,human_id,firstname,lastname) values (place_id_seq.nextval,'CORONA-BOREALIS',4,'QR','ST');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','N4365',5,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','M49',5,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','N4526',5,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','N4535',5,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','N4429',5,'QR','ST');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','M58',6,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','M59',6,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','M60',6,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','N4762',6,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','M87',6,'QR','ST');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','M89',7,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','N90',7,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,galaxy,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','N4654',7,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SOL',7,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','CHARA',7,'QR','ST');

insert into place_at_time_of_death (place_id,supercluster,galaxy,star,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SCORPII',7,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','37 GEMINORM',7,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','MERCURY',7,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','VENUS',7,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','MARS',7,'QR','ST');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','JUPITER',8,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','SATURAN',8,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','URANUS',8,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','NEPTUNE',8,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','PLUTO',8,'QR','ST');


insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,race,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','EARTH','BIRD',9,'AB','CD');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,race,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','EARTH','HUMAN',9,'EF','GH');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,race,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','EARTH','HUMAN',9,'IJ','KL');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,race,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','EARTH','HUMAN',9,'MN','OP');
insert into place_at_time_of_death (place_id,supercluster,galaxy,star,planet,race,human_id,firstname,lastname) values (place_id_seq.nextval,'VIRGO','MILKY WAY','SUN','EARTH','HUMAN',9,'QR','ST');


Output in source:
SQL> select count(*) from place_at_time_of_death;

COUNT(*)
----------
50

SQL>



In target the output is as:
SQL> select count(*) from location_at_time_of_death;

COUNT(*)
----------
50

SQL>



This human body has been given to you. This is your chance to meet the Lord of the Universe. Nothing else will work. Join the Saadh Sangat, the Company of the Holy; vibrate and meditate on the Jewel of the Naam. || 1|| Make every effort to cross over this terrifying world-ocean

Configure DDL synchronization/replication (Post 22 of series,DDL synchronization/replication with Tutorial)

The soul-bride is of no use at all, if she is evil and without virtue. She does not find peace in this world or the next; she burns in falsehood
and corruption. Coming and going are very difficult for that bride who is abandoned and forgotten by her Husband Lord.


Index of all the posts of Gurpartap Singh's Blog

Now before proceeding with more complex replication tutorials/examples lets install the DDL synchronization/replication first.

Now after sometime you would like to patch your application to add new features to it. One part of it would be to
upgrade the schema to next version i.e. change DDL. Don't worry goldengate is a golden product with lots of flexiblility and it will
happily replicate your DDL to target too. To replicate DDL's we need to configure DDL synchronization. It has few steps to it.
Lets configure DDL synchronization.

To do the following steps you should have user "goldengate" created in the database as we did in our previous posts.
From Goldengate installation directroy execute the following scripts as sysdba in source database as:


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

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 17 22:03:36 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @marker_setup

Marker setup script

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GOLDENGATE


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL> alter session set recyclebin=OFF;

Session altered.

SQL> @ddl_setup

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: On Oracle 10g and up, system recycle bin must be disabled.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GOLDENGATE

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP

Working, please wait ...
Spooling to file ddl_setup_spool.txt


Using GOLDENGATE as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes


DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


CLEAR_TRACE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


CREATE_TRACE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


TRACE_PUT_LINE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


INITIAL_SETUP STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


DDLREPLICATION PACKAGE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


DDLREPLICATION PACKAGE BODY STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
--------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
--------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
--------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
--------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/simar/simar1/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL> @role_setup

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GOLDENGATE
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to GOLDENGATE;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.

SQL> @ddl_pin GOLDENGATE

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

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


Now we will enable additional suplemental logging for the tables to enable DDL replication for them.
Though we can enable additional supplemental logging on at column level also but here we will enable
at table level.

Use command:
DBLOGIN USERID meditate, PASSWORD meditate
ADD TRANDATA meditate.EMPLOYEE;
ADD TRANDATA meditate.RESULTS;
ADD TRANDATA meditate.LIFE;

Output is as:

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

GGSCI (rac1.rac.meditate.com) 7> ADD TRANDATA meditate.EMPLOYEE;
ERROR: No viable tables matched specification.

GGSCI (rac1.rac.meditate.com) 8> ADD TRANDATA meditate.RESULTS;
ERROR: No viable tables matched specification.

GGSCI (rac1.rac.meditate.com) 9> ADD TRANDATA meditate.LIFE;
ERROR: No viable tables matched specification.

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


Output in logs is as:

2010-11-17 22:28:00 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.EMPLOYEE;.
2010-11-17 22:28:18 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.RESULTS;.
2010-11-17 22:28:32 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.LIFE;.



Now alter the parameter recyclebin from default on to off using following command and bounce the database as:
alter system set recyclebin=OFF scope=spfile;
srvtl stop database -d simar
srcvctl start database -d simar

Output is as:

SQL> alter system set recyclebin=OFF scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle
$


oracle : rac1.rac.meditate.com : @crs : /home/oracle
$ srvctl stop database -d simar


oracle : rac1.rac.meditate.com : @crs : /home/oracle
$ srvctl start database -d simar


oracle : rac1.rac.meditate.com : @crs : /home/oracle
$ crs_stat -t -v
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.DATA1.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac2
ora.RECV1.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1
ora.eons ora.eons.type 0/3 0/ ONLINE ONLINE rac1
ora.gsd ora.gsd.type 0/5 0/ ONLINE ONLINE rac1
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac1
ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1
ora.prabgun.db ora....se.type 0/2 0/2 ONLINE OFFLINE
ora....sv3.svc ora....ce.type 0/0 0/0 ONLINE OFFLINE
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.gsd application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac2
ora....SM3.asm application 0/5 0/0 ONLINE ONLINE rac3
ora....C3.lsnr application 0/5 0/0 ONLINE ONLINE rac3
ora.rac3.gsd application 0/5 0/0 ONLINE ONLINE rac3
ora.rac3.ons application 0/3 0/0 ONLINE ONLINE rac3
ora.rac3.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac3
ora....ry.acfs ora....fs.type 0/5 0/ ONLINE ONLINE rac1
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac2
ora.simar.db ora....se.type 0/2 0/1 ONLINE ONLINE rac1
ora....sv1.svc ora....ce.type 0/0 0/0 ONLINE ONLINE rac1

oracle : rac1.rac.meditate.com : @crs : /home/oracle
$


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 17 23:15:04 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string OFF
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle
$


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

Now lets enable DDL replication by adding parameter "DDL INCLUDE MAPPED" in teh extract parameter file.

From documentation we have :

DDL
Valid for Extract and Replicat
Use the DDL parameter to enable DDL support and filter DDL operations. When used
without options, the DDL parameter causes all DDL operations to be propagated as follows:
? As an Extract parameter, it captures all supported DDL operations that are generated
on all supported database objects and sends them to the trail.
? As a Replicat parameter, it replicates all DDL operations from the GoldenGate trail
and applies them to the target. This is the same as the default behavior without this
parameter.
When used with options, DDL acts as a filtering agent to include or exclude DDL operations
based on:
? scope
? object type
? operation type
? object name
? strings in the DDL command syntax or comments, or both
SPTHREAD | NOSPTHREAD Valid for Extract and Replicat. Creates a separate database
connection thread for stored procedures. The default is
NOSPTHREAD.
TDSPACKETSIZE Valid for Replicat. Sets the TDS packet size for replication to a
Sybase target. Must be set to a multiple of 512. Valid values are
512 (the default) through 8192.
TRUSTEDCONNECTION Valid for Extract and Replicat for SQL Server. Causes
GoldenGate to connect by using trusted connection = yes. Contact
GoldenGate Technical Support before using this option.
XMLBUFSIZE Valid for Extract for Oracle. Sets the size of the memory buffer
that stores XML data that was extracted from the sys.xmltype
attribute of a SDO_GEORASTER object type. The default is 1048576
bytes (1MB). If the data exceeds the default buffer size, Extract
will abend. If this occurs, increase the buffer size and start
Extract again. The valid range of values is 1024 to 10485760 bytes.


Only one DDL parameter can be used in a parameter file, but you can combine multiple
inclusion and exclusion options to filter the DDL to the required level.
? When combined, multiple option specifications are linked logically as AND statements.
? All criteria specified with multiple options must be satisfied for a DDL statement to be
replicated.
? When using complex DDL filtering criteria, it is recommended that you test your
configuration in a test environment before using it in production.
WARNING Do not include any GoldenGate-installed DDL objects in a DDL parameter, in
a TABLE parameter, or in a MAP parameter, nor in a TABLEEXCLUDE or
MAPEXCLUDE parameter. Make certain that wildcard specifications in those
parameters do not include GoldenGate-installed DDL objects. These objects
must not be part of the GoldenGate configuration, but the Extract process
must be aware of operations on them, and that is why you must not explicitly
exclude them from the configuration with an EXCLUDE, TABLEEXCLUDE, or
MAPEXCLUDE parameter statement.
Do not use DDL for an Extract data pump or for a VAM-sort Extract. These process types do
not permit mapping or conversion of DDL and will propogate DDL records automatically
in PASSTHRU mode (see page 250). DDL that is performed on a source table of a certain name
(for example ALTER TABLE TableA...) will be applied by Replicat with the same table name (ALTER
TABLE TableA). It cannot be mapped as ALTER TABLE TableB.
For detailed information about how to use GoldenGate DDL support, see the GoldenGate
for Windows and UNIX Administrator Guide.


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


GGSCI (rac1.rac.meditate.com) 11> edit params etruth

EXTRACT etruth
USERID goldengate@simarsv1, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
DDL INCLUDE MAPPED
TABLE meditate.results;


Since we have bounced the database without stopping the extracts etruth and ecreator will go to status ABENDED. So, we need to restart these processes as:

GGSCI (rac1.rac.meditate.com) 12> start extract etruth


GGSCI (rac1.rac.meditate.com) 12> start extract ecreator

I am not showing logout put here as by now we have done this many times earlier.

Now lets test it as:

Before DDL change talbe in source and target is as:

SQL> desc results
Name Null? Type
----------------------------------------- -------- ----------------------------
DEED_ID NUMBER
DEED VARCHAR2(60)
DEED_RESULT VARCHAR2(100)

SQL>

and after DDL change the table in source is as:


SQL> alter table results add test_col VARCHAR2(30);

Table altered.

SQL> desc results
Name Null? Type
----------------------------------------- -------- ----------------------------
DEED_ID NUMBER
DEED VARCHAR2(60)
DEED_RESULT VARCHAR2(100)
TEST_COL VARCHAR2(30)

SQL>



and target is also as:

SQL> desc results
Name Null? Type
----------------------------- -------- --------------------
DEED_ID NUMBER
DEED VARCHAR2(60)
DEED_RESULT VARCHAR2(100)
TEST_COL VARCHAR2(30)

SQL>


In an instant, one is born, and in an instant, one dies. In an instant one comes, and in an instant one goes. One who recognizes the Shabad merges into it, and is not afflicted by death.

Configure little bit complex Goldengate Replication (Post 21 of series,Configure little bit complex Goldengate Replication on Linux)

O Lord, You are Your Own Glorious Praise. You Yourself speak it; You Yourself hear it and contemplate it. You Yourself are the Jewel, and You are the Appraiser. You Yourself are of Infinite Value. O True Lord, You are Honor and Glory; You Yourself are the Giver.


Index of all the posts of Gurpartap Singh's Blog


Lets create a little bit more data for a litle bit complex replication

create table results (
deed_id number,
deed varchar2(60),
deed_result varchar2(100)
);

CREATE UNIQUE INDEX results_ix ON results(deed_id);


alter table results add constraint results_uq
unique (deed_id)
using index results_ix;


insert into results values (1,'Before Death was thinking of Money','Becomes snake in next incarnations');
insert into results values (2,'Before Death was thinking on Property','Becomes goblin in next incarnations');
insert into results values (3,'Before death was thinking of kids','Becomes pig in next incarnations');
insert into results values (4,'Before death was thinking of women','Becomes prostitute in next incarnations');
insert into results values (5,'Before death was thinking of LORD','Liberated');
commit;



create table life (
HUMAN_ID number,
FIRSTNAME varchar2(30),
LASTNAME varchar2(20),
DATE_OF_BITH date,
AGE_AT_TIME_OF_DEATH number,
DEED_ID_AT_TIME_OF_DEATH number
);

create unique index life_uk1 on life(human_id,firstname,lastname);

ALTER TABLE life
ADD CONSTRAINT life_fk1
FOREIGN KEY (DEED_ID_AT_TIME_OF_DEATH)
REFERENCES results (deed_id);


insert into life values (1,'AB','CD',SYSDATE - 23725,65,1);
insert into life values (1,'EF','GH',SYSDATE - 27375,75,2);
insert into life values (1,'IJ','KL',SYSDATE - 31025,85,3);
insert into life values (1,'MN','OP',SYSDATE - 34675,95,4);
insert into life values (1,'QR','ST',SYSDATE - 29200,80,5);

commit;



create or replace view PEOPLE_CLASSIFIED_ON_DEEDS as
select life.human_id, life.firstname, life.lastname, results.deed
from
life, results
where
results.deed_id=life.DEED_ID_AT_TIME_OF_DEATH
order by life.human_id
;


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

On target this table is not there and the coresponding table is "TENURE" as:

create table tenure (
PERSON_ID number,
INITIALNAME varchar2(30),
ENDNAME varchar2(20),
BIRTH_DATE date,
AGE_AT_DEATH number,
DEED_ID_AT_DEATH number
);

create unique index tenure_uk1 on tenure(PERSON_ID,INITIALNAME,ENDNAME);


ALTER TABLE tenure
ADD CONSTRAINT tenure_fk1
FOREIGN KEY (DEED_ID_AT_DEATH)
REFERENCES results (deed_id);

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

I have created the ddl on the targetside and now we can load initial data to it with expdp, insert (as just 5 recods) or we can use goldengate initial load method ( we have already used teh goldengate initial load method in old post so I will just use inset as I understand eveybody knows how yo use expdp/impdp

insert into tenure values (1,'AB','CD',SYSDATE - 23725,65,1);
insert into tenure values (1,'EF','GH',SYSDATE - 27375,75,2);
insert into tenure values (1,'IJ','KL',SYSDATE - 31025,85,3);
insert into tenure values (1,'MN','OP',SYSDATE - 34675,95,4);
insert into tenure values (1,'QR','ST',SYSDATE - 29200,80,5);

commit;

Output is as:

SQL> create table tenure (
PERSON_ID number,
INITIALNAME varchar2(30),
ENDNAME varchar2(20),
BIRTH_DATE date,
AGE_AT_DEATH number,
DEED_ID_AT_DEATH number
);
2 3 4 5 6 7 8
Table created.

SQL> create unique index tenure_uk1 on tenure(PERSON_ID,INITIALNAME,ENDNAME);

Index created.

SQL> ALTER TABLE tenure
ADD CONSTRAINT tenure_fk1
FOREIGN KEY (DEED_ID_AT_DEATH)
REFERENCES results (deed_id);
2 3 4
Table altered.

SQL> insert into tenure values (1,'AB','CD',SYSDATE - 23725,65,1);

1 row created.

SQL> insert into tenure values (1,'EF','GH',SYSDATE - 27375,75,2);
insert into tenure values (1,'IJ','KL',SYSDATE - 31025,85,3);
insert into tenure values (1,'MN','OP',SYSDATE - 34675,95,4);
insert into tenure values (1,'QR','ST',SYSDATE - 29200,80,5);

commit;

1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL> SQL>
Commit complete.

SQL> select * from tenure;

PERSON_ID INITIALNAME ENDNAME BIRTH_DAT AGE_AT_DEATH DEED_ID_AT_DEATH
---------- ------------------------------ -------------------- --------- ------------ ----------------
1 AB CD 28-NOV-45 65 1
1 EF GH 01-DEC-35 75 2
1 IJ KL 03-DEC-25 85 3
1 MN OP 06-DEC-15 95 4
1 QR ST 02-DEC-30 80 5

SQL>
=========================================================================================

Now lets start a little bit complex replication:

Lets replicate records from table life in source to table tenure in target:

Parameter file for source should be:

ecreator
ADD EXTRACT ecreator, TRANLOG, BEGIN NOW, threads 3

ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr, EXTRACT ecreator


EDIT PARAMS ecreator

EXTRACT ecreator
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr
TABLE meditate.life;

pcreator
ADD EXTRACT pcreator, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/cr

ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr, EXTRACT pcreator

EDIT PARAMS pcreator

EXTRACT pcreator
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr
PASSTHRU
TABLE meditate.life;



start EXTRACT ecreator
info all
info extract Ecreator, detail

start extract Pcreator
info extract Ecreator, detail
info all


Output is as:

GGSCI (rac1.rac.meditate.com) 31> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:00
EXTRACT RUNNING ETRUTH 00:00:00 00:00:05
EXTRACT RUNNING PTRUTH 00:00:00 00:00:00


GGSCI (rac1.rac.meditate.com) 32> ADD EXTRACT ecreator, TRANLOG, BEGIN NOW, threads 3
EXTRACT added.


GGSCI (rac1.rac.meditate.com) 33> ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr, EXTRACT ecreator
EXTTRAIL added.


GGSCI (rac1.rac.meditate.com) 34> EDIT PARAMS ecreator



GGSCI (rac1.rac.meditate.com) 35> ADD EXTRACT pcreator, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/cr
EXTRACT added.


GGSCI (rac1.rac.meditate.com) 36> ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr, EXTRACT pcreator
RMTTRAIL added.


GGSCI (rac1.rac.meditate.com) 37> EDIT PARAMS pcreator



GGSCI (rac1.rac.meditate.com) 38> start EXTRACT ecreator

Sending START request to MANAGER ...
EXTRACT ECREATOR starting


GGSCI (rac1.rac.meditate.com) 39> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED ECREATOR 00:00:00 00:02:06
EXTRACT RUNNING ELORD1 00:00:00 00:00:02
EXTRACT RUNNING ETRUTH 00:00:00 00:00:05
EXTRACT STOPPED PCREATOR 00:00:00 00:01:31
EXTRACT RUNNING PTRUTH 00:00:00 00:00:05


GGSCI (rac1.rac.meditate.com) 40> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ECREATOR 00:00:00 00:00:09
EXTRACT RUNNING ELORD1 00:00:00 00:00:07
EXTRACT RUNNING ETRUTH 00:00:00 00:00:05
EXTRACT STOPPED PCREATOR 00:00:00 00:02:50
EXTRACT RUNNING PTRUTH 00:00:00 00:00:04


GGSCI (rac1.rac.meditate.com) 41> info extract Ecreator, detail

EXTRACT ECREATOR Last Started 2010-11-12 15:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-12 15:02:50 Thread 1, Seqno 25/, RBA 16292368
Log Read Checkpoint Oracle Redo Logs
2010-11-12 15:02:50 Thread 2, Seqno 25, RBA 6216704
Log Read Checkpoint Oracle Redo Logs
2010-11-12 15:02:49 Thread 3, Seqno 21, RBA 38467584

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

/u01/app/oracle/product/goldengate/dirdat/cr 0 1012 10

Extract Source Begin End

+RECV1/simar/onlinelog/group_5.261.734045029 2010-11-12 14:59 2010-11-12 15:02
Not Available * Initialized * 2010-11-12 14:59


Current directory /u01/app/oracle/product/goldengate

Report file /u01/app/oracle/product/goldengate/dirrpt/ECREATOR.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/ecreator.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/ECREATOR.cpe
Process file /u01/app/oracle/product/goldengate/dirpcs/ECREATOR.pce
Stdout file /u01/app/oracle/product/goldengate/dirout/ECREATOR.out
Error log /u01/app/oracle/product/goldengate/ggserr.log


GGSCI (rac1.rac.meditate.com) 42> start extract Pcreator

Sending START request to MANAGER ...
EXTRACT PCREATOR starting


GGSCI (rac1.rac.meditate.com) 43> info extract Ecreator, detail

EXTRACT ECREATOR Last Started 2010-11-12 15:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-12 15:03:10 Thread 1, Seqno 25, RBA 16301072
Log Read Checkpoint Oracle Redo Logs
2010-11-12 15:03:10 Thread 2, Seqno 25, RBA 6225920
Log Read Checkpoint Oracle Redo Logs
2010-11-12 15:03:09 Thread 3, Seqno 21, RBA 38482432

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

/u01/app/oracle/product/goldengate/dirdat/cr 0 1012 10

Extract Source Begin End

+RECV1/simar/onlinelog/group_5.261.734045029 2010-11-12 14:59 2010-11-12 15:03
Not Available * Initialized * 2010-11-12 14:59


Current directory /u01/app/oracle/product/goldengate

Report file /u01/app/oracle/product/goldengate/dirrpt/ECREATOR.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/ecreator.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/ECREATOR.cpe
Process file /u01/app/oracle/product/goldengate/dirpcs/ECREATOR.pce
Stdout file /u01/app/oracle/product/goldengate/dirout/ECREATOR.out
Error log /u01/app/oracle/product/goldengate/ggserr.log


GGSCI (rac1.rac.meditate.com) 44> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ECREATOR 00:00:00 00:00:04
EXTRACT RUNNING ELORD1 00:00:00 00:00:01
EXTRACT RUNNING ETRUTH 00:00:00 00:00:09
EXTRACT RUNNING PCREATOR 00:00:00 00:03:37
EXTRACT RUNNING PTRUTH 00:00:00 00:00:00


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





In logs you will see:

2010-11-12 14:59:12 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT ecreator TRANLOG, BEGIN NOW, threads 3.
2010-11-12 14:59:18 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr EXTRACT ecreator.
2010-11-12 14:59:24 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS ecreator.
2010-11-12 14:59:48 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT pcreator EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/cr.
2010-11-12 14:59:54 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr EXTRACT pcreator.
2010-11-12 15:00:02 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS pcreator.
2010-11-12 15:00:23 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start EXTRACT ecreator.
2010-11-12 15:00:23 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ECREATOR ).
2010-11-12 15:00:23 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ECREATOR starting.
2010-11-12 15:00:23 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ecreator.prm: EXTRACT ECREATOR starting.
2010-11-12 15:01:31 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, ecreator.prm: Default thread stack size: 33554432.
2010-11-12 15:01:37 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ecreator.prm: EXTRACT ECREATOR started.
2010-11-12 15:01:37 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ecreator.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/cr000000, at RBA 0 (file not opened).
2010-11-12 15:01:37 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, ecreator.prm: Output file /u01/app/oracle/product/goldengate/dirdat/cr is using format RELEASE 10.4.
2010-11-12 15:03:09 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract Pcreator.
2010-11-12 15:03:09 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT PCREATOR ).
2010-11-12 15:03:09 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PCREATOR starting.
2010-11-12 15:03:09 GGS INFO 310 Oracle GoldenGate Capture for Oracle, pcreator.prm: EXTRACT PCREATOR starting.
2010-11-12 15:03:09 GGS INFO 310 Oracle GoldenGate Capture for Oracle, pcreator.prm: EXTRACT PCREATOR started.
2010-11-12 15:03:14 GGS INFO 406 Oracle GoldenGate Capture for Oracle, pcreator.prm: Socket buffer size set to 27985 (flush size 27985).
2010-11-12 15:03:29 GGS INFO 112 Oracle GoldenGate Capture for Oracle, pcreator.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/cr000000, at RBA 0 (file not opened).
2010-11-12 15:03:29 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, pcreator.prm: Output file /u01/app/oracle/product/goldengate/dirdat/cr is using format RELEASE 10.4.



In out case the definition of tables in source and target are not same so we need to create a definition file
so that target could understand what is coming to it.

I will create a file defecreator.prm under directroy $GOLDEN_GATE_INSALL/dirprm/defjun.prm on source as:

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

DEFSFILE /u01/app/oracle/product/goldengate/dirdat/defecreator
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TABLE meditate.life;

and then execute:

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



Here is the output:

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

$ ./defgen paramfile dirprm/defecreator.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-11-12 15:48:20
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Thu Sep 3 03:28:30 EDT 2009, Release 2.6.18-164.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: 26919

***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/app/oracle/product/goldengate/dirdat/defecreator
USERID goldengate, PASSWORD ************************************************* ENCRYPTKEY default
TABLE meditate.life;
Retrieving definition for MEDITATE.LIFE



Definitions generated for 1 tables in /u01/app/oracle/product/goldengate/dirdat/defecreator


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




This generated the definition file for this and now we will move this definition file to target with scp to location: /u01/app/oracle/product/goldengate/dirdat/defecreator

Then we need to use the following paramter in the target paramter file so that target knows of
the definitions from the souce sife.

SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/defecreator

----------------------------------------------------------------------------------------------------
On target lets add the replicats taht will apply changes from source to the target database as:


ADD REPLICAT rcreator, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr


Create the parameter file for the Replicat group

In the parameter file we have new parameter SOURCEDEFS that defines the
definition of the source objects.
then we have map parameter that defines the mapping of tables and the COLMAP parameter
that defines the column mapping of the tables. KEYCOL designates columns that uniquely identify rows.


EDIT PARAMS rcreator

REPLICAT rcreator
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/defecreator
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
MAP meditate.life, TARGET CONSCIOUSNESS.tenure, &
COLMAP (PERSON_ID=HUMAN_ID, &
INITIALNAME=FIRSTNAME, &
ENDNAME=LASTNAME, &
BIRTH_DATE=DATE_OF_BITH, &
AGE_AT_DEATH=AGE_AT_TIME_OF_DEATH, &
DEED_ID_AT_DEATH=DEED_ID_AT_TIME_OF_DEATH, &
KEYCOLS (PERSON_ID, INITIALNAME,ENDNAME);

start replicat Rcreator
status replicat Rcreator

Output is as:

GGSCI (goldengate) 48> ADD REPLICAT rcreator, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr
REPLICAT added.


GGSCI (goldengate) 49> EDIT PARAMS rcreator

REPLICAT rcreator
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/defecreator
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
MAP meditate.life, TARGET CONSCIOUSNESS.tenure, &
COLMAP (PERSON_ID=HUMAN_ID, &
INITIALNAME=FIRSTNAME, &
ENDNAME=LASTNAME, &
BIRTH_DATE=DATE_OF_BITH, &
AGE_AT_DEATH=AGE_AT_TIME_OF_DEATH, &
DEED_ID_AT_DEATH=DEED_ID_AT_TIME_OF_DEATH, &
KEYCOLS (PERSON_ID, INITIALNAME,ENDNAME);





GGSCI (goldengate) 52> start replicat Rcreator

Sending START request to MANAGER ...
REPLICAT RCREATOR starting


GGSCI (goldengate) 53> status replicat Rcreator
REPLICAT RCREATOR: RUNNING


GGSCI (goldengate) 54> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RCREATOR 00:00:00 00:00:05
REPLICAT RUNNING RLORD1 00:00:00 00:00:09
REPLICAT RUNNING RTRUTH 00:00:00 00:00:06


GGSCI (goldengate) 55>



In logs you will see:

2010-11-12 14:40:19 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD REPLICAT rcreator EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/cr.
2010-11-12 14:40:31 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS rcreator.
2010-11-12 14:42:25 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start replicat Rcreator.
2010-11-12 14:42:25 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.169 (START REPLICAT RCREATOR ).
2010-11-12 14:42:25 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RCREATOR starting.
2010-11-12 14:42:25 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rcreator.prm: REPLICAT RCREATOR starting.
2010-11-12 14:42:26 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rcreator.prm: REPLICAT RCREATOR started.






Now, do replication


On Source:
SQL> set line 300
SQL> select * from life;

HUMAN_ID FIRSTNAME LASTNAME DATE_OF_BITH AGE_AT_TIME_OF_DEATH DEED_ID_AT_TIME_OF_DEATH
---------- ------------------------------ -------------------- ------------------ -------------------- ------------------------
1 AB CD 26-NOV-45 65 1
1 EF GH 29-NOV-35 75 2
1 IJ KL 01-DEC-25 85 3
1 MN OP 04-DEC-15 95 4
1 QR ST 30-NOV-30 80 5

5 rows selected.

SQL>

On target:

SQL> set line 300
SQL> select * from tenure;

PERSON_ID INITIALNAME ENDNAME BIRTH_DAT AGE_AT_DEATH DEED_ID_AT_DEATH
---------- ------------------------------ -------------------- --------- ------------ ----------------
1 AB CD 28-NOV-45 65 1
1 EF GH 01-DEC-35 75 2
1 IJ KL 03-DEC-25 85 3
1 MN OP 06-DEC-15 95 4
1 QR ST 02-DEC-30 80 5

SQL>


On Souce:

select * from life;
insert into life values (2,'AB','CD',SYSDATE - 23725,65,1);
insert into life values (2,'EF','GH',SYSDATE - 27375,75,2);
insert into life values (2,'IJ','KL',SYSDATE - 31025,85,3);
insert into life values (2,'MN','OP',SYSDATE - 34675,95,4);
insert into life values (2,'QR','ST',SYSDATE - 29200,80,5);
commit;


Output is as:

SQL> select * from life;

HUMAN_ID FIRSTNAME LASTNAME DATE_OF_BITH AGE_AT_TIME_OF_DEATH DEED_ID_AT_TIME_OF_DEATH
---------- ------------------------------ -------------------- ------------------ -------------------- ------------------------
1 AB CD 26-NOV-45 65 1
1 EF GH 29-NOV-35 75 2
1 IJ KL 01-DEC-25 85 3
1 MN OP 04-DEC-15 95 4
1 QR ST 30-NOV-30 80 5

5 rows selected.

SQL> insert into life values (2,'AB','CD',SYSDATE - 23725,65,1);
insert into life values (2,'EF','GH',SYSDATE - 27375,75,2);
insert into life values (2,'IJ','KL',SYSDATE - 31025,85,3);
insert into life values (2,'MN','OP',SYSDATE - 34675,95,4);
insert into life values (2,'QR','ST',SYSDATE - 29200,80,5);
commit;
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL> commit;

Commit complete.

SQL>


and On target replication is done as:

SQL> select * from tenure;

PERSON_ID INITIALNAME ENDNAME BIRTH_DAT AGE_AT_DEATH DEED_ID_AT_DEATH
---------- ------------------------------ -------------------- --------- ------------ ----------------
1 AB CD 28-NOV-45 65 1
1 EF GH 01-DEC-35 75 2
1 IJ KL 03-DEC-25 85 3
1 MN OP 06-DEC-15 95 4
1 QR ST 02-DEC-30 80 5
2 AB CD 28-NOV-45 65 1
2 EF GH 01-DEC-35 75 2
2 IJ KL 03-DEC-25 85 3
2 MN OP 06-DEC-15 95 4
2 QR ST 02-DEC-30 80 5
1 AB CD 28-NOV-45 65 1
1 EF GH 01-DEC-35 75 2
1 IJ KL 03-DEC-25 85 3
1 MN OP 06-DEC-15 95 4
1 QR ST 02-DEC-30 80 5

10 rows selected.

SQL>


Next we will do a little bit more complex parameter file:

Then the servers on source side crashed and then after a reboot/crash we need to start
the manager process if manager process is not configured to start at reboot. Also
we can confugure all the extracts to start when manager process is started but we haven't then
you need to start it manually as following.


$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28

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



GGSCI (rac1.rac.meditate.com) 1> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT ABENDED ECREATOR 00:00:00 09:55:31
EXTRACT ABENDED ELORD1 00:00:00 09:55:49
EXTRACT ABENDED ETRUTH 00:00:00 09:55:48
EXTRACT ABENDED PCREATOR 00:00:00 09:55:54
EXTRACT ABENDED PTRUTH 00:00:00 09:55:54


GGSCI (rac1.rac.meditate.com) 2> start extract ETRUTH

Sending START request to MANAGER ...
EXTRACT ETRUTH starting


GGSCI (rac1.rac.meditate.com) 8> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT ABENDED ECREATOR 00:00:00 09:59:42
EXTRACT ABENDED ELORD1 00:00:00 10:00:00
EXTRACT RUNNING ETRUTH 00:00:00 00:00:01
EXTRACT ABENDED PCREATOR 00:00:00 10:00:05
EXTRACT ABENDED PTRUTH 00:00:00 10:00:05


GGSCI (rac1.rac.meditate.com) 9> start extract PTRUTH

Sending START request to MANAGER ...
EXTRACT PTRUTH starting


GGSCI (rac1.rac.meditate.com) 10> start extract ECREATOR

Sending START request to MANAGER ...
EXTRACT ECREATOR starting


GGSCI (rac1.rac.meditate.com) 13> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ECREATOR 00:00:00 00:00:10
EXTRACT ABENDED ELORD1 00:00:00 10:03:37
EXTRACT RUNNING ETRUTH 00:00:00 00:00:08
EXTRACT ABENDED PCREATOR 00:00:00 10:03:42
EXTRACT RUNNING PTRUTH 00:00:00 00:00:00


GGSCI (rac1.rac.meditate.com) 14> start extract PCREATOR

Sending START request to MANAGER ...
EXTRACT PCREATOR starting


GGSCI (rac1.rac.meditate.com) 15> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ECREATOR 00:00:00 00:00:02
EXTRACT ABENDED ELORD1 00:00:00 10:04:44
EXTRACT RUNNING ETRUTH 00:00:00 00:00:00
EXTRACT RUNNING PCREATOR 00:00:00 00:00:09
EXTRACT RUNNING PTRUTH 00:00:00 00:00:07


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


==============================================================================================================
Info in logs on source

2010-11-13 08:43:13 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract ETRUTH.
2010-11-13 08:43:13 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ETRUTH ).
2010-11-13 08:43:13 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ETRUTH starting.
2010-11-13 08:43:13 GGS INFO 310 Oracle GoldenGate Capture for Oracle, etruth.prm: EXTRACT ETRUTH starting.
2010-11-13 08:46:34 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, etruth.prm: Default thread stack size: 33554432.
2010-11-13 08:46:35 GGS INFO 310 Oracle GoldenGate Capture for Oracle, etruth.prm: EXTRACT ETRUTH started.
2010-11-13 08:46:36 GGS INFO 112 Oracle GoldenGate Capture for Oracle, etruth.prm: Recovery initialization completed for target file /u01/app/oracle/product/goldengate/dirdat/tr000001, at RBA 1071.
2010-11-13 08:46:36 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, etruth.prm: Output file /u01/app/oracle/product/goldengate/dirdat/tr is using format RELEASE 10.4.
2010-11-13 08:46:36 GGS INFO 224 Oracle GoldenGate Capture for Oracle, etruth.prm: Rolling over remote file /u01/app/oracle/product/goldengate/dirdat/tr000001.
2010-11-13 08:46:36 GGS INFO 112 Oracle GoldenGate Capture for Oracle, etruth.prm: Recovery completed for target file /u01/app/oracle/product/goldengate/dirdat/tr000002, at RBA 1071.
2010-11-13 08:46:36 GGS INFO 112 Oracle GoldenGate Capture for Oracle, etruth.prm: Recovery completed for all targets.
2010-11-13 08:46:37 GGS INFO 220 Oracle GoldenGate Capture for Oracle, etruth.prm: Found crash recovery marker from thread #2 on sequence 28 at RBA 1040. Aborting uncommitted transactions.
2010-11-13 08:47:25 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract PTRUTH.
2010-11-13 08:47:25 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT PTRUTH ).
2010-11-13 08:47:25 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PTRUTH starting.
2010-11-13 08:47:25 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ptruth.prm: EXTRACT PTRUTH starting.
2010-11-13 08:47:25 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ptruth.prm: EXTRACT PTRUTH started.
2010-11-13 08:47:30 GGS INFO 406 Oracle GoldenGate Capture for Oracle, ptruth.prm: Socket buffer size set to 27985 (flush size 27985).
2010-11-13 08:47:45 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ptruth.prm: Recovery initialization completed for target file /u01/app/oracle/product/goldengate/dirdat/tr000000, at RBA 1954.
2010-11-13 08:47:45 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, ptruth.prm: Output file /u01/app/oracle/product/goldengate/dirdat/tr is using format RELEASE 10.4.
2010-11-13 08:47:45 GGS INFO 224 Oracle GoldenGate Capture for Oracle, ptruth.prm: Rolling over remote file /u01/app/oracle/product/goldengate/dirdat/tr000001.
2010-11-13 08:47:45 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ptruth.prm: Recovery completed for target file /u01/app/oracle/product/goldengate/dirdat/tr000001, at RBA 511.
2010-11-13 08:47:45 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ptruth.prm: Recovery completed for all targets.
2010-11-13 08:47:57 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract ECREATOR.
2010-11-13 08:47:57 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ECREATOR ).
2010-11-13 08:47:57 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ECREATOR starting.
2010-11-13 08:47:57 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ecreator.prm: EXTRACT ECREATOR starting.
2010-11-13 08:50:22 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, ecreator.prm: Default thread stack size: 33554432.
2010-11-13 08:50:25 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ecreator.prm: EXTRACT ECREATOR started.
2010-11-13 08:50:25 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ecreator.prm: Recovery initialization completed for target file /u01/app/oracle/product/goldengate/dirdat/cr000000, at RBA 4816.
2010-11-13 08:50:25 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, ecreator.prm: Output file /u01/app/oracle/product/goldengate/dirdat/cr is using format RELEASE 10.4.
2010-11-13 08:50:25 GGS INFO 224 Oracle GoldenGate Capture for Oracle, ecreator.prm: Rolling over remote file /u01/app/oracle/product/goldengate/dirdat/cr000000.
2010-11-13 08:50:25 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ecreator.prm: Recovery completed for target file /u01/app/oracle/product/goldengate/dirdat/cr000001, at RBA 1075.
2010-11-13 08:50:25 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ecreator.prm: Recovery completed for all targets.
2010-11-13 08:50:25 GGS INFO 220 Oracle GoldenGate Capture for Oracle, ecreator.prm: Found crash recovery marker from thread #2 on sequence 28 at RBA 1040. Aborting uncommitted transactions.
2010-11-13 08:50:59 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract PCREATOR.
2010-11-13 08:50:59 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT PCREATOR ).
2010-11-13 08:50:59 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PCREATOR starting.
2010-11-13 08:50:59 GGS INFO 310 Oracle GoldenGate Capture for Oracle, pcreator.prm: EXTRACT PCREATOR starting.
2010-11-13 08:51:05 GGS INFO 310 Oracle GoldenGate Capture for Oracle, pcreator.prm: EXTRACT PCREATOR started.
2010-11-13 08:51:10 GGS INFO 406 Oracle GoldenGate Capture for Oracle, pcreator.prm: Socket buffer size set to 27985 (flush size 27985).
2010-11-13 08:51:24 GGS INFO 112 Oracle GoldenGate Capture for Oracle, pcreator.prm: Recovery initialization completed for target file /u01/app/oracle/product/goldengate/dirdat/cr000000, at RBA 4886.
2010-11-13 08:51:24 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, pcreator.prm: Output file /u01/app/oracle/product/goldengate/dirdat/cr is using format RELEASE 10.4.
2010-11-13 08:51:24 GGS INFO 224 Oracle GoldenGate Capture for Oracle, pcreator.prm: Rolling over remote file /u01/app/oracle/product/goldengate/dirdat/cr000001.
2010-11-13 08:51:24 GGS INFO 112 Oracle GoldenGate Capture for Oracle, pcreator.prm: Recovery completed for target file /u01/app/oracle/product/goldengate/dirdat/cr000001, at RBA 511.
2010-11-13 08:51:24 GGS INFO 112 Oracle GoldenGate Capture for Oracle, pcreator.prm: Recovery completed for all targets.


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

Info from logs in target:

2010-11-13 08:16:31 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host 192.168.1.176 (START SERVER CPU -1 PRI -1 PARAMS ).
2010-11-13 08:16:31 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started collector process (Port 7846).
2010-11-13 08:16:31 GGS INFO 373 Oracle GoldenGate Collector, port 7846: Waiting for connection (started dynamically).
2010-11-13 08:16:50 GGS INFO 373 Oracle GoldenGate Collector: Connected to 192.168.1.176:13240.
2010-11-13 08:16:50 GGS INFO 370 Oracle GoldenGate Collector, port 7846: Opening /u01/app/oracle/product/goldengate/dirdat/tr000000 (byte 1954, current EOF 1954).
2010-11-13 08:16:50 GGS INFO 371 Oracle GoldenGate Collector, port 7846: Closing /u01/app/oracle/product/goldengate/dirdat/tr000000.
2010-11-13 08:16:50 GGS INFO 370 Oracle GoldenGate Collector, port 7846: Opening /u01/app/oracle/product/goldengate/dirdat/tr000001 (byte -1, current EOF 0).
2010-11-13 08:16:50 GGS INFO 323 Oracle GoldenGate Delivery for Oracle, rtruth.prm: Processed extract process RESTART_ABEND record at seq 1, rba 511 (aborted 0 records).
2010-11-13 08:16:51 GGS INFO 323 Oracle GoldenGate Delivery for Oracle, rtruth.prm: Processed extract process RESTART_ABEND record at seq 1, rba 572 (aborted 0 records).
2010-11-13 08:20:10 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host 192.168.1.176 (START SERVER CPU -1 PRI -1 PARAMS ).
2010-11-13 08:20:10 GGS INFO 373 Oracle GoldenGate Collector, port 7847: Waiting for connection (started dynamically).
2010-11-13 08:20:10 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started collector process (Port 7847).
2010-11-13 08:20:30 GGS INFO 373 Oracle GoldenGate Collector: Connected to 192.168.1.176:16891.
2010-11-13 08:20:30 GGS INFO 370 Oracle GoldenGate Collector, port 7847: Opening /u01/app/oracle/product/goldengate/dirdat/cr000000 (byte 4886, current EOF 4886).
2010-11-13 08:20:30 GGS INFO 371 Oracle GoldenGate Collector, port 7847: Closing /u01/app/oracle/product/goldengate/dirdat/cr000000.
2010-11-13 08:20:30 GGS INFO 370 Oracle GoldenGate Collector, port 7847: Opening /u01/app/oracle/product/goldengate/dirdat/cr000001 (byte -1, current EOF 0).
2010-11-13 08:20:30 GGS INFO 323 Oracle GoldenGate Delivery for Oracle, rcreator.prm: Processed extract process RESTART_ABEND record at seq 1, rba 511 (aborted 0 records).
2010-11-13 08:20:31 GGS INFO 323 Oracle GoldenGate Delivery for Oracle, rcreator.prm: Processed extract process RESTART_ABEND record at seq 1, rba 574 (aborted 0 records).

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


Now lets modify the parameter file on the target side so that it just picks inserts as:

Take the backup of the old parameter file on target as:

oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate/dirprm
$ cp rcreator.prm rcreator.prm.orig

oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate/dirprm
$ ls -lart
total 36
-rw-rw-rw- 1 oracle oinstall 11 Sep 12 22:10 mgr.prm
-rw-rw-rw- 1 oracle oinstall 233 Sep 13 21:38 r1cust.prm
-rw-rw-rw- 1 oracle oinstall 185 Nov 7 21:15 onetime.prm
drwxr-xr-x 13 oracle oinstall 4096 Nov 7 22:18 ..
-rw-rw-rw- 1 oracle oinstall 185 Nov 8 22:53 rlord1.prm
-rw-rw-rw- 1 oracle oinstall 183 Nov 9 22:42 rtruth.prm
-rw-rw-rw- 1 oracle oinstall 458 Nov 12 15:30 rcreator.prm
-rw-r--r-- 1 oracle oinstall 458 Nov 13 10:38 rcreator.prm.orig
drwxrwxr-x 2 oracle oinstall 4096 Nov 13 10:38 .

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


Now we will use some more parameters in the parameter file and they are described as below in the documentation:

DISCARDFILE :

Valid for Extract and Replicat
Use the DISCARDFILE parameter to generate a discard file to which GoldenGate can log
records that it cannot process. Records can be discarded for several reasons. For example,
a record is discarded if the underlying table structure changed since the record was written
to the trail. You can use the discard file to help you identify the cause of processing errors.
Each entry in the discard file contains the discarded record buffer and an error code
indicating the reason. GoldenGate creates the specified discard file in the dirrpt subdirectory
of the GoldenGate installation directory. You can view it with a text editor or by
using the following command in GGSCI.
VIEW REPORT
Where: is the fully qualified name of the discard file.
To prevent having to perform manual maintenance of discard files, use either the PURGE or
APPEND option. Otherwise, you must specify a different discard file name before starting
each process run, because GoldenGate will not write to an existing discard file.
To set an upper limit for the size of the file, use either the MAXBYTES or MEGABYTES option. If
the specified size is exceeded, the process will abend.
Default By default, GoldenGate does not generate a discard file.


DISCARDROLLOVER:

Valid for Extract and Replicat
Use the DISCARDROLLOVER parameter to set a schedule for aging discard files. For long or
continuous runs, setting an aging schedule prevents the discard file from filling up and
causing the process to abend, and it provides a predictable set of archives that can be
included in your archiving routine.
When the DISCARDROLLOVER age point is reached, a new discard file is created, and old files
are renamed in the format of ., where:
? is the name of the Extract or Replicat group
? is a number that gets incremented by one each time a new file is created, for
example: myext0.dsc, myext1.dsc, myext2.dsc, and so forth.
You can specify a time of day, a day of the week, or both. Specifying just a time of day (AT
option) without a day of the week (ON option) generates a discard file at the specified time
every day.
Default Disabled. No rules specified.


REPERROR:

Use REPERROR to specify an error and a response that together control how Replicat responds
to the error when executing the MAP statement. You can use REPERROR at the MAP level to
override and supplement global error handling rules set with the REPERROR parameter.
Multiple REPERROR statements can be applied to the same MAP statement to
enable automatic, comprehensive management of errors and interruption-free replication
processing.

DEFAULT Sets a global response to all errors except those for which explicit
REPERROR statements are specified.


GETDELETES | IGNOREDELETES:

Valid for Extract and Replicat
Use the GETDELETES and IGNOREDELETES parameters to control whether or not GoldenGate
processes delete operations. These parameters are table-specific. One parameter remains
in effect for all subsequent TABLE or MAP statements, until the other parameter is
encountered.



GETUPDATES | IGNOREUPDATES:

Valid for Extract and Replicat
Use the GETUPDATES and IGNOREUPDATES parameters to control whether or not GoldenGate
processes update operations. The parameters are table-specific. One parameter remains in
effect for all subsequent TABLE or MAP statements, until the other parameter is encountered.


GETINSERTS | IGNOREINSERTS:

Valid for Extract and Replicat
Use the GETINSERTS and IGNOREINSERTS parameters to control whether or not insert operations
are processed by GoldenGate. The parameters are table-specific. One parameter remains
in effect for all subsequent TABLE or MAP statements, until the other parameter is
encountered.



and now update the parameter file on target as:

GGSCI (goldengate) 7> edit params rcreator


REPLICAT rcreator
SOURCEDEFS /u01/app/oracle/product/goldengate/dirdat/defecreator
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/creator_err, purge
DISCARDROLLOVER ON saturday
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
REPERROR (DEFAULT, DISCARD)
IGNOREDELETES
IGNOREUPDATES
GETINSERTS
MAP meditate.life, TARGET CONSCIOUSNESS.tenure, &
COLMAP (PERSON_ID=HUMAN_ID, &
INITIALNAME=FIRSTNAME, &
ENDNAME=LASTNAME, &
BIRTH_DATE=DATE_OF_BITH, &
AGE_AT_DEATH=AGE_AT_TIME_OF_DEATH, &
DEED_ID_AT_DEATH=DEED_ID_AT_TIME_OF_DEATH), &
KEYCOLS (PERSON_ID, INITIALNAME,ENDNAME);


Now stop and start the replicat on target as:

GGSCI (goldengate) 9> stop replicat RCREATOR

Sending STOP request to REPLICAT RCREATOR ...
Request processed.


GGSCI (goldengate) 10> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
REPLICAT STOPPED RCREATOR 00:00:00 00:00:04
REPLICAT RUNNING RLORD1 00:00:00 00:00:05
REPLICAT RUNNING RTRUTH 00:00:00 00:00:04


GGSCI (goldengate) 11> start REPLICAT RCREATOR

Sending START request to MANAGER ...
REPLICAT RCREATOR starting


GGSCI (goldengate) 12>



Now, lets see if our replication is working:

Source before insets:

SQL> l
1* select * from life
SQL> /

HUMAN_ID FIRSTNAME LASTNAME DATE_OF_BITH AGE_AT_TIME_OF_DEATH DEED_ID_AT_TIME_OF_DEATH
---------- ------------------------------ -------------------- ------------------ -------------------- ------------------------
1 AB CD 26-NOV-45 65 1
1 EF GH 29-NOV-35 75 2
1 IJ KL 01-DEC-25 85 3
1 MN OP 04-DEC-15 95 4
1 QR ST 30-NOV-30 80 5
2 AB CD 28-NOV-45 65 1
2 EF GH 01-DEC-35 75 2
2 IJ KL 03-DEC-25 85 3
2 MN OP 06-DEC-15 95 4
2 QR ST 02-DEC-30 80 5

10 rows selected.

SQL>

Target before inserts in source as:

SQL> l
1* select * from tenure
SQL> /

PERSON_ID INITIALNAME ENDNAME BIRTH_DAT AGE_AT_DEATH DEED_ID_AT_DEATH
---------- ------------------------------ -------------------- --------- ------------ ----------------
2 AB CD 28-NOV-45 65 1
2 EF GH 01-DEC-35 75 2
2 IJ KL 03-DEC-25 85 3
2 MN OP 06-DEC-15 95 4
2 QR ST 02-DEC-30 80 5
1 AB CD 28-NOV-45 65 1
1 EF GH 01-DEC-35 75 2
1 IJ KL 03-DEC-25 85 3
1 MN OP 06-DEC-15 95 4
1 QR ST 02-DEC-30 80 5

10 rows selected.

SQL>



Now lets insert in source as:

insert into life values (3,'AB','CD',SYSDATE - 23725,65,1);
insert into life values (3,'EF','GH',SYSDATE - 27375,75,2);
insert into life values (3,'IJ','KL',SYSDATE - 31025,85,3);
insert into life values (3,'MN','OP',SYSDATE - 34675,95,4);
insert into life values (3,'QR','ST',SYSDATE - 29200,80,5);
commit;


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


SQL> insert into life values (3,'AB','CD',SYSDATE - 23725,65,1);
insert into life values (3,'EF','GH',SYSDATE - 27375,75,2);
insert into life values (3,'IJ','KL',SYSDATE - 31025,85,3);
insert into life values (3,'MN','OP',SYSDATE - 34675,95,4);
insert into life values (3,'QR','ST',SYSDATE - 29200,80,5);
commit;

1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
Commit complete.

SQL>

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

Target:

SQL> l
1* select * from tenure
SQL> /

PERSON_ID INITIALNAME ENDNAME BIRTH_DAT AGE_AT_DEATH DEED_ID_AT_DEATH
---------- ------------------------------ -------------------- --------- ------------ ----------------
3 AB CD 29-NOV-45 65 1
3 EF GH 02-DEC-35 75 2
3 IJ KL 04-DEC-25 85 3
3 MN OP 07-DEC-15 95 4
3 QR ST 03-DEC-30 80 5
2 AB CD 28-NOV-45 65 1
2 EF GH 01-DEC-35 75 2
2 IJ KL 03-DEC-25 85 3
2 MN OP 06-DEC-15 95 4
2 QR ST 02-DEC-30 80 5
1 AB CD 28-NOV-45 65 1

PERSON_ID INITIALNAME ENDNAME BIRTH_DAT AGE_AT_DEATH DEED_ID_AT_DEATH
---------- ------------------------------ -------------------- --------- ------------ ----------------
1 EF GH 01-DEC-35 75 2
1 IJ KL 03-DEC-25 85 3
1 MN OP 06-DEC-15 95 4
1 QR ST 02-DEC-30 80 5

15 rows selected.

SQL>






.



O Dear Lord, You are the Creator and the Cause. If it is Your Will, please save and protect me; please bless me with the lifestyle of the Lord's Name.