Golden Gate DDL Replication - GoldenGate

Hi Can anyone tell me how to do DDL replication
I am using these params but its not working
EXTRACT ext04
USERID ggs, PASSWORD ggs
RMTHOST localhost, MGRPORT 7809
RMTTRAIL /u01/oracle/goldengate/dirdat/dd, purge
DDL INCLUDE MAPPED
TABLE SCOTT.*;
REPLICAT rep04
ASSUMETARGETDEFS
USERID gg, PASSWORD welcome
DISCARDFILE ./dirrpt/rep3.txt, purge
--RESTARTCOLLISIONS
HANDLECOLLISIONS
APPLYNOOPUPDATES
DDL
MAP SCOTT.*, TARGET SCOTT.*; 

Hi 786923,
At a first glance you are missing "*" in the replicats MAP statement. It should be:
MAP SCOTT.\*, TARGET SCOTT.*;
You could also change "DDL" into "DDL include all" to make sure all DDL operations which are in the trail are applied to the target db by the replicat.
P.S. Have you created database objects which are required for GoldenGate to replicate DDL? 

Thanks Dominik.
Actually that was a typo mistake while pasting the syntax here.
I specified the '*' and also created the database objects which are required for GoldenGate on both source and target. Will this be a mistake?
Shall I create them only on source itself?
I also tried using "DDL include all" but couldn't replicat DDL. 

GoldenGate DDL objects are only required on the source database, but you do not need to drop them from the target, but I would recommend executing ddl_disable.sql script (should be located in GoldenGate installation directory) on the target.
I would check if GG DDL objects are properly installed on the source by executing following scripts (also from GG installation directory):
-marker_status.sql
It should display:
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
- ddl_status.sql
It should display:
SUCCESSFUL installation of DDL Replication software components
If db objects appears to be ok, you can add DDLOPTIONS REPORT parameter to both extract and replicat parameter files. It will cause detailed messages to be written to ggserr.log file so you could see if extract is "catching" any DDL operations. 

Hi Dominik
I have done what you said. It worked.
Thanks a lot... 

Hi:
If you ever come across this reply then please let me have the EXTRACT and REPLICAT param files and if possible a little about the process. If they are available in any bolg of yours let me have the link. I am learning GG.
Thanks Caesar 

Hi caesar,
Here are the param files.
Manager:
PORT 7840
USERID gg_target, PASSWORD welcome
PURGEOLDEXTRACTS /orahome/oracle/goldengate/dirdat/*
add trandata scott.*
add extract ext1, tranlog, begin now
add rmttrail ./dirdat/dd, extract ext1
EXTRACT ext04
USERID ggs, PASSWORD ggs_source
RMTHOST localhost, MGRPORT 7840
RMTTRAIL ./dirdat/dd, purge
TABLE scott.*;
dblogin ...
add checkpointtable gg11g.checkpoint
add replicat rep1, exttrail ./dirdat/dd, checkpointtable gg11g.checkpoint
REPLICAT rep04
ASSUMETARGETDEFS
USERID gg_target, PASSWORD welcome
DISCARDFILE ./dirrpt/rep3.txt, purge
--RESTARTCOLLISIONS
HANDLECOLLISIONS
APPLYNOOPUPDATES
MAP scott.*, TARGET scott.*;
Here are the websites
http://gavinsoorma.com/oracle-goldengate-veridata-web/
http://www.oracle11ggotchas.com/articles/CreatingaGoldenGateExceptionHandlertotrapandlogOracleErrors.htm
http://itkunn.wordpress.com/2010/01/29/setting-up-oracle-golden-gate-between-2-oracle-instances-asynchronous/
Thanks.

Related

GoldenGate Replication issue!

Hi,
I have configured extract, datapump and replication process and they all are running without any error.
The issue is actual replicatin is not taking place..
Infact extract process and datapump process nothing is their, if I run any DMLs in source database no info appears when I run view report ext1, datapump or replication process detail.
Kindly help urgently.
Thanks in advance.
Poorna
Edited by: 0077 on Sep 22, 2011 4:54 PM 
Post your parameter files and GGSCI ADD statements. 
ADD EXTRACT ext1, TRANLOG, BEGIN NOW                              
                                   
ADD EXTTRAIL /u01/app/gg/dirdat/lt, extract ext1                                   
extract ext1                                             
userid gg, password gg               
rmthost test01.lab.com, mgrport 7809                         
EXTTRAIL /u01/app/gg/dirdat/lt                                   
table
TO.REM,
PA.PUR;                                                                                                                             
ADD EXTRACT dp, EXTTRAILSOURCE /u01/app/ggate/dirdat/xt                                   
                                   
ADD RMTTRAIL /u01/app/gg/dirdat/xt, EXTRACT dp               
extract dpump                                             
userid gg, password gg               
discardfile /u01/app/gg/discard/ext_discard.txt, purge, megabytes 15                         rmthost test02.lab.com, mgrport 7809                    
rmttrail /u01/app/gg/dirdat/xt                                             passthru     
table TO.*;
table PA.*;                                                  
                                   
ADD REPLICAT rep1, EXTTRAIL /u01/app/gg/dirdat/xt     
REPLICAT rep2
ASSUMETARGETDEFS
USERID gg, PASSWORD gg
discardfile /u01/app/gg/discard/rep_discard.txt, purge, megabytes 15
MAP TO.*, PA.*,
Target GG.*;
Edited by: 0077 on Sep 23, 2011 8:23 AM 
If you typed in the parameter file contents, there are some errors, and if you pasted them in, there are definitely errors.
In the extract:
table owner.table_name;
table owner.table_name;
This is not a comma separated listed. It is table by table with an ending semicolon. If you wildcard, then it is:
table owner.*;
table owner.part_of_the_table_name*;
In the replicat:
You need a MAP and TABLE in one statement/line.
MAP owner.table_from_source, TABLE owner.table_in_target;
If you wildcard this:
MAP owner.[asterisk], TABLE owner.*; 
I have Changed pamareters accordingly
but still data is not replicated on Target database
stats replicat rep1
Sending STATS request to REPLICAT REP1 ...
No active replication maps.
Kindly help
Thanks
Poorna
Edited by: 0077 on Sep 25, 2011 3:32 PM 
Let's see the parameter files again (after the fixes you made). 
ADD EXTRACT ext1, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/gg/dirdat/lt, extract ext1
extract ext1
userid gg, password gg
rmthost test01.lab.com, mgrport 7809
EXTTRAIL /u01/app/gg/dirdat/lt
table.TO.REM,
table.PA.PUR;
ADD EXTRACT dp, EXTTRAILSOURCE /u01/app/ggate/dirdat/xt
ADD RMTTRAIL /u01/app/gg/dirdat/xt, EXTRACT dp
extract dpump
userid gg, password gg
discardfile /u01/app/gg/discard/ext_discard.txt, purge, megabytes 15 rmthost test02.lab.com, mgrport 7809
rmttrail /u01/app/gg/dirdat/xt passthru
table TO.*;
table PA.*;
ADD REPLICAT rep1, EXTTRAIL /u01/app/gg/dirdat/xt
REPLICAT rep2
ASSUMETARGETDEFS
USERID gg, PASSWORD gg
discardfile /u01/app/gg/discard/rep_discard.txt, purge, megabytes 15
MAP TO.*, PA.*, Target GG.*; 
Okay, there are several mistakes.
Your extract should be like this:
extract ext1
userid gg, password gg
rmthost test01.lab.com, mgrport 7809
EXTTRAIL /u01/app/gg/dirdat/lt
table TO.REM;
table PA.PUR;
The data pump's trail source should be reading the trails generated by the extract. You have data pump reading xt files with the source files named using lt.
Multiple names for data pump: dpump and dp. That won't work.
Replicate rep1 versus rep2, plus extra stuff at the end of the parameter file:
Target GG.*;
Try following the Oracle to Oracle tutorial here (at least get a simple extract and replicat working, then add in a data pump, note the syntax used):
https://apex.oracle.com/pls/apex/f?p=44785:24:299584712491262::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:5340,2 
Hi,
all required latest trail files are present in dirdat directory of Target database but still replication process is not processing.
Please let me know.
Thanks & Regards,
Poorna 
Parameter file? ADD command? Status/info? 
Its really big pain ???   the replicat was very unstable .. there are no message , I got simply the error no.. as  ogg 0446 .and I could see Process Abending ... ???    I tried all the options ...

Strange Behaviour of Golden Gate Replication

Dear All,
I have implement golden gate replication among 2 11gR2 databases on Linux.
Structure of source and target database is exactly the same.
New records and being shifted to target from source and extract and replicat process are running fine since last 2 days.
But When i run the following after creating the extract and replicat process, But at target some records are duplicated in few tables, that I have to remove manually.
alter extract ext1 begin now
alter replicat rep1 begin now
expdp test/test directory=backup_dir dumpfile=test_gg.dmp logfile=test_gg.log schemas=test
*scp test_gg.dmp oracle#X.X.X.X
impdp test/test directory=backup_dir dumpfile=test_gg.dmp logfile=test_gg.log schemas=test
disable triggers and jobs that insert record
start extract ext1
start replicat rep1
Here is my extract and replicat parameters: trandata is added to all tables:
extract ext1
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1/")
SETENV (ORACLE_SID = "TEST1")
userid ggs_owner, password ******
rmthost 10.168.20.118, mgrport 7809
rmttrail /u01/app/oracle/oradata/GG/dirdat/lt
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY /u01/app/oracle/archive
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
DDL INCLUDE ALL
table RADIUS_DSL.*;
TABLEEXCLUDE RADIUS_DSL.SYS_EXPORT_SCHEMA_01;
TABLEEXCLUDE RADIUS_DSL.SYS_EXPORT_SCHEMA_02;
TABLEEXCLUDE RADIUS_DSL.SYS_EXPORT_SCHEMA_03;
REPLICAT rep1
ASSUMETARGETDEFS
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "TEST2")
userid ggs_owner, password ******
discardfile /u01/app/oracle/oradata/GG/discard/radius_discard.txt, append, megabytes 500
handlecollisions
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
MAP test.*, TARGET test.*;
TABLEEXCLUDE RADIUS_DSL.SYS_EXPORT_SCHEMA_01;
TABLEEXCLUDE RADIUS_DSL.SYS_EXPORT_SCHEMA_02;
TABLEEXCLUDE RADIUS_DSL.SYS_EXPORT_SCHEMA_03;
Is the sequence of my statements is OK? Kindly help why this duplication occurs.
Thanks, Imran 
Hi
How to you done the initial load? export the database with current scn or timestamp ? did u start replicat after that csn or at csn(atcsn scn# or aftercsn scn#) ? did u tried without "handlecollisions" in replicat prm.?
HANDLECOLLISIONS
Directs automatic resolution of duplicate and missing-record errors when applying data on the target database. This parameter is generally only used for the Initial Load.
Edited by: ASP on Feb 2, 2012 4:29 PM 
Thanks for the reply.
I did the initial load with expdp from source and impdp on target DB. The size of dump file is near about 50GB. Since impdp by default stores the SCN information, I did not specify it.
I followed the order I have describe above.
Without HANDLECOLLISIONS when i start replicat, it gives some constraints errors and process is stopped. 
Hi, You have to take the export with help of flashback_scn parameter in expdp, and import it in to target db, otherwise you will not get data consistency... SCN,RBA,SEQno is important in CDC mechanism. get the cureent scn from v$database and import it in to target database and start the replicat at that particular scn. 
Dear ASP,
As per your instructions I will sync database again, following these commands: Please correct me if I am wrong because the database is more then 50 GB and it almost takes a full day to replicat.
alter extract ext3 begin now
alter replicat rep3 begin now
select dbms_flashback.get_system_change_number() from dual;
expdp test/test directory=backup_dir flashback_scn=14073963 dumpfile=test_gg.dmp logfile=test_gg.log schemas=test
scp test_gg.dmp oracle#x.x.x.x:/tmp
impdp test/test directory=backup_dir dumpfile=test_gg.dmp logfile=test_gg.log schemas=test
disable triggers and jobs that insert record
start extract ext3
start replicat rep3, AFTERCSN 14073963
Thanks andRegards,
Imran Baig 
Here (roughy) what I would do when using Datapump.
1. Configure and start your Change Extract.
2. Datapump export using flashback_scn.
3. Datapump import into the target.
4. Configure and start the Change Replicat using afterscn.
Note that if any tables without primary or unique keys in the source schema undergo DML during the datapump export/import, then you will get duplicate records for INSERT (these changes will be captured by the extract started in step 1.)
Defining HANDLECOLLISIONS and KEYCOLS will not help you in this instance. You will need to define a (temporary) unique key on the table. Refer to Support Note 1350948.1 for details.

OGG PROCESS

Hi All,
This is my manager process
PORT 7808
DYNAMICPORTLIST 7813-7824
PURGEOLDEXTRACTS ‘D:\OGG\dirdat\*’, USECHECKPOINTS
USERID ogg_target#raja, PASSWORD oggtarget
This is my extract process
EXTRACT extuat
EXTTRAIL ‘D:\OGG\dirdat\rr’
USERID ogg_target#raja, PASSWORD oggtarget
TABLE scott.*;
this is my pump process
extract pmpuat
–RMTHOST MGRPORT 7809, COMPRESS
RMTTRAIL ‘D:\OGG\dirdat\rr’
USERID ogg_target#raja, PASSWORD oggtarget
–PASSTHRU
TABLE scott.*;
when i am trying to run extract process it will abending.
when i am trying to check report file it will throwugh error report file does not exist.
Can you please help me above issues.
Thanks
Raj 
Hi,
1. Remote host name not given for RMTHOST parameter,
2. Trail file assiganation is not correctly assigned to gg processes
3. Remove the single quote marks from all the path specifications,
4. Check in ggserr.log file in your GG home directory you can find the error message,
5. Also check in gg home directory there is a folder called dirrpt.
OR
use the below setup and try :
In Source
mgr prm
PORT 7809
DYNAMICPORTLIST 7813-7824
USERID ogg_target#raja, PASSWORD oggtarget
PURGEOLDEXTRACTS D:\OGG\dirdat\* , USECHECKPOINTS
ADD EXTRACT extuat1 , TRANLOG , BEGIN NOW
ADD EXTTRAIL D:\OGG\dirdat\ll, EXTRACT extuat1
ADD EXTRACT pmpuat1, EXTTRAILSOURCE D:\OGG\dirdat\ll
ADD RMTTRAIL D:\OGG\dirdat\rr EXTRACT pmpuat1
GGSCI > edit params extuat1
extract prm
EXTRACT extuat1
USERID ogg_target#raja, PASSWORD oggtarget
EXTTRAIL D:\OGG\dirdat\ll
TABLE scott.*;
GGSCI > ADD EXTRACT extuat1 , TRANLOG , BEGIN NOW
GGSCI > ADD EXTTRAIL D:\OGG\dirdat\ll, EXTRACT extuat1
GGSCI > edit params pmpuat1
Pump Prm
extract pmpuat1
USERID ogg_target#raja, PASSWORD oggtarget
RMTHOST < target server hostname> , MGRPORT 7809, COMPRESS
RMTTRAIL D:\OGG\dirdat\rr
PASSTHRU
TABLE scott.*;
GGSCI > ADD EXTRACT pmpuat1, EXTTRAILSOURCE D:\OGG\dirdat\ll
GGSCI > ADD RMTTRAIL D:\OGG\dirdat\rr EXTRACT pmpuat1 ---- this trail file (rr) path should be same from target server.
In Target
GGSCI > edit params repuat1
REPLICAT REPUAT1
USERID <username>, PASSWORD <pwd>
assumetargetdefs
MAP scott.*, TARGET scott.*;
GGSCI > ADD REPLICAT repuat1 , EXTTRAIL D:\GG\dirdat\rr , checkpointtable <checkpoint tab name>
then start the extract, pump and replicat process....
HTH
Annamalai 
Although creativity is admired in many areas, GoldenGate is not one such area. Follow the rules or it won't work, it's that simple.
You can also go through the learning library tutorial(s). For example, as pointed out in the other post, do you see quotation marks around any paths? No. So why did you include them?
http://apex.oracle.com/pls/apex/f?p=44785:2:0:FORCE_QUERY::2,CIR,RIR:P2_TAGS:Goldengate 
Hi All,
i working on windows operating system.
10g database.
in D drive i am running source
in E drive i am running target.
i dont' kow what is the value for RMTHOST < target server hostname> , MGRPORT 7809, 
What part in the tutorial was not clear? 
Hi,
As suggested by Steven once again go through the learning tutorials, you can easy to understand what is the use of RMTHOST parameter and other parameters,
In your case the RMTHOST is your same windown machine IP address/hostname. 
RMTHOST will be your system name or machine name.
If you are trying to configure source and target on the same host (your machine) go through this article by Gavin Soorma and try accordingly..
http://gavinsoorma.com/2011/08/goldengate-replication-with-source-and-target-on-the-same-physical-host/

OGG-00446  Error positioning in file.

Hi,
i am getting the following error when i run the initial load
2013-01-08 07:11:57 ERROR OGG-00446 Error positioning in file.
Please help. Let me know if more details are needed.
My initial load extract is like
SOURCEISTABLE
USERID gguser, PASSWORD gguser
RMTHOST ****, MGRPORT 15001
RMTFILE ./dirdat/AA.DAT, purge
TABLE gginf.AA, &
KEYCOLS (xxx,yyy);
My target replicat is
SPECIALRUN
USERID gguser, PASSWORD gguser
ASSUMETARGETDEFS
--
SOURCEDEFS ./dirdef/AA.defs
EXTFILE ./dirdat/AA.DAT
MAP gginf.AA, TARGET gg.BB, &
COLMAP (USEDEFAULTS, &
xxx1 = xxx, &
zzz1 = zzz), &
KEYCOLS (xxx1); 
Can you provide more information?
1. Source and target DB type and version
2. The commands used to add extract and replicat
3. GoldenGate version
Thanks. 
Thanks for the response. I have fixed the issue. The source table name in the replicat didnot match the sourcedef file.Hence the error.

Unable to do Repliaction?No active ext Maps

hi,
All the process are running fine.but no synch. not doing replication.
when i check the stats * then it throws the follows message.
GGSCI (serv1) 62> stats *
Sending STATS request to EXTRACT EMASTR ...
No active extraction maps.
Sending STATS request to EXTRACT PMASTR ...
No active extraction maps.
Regards,
AMSII 
Parameter files? 
Source
----Process Manager
PORT 7809
PURGEOLDEXTRACTS ./dirdat/sa*, USECHECKPOINTS, MINKEEPHOURS 2
--Extract Process
extract emastr
userid orclgg, password Ogg1234
exttrail ./dirdat/em
DBOPTIONS ALLOWUNUSEDCOLUMN
table crisp.test_ogg;
--Data Pump Process param
extract pmastr
rmthost 172.100.11.48, mgrport 7809
rmttrail ./dirdat/pm
passthru
table crisp.test_ogg;
Target
--Replicat
replicat rmastr
userid orclgg, password Ogg1234
handlecollisions
assumetargetdefs
allownoopupdates
map crisp.test_ogg, target crisp.test_ogg;
Thanks 
You've added extract and replicat, started ER* after changes were made to the parameter files, configured the database, created the orclgg user with appropriate permissions? And you have a working example to compare with, the one from the tutorial?
http://apex.oracle.com/pls/apex/f?p=44785:24:8956339493459::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:5340,2 
Problem resolved by myself. 
Please don't share what the problem/resolution was, and certainly, don't ever award points to the numerous people who help you here.

Categories

Resources