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.
Friday, November 12, 2010
Configure the online change synchronization of Goldengate (Part 19 of series, Configure the online change synchronization of Goldengate on Linux)
Like an overnight guest, you shall arise and depart in the morning. Why are you so attached to your household? It is all like flowers in the garden.
Index of all the posts of Gurpartap Singh's Blog
Now we will configure the online change synchronization :
I am just setting up the commection and testing it to see if I can connect to ASM with TNS as we need this to read from redologs/archivelogs
from this location later.
Here is the tnsping, i.e. my tns entry and also you can see the connection is working.
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ tnsping RAC1ASM
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2010 15:58:22
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u02/app/11.2.0.1/grid/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = +ASM)))
OK (0 msec)
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ sqlplus sys/gurpartap@RAC1ASM as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 15:58:38 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 Real Application Clusters and Automatic Storage Management options
SQL>
Our source is: (Server : rac1, Database: simar, Instance: simar1, Schema: Meditate, Tablename: Employee)
Our target is: (Server : goldengate, Database: emrep, Instance: emrep, Schema: CONSCIOUSNESS, Tablename: Employee)
These are the steps that we will do:
a. Create a GoldenGate Checkpoint table
b. Create an Extract group
c. Create a parameter file for the online Extract group
d. Create a Trail
e. Create a Replicat group
f. Create a parameter file for the online Replicat group
a. Create a GoldenGate Checkpoint table
GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat
process will start processing after any kind of error or shutdown. This ensures data integrity and a record
of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.
Source:
------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 12> EDIT PARAMS ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.check_pt_tab
-------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 13> DBLOGIN USERID goldengate,password goldengate
Successfully logged into database.
GGSCI (rac1.rac.meditate.com) 14> ADD CHECKPOINTTABLE goldengate.check_pt_tab
Successfully created checkpoint table GOLDENGATE.CHECK_PT_TAB.
GGSCI (rac1.rac.meditate.com) 15>
SQL> conn goldengate/goldengate
Connected.
SQL> desc CHECK_PT_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
SQL>
----------------------------------------------------------------------------
Target:
GGSCI (goldengate) 1> EDIT PARAMS ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.check_pt_tab
GGSCI (goldengate) 2> DBLOGIN USERID goldengate,password goldengate
Successfully logged into database.
GGSCI (goldengate) 3> ADD CHECKPOINTTABLE goldengate.check_pt_tab
Successfully created checkpoint table GOLDENGATE.CHECK_PT_TAB.
GGSCI (goldengate) 4>
SQL> conn goldengate/goldengate
Connected.
SQL> desc check_pt_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
SQL>
b. Now lets create the online extract group as:
ADD EXTRACT elord1, TRANLOG, threads 3, BEGIN NOW
Then create a trial where this extract will write. It would be in the Goldengate software location on the target system
and here the trail files will be created. I am using the prefix rm. I am using thread 3 as our source database is a 3 node RAC database.
If you don`t use the thread parameter you will see the following error.
2010-11-08 22:40:05 GGS ERROR 500 Oracle GoldenGate Capture for Oracle, elord1.prm: The number of Oracle redo threads (3) is not the same as the number of checkpoint threads (1). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT, TRANLOG, THREADS 3, BEGIN...).
Output of above command is:
GGSCI (rac1.rac.meditate.com) 21> ADD EXTRACT elord1, TRANLOG, threads 3, BEGIN NOW
EXTRACT added.
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm, EXTRACT elord1
Output is as:
GGSCI (rac1.rac.meditate.com) 17> ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm, EXTRACT elord1
RMTTRAIL added.
c. Now we will create hte parameter file for the online extract group as:
Please note I am using parameter TRANLOGOPTIONS and connecting to ASM as our redologs are in ASM and if you forget this option
you will see the following error:
2010-11-08 22:46:18 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract elord1.
2010-11-08 22:46:18 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ELORD1 ).
2010-11-08 22:46:18 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ELORD1 starting.
2010-11-08 22:46:19 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 starting.
2010-11-08 22:46:26 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, elord1.prm: Default thread stack size: 33554432.
2010-11-08 22:46:48 GGS ERROR 500 Oracle GoldenGate Capture for Oracle, elord1.prm: No valid log files for current redo sequence 15, thread 1, error retrieving redo file name for sequence 15, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2010-11-08 22:42:03.
2010-11-08 22:46:48 GGS ERROR 190 Oracle GoldenGate Capture for Oracle, elord1.prm: PROCESS ABENDING.
--------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 3> encrypt password gurpartap
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI
GGSCI (rac1.rac.meditate.com) 4>
------------------------
EDIT PARAMS elord1
EXTRACT elord1
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
TABLE meditate.employee;
Here is the output of logs as:
2010-11-08 22:33:45 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT elord1 TRANLOG, threads 3, BEGIN NOW.
2010-11-08 22:34:00 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm EXTRACT elord1.
2010-11-08 22:34:09 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS elord1.
d. Now on target system we will create the replicat group as follows, just need to note that the RMTTRAIL on
source is same as EXTTRAIL on the target:
ADD REPLICAT rlord1, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
Output is as:
GGSCI (goldengate) 8> ADD REPLICAT rlord1, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
REPLICAT added.
e. Now we will create the parameter file of the replicat group as:
EDIT PARAMS rlord1
REPLICAT rlord1
ASSUMETARGETDEFS
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
MAP meditate.employee, TARGET CONSCIOUSNESS.employee;
Now is the time to start the extract and replicat and then test.
Lets start the extract as:
On Source execute the following on GGSCI prompt:
start extract elord1
info all
STATUS EXTRACT elord1
info extract elord1
info extract elord1, detail
Output is as:
GGSCI (rac1.rac.meditate.com) 5> start extract elord1
Sending START request to MANAGER ...
EXTRACT ELORD1 starting
GGSCI (rac1.rac.meditate.com) 7> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:24:31
GGSCI (rac1.rac.meditate.com) 8> STATUS EXTRACT elord1
EXTRACT ELORD1: RUNNING
GGSCI (rac1.rac.meditate.com) 9> info extract elord1
EXTRACT ELORD1 Last Started 2010-11-08 23:06 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:08 Thread 1, Seqno 15, RBA 26684416
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:09 Thread 2, Seqno 15, RBA 36956672
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:09 Thread 3, Seqno 11, RBA 29586448
GGSCI (rac1.rac.meditate.com) 10> info extract elord1, detail
EXTRACT ELORD1 Last Started 2010-11-08 23:06 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 1, Seqno 15, RBA 26690048
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 2, Seqno 15, RBA 36961792
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 3, Seqno 11, RBA 29592064
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/app/oracle/product/goldengate/dirdat/rm 0 1010 10
Extract Source Begin End
+RECV1/simar/onlinelog/group_5.261.734045029 2010-11-08 22:42 2010-11-08 23:08
Not Available * Initialized * 2010-11-08 22:42
Current directory /u01/app/oracle/product/goldengate
Report file /u01/app/oracle/product/goldengate/dirrpt/ELORD1.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/elord1.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/ELORD1.cpe
Process file /u01/app/oracle/product/goldengate/dirpcs/ELORD1.pce
Stdout file /u01/app/oracle/product/goldengate/dirout/ELORD1.out
Error log /u01/app/oracle/product/goldengate/ggserr.log
GGSCI (rac1.rac.meditate.com) 11>
In logs on source you will see:
2010-11-08 23:04:15 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ELORD1 ).
2010-11-08 23:04:15 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ELORD1 starting.
2010-11-08 23:04:15 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 starting.
2010-11-08 23:06:23 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, elord1.prm: Default thread stack size: 33554432.
2010-11-08 23:06:38 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 started.
2010-11-08 23:06:43 GGS INFO 406 Oracle GoldenGate Capture for Oracle, elord1.prm: Socket buffer size set to 27985 (flush size 27985).
In logs on target you will see:
2010-11-08 22:35:53 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-08 22:35:53 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started collector process (Port 7840).
2010-11-08 22:35:53 GGS INFO 373 Oracle GoldenGate Collector, port 7840: Waiting for connection (started dynamically).
2010-11-08 22:36:10 GGS INFO 373 Oracle GoldenGate Collector: Connected to 192.168.1.176:37003.
2010-11-08 22:36:10 GGS INFO 370 Oracle GoldenGate Collector, port 7840: Opening /u01/app/oracle/product/goldengate/dirdat/rm000000 (byte -1, current EOF 0).
---------------------------------------------------
Now on Target execute the following as:
START REPLICAT rlord1
status REPLICAT rlord1
INFO REPLICAT rlord1
INFO REPLICAT rlord1, detail
Output is as:
GGSCI (goldengate) 10> START REPLICAT rlord1
Sending START request to MANAGER ...
REPLICAT RLORD1 starting
GGSCI (goldengate) 11> status REPLICAT rlord1
REPLICAT RLORD1: RUNNING
GGSCI (goldengate) 12> INFO REPLICAT rlord1
REPLICAT RLORD1 Last Started 2010-11-08 22:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File /u01/app/oracle/product/goldengate/dirdat/rm000000
First Record RBA 1010
GGSCI (goldengate) 13> INFO REPLICAT rlord1, detail
REPLICAT RLORD1 Last Started 2010-11-08 22:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File /u01/app/oracle/product/goldengate/dirdat/rm000000
First Record RBA 1010
Extract Source Begin End
/u01/app/oracle/product/goldengate/dirdat/rm000000 * Initialized * First Record
/u01/app/oracle/product/goldengate/dirdat/rm000000 * Initialized * First Record
Current directory /u01/app/oracle/product/goldengate
Report file /u01/app/oracle/product/goldengate/dirrpt/RLORD1.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/rlord1.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/RLORD1.cpr
Checkpoint table GGS_OWNER.GGSCHKPT
Process file /u01/app/oracle/product/goldengate/dirpcs/RLORD1.pcr
Stdout file /u01/app/oracle/product/goldengate/dirout/RLORD1.out
Error log /u01/app/oracle/product/goldengate/ggserr.log
GGSCI (goldengate) 14>
In logs you will see the following:
2010-11-08 22:44:21 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): START REPLICAT rlord1.
2010-11-08 22:44:21 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.169 (START REPLICAT RLORD1 ).
2010-11-08 22:44:21 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RLORD1 starting.
2010-11-08 22:44:22 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rlord1.prm: REPLICAT RLORD1 starting.
2010-11-08 22:44:23 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rlord1.prm: REPLICAT RLORD1 started.
--------------------------------
Now testing if our configuration is replicating or not:
On source I did:
SQL> update EMPLOYEE set COMM=100 where empno=7844;
1 row updated.
SQL> commit;
Commit complete.
SQL>
You will see the following in logs of Source as:
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery initialization completed for target file /u01/app/oracle/product/goldengate/dirdat/rm000000, at RBA 1500.
2010-11-08 23:34:06 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, elord1.prm: Output file /u01/app/oracle/product/goldengate/dirdat/rm is using format RELEASE 10.4.
2010-11-08 23:34:06 GGS INFO 224 Oracle GoldenGate Capture for Oracle, elord1.prm: Rolling over remote file /u01/app/oracle/product/goldengate/dirdat/rm000001.
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery completed for target file /u01/app/oracle/product/goldengate/dirdat/rm000001, at RBA 1010.
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery completed for all targets.
and then on target it was:
SQL> show user
USER is "CONSCIOUSNESS"
SQL> select * from employee;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 100 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Why do you say, "Mine, mine"? Look to God, who has given it to you. It is certain that you must arise and depart, and leave behind your hundreds of thousands and millions.
Index of all the posts of Gurpartap Singh's Blog
Now we will configure the online change synchronization :
I am just setting up the commection and testing it to see if I can connect to ASM with TNS as we need this to read from redologs/archivelogs
from this location later.
Here is the tnsping, i.e. my tns entry and also you can see the connection is working.
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ tnsping RAC1ASM
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2010 15:58:22
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u02/app/11.2.0.1/grid/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = +ASM)))
OK (0 msec)
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ sqlplus sys/gurpartap@RAC1ASM as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 15:58:38 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 Real Application Clusters and Automatic Storage Management options
SQL>
Our source is: (Server : rac1, Database: simar, Instance: simar1, Schema: Meditate, Tablename: Employee)
Our target is: (Server : goldengate, Database: emrep, Instance: emrep, Schema: CONSCIOUSNESS, Tablename: Employee)
These are the steps that we will do:
a. Create a GoldenGate Checkpoint table
b. Create an Extract group
c. Create a parameter file for the online Extract group
d. Create a Trail
e. Create a Replicat group
f. Create a parameter file for the online Replicat group
a. Create a GoldenGate Checkpoint table
GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat
process will start processing after any kind of error or shutdown. This ensures data integrity and a record
of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.
Source:
------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 12> EDIT PARAMS ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.check_pt_tab
-------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 13> DBLOGIN USERID goldengate,password goldengate
Successfully logged into database.
GGSCI (rac1.rac.meditate.com) 14> ADD CHECKPOINTTABLE goldengate.check_pt_tab
Successfully created checkpoint table GOLDENGATE.CHECK_PT_TAB.
GGSCI (rac1.rac.meditate.com) 15>
SQL> conn goldengate/goldengate
Connected.
SQL> desc CHECK_PT_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
SQL>
----------------------------------------------------------------------------
Target:
GGSCI (goldengate) 1> EDIT PARAMS ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.check_pt_tab
GGSCI (goldengate) 2> DBLOGIN USERID goldengate,password goldengate
Successfully logged into database.
GGSCI (goldengate) 3> ADD CHECKPOINTTABLE goldengate.check_pt_tab
Successfully created checkpoint table GOLDENGATE.CHECK_PT_TAB.
GGSCI (goldengate) 4>
SQL> conn goldengate/goldengate
Connected.
SQL> desc check_pt_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
SQL>
b. Now lets create the online extract group as:
ADD EXTRACT elord1, TRANLOG, threads 3, BEGIN NOW
Then create a trial where this extract will write. It would be in the Goldengate software location on the target system
and here the trail files will be created. I am using the prefix rm. I am using thread 3 as our source database is a 3 node RAC database.
If you don`t use the thread parameter you will see the following error.
2010-11-08 22:40:05 GGS ERROR 500 Oracle GoldenGate Capture for Oracle, elord1.prm: The number of Oracle redo threads (3) is not the same as the number of checkpoint threads (1). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT
Output of above command is:
GGSCI (rac1.rac.meditate.com) 21> ADD EXTRACT elord1, TRANLOG, threads 3, BEGIN NOW
EXTRACT added.
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm, EXTRACT elord1
Output is as:
GGSCI (rac1.rac.meditate.com) 17> ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm, EXTRACT elord1
RMTTRAIL added.
c. Now we will create hte parameter file for the online extract group as:
Please note I am using parameter TRANLOGOPTIONS and connecting to ASM as our redologs are in ASM and if you forget this option
you will see the following error:
2010-11-08 22:46:18 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract elord1.
2010-11-08 22:46:18 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ELORD1 ).
2010-11-08 22:46:18 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ELORD1 starting.
2010-11-08 22:46:19 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 starting.
2010-11-08 22:46:26 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, elord1.prm: Default thread stack size: 33554432.
2010-11-08 22:46:48 GGS ERROR 500 Oracle GoldenGate Capture for Oracle, elord1.prm: No valid log files for current redo sequence 15, thread 1, error retrieving redo file name for sequence 15, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2010-11-08 22:42:03.
2010-11-08 22:46:48 GGS ERROR 190 Oracle GoldenGate Capture for Oracle, elord1.prm: PROCESS ABENDING.
--------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 3> encrypt password gurpartap
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI
GGSCI (rac1.rac.meditate.com) 4>
------------------------
EDIT PARAMS elord1
EXTRACT elord1
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
TABLE meditate.employee;
Here is the output of logs as:
2010-11-08 22:33:45 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT elord1 TRANLOG, threads 3, BEGIN NOW.
2010-11-08 22:34:00 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm EXTRACT elord1.
2010-11-08 22:34:09 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS elord1.
d. Now on target system we will create the replicat group as follows, just need to note that the RMTTRAIL on
source is same as EXTTRAIL on the target:
ADD REPLICAT rlord1, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
Output is as:
GGSCI (goldengate) 8> ADD REPLICAT rlord1, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
REPLICAT added.
e. Now we will create the parameter file of the replicat group as:
EDIT PARAMS rlord1
REPLICAT rlord1
ASSUMETARGETDEFS
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
MAP meditate.employee, TARGET CONSCIOUSNESS.employee;
Now is the time to start the extract and replicat and then test.
Lets start the extract as:
On Source execute the following on GGSCI prompt:
start extract elord1
info all
STATUS EXTRACT elord1
info extract elord1
info extract elord1, detail
Output is as:
GGSCI (rac1.rac.meditate.com) 5> start extract elord1
Sending START request to MANAGER ...
EXTRACT ELORD1 starting
GGSCI (rac1.rac.meditate.com) 7> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:24:31
GGSCI (rac1.rac.meditate.com) 8> STATUS EXTRACT elord1
EXTRACT ELORD1: RUNNING
GGSCI (rac1.rac.meditate.com) 9> info extract elord1
EXTRACT ELORD1 Last Started 2010-11-08 23:06 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:08 Thread 1, Seqno 15, RBA 26684416
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:09 Thread 2, Seqno 15, RBA 36956672
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:09 Thread 3, Seqno 11, RBA 29586448
GGSCI (rac1.rac.meditate.com) 10> info extract elord1, detail
EXTRACT ELORD1 Last Started 2010-11-08 23:06 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 1, Seqno 15, RBA 26690048
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 2, Seqno 15, RBA 36961792
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 3, Seqno 11, RBA 29592064
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/app/oracle/product/goldengate/dirdat/rm 0 1010 10
Extract Source Begin End
+RECV1/simar/onlinelog/group_5.261.734045029 2010-11-08 22:42 2010-11-08 23:08
Not Available * Initialized * 2010-11-08 22:42
Current directory /u01/app/oracle/product/goldengate
Report file /u01/app/oracle/product/goldengate/dirrpt/ELORD1.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/elord1.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/ELORD1.cpe
Process file /u01/app/oracle/product/goldengate/dirpcs/ELORD1.pce
Stdout file /u01/app/oracle/product/goldengate/dirout/ELORD1.out
Error log /u01/app/oracle/product/goldengate/ggserr.log
GGSCI (rac1.rac.meditate.com) 11>
In logs on source you will see:
2010-11-08 23:04:15 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ELORD1 ).
2010-11-08 23:04:15 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ELORD1 starting.
2010-11-08 23:04:15 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 starting.
2010-11-08 23:06:23 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, elord1.prm: Default thread stack size: 33554432.
2010-11-08 23:06:38 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 started.
2010-11-08 23:06:43 GGS INFO 406 Oracle GoldenGate Capture for Oracle, elord1.prm: Socket buffer size set to 27985 (flush size 27985).
In logs on target you will see:
2010-11-08 22:35:53 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-08 22:35:53 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started collector process (Port 7840).
2010-11-08 22:35:53 GGS INFO 373 Oracle GoldenGate Collector, port 7840: Waiting for connection (started dynamically).
2010-11-08 22:36:10 GGS INFO 373 Oracle GoldenGate Collector: Connected to 192.168.1.176:37003.
2010-11-08 22:36:10 GGS INFO 370 Oracle GoldenGate Collector, port 7840: Opening /u01/app/oracle/product/goldengate/dirdat/rm000000 (byte -1, current EOF 0).
---------------------------------------------------
Now on Target execute the following as:
START REPLICAT rlord1
status REPLICAT rlord1
INFO REPLICAT rlord1
INFO REPLICAT rlord1, detail
Output is as:
GGSCI (goldengate) 10> START REPLICAT rlord1
Sending START request to MANAGER ...
REPLICAT RLORD1 starting
GGSCI (goldengate) 11> status REPLICAT rlord1
REPLICAT RLORD1: RUNNING
GGSCI (goldengate) 12> INFO REPLICAT rlord1
REPLICAT RLORD1 Last Started 2010-11-08 22:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File /u01/app/oracle/product/goldengate/dirdat/rm000000
First Record RBA 1010
GGSCI (goldengate) 13> INFO REPLICAT rlord1, detail
REPLICAT RLORD1 Last Started 2010-11-08 22:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File /u01/app/oracle/product/goldengate/dirdat/rm000000
First Record RBA 1010
Extract Source Begin End
/u01/app/oracle/product/goldengate/dirdat/rm000000 * Initialized * First Record
/u01/app/oracle/product/goldengate/dirdat/rm000000 * Initialized * First Record
Current directory /u01/app/oracle/product/goldengate
Report file /u01/app/oracle/product/goldengate/dirrpt/RLORD1.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/rlord1.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/RLORD1.cpr
Checkpoint table GGS_OWNER.GGSCHKPT
Process file /u01/app/oracle/product/goldengate/dirpcs/RLORD1.pcr
Stdout file /u01/app/oracle/product/goldengate/dirout/RLORD1.out
Error log /u01/app/oracle/product/goldengate/ggserr.log
GGSCI (goldengate) 14>
In logs you will see the following:
2010-11-08 22:44:21 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): START REPLICAT rlord1.
2010-11-08 22:44:21 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.169 (START REPLICAT RLORD1 ).
2010-11-08 22:44:21 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RLORD1 starting.
2010-11-08 22:44:22 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rlord1.prm: REPLICAT RLORD1 starting.
2010-11-08 22:44:23 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rlord1.prm: REPLICAT RLORD1 started.
--------------------------------
Now testing if our configuration is replicating or not:
On source I did:
SQL> update EMPLOYEE set COMM=100 where empno=7844;
1 row updated.
SQL> commit;
Commit complete.
SQL>
You will see the following in logs of Source as:
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery initialization completed for target file /u01/app/oracle/product/goldengate/dirdat/rm000000, at RBA 1500.
2010-11-08 23:34:06 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, elord1.prm: Output file /u01/app/oracle/product/goldengate/dirdat/rm is using format RELEASE 10.4.
2010-11-08 23:34:06 GGS INFO 224 Oracle GoldenGate Capture for Oracle, elord1.prm: Rolling over remote file /u01/app/oracle/product/goldengate/dirdat/rm000001.
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery completed for target file /u01/app/oracle/product/goldengate/dirdat/rm000001, at RBA 1010.
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery completed for all targets.
and then on target it was:
SQL> show user
USER is "CONSCIOUSNESS"
SQL> select * from employee;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 100 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Why do you say, "Mine, mine"? Look to God, who has given it to you. It is certain that you must arise and depart, and leave behind your hundreds of thousands and millions.
Use Goldengate to do Initial Load (Part 18 of series, Using Oracle Goldengate to do Initial Load of Oracle Goldengate on Linux)
For His sake, you assumed this body; see God always with you. God is pervading the water, the land and the sky; He sees all with His Glance of Grace.
Index of all the posts of Gurpartap Singh's Blog
Lets do the initial load, though you can can use many methods like export/import and transportable tablespaces but here I will use Goldengate to do the initial load.
For transportable tablespaces use Note : 371156.1
It works pretty good with ASM to ASM too.
For cross platform transporable tablespaces asm to asm see post 24.
Post 24 of series, Goldengate initial load using Cross platform transportable tablespaces Solaris to Linux
Lets encrypt the password first using the following statement on goldengate as:
encrypt password gurpartap
Output as:
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ 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> encrypt password gurpartap
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI
GGSCI (rac1.rac.meditate.com) 2> exit
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$
------------------------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 1> encrypt password goldengate
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC
GGSCI (rac1.rac.meditate.com) 2>
------------------------------------------------------------------------------------------------------
Now lets create the parameter file for extracting initial load.
On Source
1) Create the Initial data extract process ‘initial'
ADD EXTRACT initial, SOURCEISTABLE
SOURCEISTABLE : Designates Extract as an initial load process extracting records directly from the source tables.
So, it doesnot read the transaction log files of the database
2) Create the parameter file for the extract group load1
We need to define the following parameters in the parameter file:
EXTRACT : Name of the extract group
USERID/PASSWORD : The database user in the source database which has been configured for Extract i.e "goldengate"
RMTHOST : This will be the hostname/ip_address of the target system
MGRPORT : The port where the Manager process is running
TABLE : This is a very flexible parameter where we can define wildcards to exclude and include tables and columns for replication or
extraction. We will use this in complex replication later.
EDIT PARAMS initial
EXTRACT initial
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7809
RMTTASK replicat, GROUP onetime
TABLE meditate.EMPLOYEE;
----------------------------------------------------------------------------------------------------
Output is as:
$ 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
GGSCI (rac1.rac.meditate.com) 2> ADD EXTRACT initial, SOURCEISTABLE
EXTRACT added.
GGSCI (rac1.rac.meditate.com) 3> EDIT PARAMS initial
EXTRACT initial
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
RMTTASK replicat, GROUP onetime
TABLE meditate.EMPLOYEE;
-----------------------------------------------------------------------------------------------------
On Target:
Please note I have done all the steps of installation and created the user goldengate as:
create tablespace goldengate
datafile '/u01/app/oracle/datafiles/emrep/goldengate.dbf' size 300m;
create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp;
and then grant the following permisssions to the goldengate user as:
grant connect,resource to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
Output is as:
SQL> create tablespace goldengate
datafile '/u01/app/oracle/datafiles/emrep/goldengate.dbf' size 300m;
2
Tablespace created.
SQL> SQL> create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp; 2 3
User created.
SQL> grant connect,resource,dba to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> grant dba to goldengate;
Grant succeeded.
---------------------------------------------------------------------------------
Now we wil create the user where we will replicate the changes as:
create user consciousness identified by consciousness
default tablespace users
temporary tablespace temp;
grant connect,resource,dba to consciousness;
and then create the table as source, under this schema with extracted DDL from source we did earlier as:
CREATE TABLE "CONSCIOUSNESS"."EMPLOYEE"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
);
---------------------------------------------------------------------------------------------------
Output is as:
oracle : goldengate : @emrep : /u01/app/oracle/flash_area/EMREP/archivelog
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 20:07:55 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> altre system switch logfile;
SQL> alter system switch logfile;
System altered.
SQL> conn / as sysdba
Connected.
SQL> create user consciousness identified by consciousness
default tablespace users
temporary tablespace temp; 2 3
User created.
SQL> grant connect,resource,dba to consciousness;
Grant succeeded.
SQL>
Now create the table under this schema as:
oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate
$ sqlplus consciousness/consciousness
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 20:58:58 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE TABLE "CONSCIOUSNESS"."EMPLOYEE"
2 ( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
3 4 5 6 "HIREDATE" DATE,
"SAL" NUMBER(7,2),
7 8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0)
10 );
Table created.
SQL>
-------------------------------------------------------------------------------------
Password encryption:
oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate
$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 08:50:50
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (goldengate) 1> encrypt password goldengate
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC
GGSCI (goldengate) 2>
--------------------------------------------------------------------------------
Create the replicat as:
Create the initial data load task 'onetime'
SPECIALRUN identifies the initial-load Replicat as a one-time run, not a continuous
process.
GGSCI (devu007) 1> ADD REPLICAT onetime, SPECIALRUN
REPLICAT added.
4) Create the parameter file for the Replicat group, onetime
REPLICAT : name of the Replicat group that we just created for the initial data load
USERID/PASSWORD : database credentials for the Replicat user i.e. "goldengate"
ASSUMETARGETDEFS : this means that the source table structure exactly matches the target database table structure. Just the simple replication.
we will do the complex replication later.
MAP : with GoldenGate we can have the target database structure entirely different from that of the source in terms of tablenames
as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which
is same in this case. But this parameter can do amazing complex mappings for replications.
GGSCI (devu007) 2> EDIT PARAMS onetime
"/u01/oracle/software/goldengate/dirprm/rep4.prm" [New file]
REPLICAT onetime
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ASSUMETARGETDEFS
MAP meditate.employees, TARGET consciousness.customers;
--------------------------------------------------------------------------------------------------------------------------
Now lets start the extract on source and before that make sure that the manager process is already running
on both source and target:
GGSCI (rac1.rac.meditate.com) 1> start extract initial
Sending START request to MANAGER ...
EXTRACT INITIAL starting
GGSCI (rac1.rac.meditate.com) 2>
You can see the info of this extract by using command "info extract initial"
To see the logs of this commad look at the logfile at:
/u01/app/oracle/product/goldengate/ggserr.log
where /u01/app/oracle/product/goldengate is the location of the goldengate binaries.
and you will see the following:
2010-11-07 21:51:48 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract initial.
2010-11-07 21:51:48 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT INITIAL ).
2010-11-07 21:51:48 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT INITIAL starting.
2010-11-07 21:51:48 GGS INFO 414 Oracle GoldenGate Capture for Oracle, initial.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
2010-11-07 21:51:48 GGS INFO 310 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL starting.
2010-11-07 21:51:48 GGS WARNING 109 Oracle GoldenGate Capture for Oracle, initial.prm: No unique key is defined for table EMPLOYEE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2010-11-07 21:51:48 GGS INFO 310 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL started.
2010-11-07 21:52:05 GGS INFO 312 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL stopped normally.
---------------------------------------------------------------------------------
On target side you will see the following:
2010-11-07 21:21:05 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host 192.168.1.176 (START REPLICAT ONETIME CPU -1 PRI -1 PARAMS ).
2010-11-07 21:21:05 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started replicat task process (Port 7841).
2010-11-07 21:21:05 GGS INFO 310 Oracle GoldenGate Delivery for Oracle: REPLICAT task started by manager (port 7841).
2010-11-07 21:21:22 GGS INFO 373 Oracle GoldenGate Delivery for Oracle: Connected to 192.168.1.176:18802.
2010-11-07 21:21:22 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME starting.
2010-11-07 21:21:23 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME started.
2010-11-07 21:21:23 GGS WARNING 109 Oracle GoldenGate Delivery for Oracle, onetime.prm: No unique key is defined for table EMPLOYEE. All viable c olumns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2010-11-07 21:21:28 GGS INFO 322 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME stopped normally.
---------------------------------------------------------------------------------------------
And after this enquire on the target and you will the records have been replicated as:
oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate/dirprm
$ sqlplus consciousness/consciousness
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 21:21:47 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from employee;
COUNT(*)
----------
14
SQL>
----------------------------------------------------------------------------------------------------
Now I will create the index on both source and the target as:
Target:
SQL> show user
USER is "MEDITATE"
SQL> CREATE UNIQUE INDEX PK_employee ON employee("EMPNO");
Index created.
SQL>
and source:
SQL> show user
USER is "CONSCIOUSNESS"
SQL> CREATE UNIQUE INDEX PK_employee ON employee("EMPNO");
Index created.
SQL>
The mind and body become spotlessly pure, enshrining love for the True Lord. One who dwells upon the Feet of the Supreme Lord God has truly performed all meditations and austerities.
Index of all the posts of Gurpartap Singh's Blog
Lets do the initial load, though you can can use many methods like export/import and transportable tablespaces but here I will use Goldengate to do the initial load.
For transportable tablespaces use Note : 371156.1
It works pretty good with ASM to ASM too.
For cross platform transporable tablespaces asm to asm see post 24.
Post 24 of series, Goldengate initial load using Cross platform transportable tablespaces Solaris to Linux
Lets encrypt the password first using the following statement on goldengate as:
encrypt password gurpartap
Output as:
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ 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> encrypt password gurpartap
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI
GGSCI (rac1.rac.meditate.com) 2> exit
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$
------------------------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 1> encrypt password goldengate
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC
GGSCI (rac1.rac.meditate.com) 2>
------------------------------------------------------------------------------------------------------
Now lets create the parameter file for extracting initial load.
On Source
1) Create the Initial data extract process ‘initial'
ADD EXTRACT initial, SOURCEISTABLE
SOURCEISTABLE : Designates Extract as an initial load process extracting records directly from the source tables.
So, it doesnot read the transaction log files of the database
2) Create the parameter file for the extract group load1
We need to define the following parameters in the parameter file:
EXTRACT : Name of the extract group
USERID/PASSWORD : The database user in the source database which has been configured for Extract i.e "goldengate"
RMTHOST : This will be the hostname/ip_address of the target system
MGRPORT : The port where the Manager process is running
TABLE : This is a very flexible parameter where we can define wildcards to exclude and include tables and columns for replication or
extraction. We will use this in complex replication later.
EDIT PARAMS initial
EXTRACT initial
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7809
RMTTASK replicat, GROUP onetime
TABLE meditate.EMPLOYEE;
----------------------------------------------------------------------------------------------------
Output is as:
$ 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
GGSCI (rac1.rac.meditate.com) 2> ADD EXTRACT initial, SOURCEISTABLE
EXTRACT added.
GGSCI (rac1.rac.meditate.com) 3> EDIT PARAMS initial
EXTRACT initial
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
RMTTASK replicat, GROUP onetime
TABLE meditate.EMPLOYEE;
-----------------------------------------------------------------------------------------------------
On Target:
Please note I have done all the steps of installation and created the user goldengate as:
create tablespace goldengate
datafile '/u01/app/oracle/datafiles/emrep/goldengate.dbf' size 300m;
create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp;
and then grant the following permisssions to the goldengate user as:
grant connect,resource to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
Output is as:
SQL> create tablespace goldengate
datafile '/u01/app/oracle/datafiles/emrep/goldengate.dbf' size 300m;
2
Tablespace created.
SQL> SQL> create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp; 2 3
User created.
SQL> grant connect,resource,dba to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> grant dba to goldengate;
Grant succeeded.
---------------------------------------------------------------------------------
Now we wil create the user where we will replicate the changes as:
create user consciousness identified by consciousness
default tablespace users
temporary tablespace temp;
grant connect,resource,dba to consciousness;
and then create the table as source, under this schema with extracted DDL from source we did earlier as:
CREATE TABLE "CONSCIOUSNESS"."EMPLOYEE"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
);
---------------------------------------------------------------------------------------------------
Output is as:
oracle : goldengate : @emrep : /u01/app/oracle/flash_area/EMREP/archivelog
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 20:07:55 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> altre system switch logfile;
SQL> alter system switch logfile;
System altered.
SQL> conn / as sysdba
Connected.
SQL> create user consciousness identified by consciousness
default tablespace users
temporary tablespace temp; 2 3
User created.
SQL> grant connect,resource,dba to consciousness;
Grant succeeded.
SQL>
Now create the table under this schema as:
oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate
$ sqlplus consciousness/consciousness
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 20:58:58 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE TABLE "CONSCIOUSNESS"."EMPLOYEE"
2 ( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
3 4 5 6 "HIREDATE" DATE,
"SAL" NUMBER(7,2),
7 8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0)
10 );
Table created.
SQL>
-------------------------------------------------------------------------------------
Password encryption:
oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate
$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 08:50:50
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (goldengate) 1> encrypt password goldengate
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC
GGSCI (goldengate) 2>
--------------------------------------------------------------------------------
Create the replicat as:
Create the initial data load task 'onetime'
SPECIALRUN identifies the initial-load Replicat as a one-time run, not a continuous
process.
GGSCI (devu007) 1> ADD REPLICAT onetime, SPECIALRUN
REPLICAT added.
4) Create the parameter file for the Replicat group, onetime
REPLICAT : name of the Replicat group that we just created for the initial data load
USERID/PASSWORD : database credentials for the Replicat user i.e. "goldengate"
ASSUMETARGETDEFS : this means that the source table structure exactly matches the target database table structure. Just the simple replication.
we will do the complex replication later.
MAP : with GoldenGate we can have the target database structure entirely different from that of the source in terms of tablenames
as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which
is same in this case. But this parameter can do amazing complex mappings for replications.
GGSCI (devu007) 2> EDIT PARAMS onetime
"/u01/oracle/software/goldengate/dirprm/rep4.prm" [New file]
REPLICAT onetime
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ASSUMETARGETDEFS
MAP meditate.employees, TARGET consciousness.customers;
--------------------------------------------------------------------------------------------------------------------------
Now lets start the extract on source and before that make sure that the manager process is already running
on both source and target:
GGSCI (rac1.rac.meditate.com) 1> start extract initial
Sending START request to MANAGER ...
EXTRACT INITIAL starting
GGSCI (rac1.rac.meditate.com) 2>
You can see the info of this extract by using command "info extract initial"
To see the logs of this commad look at the logfile at:
/u01/app/oracle/product/goldengate/ggserr.log
where /u01/app/oracle/product/goldengate is the location of the goldengate binaries.
and you will see the following:
2010-11-07 21:51:48 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract initial.
2010-11-07 21:51:48 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT INITIAL ).
2010-11-07 21:51:48 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT INITIAL starting.
2010-11-07 21:51:48 GGS INFO 414 Oracle GoldenGate Capture for Oracle, initial.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
2010-11-07 21:51:48 GGS INFO 310 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL starting.
2010-11-07 21:51:48 GGS WARNING 109 Oracle GoldenGate Capture for Oracle, initial.prm: No unique key is defined for table EMPLOYEE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2010-11-07 21:51:48 GGS INFO 310 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL started.
2010-11-07 21:52:05 GGS INFO 312 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL stopped normally.
---------------------------------------------------------------------------------
On target side you will see the following:
2010-11-07 21:21:05 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host 192.168.1.176 (START REPLICAT ONETIME CPU -1 PRI -1 PARAMS ).
2010-11-07 21:21:05 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started replicat task process (Port 7841).
2010-11-07 21:21:05 GGS INFO 310 Oracle GoldenGate Delivery for Oracle: REPLICAT task started by manager (port 7841).
2010-11-07 21:21:22 GGS INFO 373 Oracle GoldenGate Delivery for Oracle: Connected to 192.168.1.176:18802.
2010-11-07 21:21:22 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME starting.
2010-11-07 21:21:23 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME started.
2010-11-07 21:21:23 GGS WARNING 109 Oracle GoldenGate Delivery for Oracle, onetime.prm: No unique key is defined for table EMPLOYEE. All viable c olumns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2010-11-07 21:21:28 GGS INFO 322 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME stopped normally.
---------------------------------------------------------------------------------------------
And after this enquire on the target and you will the records have been replicated as:
oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate/dirprm
$ sqlplus consciousness/consciousness
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 21:21:47 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from employee;
COUNT(*)
----------
14
SQL>
----------------------------------------------------------------------------------------------------
Now I will create the index on both source and the target as:
Target:
SQL> show user
USER is "MEDITATE"
SQL> CREATE UNIQUE INDEX PK_employee ON employee("EMPNO");
Index created.
SQL>
and source:
SQL> show user
USER is "CONSCIOUSNESS"
SQL> CREATE UNIQUE INDEX PK_employee ON employee("EMPNO");
Index created.
SQL>
The mind and body become spotlessly pure, enshrining love for the True Lord. One who dwells upon the Feet of the Supreme Lord God has truly performed all meditations and austerities.
Configure Manager Process of Goldengate (Part 17 of series, Configuring Manager Process of Oracle Goldengate on Linux)
Make the effort, and chant the Lord's Name. O very fortunate ones, earn this wealth. In the Society of the Saints, meditate in remembrance on the Lord, and wash off the filth of countless incarnations.
Index of all the posts of Gurpartap Singh's Blog
Configure Manager process:
Though the only parameter required is port but we will use PURGEOLDEXTRACTS and USECHECKPOINTS so that old extracts are pruged after they are applied as follows. Though you can use the AUTOSTART parameter to autostart the extracts and replicats in this and also MINKEEPDAYS but I am not using here for now. We will modify it at a later stage. First simple things and then complex things.
PORT 7909
USERID goldengate, PASSWORD goldengate
PURGEOLDEXTRACTS /u01/app/oracle/product/goldengate/dir/ext, USECHECKPOINTS
When PURGEOLDEXTRACT is used with USECHECKPOINTS clause, it will ensure that until all processes have fnished using the data
contained in the trail files, they will not be deleted.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
From documentation:
Use the PURGEOLDEXTRACTS parameter in a Manager parameter file to purge trail files when
GoldenGate has finished processing them. Without using PURGEOLDEXTRACTS, no purging is
performed, and trail files can consume significant disk space.
Please note:
When using this parameter, do not permit trail files to be deleted by any user or
program other than GoldenGate. It will cause PURGEOLDEXTRACTS to function
improperly.
and:
Autostart parameters
Use the AUTOSTART parameter to start Extract and Replicat processes when Manager starts.
This can be useful, for example, if you want GoldenGate activities to begin immediately
when you start the system, assuming Manager is part of the startup routine. You can use
multiple AUTOSTART statements in the same parameter file.
AUTOSTART {ER EXTRACT REPLICAT} {group name wildcard}
Use the AUTORESTART parameter to start Extract and Replicat processes again after
abnormal termination.
AUTORESTART {ER EXTRACT REPLICAT} {group name wildcard}
[, RETRIES]
[, WAITMINUTES]
[, RESETMINUTES]
e.g.
PORT 7909
USERID goldengate, PASSWORD goldengate PURGEOLDEXTRACTS /u01/app/orcle/product/goldengate/dir/ext, USECHECKPOINTS
autostart extract *truth
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Output as:
$ 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> EDIT PARAMS MGR
PORT 7909
USERID goldengate, PASSWORD goldengate
PURGEOLDEXTRACTS /u01/app/oracle/product/goldengate/dir/ext, USECHECKPOINTS
-----------------------------------------------------------------------------------
Now lets start the manager as:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ 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> start manager
Manager started.
GGSCI (rac1.rac.meditate.com) 2> info manager
Manager is running (IP port rac1.rac.meditate.com.7909).
GGSCI (rac1.rac.meditate.com) 3>
Manager can be stopped by the command, STOP MANAGER.
Some commads to debug incase you have problems:
VIEW REPORT MGR
OR
VIEW GGSEVT (looks in file /u01/app/oracle/product/goldengate/ggserr.log)
-----------------------------------------------------------------------------------------
Outputs of these look like:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ 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> VIEW REPORT MGR
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:48:39
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-11-07 15:03:28
***********************************************************************
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: 31826
Parameters...
PORT 7909
USERID goldengate, PASSWORD goldengate
PURGEOLDEXTRACTS /u01/app/oracle/product/goldengate/dir/ext, USECHECKPOINTS
***********************************************************************
** Run Time Messages **
***********************************************************************
2010-11-07 15:03:28 GGS INFO 330 Manager started (port 7909).
GGSCI (rac1.rac.meditate.com) 2>
--------------------------------------------------------------------------------
We can then confirm that the GoldenGate user we have just created is able to connect to the Oracle database
create user meditate identified by meditate
default tablespace users
temporary tablespace temp;
grant connect,resource,dba to meditate;
conn meditate/meditate@simarsv1
create table employee as select * from scott.emp;
----------------------------------------------------------------------------------
Output is as:
SQL> conn / as sysdba
Connected.
SQL> create user meditate identified by meditate
default tablespace users
temporary tablespace temp;
2 3
User created.
SQL> grant connect,resource,dba to meditate;
Grant succeeded.
SQL> conn meditate/meditate@simarsv1
Connected.
SQL> create table employee as select * from scott.emp;
Table created.
SQL> select count(*) from meditate.employee;
COUNT(*)
----------
14
SQL>
Extract the ddl from here and then create the table structure under target schema :
SQL> set heading off;
set echo off;
Set pages 999;
set long 90000;SQL> SQL> SQL>
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEE','MEDITATE') from dual;
CREATE TABLE "MEDITATE"."EMPLOYEE"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOC
OMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTE
NTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER
_POOL DEFAULT FLASH_CACHE DEFAULT CELL_F
LASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>
O my mind, chant and meditate on the Name of the Lord. Enjoy the fruits of your mind's desires; all suffering and sorrow shall depart.
Index of all the posts of Gurpartap Singh's Blog
Configure Manager process:
Though the only parameter required is port but we will use PURGEOLDEXTRACTS and USECHECKPOINTS so that old extracts are pruged after they are applied as follows. Though you can use the AUTOSTART parameter to autostart the extracts and replicats in this and also MINKEEPDAYS but I am not using here for now. We will modify it at a later stage. First simple things and then complex things.
PORT 7909
USERID goldengate, PASSWORD goldengate
PURGEOLDEXTRACTS /u01/app/oracle/product/goldengate/dir/ext, USECHECKPOINTS
When PURGEOLDEXTRACT is used with USECHECKPOINTS clause, it will ensure that until all processes have fnished using the data
contained in the trail files, they will not be deleted.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
From documentation:
Use the PURGEOLDEXTRACTS parameter in a Manager parameter file to purge trail files when
GoldenGate has finished processing them. Without using PURGEOLDEXTRACTS, no purging is
performed, and trail files can consume significant disk space.
Please note:
When using this parameter, do not permit trail files to be deleted by any user or
program other than GoldenGate. It will cause PURGEOLDEXTRACTS to function
improperly.
and:
Autostart parameters
Use the AUTOSTART parameter to start Extract and Replicat processes when Manager starts.
This can be useful, for example, if you want GoldenGate activities to begin immediately
when you start the system, assuming Manager is part of the startup routine. You can use
multiple AUTOSTART statements in the same parameter file.
AUTOSTART {ER EXTRACT REPLICAT} {group name wildcard}
Use the AUTORESTART parameter to start Extract and Replicat processes again after
abnormal termination.
AUTORESTART {ER EXTRACT REPLICAT} {group name wildcard}
[, RETRIES
[, WAITMINUTES
[, RESETMINUTES
e.g.
PORT 7909
USERID goldengate, PASSWORD goldengate PURGEOLDEXTRACTS /u01/app/orcle/product/goldengate/dir/ext, USECHECKPOINTS
autostart extract *truth
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Output as:
$ 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> EDIT PARAMS MGR
PORT 7909
USERID goldengate, PASSWORD goldengate
PURGEOLDEXTRACTS /u01/app/oracle/product/goldengate/dir/ext, USECHECKPOINTS
-----------------------------------------------------------------------------------
Now lets start the manager as:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ 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> start manager
Manager started.
GGSCI (rac1.rac.meditate.com) 2> info manager
Manager is running (IP port rac1.rac.meditate.com.7909).
GGSCI (rac1.rac.meditate.com) 3>
Manager can be stopped by the command, STOP MANAGER.
Some commads to debug incase you have problems:
VIEW REPORT MGR
OR
VIEW GGSEVT (looks in file /u01/app/oracle/product/goldengate/ggserr.log)
-----------------------------------------------------------------------------------------
Outputs of these look like:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ 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> VIEW REPORT MGR
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:48:39
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-11-07 15:03:28
***********************************************************************
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: 31826
Parameters...
PORT 7909
USERID goldengate, PASSWORD goldengate
PURGEOLDEXTRACTS /u01/app/oracle/product/goldengate/dir/ext, USECHECKPOINTS
***********************************************************************
** Run Time Messages **
***********************************************************************
2010-11-07 15:03:28 GGS INFO 330 Manager started (port 7909).
GGSCI (rac1.rac.meditate.com) 2>
--------------------------------------------------------------------------------
We can then confirm that the GoldenGate user we have just created is able to connect to the Oracle database
create user meditate identified by meditate
default tablespace users
temporary tablespace temp;
grant connect,resource,dba to meditate;
conn meditate/meditate@simarsv1
create table employee as select * from scott.emp;
----------------------------------------------------------------------------------
Output is as:
SQL> conn / as sysdba
Connected.
SQL> create user meditate identified by meditate
default tablespace users
temporary tablespace temp;
2 3
User created.
SQL> grant connect,resource,dba to meditate;
Grant succeeded.
SQL> conn meditate/meditate@simarsv1
Connected.
SQL> create table employee as select * from scott.emp;
Table created.
SQL> select count(*) from meditate.employee;
COUNT(*)
----------
14
SQL>
Extract the ddl from here and then create the table structure under target schema :
SQL> set heading off;
set echo off;
Set pages 999;
set long 90000;SQL> SQL> SQL>
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEE','MEDITATE') from dual;
CREATE TABLE "MEDITATE"."EMPLOYEE"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOC
OMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTE
NTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER
_POOL DEFAULT FLASH_CACHE DEFAULT CELL_F
LASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>
O my mind, chant and meditate on the Name of the Lord. Enjoy the fruits of your mind's desires; all suffering and sorrow shall depart.
Installing Oracle Goldengate (Part 16 of series, Installing Oracle Goldengate on Linux)
The day dawns, and then it ends,and the night passes away. Man's life is diminishing, but he does not understand. Each day, the mouse of death is gnawing away at the rope of life. Maya spreads out like sweet molasses; the self-willed manmukh is stuck like a fly, rotting away.
Index of all the posts of Gurpartap Singh's Blog
Oracle Goldegate is a nice tool to do replication. Though its expensive but is accurate and very flexible and with very low overhead on the database it replicates very fast. Once you know the architecture of oracle goldengate and the are familiar with the commands its a nice tool to use. Always keep documentation handy as this has so many parameters that you may not remember them all. Also, to do complex replication you should know your data.
Installing goldengate, if you are doing replication from one server to another then do teh installation on both target and the source server. I will use it on both the target and the source. You can use it to do replication between two databases on the same server also.
Here starts the install. I will install goldengate on linux. Both my source and target are linux servers.
unzip the file as you downloaded from Oracle Edelivery as:
unzip V18159-01.zip
and the untar it as:
tar -xvof ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar
I have seen the following errors on goldengate in one of the installs:
oracle : lab : @lab : /u01/app/oracle/product/goldengate
$ tar -xvof ggs_redhatAS50_x86_ora10g_32bit_v10.4.0.19_002.tar
mgr
tar: mgr: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ggsci
tar: ggsci: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ggMessage.dat
tar: ggMessage.dat: Cannot change ownership to uid 1901, gid 801: Operation not permitted
help.txt
tar: help.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
bcrypt.txt
tar: bcrypt.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libxml2.txt
tar: libxml2.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
zlib.txt
tar: zlib.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
freeBSD.txt
tar: freeBSD.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
notices.txt
tar: notices.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libicui18n.so.38
tar: libicui18n.so.38: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libicuuc.so.38
tar: libicuuc.so.38: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libicudata.so.38
tar: libicudata.so.38: Cannot change ownership to uid 1901, gid 801: Operation not permitted
cobgen
tar: cobgen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddlcob
tar: ddlcob: Cannot change ownership to uid 1901, gid 801: Operation not permitted
emsclnt
tar: emsclnt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
keygen
tar: keygen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
logdump
tar: logdump: Cannot change ownership to uid 1901, gid 801: Operation not permitted
reverse
tar: reverse: Cannot change ownership to uid 1901, gid 801: Operation not permitted
server
tar: server: Cannot change ownership to uid 1901, gid 801: Operation not permitted
convchk
tar: convchk: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddlgen
tar: ddlgen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
defgen
tar: defgen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
extract
tar: extract: Cannot change ownership to uid 1901, gid 801: Operation not permitted
replicat
tar: replicat: Cannot change ownership to uid 1901, gid 801: Operation not permitted
usrdecs.h
tar: usrdecs.h: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tcperrs
tar: tcperrs: Cannot change ownership to uid 1901, gid 801: Operation not permitted
bcpfmt.tpl
tar: bcpfmt.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
db2cntl.tpl
tar: db2cntl.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_access.tpl
tar: ddl_access.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_db2_os390.tpl
tar: ddl_db2_os390.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_db2.tpl
tar: ddl_db2.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_informix.tpl
tar: ddl_informix.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_mss.tpl
tar: ddl_mss.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_mysql.tpl
tar: ddl_mysql.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_nssql.tpl
tar: ddl_nssql.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_oracle.tpl
tar: ddl_oracle.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_sqlmx.tpl
tar: ddl_sqlmx.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_sybase.tpl
tar: ddl_sybase.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_tandem.tpl
tar: ddl_tandem.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
sqlldr.tpl
tar: sqlldr.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/readme.txt
tar: UserExitExamples/ExitDemo_lobs/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
tar: UserExitExamples/ExitDemo_lobs/exitdemo_lob.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
tar: UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/
tar: UserExitExamples/ExitDemo_lobs: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
tar: UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
tar: UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/readme.txt
tar: UserExitExamples/ExitDemo_pk_befores/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/
tar: UserExitExamples/ExitDemo_pk_befores: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/readme.txt
tar: UserExitExamples/ExitDemo/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/exitdemo.c
tar: UserExitExamples/ExitDemo/exitdemo.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/exitdemo.vcproj
tar: UserExitExamples/ExitDemo/exitdemo.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/
tar: UserExitExamples/ExitDemo: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
tar: UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/readme.txt
tar: UserExitExamples/ExitDemo_more_recs/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
tar: UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/
tar: UserExitExamples/ExitDemo_more_recs: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
tar: UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/readme.txt
tar: UserExitExamples/ExitDemo_passthru/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
tar: UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
chkpt_ora_create.sql
tar: UserExitExamples/ExitDemo_passthru: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tar: UserExitExamples: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tar: chkpt_ora_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora10.sql
tar: ddl_ora10.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora10upCommon.sql
tar: ddl_ora10upCommon.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora11.sql
tar: ddl_ora11.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora9.sql
tar: ddl_ora9.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_more_ora_create.sql
tar: demo_more_ora_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_more_ora_insert.sql
tar: demo_more_ora_insert.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_create.sql
tar: demo_ora_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_insert.sql
tar: demo_ora_insert.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_lob_create.sql
tar: demo_ora_lob_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_misc.sql
tar: demo_ora_misc.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_pk_befores_create.sql
tar: demo_ora_pk_befores_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_pk_befores_insert.sql
tar: demo_ora_pk_befores_insert.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_pk_befores_updates.sql
tar: demo_ora_pk_befores_updates.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_cleartrace.sql
tar: ddl_cleartrace.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ddl2file.sql
tar: ddl_ddl2file.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_disable.sql
tar: ddl_disable.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_enable.sql
tar: ddl_enable.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_pin.sql
tar: ddl_pin.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_remove.sql
tar: ddl_remove.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_setup.sql
tar: ddl_setup.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_status.sql
tar: ddl_status.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_staymetadata_off.sql
tar: ddl_staymetadata_off.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_staymetadata_on.sql
tar: ddl_staymetadata_on.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_tracelevel.sql
tar: ddl_tracelevel.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_trace_off.sql
tar: ddl_trace_off.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_trace_on.sql
tar: ddl_trace_on.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
params.sql
tar: params.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
role_setup.sql
tar: role_setup.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
marker_remove.sql
tar: marker_remove.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
marker_setup.sql
tar: marker_setup.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
marker_status.sql
tar: marker_status.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tar: Error exit delayed from previous errors
oracle : lab : @lab : /u01/app/oracle/product/goldengate
$
After some research, I found, it is a bug and can be ignored.
----------------------------------------------------------------------------------------
If you get the following error, fix the OS package in error and try again.
oracle : lab : @lab : /u01/app/oracle/product/goldengate
$./ggsci
./ggsci: error while loading shared libraries: requires glibc 2.5 or later dynamic linker
----------------------------------------------------------------------------------------
After everything is fixed or after a clean install just logon to the ggsci prompt as shown below
by executing ggsci utility and you can put this utility in path and ld_library_path too.
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ 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> CREATE SUBDIRS
-------------------------------------------------------------------------
Now create the subdirectories as:
GGSCI (rac1.rac.meditate.com) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/oracle/product/goldengate
Parameter files /u01/app/oracle/product/goldengate/dirprm: created
Report files /u01/app/oracle/product/goldengate/dirrpt: created
Checkpoint files /u01/app/oracle/product/goldengate/dirchk: created
Process status files /u01/app/oracle/product/goldengate/dirpcs: created
SQL script files /u01/app/oracle/product/goldengate/dirsql: created
Database definitions files /u01/app/oracle/product/goldengate/dirdef: created
Extract data files /u01/app/oracle/product/goldengate/dirdat: created
Temporary files /u01/app/oracle/product/goldengate/dirtmp: created
Veridata files /u01/app/oracle/product/goldengate/dirver: created
Veridata Lock files /u01/app/oracle/product/goldengate/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/oracle/product/goldengate/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/oracle/product/goldengate/dirver/oosxml: created
Veridata Parameter files /u01/app/oracle/product/goldengate/dirver/params: created
Veridata Report files /u01/app/oracle/product/goldengate/dirver/report: created
Veridata Status files /u01/app/oracle/product/goldengate/dirver/status: created
Veridata Trace files /u01/app/oracle/product/goldengate/dirver/trace: created
Stdout files /u01/app/oracle/product/goldengate/dirout: created
-----------------------------------------------------------------------------------------
Now we will create a database user goldengate, that will be used for managing goldengate.
create tablespace goldengate
datafile '+DATA1' size 300m;
create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp;
and then grant the following permisssions to the goldengate user as:
grant connect,resource to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
--though I gave DBA role also but its not required.
grant dba to goldengate;
Here are the results:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 14:29:48 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> create tablespace goldengate
datafile '+DATA1' size 300m; 2
Tablespace created.
SQL> create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp;
2 3
User created.
SQL> grant connect,resource to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> grant dba to goldengate;
Grant succeeded.
I am enabling supplemental logging on database level as follows.
Here is how the view v$database looks before and after enabling supplemental logging.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_PL from v$database;
SUPPLEME SUP SUP SUP SUP SUP
-------- --- --- --- --- ---
NO NO NO NO NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_PL from v$database;
SUPPLEME SUP SUP SUP SUP SUP
-------- --- --- --- --- ---
IMPLICIT NO NO NO YES NO
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
$
I take the Support of the Lord's Lotus Feet; there is no other place of rest for me. I lean upon You as my Support, O Supreme Lord God. I exist only by Your Power. O God, You are the Honor of the dishonored. I seek to merge with You.
Index of all the posts of Gurpartap Singh's Blog
Oracle Goldegate is a nice tool to do replication. Though its expensive but is accurate and very flexible and with very low overhead on the database it replicates very fast. Once you know the architecture of oracle goldengate and the are familiar with the commands its a nice tool to use. Always keep documentation handy as this has so many parameters that you may not remember them all. Also, to do complex replication you should know your data.
Installing goldengate, if you are doing replication from one server to another then do teh installation on both target and the source server. I will use it on both the target and the source. You can use it to do replication between two databases on the same server also.
Here starts the install. I will install goldengate on linux. Both my source and target are linux servers.
unzip the file as you downloaded from Oracle Edelivery as:
unzip V18159-01.zip
and the untar it as:
tar -xvof ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar
I have seen the following errors on goldengate in one of the installs:
oracle : lab : @lab : /u01/app/oracle/product/goldengate
$ tar -xvof ggs_redhatAS50_x86_ora10g_32bit_v10.4.0.19_002.tar
mgr
tar: mgr: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ggsci
tar: ggsci: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ggMessage.dat
tar: ggMessage.dat: Cannot change ownership to uid 1901, gid 801: Operation not permitted
help.txt
tar: help.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
bcrypt.txt
tar: bcrypt.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libxml2.txt
tar: libxml2.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
zlib.txt
tar: zlib.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
freeBSD.txt
tar: freeBSD.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
notices.txt
tar: notices.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libicui18n.so.38
tar: libicui18n.so.38: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libicuuc.so.38
tar: libicuuc.so.38: Cannot change ownership to uid 1901, gid 801: Operation not permitted
libicudata.so.38
tar: libicudata.so.38: Cannot change ownership to uid 1901, gid 801: Operation not permitted
cobgen
tar: cobgen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddlcob
tar: ddlcob: Cannot change ownership to uid 1901, gid 801: Operation not permitted
emsclnt
tar: emsclnt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
keygen
tar: keygen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
logdump
tar: logdump: Cannot change ownership to uid 1901, gid 801: Operation not permitted
reverse
tar: reverse: Cannot change ownership to uid 1901, gid 801: Operation not permitted
server
tar: server: Cannot change ownership to uid 1901, gid 801: Operation not permitted
convchk
tar: convchk: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddlgen
tar: ddlgen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
defgen
tar: defgen: Cannot change ownership to uid 1901, gid 801: Operation not permitted
extract
tar: extract: Cannot change ownership to uid 1901, gid 801: Operation not permitted
replicat
tar: replicat: Cannot change ownership to uid 1901, gid 801: Operation not permitted
usrdecs.h
tar: usrdecs.h: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tcperrs
tar: tcperrs: Cannot change ownership to uid 1901, gid 801: Operation not permitted
bcpfmt.tpl
tar: bcpfmt.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
db2cntl.tpl
tar: db2cntl.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_access.tpl
tar: ddl_access.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_db2_os390.tpl
tar: ddl_db2_os390.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_db2.tpl
tar: ddl_db2.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_informix.tpl
tar: ddl_informix.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_mss.tpl
tar: ddl_mss.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_mysql.tpl
tar: ddl_mysql.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_nssql.tpl
tar: ddl_nssql.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_oracle.tpl
tar: ddl_oracle.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_sqlmx.tpl
tar: ddl_sqlmx.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_sybase.tpl
tar: ddl_sybase.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_tandem.tpl
tar: ddl_tandem.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
sqlldr.tpl
tar: sqlldr.tpl: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/readme.txt
tar: UserExitExamples/ExitDemo_lobs/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
tar: UserExitExamples/ExitDemo_lobs/exitdemo_lob.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
tar: UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
tar: UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/
tar: UserExitExamples/ExitDemo_lobs: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
tar: UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
tar: UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/readme.txt
tar: UserExitExamples/ExitDemo_pk_befores/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
tar: UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/
tar: UserExitExamples/ExitDemo_pk_befores: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/readme.txt
tar: UserExitExamples/ExitDemo/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/exitdemo.c
tar: UserExitExamples/ExitDemo/exitdemo.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/exitdemo.vcproj
tar: UserExitExamples/ExitDemo/exitdemo.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
tar: UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/
tar: UserExitExamples/ExitDemo: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
tar: UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/readme.txt
tar: UserExitExamples/ExitDemo_more_recs/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
tar: UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
tar: UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/
tar: UserExitExamples/ExitDemo_more_recs: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
tar: UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/readme.txt
tar: UserExitExamples/ExitDemo_passthru/readme.txt: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
tar: UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c: Cannot change ownership to uid 1901, gid 801: Operation not permitted
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
tar: UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX: Cannot change ownership to uid 1901, gid 801: Operation not permitted
chkpt_ora_create.sql
tar: UserExitExamples/ExitDemo_passthru: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tar: UserExitExamples: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tar: chkpt_ora_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora10.sql
tar: ddl_ora10.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora10upCommon.sql
tar: ddl_ora10upCommon.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora11.sql
tar: ddl_ora11.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ora9.sql
tar: ddl_ora9.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_more_ora_create.sql
tar: demo_more_ora_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_more_ora_insert.sql
tar: demo_more_ora_insert.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_create.sql
tar: demo_ora_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_insert.sql
tar: demo_ora_insert.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_lob_create.sql
tar: demo_ora_lob_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_misc.sql
tar: demo_ora_misc.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_pk_befores_create.sql
tar: demo_ora_pk_befores_create.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_pk_befores_insert.sql
tar: demo_ora_pk_befores_insert.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
demo_ora_pk_befores_updates.sql
tar: demo_ora_pk_befores_updates.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_cleartrace.sql
tar: ddl_cleartrace.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_ddl2file.sql
tar: ddl_ddl2file.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_disable.sql
tar: ddl_disable.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_enable.sql
tar: ddl_enable.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_pin.sql
tar: ddl_pin.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_remove.sql
tar: ddl_remove.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_setup.sql
tar: ddl_setup.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_status.sql
tar: ddl_status.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_staymetadata_off.sql
tar: ddl_staymetadata_off.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_staymetadata_on.sql
tar: ddl_staymetadata_on.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_tracelevel.sql
tar: ddl_tracelevel.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_trace_off.sql
tar: ddl_trace_off.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ddl_trace_on.sql
tar: ddl_trace_on.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
params.sql
tar: params.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
role_setup.sql
tar: role_setup.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
marker_remove.sql
tar: marker_remove.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
marker_setup.sql
tar: marker_setup.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
marker_status.sql
tar: marker_status.sql: Cannot change ownership to uid 1901, gid 801: Operation not permitted
tar: Error exit delayed from previous errors
oracle : lab : @lab : /u01/app/oracle/product/goldengate
$
After some research, I found, it is a bug and can be ignored.
----------------------------------------------------------------------------------------
If you get the following error, fix the OS package in error and try again.
oracle : lab : @lab : /u01/app/oracle/product/goldengate
$./ggsci
./ggsci: error while loading shared libraries: requires glibc 2.5 or later dynamic linker
----------------------------------------------------------------------------------------
After everything is fixed or after a clean install just logon to the ggsci prompt as shown below
by executing ggsci utility and you can put this utility in path and ld_library_path too.
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ 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> CREATE SUBDIRS
-------------------------------------------------------------------------
Now create the subdirectories as:
GGSCI (rac1.rac.meditate.com) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/oracle/product/goldengate
Parameter files /u01/app/oracle/product/goldengate/dirprm: created
Report files /u01/app/oracle/product/goldengate/dirrpt: created
Checkpoint files /u01/app/oracle/product/goldengate/dirchk: created
Process status files /u01/app/oracle/product/goldengate/dirpcs: created
SQL script files /u01/app/oracle/product/goldengate/dirsql: created
Database definitions files /u01/app/oracle/product/goldengate/dirdef: created
Extract data files /u01/app/oracle/product/goldengate/dirdat: created
Temporary files /u01/app/oracle/product/goldengate/dirtmp: created
Veridata files /u01/app/oracle/product/goldengate/dirver: created
Veridata Lock files /u01/app/oracle/product/goldengate/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/oracle/product/goldengate/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/oracle/product/goldengate/dirver/oosxml: created
Veridata Parameter files /u01/app/oracle/product/goldengate/dirver/params: created
Veridata Report files /u01/app/oracle/product/goldengate/dirver/report: created
Veridata Status files /u01/app/oracle/product/goldengate/dirver/status: created
Veridata Trace files /u01/app/oracle/product/goldengate/dirver/trace: created
Stdout files /u01/app/oracle/product/goldengate/dirout: created
-----------------------------------------------------------------------------------------
Now we will create a database user goldengate, that will be used for managing goldengate.
create tablespace goldengate
datafile '+DATA1' size 300m;
create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp;
and then grant the following permisssions to the goldengate user as:
grant connect,resource to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
--though I gave DBA role also but its not required.
grant dba to goldengate;
Here are the results:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 14:29:48 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> create tablespace goldengate
datafile '+DATA1' size 300m; 2
Tablespace created.
SQL> create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp;
2 3
User created.
SQL> grant connect,resource to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> grant dba to goldengate;
Grant succeeded.
I am enabling supplemental logging on database level as follows.
Here is how the view v$database looks before and after enabling supplemental logging.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_PL from v$database;
SUPPLEME SUP SUP SUP SUP SUP
-------- --- --- --- --- ---
NO NO NO NO NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_PL from v$database;
SUPPLEME SUP SUP SUP SUP SUP
-------- --- --- --- --- ---
IMPLICIT NO NO NO YES NO
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
$
I take the Support of the Lord's Lotus Feet; there is no other place of rest for me. I lean upon You as my Support, O Supreme Lord God. I exist only by Your Power. O God, You are the Honor of the dishonored. I seek to merge with You.
Wednesday, November 10, 2010
Index of all the posts of Gurpartap Singh's Blog
Whatever pleases You is good; True is the Pleasure of Your Will. You are the One, pervading in all; You are contained in all. You are diffused throughout and permeating all places and interspaces; You are known to be deep within the hearts of all beings. Joining the Saadh Sangat, the Company of the Holy, and submitting to His Will, the True Lord is found.
Index of all the posts of my Blog:
Part 1 - Vmware Setup for RAC 11gR2
Part 2, Setup Base Brick Machine
Part 3, SAN Setup For 11gR2
Part 4, RAC VM Setup
Part 5, Install Grid Infrastructure
Part 6, Run asmca and Install RDBMS software
Part 7, Create RAC database with DBCA
Part 8, Add node and instance to existing 11gR2 cluster db
Part 9, Delete node and inst from existing 11gR2 cluster db
Part 10, Delete node manually and re-add it on 11gR2 cluster
Part 11, Add Grid Control Agent to the new node in cluster
Part 12 of series, Install JDK for Enterprise Manager Grid COntrol 11gR1
Part 13 of series, Install Weblogic wls10.3.2 for Enterprise Manager Grid Control 11gR1
Part 14 of series, Install Enterprise Manager Grid Control 11gR1
Part 15 of series, Create ASM Cluster File System (ACFS) on 11gR2 ASM
Part 16 of series, Installing Oracle Goldengate on Linux
Part 17 of series, Configuring Manager Process of Oracle Goldengate on Linux
Part 18 of series, Using Oracle Goldengate to do Initial Load of Oracle Goldengate on Linux
Part 19 of series, Configure the online change synchronization of Goldengate on Linux
Post 20 of series, Configure Oracle Goldengate Replication with Datapump on Linux
Post 21 of series, Configure little bit complex Goldengate Replication on Linux
Post 22 of series,DDL synchronization/replication with Tutorial
Post 23 of series,Configure more complex goldengate replication with Tutorial
Post 24 of series, Goldengate initial load using cross platform transportable tablespaces Solaris to Linux
Post 25 of series, Configure discard file, exception table and stored procedure in Goldengate replication with Tutorial.Post
26 of series, Using SQL Plan Management(SPM) in 11g with Tutorial.
Post 27 of series, Setup email on unix server using SMTP of gmail.com(Google Mail) to get alerts from scripts.
Part 28 of series - Upgrade Enterprise Manager Grid Control from 10.2.0.5 to 11gr1 (11.1.0.1) on Solairs/Unix, Install JDK and Weblogic
Part 29 of series - Upgrade Enterprise Manager Grid Control from 10.2.0.5 to 11gr1 (11.1.0.1) on Solairs/Unix, Install and upgrade Grid Control, OMS
Part 30 of series - Upgrade Enterprise Manager Grid Control from 10.2.0.5 to 11gr1 (11.1.0.1) on Solairs/Unix, Upgrade Additional Grid Control , OMS
Part 31 of series - Move/Migrate/Relocate OCR/Vote disk in 11gr2 Grid infrastructure from one ASM diskgroup to another ASM diskgroup
Part 32 of series - Integrate Goldengate Manager Process with 11gr2 Grid Infrastructure, use GI to start and stop Mgr Process and so extract/replict
Part 33 of series - Script to monitor Goldengate extract/replicat process status & get the email alert or the remedy ticket if any process is ABENDED
Part 34 of series - Create standby database 11gR2 from 3 node RAC primary to 2 node RAC standby
Part 35 of series - Install Oracle Enterprise Manager Cloud Control 12c on Linux 64-bit
Part 36 of series - Presentation on Oracle Enterprise Manager Cloud Control 12c at Vancouver Oracle Users Group
Post 37 of series - Zero-Downtime Database Upgrades Using Oracle GoldenGate
Part 38 of series - Insert blocking horror....Insert on child table without index on foreign key locks parent table
Part 39 of series - Upgrade Oracle Enterprise Manager 11g to Oracle Enterprise Manager 12c, Part - 1
Part 40 of series - Upgrade Oracle Enterprise Manager 11g to Oracle Enterprise Manager 12c, Part - 2
Part 41 of series - Re-define tables and create referecne partitions on child tables
Post 42 of Series - Install Oracle ZFS on Virtual Box and mount on VMware Machines - Part 1
Post 43 of Series - Create Oracle ZFS Snapshots & Clones and mount on VMware Machines - Part 2
Post 44 of Series - Create or start database on Oracle ZFS Appliance Clones that are mounted on Unix machine - Part 3
Post 58 of Series - Install Oracle OSB (Oracle Secure Backup) on RHEL/CentOS 6 and Create virtual tape drive
Post 59 of Series : Install Oracle Secure Backup Client and Configuring Hosts and Datafile Sets on OSB (Oracle Secure Bakup)
That day is beautiful, when God comes to mind. Cursed is that day, no matter how pleasant the season, when the Supreme Lord God is forgotten.
Index of all the posts of my Blog:
Part 1 - Vmware Setup for RAC 11gR2
Part 2, Setup Base Brick Machine
Part 3, SAN Setup For 11gR2
Part 4, RAC VM Setup
Part 5, Install Grid Infrastructure
Part 6, Run asmca and Install RDBMS software
Part 7, Create RAC database with DBCA
Part 8, Add node and instance to existing 11gR2 cluster db
Part 9, Delete node and inst from existing 11gR2 cluster db
Part 10, Delete node manually and re-add it on 11gR2 cluster
Part 11, Add Grid Control Agent to the new node in cluster
Part 12 of series, Install JDK for Enterprise Manager Grid COntrol 11gR1
Part 13 of series, Install Weblogic wls10.3.2 for Enterprise Manager Grid Control 11gR1
Part 14 of series, Install Enterprise Manager Grid Control 11gR1
Part 15 of series, Create ASM Cluster File System (ACFS) on 11gR2 ASM
Part 16 of series, Installing Oracle Goldengate on Linux
Part 17 of series, Configuring Manager Process of Oracle Goldengate on Linux
Part 18 of series, Using Oracle Goldengate to do Initial Load of Oracle Goldengate on Linux
Part 19 of series, Configure the online change synchronization of Goldengate on Linux
Post 20 of series, Configure Oracle Goldengate Replication with Datapump on Linux
Post 21 of series, Configure little bit complex Goldengate Replication on Linux
Post 22 of series,DDL synchronization/replication with Tutorial
Post 23 of series,Configure more complex goldengate replication with Tutorial
Post 24 of series, Goldengate initial load using cross platform transportable tablespaces Solaris to Linux
Post 25 of series, Configure discard file, exception table and stored procedure in Goldengate replication with Tutorial.Post
26 of series, Using SQL Plan Management(SPM) in 11g with Tutorial.
Post 27 of series, Setup email on unix server using SMTP of gmail.com(Google Mail) to get alerts from scripts.
Part 28 of series - Upgrade Enterprise Manager Grid Control from 10.2.0.5 to 11gr1 (11.1.0.1) on Solairs/Unix, Install JDK and Weblogic
Part 29 of series - Upgrade Enterprise Manager Grid Control from 10.2.0.5 to 11gr1 (11.1.0.1) on Solairs/Unix, Install and upgrade Grid Control, OMS
Part 30 of series - Upgrade Enterprise Manager Grid Control from 10.2.0.5 to 11gr1 (11.1.0.1) on Solairs/Unix, Upgrade Additional Grid Control , OMS
Part 31 of series - Move/Migrate/Relocate OCR/Vote disk in 11gr2 Grid infrastructure from one ASM diskgroup to another ASM diskgroup
Part 32 of series - Integrate Goldengate Manager Process with 11gr2 Grid Infrastructure, use GI to start and stop Mgr Process and so extract/replict
Part 33 of series - Script to monitor Goldengate extract/replicat process status & get the email alert or the remedy ticket if any process is ABENDED
Part 34 of series - Create standby database 11gR2 from 3 node RAC primary to 2 node RAC standby
Part 35 of series - Install Oracle Enterprise Manager Cloud Control 12c on Linux 64-bit
Part 36 of series - Presentation on Oracle Enterprise Manager Cloud Control 12c at Vancouver Oracle Users Group
Post 37 of series - Zero-Downtime Database Upgrades Using Oracle GoldenGate
Part 38 of series - Insert blocking horror....Insert on child table without index on foreign key locks parent table
Part 39 of series - Upgrade Oracle Enterprise Manager 11g to Oracle Enterprise Manager 12c, Part - 1
Part 40 of series - Upgrade Oracle Enterprise Manager 11g to Oracle Enterprise Manager 12c, Part - 2
Part 41 of series - Re-define tables and create referecne partitions on child tables
Post 42 of Series - Install Oracle ZFS on Virtual Box and mount on VMware Machines - Part 1
Post 43 of Series - Create Oracle ZFS Snapshots & Clones and mount on VMware Machines - Part 2
Post 44 of Series - Create or start database on Oracle ZFS Appliance Clones that are mounted on Unix machine - Part 3
Post 45 of Series - Space calculation and analysis in case of NORMAL redundancy on ASM
Post 46 of Series - Parallel select query spanning on multiple RAC instances dies if one of the instance its running on crashes or stops
Post 47 of Series - Multiplex votedisk in oracle grid infrastructure on 11gR2
Post 48 of Series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux as guest OS (Part 1, Vmware Setup)
Post 49 of series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux CentOS or RHEL 6 as guest OS (Part 2, Setup Base Brick Machine for RAC 12c install (bbm) and (san_rac_bbm) )
Post 50 of series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux CentOS or RHEL 6 as guest OS (Part 3, Setup SAN for RAC 12c install (san1))
Post 51 of series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux CentOS or RHEL 6 as guest OS (Part 4, Setup Database Machine for RAC 12c install (rac20))
Post 52 of series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux CentOS or RHEL 6 as guest OS (Part 5, Install Grid Infrastructure 12c)
Post 53 of series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux CentOS or RHEL 6 as guest OS (Part 6, Install Oracle 12c database software)
Post 54 of series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux CentOS or RHEL 6 as guest OS (Part 7, Create Oracle database 12c with dbca)
Post 55 of series - Install Oracle RAC 12c on Vmware with Windows 7 64-bit as host OS and Linux CentOS or RHEL 6 as guest OS (Part 8, Fix shared memory problem before container database install)
Post 57 of series : Using DBRM (Database Resource Manager) on RAC 11gR2
Post 58 of Series - Install Oracle OSB (Oracle Secure Backup) on RHEL/CentOS 6 and Create virtual tape drive
Post 59 of Series : Install Oracle Secure Backup Client and Configuring Hosts and Datafile Sets on OSB (Oracle Secure Bakup)
Post 60 of Series : Configuring Schedules, Windows and Triggers and take file system backups using Oracle Secure Backup (OSB)
Post 61 of Series : Restore file system backups using Oracle Secure Backup (OSB)
Post 62 of Series : Backup Oracle Database on 11gr2 RAC using RMAN to Tape using Oracle Secure Backup (OSB)
Post 63 of Series : Restore Oracle Database on 11gr2 RAC using RMAN from Tape using Oracle Secure Backup (OSB)
Post 64 of Series - Some errors and their fixes
That day is beautiful, when God comes to mind. Cursed is that day, no matter how pleasant the season, when the Supreme Lord God is forgotten.
Subscribe to:
Posts (Atom)