Oracle Goldengate - Parallel Replicat and Upgrade existing replicat to parallel Replicat

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 Thank you. So, Parallel Replicat is one of the new features introduced in Oracle GoldenGate 12c R3. So, Parallel Replicat wasn't available prior to GoldenGate version 12c R3. It is only available from GoldenGate version 12c R3. Now, Parallel Replicat is highly scalable apply engine for Oracle database that can automatically parallelize the apply workload and take dependencies between transactions into account. So, one of the major concern initially when Parallel Replicat was introduced was people were thinking or there was a general reservation that will it be able to take transaction dependencies into the account. So, the answer to this question is Parallel Replicat provides all the benefits of integrated Replicat by performing dependency computation and parallelism outside of the database. So, the major advantage is it doesn't load your database. It parallelizes the reading and mapping of trail files and provides ability to apply large transactions quickly. So, that is another important thing. It provides the ability to apply large transactions quickly. The dependency computation parallelism of mapping and apply are performed outside of the database and can be offloaded to another server. Transaction integrity is maintained in this process. This is very important. So, there are two types of Parallel Replicat. One is Parallel Integrated Replicat and second is Parallel Non-Integrated. So, Parallel Integrated Replicat is available from GoldenGate version 12c 12.3 and to use the Parallel Integrated Replicat the minimum database version has to be 12.3. So, if your database version is 12.1 you cannot use Parallel Integrated Replicat. However, you can always use Parallel Non-Integrated Replicat if your database version is 12.1. So, Parallel Integrated Replicat is Integrated Replicat running in Parallel. Parallel Non-Integrated Replicat is Non-Integrated Replicat running in Parallel. Now, first of all we have to understand what does Replicat process. So, Replicat process basically what it does? It does two tasks for you. So, if you do view param rep and p0. So, in this section we are going to learn two things. First of all, how to create and implement Parallel Replicat and how to upgrade your existing Replicat to Parallel. So, these are the two things we are going to learn. So, this is the Replicat process. So, the Replicat process what it does? It does two tasks. First, it reads the data from the trail file and second thing is it apply the data to the target table. I again repeat it reads the data from local trail which is done by the mapper process and then it apply the data to the target database using applier. So, in this process there are in Integrated Replicat what happens is there are one or two mappers and one or two appliers. Now, in Parallel Replicat what happens is you can define multiple mappers and multiple appliers to them. So, mapper will read the data from remote trail and applier will apply the data to the target table. So, when you run multiple mappers it speeds up the process of reading the data from the remote trail and when you run it speeds up the process of applying the data to the target database. So, in Parallel Replicat you have got multiple mappers as well as multiple appliers in your environment. So, let us see how it works and what is Parallel Replicat. So, in Parallel Replicat you can define your map parallelism. Map parallelism means number of mappers you are going to run. So, map parallelism configures number of mappers. This parameter control number of threads used for reading the trail. Then minimum value is one, maximum value is hundred and default value is two. So, if you don't provide anything in your Parallel Replicat so it will use two maps. Apply parallelism configures number of appliers. This parameter controls the number of connections in the target database that are used for applying the changes. So, by default it corresponds to four. So, by default if you don't provide anything four mappers. Then you can define min apply parallelism or max apply parallelism. So, you can define max map parallelism, apply parallelism or you can also define min apply parallelism or max apply parallelism. So, the apply parallelism. So, when you use min apply parallelism and max apply parallelism. So, the apply parallelism function is auto tuned. You can set minimum and maximum value to define the ranges in which Replicat automatically adjusts its parallelism depending on the load. There are no default but do not use this parameter at the same time as apply parallelism parameter. So, either use apply parallelism. So, apply parallelism means if you define say for example six. So, six number of appliers will run always but if you define min apply parallelism say for example two and max apply as eight. So, it will automatically tune itself. So, it will define if it requires it will increase number of appliers to eight whatever value you have defined in max apply parallelism and if the load is low it will auto tune itself to two based on min apply parallelism. But yeah if you are using this min apply parallelism and max apply parallelism then don't use apply parallelism parameter. So, let us see how you can create a parallel Replicat. So, as I told you parallel Replicat is of two types. Parallel integrated Replicat and parallel non-integrated Replicat. Now, remember one thing so far we have discussed integrated Replicat. Let me open a notepad. So, so far we have discussed integrated Replicat. Now, another type of Replicat is non-integrated Replicat or classic Replicat. Then you have parallel non-integrated or parallel integrated Replicat. So, we are going to create parallel non-integrated. We are going to create parallel non-integrated Replicat. Now, let me tell you one more thing in there is a concept in GoldenGate about checkpoint table. There is a concept of checkpoint table. This will introduce us to that topic as well. So, initially if you remember we discussed about checkpoint files. So, checkpoint files reside on OS level and under DIR CHK directory and every process in GoldenGate has checkpoint file, right? Checkpoint table is not mandatory. It resides in the database. Basically, you have to create the checkpoint table. I will show you how do you create from GDSL. It resides in the database when you create it and only non-integrated Replicat or parallel non-integrated Replicat. So, integrated Replicat or parallel integrated Replicat doesn't use a checkpoint table but parallel integrated and non-integrated Replicat. Sorry! Integrated or parallel non-integrated. Sorry, I again repeat non-integrated or parallel non-integrated. Now, what is checkpoint table? Checkpoint table is kind of I call it as an auditing table. So, it basically what it does is it stores until what point the data has been replicated, data has been processed by the replicate process. So, when I create the checkpoint table, you will see that you will get the, if you go to the database, let us create the checkpoint table. How do we create the checkpoint table? Go to ggsci, do dblogin, do dblogin to pdbd, dblogin, source gg2 underscore pdb, dblogin, user id alias, source gg2 underscore pdb. Okay. Now, what we will do? We will do add checkpoint table, c hash hash, source gg2 dot chkpt tbl. You can give any name in the database, but we are creating a checkpoint table. So, basically what it will do is when we run this command in the database, it will create the table. Let me open a new window for you. Before I run it, I will do sqlplus, c hash hash, source gg2 slash welcome1 at the rate ogg at the rate pdb2. Okay. Now, select table underscore name from pba underscore tables, user underscore tables, where table underscore name like chkpt. We don't have anything like, but now I'll create it. Add, it's double, add checkpoint table has been created. Now, go to the database. Now, you see chkpt tbl has been created along with this logs table. This is a backup table kind of thing. So, if you do describe chkpt tbl, you will see the content like group name is replicate name, sequence number, RBA, audit create timestamp, create timestamp, like it stores what sequence last this particular replicate process read, what RBA. So, basically during, you can consider, I call it as a auditing kind of table, where you can audit your replicate, like what your replicate last did, where it is reading, where it, whether it is moving forward or not, all those things. So, nothing else. Okay. So, once the checkpoint table has been added, now we can add the parallel replicate. Now, to add the parallel, so guys, for parallel replicate as well, we have an activity guide. So, for parallel replicate, we are going to follow activity guide number 22. So, we are going to follow activity guide number 22 for parallel replicate. So, so far for today's session, we have done activity guide 7, 8, 17, 9 and now we are discussing activity guide 20. Okay. So, activity guide 22, basically what we need to do, we need to add the replicate. So, to add the parallel replicate, what you need to do is, you need to add the command, add replicate, replicate name. Now, remember one thing, one more thing I will tell you. Can anyone tell me, what is the maximum number of characters you can provide for any process in Oracle GoldenGate? The name of any process in Oracle GoldenGate, how many characters it can have? 8. In parallel replicate, the limit is only 5. You cannot exceed the name of the parallel replicate more than 5. Now, why? I will tell you. So, what happens is, as I told you, there are multiple mappers and multiple appliers which runs in parallel replicate. Right. So, what happens is, say for example, you are creating a replicate with the name PAREP, PAREP means parallel replicate. When you start it, automatically, say for example, you have defined in the parameter file 2 mappers and 4 appliers will run. Okay. So, in that case, when you start PAREP, what it will do is, it will run in the background PAREP M00 and PAREP M01. So, mapper 00 will run and mapper 01 will run in this case. Okay. So, it will start your parallel replicate mapper 00 and parallel replicate mapper 01. So, if you give more than 5 characters for parallel replicate, it won't create it for you at all. Okay. Same way for applier as well. So, you provide 5 characters, GoldenGate will automatically append 3 more digits based on applier. Same way if you are running 4 appliers. So, it will start PAREP A00, PAREP A01, PAREP A02 or PAREP A03 because 2 mappers are running and 4 appliers are running in the background. So, you can only define 5 characters for GoldenGate parallel replicate. GoldenGate will automatically add appliers and mappers, names and numbers. Okay. So, add replicate PAREP and then you give, how does GoldenGate know that this is a parallel? You give a keyword parallel. You give a keyword parallel. Then after that, if you want to create integrated replicate, for example, parallel integrated, you use the keyword parallel integrated and then exct. So, if you give parallel keyword, it means it's a parallel replicate. Then if you give integrated keyword along with parallel, that means it's parallel integrated. Now, if you give nothing, if you only give parallel, it means it is parallel non-integrated replicate. Okay. So, let us do that. Let us create it. So, now as I mentioned, you are going to learn 2 tasks right now. So, we are discussing about parallel replicate and you are going to learn 2 tasks right now. First is how to create a parallel replicate and second thing is how to upgrade your existing replicate to parallel. Okay. So, what you need to do if So, this is info REPINT. So, this is integrated. Now, our requirement is to upgrade it to parallel non-integrated. How can we do? So, first of all, stop REPINT 09. Stop it. Once you have stopped, add a parallel non-integrated replicate. How can you do? Add replicate PAREP. Now, remember one thing parallel replicate cannot exceed more than 5 characters. You can have at most 5 characters defined in your parallel replicate. So, add replicate PAREP. Then, how does it know whether it is parallel replicate? Use a keyword parallel. Now, because we are going to create non-parallel, non-integrated replicate. So, we don't have to give anything. Just give extrail. Now, if you want to give parallel integrated, then give parallel integrated extrail. But our requirement is to create parallel non-integrated. So, we'll give parallel extrail. That means it will be non-integrated and it will read from the same location where your earlier replicate process is reading. Okay. So, parallel extrail and same location and then you need to provide because as I told parallel non-integrated replicate requires a checkpoint table that is the variable. So, you need to provide the checkpoint table. So, checkpoint table which we created here parallel replicate added. So, if you do info all now, you will see both your replicates REPINT 09 and PAREP. So, if you do info PAREP, you will see that how do you know what type of replicate it is parallel. So, if it says only parallel, that means it is parallel non-integrated. If it says parallel integrated, that means it is parallel. Okay. Now, how do you upgrade this parallel replicate? How do you upgrade your existing replicate which is running parallel replicates? What you need to do? You do info REPINT 09. Okay. Now, your REPINT 09 has read the data until this point on sequence 16 and RBA. So, what you need to do? You need to do ALTER PAREP EXT SEQ NO 16 EXT RBA 65725. So, basically what will happen? Until this point REPINT 09 has read. From this point onwards PAREP will read the data. Once you are done, then start PAREP. So, there is a command like if you want duplicate suppression remains active. So, what happens is, if any transaction has already been processed, GoldenGate will not process those transactions. So, if you want those transactions to be processed again, start your replicate with no filter dupe transaction. No filter dupe transaction means it will not filter the duplicate transaction. Okay. So, we cannot start PAREP. Why? Because we haven't created the parameter file yet. So, each GoldenGate process needs the parameter file for it. So, we will create the parameter file. But for now our PAREP, our existing parameter, our existing replicate has been upgraded. Now, you can use this to start. But we will create the PAREP. Then quickly copy VIEW PAREM REPINT 09. Copy the content on this. Okay. So, user ID alias is this. This is okay. This is okay. Now, we have to use these parameters. We have to use the parameters map parallelism which will, which we are going to use for. So, we want to run four mappers and we can use four appliers as well. Okay. Now, if you want to use, say for example, MIN APPLY PARALLELISM, say for example, two and MAX APPLY PARALLELISM as eight. So, you don't need APPLY PARALLELISM in that case. In that case, it will be auto. APPLY PARALLELISM will be auto. So, I will just comment this out for now. Okay. Once you are done, save it and close it and start. I did, I did PAREM PAREP. Oh, I didn't change the name here. It should match PAREP with the name of the replicant. Now, you are done, save it and close it. Now, start PAREP. So, once if PAREP starts and run successfully, that means your existing replicant is successfully upgraded to parallel. But it doesn't start. How can I find it is not running? How can I find the issue? VIEW REPORT PAREP. If you go down, it says a catalog name is specified for target table but no schema name is specified for source table parallelism. Okay, let us see what does that mean. So, it says catalog name is but no schema name is specified for source table parallelism. Oh, we have given MAP SPACE. It should be MAP UNLESS 1. That's right. There was space. So, it was considering MAP as this MAP. Start PAREP. Now, let us see info all. So, can we say that this parallel replicant is running right now? No, because time since checkpoint is yet not less than 10 seconds. Okay, now it is less than 10 seconds. Now, it's okay. So, you can now say successfully that your parallel replicant is now. So, your existing replicant is now successfully upgraded to parallel. Now, what you need to do? You just need to do some transaction. Anyways, in our environment, we have heartbeat table. So, if you check PAREP statistics, you will see that it is already implementing heartbeat table data. So, existing replicant will show until and unless you delete. So, if you don't want to use it, you can delete it. So, now if you want to do, you can successfully go ahead and delete. So, until you delete it, it will show. So, if you don't want it anymore, you can delete. Okay, another thing like if you are concerned that it might start automatically or other thing. So, what you can do? You can in the parameter file, you can comment this first line, name of the replica or you can put some jump character. So, even if you try to start that replica, it won't start. So, sometimes what happens is we don't want to delete this process immediately. We want to wait for five days or a week before we delete the existing one. So, that to ensure that new one is working, right? But to ensure that no one accidentally starts it, right? So, what we have done is we have edited that replicate parameter file. We have, I have commented out the name. So, even if now if someone tried to start, you won't be able to see an unexpected value. First parameter must be one