Oracle GoldenGate Installation, Part 1

Feb 18, 2010 / By Gleb Otochkin

Tags: ,

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.

239 Responses to “Oracle GoldenGate Installation, Part 1”

  • Steve B says:

    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

  • Steve B says:

    … it is skipped.

    - Steve B

  • Yes, you are absolutely right Steve. We can use it and it works fine. The command creates all necessary directories. I’ll correct the post.
    Thank you Steve.

  • David Mann says:

    I haven’t used GG for about 2 years, is the monitoring of the replication lag time still done via their command line tools?

    I created some scripts to check lag time and graph the results with gnuplot — I can find them and dust them off if anyone is interested.

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

  • V Patel says:

    This is really a great post. I have been looking for an article like this for last few days and finally find something that is very well straight forward to implement gg. I noticed this particual post is replicating only ddl, can you explain or post an article regarding replicate schema or perhaps an entire database? Thank you.

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

  • Hi,
    This post is about replication entire schema.
    It means all dml and ddl changes will be replicated to destination database.
    The index or table created in the schema SENDER on source would be replicated to target schema RECEIVER.
    Also any manipulation with data on source would be propagated and applied on target schema.
    –Gleb

    • V Patel says:

      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,

  • Manish says:

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

    help is appriciated
    Regards

  • murali says:

    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

  • V Patel says:

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

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

    • Manish says:

      Thats excellent!!

    • murali says:

      Eagerly waiting for installing on windows server.

      Thanks in advance.

    • Pramod says:

      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

  • Manish says:

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

  • murali says:

    Excellent, you are rocking!!!

  • sns says:

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

    • Manish says:

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

      Manish

    • Hi sns,
      Can you provide information from extract rpt file?
      You can find it in $GGATE/dirrpt
      I think it should be:
      $GGATE/dirrpt/ext1.rpt
      I guess some parameters could be wrong or firewall blocked connections from source to destination.
      If you have manager on destination side on port 7810 then you have use parameter fro extract:
      rmthost db2, mgrport 7810

      –Gleb

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

  • andrew lee says:

    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

  • adriana says:

    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.

  • Anil says:

    Thank you.

  • adriana says:

    Hi
    The version GG is 10.4 and of Oracle 10.2.

    Thank you

  • sean says:

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

    -Sean

  • siva prakash says:

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

  • sean says:

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

  • Hi, in my test installation the DDLs, INSERTs AND DELETEs are replicated with success but the UPDATEs are getting error.
    The error is:

    Problem replicating SENDER.TEST_TAB_1 to RECEIVER.TEST_TAB_1
    Record not found
    Error (1403) occurred with compressed update record (target format)…
    *
    ID =
    RND_STR = XxXxXy
    *

    May be caused by the inexistence of PK ?

    • Yes, the possible cause of the error was lack of primary key for the replicated table.
      It is strongly recommended to have PK for all replicated tables.
      But in every cases it can be related to different reasons.
      You can check the records in destination table.
      Maybe you don’t have a record for update or destination records had different values in comparing with source.

      –Gleb

    • Jay says:

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

      Jay

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

        –Gleb

        • Jay says:

          I will try create primary/unique key and see if any abending errors ,
          It seems that we can add for extrator as below:
          add trandata user.table , COLs (A)
          to specify keycols without creating primary keys , I will try this else , thanks your advice~

          • hi Jay,
            Yes you can define some cols for identify row in parameters.
            But primary key is not only for identifying rows but also for improving replicat work.
            If you use primary key for tables the replicat will use them for update or delete and the sql plan will be created with using these primary key.
            It will play significant role in case big tables.

            Gleb

  • PM says:

    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

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

  • engin says:

    Hi,
    I get this error:

    GGS WARNING 150 TCP/IP error 10060

    after create table for replica

    • Hi engin,
      It looks as some network problem.
      As I remember correctly it is tcp timeout error.
      Maybe your GG on destination side was down or inaccessible on that time.

      –Gleb

  • Nith says:

    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

  • engin says:

    hi,
    I done only one hr schema for replicate and it works. but how I can add second schema exaple second schema name is scot and I want replicat both schema ?

    thank you

    • hi engin,
      For add schema to replication you have to change configuration for extract on source database and add new schema:
      change:
      ddl include mapped objname sender.*;
      to
      ddl include mapped objname sender.*,new_schema.*;
      and
      TABLE sender.*;
      to
      TABLE sender.*,new_schema.*;

      also you need to include the schema to datapump (if you use it)
      change
      TABLE sender.*;
      to
      TABLE sender.*,new_schema.*;

      and modify mapping on destination database (if you use remapping for the schemas)

      –Gleb

  • engin says:

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

    thank you

    • hi engin,
      For bi-directional replication we need:
      requirements:
      replicat checkpoint table on both systems.
      add parameter to extract:
      TRANLOGOPTIONS EXCLUDEUSER
      On every system we have to create:
      1. extract (for capture local changes)
      2. datapump (for send changes to remote system)
      3. replicat (for apply changes from remote system)

      Synchronize the data and start the replication on both side.

      Also I think you probably will need in conflict resolution for your transaction.

      –Gleb

  • engin says:

    thank you so much for repling.

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

  • raji says:

    hi ,

    the steps are very clear to me. i successfully did for one schema.

    my requirement is this

    i have 5 schemas. i need to capture all ddl and dml changes of all the schemas.

    do i need to create individual extract process for each schema?

    do i need to create individual replicat process for each schema?

    currently my ext params is this way

    extract ext_new
    userid ggate, password abcd
    rmthost cccc007, mgrport 7809
    rmttrail /s001/copy01/oracle/oracle_sw/ggate/dirdat/nw
    ddl include mapped objname AAPROFILE.* &
    include mapped objname sender.* &
    include mapped objname xxxx.* &
    include mapped objname yyyy.* &
    include mapped objname zzzzz.*
    table sender.*;
    TABLE xxxx.*;
    TABLE yyyy.*;
    TABLE zzzz.*;

    and rep params are this way

    REPLICAT apc_rep
    ASSUMETARGETDEFS
    userid ggate, password s0lut_1on
    discardfile /s001/copy01/oracle/oracle_sw/ggate/discard/apc_rep_discard.txt, append, megabytes 10
    DDL
    MAP xxxx.*,TARGET xxxx.*;
    MAP yyyy.*,TARGET yyyy.*;
    MAP zzzz.*,TARGET zzzz.*;
    map sender.*, target receiver.*;

    both ext and rep are running but not happening

    pls give me right method

    all above schemas are important . i dont want replicat to fail for one schema. i need other 3 to run atleast. in that case. should i create all seperate ext and rep for all?

    awaiting for the reply

    • hi raji,
      I guess you did one mistake in your parameter files:
      you have to put mark “;” only after all instruction for process.
      As example for extract:

      extract ext_new
      userid ggate, password abcd
      rmthost cccc007, mgrport 7809
      rmttrail /s001/copy01/oracle/oracle_sw/ggate/dirdat/nw
      ddl include mapped objname AAPROFILE.* &
      include mapped objname sender.* &
      include mapped objname xxxx.* &
      include mapped objname yyyy.* &
      include mapped objname zzzzz.*
      table sender.*
      TABLE xxxx.*
      TABLE yyyy.*
      TABLE zzzz.*;

      And for replicat:

      REPLICAT apc_rep
      ASSUMETARGETDEFS
      userid ggate, password s0lut_1on
      discardfile /s001/copy01/oracle/oracle_sw/ggate/discard/apc_rep_discard.txt, append, megabytes 10
      DDL
      –I am not sure you need remap for your schema
      –MAP xxxx.*,TARGET xxxx.*
      –MAP yyyy.*,TARGET yyyy.*
      –MAP zzzz.*,TARGET zzzz.*
      map sender.*, target receiver.*;

      Please let me know if it will help.

      –Gleb

  • raji says:

    hi thanks,

    it worked .. issue is if i dont remap.. it does not replicate DML only ddl is happening for all schemas put in extract trails.

    thanks again.

    to add with this i have one more doubt while untarring in lunux .. i got below warnign in 3,4 servers where i tried

    tar: mgr: Cannot change ownership to uid 1901, gid 801: Operation not permitted
    ggsci

    same uid gid in all the linux servers . i downloaded

    V18157-01.zip and unzipped on this

    ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

    and tar xvf

    shoudl this warning be ignored?

    in my linux neigther root nor oracle userid has this UDI and gid.

    any thought?

    extract and replication working fine :)

    • I can suppose the tar has saved uid and gid for the files and tried to preserve them during unpacking.
      I know about such bug when tar is unpacking archive to NFS share.
      I don’t think it can be a problem. You can change ownership and group after unpacking.

      –Gleb

  • raji says:

    thanks much. tomorrow i am testing 10 20 05 upgrade ( from 10 2 0 4) with minimal down time of application. currnetly buidling clone db.

    if you have any such steps please pass on to me. will be helpful

  • Do you mean step for upgrade database to 10.2.0.5?
    I used different way for upgrade.
    One of way was rolling upgrade using transient logical standby.
    But I am afraid it is a subject for another post :)

    –Gleb

  • raji says:

    Hi Gleb,

    10 20 4 source and target are in synch.
    At this state to start replicatin testing. i m doing below steps.

    tomorrow when applcation starts i am goign to start extrat process.

    source

    env for ggate is set with 10 20 4 home and ld_library_path

    target

    env for ggate is set with 10 20 4 home and ld_library_path

    application team verifies target gets correct data.

    then decided to upgrade.

    at this stage , i m making target db down after stopping REPLICAT in target.

    manager process is still running in target.

    but when i point target db to 10 2 0 5 home and upgrade, post upgrade when i start replicat,
    will the trails be applied to new upgraded db?

    next time when i source upgraded db, the ld_library_path will be new one and oracle_home also new one.
    please suggest.

    thanks
    raji

  • It should work after upgrade without problem.
    You have to check on destination that LD_LIBRARY_PATH is pointed to proper home.
    Also You have to store all archivelogs for restart extract.
    Extract should catch up all changes on source from archivelogs.

    –Gleb

  • Sebastian says:

    I receive the following when I start replication process on second instance.

    GGS ERROR 2001 Fatal error executing DDL replication: error [Error code [1741], ORA-01741: illegal zero-length identifier, SQL create table “GGATE”.”” (id number, rnd_str varchar2(12)
    ) /* GOLDENGATE_DDL_REPLICATION */], no error handler present.

    and the replicat is abending. Do you know what is the problem?

    • hi Sebastian,
      I suspect some incorrect ddl command on source or incorrect mapping parameter on the destination side.
      As you can see the replicat process tried to create table “GGATE”.””.

      –Gleb

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

  • Bhaskar says:

    Hi Gleb,

    I am testing Bi-Directional scenario in GGenGate using DB 10.2.0.1.0

    Source and Destination are fresh databases. Created GG schema on both the databases.

    SOURCE
    ======
    GGSCI (ctsincalsdcg) 1> add extract BGGGO, tranlog, begin now
    EXTRACT added.

    GGSCI (ctsincalsdcg) 2> add exttrail /punvis/ggdb/GGenGate/dirdat/BD, extract BGGGO
    EXTTRAIL added.

    GGSCI (ctsincalsdcg) 3> edit params BGGGO

    EXTRACT BGGGO
    USERID GG, PASSWORD GG
    RMTHOST ctsincalsdcg, mgrport 7810
    rmttrail /punvis/ggdb/GGenGate/dirdat/BD
    GETUPDATEBEFORES
    table gg.*;

    DESTINATION
    ===========
    GGSCI (ctsincalsdcg) 1>edit params ./GLOBAL
    GGSCHEMA GG
    CHECKPOINTTABLE GG.BIDIRECTIONAL

    GGSCI (ctsincalsdcg) 2> ADD CHECKPOINTTABLE GG.BIDIRECTIONAL

    Successfully created checkpoint table GG.BIDIRECTIONAL.

    GGSCI (ctsincalsdcg) 3> ADD REPLICAT BGGGO, EXTTRAIL /punvis/ggdb/GGenGate/dirdat/BD, checkpointtable GG.bidirectional
    REPLICAT added.

    GGSCI (ctsincalsdcg) 4> edit params BGGGO

    –Replicat group –
    Replicat BGGGO
    –source and target definitions
    ASSUMETARGETDEFS
    –target database login –
    userid GG, password GG
    DDLOPTIONS IGNOREREPLICATES, GETAPPLOPS
    –file for dicarded transaction –
    discardfile /chnvis/GGdb/GGenGate/discard/BGGGO_discard.txt, append, megabytes 10
    –Specify table mapping —
    map gg.*, target GG.*;

    SOURCE
    ======
    GGSCI (ctsincalsdcg) 1> add extract BGOGG, tranlog, begin now
    EXTRACT added.

    GGSCI (ctsincalsdcg) 2> add exttrail /chnvis/golddb/GoldenGate/dirdat/DB, extract BGOGG
    EXTTRAIL added.

    GGSCI (ctsincalsdcg) 3> edit params BGOGG

    EXTRACT BGOGG
    USERID GG, PASSWORD GG
    RMTHOST ctsincalsdcg, mgrport 7811
    rmttrail /chnvis/golddb/GoldenGate/dirdat/DB
    GETUPDATEBEFORES
    table gg.*;

    DESTINATION
    ===========
    GGSCI (ctsincalsdcg) 1>edit params ./GLOBAL
    GGSCHEMA GG
    CHECKPOINTTABLE GG.BIDIRECTIONAL

    GGSCI (ctsincalsdcg) 1> ADD CHECKPOINTTABLE GG.BIDIRECTIONAL

    Successfully created checkpoint table GG.BIDIRECTIONAL.

    GGSCI (ctsincalsdcg) 2> ADD REPLICAT BGOGG, EXTTRAIL /chnvis/golddb/GoldenGate/dirdat/DB, CHECKPOINTTABLE GG.BIDIRECTIONAL
    REPLICAT added.

    GGSCI (ctsincalsdcg) 3> edit params BGOGG

    –Replicat group –
    Replicat BGOGG
    –source and target definitions
    ASSUMETARGETDEFS
    –target database login –
    userid gg, password gg
    –file for dicarded transaction –
    discardfile /punvis/ggdb/GoldenGate/discard/BGOGG_discard.txt, append, megabytes 10
    –Specify table mapping —
    map gg.*, target gg.*;

    When i am starting Extract & Replicat (BGGGO) is working fine.

    When i am starting Extract & Replicat (BGOGG) is Abending with the following error.

    2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Aborted grouped transaction on
    ‘GG.BIDIRECTIONAL’, Database error 1 (ORA-00001: unique constraint (GG.SYS_C005495) violated).
    2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Repositioning to rba 917 in seq
    no 0.
    2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: SQL error 1 mapping GG.BIDIRECT
    IONAL to GG.BIDIRECTIONAL OCI Error ORA-00001: unique constraint (GG.SYS_C005495) violated (status = 1), SQL .
    2010-07-12 05:18:19 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Repositioning to rba 1178 in se
    qno 0.
    2010-07-12 05:18:19 GGS ERROR 218 Oracle GoldenGate Delivery for Oracle, bgggo.prm: Error mapping from GG.BIDIRECTI
    ONAL to GG.BIDIRECTIONAL.
    2010-07-12 05:18:19 GGS ERROR 190 Oracle GoldenGate Delivery for Oracle, bgggo.prm: PROCESS ABENDING.

    Could you please provide your valuable inputs to proceed further.

    Thanks,
    Bhaskar Mudunuri

    • hi Bhaskar,
      The problem is in your checkpoint table.
      You are replicating schema gg.
      The checkpoint table is on the schema gg as well.
      The table shouldn’t be in your replicated schema.
      I would advise to create the checkpoint table in some another schema where all golden gate internal tables will be situated.

      As you can see in your log the two source tried to insert same data to the table.


      Gleb

  • Bhaskar says:

    Hi Gleb,

    I had configured Oracle DB for GoldenGate.

    I have used one more schema for the Goldengate and other schema for transactions.

    Could you please tell us the Parametars are required in EXTRACT and REPLICAT to make bidirectional work properly.

    As of now i have used the above mentioned configuration and it is going to infinite loop.

    Thanks a lot for your help in advance :-)

    Regards,
    Bhaskar Mudunuri

  • hi Bhaskar,
    1. DDL replication is not supported in bi-directional replication.
    Hence forget about ddl replication.

    2. It is strongly recommended use primary keys for replicated tables.

    3. I advise to check If you have some triggers or constraints (on delete cascade) on your replicated tables which can generate DML operations.
    Maybe they should be modified.

    4. I advice to think about update conflict resolution for replicated tables.

    5. Data looping:
    By default the extract should ignore any transaction generated by replicat process.
    It means you should not hit data looping in your bi-direction replication and can use usual parameters for extract and replicat.

    But I would insure it by using parameter for extract:

    tranlogoptions excludeuser ggate

    where GGATE is replicat database user which you have used in replicat parameter:

    userid ggate, password qwerty

    –Gleb

  • tramchand says:

    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

  • Dominica says:

    I have a question, our “steps” look good.
    I have trouble for the db_install.rsp. Since there is 2 disk for the oracle software,
    which line you tell oracle ‘s response file (db_install.rsp) to look for the second disk.

    Is it ok for you to post the db_install.rsp?
    or email to me directly , maybe I see it?

    Thanks,

    Dominica L

    • Hi Dominica,
      What disks do you mean?
      Do you have oracle installation software on DVD?
      I unzipped the both files in one directory.
      I didn’t have any second disk. Also I hadn’t changed default path for installation in response file.

      –Gleb

  • Prachi says:

    Hi Gleb,

    I am trying to migrate a table from Sybase to Oracle using Oracle Golden Gate.

    I have installed Sybase 15 and Oracle golden gate 10.4 for sybase 15.

    My sysbase services are running.
    But when i try to connect to sybase using dblogin in GGSCI i am not being able to do it.

    I receive following error –

    ERROR: Cannot connect to database master at server BL1DDG1W4BS.
    Client Library error: severity(0) number (44) origin(1) layer(4).
    ct_connect(): protocol specific layer: external error: The attempt to connect to
    the server failed.

    Command used –
    GGSCI (BL1DDG1W4BS) 12> dblogin sourcedb master userid sa
    Password:

    master – database name
    sa – userid

    Both my oracle and sybase are installed on the same system.
    Golden gate for sybase and oracle are in different directories.

    As per the installation guide DSQUERY variable should be set to the server on which sybase is running.
    I have done that also.

    Please if you can help me out.
    Does sybase db needs special grants?

    • Hi Prachi,
      It looked the problem in libraries using for connection. What operation system do you use?
      Maybe setting proper PATH variable can help to resolve it.
      also I would mention some things (i don’t think it a cause of problem but …):
      Have you created dedicated user for Golden gate and granted system administartor privileges to it?
      I wouldn’t use the any default user for connection.

      –Gleb

      • Prachi says:

        Hi Gleb,
        Thanks for your prompt reply.
        I was being able to resolve the issue.
        Issue was at the sybase end, password related thing. I changed the password and managed to connect to sybase.

        I am trying to migrate a sybase table to oracle.

        Both sybase and oracle are on same system.
        For initial load when i start extract process, it is not finding the replicat parameter files. The details are as below:

        I have both Sybase and Oracle installed on the same system. I have installed golden gate for Sybase and Oracle in different directories.
        When I run start extract load 1, I receive an error that it could not find file load2.
        In my case Sybase is the source and Oracle is the target.
        Param file load2 I created on target, ie. Using the command prompt for Oracle which is my target.

        Below are the param file definitions

        Load1
        EXTRACT load1
        SOURCEDB pubs,USERID sa,PASSWORD test123
        RMTHOST BL1DDG1W4BS, MGRPORT 7812
        RMTTASK replicat,GROUP load2
        TABLE pubs.dbo.hs_1;

        Load2
        REPLICAT load2
        USERID ggs_owner,PASSWORD ggs_owner
        ASSUMETARGETDEFS
        MAP pubs.hs_1, TARGET ggs_owner.hs_1
        ;

        It is giving me following error

        2010-08-12 17:05:55 GGS INFO 302 Oracle GoldenGate Manager for Sybase,
        mgr.prm: EXTRACT LOAD1 starting.
        2010-08-12 17:05:55 GGS INFO 414 Oracle GoldenGate Capture for Sybase,
        LOAD1.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
        2010-08-12 17:05:55 GGS INFO 310 Oracle GoldenGate Capture for Sybase,
        LOAD1.prm: EXTRACT LOAD1 starting.
        2010-08-12 17:05:55 GGS INFO 310 Oracle GoldenGate Capture for Sybase,
        LOAD1.prm: EXTRACT LOAD1 started.
        2010-08-12 17:05:56 GGS INFO 301 Oracle GoldenGate Manager for Sybase,
        mgr.prm: Command received from EXTRACT on host 10.200.21.20 (START REPLICAT LOA
        D2 CPU -1 PRI -1 PARAMS ).
        2010-08-12 17:05:56 GGS ERROR 150 Oracle GoldenGate Capture for Sybase,
        LOAD1.prm: Error reported by MGR : Parameter file C:\GGSCI\dirprm\load2.prm doe
        s not exist.

        The golden gate for Oracle which is my target is in D:\GGSC folder. But over here it looks like it is going to the golden gate of source.
        Is it that we cannot have both source and target on same system.

        Thanks In Advance.

        • Hi,
          I hope your managers for oracle and for sybase are working using different ports and started properly.
          Have you tried to start the replicat load02 ( which I guess should be on oracle side) first before starting extract on sybase side?
          Maybe in this case it will not try to start replicate because it will be up already.
          I was able to run 2 different manager on my machine but it was mssql and oracle.
          I think I have to try sybase as well.

          –Gleb

          • Prachi says:

            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

  • Manish says:

    Hi Gleb,

    @Goldengate source and target, the memory utilization is very high.
    Like in case of one of the system @source the memory utilization is 44% and it is constant.

    Need Your advice on how to reduce memory utilization.

    Thanks for your help.

    Regards
    Manish

    • Hi Manish,
      Do you work with data pump or with direct replication from extract to remote replicat trail?
      First of all I would advice to use data pump for reduce memory allocation by extract process.
      In second we can use parameter CACHEMGR for adjust memory allocation by extract process.

      You can check your memory allocation in logs for processes.

      –Gleb

      • Manish says:

        Hi Gleb,

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

        Regards
        Manish

        • Hi Manish,
          Have you tried to use parameter CACHEMGR for your extract and datapump processes?
          Also it would be great to make sure about memory consumers on the box.
          Maybe memory was consumed by some another processes.

          –Gleb

  • Krishna says:

    Hi

    Can it be setup (am sure we should be) with source and destination same Oracle?

    Do I need to follow any other steps than posted?
    How is more beniftale than Active Data Guard?

    Thanks,
    Krishna.

    • hi Krishna,
      Yes, you can use replication inside one database between 2 schemas as example.
      Also You off course can yse replication between 2 oracle databases.
      And also you can use it as logical standby.
      The main differenc in compare to active data guard is the database will be open in read write mode.
      The replication using GG is more likely to replication by streams than replication which used for standby.

      –Gleb

  • Daniel says:

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

    Thanks

    • Hi Daniel,
      I would advice to set up proper environment variable (ORACLE_SID,ORACLE_HOME) for ssh session before starting replicat or extract. It should be enough for resolving the problem and running extract and replicat for necessary instance.

      –Gleb

  • param says:

    I am using oracle CDC which is built on top of streams. This enables me to subscribe and process incremental data only. If I switch to GG is how am I going to process incremental data only?

    • Hi param,
      What do you mean under “incremental”?
      GG will send only changes in your data except first load (which can be done without using GG).
      I think it is quite “incremental”.
      From my point of view the GoldenGate is just another way for replication.

      –Gleb

  • Esskay says:

    i have been assighned one task to execute , details are as below .

    i need to setup a Table A1 ,B1 and table A2,B2 on the same database .there columns are as below .

    QL> DESC A1
    Name Null? Type
    —————————————– ——– ——————-
    ID NOT NULL VARCHAR2(20)
    NAME VARCHAR2(20)
    QL> DESC B1
    Name Null? Type
    —————————————– ——– ——————-
    NAME NOT NULL VARCHAR2(20)
    STATE VARCHAR2(20)
    QL> DESC A2
    Name Null? Type
    —————————————– ——– ——————-
    ID NOT NULL VARCHAR2(20)
    NAME VARCHAR2(20)
    STATE VARCHAR2(20)
    QL> DESC B2
    Name Null? Type
    —————————————– ——– ——————-
    NAME NOT NULL VARCHAR2(20)
    STATE VARCHAR2(20)

    i have to replicate table B1 TO B2 with out any logic but where as Table A1 TO A2 I NEED TO REPLICAT A1 THAT CALLS THE PACKAGE THAT HAS A QUERY PULLS THE DATA FROM B2 TABLE BY JOINING THE TABLE B2 GET THE STATE COLUMN DATA INTO A2.i am verymuch intrested in GG replications so i applied in my Company for Internal JOB posting but they want to pre screen actually i know the basic stuffs but i dont how to use Macros .i would apperciate if you can help me in this Task .

    • Hi Esskay,
      To be honest I couldn’t get all your needs.
      Can you please show what join you meant for tables B?
      Do you mean to insert/update to A2 id/name from A1 and state from B2?

      –Gleb

  • sk says:

    Hi all ,
    i was trying to install Goldengate 10.4 on IBM AIX 5.3 powerssytem after tar ing the files when i try to run ggsci i get below errors
    Could not load program ggsci:
    Symbol resolution failed for ggsci because:
    Symbol _GetCatName__FiPCc (number 159) is not exported from dependent
    module /usr/lib/libC.a[ansi_64.o].
    Symbol __ct__Q2_3std8_LocinfoFPCci (number 184) is not exported from dependent
    module /usr/lib/libC.a[ansi_64.o].
    Examine .loader section symbols with the ‘dump -Tv’ command.

    please let me know solutions if you have come across this kind of problem

    • Hello sk,
      Sorry for late reply. Can you give me exact version of installed GoldenGate.
      It looks you have installed version for AIX 5.2 instead version for 5.3/6.1
      There are two different version on oracle for Oracle GoldenGate V10.4.0.x for Oracle.

      Thanks,
      Gleb

  • Balwinder says:

    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.

  • Manish Chakraborty says:

    Hi Gleb,

    I am getting the below error in the extract part of golden gate.

    Using the following key columns for source table DMS.STG_CHANNEL_BANK_DETAILS: CHANNEL_PROFILE_ID.

    2010-07-28 19:46:10 GGS INFO 224 Rolling over remote file ./dirdat/dm006026.

    2010-07-28 19:47:34 GGS INFO 224 Rolling over remote file ./dirdat/dm006027.

    What can be the exact reason of this error.

    Regards
    Manish

  • Hi Manish,
    I don’t see a error. It is informing message about a new trail file opened by your extract process.
    It is only for tracing extract activity and doesn’t signal about any error.

    –Gleb

  • Hi, I am getting these messages on file “ggserr.log”. These transactions are related to distributed transactions when the source database is a RAC. Do you know any workaround ? Thank you.

    2010-09-30 10:27:33 GGS WARNING 220 Oracle GoldenGate Capture for Oracle, ext1.prm: Record with class# 1960, slt# 0, at seqno 21906, rba 1441055760 SCN
    1401.4250826332 (6021500008028) has secondary transaction ID that is duplicate of existing open uncommitted transaction.
    2010-09-30 10:27:33 GGS WARNING 220 Oracle GoldenGate Capture for Oracle, ext1.prm: [Thread #2] Purging transaction (transaction id: 972.0.787746, star
    t time: 2010-09-30 10:27:30, start seqno: 21906, start RBA: 1436612112).
    2010-09-30 10:27:33 GGS WARNING 220 Oracle GoldenGate Capture for Oracle, ext1.prm: Updating I/O checkpoint after purging orphaned transactions on thre
    ad 2 with current position (Seq#: 21906, RBA: 1441058444).

  • Prakash says:

    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

    • 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

  • Caesar Dutta says:

    Dear Sir:

    Is it possible to use GG between MS-SQL Server and Oracle? Every tutorial / website is speaking of same database though GG is supposed to be heterogenous (having different installers). Please let me know or answer to my email so that I can check your site.

    • Yes,
      It should be possible but you have use different version oracle GoldenGate for Oracle and MS SQL server.
      As example:
      Create extract and datapump on oracle side and set up remote trail on MS SQL server side.
      On MS SQL side replicat GoldenGate for MS SQL.

      Also here is from GG documentation:
      “Oracle GoldenGate enables the exchange and manipulation of data at the transaction level
      among multiple, heterogeneous platforms across the enterprise1.”

      The key word is “heterogeneous”

      Cheers,
      Gleb

  • maclean says:

    Hi,In “–extract group–
    extract ext1
    –connection to database–
    userid ggate, password qwerty
    –hostname and port for trail–
    rmthost db2, mgrport 7809
    –path and name for trail–
    rmttrail /u01/app/oracle/product/gg/dirdat/lt
    –DDL support
    ddl include mapped objname sender.*;
    –DML
    table sender.*” , comma should be at last

    • Hi
      Yes it is true. The semicolon should be moved to end of the file.
      Here is what was supposed to be in the end of configuration:

      –DDL support
      ddl include mapped objname sender.*
      –DML
      table sender.*;

      –Gleb

  • arpit says:

    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

    • 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

  • Reddy says:

    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

  • Reddy says:

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

  • Reddy says:

    Hi Gleb, Thanks for your response.

    I’m stuck at STEP 5 in the process. I created the link in step 2 as mentioned and “ggsci” worked perfectly but at step 5 when I’m trying to invoke ggsci i’m getting this error:

    ld.so.1: ggsci: fatal: libclntsh.so.10.1: open failed: No such file or directory
    Killed

    Please advise. My Oracle DB version is 11.2.0 and OS is Solaris 10 SPARC

    Thanks
    REddy

    • Hi Reddy,
      It looks your GoldenGate cannot find oracle lib libclntsh.so. Please check existence of the lib:
      ls -l $ORACLE_HOME/lib/libclntsh.so.10.1
      It should be on the place. Actually it is a link to the lib $ORACLE_HOME/lib/libclntsh.so

      If you have the lib but still geting the error you have to add the path $ORACLE_HOME/lib to the environment variable LD_LIBRARY_PATH.
      Try to run before starting ggci:
      export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

      Also I would like to mention that we have GoldenGate 10 and 11 versions for oracle 11g which doesn’t require creation any addition links during installation.


      Gleb

  • Reddy says:

    Perfect!! Thanks it worked.

    -Reddy

  • Reddy says:

    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

  • Reddy says:

    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.

  • Reddy says:

    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

  • Reddy says:

    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

  • Reddy says:

    Nevermind. I got it fixed. Just Added PORT 1522 and started the manager it worked. Sorry to bother you with too many emails. Thanks for your help.

    -Reddy

  • hi Reddy,
    I see you fixed the error. The manager process should not use same port as listener. It is different process and it is listening its own port. Yes you can use port 1522 if your listener doesn’t use it.
    I prefer to use default port 7809.
    The parameter should be without teg

    --
    Gleb

  • Reddy says:

    Hi Gleb,

    When I’m trying to dblogin from destination db it’s not letting me to login.

    GGSCI (devsx032) 1> dblogin userid ggate
    Password:
    ERROR: Failed to open data source for user GGATE.

    I ran the role_setup.sql with the same user and it worked but this is failing here. Please advice.

    Thanks
    Reddy

  • Hi Reddy,
    The script role_setup.sql should be run from user with dba privilege. I ran it from user sys. User cannot grant privileges to itself. I suggest to check environment variable ORACLE_SID for session and repeat the scripts.


    Gleb

  • Reddy says:

    I’m sorry I meant to say, I ran that script as sys only and when it asked to enter GG schmea name ten I entered ‘ggate’ and it worked perfectly. When I’m trying to do dblogin it’s failing.

    GGSCI (devsx032) 1> dblogin userid ggate
    Password:
    ERROR: Failed to open data source for user GGATE.

    I tested the ggate user conenction to db from sqlplus and it had no issues.

    Thanks
    Reddy

    • Hi Reddy,
      Just to confirm check connection using sqlplus.
      From same command line where you start ggsci run please:
      sqlplus ggate/

      You have to connect to the database if all setting are proper.
      If you get a error please check your ORACLE_SID:
      env | grep ORACLE_SID

      It should show you proper value for your database.
      The next step should be checking privileges for user ggate in the database.
      Check the privilege connect.


      Gleb

  • Reddy says:

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

    -Reddy

  • Reddy says:

    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.

  • Reddy says:

    Thanks I’ll try that.

  • ian says:

    Hi,
    I have high chkpt at Replicat. when i check ggserr.log, I Found Warning about “Connection reset by peer”, below the capture.
    ===================================
    –> 2011-01-04 16:30:26 GGS WARNING 150 Oracle GoldenGate Delivery for Oracle, RC07.prm: Connection reset by peer.
    –> 2011-01-07 10:01:41 GGS WARNING 150 Oracle GoldenGate Delivery for Oracle, RC07.prm: Connection reset by peer.
    2011-01-07 10:51:36 GGS WARNING 150 Oracle GoldenGate Delivery for Oracle, RC07.prm: Connection reset by peer.
    =================================

    Pls advise,,
    Thanks,,

    • Hi Ian,
      I would check network between replicat and destination database (in case replicat and dest db are on different boxes.
      Also I see the warning is not so often.
      Do you have any problem from it?
      Is your replication working properly?

      –Gleb

  • Riaz says:

    HI Gleb,

    in Step 3, dblogin userid ggate
    This user ggate is the user in souce db ?

    Do we need to put tns entry of souce db in target tns file ? or else do we need to create gate user in target too ?

    • Hi Riaz,
      I have user ggate on both side because it keeps tables for ddl replication and checkpoint table.
      We need the tns entry for the source db on destination side if you will put extract to destination side.
      In our example replication extract process is on source side and we don’t need tns entry for the source on destination side.


      Gleb

  • Shiv Kumar Pandey says:

    I am looking out for GG Professionals.

  • satish says:

    Hi Otochkin,

    I fallowed the steps provided by you and i am successful in doing the replication of create table, insert , update , delete on tables in to source to Target. But when i drop the table in the source , table is not getting dropped in the target.

    Both source and Target database are of 10g 10.2.0.5.0

    Can you please let me know what is the reason behind this.

    Thanks and Regards,
    Satish.G.S

    • Hi Satish,
      It would be great if you posted your replicat and extract parameters files here for analysis.
      Also the reason can be found in replicat log. We can check records and found what had happened.

      –Gleb

  • satish says:

    I have one more problem. If i create a table without primary key in the source it is getting replicated to target.But if i create a table with primary key it is not getting replicated to target.

    Thanks and Regards,
    Satish

    • Hi Satish,
      Please put your replicat and extract parameters and we will try to find out the reason.
      Also it would be great to see replicat log.

      –Gleb

      • Ankur says:

        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

  • Jonah says:

    This may be a basic question…I have a two node RAC 11GR2 database and I am trying to set up the replication to the non rac database…we use ASM…
    When I start the extract I get the following error
    2011-01-25 09:19:09 WARNING OGG-01423 No valid default archive log destination
    directory found for thread 1.

    2011-01-25 09:19:10 WARNING OGG-01423 No valid default archive log destination
    directory found for thread 2.
    Where to configure this parameters…

    Your input will be highly appreciated..Jonah

    • Hi Johan,
      What version of Golden Gate do you use?
      You have to define ASM instance in listener and make sure the listener ready to handle connection to ASM.
      Add to listener ora something addition sid for ASM:

      (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/11.1.0/asm)
      (SID_NAME = +ASM1)

      –Gleb

  • Jonah says:

    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…

  • Adarsh Kumar says:

    Excellent post, Keep up good work
    Thanks
    Adarsh

  • Ankur says:

    Hi Gleb,

    I am very new to golden gate and I am trying your example as it is -
    I am getting this error and my Extracter in Abending with following error -

    2011-03-09 02:38:44 ERROR OGG-01496 Failed to open target trail file /home/oracle/app/oracle/product/gg/dirdat/lt000000, at RBA 2504.

    2011-03-09 02:38:44 ERROR OGG-01668 PROCESS ABENDING.

    My Ext param file on RACLAB01 is
    –extract group–
    extract ext1
    –connection to database–
    userid ggate, password qwerty
    –hostname and port for trail–
    rmthost raclab02, mgrport 7809
    –path and name for trail–
    rmttrail /home/oracle/app/oracle/product/gg/dirdat/lt
    –DDL support
    ddl include mapped objname sender.*;
    –DML
    table sender.*;

    Just wanted to know, how from my source host, it will able to write to destination host? via port mentioned 7809, via tns entry? In case I want to use my port 1521, on which listener is running, then how can I use?

    Thanks
    Ankur

    • The GoldenGate works through own ports defined in configuration for GoldenGate manager.
      In our example it was default port 7809.
      You have to start GoldenGate managers on both side.
      You can define your own ports for the managers but the ports should be different from listener port.


      Gleb

  • Sanjay Nayak says:

    Gleb,

    Any idea what this error may be? I am getting the same error on the extract as well as the replicat side.

    ERROR OGG-00662 Oracle GoldenGate Capture for Oracle, EXT1.prm: OCI Error allocating error handle (status = -1).

    I am trying OGG for the first time using our tutorial.

    • What version of GoldenGate and database do you use?
      It looks your OCI was not initialized properly.

      Did you put proper path for your oracle software?

      Also I have an error in configuration for extract, semicolon should be on very end of file.
      Please change:

      –DDL support
      ddl include mapped objname sender.*;
      –DML
      table sender.*

      to:
      –DDL support
      ddl include mapped objname sender.*
      –DML
      table sender.*;


      Gleb

  • Sanjay Nayak says:

    Gleb,

    Sorry for the late reply.

    I solved this problem. I had both the Oracle database 10g and Oracle database client installed on the test machine. I uninstalled the client and OGG worked as expected.

    I am not sure why OGG has a problem when both the db and client are installed.

    • I guess the problem could come from discrepancy between version for client shared libraries and database software client shared libraries. I think particulary problem was in OCI version and functionality.
      In Linux I would recommend to check environment LD_LIBRARY_PATH and PATH variables on Windows.


      Gleb

  • Irfan Rasheed says:

    I am getting following error when I put null in insert statement

    2011-03-31 18:30:45 ERROR OGG-01148 programming error, data type not supported for column TXID in table advoss.tblaudittrail.

    I am replicating MySQL 5.5.9 to Oracle 11g rel2

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


      Gleb

      • Irfan Rasheed says:

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

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

        • Hi,
          I would try GoldenGate 11 since it is supporting BIGINT for MySQL. Have a look difference between 10 and 11 versions.

          Here is supported data types for the GoldenGate 10:

          Supported data types
          ? CHAR
          ? VARCHAR
          ? INT
          ? DECIMAL
          ? FLOAT
          ? DOUBLE
          ? TIME
          ? DATETIME
          ? DATE
          ? TIMESTAMP
          ? BINARY
          ? TINYTEXT
          ? TINYLOB

          Here is supported data types for GG 11:

          CHAR
          VARCHAR
          INT
          TINYINT
          SMALL INT
          MEDIUM INT
          BIG INT
          DECIMAL
          FLOAT
          DOUBLE
          DATE
          TIME
          YEAR
          DATETIME
          TIMESTAMP
          BINARY
          VARBINARY
          TEXT
          TINYTEXT
          MEDIUMTEXT
          LONGTEXT
          BLOB
          TINYBLOB
          MEDIUMBLOB
          LONGBLOB
          ENUM
          BIT(M)


          Gleb

          • Irfan Rasheed says:

            Dear Gleb,
            I can insert normal data via replication, it only raises error when i try to insert null value in this column.
            I would really be thankful of you for helping me on this.

          • What goldengate version do you run?
            I would check also constraints on the table in oracle database.
            It would be great to get more information about the issue:
            table structure on mysql and oracle,
            insert statement.


            Gleb

          • Irfan Rasheed says:

            ***********************************************************************
            Oracle GoldenGate Capture for MySQL
            Version 11.1.1.0.3 Build 001
            Linux, x64, 64bit (optimized), MySQL Enterprise

            ==========================================
            Version of Installed MySQL found in OGG
            ————————————–
            Database Version:
            MySQL
            Server Version: 5.5.9-log
            Client Version: 6.0.0
            Host Connection: Localhost via UNIX socket
            Protocol Version: 10
            =======================================
            table structure in MySQL
            —————————————
            CREATE TABLE `tblaudittrail` (
            `AuditTrailID` int(10) unsigned NOT NULL AUTO_INCREMENT,
            `LoggedInUser` varchar(250) NOT NULL COMMENT ‘MySql logged in user who performed delete or update on a table. It contains the IP as well’,
            `Action` enum(‘DELETE’,’UPDATE’) NOT NULL DEFAULT ‘DELETE’,
            `TableName` varchar(128) NOT NULL COMMENT ‘Table name whose row is deleted’,
            `TablePrimaryKeyValue` varchar(250) NOT NULL COMMENT ‘Primary key value of the row of the table that is deleted’,
            `InsertionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `TXID` bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (`AuditTrailID`)
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Used for Audit of tables’

            ======================================

            ***********************************************************************
            Oracle GoldenGate Delivery for Oracle
            Version 11.1.1.0.0 Build 078
            Linux, x86, 32bit (optimized), Oracle

            ========================================
            Version of Installed Oracle Database found in OGG
            —————————————
            Database Version:
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
            PL/SQL Release 11.2.0.1.0 – Production
            CORE 11.2.0.1.0 Production
            TNS for Linux: Version 11.2.0.1.0 – Production
            NLSRTL Version 11.2.0.1.0 – Production

            ===========================================
            table structure for oracle
            —————————————–

            CREATE TABLE “ADVOSS”.”TBLAUDITTRAIL”
            ( “AUDITTRAILID” NUMBER(10,0) NOT NULL ENABLE,
            “LOGGEDINUSER” VARCHAR2(250 CHAR) NOT NULL ENABLE,
            “ACTION” VARCHAR2(4000 CHAR) DEFAULT ‘DELETE’ NOT NULL ENABLE,
            “TABLENAME” VARCHAR2(128 CHAR) NOT NULL ENABLE,
            “TABLEPRIMARYKEYVALUE” VARCHAR2(250 CHAR) NOT NULL ENABLE,
            “INSERTIONTIME” DATE DEFAULT SYSDATE NOT NULL ENABLE,
            “TXID” NUMBER(24,0),
            CONSTRAINT “PRIMARY” PRIMARY KEY (“AUDITTRAILID”)
            )

            =======================================
            I can insert following two insert statements without any issue
            —————————————-
            INSERT INTO `tblaudittrail` VALUES (1,’MIR3′,’UPDATE’,’audittrail’,’22′,’2011-03-30 13:29:20′,9);

            INSERT INTO `tblaudittrail` VALUES (2,’MIR2′,’UPDATE’,’audittrail’,’23′,’2011-03-29 06:17:20′,”);

            =======================================
            OGG extract fails when i execute following insert statement
            —————————————-
            INSERT INTO `tblaudittrail` VALUES (3,’mir3′,’UPDATE’,’audittrail222′,’25′,’2011-03-29 06:19:20′,NULL);

            =====================================
            error found in OGG extract report file
            —————————————

            Using the following key columns for source table advoss.tblaudittrail: AuditTrailID.

            Source Context :
            SourceModule : [ggvam.rec]
            SourceID : [/mnt/ecloud/workspace/Build_OpenSys_r11.1.1.0.3_001_[36467]/perforce/src/gglib/ggvam/cvamrec.cpp]
            SourceFunction : [mapToInternal]
            SourceLine : [2708]
            ThreadBacktrace : [10] elements
            : [/u01/app/oracle/ggs/extract(CMessageContext::AddThreadContext()+0x26) [0x7c5666]]
            : [/u01/app/oracle/ggs/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x7bc102]]
            : [/u01/app/oracle/ggs/extract(_MSG_ERR_MAP_NULL_FIELD(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x92) [0x767362]]
            : [/u01/app/oracle/ggs/extract(com_goldengate_vam::CVamRec::mapToInternal(file_def const*, com_goldengate_vam::CVamCol*, char**)+0xc5c) [0x70e26c]]
            : [/u01/app/oracle/ggs/extract(com_goldengate_vam::CVamRec::outputRow(file_def const*, char**)+0x3b3) [0x70ed93]]
            : [/u01/app/oracle/ggs/extract(com_goldengate_vam::CVamFmt::formatRecord(file_def const*, unsigned int, char*, int*)+0x229) [0x6e3a39]]
            : [/u01/app/oracle/ggs/extract [0x5993d6]]
            : [/u01/app/oracle/ggs/extract(main+0xff0) [0x59aea0]]
            : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x351d01d994]]
            : [/u01/app/oracle/ggs/extract(__gxx_personality_v0+0x19a) [0x51086a]]

            2011-04-07 14:43:25 ERROR OGG-01148 programming error, data type not supported for column TXID in table advoss.tblaudittrail.

          • Hi Irfan,
            I think it is error for oracle support.
            I don’t have a solution for it.
            Maybe some addition information could be found using strace but I would open SR for oracle support about the issue.
            In first I don’t see version MySQL 5.5.9 in certification Matrix for the GoldenGate.
            It looks some features are still working incorrectly.


            Gleb

        • Irfan Rasheed says:

          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.

          • Irfan Rasheed says:

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

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

    Gleb,

    I have progressed in my testing and am facing an interesting problem. I am trying out an app migration from MS SQL and Oracle. This app uses 100s of tables. The only difference is that in Oracle there are extra columns but only towards the end. The sequence of columns remains the same.

    When I ran an Extract process with SOURCEISTABLE, it failed as it is unable to handle LOBs. I therefore dumped data to the destination using the “Loading data from file to Replicat” technique.

    However, because of the number of columns mismatch, Replicat does not merge the data.

    I am aware of SOURCEDEFS but doing the same for a number of tables is time consuming.

    Is there an easier method?

    • Hi Sanjay,
      Did you try the COLMATCH parameter.
      I am not absolutely sure it is exactly it what you are looking for.
      But I would try to use it.
      In any way you have to point to GG what differences you have.
      Did you try to automate creating file for SOURCEDEFS ?


      Gleb

      • bijitesh says:

        Hi Gleb,

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

        Thanks
        Bijitesh

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

          • bijitesh says:

            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

          • 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

      • Sanjay Nayak says:

        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.

  • mkmansoori says:

    Thanks for great job , please keep it up.

  • mkmansoori says:

    Hello Gleb,

    Environment is as below:

    RAC 2 nodes
    Oracle RAC 11gr2
    ASM
    Enterprise Linux Server release 5.5 (Carthage)

    I am new to GG and I need to install and setup Oracle GoldenGate 11g Release 1 (11.1.1).

    Is there any such self-explainatory blog available for my requirement?

    can anyone give me full steps from scratch ?

    Confusions:

    1.Do we need to install GG on all nodes?
    2.db_name and instance name can be different on source and destination ?
    3.Do we need to set any specific parameters in pfile/spfile for GG on source and destination?
    4.any specific entries in listener.ora and tnsnames.ora for connectivity between source and destination?
    5.Do we to configure anything on ASM for GG installation?

    responses will be highly appreciated.

    Reagrds,

    • Hi mkmansoori,

      >1.Do we need to install GG on all nodes?
      GG works on one node on one time. You must stop it on one node before start on another.
      I would advise to put GG binares to shared FS.
      You can potentially create resource in clusterware which will handle start/stop for GG manager.

      >2.db_name and instance name can be different on source and destination ?
      it doesn’t matter for GG.

      >3.Do we need to set any specific parameters in pfile/spfile for GG on source and destination?
      there is no special parameters for RAC database

      >4.any specific entries in listener.ora and tnsnames.ora for connectivity between source and destination?
      No specific entries except listener for ASM instances

      >5.Do we to configure anything on ASM for GG installation?
      Add listener for ASM.

      Also you have to use THREADOPTIONS parameter in case RAC source.

      –Gleb

  • Gulmira Lomanova says:

    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.

    • Irfan Rasheed says:

      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.

  • Irfan Rasheed says:

    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?

  • Irfan Rasheed says:

    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

  • asfd says:

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

  • Hi

    In the example, what is “db2″, on the line
    rmthost db2, mgrport 7809

    I don’t know if is a host reference, alias from tnsnames or another thing.

    Have an error to startup the ext1 service

    –extract group–
    extract ext1
    SETENV (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1″)
    Set environment variable (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
    SETENV (ORACLE_SID = “diuca”)
    Set environment variable (ORACLE_SID=diuca)
    –connection to database–
    userid ggate, password ******
    –hostname and port for trail–
    rmthost db2, mgrport 7809

    Source Context :
    SourceModule : [er.extrout]
    SourceID : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34086]/perforce/src/app/er/extrout.c]
    SourceFunction : [get_remote_host]
    SourceLine : [3303]
    ThreadBacktrace : [8] elements
    : [/u01/app/oracle/product/gg/extract(CMessageContext::AddThreadContext()+0x26) [0x62ab66]]
    : [/u01/app/oracle/product/gg/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int,
    ...)+0x7b2) [0x621602]]
    : [/u01/app/oracle/product/gg/extract(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char c
    onst*, CMessageFactory::MessageDisposition)+0x92) [0x5e1842]]
    : [/u01/app/oracle/product/gg/extract(get_remote_host(char*)+0x696) [0x812bfe]]
    : [/u01/app/oracle/product/gg/extract [0x51501b]]
    : [/u01/app/oracle/product/gg/extract(main+0x121) [0x517cf1]]
    : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x357661d994]]
    : [/u01/app/oracle/product/gg/extract(__gxx_personality_v0+0x1ea) [0x4e62aa]]

    2011-05-17 15:09:07 ERROR OGG-00303 Could not establish host TCP/IP address.

    2011-05-17 15:09:07 ERROR OGG-01668 PROCESS ABENDING

    Thank’s in advance.

    Leo.

  • Hi Leonel,
    db2 is remote host on the line.
    You have to define port and host where your remote GG process works.

    As you can see in error manager cannot establish connection to the host.
    Put hostname for your remote host.

    –Gleb

  • asfd says:

    Hi Gleb,
    Yes I configured asm instance in listener.ora as below:

    $ cat listener.ora
    LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
    LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
    LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
    LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = +ASM1)
    (ORACLE_HOME = /u01/app/11.2.0/grid)
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = +ASM2)
    (ORACLE_HOME = /u01/app/11.2.0/grid)
    )
    )
    $

    $ cat tnsnames.ora
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    ASM =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxdata-scan)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = +ASM2)
    )
    )

    $

    Plz suggest me ..

    Cheers !!!!

    asfd,

    • Hi asfd,
      Have you checked services running on your listener?
      Here is command:
      lsnrctl service

      Please try to connect using sqlplus and values from your parameter file for extract.


      Gleb

      • asfd says:

        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,

        • 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

  • asfd says:

    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

    • Chandra says:

      Hi Afsd,

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

      Regards
      Chandra

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

  • Ravi Kasibhatla says:

    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

  • Muskan says:

    Hi ,

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

    Thanks in advance,
    Ekta

  • Eric says:

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

  • Roberto says:

    Hi Gleb,

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

    Segmentation fault

    In replicate process.

    Regards,Roberto

  • Roberto says:

    can you give me a recommendation?

    Regards, Roberto

  • Ravi says:

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

    • Roberto says:

      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

    • 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

  • Roberto says:

    Hi Gleb,

    you have seen east error before?

    Segmentation fault.

    Thanks,
    Roberto

  • Ravi says:

    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

  • Ravi says:

    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

  • Ravi says:

    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

  • Ravi says:

    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

  • 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

  • Ravi says:

    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

  • Ravi says:

    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

  • Ravi says:

    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

    • 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

  • rrk says:

    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

  • venkat says:

    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

  • Odai says:

    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 .

  • aslihan says:

    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?

  • Siva says:

    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

  • Rupesh says:

    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.

  • Amol Bhor says:

    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

  • Doro says:

    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!

  • zikri (zach) a ince says:

    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.

  • Jeny says:

    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.

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

  • whyt says:

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

  • Alex says:

    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.

  • Vijay Kumar says:

    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

    • 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

      • Vijay Kumar says:

        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.

        • Vijay Kumar says:

          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
          *

          • Vijay Kumar says:

            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.

          • Vijay Kumar says:

            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

  • madhu says:

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

    • 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

      • madhu says:

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

        • Gleb Otochkin says:

          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

  • madhu says:

    Can you please give sample steps
    after .\ggsci

  • madhu says:

    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

  • madhu says:

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

    • Gleb Otochkin says:

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

      Regards,
      Gleb

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>