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:[[email protected] distr]$ export DISTR=/u01/app/oracle/distr [[email protected] distr]$ cd $DISTR [[email protected] distr]$ unzip linux.x64_11gR2_database_1of2.zip [[email protected] distr]$ unzip linux.x64_11gR2_database_2of2.zip
Edit response file for silent installation:
[[email protected] distr]$ vi $DISTR/database/response/db_install.rsp [[email protected] 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:
[[email protected] database]$ ./runInstaller -silent -responseFile $DISTR/database/response/db_install.rsp [[email protected] ~]$ su - root [[email protected] ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh
- Create a test database:
[[email protected] ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [[email protected] ~]$ export PATH=$ORACLE_HOME/bin:$PATH [[email protected] ~]$ 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:
[[email protected] ~]$ lsnrctl start [[email protected] ~]$ 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:
[[email protected] product]$ mkdir /u01/app/oracle/product/gg [[email protected] product]$ export GGATE=/u01/app/oracle/product/gg [[email protected] product]$ cd $GGATE [[email protected] gg]$ unzip V18157-01.zip Archive: V18157-01.zip inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar [[email protected] 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 . . .
[[email protected] 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.
[[email protected] gg]$ ./ggsci
- 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.
[[email protected] gg]$ ./ggsci
GGSCI (db1) 1>create subdirs
GGSCI (db1) 1>exit
[[email protected] gg]$ mkdir $GGATE/discard - The GoldenGate software has been successfully installed to the box db1.
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..
[[email protected] 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 dml 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:
[[email protected] gg]$ cd $GGATE [[email protected] gg]$ ./ggsci GGSCI (db1) 4> info all Program Status Group Lag Time Since Chkpt MANAGER STOPPED GGSCI (db1) 6> edit params mgr PORT 7809 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:
[[email protected] gg]$ cd $GGATE [[email protected] 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.
279 Comments. Leave new
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
while GUI installation we have an option to create subdirectories. If we have not selected that then we can use “create subdirs” manually.
… 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.
That would be great!
Thanks.
[email protected], could you please provide me those scripts
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
https://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,I find the same problem as you.If you find the solution of this problem,please tell me ,I am so thanks for your answer.
Hi,
What kind of problem are you referring too? Working with ASM ?
—
Gleb
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.
Hi Gleb,
i m very newer in GG,i wanted to install and configure GG 11 in windows xp invoirnemnt using oracle 11g client. as u mention u have done with windows pls send the link for the same.
one more thing i wanted to confirm in window machine have a client and i m going to install GG in the same.can Oracle client connect the remote server and perform the operation,see i have a oracle 11g sid = orcl in which two schema sender and reciver i wanted to replicate the same,can it possbile we can replicate in same data base in two different schema should i need to install two different gg or it will work.
and wanted to confirm gg must intall in db server or it will work with client also.
your reply awaited
Pramod
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
https://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
Hi Andrew
did you get any solution for your “process abending” error ?
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 [email protected]:1433/bd_golden, PASSWORD adriana
ERROR: Failed to open data source for user [email protected]: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 [email protected]_sql, PASSWORD adriana
ERROR: Failed to open data source for user [email protected]_SQL.
Where as for oracle DB express it works the DBLOGIN command:
GGSCI (rst-act3ct2) 36> DBLOGIN USERid [email protected]: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
I have a question, our “steps” look good.
I have trouble for the db_install.rsp. Since there is 2 disk for the oracle software,
which line you tell oracle ‘s response file (db_install.rsp) to look for the second disk.
Is it ok for you to post the db_install.rsp?
or email to me directly , maybe I see it?
Thanks,
Dominica L
Hi Dominica,
What disks do you mean?
Do you have oracle installation software on DVD?
I unzipped the both files in one directory.
I didn’t have any second disk. Also I hadn’t changed default path for installation in response file.
–Gleb
Hi Gleb,
I am trying to migrate a table from Sybase to Oracle using Oracle Golden Gate.
I have installed Sybase 15 and Oracle golden gate 10.4 for sybase 15.
My sysbase services are running.
But when i try to connect to sybase using dblogin in GGSCI i am not being able to do it.
I receive following error –
ERROR: Cannot connect to database master at server BL1DDG1W4BS.
Client Library error: severity(0) number (44) origin(1) layer(4).
ct_connect(): protocol specific layer: external error: The attempt to connect to
the server failed.
Command used –
GGSCI (BL1DDG1W4BS) 12> dblogin sourcedb master userid sa
Password:
master – database name
sa – userid
Both my oracle and sybase are installed on the same system.
Golden gate for sybase and oracle are in different directories.
As per the installation guide DSQUERY variable should be set to the server on which sybase is running.
I have done that also.
Please if you can help me out.
Does sybase db needs special grants?
Hi Prachi,
It looked the problem in libraries using for connection. What operation system do you use?
Maybe setting proper PATH variable can help to resolve it.
also I would mention some things (i don’t think it a cause of problem but …):
Have you created dedicated user for Golden gate and granted system administartor privileges to it?
I wouldn’t use the any default user for connection.
–Gleb
Hi Gleb,
Thanks for your prompt reply.
I was being able to resolve the issue.
Issue was at the sybase end, password related thing. I changed the password and managed to connect to sybase.
I am trying to migrate a sybase table to oracle.
Both sybase and oracle are on same system.
For initial load when i start extract process, it is not finding the replicat parameter files. The details are as below:
I have both Sybase and Oracle installed on the same system. I have installed golden gate for Sybase and Oracle in different directories.
When I run start extract load 1, I receive an error that it could not find file load2.
In my case Sybase is the source and Oracle is the target.
Param file load2 I created on target, ie. Using the command prompt for Oracle which is my target.
Below are the param file definitions
Load1
EXTRACT load1
SOURCEDB pubs,USERID sa,PASSWORD test123
RMTHOST BL1DDG1W4BS, MGRPORT 7812
RMTTASK replicat,GROUP load2
TABLE pubs.dbo.hs_1;
Load2
REPLICAT load2
USERID ggs_owner,PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP pubs.hs_1, TARGET ggs_owner.hs_1
;
It is giving me following error
2010-08-12 17:05:55 GGS INFO 302 Oracle GoldenGate Manager for Sybase,
mgr.prm: EXTRACT LOAD1 starting.
2010-08-12 17:05:55 GGS INFO 414 Oracle GoldenGate Capture for Sybase,
LOAD1.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
2010-08-12 17:05:55 GGS INFO 310 Oracle GoldenGate Capture for Sybase,
LOAD1.prm: EXTRACT LOAD1 starting.
2010-08-12 17:05:55 GGS INFO 310 Oracle GoldenGate Capture for Sybase,
LOAD1.prm: EXTRACT LOAD1 started.
2010-08-12 17:05:56 GGS INFO 301 Oracle GoldenGate Manager for Sybase,
mgr.prm: Command received from EXTRACT on host 10.200.21.20 (START REPLICAT LOA
D2 CPU -1 PRI -1 PARAMS ).
2010-08-12 17:05:56 GGS ERROR 150 Oracle GoldenGate Capture for Sybase,
LOAD1.prm: Error reported by MGR : Parameter file C:\GGSCI\dirprm\load2.prm doe
s not exist.
The golden gate for Oracle which is my target is in D:\GGSC folder. But over here it looks like it is going to the golden gate of source.
Is it that we cannot have both source and target on same system.
Thanks In Advance.
Hi,
I hope your managers for oracle and for sybase are working using different ports and started properly.
Have you tried to start the replicat load02 ( which I guess should be on oracle side) first before starting extract on sybase side?
Maybe in this case it will not try to start replicate because it will be up already.
I was able to run 2 different manager on my machine but it was mssql and oracle.
I think I have to try sybase as well.
–Gleb
Hi Gleb,
My managers for Oracle and sybase are running and working using different ports.
The error (Error reported by MGR : Parameter file C:\GGSCI\dirprm\load2.prm doe
s not exist.) no longer comes.
When i start the extract process at source it runs the extract process normally. But at the target end data is not inserted into the table
eg. info loadp1 gives me following
EXTRACT LOADP1 Last Started 2010-08-16 16:04 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table GGS_OWNER.PRAC_1
2010-08-16 16:04:50 Record 3
Task SOURCEISTABLE.
Below is the detailed step i do
Am just trying to do initial load in sybase from oracle
GGSCI (source) > add extract loadp1, sourceistbale
GGSCI (source) > edit params loadp1
Contents of loadp1
EXTRACT loadp1
USERID ggs_owner,PASSWORD ggs_owner
RMTHOST (Destiniation IP address), MGRPORT 7814 (Port on which sybase is running ie. target
RMTTASK replicat, GROUP loadtest
TABLE ggs_owner.prac_1
On Destination
GGSCI (dest) > add replicat loadtest, specialrun
GGSCI (dest) > edit params loadtest
Contents of loadtest
replicat loadtest
assumetargetdefs
sourcedb pubs userid sa, password test123
map ggs_owner.prac_1, target pubs.hs_1;
On source i run
start extract loadp1
On destination
A window for replication starts
GGSCI (dest) > info loadtest
REPLICAT LOADTEST Initialized 2010-08-16 15:58 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:17:01 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN.
In both oracle and sybase i have dedicated user for golden gate.
Not sure if i am missing something.
Requesting your help.
Thanks In Advance.
Regards,
Prachi
Hi Gleb,
@Goldengate source and target, the memory utilization is very high.
Like in case of one of the system @source the memory utilization is 44% and it is constant.
Need Your advice on how to reduce memory utilization.
Thanks for your help.
Regards
Manish
Hi Manish,
Do you work with data pump or with direct replication from extract to remote replicat trail?
First of all I would advice to use data pump for reduce memory allocation by extract process.
In second we can use parameter CACHEMGR for adjust memory allocation by extract process.
You can check your memory allocation in logs for processes.
–Gleb
Hi Gleb,
I use EXTRACT to extract from the REDO/Archive Logs and PUMP to push to the remote server.
Regards
Manish
Hi Manish,
Have you tried to use parameter CACHEMGR for your extract and datapump processes?
Also it would be great to make sure about memory consumers on the box.
Maybe memory was consumed by some another processes.
–Gleb
Hi
Can it be setup (am sure we should be) with source and destination same Oracle?
Do I need to follow any other steps than posted?
How is more beniftale than Active Data Guard?
Thanks,
Krishna.
hi Krishna,
Yes, you can use replication inside one database between 2 schemas as example.
Also You off course can yse replication between 2 oracle databases.
And also you can use it as logical standby.
The main differenc in compare to active data guard is the database will be open in read write mode.
The replication using GG is more likely to replication by streams than replication which used for standby.
–Gleb
Hi,
When target / source DB instance name are different, where do I have to config this issue?
Thanks
Hi Daniel,
I would advice to set up proper environment variable (ORACLE_SID,ORACLE_HOME) for ssh session before starting replicat or extract. It should be enough for resolving the problem and running extract and replicat for necessary instance.
–Gleb
I am using oracle CDC which is built on top of streams. This enables me to subscribe and process incremental data only. If I switch to GG is how am I going to process incremental data only?
Hi param,
What do you mean under “incremental”?
GG will send only changes in your data except first load (which can be done without using GG).
I think it is quite “incremental”.
From my point of view the GoldenGate is just another way for replication.
–Gleb
i have been assighned one task to execute , details are as below .
i need to setup a Table A1 ,B1 and table A2,B2 on the same database .there columns are as below .
QL> DESC A1
Name Null? Type
—————————————– ——– ——————-
ID NOT NULL VARCHAR2(20)
NAME VARCHAR2(20)
QL> DESC B1
Name Null? Type
—————————————– ——– ——————-
NAME NOT NULL VARCHAR2(20)
STATE VARCHAR2(20)
QL> DESC A2
Name Null? Type
—————————————– ——– ——————-
ID NOT NULL VARCHAR2(20)
NAME VARCHAR2(20)
STATE VARCHAR2(20)
QL> DESC B2
Name Null? Type
—————————————– ——– ——————-
NAME NOT NULL VARCHAR2(20)
STATE VARCHAR2(20)
i have to replicate table B1 TO B2 with out any logic but where as Table A1 TO A2 I NEED TO REPLICAT A1 THAT CALLS THE PACKAGE THAT HAS A QUERY PULLS THE DATA FROM B2 TABLE BY JOINING THE TABLE B2 GET THE STATE COLUMN DATA INTO A2.i am verymuch intrested in GG replications so i applied in my Company for Internal JOB posting but they want to pre screen actually i know the basic stuffs but i dont how to use Macros .i would apperciate if you can help me in this Task .
Hi Esskay,
To be honest I couldn’t get all your needs.
Can you please show what join you meant for tables B?
Do you mean to insert/update to A2 id/name from A1 and state from B2?
–Gleb
Hi all ,
i was trying to install Goldengate 10.4 on IBM AIX 5.3 powerssytem after tar ing the files when i try to run ggsci i get below errors
Could not load program ggsci:
Symbol resolution failed for ggsci because:
Symbol _GetCatName__FiPCc (number 159) is not exported from dependent
module /usr/lib/libC.a[ansi_64.o].
Symbol __ct__Q2_3std8_LocinfoFPCci (number 184) is not exported from dependent
module /usr/lib/libC.a[ansi_64.o].
Examine .loader section symbols with the ‘dump -Tv’ command.
please let me know solutions if you have come across this kind of problem
Hello sk,
Sorry for late reply. Can you give me exact version of installed GoldenGate.
It looks you have installed version for AIX 5.2 instead version for 5.3/6.1
There are two different version on oracle for Oracle GoldenGate V10.4.0.x for Oracle.
Thanks,
Gleb
Thanks for the great post. I am new to GG, I am trying to figure if we can setup DDL and DML replication from Sybase to Oracle. I could not find information on this, please share some insight on this.
Hi Gleb,
I am getting the below error in the extract part of golden gate.
Using the following key columns for source table DMS.STG_CHANNEL_BANK_DETAILS: CHANNEL_PROFILE_ID.
2010-07-28 19:46:10 GGS INFO 224 Rolling over remote file ./dirdat/dm006026.
2010-07-28 19:47:34 GGS INFO 224 Rolling over remote file ./dirdat/dm006027.
What can be the exact reason of this error.
Regards
Manish
Hi Manish,
I don’t see a error. It is informing message about a new trail file opened by your extract process.
It is only for tracing extract activity and doesn’t signal about any error.
–Gleb
Hi, I am getting these messages on file “ggserr.log”. These transactions are related to distributed transactions when the source database is a RAC. Do you know any workaround ? Thank you.
2010-09-30 10:27:33 GGS WARNING 220 Oracle GoldenGate Capture for Oracle, ext1.prm: Record with class# 1960, slt# 0, at seqno 21906, rba 1441055760 SCN
1401.4250826332 (6021500008028) has secondary transaction ID that is duplicate of existing open uncommitted transaction.
2010-09-30 10:27:33 GGS WARNING 220 Oracle GoldenGate Capture for Oracle, ext1.prm: [Thread #2] Purging transaction (transaction id: 972.0.787746, star
t time: 2010-09-30 10:27:30, start seqno: 21906, start RBA: 1436612112).
2010-09-30 10:27:33 GGS WARNING 220 Oracle GoldenGate Capture for Oracle, ext1.prm: Updating I/O checkpoint after purging orphaned transactions on thre
ad 2 with current position (Seq#: 21906, RBA: 1441058444).
Hi,
I had installed GGS on Linux server and while logging GGSCI following message or error displayed:-
[[email protected] ggs1111]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
Hi,
It looks your GoldenGate cannot find the oracle libraries from oracle 11g.
1 way:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
2-nd way:
use ld linker
add file oracle11.conf to /etc/ld.so.conf.d/:
touch /etc/ld.so.conf.d/oracle11.conf
echo $ORACLE_HOME/lib >>/etc/ld.so.conf.d/oracle11.conf
run linker:
ld
Regards,
Gleb
Dear Sir:
Is it possible to use GG between MS-SQL Server and Oracle? Every tutorial / website is speaking of same database though GG is supposed to be heterogenous (having different installers). Please let me know or answer to my email so that I can check your site.
Yes,
It should be possible but you have use different version oracle GoldenGate for Oracle and MS SQL server.
As example:
Create extract and datapump on oracle side and set up remote trail on MS SQL server side.
On MS SQL side replicat GoldenGate for MS SQL.
Also here is from GG documentation:
“Oracle GoldenGate enables the exchange and manipulation of data at the transaction level
among multiple, heterogeneous platforms across the enterprise1.”
The key word is “heterogeneous”
Cheers,
Gleb
Hi,In “–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.*” , comma should be at last
Hi
Yes it is true. The semicolon should be moved to end of the file.
Here is what was supposed to be in the end of configuration:
–DDL support
ddl include mapped objname sender.*
–DML
table sender.*;
–Gleb
SQL> @$GGATE/role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:Wrote file role_setup_set.txt
BEGIN
*
ERROR at line 1:
ORA-20783:
GoldenGate Marker setup:
*** Cannot find user
*** Please enter existing user name.
ORA-06512: at line 10
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
*******************************************
my .GLOBAL file looks like
GGSCHEMA ggate
MARKERTABLE GGS_MARKER
DDLTABLE GGS_DDL_HIST
~
~
~
Arpit Sinha
[email protected]
Hi Arpit,
Have you created the user ggate before run the script?
You have to create the user ggate and then put the username when you will be asked:
Example:
QL> @$GGATE/role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:GGATE
Cheers,
Gleb
SQL> alter database add supplemental log data;
When I’m trying to add supplemental logging, it’s taking for ever to complete. Are there any other parameters which I need to consider before adding this.
Thanks
No issues, I did that in mount mode and worked.
Hi Reddy,
Good to know that problem has solved.
I would advice on next time to have a look what session was blocking yours.
—
Gleb
Hi Gleb, Thanks for your response.
I’m stuck at STEP 5 in the process. I created the link in step 2 as mentioned and “ggsci” worked perfectly but at step 5 when I’m trying to invoke ggsci i’m getting this error:
ld.so.1: ggsci: fatal: libclntsh.so.10.1: open failed: No such file or directory
Killed
Please advise. My Oracle DB version is 11.2.0 and OS is Solaris 10 SPARC
Thanks
REddy
Hi Reddy,
It looks your GoldenGate cannot find oracle lib libclntsh.so. Please check existence of the lib:
ls -l $ORACLE_HOME/lib/libclntsh.so.10.1
It should be on the place. Actually it is a link to the lib $ORACLE_HOME/lib/libclntsh.so
If you have the lib but still geting the error you have to add the path $ORACLE_HOME/lib to the environment variable LD_LIBRARY_PATH.
Try to run before starting ggci:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
Also I would like to mention that we have GoldenGate 10 and 11 versions for oracle 11g which doesn’t require creation any addition links during installation.
—
Gleb
Perfect!! Thanks it worked.
-Reddy
One more issue starting MANAGER at source side. If I say start manager it’s starting manager but in the info all status is showing as stopped:
GGSCI (devsx030) 29> start manager
Manager started.
GGSCI (devsx030) 30> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 00:23:10
REPLICAT STOPPED REP1 00:00:00 00:04:01
Hi Reddy,
You can find manager log in the dirrpt directory:
/dirrpt/MGR.rpt
Can you please post error here?
—
Gleb
Thanks for the reply, I didn’t change any param for code params, I just copied and added this:
PORT 7809***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.1.1.0.0 Build 078
Solaris, sparc, 64bit (optimized), Oracle 10 on Jul 28 2010 13:23:00
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-12-20 14:52:30
***********************************************************************
Operating System Version:
SunOS
Version Generic_141444-09, Release 5.10
Node: devsx030
Machine: sun4u
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 24908
Parameters…
PORT 7809Source Context :
SourceModule : [mgr.main]
SourceID : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34085]/perforce/src/app/mgr/mgr.c]
SourceFunction : [param_file_args]
SourceLine : [2854]
2010-12-20 14:52:30 ERROR OGG-00303 Unrecognized parameter (
PORT).2010-12-20 14:52:30 ERROR OGG-01668 PROCESS ABENDING.
I think I need to change the port number, I thought 7809 will be the standard port. I’ll try with the port which oracle listener has registered.
Thanks
Bharath
What should be added here?? Am I following right in step 5:
GGSCI (devsx030) 1> edit params mgr
“/opt/oracle/product/11.2.0/GoldenGate/dirprm/mgr.prm” 3 lines, 27 characters
PORT 1521What should I replace for
Thanks
Reddy
“
"Nevermind. I got it fixed. Just Added PORT 1522 and started the manager it worked. Sorry to bother you with too many emails. Thanks for your help.
-Reddy
hi Reddy,
I see you fixed the error. The manager process should not use same port as listener. It is different process and it is listening its own port. Yes you can use port 1522 if your listener doesn’t use it.
I prefer to use default port 7809.
The parameter should be without teg
--
Gleb
Hi Gleb,
When I’m trying to dblogin from destination db it’s not letting me to login.
GGSCI (devsx032) 1> dblogin userid ggate
Password:
ERROR: Failed to open data source for user GGATE.
I ran the role_setup.sql with the same user and it worked but this is failing here. Please advice.
Thanks
Reddy
Hi Reddy,
The script role_setup.sql should be run from user with dba privilege. I ran it from user sys. User cannot grant privileges to itself. I suggest to check environment variable ORACLE_SID for session and repeat the scripts.
—
Gleb
I’m sorry I meant to say, I ran that script as sys only and when it asked to enter GG schmea name ten I entered ‘ggate’ and it worked perfectly. When I’m trying to do dblogin it’s failing.
GGSCI (devsx032) 1> dblogin userid ggate
Password:
ERROR: Failed to open data source for user GGATE.
I tested the ggate user conenction to db from sqlplus and it had no issues.
Thanks
Reddy
Hi Reddy,
Just to confirm check connection using sqlplus.
From same command line where you start ggsci run please:
sqlplus ggate/
You have to connect to the database if all setting are proper.
If you get a error please check your ORACLE_SID:
env | grep ORACLE_SID
It should show you proper value for your database.
The next step should be checking privileges for user ggate in the database.
Check the privilege connect.
—
Gleb
I just ran all the scripts which you mentioned in the dest db also and it worked now.
-Reddy
I’m trying to start the extract process, but it’s having some issues. below is from logfile:
***********************************************************************
** Running with the following parameters **
***********************************************************************
–extract group–
extract ext1
–connection to database–
userid ggate, password *****
–hostname and port for trail–
rmthost devsx032, mgrport 1522
–path and name for trail–
rmttrail /opt/oracle/product/11.2.0/GoldenGate/dirdat/lt
–DDL support
ddl include mapped objname sender.*;
–DML
table sender.*
Source Context :
SourceModule : [ggapp.parmscn]
SourceID : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.
0_078_[34085]/perforce/src/gglib/ggapp/parmscn.c]
SourceFunction : [ci_get_next_cmd]
SourceLine : [1785]
2010-12-21 11:27:29 ERROR OGG-00268 Parameter unterminated.
2010-12-21 11:27:29 ERROR OGG-01668 PROCESS ABENDING.
2010-12-21 11:27:29 ERROR OGG-01668 PROCESS ABENDING.
Hi Reddy,
Please put “;” to the end of configuration file for extract.
It should be :
–DDL support
ddl include mapped objname sender.*
–DML
table sender.*;
Thanks I’ll try that.
Hi,
I have high chkpt at Replicat. when i check ggserr.log, I Found Warning about “Connection reset by peer”, below the capture.
===================================
–> 2011-01-04 16:30:26 GGS WARNING 150 Oracle GoldenGate Delivery for Oracle, RC07.prm: Connection reset by peer.
–> 2011-01-07 10:01:41 GGS WARNING 150 Oracle GoldenGate Delivery for Oracle, RC07.prm: Connection reset by peer.
2011-01-07 10:51:36 GGS WARNING 150 Oracle GoldenGate Delivery for Oracle, RC07.prm: Connection reset by peer.
=================================
Pls advise,,
Thanks,,
Hi Ian,
I would check network between replicat and destination database (in case replicat and dest db are on different boxes.
Also I see the warning is not so often.
Do you have any problem from it?
Is your replication working properly?
–Gleb
HI Gleb,
in Step 3, dblogin userid ggate
This user ggate is the user in souce db ?
Do we need to put tns entry of souce db in target tns file ? or else do we need to create gate user in target too ?
Hi Riaz,
I have user ggate on both side because it keeps tables for ddl replication and checkpoint table.
We need the tns entry for the source db on destination side if you will put extract to destination side.
In our example replication extract process is on source side and we don’t need tns entry for the source on destination side.
—
Gleb
I am looking out for GG Professionals.
Hello,
We have GG Professionals in the Pythian.
What do you need exactly?
–Gleb
Hi Otochkin,
I fallowed the steps provided by you and i am successful in doing the replication of create table, insert , update , delete on tables in to source to Target. But when i drop the table in the source , table is not getting dropped in the target.
Both source and Target database are of 10g 10.2.0.5.0
Can you please let me know what is the reason behind this.
Thanks and Regards,
Satish.G.S
Hi Satish,
It would be great if you posted your replicat and extract parameters files here for analysis.
Also the reason can be found in replicat log. We can check records and found what had happened.
–Gleb
I have one more problem. If i create a table without primary key in the source it is getting replicated to target.But if i create a table with primary key it is not getting replicated to target.
Thanks and Regards,
Satish
Hi Satish,
Please put your replicat and extract parameters and we will try to find out the reason.
Also it would be great to see replicat log.
–Gleb
Hi Gleb,
I am very new to golden gate and I am trying your example as it is –
I am getting this error and my Extracter in Abending with following error –
2011-03-09 02:38:44 ERROR OGG-01496 Failed to open target trail file /home/oracle/app/oracle/product/gg/dirdat/lt000000, at RBA 2504.
2011-03-09 02:38:44 ERROR OGG-01668 PROCESS ABENDING.
My Ext param file on RACLAB01 is
–extract group–
extract ext1
–connection to database–
userid ggate, password qwerty
–hostname and port for trail–
rmthost raclab02, mgrport 7809
–path and name for trail–
rmttrail /home/oracle/app/oracle/product/gg/dirdat/lt
–DDL support
ddl include mapped objname sender.*;
–DML
table sender.*;
Just wanted to know, how from my source host, it will able to write to destination host? via port mentioned 7809, via tns entry? In case I want to use my port 1521, on which listener is running, then how can I use?
Thanks
Ankur
This may be a basic question…I have a two node RAC 11GR2 database and I am trying to set up the replication to the non rac database…we use ASM…
When I start the extract I get the following error
2011-01-25 09:19:09 WARNING OGG-01423 No valid default archive log destination
directory found for thread 1.
2011-01-25 09:19:10 WARNING OGG-01423 No valid default archive log destination
directory found for thread 2.
Where to configure this parameters…
Your input will be highly appreciated..Jonah
Hi Johan,
What version of Golden Gate do you use?
You have to define ASM instance in listener and make sure the listener ready to handle connection to ASM.
Add to listener ora something addition sid for ASM:
(SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/11.1.0/asm)
(SID_NAME = +ASM1)
–Gleb
Thanks Gleb
Appreciated…we are in the process of implementing GG…The source and target are are out of sysnc…extract is spopped and apended…
Does any one experienced this before? your feed back will be greatly appreciated…
Excellent post, Keep up good work
Thanks
Adarsh
Hi Gleb,
I am very new to golden gate and I am trying your example as it is –
I am getting this error and my Extracter in Abending with following error –
2011-03-09 02:38:44 ERROR OGG-01496 Failed to open target trail file /home/oracle/app/oracle/product/gg/dirdat/lt000000, at RBA 2504.
2011-03-09 02:38:44 ERROR OGG-01668 PROCESS ABENDING.
My Ext param file on RACLAB01 is
–extract group–
extract ext1
–connection to database–
userid ggate, password qwerty
–hostname and port for trail–
rmthost raclab02, mgrport 7809
–path and name for trail–
rmttrail /home/oracle/app/oracle/product/gg/dirdat/lt
–DDL support
ddl include mapped objname sender.*;
–DML
table sender.*;
Just wanted to know, how from my source host, it will able to write to destination host? via port mentioned 7809, via tns entry? In case I want to use my port 1521, on which listener is running, then how can I use?
Thanks
Ankur
The GoldenGate works through own ports defined in configuration for GoldenGate manager.
In our example it was default port 7809.
You have to start GoldenGate managers on both side.
You can define your own ports for the managers but the ports should be different from listener port.
—
Gleb
Gleb,
Any idea what this error may be? I am getting the same error on the extract as well as the replicat side.
ERROR OGG-00662 Oracle GoldenGate Capture for Oracle, EXT1.prm: OCI Error allocating error handle (status = -1).
I am trying OGG for the first time using our tutorial.
What version of GoldenGate and database do you use?
It looks your OCI was not initialized properly.
Did you put proper path for your oracle software?
Also I have an error in configuration for extract, semicolon should be on very end of file.
Please change:
–DDL support
ddl include mapped objname sender.*;
–DML
table sender.*
to:
–DDL support
ddl include mapped objname sender.*
–DML
table sender.*;
—
Gleb
Gleb,
Sorry for the late reply.
I solved this problem. I had both the Oracle database 10g and Oracle database client installed on the test machine. I uninstalled the client and OGG worked as expected.
I am not sure why OGG has a problem when both the db and client are installed.
I guess the problem could come from discrepancy between version for client shared libraries and database software client shared libraries. I think particulary problem was in OCI version and functionality.
In Linux I would recommend to check environment LD_LIBRARY_PATH and PATH variables on Windows.
—
Gleb
I am getting following error when I put null in insert statement
2011-03-31 18:30:45 ERROR OGG-01148 programming error, data type not supported for column TXID in table advoss.tblaudittrail.
I am replicating MySQL 5.5.9 to Oracle 11g rel2
Hi Irfan,
What datatype do you have in the column TXID in table advoss.tblaudittrail?
—
Gleb
at mysql
`TXID` bigint(20) unsigned DEFAULT NULL,
at Oracle
“TXID” NUMBER(24,0),
Hi,
I would try GoldenGate 11 since it is supporting BIGINT for MySQL. Have a look difference between 10 and 11 versions.
Here is supported data types for the GoldenGate 10:
Supported data types
? CHAR
? VARCHAR
? INT
? DECIMAL
? FLOAT
? DOUBLE
? TIME
? DATETIME
? DATE
? TIMESTAMP
? BINARY
? TINYTEXT
? TINYLOB
Here is supported data types for GG 11:
CHAR
VARCHAR
INT
TINYINT
SMALL INT
MEDIUM INT
BIG INT
DECIMAL
FLOAT
DOUBLE
DATE
TIME
YEAR
DATETIME
TIMESTAMP
BINARY
VARBINARY
TEXT
TINYTEXT
MEDIUMTEXT
LONGTEXT
BLOB
TINYBLOB
MEDIUMBLOB
LONGBLOB
ENUM
BIT(M)
—
Gleb
Dear Gleb,
I can insert normal data via replication, it only raises error when i try to insert null value in this column.
I would really be thankful of you for helping me on this.
What goldengate version do you run?
I would check also constraints on the table in oracle database.
It would be great to get more information about the issue:
table structure on mysql and oracle,
insert statement.
—
Gleb
***********************************************************************
Oracle GoldenGate Capture for MySQL
Version 11.1.1.0.3 Build 001
Linux, x64, 64bit (optimized), MySQL Enterprise
==========================================
Version of Installed MySQL found in OGG
————————————–
Database Version:
MySQL
Server Version: 5.5.9-log
Client Version: 6.0.0
Host Connection: Localhost via UNIX socket
Protocol Version: 10
=======================================
table structure in MySQL
—————————————
CREATE TABLE `tblaudittrail` (
`AuditTrailID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`LoggedInUser` varchar(250) NOT NULL COMMENT ‘MySql logged in user who performed delete or update on a table. It contains the IP as well’,
`Action` enum(‘DELETE’,’UPDATE’) NOT NULL DEFAULT ‘DELETE’,
`TableName` varchar(128) NOT NULL COMMENT ‘Table name whose row is deleted’,
`TablePrimaryKeyValue` varchar(250) NOT NULL COMMENT ‘Primary key value of the row of the table that is deleted’,
`InsertionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`TXID` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`AuditTrailID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Used for Audit of tables’
======================================
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.0.0 Build 078
Linux, x86, 32bit (optimized), Oracle
========================================
Version of Installed Oracle Database found in OGG
—————————————
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
===========================================
table structure for oracle
—————————————–
CREATE TABLE “ADVOSS”.”TBLAUDITTRAIL”
( “AUDITTRAILID” NUMBER(10,0) NOT NULL ENABLE,
“LOGGEDINUSER” VARCHAR2(250 CHAR) NOT NULL ENABLE,
“ACTION” VARCHAR2(4000 CHAR) DEFAULT ‘DELETE’ NOT NULL ENABLE,
“TABLENAME” VARCHAR2(128 CHAR) NOT NULL ENABLE,
“TABLEPRIMARYKEYVALUE” VARCHAR2(250 CHAR) NOT NULL ENABLE,
“INSERTIONTIME” DATE DEFAULT SYSDATE NOT NULL ENABLE,
“TXID” NUMBER(24,0),
CONSTRAINT “PRIMARY” PRIMARY KEY (“AUDITTRAILID”)
)
=======================================
I can insert following two insert statements without any issue
—————————————-
INSERT INTO `tblaudittrail` VALUES (1,’MIR3′,’UPDATE’,’audittrail’,’22’,’2011-03-30 13:29:20′,9);
INSERT INTO `tblaudittrail` VALUES (2,’MIR2′,’UPDATE’,’audittrail’,’23’,’2011-03-29 06:17:20′,”);
=======================================
OGG extract fails when i execute following insert statement
—————————————-
INSERT INTO `tblaudittrail` VALUES (3,’mir3′,’UPDATE’,’audittrail222′,’25’,’2011-03-29 06:19:20′,NULL);
=====================================
error found in OGG extract report file
—————————————
Using the following key columns for source table advoss.tblaudittrail: AuditTrailID.
Source Context :
SourceModule : [ggvam.rec]
SourceID : [/mnt/ecloud/workspace/Build_OpenSys_r11.1.1.0.3_001_[36467]/perforce/src/gglib/ggvam/cvamrec.cpp]
SourceFunction : [mapToInternal]
SourceLine : [2708]
ThreadBacktrace : [10] elements
: [/u01/app/oracle/ggs/extract(CMessageContext::AddThreadContext()+0x26) [0x7c5666]]
: [/u01/app/oracle/ggs/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, …)+0x7b2) [0x7bc102]]
: [/u01/app/oracle/ggs/extract(_MSG_ERR_MAP_NULL_FIELD(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x92) [0x767362]]
: [/u01/app/oracle/ggs/extract(com_goldengate_vam::CVamRec::mapToInternal(file_def const*, com_goldengate_vam::CVamCol*, char**)+0xc5c) [0x70e26c]]
: [/u01/app/oracle/ggs/extract(com_goldengate_vam::CVamRec::outputRow(file_def const*, char**)+0x3b3) [0x70ed93]]
: [/u01/app/oracle/ggs/extract(com_goldengate_vam::CVamFmt::formatRecord(file_def const*, unsigned int, char*, int*)+0x229) [0x6e3a39]]
: [/u01/app/oracle/ggs/extract [0x5993d6]]
: [/u01/app/oracle/ggs/extract(main+0xff0) [0x59aea0]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x351d01d994]]
: [/u01/app/oracle/ggs/extract(__gxx_personality_v0+0x19a) [0x51086a]]
2011-04-07 14:43:25 ERROR OGG-01148 programming error, data type not supported for column TXID in table advoss.tblaudittrail.
Hi Irfan,
I think it is error for oracle support.
I don’t have a solution for it.
Maybe some addition information could be found using strace but I would open SR for oracle support about the issue.
In first I don’t see version MySQL 5.5.9 in certification Matrix for the GoldenGate.
It looks some features are still working incorrectly.
—
Gleb
hi dear Gleb,
sorry for late response,
I have tested same process with mysql 5.1.54 and golden gate version remains 11
but I am getting same error. that means this error belongs golden gate configuration issue or may be it is a bug of goldengate 11.
can you give me you point of view in this regard.
Dear Gleb,
In addition of my last comments, can you little describe the following
1- what is add trandata? is it supported by MySQL?
2- if extract fails due to bad record, how to skip that error to continue replication
3- if replicat fails due to bad record, how to skip that error to continue replication
it is really very nice to have support from your side to help us in this regard.
I am able to diagnose what is cuasing the problem
unsigned flag was the culprit of this error
I am able to insert null after removing unsigned flag.
thank you very much for your kind support.
Gleb,
I have progressed in my testing and am facing an interesting problem. I am trying out an app migration from MS SQL and Oracle. This app uses 100s of tables. The only difference is that in Oracle there are extra columns but only towards the end. The sequence of columns remains the same.
When I ran an Extract process with SOURCEISTABLE, it failed as it is unable to handle LOBs. I therefore dumped data to the destination using the “Loading data from file to Replicat” technique.
However, because of the number of columns mismatch, Replicat does not merge the data.
I am aware of SOURCEDEFS but doing the same for a number of tables is time consuming.
Is there an easier method?
Hi Sanjay,
Did you try the COLMATCH parameter.
I am not absolutely sure it is exactly it what you are looking for.
But I would try to use it.
In any way you have to point to GG what differences you have.
Did you try to automate creating file for SOURCEDEFS ?
—
Gleb
Hi Gleb,
Thanks for the steps.
What is the changes required in the above steps in case of ASM?
Thanks
Bijitesh
Hi Bijitesh,
You have to set up parameters to connect to the ASM in case Goldengate 10
ASMUSER [email protected],ASMPASSWORD
and set up listener for asm instance:
SID_LIST_LISTENER_DARAN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /rdbms/oracle/ora1012r/64)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /rdbms/oracle/ora1012r/64)
(SID_NAME = +ASM1)
)
)
—
Gleb
Hi Gleb,
please find the below steps.It is failed with the error-2011-04-06 13:59:59 ERROR OGG-00519 Oracle GoldenGate Delivery for Oracle, rep1.prm: Fatal error executing DDL replication: error [Error code [1031], OR
A-01031: insufficient privileges, SQL create table “RECEIVER”.”TEST_TAB_1″ (id number,rnd_str varchar2(12)) /* GOLDENGATE_DDL_REPLICATION */], no error hand
ler present.Can you please check?
@source
GGSCI (dbatest01) 1> create subdirs
[email protected](DB01):/u01/oracle/goldengate# mkdir discard
@destination
GGSCI (dbatest02) 1> create subdirs
[email protected](DB02):/u01/oracle/goldengate# mkdir discard
@source
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter database add supplemental log data;
SQL> alter system set recyclebin=off scope=spfile;
bounce
SQL> create user ggate identified by qwerty default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;
SQL> grant select any dictionary to ggate;
SQL> @marker_setup.sql(ggate)
SQL> @ddl_setup.sql(ggate,INITIALSETUP)
SQL> @role_setup.sql(ggate)
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql
@source
SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to sender;
@destination
SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to receiver;
SQL> create user ggate identified by qwerty default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;
SQL> grant select any dictionary to ggate;
@source
GGSCI (dbatest01) 1> edit params mgr
PORT 7808
GGSCI (dbatest01) 2> start mgr
@destination
GGSCI (dbatest02) 1> edit params mgr
PORT 7809
GGSCI (dbatest02) 2> start mgr
@source
GGSCI (dbatest01) 3> add extract ext1, tranlog, begin now
GGSCI (dbatest01) 4> add exttrail /u01/oracle/goldengate/dirdat/lt, extract ext1
GGSCI (dbatest01) 5> edit params ext1
–extract group–
extract ext1
–connection to database–
userid ggate, password qwerty
tranlogoptions asmuser [email protected]+ASM,asmpassword gpdba123
–hostname and port for trail–
rmthost 10.10.18.97, mgrport 7809
–path and name for trail–
rmttrail /u01/oracle/goldengate/dirdat/lt
–DDL support
ddl include mapped objname sender.*
–DML
table sender.*;
GGSCI (dbatest01) 7> start EXTRACT EXT1
@destination
GGSCI (dbatest02) 3> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
GGSCI (dbatest02) 5> DBLOGIN USERID ggate, PASSWORD ggate sysdba
GGSCI (dbatest02) 6> add checkpointtable ggate.checkpoint
GGSCI (dbatest02) 7> add replicat rep1, exttrail /u01/oracle/goldengate/dirdat/lt,checkpointtable ggate.checkpoint
GGSCI (dbatest02) 8> edit params rep1
–Replicat group —
replicat rep1
–source and target definitions
ASSUMETARGETDEFS
–target database login —
userid ggate, password qwerty
–file for dicarded transaction —
discardfile /u01/oracle/goldengate/discard/rep1_discard.txt, append, megabytes 10
–ddl support
DDL
–Specify table mapping —
map sender.*, target receiver.*;
GGSCI (dbatest02) 10> start REPLICAT REP1
@source
GGSCI (dbatest01) 10> info all
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:08
@destination
GGSCI (dbatest02) 12> info all
MANAGER RUNNING
REPLICAT ABENDED REP1 00:00:00 00:00:03
——checking
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;
SQL> select * from receiver.test_tab_1;
select * from receiver.test_tab_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
@destination
GGSCI (dbatest02) 12> info all
MANAGER RUNNING
REPLICAT ABENDED REP1 00:00:00 00:00:03
thanks
bijitesh
I didn’t hit such problem in my replication.
Where do you run your replicat process?
I assume it is running on the destination server. Can you provide privileges granted to the ggate user on the destination database?
I would advise to run
SQL> @$GGATE/marker_setup.sql
SQL> @$GGATE/ddl_setup.sql
SQL> @$GGATE/role_setup.sql
on destination.
—
Gleb
Hi Gleb,
Apologies for the late reply but missed seeing your answer in the long thread.
I have still not made progress on the migration. And COLMATCH does not serve my purpose as my column names remain the same. The difference is that the newer application version has extra columns.
I did not understand how to automate creating the SOURCEDEFS for the tables that have different no of columns. Could you please provide an example?
Also, is it possible to run OGG in a test run mode and understand the tables that will fail to get migrated correctly? I have been suppressing the error to record the table names that are failing.
Thank you for the help you have provided.
Thanks for great job , please keep it up.
Hello Gleb,
Environment is as below:
RAC 2 nodes
Oracle RAC 11gr2
ASM
Enterprise Linux Server release 5.5 (Carthage)
I am new to GG and I need to install and setup Oracle GoldenGate 11g Release 1 (11.1.1).
Is there any such self-explainatory blog available for my requirement?
can anyone give me full steps from scratch ?
Confusions:
1.Do we need to install GG on all nodes?
2.db_name and instance name can be different on source and destination ?
3.Do we need to set any specific parameters in pfile/spfile for GG on source and destination?
4.any specific entries in listener.ora and tnsnames.ora for connectivity between source and destination?
5.Do we to configure anything on ASM for GG installation?
responses will be highly appreciated.
Reagrds,
Hi mkmansoori,
>1.Do we need to install GG on all nodes?
GG works on one node on one time. You must stop it on one node before start on another.
I would advise to put GG binares to shared FS.
You can potentially create resource in clusterware which will handle start/stop for GG manager.
>2.db_name and instance name can be different on source and destination ?
it doesn’t matter for GG.
>3.Do we need to set any specific parameters in pfile/spfile for GG on source and destination?
there is no special parameters for RAC database
>4.any specific entries in listener.ora and tnsnames.ora for connectivity between source and destination?
No specific entries except listener for ASM instances
>5.Do we to configure anything on ASM for GG installation?
Add listener for ASM.
Also you have to use THREADOPTIONS parameter in case RAC source.
–Gleb
Hi Gleb,
I’ve very interesting error. My etract abends with follow logs:
2011-04-08 16:59:09 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ext.prm: error 2 (No such file or directory) opening redo log +ARCHLOG/1_1567_716136834.dbf for sequence 1567Not able to establish initial position for sequence 1567, rba 747926451
While I does not have archivelog with 1567 number in mentioned ASM directory. There are number less than 1567 there, i.e. 1567 archive has not been created yet.
I have tried to alter extract like
alter etract ext, EXTSEQNO 1566
and got some error??
And one more question..I grant golgengate user (used in extract parameter ile) sysdba and my asm instance password file is SHARED. So, I suppose to not have problems for reading from ASM?? I did not use additional user for using ASM in extract parameter ile.
Dear Gulmira Lomanova ,
correct me if I am wrong, I think you have done some kind changes in database configuration after the creation of extract. if yes, then delete your extract and reconfigure all settings for extract. I hope this will help you.
Dear Gleb,
I am having trouble with setting up replication with mysql and the thing which is cuasing problem is ‘unsigned’ flag .
If I remove this flag it is not giving me trouble.
I want to keep unsigned flag. Is there any solution for this problem?
I have a mysql replication slave
and I have setup ogg on that mysql slave
binlog is enabled
extract & replicat is running without any errors but no records are being extracted by goldengate, kindly help me in this
Hello Guys,
Environment is as below:
Primary site (source): This is production DB and up and running.
RAC 2 nodes
Oracle RAC 11gr2
ASM
Enterprise Linux Server release 5.5 (Carthage)
target Site (destination): Software installed and instance has been created.
Standalone single server – Non RAC
Oracle 11gr2
ASM
Enterprise Linux Server release 5.5 (Carthage)
Oracle GoldenGate 11g Release 1 (11.1.1)
I have installed and setup Oracle GoldenGate 11g Release 1 (11.1.1) But I am getting below error while starting extract:
2011-05-01 21:27:41 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): edit params ext1.
2011-05-01 21:29:11 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract ext1.
2011-05-01 21:29:11 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 172.20.9.x (START EXTRACT EXT1 ).
2011-05-01 21:29:11 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EXT1 starting.
2011-05-01 21:29:11 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, ext1.prm: EXTRACT EXT1 starting.
2011-05-01 21:29:11 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, ext1.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2011-05-01 21:29:12 ERROR OGG-00868 Oracle GoldenGate Capture for Oracle, ext1.prm: Attaching to ASM server asm: (12514) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
2011-05-01 21:29:12 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.
Cheers !!!
Any idea whether we need some change while using on delete cascade option on replicated tables.
What I’ve seen is replicat crashing with this option.
Thanks,
mgmt.
Hi mgmt,
Maybe some helpful information could be found in relicat log.
Did you check it?
—
Gleb
Hi,
Did you configured asm instance in listener?
According to your log the GG tried to connect to ASM instance but was not able to do it.
—
Gleb
Hi
In the example, what is “db2”, on the line
rmthost db2, mgrport 7809
I don’t know if is a host reference, alias from tnsnames or another thing.
Have an error to startup the ext1 service
–extract group–
extract ext1
SETENV (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)
Set environment variable (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
SETENV (ORACLE_SID = “diuca”)
Set environment variable (ORACLE_SID=diuca)
–connection to database–
userid ggate, password ******
–hostname and port for trail–
rmthost db2, mgrport 7809
Source Context :
SourceModule : [er.extrout]
SourceID : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34086]/perforce/src/app/er/extrout.c]
SourceFunction : [get_remote_host]
SourceLine : [3303]
ThreadBacktrace : [8] elements
: [/u01/app/oracle/product/gg/extract(CMessageContext::AddThreadContext()+0x26) [0x62ab66]]
: [/u01/app/oracle/product/gg/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int,
…)+0x7b2) [0x621602]]
: [/u01/app/oracle/product/gg/extract(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char c
onst*, CMessageFactory::MessageDisposition)+0x92) [0x5e1842]]
: [/u01/app/oracle/product/gg/extract(get_remote_host(char*)+0x696) [0x812bfe]]
: [/u01/app/oracle/product/gg/extract [0x51501b]]
: [/u01/app/oracle/product/gg/extract(main+0x121) [0x517cf1]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x357661d994]]
: [/u01/app/oracle/product/gg/extract(__gxx_personality_v0+0x1ea) [0x4e62aa]]
2011-05-17 15:09:07 ERROR OGG-00303 Could not establish host TCP/IP address.
2011-05-17 15:09:07 ERROR OGG-01668 PROCESS ABENDING
Thank’s in advance.
Leo.
Hi Leonel,
db2 is remote host on the line.
You have to define port and host where your remote GG process works.
As you can see in error manager cannot establish connection to the host.
Put hostname for your remote host.
–Gleb
Hi Gleb,
Yes I configured asm instance in listener.ora as below:
$ cat listener.ora
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = +ASM1)
(ORACLE_HOME = /u01/app/11.2.0/grid)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = +ASM2)
(ORACLE_HOME = /u01/app/11.2.0/grid)
)
)
$
$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxdata-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM2)
)
)
$
Plz suggest me ..
Cheers !!!!
asfd,
Hi asfd,
Have you checked services running on your listener?
Here is command:
lsnrctl service
Please try to connect using sqlplus and values from your parameter file for extract.
—
Gleb