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:
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
?
?
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.
No comments:
Post a Comment