Oracle Goldengate - How to add new table in running live environment!

My name is Ashish, Ashish Agarwal and I'll be delivering this lecture to you. So, before commencing the session just a quick round of update regarding my YouTube channel which is youtube.com slash ashishagarwal underscore gg. So, if you haven't visited the channel I would request you to do visit the channel and do like share and subscribe the channel. The channel is dedicated to Golden Geek. Thank you. In this section we are going to discuss about how to add a new table in existing Golden Gate. So, suppose customer is running your Golden Gate environment for last one year and no issue but suddenly a new requirement pops up where customer wants to add one or more than one column into the existing Golden Gate. So, how do you do that? So, the first requirement is to add a new table into the Golden Gate configuration. What you require to do is the first step is you need to add the TRAN data into the Golden Gate source side. So, by adding the TRAN data means enabling the supplemental logging at table level which we have already discussed in one of the videos. So, add TRAN data you can do at ggsa level you can execute add TRAN data and on the source side you can add the supplement. Once you have done that then so add TRAN data basically will enable the supplemental logging at table. Once you have done that stop the source extract. Once the extract process is stopped add the new table details in extract parameter file and data pump. After that stop the replicate on target and make the changes the new table in replicate parameter. So, basically use the map statement. Once you have done this start extract and data pump on source. Once extract and data pump processes are started on source perform initial load of new table. So, perform the load of data from source table to the target. Once you have done that once it is completed then start the replica. So, in our environment we are already the practical which we are going to discuss today. We already have data replication happening from em dpt11 to dpt22 data. Right now what we want to do is we have a new table on target side. So, what we want that dpt11 data should be replicated to dpt underscore copy 22. So, first of all let us discuss about how to add a new table into our So, for that purpose what I will do is we'll use this table dpt. Okay. So, first step is connect to the database. So, this is our existing Golden Gate configuration where Golden Gate is currently So, the first step is adding that fan data on the table. So, what we need to do is we want to add the replication from dpt11 to dpt underscore copy. So, what we need to do is we need to do DB login. So, that database connectivity is done. So, you can do info credential store. You can do info credential store. So, do DB login user ID alias source gg1 underscore pdb1 for adding that once trend data once DB login is done now to add trend data. So, remember if you are using multi-tenant architecture that is pluggable container pluggable then in that case to add that trend data, you need to log into pluggable data. Now to add trend data pdb1 which is pluggable database name dot schema name dot table which is dpt. So, trend data will be added now trend data is very important for you to add if trend it is one of the mandatory thing. If your trend data is not enabled, you will not be able to replicate your updates. What is trend data follow the video on available on supplemental log. Okay. Now trend data basically adds extra column into redo log so that a row can be uniquely identified. So, if it's not present then your insert will work fine but updates and release Golden Gate will not be able to uniquely identify on target. Hence the replication will not now in our case because dpt112 dpt222 replication is already happening. So, you will get the mess. So, we already enabled dpt11 supplemental login. So, you are getting this message. It is already suppose it is not enabled and you are enabling it for the first time you will get that you will get the message logging of supplemental redo data is enabled or successfully enabled for table pdb1.gg. Once you have enabled the supplemental logging that is Trump stop the extract existing. So, which is stop ext int09 and stop data. Remember Golden Gate also accept wildcard as well. So, if you want to stop all the processes in your environment, you can do stop start. So, it will stop all the processes which are part of the Golden Gate GGS. And if you want to say for example, stop all the processes which starts with D, you can just give star D star in our case. They are already stopped. So, it is giving you the message that it is already stopped. If it is not then it will give it will stop. Okay. So now these are stop once these are stopped then what we need to do you need to add this table new table edit param ext int09 edit the extract parameter file add the new table in your file. So we'll do table pdb1.gg training 1 dot ddd1. So in our case, we are doing multiple filtration Etc. So you might find so these all so how to put comment in a Golden Gate parameter file is just put double hyphenate. So these are commented that means they are not so we are using this pdb1.gg training 1 dot ddd1. Once you have done it save it and close. Same way edit param dp int 09 here as well you need to include the new table. So in our case, this table is already part of it. But if you if it's completely new table on source as well, you can just give table pdb1.gg training 1 dot ddd1. But in our case, this is already there. So we don't want to have done save it and close it close it verify the changes view param ext int 09. Yeah view param. So I see this new table which we have added view param dp int 09. Yeah dp dpt11 is still there. So once you are done now go to target side. This is ggate2 which is target on target side. Stop the replicant stop rp int 09 edit param rp int 09 add the new table for your configuration map pdb1.gg training 1 dot dpt11 comma target pdb2.gg training 2 dot dpt underscore copy 2. So as I mentioned we have a requirement from the customer where we want to add the new Golden Gate table into our application. So dpt11 to dpt underscore copy 22 which is already created in that we want to add. Okay, so dpt11 to dpt underscore copy 22 we are okay. So if you look at our existing parameter effect then that is complete possibility source table dpt11 is getting replicated to two target tables dpt22 as well as dpt underscore copy 22. So the same source table is getting replicated to two target tables dpt11 as well as dpt underscore copy. Okay, so once you are done save it and close it now go to source side here you now need to start your extract and data pump don't start replicate right now just start data. Okay. So now once your extract and data pump are started, okay after that what you need to do is you need to perform the initial load from source to target. Okay, so initial load you can use either export import or by a DB link or Golden Gate initial load you can okay, so we are going to use when in one of our previous videos we discussed about how to perform initial load in Oracle Golden Gate. So we created some processes. So we are going to use the same process edit param load one will use direct load. So we now we just want to replicate perform the initial load of dpt11 to dpt underscore copy 221 target. So edit param load 2. So here what we'll do is we'll map pdb1 dot GG training 1 dot dp so dpt underscore copy to copy appended by seven. Okay, so once you are done save it and close it now you can go ahead and start load one here you can go ahead and start load one process. So if you do info load one so Golden Gate has capability of performing the initial load which is very important fault point you can just like if there is small data and if the structure of tables is present on source and target this is quick and easy way. So it is running right now. It is processing first record. So if you go to target side if your load to is running info load to it is starting right now. It is not running. You can check the view report load to it is running. So automatically this is direct load once it completes it will be stopped. So once it is stopped you can verify the data in the table on target. So you can see select star from dpt underscore copy to describe we have more columns on target. So I will do call dname1 for A10 call location1 for A10 calls 1k for A20 and call city format. So now you see that initial load has successfully happened even though other column because these are the extra columns. These are some of the extra columns which you see here. So that's why you don't see the value in those columns, but the initial load has successfully Okay. Now once initial load is completed successfully. So another reason is there are extra columns and we haven't provided the column mapping yet. So Golden Gate by default map only those columns which have same names between four. Now here we don't have same names within source and target and on source. You have only three columns like I'll show you on source if you describe you have only three columns. However on target we have got eight columns and that to only department number column value method. So what happens is in Golden Gate you Golden Gate by default replicates the data between column names, which are Golden Gate, but the column names which are different it is not able to replicate until and unless you Define the column mapping. So because we haven't defined during initial is successfully completed now. Okay, and how to map different the column names between source and target will discuss in different video for that. So now your initial load is completed. Once your initial load is completed. Now what you have to do you just have to go ahead and start your replicate now, you can go ahead and start your So your once your replicate process successfully starts and your time scene checkpoint becomes less than 10 seconds after that your new table edition is completed.