News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle GoldenGate Tutorial 4 – performing initial data load

  • Posted by Gavin Soorma
  • On February 15, 2010
  • 40 Comments
  • direct load, extract, GoldenGate, replicat

This example illustrates using the GoldenGate direct load method to extract records from an Oracle 11g database on Red Hat Linux platform and load the same into an Oracle 11g target database on an AIX platform.

The table PRODUCTS in the SH schema on the source has 72 rows and

You need to be logged in to see this part of the content. Please Login to access.
 3

40 Comments

Ashish
  • Feb 17 2010
Can we use Oracle Golden gate to replicate the data from Oracle source to SQL Server target ? What are the steps invloded in setting-up this replication ? Any help in this regard will be really appreciated. Thanks,
Gavin Soorma
  • Feb 18 2010
Hi - yes - GoldenGate can be used to replicate between different Oracle versions as well as between heterogenous database platforms - like Oracle to Sql server, or Db2 or MySQL etc. You will need to install the Goldengate software on the Windows machine where sql server is running - create an ODBC data source and a few other steps like creating the Manager service - the procedure is available Oracle Goldengate documentation - will try it out myself and drop you a line if I have any luck - thanks
Mahesh
  • Feb 19 2010
Hi, We have done setup of golden gate but after made configuration of Initial load setup I am not able to replicate the emp table. Log Read Checkpoint is not started. EXTRACT LOAD1 Initialized 2010-02-18 16:45 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE Please help in this regard will be really appreciated Mahesh
Gavin Soorma
  • Feb 19 2010
Hi mahesh - can you check the target table and see the row count - since this is not a continuous extract process, once the initial load is completed the process will stop - check the GoldenGate log as well via the VIEW GGSEVT or VIEW REPORT (extract name) and see if you can see any errors
Mahesh
  • Feb 19 2010
Thanks for update immediately. VIEW GGSEVT :- 2010-02-19 10:41:13 GGS INFO 310 Oracle GoldenGate Capture for Oracle, load1.prm: EXTRACT LOAD1 started. 2010-02-19 10:41:18 GGS WARNING 147 Oracle GoldenGate Capture for Oracle, load1.prm: EXTRACT task LOAD2 abended : Could not open checkpoint file /RAPhome/oracle/GG/dirchk/REP4.cpr, mode 1 (error 2, No such file or directory). 2010-02-19 10:41:18 GGS ERROR 147 Oracle GoldenGate Capture for Oracle, load1.prm: EXTRACT abending. 2010-02-19 10:41:18 GGS ERROR 190 Oracle GoldenGate Capture for Oracle, load1.prm: PROCESS ABENDING. So I chenge at target side load2 parameter file REPLICAT rep4 to REPLICAT load2 REPLICAT load2 USERID ggs_owner, PASSWORD ggs_owner ASSUMETARGETDEFS MAP sh.customers, TARGET sh.customers; Now it works fine. Thanks, Mahesh
Gavin Soorma
  • Feb 22 2010
Hi Ashish - yes - we can use GoldenGate to replicate from source SQL Server to Oracle and vice versa as well. I have tested the migration from SQL Server 2005 to Oracle 11g using SQL Developer to create the migration scripts and then installing GOldengate on the windows server where SQL Server 2005 is running - I have not tried the other way around but it should be the same - will provide a use case scenario shortly - thanks
Viral
  • Mar 3 2010
Very good tutorial but can you configure GoldenGate to replicate entire database and not just one table or schema? if so, Can you show us the steps on how to accomplish this? I have been trying to replicate the entire database on Oracle 10gR2 but have not had any luck. Any help will be appreciated. Thank you.
GAVIN
  • Mar 5 2010
Hi Viral - I have used ths parameter file to replicate all the demo schemas in a particular database - I think you have to specifu which schemas and tables you would like to replicate and if you see the documentation, you cannot specify certain 'reserved' schemas like SYS,SYSTEM,OUTLN,PERFSTAT etc EXTRACT myext USERID ggs_owner, PASSWORD ggs_owner RMTHOST redhat346, MGRPORT 7809 RMTTRAIL /u01/oracle/ggs/dirdat/my DISCARDFILE discard.txt, APPEND TABLE sh.*; TABLE hr.*; TABLE pm.*; TABLE oe.*; TABLE ix.*;
adriana
  • Mar 9 2010
Hi I’m trying to set up SQL Server 2005 (without CU6) as a source. Therefore. I configured the DSN (ODBC for SQL Server) and I’m trying to DBLOGIN the database, however I cannot. This is the error I get: 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. Do you know which should be the right command so I can connect to the SQL Server Database. Thanks.
Gavin Soorma
  • Mar 10 2010
Hi Adriana - I think the dblogin clause with the username and password is only required for an Oracle database. Assuming you have set up your data source ODBC correctly and tested that it is working fine and connecting to the SQL Server database, you can just use "SOURCEDB DSN_SQL" - try that and let me know if it works .....
adriana
  • Mar 10 2010
Hi I check the ODBC AND using SOURCEDB DSN_SQL. I receive the following error: DBLOGIN SOURCEDB dns_sql, USERID adriana, PASSWORD adriana ERROR: Unrecognized parameter (SOURCEDB), expected USERID What can I do? Thanks.
Gavin Soorma
  • Mar 11 2010
Hi Adriana, This is an example of an extract parameter file I used to replicate from Windows SQL Server 2005 to Oracle on AIX ..... EXTRACT ext1 sourcedb sql2005 TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT RMTHOST devu007, MGRPORT 7809 RMTTRAIL /u01/oracle/software/goldengate/dirdat/ms TABLE SALES.CUSTOMER; Regards, Gavin Soorma Senior DBA Oracle Specialist Platform Operations IT Infrastructure Information Technology Division Bankwest 199 Hay Street Perth WA 6000 Telephone: (08) 9449 8930 Email: gavin.soorma@bankwest.com.au "comment-reply@wo To rdpress.com" gavin.soorma@bankwest.com.au Subject [Oracle DBA - Tips and Techniques] Comment: "Oracle GoldenGate 10/03/2010 10:33 Tutorial 4 - performing initial PM data load" Please respond to comment+r7pehtipb j5baopp@comment.w ordpress.com
adriana
  • Mar 30 2010
I did what you told me but errors continue. I did as you mention in your blog. -But when I run "start extract" does not work, show the following: 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 - With the director shows me this error: Make sure the userid and password are correct and that the database server is running. I have tried to target sql and mysql and I get the same errors. I could help. Thanks.
Gavin Soorma
  • Mar 30 2010
Hi Adriana - can you give me the results of a simple SQL statement like 'SELECT COUNT(*) FROM ;' - run this on both source database and on the target database - also send me the results of GGSCI command ' view params c2' (on source side) and 'view params d1' ( this is on replicat side)
adriana
  • Mar 31 2010
Hi. This is the extract and replicat EXTRACT c1 USERID adriana, PASSWORD adrianita7 RMTHOST localhost, MGRPORT 7810 RMTTASK replicat, GROUP d1 TABLE adriana.empleados; REPLICAT d1 USERID adriana, PASSWORD adriana ASSUMETARGETDEFS MAP adriana.empleados, TARGET adriana.empleados; -The source is oracle y the target is SQL 2005 - Running SELECT COUNT(*) FROM empleados In oracle =1 In sql =0 thanks.
Gavin Soorma
  • Mar 31 2010
Hi Adriana - if your target is SQL Server, then in the replicat parameter file instead of USERID and PASSWORD (this is only for Oracle), you need to put SOURCEDB along with the ODBC data source like SOURCEDB sql2005 try that out and let me know - thanks
adriana
  • Mar 31 2010
But still the error same. With the "start c1" and "start r1" in source and target. Thanks.
adriana
  • Apr 1 2010
HI Using this extract: EXTRACT c1 USERID adriana, PASSWORD adrianita7 RMTHOST localhost, MGRPORT 7810 RMTTASK replicat, GROUP r1 TABLE adriana.empleados; And this Replicat: REPLICAT r1 SOURCEDB dns_sql ASSUMETARGETDEFS MAP adriana.empleados, TARGET adriana.empleados; This is the error that shows in the log of the source: 2010-03-31 10:51:38 GGS WARNING 147 Oracle GoldenGate Capture for Oracle, C1.prm: EXTRACT task R1 abended : Unrecognized parameter: SOURCEDB. Parameter could be misspelled or unsupported. 2010-03-31 10:51:38 GGS ERROR 147 Oracle GoldenGate Capture for Oracle, C1.prm: EXTRACT abending. 2010-03-31 10:51:38 GGS ERROR 190 Oracle GoldenGate Capture for Oracle, C1.prm: PROCESS ABENDING. It seems that the error is related to the “SOURCEDB”. Thanks.
Gavin Soorma
  • Apr 1 2010
Hi Adriana - sorry my mistake - since your target is SQL Server, you need to use TARGETDB instead of SOURCEDB - also what kind of authentication you are using for SQL Server? - if you are using OS authentication, then just TARGETDB is fine specifying the ODBC data source - otherwise you will have to provide the USERID and PASSWORD as well - see page 337 of the Windows and Unix Reference Guide available in the Goldengate documentation. TARGETDB mydb, USERID ggs, PASSWORD ggs123 Also, you need to take into account the schema names in the Oracle and SQL Server database when use specify the MAP and TARGET values as well.
adriana
  • Apr 6 2010
HI Gavin With TARGETDB: REPLICAT r1 TARGETDB dns_sql, USERID adriana, PASSWORD adriana ASSUMETARGETDEFS MAP adriana.empleados, TARGET adriana.empleados; I get the following error: 2010-04-05 15:27:55 GGS ERROR 101 Oracle GoldenGate Delivery for Oracle, R1.prm: Unrecognized parameter: TARGETDB. Parameter could be misspelled or unsupported. thanks.
adriana
  • Apr 6 2010
What I can do to accept the parameter?
sean
  • Apr 22 2010
Adriana, It sounds like you are not running GoldenGate for SQL Server but GG for Oracle. What does it say when you open GGSCI? -Sean
Raj
  • Apr 27 2010
I am using teradata as the target database and hitting the same issue. [ggs@db05]/u36/ggs/app/teradata_ggs>ggsci Oracle GoldenGate Command Interpreter for Teradata Version 10.4.0.47 Build 001 Solaris, sparc, 64bit (optimized), Teradata ODBC on Mar 9 2010 16:08:49 Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved. GGSCI (usphlsodb05) 2> dblogin targetdb AS_EDW_Dev userid ggs_user, password ggs_user ERROR: Unrecognized parameter (TARGETDB), expected SOURCEDB. all my configuration files looks ok. How to resolve this connection error? Thanks for your time. Raj
Gavin Soorma
  • Apr 28 2010
Hi Raj - if you are using the keyword DBLOGIN then you should not be using the clause TARGETDB. If you are connecting to an Oracle database then it should be : DBLOGIN USERID xxx, PASSWORD xxx For SQL Serveror other databases that use ODBC you need to use : DBLOGIN SOURCEDB But where in your configuration files are you using DBLOGIN? - this should really be only used when you wish to connect to the database to issue any command that affects the database - for example if you want to delete a replicat process, you have to stop it first and you do that by issuing a DBLOGIN command to connect to the database and then once connected we can stop the replicat process. Please provide me the extract or replicat parameter file contents where you are using the DBLOGIN command. Thanks.
Ashok
  • Jul 18 2010
Hi Gavin, We have a Oracle 11g RAC database for which we have to set up the Golden gate replication. But the source database has been resetlogs couple of times. So v$database_incarnation is showing 3 records. When we are setting up the Golden gate, it is looking for the archive logs of the first incarnation. Is there a way to configure Golden Gate so that it will look for only current incarnation's archive logs ? We have already setup the Golden gate replication for residing in the same RAC nodes which dont have multiple incarnations. Ashok
Gavin Soorma
  • Jul 20 2010
Hi Ashok - I will try and simulate this in my environment - what exact error are you getting when you run the extract .... can you try running the extract specifying the time you want to start extracting records from ... maybe try : ALTER/ADD EXTRACT accounts, THREAD 4, BEGIN YYYY-MM-DD
Ravikumar
  • Aug 1 2010
There is a way to do this. You need to configure the extract in archived log mode and then you need to create a link between the physical name of the archive log(Archive log with old incarnation) and the archive log which extract is looking for(archive log with new incarnation). Thanks, Ravi
Ravi
  • Aug 2 2010
Hi Ashok, I think initially you have to take a full database export using flashback_scn which you get by select dbms_flashback.get_system_change_number from dual; and then import it into target database. Then configure the extract and replicat processes using threads keyword for RAC environment. In the target, you use start replicat rep1, atcsn scn# or aftercsn scn#. So that it will start replicating data from that scn. I think this should work. Let me know if it works.
Aakash
  • Aug 2 2010
Hi Gavin, How do we use the methods that you have described here when we have to synchronize two different databases using GoldenGate but they are on the same host? Or the case that the remote host is running multiple databases, how do we specify which database to go to? Thanks!
Gavin Soorma
  • Aug 12 2010
Good reply Ravi ...
Gavin Soorma
  • Aug 12 2010
Hi - I will try and simulate this on the same machine and let you know ... but if you have multiple databases on the same server , you can used the TNS ALIAS to connect to the right GoldenGate user for example in the Extract Parameter file - USERID ggs_owner@orcl.world, PASSWORD ggs_owner
Gabriel
  • Aug 13 2010
Hello, What would I need to do to replicate a table from a source database to a target database on the same server. Here are my parameter files extract load1 userid ggs_owner, password ggs_owner rmthost .ca, mgrport 7576 rmttask replicat, group load2 table gsdba.mytesttable; replicat load2 userid ggs_owner, password ggs_owner assumetargetdefs map gsdba.mytesttable, target gsdba.mytesttable; port 7576 userid ggs_owner, password ggs_owner purgeoldextracts /t01/software/gg/dirdat With this configuration the data is reloaded in the source table Thank you,
neerudba
  • Aug 20 2010
I have a linux box with 2 10g databases i created 2 seperate gg installations and want to enable replication between the 2 database on the same server I am getting the same problem of "PROCESS ABENDING" error where EXTRACT and REPLICAT processes are not starting Posting the details of the source and target files source : ---edit params mgr PORT 7809 ---edit params ext1 --extract group-- extract ext1 --connection to database-- userid ggate, password ggate --hostname and port for trail-- rmthost datapatrolserver.vsnl.net, mgrport 7810 --path and name for trail-- rmttrail /fs02/trn2/gg/dirdat/lt --DDL support ddl include mapped objname sender.*; --DML table sender.*
Gavin Soorma
  • Aug 23 2010
Hi - what is the error you are getting .....? - run the view report ext1 and view report rep1 commands from ggsci and you should see some details of the actual error you are facing
Gavin Soorma
  • Aug 23 2010
Hi Gabriel - I will test this out and get back to you in a day or two - thanks
neerudba
  • Aug 24 2010
Thanks gavin extract issue was resolved after adding semi colon
Gavin Soorma
  • Aug 25 2010
Hi Gabriel - I have just posted something on this - kindly have a look and see if this works in your environment - thanks https://gavinsoorma.com/2010/08/using-goldengate-to-replicate-between-databases-on-same-host/
Nidre
  • Jan 27 2011
Hi gavin thxx for good tutorial. But i have a problem with it. I run START EXTRACT LOAD1 with no errors. But then i type VIEW REPORT LOAD1 there is an error 2011-01-27 14:50:32 ERROR OGG-00901 Failed to lookup object ID for table GGS_OWNER. BOOKS. 2011-01-27 14:50:32 ERROR OGG-01668 PROCESS ABENDING. my PARAMS FOR LOAD1 EXTRACT load1 USERID ggs_owner, PASSWORD ggs_owner RMTHOST target, MGRPORT 7809 RMTTASK replicat, GROUP load2 TABLE books; and for LOAD2 REPLICAT load2 USERID ggs_owner, PASSWORD ggs_owner ASSUMETARGETDEFS MAP books, TARGET books; and if i start REPLICAT LOAD2 from target i got this error from the log 2011-01-27 14:54:05 ERROR OGG-00446 Invalid data source -1 in checkpoint file /u01/app/oracle/product/ggs/dirchk/LOAD2.cpr. Thx
Nidre
  • Jan 27 2011
Sorted it out.I managed that i have crated my tables at sysDBA not the ggs_owner. Now there are no errors but they are not worrking neither. Both load1 and load2 report says "no data extracted". I have eatcly same db created in source and target db.In ggs_owner user.
Arun
  • Jun 2 2011
Hi gavin, its a nice tutorial on inital load :-) what will be the plan of action on initial loading for active active replication. Can you help me with some documentation on active active goldengate replication if you have.

Leave Reply

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