Oracle GoldenGate Installation, Part 1
I want to introduce several post about Oracle GoldenGate (GG), a relatively new replication software from Oracle.
We all know replication using Oracle Streams technology. Streams are improving from version to version of the RDBMS, and now it is a very well managed and reliable technology with a large set of options and capabilities. But, in the document Oracle – GoldenGate Statement of Direction (PDF), GoldenGate software was announced as the strategic replication solution for Oracle Database, and Oracle plans to direct all efforts to improve it.Therefore, I think it is time to make the acquaintance of GoldenGate.
This first post is about installating GG software and creating the simplest replication using only the “extract” and “replicat” processes.
Lets start the installation.
1. Preparation
Our first step is preparing the box for our test replication. I am using a VMWare virtual server for my tests.
- Install linux x64 on your box. I used Linux Centos 5.3. The hostname for our source box will be db1.
- Install the Oracle software on the box. I am using Oracle 11gr2 for test purposes. The software from can be found on otn.oracle.com. Unzip and install it.
Oracle software installation:
Copy the two zip files to some location on the machine and unzip them:[oracle@db1 distr]$ export DISTR=/u01/app/oracle/distr [oracle@db1 distr]$ cd $DISTR [oracle@db1 distr]$ unzip linux.x64_11gR2_database_1of2.zip [oracle@db1 distr]$ unzip linux.x64_11gR2_database_2of2.zip
Edit response file for silent installation:
[oracle@db1 distr]$ vi $DISTR/database/response/db_install.rsp [oracle@db1 distr]$ cd $DISTR/database
Set proper parameter for kernel, create necessary user and groups and set limits for the Oracle owner. (I used the
oracleuser for this).Install the software:
[oracle@db1 database]$ ./runInstaller -silent -responseFile $DISTR/database/response/db_install.rsp [oracle@db1 ~]$ su - root [root@db1 ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh
- Create a test database:
[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@db1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName base11r2 -sysPassword qwerty -systemPassword qwerty -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS
- Start the listener and set up a network alias:
[oracle@db1 ~]$ lsnrctl start [oracle@db1 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
2. Install
With the database created, we can install GoldenGate software to the box:
- Get the GG software from OTN. We need “Oracle GoldenGate v10.4.0.x for Oracle 10g 64bit on Redhat 4.0″. The file has size is 27 Mb. It is officially intended for RHEL 4.0 x86-64, but it works fine on OEL 5.3 x86-64 and on Centos 5.3 x86-64.
- Create directory for the software and extract it to the directory:
[oracle@db1 product]$ mkdir /u01/app/oracle/product/gg [oracle@db1 product]$ export GGATE=/u01/app/oracle/product/gg [oracle@db1 product]$ cd $GGATE [oracle@db1 gg]$ unzip V18157-01.zip Archive: V18157-01.zip inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar [oracle@db1 gg]$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
- If you are using Oracle 11gr1 or 11gr2, make a symbolic link . . .
[oracle@db1 gg]$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so /u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so
- . . . and the path to the GG libraries to
LD_LIBRARY_PATH:export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg
- Now we can start GG command line utility to make sure it works.
- You should now get the prompt to the GoldenGate command line interface:
Oracle GoldenGate Command Interpreter for Oracle Version 10.4.0.19 Build 002 Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved. GGSCI (db1) 1>
- Create the necessary working directories for GG.
[oracle@db1 gg]$ ./ggsci
GGSCI (db1) 1>create subdirs
GGSCI (db1) 1>exit
[oracle@db1 gg]$ mkdir $GGATE/discard - The GoldenGate software has been successfully installed to the box db1.
[oracle@db1 gg]$ ./ggsci
3. Destination-side
We must repeat all the above steps on the destination side. Here, it will be host db4 with same database.
4. Source database
The GoldenGate software having been installed successfully, we must prepare the source database for replication.
- Switch the database to archivelog mode:
SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open;
- Enable minimal supplemental logging:
SQL> alter database add supplemental log data;
- Prepare the database to support ddl replication (optional).
a) Turn off recyclebin for the database . . .
SQL> alter system set recyclebin=off scope=spfile;
. . . and bounce it.
b) Create schema for ddl support replication . . .
SQL> create user ggate identified by qwerty default tablespace users temporary tablespace temp;
. . . and grant the necessary privileges to the new user..
[oracle@db1 gg]$ cd $GGATE SQL> grant connect,resource,unlimited tablespace to ggate; SQL> grant execute on utl_file to ggate;
c) Run scripts for creating all necessary objects for support ddl replication:
SQL> @$GGATE/marker_setup.sql SQL> @$GGATE/ddl_setup.sql SQL> @$GGATE/role_setup.sql SQL> grant GGS_GGSUSER_ROLE to ggate; SQL> @$GGATE/ddl_enable.sql
- Create test schemas for replication. I will create a replication from schema
senderto schemareceiver(the receiving schema can be on the same database or on another).a) Source database:
SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp; SQL> grant connect,resource,unlimited tablespace to sender;
b) Destination database:
SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp; SQL> grant connect,resource,unlimited tablespace to receiver;
5. Replication
We’re going to create the simplest replication without the GG data pump (we can add it later). Our goal is to create ddl and ddl replication from the sender schema on the source database to receiver schema on the destination.
Replication also works if you’re using only one database. This is replication between schemas.
- Create and start manager on the source and the destination.
Source:
[oracle@db1 gg]$ cd $GGATE [oracle@db1 gg]$ ./ggsci GGSCI (db1) 4> info all Program Status Group Lag Time Since Chkpt MANAGER STOPPED GGSCI (db1) 6> edit params mgr <code>PORT 7809</code> GGSCI (db1) 7> start manager Manager started.
We can check status of our processes:
GGSCI (db1) 8> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING
- Create the
extractgroup on the source side:GGSCI (db1) 1> add extract ext1, tranlog, begin now EXTRACT added. GGSCI (db1) 2> add exttrail /u01/app/oracle/product/gg/dirdat/lt, extract ext1 EXTTRAIL added. GGSCI (db1) 3> edit params ext1
Add the following lines to the new parameter file for our extract:
--extract group-- extract ext1 --connection to database-- userid ggate, password qwerty --hostname and port for trail-- rmthost db2, mgrport 7809 --path and name for trail-- rmttrail /u01/app/oracle/product/gg/dirdat/lt --DDL support ddl include mapped objname sender.*; --DML table sender.*
We can check our processes again:
GGSCI (db1) 6> info all Program Status Group Lag Time Since Chkpt MANAGER STOPPED EXTRACT STOPPED EXT1 00:00:00 00:10:55
- Create replicat on the destination side:
[oracle@db2 gg]$ cd $GGATE [oracle@db2 gg]$ ./ggsci add checkpoint table to the destination database GGSCI (db2) 1> edit params ./GLOBAL and put following lines to the global parameter file: GGSCHEMA ggate CHECKPOINTTABLE ggate.checkpoint ~ GGSCI (db2) 2> dblogin userid ggate Password: Successfully logged into database. GGSCI (db2) 3> add checkpointtable ggate.checkpoint Successfully created checkpoint table GGATE.CHECKPOINT. Create replicat group: GGSCI (db2) 4> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/lt,checkpointtable ggate.checkpoint REPLICAT added. create parameter file for replicat: GGSCI (db2) 5> edit params rep1
And put following lines in the parameter file:
--Replicat group -- replicat rep1 --source and target definitions ASSUMETARGETDEFS --target database login -- userid ggate, password qwerty --file for dicarded transaction -- discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10 --ddl support DDL --Specify table mapping --- map sender.*, target receiver.*;
- Start extract and replicat:
Source:
GGSCI (db1) 14> start extract ext1
Destination:
GGSCI (db2) 15> start replicat rep1
- Check all processes.
Source:
GGSCI (db1) 8> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:05
Destination:
GGSCI (db1) 8> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:00
Our replication has been successfully created.
6. Checking
Now we can check our replication. We will create some tables in the sender schema on the source, insert some rows, and check how it will replicate to destination side.
- Source database:
SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12)); SQL> insert into sender.test_tab_1 values (1,'test_1'); SQL>commit;
- Destination database:
SQL> select * from receiver.test_tab_1; ID RND_STR ---------- ------------ 1 test_1
Our GoldenGate DDL and DML replication is now working. The table was created on the destination side and data were replicated.
In the next post in this series, I will show how to modify our replication.
Category: Oracle, Technical Blog
Tags: GoldenGate, Oracle
Nice write-up, Gleb.
In step 3 of the Install phase, you can use ggsci to create the required sub-directories instead of creating them manually.
The ggsci command to use is “create subdirs”. When executed, it will create all the required sub-directories needed for Oracle GoldenGate. If the directory already exists
… it is skipped.
- Steve B
Yes, you are absolutely right Steve. We can use it and it works fine. The command creates all necessary directories. I’ll correct the post.
Thank you Steve.
I haven’t used GG for about 2 years, is the monitoring of the replication lag time still done via their command line tools?
I created some scripts to check lag time and graph the results with gnuplot — I can find them and dust them off if anyone is interested.
I am checking lag using command line. But I think it would be great if you share you scripts.
Gleb
This is really a great post. I have been looking for an article like this for last few days and finally find something that is very well straight forward to implement gg. I noticed this particual post is replicating only ddl, can you explain or post an article regarding replicate schema or perhaps an entire database? Thank you.
[...] Pythian’s Gleb Otochkin begins a series on Oracle GoldenGate installation. [...]
Hi,
This post is about replication entire schema.
It means all dml and ddl changes will be replicated to destination database.
The index or table created in the schema SENDER on source would be replicated to target schema RECEIVER.
Also any manipulation with data on source would be propagated and applied on target schema.
–Gleb
Thank you for your prompt reply and yes, this article does replicate both the DDL and DML. there were some things to be changed in my parameter files but I have it fixed. Now I am trying configure my parameter files to load the initial data but everytime I try to run the extract command, goldengate cannot find my redo logs or the archive logs. I am using ASM so I am not sure if there is a way for GoldenGate to access ASM filesystem or what. I have included the necessary parameters regarding asm user and password but I don’t know what to pass as for the destination path for log files in parameter file. Does anyone as any idead what to put there? I tried +DGROUP/dbname/filename but didn’t work. I also tried without the plus (+) sign but that didn’t work either. any help would be appreciated.
I understand that this may not the right place to ask this questions but I don’t know if there are any other forums or blogs dedicated to gg.
Thank you,
It would be great to get bit more information how you try to extract data.
The extract process would find information about necessary logs in v$log and v$logfile or in v$archived_log views.
–Gleb
Also Alex did great job and create blog posts about extract internals.
You can read it here
http://www.pythian.com/news/7225/oracle-goldengate-extract-internals-part-i/
Thanks, GoldenGate is pulling logs from ASM now. I had to modify my listener and tnsnames file. Thanks for all your help.
HI
does anyone have similar setup guide for goldengate on mssql db platform ?
help is appriciated
Regards
I’ll try to do it in another post.
Thanks Gleb,
I appriciate your help, please send me link when you upload this info.
Kind Regards
Manish
It will be great if you can explain how to install goldengate on windows system. Also am looking for table to table replication along with initial loading.
Thanks in advance.
Regards,
Murali
How would you replicat entire database instead of a particular schema? Thank you.
I have installed Golden Gate on windows. I’ll create post about installing it to windows and how replicate some tables.
Thats excellent!!
Eagerly waiting for installing on windows server.
Thanks in advance.
i am getting following error for initial data load from sql2000 To sql2000 db
after executing following command on destination server
Start replicat repl6
Database Language and Character Set:
Warning: Unable to determine the application and database codepage settings.
Please refer to user manual for more information.
***********************************************************************
** Run Time Messages **
***********************************************************************
Have you set setting for ODBC connection to use ANSI?
– Gleb
HI Gleb
yes i have checked boxes for following options
Use ANSI quoted identifiers. and
Use Ansi nulls,padding and warnings.
on source and destination ODBC connection.
Hi Manish,
I am installing GG for ms sql server now and will check you case.
Unfortunately I don’t have ms sql 2000 and will check on another version.
Can you provide bit more information about replicated objects?
–Gleb
A windows installation guide is available here
http://thinktibits.blogspot.com/2010/04/oracle-goldengate-installation-on_5820.html
Excellent, you are rocking!!!
Hi,
After following the entire steps provided here, I am not able to start the extract process. REPLICAT has been started.
My environment:
1. I have 2 db’s (ver 11.2) on the same host.
2. Have 2 separate installations of goldengate for 2 diff DB’s
3. Started managers at both the places (on ports 7809 and 7810)
Pls help
—————————————
GGSCI (adc2180645) 105> start extract myext1
Sending START request to MANAGER …
EXTRACT MYEXT1 starting
GGSCI (adc2180645) 106> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED MYEXT1 00:00:00 00:33:45
—————————————
HI
please check your event viewer for specific description of error message. there could be several reason why Extract fail to start.
Manish
Hi sns,
Can you provide information from extract rpt file?
You can find it in $GGATE/dirrpt
I think it should be:
$GGATE/dirrpt/ext1.rpt
I guess some parameters could be wrong or firewall blocked connections from source to destination.
If you have manager on destination side on port 7810 then you have use parameter fro extract:
rmthost db2, mgrport 7810
–Gleb
[...] 30-How to install Golden Gate for test purposes? Gleb Otochkin-Oracle GoldenGate Installation, Part 1 [...]
Hi;
I’ve follow exactly your steps above, i can start manager and replicat process but i fail to start the extract process. Running “view ggsevt” shows process abending. Any idea? Is there any detail logfiles that i can see? I’m setting up gg on one linux server running one instance only.
thanks;
andrew
hi Andrew,
Yes, you can see logs for the processes in the directory $GGate/dirrpt
Your last log file for extract should have name ext1.rpt.
–Gleb
I have installed Oracle golden gate in windows. I have used with and without the director, but I get an error:
The source is oracle and the target is SQL 2005 , Although I tried to use Mysql 4.1.
The errors are:
- In SQL, If I run:
GGSCI (rst-act3ct2) 32> DBLOGIN SOURCEDB dns_sql, USERid adriana, PASSWORD adriana
ERROR: Unrecognized parameter (SOURCEDB), expected USERID.
I have tried in different ways (dns_sql is the DSN configured):
GGSCI (rst-act3ct2) 33> DBLOGIN USERid adriana@localhost:1433/bd_golden, PASSWORD adriana
ERROR: Failed to open data source for user ADRIANA@LOCALHOST:1433/BD_GOLDEN.
GGSCI (rst-act3ct2) 34> DBLOGIN USERid adriana, PASSWORD adriana
ERROR: Failed to open data source for user ADRIANA.
GGSCI (rst-act3ct2) 35> DBLOGIN USERid adriana@dsn_sql, PASSWORD adriana
ERROR: Failed to open data source for user ADRIANA@DSN_SQL.
Where as for oracle DB express it works the DBLOGIN command:
GGSCI (rst-act3ct2) 36> DBLOGIN USERid adriana@localhost:1521, PASSWORD adriana
Successfully logged into database.
-Besides, If I run “start extract” does not work:
GGSCI (rst-act3ct2) 25> START C2
Sending START request to MANAGER …
EXTRACT C2 starting
GGSCI (rst-act3ct2) 27> INFO C2
EXTRACT C2 Initialized 2010-03-29 15:15 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
And if I run “start Replicat” shows the following:
GGSCI (rst-act3ct2) 49> INFO D1
REPLICAT D1 Initialized 2010-03-29 14:39 Status STOPPED
Checkpoint Lag 00:00:00 (updated 01:15:18 ago)
Log Read Checkpoint File C:\MANAGERSQL\dirdat\11000000
First Record RBA 0
It appears that the target is not working properly.
I could help. Thanks.
hi Adriana,
What version GG are you using for SQL 2005?
and
What version of oracle do you use?
–Gleb
Thank you.
Hi
The version GG is 10.4 and of Oracle 10.2.
Thank you
Adriana,
Are you sure you have GG 10.4 for SQL Server and not a version for Oracle?
-Sean
for some reason extract process is not starting , i don’t know where to look for error .
can you please take a look .
GGSCI (belgium.nms.fnc.fujitsu.com) 45> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (belgium.nms.fnc.fujitsu.com) 46> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 01:09:01
REPLICAT STOPPED REP1 00:00:00 00:17:42
- ext1
USERID ggate, PASSWORD qwerty_123
RMTHOST belgium.nms.fnc.fujitsu.com, MGRPORT 7809
rmttrail /netsmart/nsoracle/product/gg/dirdat/lt
ddl include mapped objname sumber.*
TABLE sumber.*;
You can find extract log in the directory:
/dirrpt
In you case the log name should be ext1.rpt
vi /dirrpt/ext1.rpt
–Gleb
look in the ggserr.log and the dirrpt/ext1.rpt
Hi, in my test installation the DDLs, INSERTs AND DELETEs are replicated with success but the UPDATEs are getting error.
The error is:
Problem replicating SENDER.TEST_TAB_1 to RECEIVER.TEST_TAB_1
Record not found
Error (1403) occurred with compressed update record (target format)…
*
ID =
RND_STR = XxXxXy
*
May be caused by the inexistence of PK ?
Yes, the possible cause of the error was lack of primary key for the replicated table.
It is strongly recommended to have PK for all replicated tables.
But in every cases it can be related to different reasons.
You can check the records in destination table.
Maybe you don’t have a record for update or destination records had different values in comparing with source.
–Gleb
Any progress ? I get stuck into this error , else~
Jay
hi Jay,
Have you tried to create primary key for replicated tables?
–Gleb
I will try create primary/unique key and see if any abending errors ,
It seems that we can add for extrator as below:
add trandata user.table , COLs (A)
to specify keycols without creating primary keys , I will try this else , thanks your advice~
hi Jay,
Yes you can define some cols for identify row in parameters.
But primary key is not only for identifying rows but also for improving replicat work.
If you use primary key for tables the replicat will use them for update or delete and the sql plan will be created with using these primary key.
It will play significant role in case big tables.
Gleb
Extract is failing with the following error –
2010-05-12 17:28:05.238 Redo thread 1: No valid log files for current redo sequence 32, thread 1, error retrieving redo file name for sequence 32, archived = 0, use_alternate = 0
2010-05-12 17:28:05.238 Redo thread 1: Not able to establish initial position for begin time 2010-05-12 17:15:35.
The source is one of the node in a RAC and redo logs are on +DATA01. Earlier, I modified the extract to configure the threads (per error message) as follows and resolved earlier errors –
add extract ext1, tranlog, threads 2, begin now
Thanks
It more likes as lost archive log on source.
Are all your redo and archive logs available from cluster node where GG is working?
–Gleb
The “Record not found” error on UPDATEs was fixed with the command “ADD TRANDATA SENDER.*” on source. Thanks, Gleb.
Hi,
I get this error:
GGS WARNING 150 TCP/IP error 10060
after create table for replica
Hi engin,
It looks as some network problem.
As I remember correctly it is tcp timeout error.
Maybe your GG on destination side was down or inaccessible on that time.
–Gleb
Hi everyone!
I need to migrate my data from SQL Server 2005 to Oracle 10g.
I configured Oracle as per the guidelines given in this site but how should I configure my SQL Server? Both OS are Windows.
Please guide me.
Really looking ahead for a solution.
Thanks a bunch.
Nith
hi,
I done only one hr schema for replicate and it works. but how I can add second schema exaple second schema name is scot and I want replicat both schema ?
thank you
hi engin,
For add schema to replication you have to change configuration for extract on source database and add new schema:
change:
ddl include mapped objname sender.*;
to
ddl include mapped objname sender.*,new_schema.*;
and
TABLE sender.*;
to
TABLE sender.*,new_schema.*;
also you need to include the schema to datapump (if you use it)
change
TABLE sender.*;
to
TABLE sender.*,new_schema.*;
and modify mapping on destination database (if you use remapping for the schemas)
–Gleb
hi again,
I could not find configure for active – active (bi-directional). do you have any idea for this configuration?
thank you
hi engin,
For bi-directional replication we need:
requirements:
replicat checkpoint table on both systems.
add parameter to extract:
TRANLOGOPTIONS EXCLUDEUSER
On every system we have to create:
1. extract (for capture local changes)
2. datapump (for send changes to remote system)
3. replicat (for apply changes from remote system)
Synchronize the data and start the replication on both side.
Also I think you probably will need in conflict resolution for your transaction.
–Gleb
thank you so much for repling.
the checkpointtable name must be the same on both system or will be different?
The checkpoint table will store different data for different system you have to create and define it for every system.
–Gleb
hi ,
the steps are very clear to me. i successfully did for one schema.
my requirement is this
i have 5 schemas. i need to capture all ddl and dml changes of all the schemas.
do i need to create individual extract process for each schema?
do i need to create individual replicat process for each schema?
currently my ext params is this way
extract ext_new
userid ggate, password abcd
rmthost cccc007, mgrport 7809
rmttrail /s001/copy01/oracle/oracle_sw/ggate/dirdat/nw
ddl include mapped objname AAPROFILE.* &
include mapped objname sender.* &
include mapped objname xxxx.* &
include mapped objname yyyy.* &
include mapped objname zzzzz.*
table sender.*;
TABLE xxxx.*;
TABLE yyyy.*;
TABLE zzzz.*;
and rep params are this way
REPLICAT apc_rep
ASSUMETARGETDEFS
userid ggate, password s0lut_1on
discardfile /s001/copy01/oracle/oracle_sw/ggate/discard/apc_rep_discard.txt, append, megabytes 10
DDL
MAP xxxx.*,TARGET xxxx.*;
MAP yyyy.*,TARGET yyyy.*;
MAP zzzz.*,TARGET zzzz.*;
map sender.*, target receiver.*;
both ext and rep are running but not happening
pls give me right method
all above schemas are important . i dont want replicat to fail for one schema. i need other 3 to run atleast. in that case. should i create all seperate ext and rep for all?
awaiting for the reply
hi raji,
I guess you did one mistake in your parameter files:
you have to put mark “;” only after all instruction for process.
As example for extract:
extract ext_new
userid ggate, password abcd
rmthost cccc007, mgrport 7809
rmttrail /s001/copy01/oracle/oracle_sw/ggate/dirdat/nw
ddl include mapped objname AAPROFILE.* &
include mapped objname sender.* &
include mapped objname xxxx.* &
include mapped objname yyyy.* &
include mapped objname zzzzz.*
table sender.*
TABLE xxxx.*
TABLE yyyy.*
TABLE zzzz.*;
And for replicat:
REPLICAT apc_rep
ASSUMETARGETDEFS
userid ggate, password s0lut_1on
discardfile /s001/copy01/oracle/oracle_sw/ggate/discard/apc_rep_discard.txt, append, megabytes 10
DDL
–I am not sure you need remap for your schema
–MAP xxxx.*,TARGET xxxx.*
–MAP yyyy.*,TARGET yyyy.*
–MAP zzzz.*,TARGET zzzz.*
map sender.*, target receiver.*;
Please let me know if it will help.
–Gleb
hi thanks,
it worked .. issue is if i dont remap.. it does not replicate DML only ddl is happening for all schemas put in extract trails.
thanks again.
to add with this i have one more doubt while untarring in lunux .. i got below warnign in 3,4 servers where i tried
tar: mgr: Cannot change ownership to uid 1901, gid 801: Operation not permitted
ggsci
same uid gid in all the linux servers . i downloaded
V18157-01.zip and unzipped on this
ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
and tar xvf
shoudl this warning be ignored?
in my linux neigther root nor oracle userid has this UDI and gid.
any thought?
extract and replication working fine :)
I can suppose the tar has saved uid and gid for the files and tried to preserve them during unpacking.
I know about such bug when tar is unpacking archive to NFS share.
I don’t think it can be a problem. You can change ownership and group after unpacking.
–Gleb
thanks much. tomorrow i am testing 10 20 05 upgrade ( from 10 2 0 4) with minimal down time of application. currnetly buidling clone db.
if you have any such steps please pass on to me. will be helpful
Do you mean step for upgrade database to 10.2.0.5?
I used different way for upgrade.
One of way was rolling upgrade using transient logical standby.
But I am afraid it is a subject for another post :)
–Gleb
Hi Gleb,
10 20 4 source and target are in synch.
At this state to start replicatin testing. i m doing below steps.
tomorrow when applcation starts i am goign to start extrat process.
source
env for ggate is set with 10 20 4 home and ld_library_path
target
env for ggate is set with 10 20 4 home and ld_library_path
application team verifies target gets correct data.
then decided to upgrade.
at this stage , i m making target db down after stopping REPLICAT in target.
manager process is still running in target.
but when i point target db to 10 2 0 5 home and upgrade, post upgrade when i start replicat,
will the trails be applied to new upgraded db?
next time when i source upgraded db, the ld_library_path will be new one and oracle_home also new one.
please suggest.
thanks
raji
It should work after upgrade without problem.
You have to check on destination that LD_LIBRARY_PATH is pointed to proper home.
Also You have to store all archivelogs for restart extract.
Extract should catch up all changes on source from archivelogs.
–Gleb
I receive the following when I start replication process on second instance.
GGS ERROR 2001 Fatal error executing DDL replication: error [Error code [1741], ORA-01741: illegal zero-length identifier, SQL create table “GGATE”.”" (id number, rnd_str varchar2(12)
) /* GOLDENGATE_DDL_REPLICATION */], no error handler present.
and the replicat is abending. Do you know what is the problem?
hi Sebastian,
I suspect some incorrect ddl command on source or incorrect mapping parameter on the destination side.
As you can see the replicat process tried to create table “GGATE”.””.
–Gleb
Hi, do you have any example of configuring an extract/replicat in ALO mode (archived log only mode) ? Thanks a lot.
Hi Gleb,
I am testing Bi-Directional scenario in GGenGate using DB 10.2.0.1.0
Source and Destination are fresh databases. Created GG schema on both the databases.
SOURCE
======
GGSCI (ctsincalsdcg) 1> add extract BGGGO, tranlog, begin now
EXTRACT added.
GGSCI (ctsincalsdcg) 2> add exttrail /punvis/ggdb/GGenGate/dirdat/BD, extract BGGGO
EXTTRAIL added.
GGSCI (ctsincalsdcg) 3> edit params BGGGO
EXTRACT BGGGO
USERID GG, PASSWORD GG
RMTHOST ctsincalsdcg, mgrport 7810
rmttrail /punvis/ggdb/GGenGate/dirdat/BD
GETUPDATEBEFORES
table gg.*;
DESTINATION
===========
GGSCI (ctsincalsdcg) 1>edit params ./GLOBAL
GGSCHEMA GG
CHECKPOINTTABLE GG.BIDIRECTIONAL
GGSCI (ctsincalsdcg) 2> ADD CHECKPOINTTABLE GG.BIDIRECTIONAL
Successfully created checkpoint table GG.BIDIRECTIONAL.
GGSCI (ctsincalsdcg) 3> ADD REPLICAT BGGGO, EXTTRAIL /punvis/ggdb/GGenGate/dirdat/BD, checkpointtable GG.bidirectional
REPLICAT added.
GGSCI (ctsincalsdcg) 4> edit params BGGGO
–Replicat group –
Replicat BGGGO
–source and target definitions
ASSUMETARGETDEFS
–target database login –
userid GG, password GG
DDLOPTIONS IGNOREREPLICATES, GETAPPLOPS
–file for dicarded transaction –
discardfile /chnvis/GGdb/GGenGate/discard/BGGGO_discard.txt, append, megabytes 10
–Specify table mapping —
map gg.*, target GG.*;
SOURCE
======
GGSCI (ctsincalsdcg) 1> add extract BGOGG, tranlog, begin now
EXTRACT added.
GGSCI (ctsincalsdcg) 2> add exttrail /chnvis/golddb/GoldenGate/dirdat/DB, extract BGOGG
EXTTRAIL added.
GGSCI (ctsincalsdcg) 3> edit params BGOGG
EXTRACT BGOGG
USERID GG, PASSWORD GG
RMTHOST ctsincalsdcg, mgrport 7811
rmttrail /chnvis/golddb/GoldenGate/dirdat/DB
GETUPDATEBEFORES
table gg.*;
DESTINATION
===========
GGSCI (ctsincalsdcg) 1>edit params ./GLOBAL
GGSCHEMA GG
CHECKPOINTTABLE GG.BIDIRECTIONAL
GGSCI (ctsincalsdcg) 1> ADD CHECKPOINTTABLE GG.BIDIRECTIONAL
Successfully created checkpoint table GG.BIDIRECTIONAL.
GGSCI (ctsincalsdcg) 2> ADD REPLICAT BGOGG, EXTTRAIL /chnvis/golddb/GoldenGate/dirdat/DB, CHECKPOINTTABLE GG.BIDIRECTIONAL
REPLICAT added.
GGSCI (ctsincalsdcg) 3> edit params BGOGG
–Replicat group –
Replicat BGOGG
–source and target definitions
ASSUMETARGETDEFS
–target database login –
userid gg, password gg
–file for dicarded transaction –
discardfile /punvis/ggdb/GoldenGate/discard/BGOGG_discard.txt, append, megabytes 10
–Specify table mapping —
map gg.*, target gg.*;
When i am starting Extract & Replicat (BGGGO) is working fine.
When i am starting Extract & Replicat (BGOGG) is Abending with the following error.
2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Aborted grouped transaction on
‘GG.BIDIRECTIONAL’, Database error 1 (ORA-00001: unique constraint (GG.SYS_C005495) violated).
2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Repositioning to rba 917 in seq
no 0.
2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: SQL error 1 mapping GG.BIDIRECT
IONAL to GG.BIDIRECTIONAL OCI Error ORA-00001: unique constraint (GG.SYS_C005495) violated (status = 1), SQL .
2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Repositioning to rba 1178 in se
qno 0.
2010-07-12 05:18:19 GGS ERROR 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Error mapping from GG.BIDIRECTI
ONAL to GG.BIDIRECTIONAL.
2010-07-12 05:18:19 GGS ERROR 190 Oracle GoldenGate Delivery for Oracle, bgggo.prm: PROCESS ABENDING.
Could you please provide your valuable inputs to proceed further.
Thanks,
Bhaskar Mudunuri
hi Bhaskar,
The problem is in your checkpoint table.
You are replicating schema gg.
The checkpoint table is on the schema gg as well.
The table shouldn’t be in your replicated schema.
I would advise to create the checkpoint table in some another schema where all golden gate internal tables will be situated.
As you can see in your log the two source tried to insert same data to the table.
–
Gleb
Hi Gleb,
I had configured Oracle DB for GoldenGate.
I have used one more schema for the Goldengate and other schema for transactions.
Could you please tell us the Parametars are required in EXTRACT and REPLICAT to make bidirectional work properly.
As of now i have used the above mentioned configuration and it is going to infinite loop.
Thanks a lot for your help in advance :-)
Regards,
Bhaskar Mudunuri
hi Bhaskar,
1. DDL replication is not supported in bi-directional replication.
Hence forget about ddl replication.
2. It is strongly recommended use primary keys for replicated tables.
3. I advise to check If you have some triggers or constraints (on delete cascade) on your replicated tables which can generate DML operations.
Maybe they should be modified.
4. I advice to think about update conflict resolution for replicated tables.
5. Data looping:
By default the extract should ignore any transaction generated by replicat process.
It means you should not hit data looping in your bi-direction replication and can use usual parameters for extract and replicat.
But I would insure it by using parameter for extract:
tranlogoptions excludeuser ggate
where GGATE is replicat database user which you have used in replicat parameter:
userid ggate, password qwerty
–Gleb
Hello,
We have recently configured goldengate on Solaris/Oracle DBs
Relication is working fine with DDLs but DMLs are not replicating.
We tried this on some of the schema tables as well as dummy tables.
On source create table XYZ as select * from dba_objects.
Table appears on target
drop table XYZ; on source
table gets dropped on target immediately
insert into xyz select * from dba_objects;
Target count does not change.
But same works for dba_tables and some other tables created manually say create table(n number);
Also sometimes replication hung as it is looking for previous incarnation of DB on source.
Did anyone face this issue.
Any help appreciated.
Thanks