Oracle GoldenGate Parameters - Understand and create Parameter files

So today we are going to discuss regarding the parameters of GoldenGate. So in the last session we discussed regarding how to create the processes and we created the parameter file, right? Yeah, yes. Parameter file are stored under DIR PRM directory. Right. Every process has its own parameter file. So if you see manager has its own parameter file, extract has its own parameter file, datapump has its own parameter file. So how do you create the parameter file? You fire the command, edit param and process name. So first time when you do it, you fire this command inside the ggsci prompt. So when you fire this command, automatically it creates the parameter file inside the DIR PRM directory. Yeah, okay. Edit param DIR PRM. When you have to view the already configured parameter file inside the ggsci prompt, then you fire view param and process name. So like view param ext1, view param rep1. It will give you that name. Okay, now once your parameter file is created, then you can start the process. If parameter file is not created, GoldenGate processes won't start. So parameter file is the heartbeat of GoldenGate. Without parameter, your GoldenGate process won't start, run, they won't work at all, okay? Now parameter file is stored inside DIR PRM directory. DIR PRM directory. Now every parameter has default value. Every parameter has default value. Every parameter has a counter parameter. Every parameter has a default value. Every parameter has a counter parameter. What does it mean? Yeah, we are going to discuss. Okay. Every parameter is valid in a parameter file until its counter is encountered. Until its counter is encountered. Now every parameter is valid for either extract, data pump, or replicate parameter file, or all three of them. See, when I open the extract parameter file, what it does? It captures the DMA transactions for this table, right? Now how does GoldenGate know that what all transactions it has to capture for this table? Have we specified anywhere that it has to capture insert, update, or delete, or anything? We haven't mentioned anything, right? So how does GoldenGate know that? Because I think, what do you call that? Log, whatever it's committed, only it will send. So most of these DML statements, they might be in a committed, there may be a flag in the log file that committed and non-committed. Now remember with GoldenGate, it is possible to filter out the data. GoldenGate is not like DR, which replicates everything, right? Yeah, right. You can choose to filter out the data. So we discussed last time as well, you have Vodafone India database, which contains the data for all the Indian customers. It would be billions of customers. Now Maharashtra government decide to build a separate database for Maharashtra customers. Vodafone Delhi decides to implement their own database. Vodafone Madhya Pradesh, Vodafone Uttar Pradesh, they all decide. So it is possible with GoldenGate. Similarly, GoldenGate is again for BI purpose. You can use it for data warehousing, many purpose. In data warehousing, you don't delete your data, you just insert or update, but you never delete the data. So it is possible to filter out the inserts and deletes with GoldenGate or updates. If you want to capture only updates and deletes with GoldenGate, it is possible. If you want to ignore inserts, it is possible with GoldenGate. How does it do that? We are going to discuss that. This comes under filtering events. Yeah, so filtering data, filtration more we are going to discuss later on. But how does parameters work? This is more about the parameters. How does parameters work in GoldenGate? Also, a parameter is valid for tables following the parameter. Now we'll understand what all these means. Remember, these are the golden five rules when you are implementing GoldenGate. Or when you go to any organization to support the already existing GoldenGate. Remember these five things, okay? Now we have a table called as pdborcl.sh1.test1. The question comes here, how does GoldenGate know what all transactions it has to capture? So can you read out the first line? It's what you call, every parameter has a default value. Right, so by default there are some parameters which are working. Now what are those parameters? Get inserts, ignore inserts. Remember GoldenGate parameters are very simple. Means you can understand just by their plain English text. So you can know what it means. Get updates, ignore updates. Get deletes, ignore deletes. So default value means, even when you're not specifying anything, there is some default value which is in place in a parameter file. Okay, so every parameter has a default value. So in case of insert, updates and deletes, get is default. Get means it has to capture, send or apply. Get, it will get. Ignore means it will ignore the insert transaction. So in case of these six parameter, get is default. Every parameter has a counter parameter. So counter of get insert is ignore insert. Counter of get updates is ignore updates. Counter of get deletes is ignore deletes. Similarly, counter of ignore inserts is get inserts. Counter of ignore updates is get updates. Counter of ignore deletes is get deletes. Every parameter is valid in a parameter file until its counter is encountered. So get inserts would be valid in a parameter file until ignore inserts is encountered. Get updates would be valid until ignore updates is encountered. Get deletes will be valid until ignore deletes is encountered. Similarly, vice versa. Ignore deletes will be encountered until get inserts is there. Now these six parameters which we have discussed, these are valid for all three parameter files. So you could use them either in extract file or in datapump file or in replicate parameter file. Okay. This parameter we are going to discuss it later on. Means right today only. But let us see how does it work. Now we are saying every parameter has some default value. So if we look at this parameter file, let me copy it on the notepad only. Let me copy it over here. So when we are not mentioning anything, by default, get is there. So let me type that. So by default, get inserts, get updates and get deletes is present. So when we are not mentioning anything, get is default. So whenever any transaction happens in pdvorcl.sh1.test1, inserts are captured. Update transaction happens, it is getting captured. Why? Because the parameter is present by default. Now a parameter is valid for a table following the parameter. So these parameters would be valid for the table specification we are providing below this. Now if I provide below this, say for example, I'm providing no deletes over here. Okay. So this parameter won't affect this at all. Okay. However, this parameter will affect all the tables following this parameter. Also, read the third line. A parameter is valid in a parameter file until its counter is encountered. So get inserts would be valid over here. Get updates would be valid over here. Get deletes would be valid for all the tables in this parameter file until their counter ignore inserts and encounter. So let us do one exercise now. I have table test1 in which I want to capture inserts and updates. I don't want to capture delete. In test2, I want to capture inserts and deletes but not updates. Test3, I want to capture updates and deletes but not inserts. And in test4, I want to capture inserts, updates and deletes. So this is my parameter file. Now tell me if whatever you have understood so far. Okay. Based on that, tell me how will I create the parameter file. So for test1, you just write ignore deletes. That's it. And because insert updates are default, so those will be captured default. So only deletes will be ignored. Right, then what would be my next statement? Next statement is your table or you need to give what table you need to capture. Now remember, this has to be appended or end with semicolon, complete the specification, okay? Otherwise, GoldenGate will not start and it will give you the error. Okay. Okay, now what would be my next statement? Do you want to write all the test in a single file? It would be in a single file, definitely. You will not be creating separate, separate processes. It would be a single file. So I'll open up, I'll write these rules in that notepad and that will help you in building the parameter file. Okay, the test2 is what you call, it's, okay. Ignore updates. Okay. Ignore updates and you can write a table, what do you call it, PDB or CLSH test1. Yeah, I'll write that for you, right. Okay, then the third test is what is that update and deletes you need to capture. So ignore inserts. Okay. And yeah, table, PDB or CLSH.SH1.test. Right, and next? And next one is you need to capture everything. So you need to, ignore insert. Okay, you need to get insert. You need to update insert. You need to delete insert. No, get, you mean get for get inserts, get updates and get deletes, right? You want get. Yeah, get updates and get deletes. Okay, right. Now, just want to understand why did you include get inserts, get updates and get deletes at the bottom for our export table? Because, so default is actually insert, it will get insert, get default, it will come everything. All the get will come. But we already, we do the, what I call the counter argument that ignore updates, ignore deletes and ignore inserts. Right. So again, you want to counter those stuff, we need to again, we need to put the get insert manually. Kandan, I'm very happy your understanding is correct. Your understanding which you have developed is absolutely correct. And I'm very happy for that. Thank you. Now, tell me one thing. Now we'll move table by table, okay? I'll. Okay. Now tell me, for test one table, what all would be captured and what all would be captured and what would be ignored? And this is a requirement. Remember the rule, a parameter is valid in a parameter file until its counter is encountered. Yeah. So first, is first one correct? So I want to capture inserts and updates. However, I want to ignore deletes. Yeah. So first one is correct because by default, it would be get inserts and get updates. However, as I want to ignore deletes. So from this point onwards, from this point onwards, now what will happen? Your get inserts and get updates along with ignore deletes will be active. Until more inserts, ignore updates and get deletes is encountered. Now, ignore deletes would be valid in this parameter file until get deletes is there. So that means, that means ignore deletes will be valid for test one table. Now it comes down, see for get deletes. If it is not found, then it will remain valid and deletes will remain ignored for test two table. Similar, it comes down, finds ignore inserts but there is no get deletes. Hence, again, deletes will be ignored for test three table. Now it comes down, find get deletes over here. So now, no deletes is no longer valid. Get deletes will be valid in this parameter file now. From this point, remember the last line as well. A parameter is valid for tables following the parameter. This get deletes won't be effective for tables which are above this. It would be valid only for tables which are below this. So get deletes would be active until it's countered, ignore deletes is encountered. So now get will be valid for test four. So now tell me, does it matches our requirement now? You have developed your understanding, right? I am very happy. But it doesn't matches our requirement. Right, yeah, now tell me, what changes I need to make? So here I need to, okay, here, ignore updates. Then I need to just enter get deletes. Right, so now get deletes will be active for all the tables following this parameter. And this will be active until it's countered, ignore deletes is encountered. Yeah. Right. So now my requirement for test two table is meeting, right? Yeah, true. Let us go down and see for test three. So what is happening so far? What all is valid? Yeah, well, here we need to get updates and delete. So we're already capturing a delete. Very good, right. Okay, get updates. Right, get updates, right, correct. Now let me come down. Now remember, if a parameter is getting repeated, it doesn't matter at all. However, again, GoldenGate, when you are implementing, as there are, in database, you may have separate teams. But in GoldenGate, you have, I mean, there are not much GoldenGate expert right now in the market. So you have to develop and support as well. So now supporting becomes easy if it is compact. So you could choose to remove the duplicate parameters. Right? Yep. So how will I proceed for test four table now? You just remove the get insert, get updates, get deletes, all the three. Okay, I remove all the three now. For test four, see ignore insert is getting valid for test three table right now. Oh, yeah, yeah, yeah, okay. So that one, we need to include that insert, get insert. Get inserts will come now from the parameter. Get updates will come from above. And get deletes will also come from above. So this is how my parameter file looks like. So this is the requirement which is met by my, as per my requirement, it is meeting right now. Okay. Now, similarly, we are going to discuss some more parameters. So support wise, say, for example, you have been given a new environment at all. You are asked to support. So this is how you will have to develop the understanding. Now, remember in GoldenGate, there are thousands and lakhs of parameters. Now, thing is, again, you will learn lot of new parameters based on your client requirement, based on the new requirements which you are getting in the support or development. However, these five golden rules remains for every parameter. Okay. Okay? Yeah. So if nothing is there, then there is some default value if I'm not mentioning anything. So there is some default value which is active. In this parameter file, the actual parameter file, even if I include get inserts, get updates, and get deletes, the behavior it would work, it will always remain the same. It is working right now. Okay. The functioning won't change at all. Only thing is, it will add some more parameters to the parameter file, and it will make the parameter file a bit lengthy. And if you're supporting, it will be difficult to understand. It may be difficult to understand. Okay? Right. Now, similarly, we have... So DDL is a replication. DDL replication, we are going to discuss later on. However, just for truncates, GoldenGate has one parameter, which is get truncates, and the counter of get truncates is ignore truncates. So when you give truncates, it will capture the truncates as well. So far, if you see, it is just capturing inserts, updates, and deletes. Now, if we have to capture truncates, there are two ways. Either enable the DDL replication. We are going to discuss that during the later part. Or just enable the... Just use the parameter get truncates and ignore truncates. So does that mean... Does that mean this parameter also captures the truncates? No. Which parameter? This parameter file. The previous one, no, it never, because we need to manually write a get truncates. Why we manually need to write get truncates? What is the reason? Because it's a DDL statement, and so we didn't activate the DDL capturing. No, the thing is, that's what I said. For DDL enable, if I'm enabling DDL, so it will capture all create, alter, modify, drop, everything it will. Right. However, my requirement is just to enable truncates. I don't want to create, alter anything else. Okay. If I want to enable that, GoldenGate has given me a parameter called as get truncates and ignore truncates. Okay. Now, you said we need to manually add get truncates, which is absolutely right. Now, why we need to add it? Can you read first line once more? Every parameter has a default value. Exactly. So, in case of truncates, the default is ignore. Okay. Default is ignore truncates. In case of truncates, default is ignore. So, when I see this parameter file, by default, there is get truncates, which is active. By default, sorry, ignore truncates, not get. Get in case of DMLs, in case of truncates, it is ignore truncates. So, when you're mentioning, when you're not mentioning anything, it is ignore truncates by default. Right. Now, we are going to discuss all these parameters. Every parameter has a default value. So, the default is ignore. Every parameter has a counter parameter. So, the counter of get truncates is ignore truncates. Counter of ignore truncates is get truncates. Every parameter is valid in a parameter file until its counter is encountered. Every parameter is valid for either extract, data pump, or replicate, or all three of them. So, again, this parameter, get truncates and ignore truncates, you could use either in extract or data pump or replicate. It doesn't matter. This is valid for all three of them. We are going to discuss some parameters which could be used only in data pump or which could be used only in replicate, but they cannot be used interchangeably in extract or any other parameter file. We are going to discuss. But these eight parameters which we have discussed so far, you can use them in any parameter file. Now, there are other parameters like pass through. The counter of pass through is no pass through. Now, the default is pass through. Default is pass through. Counter of pass through is no pass through, counter of no pass through is pass through. This parameter is valid only in data pump parameter file. You cannot use this parameter in extract or replica. This could be used only in datapump parameter. Now what does pass-through and no pass-through mean? Now in GoldenGate, it is also possible to filter or transform the data. Pass-through means pass the data as it is, means whatever extract has returned to the local trail file, pass the data as it is without any changes. It is possible to make the changes in the parameter file. However, not in the parameter file, but the way it is being written into the local trail file, it is possible to filter out the data or do the transformation at the datapump level. However, when we are using pass-through, pass-through means pass the data as it is being written into the trail file. No pass-through is when you are making some changes, when you are doing some transformation or filtration, then you use no pass-through. So default is pass-through. Similarly, other parameter is assume target diffs and the counter is source diffs. Now default is assume target diffs and this parameter is valid only for replicate parameter. You cannot use it in extract or datapump. Now what does this mean? In GoldenGate, it is possible to replicate the data between dissimilar database. You could replicate the data between Oracle to MySQL. You could replicate the data from DB2 to SQL server. You could replicate the data from Oracle to Oracle between dissimilar tables which have separate structures. So in that case, what happens, you are replicating the data between two non-identical tables. Now assume target diffs, as the name suggests, it tells replicate to assume the target definition that it is similar to the source definition. Now when it is different, when it is different, we use source diffs. That means we are creating the file based on source definition and send it to target to understand how the source definition looks like. We are going to do the exercise on that, but just for your understanding, there are two parameters, assume target diffs and source diffs, which are being used in the replicate parameter. Assume target diffs is used when the structure of tables is same between source and target. Source diffs is used when the structure of table differs or you are replicating the data between heterogeneous environment, okay? Right, now let us discuss regarding, there is one more parameter, handle collision and no handle collision. I get this, no handle collisions. Now the default is no handle collisions, valid for replicate parameter file only. Valid for replicate parameter file only. Now what does this mean? If you go and see the parameter, what do you understand? It's a plain text English, right? It handles the collisions, right? It handles the collisions. Now what kind of collisions it handles? Now remember in GoldenGate, there are three types of collisions which happen, which can happen from target. First is insert collision. Next is update collision. Third is delete collision. Now what does that mean? What does insert collision means? What does update collision means? And what does delete collision mean? Now you have a table called as test one. Inside that you have a column called as S number and name. You have source database and you have target database, right? Now on the source database, remember you are using in GoldenGate, you are using ignore deletes. What it will do? It will not send the deletes from source to target. So at source you did one insertion, one and kernel, okay? Now with GoldenGate what will happen? This will be replicated to target. One kernel will be replicated. And this S number is a primary key. S number is a primary key. Remember with GoldenGate, it is always mandatory to have a primary key on both source and target. It is always mandatory to have a primary key on both source and target. At least on source, otherwise what will happen? If source database gets multiple transaction, they will definitely affect the target database. So it is always mandatory to have primary key, okay? Now one kernel you inserted on source, that will be inserted on target. Now you fire the command delete where id equal to one. So what will happen? This record will be deleted from source. Yes. But what will happen on target? It won't delete. It won't delete because we are using? Ignore delete. So target will remain same, one kernel. Now this primary key is now available. This primary key one is now available on the source database now. Means this could be assigned to some other name. So what happened? You fired the command insert into test one, name equal to Ashish and id equal to, S number equal to one. So what will happen? One Ashish will insert on source database now. Now with GoldenGate, it will come to target. So what will happen now? One is a primary key. Now GoldenGate, when it tries, when Replica tries to apply this record, it will stop or abend, abend means abnormal end. Saying record already exists. Error, record already exists. The reason being, the primary key one is already existing on the target database. However, as that was not present on source when Replica tries to apply it on the target database, it says record already exists, cannot apply the data. So what happens in this scenario? Replica abend. And this is because of insert collision. So insert collision is you are trying to insert a same row on source which already exists on target. Trying to insert a row at source which already exists on, right? Now we are going to discuss the solution later on. Currently, we are discussing the problem which is happening. Now what happened? On source database, you have one Ashish. On target, you removed one Kannan, you removed it. So what is happening right now? On source, you have S number as one. However, on target, S number one does not exist at all. Now what you did? You now fired the command update test one, set name equal to ABC where S number equal to one and committed it. Now what will happen? This will go to target using GoldenGate, right? Yeah. Now when it goes to target, actually GoldenGate is a SQL apply. Now when it applies the SQL, it is unable to find the ID one. It is unable to find the ID one and replicat will append again stating record not found. Exactly, record not found. It is SQL error 1403 with which it append. Record not found error. So this is what is known as update collision. So you are trying to update a row at source which does not exist on target, which does not exist on target. So what happens with delete collision? Now delete collision is same as update collision. You're trying to delete a row. At source which does not exist on. So what happened now? One Ashish is present on source and record with ID as one, not at all present on the target database. Now you fired the command delete from test one where ID is, where S number is one. So what will happen? The record will be deleted from source. Now when you go to target, the record is not found. So replicat will append saying record not found. Saying record not found. So this is how your collision happens. This is how your collision happens. Now what is the solution? We have discussed the problem right now. So what is the solution? So the solution behind this is handle collision. So this all error comes because in the replicat side there is a default which is valid, which is no handle collision. Now we have to resolve these. We'll have to use the parameter handle collision which handles all these collisions. Now how does it handle? So in case of inserts, in case of insert collision, how it works? It will overwrite the record. Handle collision will? Overwrite the records. Overwrite the record, overwrite the record. So what will happen now? Now the ID one will have the Ashish. Exactly. So what will happen? One Ashish on source is there, right? You inserted it. Now if you go to target, it will find that one ID one is already present. It will, what it will do? It will overwrite the already existing record. One Ashish, one Kannan will change to one Ashish. So here what is, if you, ideally it's an insert statement on source side, right? On the target side, if you consider what is happening, it is basically converting the insert statement into update and updating the name from Kannan to Ashish. So it is kind of a update. Insert is getting converted into update. Now remember, there is one unique thing about GoldenGate. You could convert your insert statement into update on the target database. You could convert your deletes into updates on the target database. We are going to discuss more on that. So just for understanding what is happening here, your delete, on target date, on source it was an insert. However, on target, what happened? It converted into update and it overwrote the record. Now, in update collision, what will happen now? One Ashish is there, replicant will have it. When you use handle collision, handle collision will insert the missing update. So what will happen on target database now? One Ashish will get inserted. So target will have one Ashish. So what is happening in case of update collision? Your source database update statement is getting converted into insert and one Ashish will be inserted on a target database. In case of delete, when you do handle collision, what will happen? It won't do anything. It will just let your replicant run. Because anyways, nothing needs to be done. One Ashish is there, which is not present on target. However, when a replicant events, one Ashish already is gone from source. This record is not present on target. So it will just make your replicant to run without doing anything. So here, whatever parameters we have discussed, handle collision is combination of multiple parameters. Update inserts and insert missing updates. These are again the parameters. That's what I said. There are lakhs of parameters in GoldenGate. So update inserts default is, update inserts counter would be no update inserts. Means there would be, inserts won't be converted into updates. Insert missing updates counter is no insert missing update. And the default is no. Default is no. Only and only if you provide update inserts, then only inserts will be converted into updates. So this is what will happen when we'll provide insert updates, update inserts, then your inserts will get converted into updates. Now the question which would be coming in your mind is, what is the use of these? So there are certain scenarios in your environment where you want to convert your updates into inserts. So you don't delete the data on the target database. However, when a new data comes, you want to keep that data. In that scenario, you use update inserts. Okay. Similarly, insert missing updates is very useful in real time as well. Sometimes what happens, the source and target are not matching. In that scenario, you may want to use insert missing updates because anyways, that will make the record inserted on the target database. See, if you read this parameter, update inserts, it is very simple, right? You can understand what it means. It means insert statement which is happening on the source database that will be converted into updates on target. Insert missing updates, it means updates which are happening on the source database and missing on the target database will be converted into inserts. Similarly, we have parameter update deletes which will convert your deletes into updates. We'll discuss this parameter again. We'll do the practical with this. So these are the parameters which are used in GoldenGate. So GoldenGate parameter file is very important. It's the heartbeat. So when I start any process, say for example, I execute start ext1. What happens, GoldenGate goes to the GoldenGate home, ggsa process, it goes to the GoldenGate home, goes inside dirprm and searches for the parameter file named same as the process. If it differs, it will give you the error, parameter file does not exist. If it is present, then it will start based on the parameters provided in that parameter. I'll repeat the golden lines which we have written. Every parameter has a default value. Every parameter has a counter parameter. Every parameter is valid in a parameter file until its counter is encountered. Every parameter is valid for either extract, data pump or replicate parameter file or all three of them. A parameter is valid for tables following the parameter. So these are the important thing which you need to keep in mind while creating the parameter file. Or if you are given the support of the environment wherein GoldenGate is already installed. So using these parameters and understanding, you can understand or comprehend the parameter file. The thing is, as I said, in your environment, you may go and find a new parameter which we may not have discussed. Only thing is, you just need to understand what that parameter does. So either you could Google it out or you could just find it from the administration guide. What does that? So update, delete. If you're not able to understand what it exactly does. So you could just search in the administration guide or you could just go into Google and find what it does. It will help you in understanding. I'll do. So say for example, update deletes GoldenGate. So what it does? Let me see what description it gives. So this parameter is valid for replicate. Update deletes counter is no update deletes. Use the update deletes parameter to convert delete operation to update operation. The deletes would be converted into update. Default is no update deletes. Okay, we are going to discuss more on these parameters. We are going to do the demo of all these parameters by creating and how the parameter works.