OGG app - GoldenGate

Can you please clarify the below queries....
1. Where we can use the filter condition in goldengate , whether extract process or datapump files , which one will give the better performance?
2. In case i want to add one more schema replication between two dbs, but currently in my GG running for single schema replication with single extract and three pumps , now i want to add one more schema in the replication process , how to add the new schema in my GG setup without OLTP impact? 

"Like a primary Extract group, a data pump can be configured for either online or batch processing. It can perform data filtering, mapping, and conversion, or it can be configured in pass-through mode, where data is passively transferred as-is, without manipulation."
When are you using data pump? If the filtering is a one time shot, then put the load on the data pump. If ongoing, you'd have to do it in the extract anyway. Or, if it is a large amount, then:
Filtering and conversion
Data filtering and data conversion both add overhead, and these activities are sometimes prone to configuration errors. If Oracle GoldenGate must perform a large amount of filtering and conversion, consider using one or more data pumps to handle this work. You can use Replicat for this purpose, but you would be sending more data across the network that way, as it will be unfiltered. You can split filtering and conversion between the two systems by dividing it between the data pump and Replicat."
"Oracle GoldenGate supports adding tables for synchronization without having to stop and start processes or perform special procedures. The procedure varies, depending on whether or not you used wildcards in the TABLE parameter to specify tables."
See page 310 in the Windows/UNIX admin guide. 

Thank you....


GG Sequence Issues

Hi all today we had performed a load test on our Test environment, when we have done one way replication it didnt throw us any errors but when we tried to replicate from target to source it says sequence doesnt exist? Does OGG replicate Sequences? what we have seen is there are sequence mismatch, can anybody tell me in A-A bi-directional will OGG support updating Sequences? We are using DDL replication we are only using DML rplication, is this a reason why its not replicating sequneces?
And our Source and target are same platform Oracle-Oracle.
GG can replicate sequences (as in CREATE SEQUENCE) if you have DDL support configured. Your error sounds like DDL replication is not working from target to source.
You said:
We are using DDL replication we are only using DML rplication, is this a reason why its not replicating sequneces?
Did you mean to say:
We are NOT using DDL replication we are only using DML rplication, is this a reason why its not replicating sequneces?
If that is the case, then yes. You need to enable DDL replication. 
Yes we are working only on DML no DDL is configured for our environment, and while we are trying to run all the scripts
role.sql, marker.sql our DB screens are freezing and we are cancelling the execution, as here in my case we are not creating sequences, but the sequences what we are replicating are not getting updated on target side, on target side it still shows old sequence values? i dont think this is because we did not enable DDL config, am i correct? 
On your source, you can use a sequence to generate a value for a record. Nothing new there. When that record is put into redo/extracted to trail, you have the hard-coded value for that sequence value. On your target, assuming the tables and objects are the same, you are going to have the same trigger on that table. What is going to be inserted? The value from redo log/trail, or the sequence.nextval that exists on the table? That is a choice for you to make (not using the nextval on the target is probably what you want).
The DDL part of this is whether you want the creation or alteration to take place (i.e., be replicated). This is typically a one-time setup/at least not done frequently in a normal database.
If you are going bidirectional, then you want to stagger the sequence values such that odd numbers come from the source and evens from the target. That way, you can tell where the record originated from and you don't have to worry (as much) about collisions/same values being inserted at both locations at nearly the same time.
Triggers and cascaded deletes
Triggers and ON DELETE CASCADE constraints generate DML operations that can be replicated
by Oracle GoldenGate. To prevent the local DML from conflicting with the replicated DML
from these operations, do the following:
● Modify triggers to ignore DML operations that are applied by Replicat.
Database-generated values
Do not replicate database-generated sequential values in a bi-directional configuration.
The range of values must be different on each system, with no chance of overlap. For
example, in a two-database environment, you can have one server generate even values,
and the other odd. For an n-server environment, start each key at a different value and
increment the values by the number of servers in the environment. This method may not
be available to all types of applications or databases. If the application permits, you can
add a location identifier to the value to enforce uniqueness.
See page 41 in the Oracle install guide (11.1). 
In the document they have mentioned to use SEQUENCE parameter in extract file to enable replication on sequences does this mean all the sequences what ever we are updating on source will be applied same on the target, our aim is to sync up the sequences on source & target? 
For a simple one-way replication, you have a sequence on the source. You do not have a sequence on the target (or if you do, suppress its firing from GG). The sequence values will match because of the insert on the source, and the replication of the sequence VALUE on the target, via the parameter. It means the sequences for tables listed in that particular extract group, not all sequences. 
Modify triggers to ignore DML operations that are applied by Replicat
can you be specific on this point? weather i need to do it at DB level or else in OGG parameters?
I am unable to find note on the point what you mentioned to look over install guide,
We are using OGG build 2 so i think the options differ here?
Thanks a lot for your support steven 
Hi steven you told to use the even odd relationship for the sequences, to avoid overlap scenario, whats the case with Production database which has some GB of data in them will it be affected once if we try to change the sequences increment values? 
Do you mean assign new/higher starting/current values at each end, both incrementing by 2, but staggered odd/even with the start value? 
What i am planing to do is take last_number from user_sequences and drop the sequence on source and target side and on source side i am creating sequence with starting number as last_number & on target side i am creating sequence with starting number as last_number+1 & on both side i am incrementing it by 2. What will happen to old data will it effect it anyways? I am much bothered about that?
Thanks a lot for your support for all my questions 
What/when/where/how are you updating values generated by a sequence? More than likely not, they're probably just pseudo keys for a primary key or unique constraint. What happens when the Oracle instance crashes? Sequence values that were cached are lost. The point is that - assuming you are not doing any updates on sequence generated values - moving the nextval to something higher will have no impact. Do take care that your column isn't something like Number(6) and you are starting with a high number close to 999999.

Data Filtering, Transformation & Manipulation

Dear  OGG Experts, I am getting bit confused regarding DATA TRANSFORMATION & MANIPULATION in OGG.I am searching good examples to understand efficiently.Can anyone help me to get detailed information about FILTERING, TRANSFORMATION & MANIPULATION  for Schema, Table, column and Row -Level ?        Thanks in advance.
For a general description, see https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_datainteg.htm#GWUAD391 See the reference guide for the MAP and TABLE parameter in OGG to do nearly all transformations.OGG allows you to capture a table from any supported databases and replicate them to any supported database irrespective of whether the data structure is the same or otherwise. So a simple 'default' transformation such as this parameter with for example tablex having more columns than tabley MAP myschema.tablex, TARGET otherschema.tabley; Basically replicates a different schema and table to another and OGG simply ignores the extra columns for you. Just an example of such a data transformation. You can also choose to say insert all the records, whether it is an insert, update or delete. MAP myschema.tablex, TARGET otherschema.tabley, insertallrecords;The possibility is endless. See also the section of the reference for 'column conversion functions' where one can concatenated strings, compare them before doing anything.  CheersKee Gan
Hi K.Gan, I do NOT have much idea regarding Filtering and Transformation.I have read some docs. Some links are recommending to use datapump and some are NOT recommending. 1) If we use Datapump, by default pass-through mode is enabled - am i right ? PASSTHRU parameter isn’t supported with OGG.  For the Datapump pass-through mode , both source and target table names and structures must be the same, and no filtering operations can be performed on the data. If Data Filtering or Transformation is required,it’s a good idea to do this with the Datapump to reduce the amount of data sent across the network. 2)  can we use Datapump option (without passthrough) to perform Data filtering and Transformation ?
1) If we use Datapump, by default pass-through mode is enabled - am i right ?> There is no default, an extract pump decides whether it is using passthru or otherwise. If you do any transformation you first need to use the userid parameter which enables the extract pump to create a session with the database. You should get a baseline as to how much data is captured. Just capture what you need without any change and see what sort of volumes you get. Filtering is reasonably fast like using a WHERE clause. Data transformation, example concatenating strings etc takes more time. So you have volume versus cpu time. You will find that complex transformation is easier if done at the replicat side. 2)  can we use Datapump option (without passthrough) to perform Data filtering and Transformation ?> Yes you can do this. However you can also do that in the main extract. The things you can do in a extract pump can be done in the main extract. Maybe if you describe what are you doing with OGG then I may be able to give you some suggestions. CheersKee Gan
Hi K.GAN, Maybe if you describe what are you doing with OGG then I may be able to give you some suggestions.I am trying to understand /  learning Data selection, Filtering and Transformation EXAMPLE :   i do not understand  (Whatever given below) To Transform data,  Native Oracle GoldenGate conversion functionsA user exit from the Extract or Replicat process that applies rules from an externaltransformation solution, then returns the manipulated data to Oracle GoldenGate. To filter data, A SQLEXEC query or procedureUser exists Without knowing the base (transformations), i am struggling to understanding it.If you wish to share some good links or good doc id, please share it
Which parts of https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_datainteg.htm#GWUAD391 do you need clarification. This section explains it reasonably well. The user exit is a really advance way to configure OGG. where did you get these statements from?CheersKee Gan
Hi,do you need clarification.Yes. I am much confused about filtering and transformations.Very initial which part is essential to concentrateDo i need  end to end knowledge on Filtering & Transformations ?I need some suggestions. where did you get these statements from?Mastering Oracle GoldenGate Real-time Replication and Data Transformation  by  Ravinder Gupta  http://apps2fusion.com/training_demo/golden-gate-training/Oracle-Golden-Gate-Learning-Guide.pdf  Data selection, filtering and transformation Thanks
Ok you can ignore the user exit bits. User exit means you can embed your own C++ code into OGG to do your customized processing and it does not stop at data transformation. It's code you can virtually do anything. You don't need end to end knowledge for data transformation and filtering. For example:A simple WHERE clause is filtering. Actual OGG parameter eg:TABLE MYSCHEMA.MYTABLE where ID_NUM > 2000; A simple concatenate of two strings into a target column is transformation, eg. this maps two different tables and a column in tableb is made of two cols in tablea:MAP  SCHEMX.TABLEA, TARGET SCHEMAY.TABLEB, COLMAP (USEDEFAULT, newcol=#strcat(col3,col4);  This is basically what it means. You can do the simplest to crazy off the chart stuff. In fact by default OGG already do a lot of filtering and transformation for you. Simply saying which table you want is filtering and the replicat automatically matches column names and  datatypes. And if you capture from Oracle to DB2 for example the configuration looks very simple but obviously OGG will need to do a lot of work.  If you want to learn OGG,  start by installing and do some replication. Then try different table structures on both ends.  CheersKee Gan
HI K.Gan, I am extremely sorry for being very very late reply.I went out station just for  1 week. Thanks for your suggestion.

Range function in GG

Guys, am configuring 4 new replicats with range function for faster data processing. The table size is around 194Gb and has one P.Key column. Please clarify me on the below doubts. a) on what basis can i arrive on the number of ranges?b) Range function doesn't support DDL, how can i have DDL enabled for the table defined in range function? Can i have DDL enabled in one replicat and define DDL exclude all in another three replicats?
Hi , Please check the below link which explains you clearly, https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/column_conversion_functions022.htm#GWURF809 I think #Range supports DDL. The limitation in here is, there should be no changes to the column which you are specified in the Range function. To ensure that rows do not shift partitions to another process group and that the DML is performed in the correct order, the column on which you base the #RANGE partitioning must not ever change during a process run. Updates to the partition column may result in "row not found" errors or unique-constraint errors. Note: you can close this thread as there is another one opened for the same query. Regards,Veera 
Hi,Also please refer to Range Replicat Abend With ORA-00054: Resource Busy and Acquire With NOWAIT (Doc ID 1379359.1) Please make sure that only one of the Range Replicat applies the DDL changes and include DDL EXCLUDE ALL in the other Range replicats to avoid this DDL duplication errors. Regards,Pooja Garg
Got it thanks, how about on choosing number of replicats. On what basis can i have a specified number of ranges defined?
It is up to the number of records and the table size... It is better totest it in the lower environment and then implement it in the production..  Regards,Veera On 13 Apr 2017 10:20 p.m., "community-admin" <
ORASCN Table size is close is 120Gb....
a) on what basis can i arrive on the number of ranges?> As with all performance analysis you need to get a baseline. Do a single replicat without range to start with. The size of the table is not the irrelevant and it is all to do with volume. Then work out your service level, how fast do you want the data replicated. b) Range function doesn't support DDL, how can i have DDL enabled for the table defined in range function? Can i have DDL enabled in one replicat and define DDL exclude all in another three replicats?> If you are classic replicat with range and DDL that is exactly what you need to do. Having said that the DDL handling replicat can fall behind and the other replicats can crashed like if you alter add column for example. There are manual ways to get around this and if you want to go down this path let me know.What version of OGG and DB are you using? If you are running Oracle 11.2+ and OGG 12.1+ you can use coordinated replicat which is like range except that it coordinates all transactions, (barrier coordination in OGG speak) that takes care of transactions such as a DDL. See a great explanation at https://blogs.oracle.com/dataintegration/entry/goldengate_12c_coordinated_replicat (See Example 4 which is what you are after). Let me know if you need help in using this for your current classic replicats with range.Note that there is also another type of replicat call Integrated Replicat. This also does parallel processing but it does not split work for one table.   CheersKee Gan
Hi , You need to test it by using first one single replicat process and then make a range of 2 replicat processes and increase it one by one to check the speed of the apply. That is the reason I asked you to test it in the test environment first and then implement it in the production. You can also go with coordinated replicat process where parallel threads works simultaneously and applies the transactions to the target table. You can specify the number of threads you want to spawn in parallel. Regards,Veera
This is a setup which is already there and to have 12c integrated, i need to start from scratch like opening port under 12c GG, install of binaries and apply latest patchset. For this customer is not ready and asking us to have a way around with 11g GG, hence we chose using range function.
Thanks for the input, much appreciated. I will simulate the load in lowe env and accordingly decide on the number of ranges required.

Golden Gate initial Load

Requirment: Need to replicate a subset on a table to another database. it is a 24 x 7 OLTP Oracle RAC DB. Since it is 24x7, need to do everything online.
1) on 24x7 Database, If I start a Initial Load when it will stop the extraction and replication? Because during intial load time transactions are going on to the table
Or can we instruct GG to capture only current data in the table at the time of starting Initial LOAD? we will have another extract procees to capture on-going transactions.
How can identify SCN/TIMESTAMP associated with inital Load Extract & replication STOP TIMES.
Do not like to use DATAPUMP initial load, as many BIG tables to be replicated
2) What is the approximate speed we can expect from GG Inital Load? or How much data can be captured in a hour?
Host: RHEL5, DB with 30GB SGA, 16 CPU 
Not wanting to use Oracle Data Pump during synchronization for large tables is not a good understanding of how to do initial load in the first place. Data Pump for initial load is probably one of the very best methods (short of a database clone) because you know exactly what the as of SCN is. In fact, that is the same methodology Streams uses in the MAINTAIN or simplified setup.
You could use GoldenGate initial load methods, but even then, the recommendation is that for large data sets, vendor tools will be faster. So, if you really do not want to use Data Pump, and you're probably not going to be able to do a transportable tablespace (source being read only for a short time), your options are limited.
You start extract so you can capture changes while the initial load is taking place. This would be a case where you have to do it this way (as opposed to letting sync take place while the source does not have active changes taking place). After the data pump import is complete (of which, you can use more than one export/import process as opposed to streaming everything in one data pump job), you tell GoldenGate to start as of the sync SCN - which GoldenGate knows of via checkpointing. You also use HANDLECOLLISIONS, and when everything is caught up, turn off HANDLECOLLISIONS, which you can "send" that directive to Replicat without having to stop the apply process (but don't forget to remove it from the parameter file for next startup). 
I would like to re-phrase my question....
We want to PURGE old data in very big table, its size may range from 200 ~400 GB PER table and the database in 24X7. Maintenance window are very short, so we need to do everything online
To efficiently purge, I would like to REPLICATE SUBSET(based on where clause) of the data from orginal table to another table within same database and then I will rename the tables during next maint window.
Basically I need to do everything online...so can you suggest how to do it using Golden gate?

gg filter part

Hi experts, We have configured gg for one to many oracle database replication , we have used filter on each extract based on the region code we filter data and sends to target databases , could you please suggest and clarify the below query,  1.which below option is recommend to filter the data a)create an seperate extract and pump for each region and put the filter on each primary extract b) create an seperate extract and pump and put the filter on pump process for each region c) create single primary extract and create seperate pump process for each region and put the filter on pump process  Please suggest the good option and advantages since we need to add morethan 10 regions to replicate the data from one source.  Thanks in advance
Hi , Since this is the One-to-Many, It is always better to filter the data in the Datapump process or Replicat Process. A Single extract is enough which writes to the trail files, because even though if you have multiple extracts, it is going to fetch or capture the same records or data. Multiple datapump process can be used.  It is not much recommended to use Filters at Extract Level because, it takes some extra time and will lead to performance degradation. It is always better to use the filters at either Datapump process or Replicat Process. Regards,Veera