Oracle Goldengate Initial Load and Zero downtime Migration and Upgradation Strategy

So today we are going to discuss about initial load. So initial load is the process of loading the source database data into the target database initially. So that process is known as initial load. Now for performing the initial load you have various options such as at the database level you have export import or you can do arm and duplicate or you have transportable table space or you can use oracle golden gate as well for performing the initial load. Now how golden gate initial load is different from its database utilities let us see that. So first of all with oracle golden gate or I remember golden gate is a golden gate initial load is a feature which can replicate the day which fetches the data from table and applies the data into the target table. So in that scenario you don't have any means the first requirement first prerequisite is the structure of table should be present on source and target. So with export import or arm and duplicate the if the structure of table is not created that could be created. However with this golden gate it is mandatory to have the first prerequisite is structure of table should be created on both source and target. Remember golden gate initial load can just transfer the data. So suppose on the source table there are 10,000 rows only those 10,000 rows could be transferred apart from that no constraint no grants in simple words meta data cannot be transferred golden gate can only transfer the data from source database to target database. So if the structure of table is not present on the target database in that scenario your initial load process is going to fail. Now let us see how you perform the initial load. So consider let us see select star from dpt1. So on source we don't have any rows and on target we have how many rows so let's start from dpt2. So we have six rows. Okay so let us truncate the tables on both source and target sorry on target anyways on source we don't have any data. So we'll be truncating the table on dpt1 dpt2 sorry truncate table dpt2 and truncate table dpt emp2. So I have truncated both these tables on source and target. Now prior to start the initial load remember your replicate process on target should be stopped before you can start the initial load. So now our replicate is stopped on target and sorry select star from dpt2 and select star from emp2. So there are no rows selected. Now let us insert let us see select star from emp1. So let me do at the rate dml.sql. So there are some there is some rows which has been inserted using dml.sql on source. So if I see select star from dpt1 there are four rows and select star from emp1. There are seven rows on source. Now on target you don't have any row. So this process of loading the initial data is known as initial load. Now in our scenario we have only four and seven rows. So it doesn't matter we could use any process. However, in ideal scenario there would be lots lot of data which you will be doing. So let us do this initial load of four and seven rows using oracle rule. So what steps to follow how to follow let us see. So first and foremost requirement is your replicate on target before starting the initial load should be stopped. Your extract and data pump could be started. However, your replicate should be stopped. The reason being why replicate should be stopped is whatever transactions you are doing in the meanwhile those will be captured by the extract process. However, you cannot apply them by the replicate process because there is no initial load of data. Consider a scenario wherein you have 500 GB of source database already present and today you decided that you will have the replica means you will create a secondary database which will be replicating data from source to target. So this database is running for past two years and so far has 500 GB of data. On target you don't have any data right now. So GoldenGate can take care of the transactions or changes which are happening from in the database from today 23rd of March 2017. However, you first need the data prior to 23rd of March that is 500 GB of data. So first of all you will perform the initial load of 500 GB but before starting the initial load there is a process which you need to follow. So what you will be doing you will be first of all preparing the environment. In the first step you will be preparing the environment by installing Oracle GoldenGate on both source as well as target database. Remember when you are creating a secondary site the primary site is going to be connected to the application and application is going to continue the change to source database. So now you will prepare the environment you will install Oracle GoldenGate along with that you will create the extract process and data pump process on the source database. However on target you will create the replicate process. So your source and target will be your source on source you will create extract and data pump and on target you will create the replicate process. So in the first step install GoldenGate create extract data pump on source create replicate on target. In the second step what you will do you will start the extract process and data pump process. So what will happen on 23rd of March at 10 p.m in the evening 10 p.m at night I should say whatever changes are happening from 23rd of March 10 a.m your extract is going to capture those changes and you can start the data pump which will write data to remote. Next you're going to note down the SCN of the database. So consider let me note down these steps. So first of all you will prepare the environment by installing Oracle GoldenGate and Oracle Database. In the next step what we are going to do create the extract and data pump on source and replicate on target. Okay now your application is going to make the changes. Now your initial load process whatever you whatever strategy you are using like export import arm and duplicate that is going to take around that will take some time right 10 hours 8 hours maybe more maybe less depending on size of data which you are doing initial load on. However during that time your application is connected to the source database you're not going to take downtime just for creating the secondary site. Right so there must be some extraction of data on source side which will replicate the change once initial load complete. So what you will do in the second step you will start the extract and data pump process on source and write data to remote. So whatever changes are happening from 24th march 23rd march 10 pm those changes will be recorded. In the next step what you'll do you'll note down the SCN of database. Select current underscore SCN from V$ database. Now consider this comes as 1 2 3 4 5 6. Current SCN of database is 1 2 3 4 5 6. In the fourth step you will start the initial load process and take the consistent image on means you'll you'll do the initial load till SCN 1 2 3 4 5 6. So you will start the initial process initial load process and do the initial load on target database till SCN 1 2 3 4 5 6. Post 1 2 3 4 5 6 in fact before 1 2 3 4 5 6 your extract is capturing the data. Consider when you started the extract process the SCN was 1 2 3 4 5 4 and when you checked on database it was 1 2 3 4 5 6. So definitely extract process is going to capture the means you have started the extract process before the initial load. So definitely why do you want to start the extract process before initial load so that you are not missing any data. As soon as you start the initial load next process next step would be wait for completion of initial load. Now suppose you started the initial load at 10 pm on 23rd March. It takes 10 hours to complete it is 500 GB of data it takes 10 hours. Now here that by initial load process we mean that we can we can use any utility like database utility export import or arm and duplicate or transportable tablespace or golden gate utility or any other way which feels comfortable to you. Now this process whatever step you choose whatever utility you choose it takes 10 hours to complete. So by 8 a.m on 24th March your initial load process will complete. That means that by 8 a.m on 24th of March your target database is in the consistent image which was there at source on 10 p.m of 23rd March. Correct. So your target database now contains the data till 10 p.m on 23rd of March which was there on source database. Now your application was still connected. That means there were lot of changes which were made during the night. So how will you apply those changes. So see your extract process was capturing and data pump was starting. So what you will do in the next step you will start the Replicat process and it will apply the data post SCN 1 2 3 4 5 6. So though extra see extract has captured the data 1 2 3 4 5 4 1 2 3 4 5 5 1 2 3 4 5 6 as well. But when Replicat will try to apply it would be a duplicate transaction though in 12c automatically duplicate transactions are suppressed. So you need not to worry from 12c. But if you want to be sure you can start the Replicat process alter Replicat int rep after CSN 1 2 3 4 5 6. So what will happen Replicat process is going to apply the data on to target database after SCN 1 2 3 4 5 6. So there is no duplication of data. So suppose this process of applying the incremental changes from 10 p.m to 8 a.m took two hours. So that means that by 10 a.m in the morning on 23rd of March 24th of March the both source and target databases will be in sync. And from now this point onwards your real-time transactional changes will be applied from source to target data. So while performing the initial load you have to follow this step. So first of all prepare the environment by installing Oracle GoldenGate on both source and target. Next is create the extract data pump process on source and Replicat on target. Next step start the extract and data pump then note down the SCN perform the initial load of source database into target database till that particular SCN noted in above step and then once initial load completes start the Replicat wait for the two databases to be in sync and then your normal process will continue. Similarly using the same process you use while you upgrade or migrate the databases. We say that GoldenGate supports zero downtime migration and upgradation. How is that possible? So consider suppose you are migrating from AIX to Linux server. So you consider your source database as AIX server and consider the target over here in Linux server. So what you'll do you'll perform the same steps what we have followed. You'll prepare the environment by installing Oracle GoldenGate on AIX server as well as Linux server. You'll add the extract and data pump on AIX server and Replicat on target migrating 12c server. Next you will start the extract process and data pump process on AIX server. Once you start note down the SCN of the database on source database. SCN means what? CSN right? Okay SCN is system change number. Yeah but then that's from the redo log. Yeah that's from the redo log. So SCN converts to CSN in GoldenGate. The same SCN is referred as CSN in GoldenGate. CSN refers... So what are you... So at the GoldenGate level it is referred as CSN. Same value? The value will be same there would be no difference. Okay. Only thing is at the database level it is known as SCN on the GoldenGate level it is known as commit sequence number. The reason being SCN is generated for every transaction however CSN GoldenGate follows it in in the form of commit sequence number. So whatever transactions are there GoldenGate Replicat applies in the same order they are they have been committed on source. So SCN is also moving forward right? SCN is like suppose right now current SCN is 1, 2, 3, 4, 5, 6 next would be greater than 1, 2, 3, 4, 5, 6 but not lesser than. In the same way it GoldenGate refers to same as commit sequence number. The value of SCN and CSN is same but the way they refer GoldenGate and database there lies the difference. So database level we call it as SCN and GoldenGate level we call it as CSN. So note down the SCN of the database. Once you noted down the SCN load the consistent image of source database to target database till a particular SCN. As soon as your initial load completes whatever changes happen during the time your initial load was happening from AIX to Linux server applies those apply those changes from source to target server that is AIX to Linux server and then as soon as both the databases the source database and the migrated database get in sync you just need to switch over your application from AIX server to Linux server that is the only downtime you require but at the database level you don't require any downtime that's why we say GoldenGate supports zero downtime migration and upgradation okay Ashish I have questions yeah let's say I want to take a scenario here that I have doesn't matter which environment of course this database on A is running from two years it has 500 GB data B has nothing I want to do a do a do a match like you know replication so I take an export okay let's say I start the export at 10 o'clock tonight as you said okay so I didn't get that so when will we take the SCN? when we have run the export like we have run it at 10 o'clock the objective is that we have to sync both then what? after that we go to the database select start from data database and after running the export then we will see the SCN number any SCN number no see what is the step mentioned before you start the initial load before that you take the SCN so you will come from initial load till that particular SCN only so now let's say I started this I mean the database is running I take an SCN number it's generating SCN right so I just say I am ready to take the export I took the SCN number 123 okay and then I started the export right okay I started the export now export is taking time and it's exporting in 10 hours the export is complete now I still am not sure when will we take the SCN how will it be synced while giving the export command you have the option of flashback underscore SCN so you need to provide that whatever SCN you have noted down in step number 3 you have to provide that SCN in step number 4 otherwise if you don't provide the SCN over there what will happen the export will be taken till what time your so there won't be a consistent export that's what we are trying to see no consistency that is what we are trying consistent backup you have to take consistent export that's why we are noting down the SCN and you will do initial load till only that particular SCN the reason is that from that SCN or only that particular SCN till this SCN you will take the you will perform the initial load till SCN 123456 SCN what is SCN I will start taking the export from that SCN number why you would start taking the export from that this so if you start taking the export from this particular SCN what about the earlier data how will you get the earlier data what is the objective of performing the initial load the objective of initial load is to do the initial load from SCN number 0 to SCN 123456 that is our so I take an export I start what is the command expdp blah blah blah dump file is equal to something something and then do you remember there is a parameter flashback underscore SCN you can do expdp hyphen there is a flashback underscore SCN parameter so you can use that ok so I will do an export and it will be up till that point it will be up to that it will be up to that particular SCN and then yeah so we will be running the extract after that SCN so that's why for doing that we use the command alter replicate replicate name after CSN and CSN number so here why we are doing this the reason being we started the extract and data we started the extraction of data before the means there are some transactions which would be common which has been extract which would be extracted by the extract process and which are already applied to the target database right so there would be few transactions so to overcome that you can start alter replicate int ref int ref after CSN 1 2 3 4 5 6 now from 12.2 golden gate there is a new feature it is automatically integrated this initial load process is automatically integrated with expdp and impdp so automatically when you start the replicate process you need not to give from 12.2 onward you need not to give after CSN automatically golden gate integrates itself with the initial load process and then it starts from that particular and there is one more feature the duplicate transactions are automatically suppressed so as the technology means golden gate is getting advanced we need not to worry about this after CSN however in the earlier version in that scenario you will have to use this ok that's so one more thing Ashish now please correct me if I am wrong so extract or replicate both side SCN number will be same only transaction which would be written in the extract and replicate will always contain the same kind of SCN number that is the reason how it will be able to this is the way I got it through export we can mention an SCN number and of course take the export up till there import that dump and then in extrail we can start after that so that the trail is capturing everything after that and then replicate applies it then replicate applies it from after till your initial load took 10 hours so our main motive of using golden gate is a database primary database should not be shutdown it should be up and running application users who are getting who are connecting to database they should not face any issue our primary motive is I know there would be lakhs thousand one transaction which may happen so you should ensure that your database is highly available and this process ensures that your database is always highly available yeah because it's running you know while the export is going on also so when let's say let me understand this again if there is any missing piece to it EXPTP we took SCN number 1,2,3 till then our extract is still going on then the database is up and running so when I start the extract it will be after that SCN number 1,2,3 so let's say 1,2,4 no correct see before start okay there see in the step 1 we are we are starting the initial load in step 4 however we have started the extract process in step 2 so definitely extract process must have captured some transactions which are already done by initial load which are already implemented onto the target database during the initial load process and that is the reason to remove the duplicate transactions to eliminate the duplicate transactions we are using alter replicate after CSN and CSN number okay see the reason being okay if you start the extract later on after you start the initial load what will happen now suppose the initial load when you starting is 1,2,3,4,5,6 now when you now consider a scenario when you starting the extract later so when you start the extract the SCN was 1,2,3,4,6,0 so how will you get the SCN 1,2,3,4,5,7,5,8,5,9 so you will you may lose some transaction in that scenario so to overcome the possibility of losing any transaction you have to start the extract process before starting the initial load process correct there is no need to give any SCN or CSN just extract it but when we will apply it in replicate then we will only say after that after CSN now again I am repeating from 12.2 onwards it is no longer required prior in fact from 12c if you are using the version 12c it is you can choose to ignore it however if you are using prior version in that scenario you will have to use it or you may have to use handle collisions because there would be duplicate transactions right so if suppose at SCN which you are 1,2,3,4,5,4 there was an insert now that insert is already applied during initial load now when you start the replicate your replicate will fail stating that the row already exists handle collision will have to be applied in replicate again I understood if I am using golden gate for initial load as well as for the synchronization we haven't discussed so far the golden gate initial load we are going to discuss that now so the same process you need the process will always be same in fact for even if you are the steps are going to be same even if you are using export import if you are using impdp if you are using armand duplicate or if you are using transportable table space or if you are using this golden gate initial load the steps will always remain same only thing which will be different would be how you are performing the initial load so there are many to do that there is golden gate as well which you can do now how to perform golden gate initial load we are going to discuss that but the same process same steps are there when you are performing golden gate when you are upgrading or migrating oracle database using oracle golden gate in that scenario as well you will be following the same steps so let us now do what is golden gate initial load how you are going to perform golden gate initial load so first and foremost requirement with golden gate initial load is your replicate on target should be stopped so we have already configured we have already installed oracle golden gate on ggate 1 as well as ggate 2 our processes are already configured the chain these are the chain synchronization processes which are already configured so prior to starting the initial load we have to start the extract in datapump however ensure that the replicate process on target is stopped next is you have to start the initial load now we are going to do the initial load using golden gate initial so how is golden gate initial load different from oracle export import or other utilities the first and foremost requirement for initial load is the source and target structure should be present so your table metadata should be present on both source and target remember golden gate initial load can just transfer the data from source and target it cannot transfer the metadata information cannot transfer constraints it cannot transfer grants or any metadata information could not be transferred so now what is the difference between golden gate initial load and normal chain synchronization so the difference between golden gate initial load is in golden gate initial load there are only two processes extract and replicate there is no datapump there is no trail files so you only have extract and replicate so extract process directly reads data from the tables and apply it on target database so there is no storage of this data anywhere the extract process reads the data directly from tables in contrast to your online chain synchronization extract which reads data from your transaction log in golden gate initial load extract it reads data from your tables directly there is no storage of data anywhere it directly feeds that data from source table to target replicate and replicate applies it to target database so how do you create these processes let us see that so first of all you have to define your source and target so our source is DEPT1 and EMP1 and target is DEPT2 and EMP2 on ggate2 and schema is ggtrailing2 so now on source what you will do you will create a extract process add extract extract name source is table so add extract extract name source is table now remember you add the you can give any name to the extract process it should be 8 characters long and see we are providing source is table over here press enter now once you have added the extract process next is you need to create a parameter file for this edit param load one so first line of every process is same process type and process name next is user id user id is let me copy from int exe edit param so user id next is you have to give target details so you have to give remote host and manager port over here so remote host is 192 168 56.103 and manager port of target is we need to see from ggate2 by doing info mgr so target manager is running on port number 7809 so 7809 next is rmt task replicate and group is load2 we are going to discuss this so it means that remote task is replicate and load2 process is going to apply to target data let us see this parameter I will make you understand once we configure the target process and then you need to provide the table specification so table pdb1.gg training1. dept 1 table pdb2.gg sorry pdb1 .gg training 1. emp1 put semicolon save this and close this now your source side configuration for initial load is completed the source side configuration for initial load is completed now we will jump to target and create the replicate process over there as I mentioned only two components are there while performing initial load using golden gate extract on source replicate on target there is no data pump there is no trail files and the source for extracting the data is table so extract will directly read the data from table and apply it to target database so now on target we need to add replicate load2 you can give the name again up to 8 characters any name I am mentioning it as load1 on source and load2 on target and special run you have to give initial load replicate process as special run press enter so it says replicate edit once you have added the replicate now you have to create the parameter file for this so do edit param load2 so here you will provide first line is always process type and process name next is user id user id I will copy from view param int rep I will copy from here it would be same copy so I will do edit param load2 so do this next is assume target desk this parameter is using you use when this parameter you use when the structure of tables between source and target matches so from 12.2 onwards automatically you have to use assume target desk the reason being the metadata information is contained in the trail file more on this we will be discussing from during the I think most probably during the end last session assume target desk we don't need in 12c is that what it says no we need assume target desk in 12c as well so we always needed it we always need assume target desk it means that assume the target definition similar to source now once you have done it then you have to give map gg training 1 dot pdb 1 dot dpt 1 to target gg training 2 dot pdb 2 dot dpt similarly map gg training 2 dot 1 dot pdb 1 dot emp 1 target gg training 2 dot pdb 2 dot emp 2 mapping help me recollect assume target is what it's always in replicate or it's always it's a pump right replica see assume that means assume the target definition that it is similar to source got it got it thank you save this close this so once you have done this now see remember one thing the initial load process won't be shown to you when you do in fall they have been created their checkpoint file will be present their report file will be created their parameter file will be created however they won't be shown into in fall the reason being it makes sense you will be creating the initial load process only for once they would be used only for once after that they won't be required anymore so when you do in fall though they are actively present you can find their presence in all the parameter file you can find their present everywhere in every directory which are required for golden gate functionality however they are not shown when you do in fall in in fall only online chain synchronization processes are so sure so now once you have done the configuration your initial load configuration between source and target is done now you have to start the load one process now before starting the load one process let us discuss about this line what does this mean it means that RMT task is replicate and group is local so if you see the line in ggate 2 when we added replicate we gave the while adding the line while adding the replicate of initial load we give special run now what happens over here as soon as we start the load one process on source your load one process will communicate with target manager which is present on this host on this port number and then this port number will start the load to this manager process on target will automatically start the load to process on target so when we will be starting load one automatically load two will start and will finish the initial load so let us start the load one process you will find see that we don't need to start load two let us start load one when I start load one you can check info load one see right now the status says starting so it has stopped without starting even it has stopped without starting at all so let us see view report load one so it says access denied the reason being we have to while performing the initial load in manager parameter file we need to use parameter this parameter access rule program star IP address allow so we need to allow the connection being made for initial load so I just included this parameter on target but I didn't refresh the manager parameter remember to make the changes effective in the parameter files you need to stop and start however this condition does not apply for manager manager process parameter file could be refreshed by firing this command refresh mgr so now automatically the change which I made after starting the manager you will find that automatically now the parameters are effective now I will start load one so when we do info load one now it still says it is starting again it is stopped it hasn't processed anything so let us again do view report load one it says extract abending it says replica cannot apply transactions to catalog gg training two while it is logged into catalog pdb two so let us go to target and see info view param load two so we are giving I think okay c hash hash gg user two password is welcome one correct view report replicate cannot apply transactions to catalog gg training two while it is logged in catalog pdb two so it is okay is it something to do with no plus where you have logged in no see okay I think it has to do with mapping it is mapping see what I have done I have given gg training one in place of database name and in schema name I have given pdb one so we have to give pdb one dot gg training one mapping was wrong mapping was wrong correct pdb two dot gg training two one more thing while we are doing that allow replicate allow extract the manager you know you had that allow in the manager earlier I think in the replicate side manager correct that allow was for the IP address of the extract manager correct correct so means if you ask me why it comes I am really not sure but the solution is present this solution means this issue access denied error it comes only during initial load and the solution has been provided on support.oracle.com so I think basically it is it comes because the two data two servers are in different subnet mask so as per that note it is being written over there this needs to be the only added on the replicate side yeah on the target side obviously from source to target correct got it target pdb two dot gg training pdb two dot gg training two so I guess now the mapping is correct pdb one yeah so now mapping is correct so now let us start load one once again on source so start load one so now when I do info load one now ask initial load will get complete quickly because it does not have much data it has only four records see it has running now it will run in sequential manner so it has already completed info load one if you see it has already completed now see it has stopped now it has stopped in emp1 and record7 so now when we go to target gget2 we do select star from load2 sorry dpt2 four records have been successfully inserted select star from emp2 7 rows initial load is successfully now this initial load was completed quickly however it depends suppose you have 10,000 million rows this initial load will take lot of time also this initial load requires lot of network bandwidth for performing the initial load the network bandwidth should be good so this initial load is generally recommended using golden gate only when you have small amount of data you don't have much data to transfer if it is big data suppose there are rows and 10 million or 100 million so if you are performing this initial load using golden gate the data will flow over the network and you can consider how much network bandwidth it will require so oracle always recommend the golden gate you all have provided the initial load however it should be used only in cases where data is small not for big data also the initial load should be performed how what is the process of performing the initial load so initial load is performed in the same order you are providing the tables in the parameter file so dpt1 initial load will be first done until and unless it gets completed then only emp1 initial load will start so the initial load is performed in the same order as they are as you have provided into parameter file that's why whenever you are loading the whenever you are doing initial load between tables having primary key and unique key relationship primary key and foreign key relationship in that scenario first you should mention the primary key table and next you should mention the foreign key table otherwise what will happen if you do the initial load of foreign key first and then initial load of primary key table your initial load process will fail the reason being the parent record is not found so the initial load is performed in the same order you are performing you are providing in the parameter file another thing is you can perform the initial load till particular scn as well so in the parameter file you can use the where clause we are going to discuss the data filtration technique in our next session you can use the where clause over here and you could give after csn and in the parameter file and from till that point your initial load will be completed automatically so initial load how you do the main prerequisite is between source and target your the first and foremost prerequisite is the structure of table should be present on both source as well as target in case of export import it is not mandatory golden gate initial load is meant only for doing the data transfer cannot do the metadata transformation from source to target and another important thing to keep in mind is initial load should be performed only for small tables for big tables you can use it is not that you cannot use but consider a scenario wherein you have huge table and if you are sending it over the network it could cause network jam and suppose the network goes down in between what happens the initial load process will stop then you will have to restart it once again also while giving the tables you have to keep the relationship between parent table and child table so parent table should be loaded first then the child table can we use compression while transferring big tables see golden gate transfers just the data so there is no possibility to use the compression over here so whatever data is there on the source table suppose there are 10 rows so data is not transferred in megabytes or bytes or gigabytes data is transferred in form of rows over here so you can see info load 1 in fact you can check the same kind of statistics which are generated while we perform the normal online chain synchronization the same kind of statistics are generated in this scenario as well you can check the stats over there it will show you that so far 100 inserts have been transferred so far 500 has been there 10,000 million rows have been transferred okay so in the next session we are going to continue on this and we are going to discuss about data filtration and transformation techniques thank you for listening please like and subscribe to our channel see you in the next session bye bye