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 on the target database the same table is present only in structure without any data. We will be loading the 72 rows in this example from the source database to the target database using GoldenGate Direct Load method.
On Source
1) Create the Initial data extract process ‘load1’
GGSCI (redhat346.localdomain) 5> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.
Since this is a one time data extract task, the source of the data is not the transaction log files of the RDBMS (in this case the online and archive redo log files) but the table data itself, that is why the keyword SOURCEISTABLE is used.
2) Create the parameter file for the extract group load1
EXTRACT: name of the extract group
USERID/PASSWORD: the database user which has been configured earlier for Extract ( this user is created in the source database)
RMTHOST: This will be the IP address or hostname of the target system
MGRPORT: the port where the Manager process is running
TABLE: specify the table which is being extracted and replicated. This can be specified in a number of ways using wildcard characters to include or exclude tables as well as entire schemas.
GGSCI (redhat346.localdomain) 6> EDIT PARAMS load1
EXTRACT load1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE sh.products;
On Target
3) Create the initial data load task ‘load2’
Since this is a one time data load task, we are using the keyword SPECIALRUN
GGSCI (devu007) 1> ADD REPLICAT load2, SPECIALRUN
REPLICAT added.
4) Create the parameter file for the Replicat group, load2
REPLICAT: name of the Replicat group created for the initial data load
USERID/PASSWORD: database credentials for the Replicat user (this user is created in the target database)
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
MAP: with GoldenGate we can have the target database structure entirely differ from that of the source in terms of table names as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which is same in this case
GGSCI (devu007) 2> EDIT PARAMS load2
“/u01/oracle/software/goldengate/dirprm/rep4.prm” [New file]
REPLICAT load2
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP sh.customers, TARGET sh.customers;
On Source
SQL> select count(*) from products;
COUNT(*)
———-
72
On Target
SQL> select count(*) from products;
COUNT(*)
———-
0
On Source
5) Start the initial load data extract task on the source system
We now start the initial data load task load 1 on the source. Since this is a one time task, we will initially see that the extract process is runningand after the data load is complete it will be stopped. We do not have to manually start the Replicat process on the target as that is done when the Extract task is started on the source system.
On Source
GGSCI (redhat346.localdomain) 16> START EXTRACT load1
Sending START request to MANAGER …
EXTRACT LOAD1 starting
GGSCI (redhat346.localdomain) 28> info extract load1
EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE
GGSCI (redhat346.localdomain) 29> info extract load1
EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE
On Target
SQL> select count(*) from products;
COUNT(*)
———-
72
Coming Soon! – Creating an Online Extract and Replicat Group for Change Synchronization …..
Gavin Soorma
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,
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
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
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
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
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
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.
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.*;
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.
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 …..
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.
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
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.
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)
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.
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
But still the error same. With the “start c1” and
“start r1” in source and target.
Thanks.
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.
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.
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.
What I can do to accept the parameter?
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
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
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.
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
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
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
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.
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!
Good reply Ravi …
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
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,
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.*
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
Hi Gabriel – I will test this out and get back to you in a day or two – thanks
Thanks gavin
extract issue was resolved after adding semi colon
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.au/2010/08/using-goldengate-to-replicate-between-databases-on-same-host/
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
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.
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.