OGG with SQLEXEC - GoldenGate

Hi,
Can any one clarify on this? In case if im using SQLEXEC parameter in inside of the datapump, whether the particular query run against the EXTTRAIL file or database? the datapump source desitination is Trail file , then how the sqlexec work on the database tables?
Thanks in Advance.
Annamalai. 

Hi.
SQLEXEC will use the connection defined by USERID xxx, PASSWORD yyy and run the query against the database it is connected to. It does not query the Trail file.
Regards,
-joe 

Thanks Joe. 

.

Related

Data Loding by using database link.

Dear All,
I am New to Oracle GoldenGate..
How to use database link in GG??
bellow is the my scenario....
I have one source table in client machine (.28) and i nedd to laod data into target machine (.149) through database link.
i am running GG application in (.28) and create database link called docqvoice_db alos.
i have used bellow extract code also
REPLICAT load6
USERID sh, PASSWORD sh
ASSUMETARGETDEFS
MAP sh.products, TARGET products#docqvoice_db;
i have creates source structure in target also.
i got the error
WARNING OGG-01194 Oracle GoldenGate Capture for Oracle, LOAD5.prm: EXTRACT task LOAD6 abended : Table PRODUCTS#DOCQVOICE_DB does not exist in target database.
please advice...
thanks in advacne...
Zakeer Hussain
zakeer7007#gmail.com
cell No +91-9886006549 
Zakeer,
You have two choices. First choice is what you're doing but your syntax is off. It should be:
REPLICAT load6
USERID sh#docqvoice_db, PASSWORD sh
ASSUMETARGETDEFS
MAP sh.products, TARGET sh.products;
Second choice is to create a view on the source using the database link to the target and then just map the view as in:
REPLICAT load6
USERID sh, PASSWORD sh
ASSUMETARGETDEFS
MAP sh.products, TARGET sh.view_on_products;
Good luck,
-joe 
Dear Joe,
Thanks for your response.

new table

Hi,
as per the requirment , i'm doing DML replication from one source to three target databases, everything works fine, now i want to add new table in the extract parameter without stopping the GG ? Is it possible ? if we given schemaname.* in the DML replication whether the new table will be added in the replication part or not? note : i have added the supplemental log add in DB level? please assist me how to add the new table name in the extract prm file? 
Annamalai,
Yes this is possible.
Have a look at DDL replication.
If you install/configure DDL replication then you can create a table on the source system, and the same table gets created on your target system(s).
The extract can also do the ADD TRANDATA (with the DDLOPTIONS ADDTRANDATA parameter).
Have a look at the Oracle GoldenGate installation and setup guide (chapter 3), on how to install DDL, and at the DDL parameter in the reference guide.
regards,
Eric 
I think you cannot add table in an extract parameter file without stopping it. You need to stop it before you add/modify parameters in it.
If you give schemaname.* in the DML replication you cannot have the table in the replicat side. You need to specify DDL parameters in both extract and replicat.
If you don't want to stop GG then you better add one more extract parameter file and specify the table in it.
Thanks. 
Hi thanks a lot both of them,
Eric,
Im not using DDL option in my requirment because i nee to to turn off recycle bin , my exact scenario is,
The replication is working fine now from source test1 schema to target test1 schema , now i want to add one new table in source under test1 schema ... if the same table will be created in the target side DB with test1 schema , whether new table changes replicated to target side or not? kindly clarify
Note : when ever i need a DDL changes, i can run the same DDL operation script manually in both side .
thanks in advance
Edited by: Annamalai on Feb 7, 2011 3:29 PM 
As you are doing DDL replication, the new table will be created at target and will continue replicating any new DDL or DML operations performed on that table.

Table doesn't exist, rep cannot start

Hi,
I have the following error
2011-09-30 17:38:34 ERROR OGG-00199 Oracle GoldenGate Delivery for Oracle, rep1.prm: Table SIEBEL.JMT_PWD does not exist in target database.
so the rep1 cannot start
how can I solve this ?
Thanks for your help
OGG 11.1.1 on AIX
Oracle 9.2 
How are you using it in Replicat? (i.e., post your parameter file) 
Hi Steven,
below my rep1.prm
replicat rep1
setenv (oracle_sid=T3W)
userid ogg,password xxx
handlecollisions
assumetargetdefs
discardfile /tst/data/ogg/discards/resp.dsc,purge
reperror default discard
map SIEBEL.*, target SIEBEL.*;Thanks for your help 
Okay, does that table exist in the target? The schema to schema map is correct, but if there is a table in the source trying to be mapped to in the target, and there is nothing to map to, replicat will error out. 
Steven,
I dropped the table in the source db and in the targget db (test table)
how can i start the replicate ?
Thanks for your help 
GGSCI xx> start replicat name_of_your_replicat 
Steven,
rep1 doesn't start with the message OGG-00199
I dropped the table in the source db and in the targget db (test table)
how can i start the replicate ?
Thanks for your help 
Do you have trails waiting to be applied, with references to the table? If you need those trails to be applied, then add in exception handling to account for the missing table. 
Steven,
I don't need this table (test table)
It is in a test env.
how can I start my replicate ....
Thanks for you help 
Again, do you have any trails waiting to be applied? You need to move past those. Or, just drop and re-create the process group (having extract starting now, and replicat starting now).
You can also go through the tutorial to get a working environment running. Have you tried that?
https://apex.oracle.com/pls/apex/f?p=44785:24:4300991440933995::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:5340,2

Goldengate Insert duplicate records.

Hi,
I have configured goldengate in oracle 11g as below configuration.
Primary
ORACLE 11G-STD EDITION-LINUX 6.2
Standby
ORACLE 11G-ENTERPROSE-Windows 7.
Primary ==to==>standby====>all replication(DDL,DML, PROCEDURE,ETC.,)
Standby==to==>Primary=====>DML Replication.
Primary to standby working fine(I insert one record perfectly it insert into standby).
But in standby to primary, If i insert 1 record in standby db, It record many records in primary like insert into table1 as select * from table1;
How can i avoid this?? Please help me.
If any doubt pls ask and help me to solve this issue.
Thanks in advance. 
user8665771 wrote:
Hi,
I have configured goldengate in oracle 11g as below configuration.
Primary
ORACLE 11G-STD EDITION-LINUX 6.2
Standby
ORACLE 11G-ENTERPROSE-Windows 7.
Primary ==to==>standby====>all replication(DDL,DML, PROCEDURE,ETC.,)
Standby==to==>Primary=====>DML Replication.
Primary to standby working fine(I insert one record perfectly it insert into standby).
But in standby to primary, If i insert 1 record in standby db, It record many records in primary like insert into table1 as select * from table1;
How can i avoid this?? Please help me.
If any doubt pls ask and help me to solve this issue.
Thanks in advance.Can you please post your configuration parameters? So that we can help you. Thanks!
From Standby->Primary (Both replicat on Primary and extract on Standby) 
Hi,
Standby
=====
GGSCI (standdb) 2> view params dpump
EXTRACT dpump
USERID scott, PASSWORD tiger
RMTHOST 192.168.237.130, MGRPORT 7878
RMTTRAIL /home/oracle/app/dirdat/rt
PASSTHRU
TABLE scott.*;
GGSCI (standdb) 3> view params ext2
EXTRACT ext2
USERID scott, PASSWORD tiger
EXTTRAIL E:\app\ggscii\dirdat\lt
TABLEEXCLUDE scott.GGSCHKPT
TABLE scott.*;
Primary
======
REPLICAT rep2
HANDLECOLLISIONS
ASSUMETARGETDEFS
USERID scott, PASSWORD tiger
DISCARDFILE /home/oracle/app/dirrpt/rep201.DSC, PURGE
MAP scott.*, TARGET scott.*; 
You need to use the EXCLUDEUSER parameter in the extracts and select the user that is aplying the changes for OGG. Else the data will "ping-pong" all the time.
TRANLOGOPTIONS EXCLUDEUSER <GGSUSER>
Try it once and let me know if it helped.
Edited by: N K on 03-abr-2012 6:31 
Hi,
Thank you very much. Now it's working properly. Currently i am monitoring the data.
But if i want replicate the gguser data also, what can i do?
Also currenlty i am configuring different checkpoint table as like below,
primary ==to==>  standby  is <gguser>.ggschkpt
standby ==to==>primary is  <gguser>.ggschkpts
Is it best way to configure or am i do wrongly ?
Is there possible to configure different gguser for primary to standby and standby to primary? ===>I know it makes complex.Just for asking whether gg will work by this way?
Currently i gave dba privs to gguser (Since ddl need to replicate right). Need to give this privs or not required?
Please guide me. 
user8665771 wrote:
Hi,
Thank you very much. Now it's working properly. Currently i am monitoring the data.
But if i want replicate the gguser data also, what can i do?
Also currenlty i am configuring different checkpoint table as like below,
primary ==to==>  standby  is <gguser>.ggschkpt
standby ==to==>primary is  <gguser>.ggschkpts
Is it best way to configure or am i do wrongly ?
Is there possible to configure different gguser for primary to standby and standby to primary? ===>I know it makes complex.Just for asking whether gg will work by this way?
Currently i gave dba privs to gguser (Since ddl need to replicate right). Need to give this privs or not required?
Please guide me.Yes you can go that way for the users, just have another one that performs the GG transactions and exclude that user which is not the owner of the checkpoints.
Also if you are replicating ddl, in the target server all you need to have is the DML privileges, no need to grant DBA.
Please remember to mark your questions as answered so as to keep the forum clean.
Greetings,
NK 
Hi,
Thanks for your reply..

golden gate - purging processin source db should not replicate in Target db

Oracle : 11.2.0.3
Server: Linux 64-bit
I have a requirement in my golden gate environment that, some purging process in Source database should not reflect in target database.
(few tables will be purged once in 3 months ,that should not be replicate in target)
Golden gate is configured for few schemas.
Could anyone please help me to solve this.
Thanks in advance, 
Which Of Golden Gate Topology You Have ? 
hi,
Unidirectional.
Thanks & Regards,
Feroz Khan 
Please repost it on Golden Gate's forum,
GoldenGate
Aman.... 
GoldenGate supports truncation in one of two ways: full DDL, and truncates only.
You could refer to
truncating and recreating target tables 
Hi Osama,
We ara deleteing records from a table in source database, only this particualr delete should not replicate in target.
Is there any configuration for this?
thanks, 
969694 wrote:
Hi Osama,
We ara deleteing records from a table in source database, only this particualr delete should not replicate in target.
Is there any configuration for this?
thanks,http://mverzijl.wordpress.com/2011/10/15/handle-truncates-in-goldengate/ 
thanks Osama for your quick response. 
Hi Osama,
Create sequence is not replicating to Target database, no error found in gsserr.log file.
extract & replicate are ruunig fine, only sequence is not getting replicated.
thanks in advance, 
You will not getting any error , Seems you don't have DDL Support . To replicate Sequence you should Have DDL Support
Also Refer
GG Sequence Issues 
Osama thanks for your promt response and help..
I think the link which you have provided is not suitable for my scenario ( http://mverzijl.wordpress.com/2011/10/15/handle-truncates-in-goldengate/ ),
In my scenario, we want to delete some 2 years record from a table in source db, but this delete should not replicate in target. We want to maintain the records in target db.
Find below my EXTRACT & REPLICATE Parameters
EXTRACT ext1     
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST lnxdev.hq.company.com, MGRPORT 7809
RMTTRAIL /oradata/ORACLE/goldengate/dirdat/rt
DDL INCLUDE MAPPED          
TABLE CLG_OWNR.*;     
TABLE LOC_OWNR.*;     
SEQUENCE CLG_OWNR.*;
SEQUENCE LOC_OWNR.*;
-Rep
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP CLG_OWNR.*, TARGET CLG_OWNR.*;
MAP LOC_OWNR.*, TARGET LOC_OWNR.*;
DDLERROR DEFAULT IGNORE RETRYOP
thanks & regards,
Feroz Khan 
If you want to issue a DML command and have it be ignored by the Extract process take a look at the TRANLOGOPTIONS EXCLUDEUSER parmeter. And then run the DML by a user who is excluded. 
we are in the same situation, I have another thread with very similar requirement Please comment - we are planning to use GG in unconventional way to replica 
And you have already received a very similar answer/solution.

Categories

Resources