As long as the soul-companion is with the body, it dwells in happiness. But when the companion arises and departs, then the body-bride mingles with dust.
Index of all the posts of Gurpartap Singh's Blog
Lets create table results and create the data for it in source as:
create table results (
deed_id number,
deed varchar2(60),
deed_result varchar2(100)
);
CREATE UNIQUE INDEX results_ix ON results(deed_id);
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');
commit;
Output is as:
SQL>
SQL> show user
USER is "MEDITATE"
SQL> create table results (
deed_id number,
deed varchar2(60),
deed_result varchar2(100)
);
2 3 4 5
Table created.
SQL> CREATE UNIQUE INDEX results_ix ON results(deed_id);
Index created.
SQL>
SQL> insert into results values (1,'Before Death was thinking of Money','Becomes snake in next incarnations');
1 row created.
SQL> 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');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
--------------------------------------------------------------------------------------------
The datapump for goldengate is different than from expdp. This is the secondary extract group that is created on the
source system. This is the optional group but is very useful to beat network failures. When its not configured
extract process writes to a remote trail that is located on the target system using TCP/IP.
But when we have it configured the extract process writes to a local trail and from here Data Pump will read the
trail and write the data over the network to the remote trail located on the target system. It will also be useful
when we are consolidating data from several sources into one central target where data pump on each individual source
system can write to one common trail file on the target.
Now lets create the Extract process
ADD EXTRACT etruth, TRANLOG, BEGIN NOW, threads 3
Create a local trail
Using the ADD EXTRAIL command we will now create a local trail on the source system where the Extract process
will write to and which is then read by the Data Pump process. We will link this local trail to the Primary Extract
group we just created, etruth
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr, EXTRACT etruth
Now, lets create the parameter file for the primary extract group i.e. etruth
EDIT PARAMS etruth
EXTRACT etruth
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
TABLE meditate.results;
Output is as:
GGSCI (rac1.rac.meditate.com) 14> ADD EXTRACT etruth, TRANLOG, BEGIN NOW, threads 3
EXTRACT added.
GGSCI (rac1.rac.meditate.com) 15> ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr, EXTRACT etruth
EXTTRAIL added.
GGSCI (rac1.rac.meditate.com) 16> EDIT PARAMS etruth
EXTRACT etruth
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
TABLE meditate.results;
Output from log is:
2010-11-09 22:03:24 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT etruth TRANLOG, BEGIN NOW,threads 3.
2010-11-09 22:03:50 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr EXTRACT etruth.
2010-11-09 22:05:03 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS etruth.
--------------------------------------------------------------------------------------
Specify the location of the remote trail on the target system
Use the RMTTRAIL to specify the location of the remote trail and associate the same with the Data Pump group as it will
be wriiten to over the network by the data pump process
ADD EXTRACT ptruth, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/tr
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr, EXTRACT ptruth
Create the parameter file for the Data Pump group
Note- the parameter PASSTHRU signifies the mode being used for the Data Pump which means that the names of
the source and target objects are identical and no column mapping or filtering is being performed here.
EDIT PARAMS ptruth
EXTRACT ptruth
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
PASSTHRU
TABLE meditate.results;
Output is as:
GGSCI (rac1.rac.meditate.com) 19> ADD EXTRACT ptruth, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/tr
EXTRACT added.
GGSCI (rac1.rac.meditate.com) 20> ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr, EXTRACT ptruth
RMTTRAIL added.
GGSCI (rac1.rac.meditate.com) 21> EDIT PARAMS ptruth
EXTRACT ptruth
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
PASSTHRU
TABLE meditate.results;
From logs its:
2010-11-09 22:23:26 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT ptruth EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/tr.
2010-11-09 22:23:38 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr EXTRACT ptruth.
2010-11-09 22:23:50 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS ptruth.
----------------------------------------------------------------------------------------
Now lets start extracts etruth and ptruth
GGSCI (rac1.rac.meditate.com) 29> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:03
EXTRACT STOPPED ETRUTH 00:00:01 00:01:08
EXTRACT STOPPED PTRUTH 00:00:00 00:08:21
GGSCI (rac1.rac.meditate.com) 30> start extract ETRUTH
Sending START request to MANAGER ...
EXTRACT ETRUTH starting
GGSCI (rac1.rac.meditate.com) 31> info extract ETRUTH, detail
EXTRACT ETRUTH Last Started 2010-11-09 22:33 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-09 22:33:39 Thread 1, Seqno 17, RBA 36973568
Log Read Checkpoint Oracle Redo Logs
2010-11-09 22:33:40 Thread 2, Seqno 18, RBA 1101312
Log Read Checkpoint Oracle Redo Logs
2010-11-09 22:33:40 Thread 3, Seqno 14, RBA 34875904
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/app/oracle/product/goldengate/dirdat/tr 0 1010 10
Extract Source Begin End
+RECV1/simar/onlinelog/group_6.260.734045033 2010-11-09 22:30 2010-11-09 22:33
Not Available * Initialized * 2010-11-09 22:30
Current directory /u01/app/oracle/product/goldengate
Report file /u01/app/oracle/product/goldengate/dirrpt/ETRUTH.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/etruth.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/ETRUTH.cpe
Process file /u01/app/oracle/product/goldengate/dirpcs/ETRUTH.pce
Stdout file /u01/app/oracle/product/goldengate/dirout/ETRUTH.out
Error log /u01/app/oracle/product/goldengate/ggserr.log
GGSCI (rac1.rac.meditate.com) 32> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:04
EXTRACT RUNNING ETRUTH 00:00:00 00:00:09
EXTRACT STOPPED PTRUTH 00:00:00 00:12:29
GGSCI (rac1.rac.meditate.com) 33> start extract PTRUTH
Sending START request to MANAGER ...
EXTRACT PTRUTH starting
GGSCI (rac1.rac.meditate.com) 34> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:01
EXTRACT RUNNING ETRUTH 00:00:00 00:00:08
EXTRACT RUNNING PTRUTH 00:00:00 00:00:09
GGSCI (rac1.rac.meditate.com) 35>
In logs you will see:
2010-11-09 22:32:05 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract ETRUTH.
2010-11-09 22:32:05 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-09 22:32:05 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ETRUTH starting.
2010-11-09 22:32:05 GGS INFO 310 Oracle GoldenGate Capture for Oracle, etruth.prm: EXTRACT ETRUTH starting.
2010-11-09 22:33:08 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, etruth.prm: Default thread stack size: 33554432.
2010-11-09 22:33:20 GGS INFO 310 Oracle GoldenGate Capture for Oracle, etruth.prm: EXTRACT ETRUTH started.
2010-11-09 22:33:20 GGS INFO 112 Oracle GoldenGate Capture for Oracle, etruth.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/tr000000, at RBA 0 (file not opened).
2010-11-09 22:33:20 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-09 22:36:08 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract PTRUTH.
2010-11-09 22:36:08 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-09 22:36:08 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PTRUTH starting.
2010-11-09 22:36:08 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ptruth.prm: EXTRACT PTRUTH starting.
2010-11-09 22:36:08 GGS INFO 310 Oracle GoldenGate Capture for Oracle, ptruth.prm: EXTRACT PTRUTH started.
2010-11-09 22:36:13 GGS INFO 406 Oracle GoldenGate Capture for Oracle, ptruth.prm: Socket buffer size set to 27985 (flush size 27985).
2010-11-09 22:36:26 GGS INFO 112 Oracle GoldenGate Capture for Oracle, ptruth.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/tr000000, at RBA 0 (file not opened).
2010-11-09 22:36:26 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.
---------------------------------------------------------------------------------------
Now we will create the replicat on the target side.
First I will create the table in the schema and instead of doing initial load I will copy the data as we did on the source as:
SQL> show user
USER is "CONSCIOUSNESS"
SQL> create table results (
deed_id number,
deed varchar2(60),
deed_result varchar2(100)
);
2 3 4 5
Table created.
SQL>
SQL> CREATE UNIQUE INDEX results_ix ON results(deed_id);
Index created.
SQL> 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');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from results;
COUNT(*)
----------
4
SQL> set line 300
SQL> select * from results;
DEED_ID DEED DEED_RESULT
---------- ------------------------------------------------------------ ----------------------------------------------------------------------------------------------------
1 Before Death was thinking of Money Becomes snake in next incarnations
2 Before Death was thinking on Property Becomes goblin in next incarnations
3 Before death was thinking of kids Becomes pig in next incarnations
4 Before death was thinking of women Becomes prostitute in next incarnations
SQL>
Create the Replicat group
The EXTTRAIL clause indicates the location of the remote trail and should be the same as the RMTTRAIL value that
was used when creating the Data Pump process on the source system.
ADD REPLICAT rtruth, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
Create the parameter file for the Replicat group
EDIT PARAMS rtruth
REPLICAT rtruth
ASSUMETARGETDEFS
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
MAP meditate.results, TARGET CONSCIOUSNESS.results;
Output is as:
GGSCI (goldengate) 26> ADD REPLICAT rtruth, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
REPLICAT added.
GGSCI (goldengate) 27> EDIT PARAMS rtruth
REPLICAT rtruth
ASSUMETARGETDEFS
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
MAP meditate.results, TARGET CONSCIOUSNESS.results;
In logs you will see:
2010-11-09 22:14:39 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD REPLICAT rtruth EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr.
2010-11-09 22:15:03 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS rtruth.
---------------------------------------------------------
Here are the details:
GGSCI (rac1.rac.meditate.com) 36> info extract ETRUTH
EXTRACT ETRUTH Last Started 2010-11-09 22:33 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-09 22:48:19 Thread 1, Seqno 17, RBA 37490688
Log Read Checkpoint Oracle Redo Logs
2010-11-09 22:48:20 Thread 2, Seqno 18, RBA 1619456
Log Read Checkpoint Oracle Redo Logs
2010-11-09 22:48:21 Thread 3, Seqno 14, RBA 35416576
GGSCI (rac1.rac.meditate.com) 37> info extract PTRUTH
EXTRACT PTRUTH Last Started 2010-11-09 22:36 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File /u01/app/oracle/product/goldengate/dirdat/tr000000
First Record RBA 1010
GGSCI (rac1.rac.meditate.com) 38>
Now lets start the replicat on the target as:
GGSCI (goldengate) 28> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RLORD1 00:00:00 00:00:09
REPLICAT STOPPED RTRUTH 00:00:00 00:04:04
GGSCI (goldengate) 29> start replicat RTRUTH
Sending START request to MANAGER ...
REPLICAT RTRUTH starting
GGSCI (goldengate) 31> status replicat RTRUTH
REPLICAT RTRUTH: RUNNING
GGSCI (goldengate) 32>
In logs you will see:
2010-11-09 22:19:58 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start replicat RTRUTH.
2010-11-09 22:19:58 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.169 (START REPLICAT RTRUTH ).
2010-11-09 22:19:58 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RTRUTH starting.
2010-11-09 22:19:58 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rtruth.prm: REPLICAT RTRUTH starting.
2010-11-09 22:19:59 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rtruth.prm: REPLICAT RTRUTH started.
-------------------------------------------
Now lets test the replication:
insert into results values (5,'Before death was thinking of LORD','Liberated');
Ok, on source we have:
SQL> select count(*) from results;
COUNT(*)
----------
4
SQL> select * from results;
DEED_ID DEED DEED_RESULT
---------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------
1 Before Death was thinking of Money Becomes snake in next incarnations
2 Before Death was thinking on Property Becomes goblin in next incarnations
3 Before death was thinking of kids Becomes pig in next incarnations
4 Before death was thinking of women Becomes prostitute in next incarnations
SQL>
Lets do the insert:
SQL> insert into results values (5,'Before death was thinking of LORD','Liberated');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from results;
DEED_ID DEED DEED_RESULT
---------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------
1 Before Death was thinking of Money Becomes snake in next incarnations
2 Before Death was thinking on Property Becomes goblin in next incarnations
3 Before death was thinking of kids Becomes pig in next incarnations
4 Before death was thinking of women Becomes prostitute in next incarnations
5 Before death was thinking of LORD Liberated
SQL> select count(*) from results;
COUNT(*)
----------
5
SQL> !date
Tue Nov 9 23:16:02 PST 2010
SQL>
On target we have:
SQL> select count(*) from results;
COUNT(*)
----------
5
SQL> select * from results;
DEED_ID DEED DEED_RESULT
---------- ------------------------------------------------------------ ----------------------------------------------------------------------------------------------------
1 Before Death was thinking of Money Becomes snake in next incarnations
2 Before Death was thinking on Property Becomes goblin in next incarnations
3 Before death was thinking of kids Becomes pig in next incarnations
4 Before death was thinking of women Becomes prostitute in next incarnations
5 Before death was thinking of LORD Liberated
SQL>
True is the Creator, True is the Doer. True is our Lord and Master, and True is His Support. So speak the Truest of the True. Through the True One, an intuitive and discerning mind is obtained.
No comments:
Post a Comment