Oracle GoldenGate Installation, Part 1

Posted in: Technical Track

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.

  1. Install linux x64 on your box. I used Linux Centos 5.3. The hostname for our source box will be db1.
  2. 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 oracle user 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
    
  3. 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
    
  4. 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:

    1. 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.
    2. 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
      
    3. 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
      
    4.  . . . and the path to the GG libraries to LD_LIBRARY_PATH:
      export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg
      
    5. Now we can start GG command line utility to make sure it works.
[oracle@db1 gg]$ ./ggsci
  1. 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>
    
  2. 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
  3. 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.

  1. Switch the database to archivelog mode:
    SQL> shutdown immediate
    SQL> startup mount
    SQL> alter database archivelog;
    SQL> alter database open;
    
  2. Enable minimal supplemental logging:
    SQL> alter database add supplemental log data;
  3. 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
    

     

  4. Create test schemas for replication. I will create a replication from schema sender to schema receiver (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.

  1. 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&gt; 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
    
  2. Create the extract group 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
    
  3. 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.*;
    
  4. Start extract and replicat:Source:
    GGSCI (db1) 14> start extract ext1
    

    Destination:

    GGSCI (db2) 15> start replicat rep1
    
  5. 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.

  1. 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;
    
  2. 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.

email

Interested in working with Gleb? Schedule a tech call.

About the Author

Regarded by his peers as an Oracle guru, Gleb is known for being able to resolve any problem related to Oracle. He loves the satisfaction of troubleshooting, and his colleagues even say that seeking Gleb’s advice regarding an issue is more efficient than looking it up. Gleb enjoys the variety of challenges he faces while working at Pythian, rather than working on the same thing every day. His areas of speciality include Oracle RAC, Exadata, RMAN, SQL tuning, high availability, storage, performance tuning, and many more. When he’s not working, running, or cycling, Gleb can be found reading.

268 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

Reply

… it is skipped.

– Steve B

Reply

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.

Reply

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.

Reply

That would be great!

Thanks.

Reply

I am checking lag using command line. But I think it would be great if you share you scripts.
Gleb

Reply

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.

Reply
Log Buffer #180: a Carnival of the Vanities for DBAs | The Pythian Blog
February 26, 2010 1:04 pm

[…] Pythian’s Gleb Otochkin begins a series on Oracle GoldenGate installation. […]

Reply

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

Reply

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,

Reply
Gleb Otochkin
March 2, 2010 10:04 am

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

Reply
Gleb Otochkin
March 4, 2010 11:47 am

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/

Reply

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.

Reply

Hi,
What kind of problem are you referring too? Working with ASM ?


Gleb

Reply

HI
does anyone have similar setup guide for goldengate on mssql db platform ?

help is appriciated
Regards

Reply
Gleb Otochkin
March 3, 2010 4:38 pm

I’ll try to do it in another post.

Reply

Thanks Gleb,
I appriciate your help, please send me link when you upload this info.
Kind Regards
Manish

Reply

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

Reply

How would you replicat entire database instead of a particular schema? Thank you.

Reply
Gleb Otochkin
March 3, 2010 4:39 pm

I have installed Golden Gate on windows. I’ll create post about installing it to windows and how replicate some tables.

Reply

Thats excellent!!

Reply

Eagerly waiting for installing on windows server.

Thanks in advance.

Reply

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

Reply

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 **
***********************************************************************

Reply
Gleb Otochkin
March 4, 2010 5:30 pm

Have you set setting for ODBC connection to use ANSI?

— Gleb

Reply

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.

Reply
Gleb Otochkin
March 10, 2010 9:45 am

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

Reply

Excellent, you are rocking!!!

Reply

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
—————————————

Reply

HI
please check your event viewer for specific description of error message. there could be several reason why Extract fail to start.

Manish

Reply
Gleb Otochkin
March 10, 2010 9:40 am

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

Reply
Blogroll Report 12/02/2009 – 19/02/2010 « Coskan’s Approach to Oracle
March 18, 2010 11:45 am

[…] 30-How to install Golden Gate for test purposes? Gleb Otochkin-Oracle GoldenGate Installation, Part 1 […]

Reply

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

Reply

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

Reply

Hi Andrew

did you get any solution for your “process abending” error ?

Reply

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.

Reply

hi Adriana,
What version GG are you using for SQL 2005?
and
What version of oracle do you use?

–Gleb

Reply

Hi
The version GG is 10.4 and of Oracle 10.2.

Thank you

Reply

Adriana,
Are you sure you have GG 10.4 for SQL Server and not a version for Oracle?

-Sean

Reply
siva prakash
May 6, 2010 7:15 pm

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.*;

Reply
Gleb Otochkin
May 16, 2010 11:42 am

You can find extract log in the directory:
/dirrpt
In you case the log name should be ext1.rpt

vi /dirrpt/ext1.rpt

–Gleb

Reply

look in the ggserr.log and the dirrpt/ext1.rpt

Reply
Fabio Sartori
May 12, 2010 12:54 pm

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 ?

Reply
Gleb Otochkin
May 16, 2010 11:36 am

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

Reply

Any progress ? I get stuck into this error , else~

Jay

Reply
Gleb Otochkin
July 19, 2010 2:07 pm

hi Jay,
Have you tried to create primary key for replicated tables?

–Gleb

Reply

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~

Reply
Gleb Otochkin
July 21, 2010 5:07 pm

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

Reply
Gleb Otochkin
May 16, 2010 11:38 am

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

Reply
Fabio Sartori
May 19, 2010 1:59 pm

The “Record not found” error on UPDATEs was fixed with the command “ADD TRANDATA SENDER.*” on source. Thanks, Gleb.

Reply

Hi,
I get this error:

GGS WARNING 150 TCP/IP error 10060

after create table for replica

Reply
Gleb Otochkin
May 20, 2010 9:54 am

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

Reply

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

Reply

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

Reply
Gleb Otochkin
June 8, 2010 8:26 am

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

Reply

hi again,
I could not find configure for active – active (bi-directional). do you have any idea for this configuration?

thank you

Reply
Gleb Otochkin
June 8, 2010 8:39 am

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

Reply

thank you so much for repling.

the checkpointtable name must be the same on both system or will be different?

Reply
Gleb Otochkin
June 9, 2010 4:00 pm

The checkpoint table will store different data for different system you have to create and define it for every system.

–Gleb

Reply

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

Reply
Gleb Otochkin
June 9, 2010 4:11 pm

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

Reply

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 :)

Reply
Gleb Otochkin
June 10, 2010 8:30 am

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

Reply

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

Reply
Gleb Otochkin
June 10, 2010 10:21 am

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

Reply

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

Reply
Gleb Otochkin
June 10, 2010 1:16 pm

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

Reply

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?

Reply
Gleb Otochkin
June 21, 2010 4:21 pm

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

Reply
Fabio Sartori
July 6, 2010 9:29 am

Hi, do you have any example of configuring an extract/replicat in ALO mode (archived log only mode) ? Thanks a lot.

Reply

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

Reply
Gleb Otochkin
July 16, 2010 2:38 pm

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

Reply

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

Reply
Gleb Otochkin
July 21, 2010 5:01 pm

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

Reply

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

Reply

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

Reply
Gleb Otochkin
August 4, 2010 2:51 pm

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

Reply

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?

Reply

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

Reply

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.

Reply

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

Reply

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

Reply

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

Reply

Hi Gleb,

I use EXTRACT to extract from the REDO/Archive Logs and PUMP to push to the remote server.

Regards
Manish

Reply

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

Reply

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.

Reply

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

Reply

Hi,
When target / source DB instance name are different, where do I have to config this issue?

Thanks

Reply

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

Reply

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?

Reply

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

Reply

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 .

Reply

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

Reply

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

Reply

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

Reply

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.

Reply
Manish Chakraborty
October 13, 2010 11:16 pm

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

Reply

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

Reply

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).

Reply

Hi,
I had installed GGS on Linux server and while logging GGSCI following message or error displayed:-
[erprod@online ggs1111]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

Reply

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

Reply

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.

Reply

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

Reply

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

Reply

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

Reply

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
arpitrhce@gmail.com

Reply

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

Reply

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

Reply

No issues, I did that in mount mode and worked.

Reply

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

Reply

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

Reply

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

Reply

Perfect!! Thanks it worked.

-Reddy

Reply

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

Reply

Hi Reddy,
You can find manager log in the dirrpt directory:
/dirrpt/MGR.rpt
Can you please post error here?


Gleb

Reply

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 7809

Source 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.

Reply

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

Reply

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 1521

What should I replace for

Thanks
Reddy

Reply

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

Reply

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

Reply

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

Reply

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

Reply

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

Reply

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

Reply

I just ran all the scripts which you mentioned in the dest db also and it worked now.

-Reddy

Reply

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.

Reply

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.*;

Reply

Thanks I’ll try that.

Reply

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,,

Reply

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

Reply

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 ?

Reply

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

Reply
Shiv Kumar Pandey
January 18, 2011 10:33 pm

I am looking out for GG Professionals.

Reply

Hello,
We have GG Professionals in the Pythian.
What do you need exactly?

–Gleb

Reply

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

Reply

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

Reply

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

Reply

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

Reply

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

Reply

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

Reply

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

Reply

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…

Reply

Excellent post, Keep up good work
Thanks
Adarsh

Reply

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

Reply
Gleb Otochkin
March 25, 2011 3:09 pm

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

Reply

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.

Reply
Gleb Otochkin
March 25, 2011 3:17 pm

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

Reply

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.

Reply
Gleb Otochkin
March 30, 2011 8:52 am

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

Reply
Irfan Rasheed
April 1, 2011 1:07 am

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

Reply
Gleb Otochkin
April 1, 2011 3:40 pm

Hi Irfan,
What datatype do you have in the column TXID in table advoss.tblaudittrail?


Gleb

Reply
Irfan Rasheed
April 3, 2011 1:19 pm

at mysql
`TXID` bigint(20) unsigned DEFAULT NULL,

at Oracle
“TXID” NUMBER(24,0),

Reply
Gleb Otochkin
April 5, 2011 11:42 am

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

Reply
Irfan Rasheed
April 5, 2011 11:39 pm

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.

Gleb Otochkin
April 6, 2011 12:55 pm

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

Irfan Rasheed
April 7, 2011 5:21 am

***********************************************************************
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.

Gleb Otochkin
April 7, 2011 4:12 pm

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

Irfan Rasheed
April 11, 2011 4:51 am

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.

Reply
Irfan Rasheed
April 11, 2011 2:40 pm

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.

Irfan Rasheed
April 15, 2011 3:54 am

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.

Sanjay Nayak
April 1, 2011 3:48 am

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?

Reply
Gleb Otochkin
April 1, 2011 3:58 pm

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

Reply

Hi Gleb,

Thanks for the steps.
What is the changes required in the above steps in case of ASM?

Thanks
Bijitesh

Reply
Gleb Otochkin
April 5, 2011 11:31 am

Hi Bijitesh,
You have to set up parameters to connect to the ASM in case Goldengate 10
ASMUSER SYS@,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

Reply

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
oracle@dbatest01(DB01):/u01/oracle/goldengate# mkdir discard

@destination
GGSCI (dbatest02) 1> create subdirs
oracle@dbatest02(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 sys@+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

Gleb Otochkin
April 6, 2011 1:46 pm

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.

Reply

Thanks for great job , please keep it up.

Reply

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,

Reply
Gleb Otochkin
May 3, 2011 4:09 pm

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

Reply
Gulmira Lomanova
April 9, 2011 5:36 pm

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.

Reply
Irfan Rasheed
April 12, 2011 2:39 am

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.

Reply

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?

Reply
Irfan Rasheed
May 3, 2011 3:37 am

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

Reply

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 !!!

Reply

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.

Reply
Gleb Otochkin
May 11, 2011 4:47 pm

Hi mgmt,
Maybe some helpful information could be found in relicat log.
Did you check it?


Gleb

Reply
Gleb Otochkin
May 11, 2011 4:45 pm

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

Reply
Leonel Sanhueza
May 17, 2011 2:56 pm

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.

Reply
Gleb Otochkin
May 18, 2011 4:55 pm

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

Reply

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,

Reply
Gleb Otochkin
May 25, 2011 12:43 pm

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

Reply

Hi Gleb,

Here is the listener status.

]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 15-MAY-2011 12:36:12

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 08-MAY-2011 17:41:59
Uptime 6 days 18 hr. 54 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/xxx_data2/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.x.4)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.x.40)(PORT=1521)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM2”, status READY, has 1 handler(s) for this service…
Service “+ASM2” has 1 instance(s).
Instance “+ASM2”, status UNKNOWN, has 1 handler(s) for this service…
Service “xxx1” has 1 instance(s).
Instance “xxx2”, status READY, has 1 handler(s) for this service…
Service “xxxXDB” has 1 instance(s).
Instance “xxx2”, status READY, has 1 handler(s) for this service…
The command completed successfully

Cheers !!

asfd,

Reply
Gleb Otochkin
May 30, 2011 11:15 am

Hi asfd,
Have you tried to connect to the ASM using sqlplus and tns alias from your parameter name for GoldenGate capture?
I mean connect from the box and user for GG manager.

Thanks,
Gleb

Reply

Hi Gleb,

I am able to connect ASM using OS authentication. But when tried to connect to the ASM using sqlplus and tns alias . I am getting below error.

]$ sqlplus sys@asm as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 12:36:50 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

I have set local_listener in ASM instance as as well.

I am totally lost as to what should I do ?

Regards,

asfd

Reply

Hi Afsd,

You need to create a tnsnames.ora entry with connect string ASM , and the service_name = +ASM.

Regards
Chandra

Reply
GoldenGate enters the Oracle database « Julian Dontcheff's Database Blog
May 31, 2011 4:02 pm

[…] Pythian’s Oracle GoldenGate Installation, Part 1 2. Pythian’s Oracle GoldenGate Installation, Part […]

Reply
Ravi Kasibhatla
June 12, 2011 1:18 pm

Hi,

I am trying to do a very BASIC GG based replication in the following environment:

Source:
AIX 6.1
Oracle 10g R2 10.2.0.4 – Non-ASM Database
SCOTT.*
GG user has been granted DBA previlege

Destination:
Solaris 10
Oracle 10g R2 10.2.0.4 – Non-ASM Database
SCOTT.*
GG user has been granted DBA previlege

– On the destination the manager and replicat are running successfully
– On the source the manager is running but the EXTRACT is not getting registered nor running
– While registering the EXTRACT GG is giving an error that it can’t be registered because some table or view is not is not found in the database.

What could be the cause?

Regards
Ravi Kasibhatla

Reply

Hi ,

Can any one provide me oracle golden gate implementation with microsoft sql server 2008

Thanks in advance,
Ekta

Reply

Thanks a lot. This is really simplify installing Golden Gate.

Reply

Hi Gleb,

I have a problem with OGG sybase replicate, show me a this message

Segmentation fault

In replicate process.

Regards,Roberto

Reply

can you give me a recommendation?

Regards, Roberto

Reply

I have configured GoldenGate with Oracle (non-rac) database on same machine.

Both Extract and Replicat are running fine. However if i create a table i do not see it appear in the other database (Replicat).

I also tried having same table with same structure in Source and Destination database. and then inserted values in Source database and commited it. Still no difference at Replicat side. Can you please help me understand what could be the issue?

below are the steps followed can you please correct if i have missed or needs modification?

1) created 2 databases a and b in same machine.
2) created ggate in database a and ggate1 in database b, with appropriate grants mentioned above in the post.
3) created dma66e user in DB a and dma66r user in DB b
4) below commands were executed (i have tried by executing only in a database and not in b, as well as in both databases (a and b))
shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter database add supplemental log data;
alter system set recyclebin=off scope=spfile;

5) Scripts were executed (i have tried by executing only in a database and not in b, as well as in both databases (a and b))
@$GGATE/marker_setup.sql
@$GGATE/ddl_setup.sql
@$GGATE/role_setup.sql
grant GGS_GGSUSER_ROLE to ggate;
@$GGATE/ddl_enable.sql

6) both managers were started in source and destination GG

from GGSCI Source
4) add extract extcdm, tranlog, begin now

5) add exttrail E:\oracle\app\product\GoldenGates\Extract\dirdat\lt, extract extcdm

6) EXTRACT cdmext
SETENV (ORACLE_HOME = “E:\oracle\app\product\11.2.0\dbhome_1”)
SETENV (ORACLE_SID = “CDMEXT”)
USERID ggate, PASSWORD ggate
EXTTRAIL E:\oracle\app\product\GGate\Extract\dirdat\lt
rmttrail E:\oracle\app\product\GGate\Extract\dirdat\lt
rmthost PCRAGHPW7, mgrport 7809
DISCARDFILE E:\oracle\app\product\GGate\Extract\discard\discard.txt, PURGE, MEGABYTES 100
DDL INCLUDE MAPPED OBJNAME “dma66e.*”
DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 60 MAXRETRIES 10, REPORT
TABLE dma66e.*;

from GGSCI Destination
7) add replicat CDMREP, exttrail E:\oracle\app\product\GoldenGates\Extract\dirdat\lt,checkpointtable ggate.checkpoint

8) REPLICAT CDMREP
SETENV (ORACLE_HOME = “E:\oracle\app\product\11.2.0\dbhome_1”)
SETENV (ORACLE_SID = “CDMREP”)
ASSUMETARGETDEFS
USERID ggate1, PASSWORD ggate1
DISCARDFILE E:\oracle\app\product\GGate\Rep\discard\discard.txt, append, megabytes 100
DDL INCLUDE MAPPED OBJNAME “dma66e.*”
MAP dma66e., TARGET dma66r.;

Reply

Hi Ravi,

Architecture:

Database: SYBASE 15.0 TO SYBASE 15.0
OGG: 11.1.1.1.2

the issue is when try start the replicate process

sybase@brmdsybv15:/discopaso/gg> ./replicat paramfile dirprm/repsa.prm

***********************************************************************
Oracle GoldenGate Delivery for Sybase
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Sybase 15_0 on Oct 5 2011 00:41:38

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

Starting at 2011-11-17 22:18:10
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Mon Dec 12 18:32:25 UTC 2005, Release 2.6.5-7.244-smp
Node: brmdsybv15
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 13653

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************
replicat REPSA
sourcedb BANREGIO@BRMDSYBV15, userid sa, password *******
handlecollisions
ASSUMETARGETDEFS
reportcount every 10000 records, rate
discardfile ./dirout/discardes.dsc , purge
MAP dbo.* , TARGET dbo.*;

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 512M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1G
CACHESIZEMAX (strict force to disk): 881M

Database Version:
Adaptive Server Enterprise
Version 15.0.3 (Build EBF 16555 ESD#1) – Production
Linux Intel Linux 2.6.9-42.ELsmp i686 ase1503 2680 32-bit FBO Thu Mar 5 04:50:21 2009

***********************************************************************
** Run Time Messages **
***********************************************************************

Opened trail file ./dirdat/sa000000 at 2011-11-17 22:18:11

Wildcard MAP resolved (entry dbo.*):
MAP dbo.SYDIATRA, TARGET dbo.SYDIATRA;
Using following columns in default map by name:
Dit_Numero, Dit_Consec, Dit_Fecha, Dit_Transa, Dit_Datos, Dit_Usuari,
Dit_Estaci, Dit_Modulo, Dit_Pantal, Dit_Progra, NumTransac,
Transaccio, Usuario, FechaSis, SucOrigen, SucDestino

Using the following key columns for target table dbo.SYDIATRA: Dit_Numero, Dit_Consec, Dit_Fecha, Dit_Transa, Dit_Usuari, Dit_Estaci, Dit_Modulo, Dit_Pantal, Dit_Progra, NumTransac, Transaccio, Usuario, FechaSis, SucOrigen, SucDestino.

Segmentation fault
sybase@brmdsybv15:/discopaso/gg>

When the try to replicate this table change the status to abend.

Regards, Roberto

Reply

Hi Ravi,
Can you please provide information from replicat log?
Also it would be great to make sure your extract and replicat were running.

Thanks,
Gleb

Reply

Hi Gleb,

you have seen east error before?

Segmentation fault.

Thanks,
Roberto

Reply

Hi Roberto,
Usually it appears when anybody try to run GoldenGate on wrong platform/version OS.
Can you provide OS version and GoldenGate version?

Cheers,
Gleb

Reply

Thanks Gleb,

Apology for my less knowledge on GoldenGate, can you please let me know where can i find the replicat logs? yes i have made sure both Extract and Replicat are running.

Regards,
Ravi

Reply

Hello Gleb,

Got it working, the one change i did is to use same trail location for Extract and Replicat and RmtTrail. and 2 different managers. but i guess this is not the correct way to get it working isnt it?

Also i do not see that if i create a table in Database A, it does not appear in Database B, is there a way to get this as well working? Also i am trying to have Active-Active situation such that i can hit any of the database i should have appropriate data. (But i know these are later steps and i am hurruying here, just wanted to get info if you already have on this.)

Regards,
Ravi

Reply

Hello Gleb,

I configured Active-Active way with Oracle and GoldenGate and it runs properly, however when i insert a value in Primary database, it replicates properly in Second database, but the catch is, again the insert that happened in Second Database, issues one more insert to Primary database, because of which i get exception as OGG-01296 and says error mapping Replicat.table with Extract.table. and the Replicat gets abended

Can you please help me overcome this situation?

Regards,
Ravi

Reply

Hi Ravi,
Do you have primary keys on the replicated tables?
Do you have supplemental logging (what columns/keys) enabled for the tables?

Thanks,
Gleb

Reply

Hello Gleb,

Yes we have primary keys on replicated tables.

I have not specifically created supplemental logging enabled for tables. could you please guide me how to do that?

Also few suggest to make use of below in the Extract, i tried this but now the replication itself has stopped (as i am not able to give the user name of replicat user here as it is not created here, if i do give user name of replicat user the extract does not start)

TRANLOGOPTIONS EXCLUDEUSER
DDLOPTIONS GETAPPLOPS IGNOREREPLICATES

Reply

Hi Ravi,
You can check supplemental logging for the table in the view dba_log_groups.
The simplest way to enable supplemental logging is to use dbms_capture_adm.prepare_table_instantiation procedure.

I am sorry I am bit lost about the second question.
What do you want to achieve?

Thanks,
Gleb

Reply

Thanks Gleb,

I am trying to get Active-Active situation with our GoldenGate.

Though basic thing works, only with DML operations we are stuck with data looping.

From Oracle Admin Guide for GoldenGate (E17341-01, Page 82) it says to make use of :

TRANLOGOPTIONS EXCLUDEUSER
DDLOPTIONS GETAPPLOPS IGNOREREPLICATES

Reply

Hello Gleb,

Thanks for all the help and this wonderful installation steps. Currently i am able to get Active-Active working with GG. The mistake i did was passing wrong user name. with the correct username with TranLOGOptions, it worked fine.

Still not done, lot of other schemas i would be trying this, so i am sure to trouble you more if i need help :) hope you dont mind.

Regards,
Ravi

Reply

Hello Gleb,

Back to trouble you :(

We have lot of sequences used in our application and hence we have it in our scripts.

I do not see GoldenGate replicating this, any idea on how to get the sequence thing working?

Regards,
Ravi

Reply

Hi Ravi,
Do you mean you have triggers on replicated tables which use sequences?
It is hard to advise without knowledge about your architecture and replicat logs.
Try to check firing properties for a trigger maybe it is the problem.

Thanks,
Gleb

Reply

Hello,
Could you pls give me an example of how i could join 2 source tables and replicate it into 1 target table.

Regards
-rrk

Reply

Hi,

After implementation of GG, to which db users are going to be connect, if all user connection are transfer ting to target db, does we need to remove the source db.

Thanks

Reply

I need advice about oracle goldengate 11.1.1 ,
We have 30 oracle 11gr2.0.2 standard edition databases in different sites and it is identical databases so we need implementing GoldenGate to have schema replication
between all databases please advice what is the best design to do that and have compatible with network failure so I have read about multi topology using in goldengate
Bi-directional peer-to-peer distribution and how many goldengate processes I need in every instance .

Reply

Hi,
Thank you for your article, it is really great.
I tried to carry out a replication on a single database on my localhost. I have two users:receiver and sender for my oracle 10g database.

I could not start the replicat.

First of all, for my case:
1) Do I need two mannagers and two gg installed on my PC?
2) For my extract parameters: rmthost is the same as my localhost hostname and for replicat the database I logged in, is the same database as my source database.

Under these conditions, GG can realize the replication?

Reply

Thank you so much. nice article. extract process was not running throwing Source Context :
SourceModule : [ggapp.parmscn]
SourceID : [../gglib/ggapp/parmscn.c]
SourceFunction : [ci_get_next_cmd]
SourceLine : [1789]

2012-03-10 20:04:56 ERROR OGG-00268 Parameter unterminated.

2012-03-10 20:04:56 ERROR OGG-01668 PROCESS ABENDING.
error on log file ext1.rpt

change ext1.prm content from table sender.* to table sender.*;(semicolon)
The above instructions work perfectly for Windows (single instance). Oracle11gr2, GG Version 11.1.1.1.2

Reply

Hello,

In my target setup i couldnot able to start replicat its saying following error.

GCCSI> edit pramas rep1

–Replicat group —

replicat rep1

–source and target definitions

ASSUMETARGETDEFS

–target database login —

userid receiver, password receiver1

–file for dicarded transaction —

discardfile D:OGGdiscardrep1_discard.txt, append, megabytes 10

–ddl support

DDL INCLUDE ALL

–Specify table mapping —

map sender.*, target receiver.*;

GGSCI (chethan-PC) 70> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:01:15

the GGSERR showing the following

2012-04-13 12:52:42 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, MGR.prm: Command received from GGSCI on host 192.168.1.109 (START REPLICAT REP1 ).
2012-04-13 12:52:42 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, MGR.prm: REPLICAT REP1 starting.
2012-04-13 12:52:43 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, REP1.prm: REPLICAT REP1 starting.
2012-04-13 12:52:44 ERROR OGG-00446 Oracle GoldenGate Delivery for Oracle, REP1.prm: SQL operation failed: Inserting into checkpoint table GGATE.CHECKPOINT, group REP1, SQL SQL Error 942: ORA-00942: table or view does not exist.
2012-04-13 12:52:44 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, REP1.prm: PROCESS ABENDING.
2012-04-13 12:58:15 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (chethan): edit params rep1.

Reply

Hi,

I followed this post to configure GoldenGate on Solaris 10 x86_64.
I faced issue to connect to golden gate user (ggate from GGSCI) at destination. I granted the connect to ggate and able to connect using GGATE.

However, when I started EXT1 and REP1 I didn’t see replication actually happened.

Appreciate your help!
-Amol

Reply

Hello Mr Gleb!
Q: can OGG run on a different server than the database? If yes what are the steps for such an install?(guidelines)

Thanks!

Reply
zikri (zach) a ince
July 5, 2012 11:14 am

Even though I have not tried installing OGG on a server without a database, i suspect it may work if Oracle Client exists. OGG uses Oracle libraries.

Reply

Hi Gleb,

I followed the above steps and I’m able to do dml replication frm source to target machine but not DDL replication.
For ddl replication , I have to mandatorily run ddl_enable.sql which enables the ddl trigger but the issue is after running ddl_enable I’m not able to run any DDL command like create table or alter table on source . Hence I cant check DDL replication .

The error that I get is :

SQL> alter table sender.test1 add dept varchar2(20);
alter table sender.test1 add dept varchar2(20)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 999
ORA-30511: invalid DDL operation in system triggers

I’m able to temporarily solve the above error /run any DDL command on source only when
1. I run ddl_disable.sql
2. Run the ddl command
3. Then again run ddl_enable.sql

But that is not the permanent solution because replication would not take place if I run ddl_disable and then run the ddl commands.

Please help me with ddl replication.Thanks in advance.

Reply
Oracle Datenbanken
March 4, 2013 6:59 am

What a great instruction regarding to the installation of Oracle!
Thank you very much for sharing.

Reply

That’s great article!
Thank you from Russia :)

Reply

Gleb,
You have a minor error in the text. Where it says:

“…Our goal is to create DDL and DDL replication from the sender schema on the source database to receiver schema on the destination…”

it should say

“…Our goal is to create DDL and DML replication from the sender schema on the source database to receiver schema on the destination…”

Right?
Alex.

Reply
Gleb Otochkin
May 30, 2014 10:56 am

Yes, you are right. I’ve corrected the line.
Thank you Alex.

–Gleb

Reply

hi

Table replication is not happening :(

GGSCI (eisoradbsrv1) 36> stop extract ext1

Sending STOP request to EXTRACT EXT1 …
Request processed.

GGSCI (eisoradbsrv1) 37> start extract ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (eisoradbsrv1) 38> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:13

GGSCI (eisoradbsrv1) 39>

=====

GGSCI (eisoradbserv2) 22> stop replicat rep1

Sending STOP request to REPLICAT REP1 …
Request processed.

GGSCI (eisoradbserv2) 23> start replicat rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (eisoradbserv2) 24> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:09

GGSCI (eisoradbserv2) 25>

======

SQL> select * from sender.test_tab_1;

ID RND_STR
———- ————
1 test_1
1 test_1
1 test_1
1 test_1

======

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

SQL>

Any help please

Reply
Gleb Otochkin
June 19, 2014 9:06 am

Hi Vijay,
I have couple of questions regarding your issue.
Have you created the table sender.test_tab_1 before setting up replication or after?
I am not sure I understand why you restart extract and replicat.
Regards,
Gleb

Reply

Hi

Thanks for reply

I created the table after setting up replication. restarting the replicate and extract was just to see if anything wrong with that.

Reply

Hi

Today i again tried all the steps provided by you . installation > config extract/replicate > create table > insert data > but same error

TARGET_DB >> conn / as sysdba
Connected.
TARGET_DB >>
TARGET_DB >>
TARGET_DB >> select * from receiver.test_tab_1;
select * from receiver.test_tab_1
*

Reply

Hi
I just checked the logs

and found

2014-07-18 13:53:59 ERROR OGG-01224 TCP/IP error 111 (Connection refused); retries exceeded.

2014-07-18 13:53:59 ERROR OGG-01668 PROCESS ABENDING.

Do we need to have tns entry configured between Databases.

getting this error
=================

2014-07-18 18:21:02 WARNING OGG-01223 Oracle GoldenGate Capture for Oracle, ext1.prm: Unknown error sending data over TCP. Please check if intended receiver program is running (for example server or manager).
2014-07-18 18:21:02 WARNING OGG-01223 Oracle GoldenGate Capture for Oracle, ext1.prm: Unknown error sending data over TCP. Please check if intended receiver program is running (for example server or manager).

BUT mgr/ext/rep running find ?? :( do not know where doing mistake

INCASE TWO DATABASES ARE THERE IN SAME HOST THERE SHOULD BE TWO DIFFERENT GG HOMES FOR DB1 AND DB2

Reply
Gleb Otochkin
June 26, 2014 7:20 am

Hi Madhu,
You don’t need a separate GG home for your second database unless the database has different version and resided in its own separate oracle home.

Regards,
Gleb

Reply

But how to connect to source db to create extract in case db1 db2 are on same host

Reply
Gleb Otochkin
June 28, 2014 11:03 am

Hi Madhu,
You have a choice. You can set up environment variables to proper ORACLE_SID or specify userid adding TNS alias like
USERID ggate@db1, password AAXX….

Regards,
Gleb

Reply

Can you please give sample steps
after .\ggsci

Reply

i was able to login to database with DBLOGIN USERID ggate@data05
But how to invoke manager on different databases in same host because only one manager will be there

Reply

IS there any syntax for two managers,2 databases,1 host,1 gghome

Reply
Gleb Otochkin
June 28, 2014 7:42 pm

Madhu,
You don’t need the second manager. One manager should be able to handle your extract and replicate.

Regards,
Gleb

Reply

Hi,

I am new to GG. I have requirement to migrate the database. Do I need to do any special if I install the GG instance for target database on any other box rather that Target database box?

Thanks

Reply

Hi Ashish,
You have to install GoldenGate to both sides, source and target.

Cheers,
Gleb

Reply

HI,
if i have a source DB and want to Sync it with more than 10 targets as bi-direction , what is the best plan for this case? , create Extract process +data pump for each target?, what if targets more than 100 ?!

please advise …

Reply

Hi Mazen,
100 targets may be a challenge. Can you please outline your configuration more precisely? Is it one source database replication to 100 independent target each of those would be replicating changes back? Or those 100 targets also replicate to each other?
You may think about implementing several hubs with 10 or 20 spokes. As matter of fact you can use only one extract on the main source and replicate to several hubs. Those hubs may replicate those changes further to spokes.

Reply

Hi Gleb,
thanks for reply, actually it is 1 source and 100 independent targets (moreover those targets can replicate to source too), so i’m not sure how the implementation of this issue should be!!, can you please advise.
And i couldn’t understand the issue of spkoes and hubs could you please explain more about it .
thanks

Reply

Hi Mazen,
Here is an example of hub and spokes :
on each hub you have 20 targets replicating to the hub database, they are not replicating to any other databases outside the hub. In the example you have 20 spoke databases per hub. It means each hub have 20 + 1 database.
The hub databases are replicating each other and subsequently changes are going from one hub to another and subsequently to each spoke database.
To use it you can use tag to mark transaction for each spoke and setup rules to avoid loop transactions.

Thanks,
Gleb

Reply

Hi, Is this possible to replicate already created tables (before gg installation) to destination with dml operation. If yes can u suggest where to provide dml configuration.

Reply

Hi Seetha,
Yes, you can add tables to existing replication.
You will need to add the tables mapping to extract and replicat parameters.

Thanks,
Gleb

Reply

I am getting the following error while trying to start ext1. Can someone plese help?

ERROR OGG-01626 Oracle GoldenGate Capture for Oracle, ext1.prm: br_dir_get: ggStat: /opt/app/oracle/golden_gate/BR: error code: 13 (Permission denied).

Reply

Check if GG user can read archivelogs and redo logs.

Thanks,
Gleb

Reply

Thanks for your reply Gleb. In our configuration we are using oracle id as the owner for both rdbms and golden gate binaries on source. So technically the id oracle has access to everything. I am stil not able to resolve this. Can you please advise?

Reply

It is really hard to say what exactly is causing the problem. If it is not permission issue the error can be result of missing archivelog. You should have more information in your ggserror.log

Cheers,
Gleb

Reply

Hi Gleb,

The following are the lines from ggserror.log…It is sun solaris 11 64 bit environment..source db in 11.2.0.4

2015-02-13 11:28:08 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext1.
2015-02-13 11:28:09 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host ahab.lbl.gov:41325 (START EXTRACT EXT1 ).
2015-02-13 11:28:09 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EXT1 starting.
2015-02-13 11:28:09 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, ext1.prm: EXTRACT EXT1 starting.
2015-02-13 11:28:09 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:28:09 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:28:09 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:28:09 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, ext1.prm: Operating system character set identified as US-ASCII. Locale: en_US_POSIX, LC_ALL:.
2015-02-13 11:28:09 ERROR OGG-00403 Oracle GoldenGate Capture for Oracle, ext1.prm: There can be only one DDL filtering statement. If DDL filter is long, use ampersand (&) sign to continue it on another line.
2015-02-13 11:28:09 WARNING OGG-01525 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed to open trace output file, ‘gglog-EXT1.dmp’, error 13 (Permission denied).
2015-02-13 11:28:09 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.
2015-02-13 11:30:02 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): edit params ext1.
2015-02-13 11:30:32 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext1.
2015-02-13 11:30:32 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host ahab.lbl.gov:34501 (START EXTRACT EXT1 ).
2015-02-13 11:30:33 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EXT1 starting.
2015-02-13 11:30:33 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, ext1.prm: EXTRACT EXT1 starting.
2015-02-13 11:30:33 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:30:33 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:30:33 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:30:33 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, ext1.prm: Operating system character set identified as US-ASCII. Locale: en_US_POSIX, LC_ALL:.
2015-02-13 11:30:34 ERROR OGG-00303 Oracle GoldenGate Capture for Oracle, ext1.prm: Unrecognized INCLUDE/EXCLUDE option: [DDL].
2015-02-13 11:30:34 WARNING OGG-01525 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed to open trace output file, ‘gglog-EXT1.dmp’, error 13 (Permission denied).
2015-02-13 11:30:34 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.
2015-02-13 11:31:37 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): edit params ext1.
2015-02-13 11:31:48 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext1.
2015-02-13 11:31:48 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host ahab.lbl.gov:34420 (START EXTRACT EXT1 ).
2015-02-13 11:31:48 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EXT1 starting.
2015-02-13 11:31:48 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, ext1.prm: EXTRACT EXT1 starting.
2015-02-13 11:31:48 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:31:48 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:31:48 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:31:48 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, ext1.prm: Operating system character set identified as US-ASCII. Locale: en_US_POSIX, LC_ALL:.
2015-02-13 11:31:49 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, ext1.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2015-02-13 11:31:49 ERROR OGG-01626 Oracle GoldenGate Capture for Oracle, ext1.prm: br_dir_get: ggStat: /opt/app/oracle/golden_gate/BR: error code: 13 (Permission denied).
2015-02-13 11:31:49 WARNING OGG-01525 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed to open trace output file, ‘gglog-EXT1.dmp’, error 13 (Permission denied).
2015-02-13 11:31:49 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.

Reply

I am sorry Gelb for sending so many lines…Following are the most recent log entries..

015-02-13 11:31:48 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext1.
2015-02-13 11:31:48 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host ahab.lbl.gov:34420 (START EXTRACT EXT1 ).
2015-02-13 11:31:48 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EXT1 starting.
2015-02-13 11:31:48 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, ext1.prm: EXTRACT EXT1 starting.
2015-02-13 11:31:48 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:31:48 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:31:48 WARNING OGG-01834 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2015-02-13 11:31:48 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, ext1.prm: Operating system character set identified as US-ASCII. Locale: en_US_POSIX, LC_ALL:.
2015-02-13 11:31:49 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, ext1.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2015-02-13 11:31:49 ERROR OGG-01626 Oracle GoldenGate Capture for Oracle, ext1.prm: br_dir_get: ggStat: /opt/app/oracle/golden_gate/BR: error code: 13 (Permission denied).
2015-02-13 11:31:49 WARNING OGG-01525 Oracle GoldenGate Capture for Oracle, ext1.prm: Failed to open trace output file, ‘gglog-EXT1.dmp’, error 13 (Permission denied).
2015-02-13 11:31:49 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.

Reply

Hi,
Looks like your bounded recovery cannot proceed due to problem with reading a checkpoint file or an archivelog required for the recovery.
I don’t know exactly what happened in your case and whether it was a long transaction or any other reasons. But, what I can see from your log, is error when extract tries to reach checkpoint file, or maybe it is misleading and it tries to reach required archivelog and cannot find it (if I remember correctly it was a bug for some of old version).
You can try brreset to switch to normal recovery but please be careful. I would double check why I could not access to my BR directory and checkpoint files.
Did you try to check permissions for /opt/app/oracle/golden_gate/BR directory?

–Gleb

Reply

Hi Gleb,

I setup gold gate using the steps in this article. I dont see BR directory on source side. What am i doing wrong? Did i miss something in configuration? The following are the directories i see in golden_gate HOME.
drwxr-x— 2 oracle dba 4096 Feb 12 15:02 cfg/
drwxrwxr-x 2 oracle dba 4096 Feb 12 15:02 dirdat/
drwxrwxr-x 2 oracle dba 4096 Feb 12 15:02 dirdef/
drwxr-x— 2 oracle dba 4096 Feb 12 15:02 dirjar/
drwxrwxr-x 2 oracle dba 4096 Feb 12 15:02 dirout/
drwxrwxr-x 2 oracle dba 4096 Feb 12 15:02 dirsql/
drwxrwxr-x 2 oracle dba 4096 Feb 12 15:02 dirtmp/
drwxr-x— 2 oracle dba 4096 Feb 12 15:02 discard/
drwxrwxr-x 2 oracle dba 4096 Feb 13 11:14 dirchk/
drwxrwxr-x 2 oracle dba 4096 Feb 13 11:31 dirrpt/
drwxrwxr-x 2 oracle dba 4096 Feb 13 11:31 dirpcs/
-rw-rw-rw- 1 oracle dba 58168 Feb 19 10:04 ggserr.log
drwxr-x— 2 oracle dba 4096 Feb 19 10:04 dirprm/
(ahab.oracle) golden_gate % pwd
/opt/app/oracle/golden_gate

Reply

Hi Gleb,

My dear friend unix admin had not changed the volume ownership to oracle:dba hence was running into the BR access problem!. Thanks. it is moving along now…will see how it goes.

Reply

I am glad it was eventually solved. Good luck!

Cheers,
Gleb

Reply

Hi Gleb, I am setting up golden gate in Oracle linux using Oracle RAC DB(2 nodes) as source and a single instance DB as target but I could not make it work…. Is it because my source database is RAC?

Reply
Gleb Otochkin
March 4, 2015 11:36 am

It has to work in Oracle RAC. One thing about RAC installation. You may need to make sure you can read your redo and archived log files. Also I think you may explore high availability for GoldenGate in RAC environment.

Regards,
Gleb

Reply

Hi Gleb,

Do you have a step by step document for GoldenGate in RAC environment? Can you refer a site or any blog I can use?

Reply
Gleb Otochkin
March 5, 2015 10:22 am

Hi Erick,
I am sorry, but I don’t have a paper with step by step instruction for RAC.
It is not going to be too different from the standalone instance setup.
You need only to keep in mind how you connect to RAC (you have more than one instance and gg extract will be running on one of them). So you need a VIP for manager and you will need a shared FS for some of GG files.
You may have a look to Oracle support article :
Oracle GoldenGate Best Practices: Oracle GoldenGate High Availability Using Oracle Clusterware (Doc ID 1313703.1)

Also you may look to IGGOUG web site.

Cheers,
Gleb

Reply

I dont see part 2 for this Golden Gate article. Can you please update for Oracle 12c , if there are any changes.

Reply
Freight shipping uk
December 25, 2015 6:59 pm

Admiring the time and effort you put into your site and detailed information you provide.

It’s great to come across a blog every once in a while
that isn’t the same old rehashed information. Fantastic read!

I’ve saved your site and I’m including your RSS feeds to
my Google account.

Reply

If same database technology is used on source and target, will the index structure (eg. b-tree) also be replicated to the target database during ddl replication? What happens if the database technology is different on source and target systems and we want to maintain the same structure on both systems?

Reply

Hi Akileish,
If you replicate from Oracle to Oracle ,as example, and enable DDL replication, it will create the same logical structure on the target . Essentially the GoldenGate will replay the command to create the index applying the mapping. Of course the internal index structure like number of levels and leaf blocks may be different. Speaking about other database engines it will depend from supported operations for the replication. As example if you replicate from Oracle to MySQL all DDL it is not going to work since DDL replication is not supported for that configuration.


Gleb

Reply

The article is very informative Gleb.
Thanks for sharing valuable information.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *