How Oracle Goldengate reads data from Oracle Database

Hi everyone. Welcome to our 30 day learning Oracle GoldenGate classic architecture training. Today is day 1. 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 Ashish Agarwal 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 Oracle GoldenGate related videos. Thank you. So first of all, before we proceed, let us discuss about Oracle database architecture. Now GoldenGate is a product which works on database, right? Now, what is GoldenGate? So GoldenGate is a CDC tool. CDC stands for Change Data Capture Tool, right? So GoldenGate is a CDC tool, Change Data Capture Tool. So what does it mean is it captures the changes from the source database and then whatever change data has been captured, it applies it to target database. Okay, so GoldenGate does the replication from source to target database. Now for GoldenGate, the source has to be a database, right? So the best part with GoldenGate is GoldenGate was developed in 1997. It was a standalone company at that time. It wasn't Oracle. In 2009, Oracle overtook it and since then it is known as Oracle GoldenGate. Now the thing is GoldenGate was a product which was developed or whose concept was there basically prior to Oracle overtook it. So initially the best part with GoldenGate is when GoldenGate was developed initially, it was developed not only keeping Oracle in mind but keeping all the databases. So initially when it was developed, it was developed for tandem databases. So which are used for ATM network, but later on it support like it wasn't basically developed for Oracle. In 2009, Oracle overtook it and it made it marketable. And of course it made some changes in the technology as well. So the best part with GoldenGate is the way it works for Oracle in the same way it works for SQL Server, in the same way it works for MySQL, in the same way it works for Postgres databases. So any database, so I have named few, but whatever database is supported by the GoldenGate, it works in the same way. Okay, now for each database, now the question is from where GoldenGate is able to capture the data. Does GoldenGate reads the tables directly or how does it able to capture the data? So the answer to the question is each database has a transaction log. Each database has transactional log from where GoldenGate reads the data. So GoldenGate is a change data capture tool. So each database, when we talk about Oracle, these transaction logs are called as, can anyone tell me, what these transaction logs are called as in Oracle database? Redo logs. Redo logs or backup of redo logs are taken on disk which are called as archive logs. So in Oracle, these transactional logs are called as redo logs or archive logs. In SQL Server, MySQL, they are called as transaction logs. So basically what happens is whatever changes are happening in the database, this concept is there with each database that the transactional data, whatever transactional changes are being made to the database, all changes gets returned to the transactional logs of the database. So these transactional logs in case of Oracle are called as redo logs or once the backup of redo logs is taken on disk, they are called as archive, okay. So it is very important for us to understand how GoldenGate works. So let us discuss about Oracle database architecture to understand. So if I have to consider Oracle database, so Oracle database is divided into two parts. First is instance which again in turn is divided into two parts, memory components and background processes, right. So Oracle database is divided into two parts, instance and Oracle database file. Again, the instance is divided into two parts which is memory components and background processes, right. Now, Oracle database files are the files. Basically, Oracle database contains the files which are data files. Data files are the files which are actual files which contains the actual data. So say for example, when you do select star from employee table. So from where do you get that information from? You get that information from the data stored in the data file. Then you have control files. Control files are the files which are meta, which contains the metadata information. They are the most critical files in Oracle database. If you lose them, you lose the data in the database. So control files are the most important files at the database level. Then you have redo log file. So redo log file contains the change data. So whatever changes are happening in the database, those changes gets returned into the redo log. Now these redo logs are in limit, right? They are in limit. So say for example, you have defined 150 MB of redo. So three reduce you have defined a 50 MB. So subsequently they will include they will get spilled right? So what happened is what happens is you take the backup of these redo log files on disk on your operating system. So the backup of redo log files when taken on the operating system your OS level. They are called as archive log. So archive log files are nothing but backup of your redo log. So archive log file contains the changes. Whatever is being made to the database. So remember one thing your redo log files or subsequently archive log files contains committed as well as uncommitted data. They contain both DDL as well as DML chunk. Okay, so any change which is being made to the database gets returned into the transaction log in case of Oracle these transaction logs are called as redo logs or backup of redo logs, which are called as okay. Now, how do these redo logs or archive logs get the data from so the thing is when we talk about any change data capture tool including Golden Gate or any of its competitors. So redo log files or the archive log files the transaction logs basically are the source from where they fetch the data from or read the data. So I again repeat for Golden Gate these redo log files or backup of redo log files, which are called as archive log files. They serve as the source. So Golden Gate processes reads the data from these transaction logs captures the changes and then those gets applied on the target data. Okay. Now the question comes how do your redo log file get the data from and of course subsequently the data goes into our table. So how do they get the data from so the thing is this is internal concept and I like right now. I'm only just going to discuss about redo log files or archive log files. What is related to Golden Gate this Oracle database architecture itself is so wide if if we decide to discuss on this it might take us four five session. So I'm going to discuss only what is related to Oracle Golden Gate these redo log files or archive log files now the question comes how do these redo log files or archive log files get the data from so the thing is your instance has different memory component library cache data dictionary cache, right? So these are the memory component library cache data dictionary cache database buffer cache Java pool redo log buffer large pool and there are other memory components along with it. There are background processes as well different background processes are p-mon s-mon database writer, which is called as DBW are then you have log writer, which he which is called as LGW are then you have checkpoint. So this is database checkpoint. We are talking about because we are talking about database architecture. So this is database checkpoint. Okay, and then you have other background processes as well. So how do these redo log files get the data from is they get the data from your redo log buffer with help of log writer. Now the question comes how does this log redo log buffer get the data from let me give you an overview. So the thing is at the database level stay for example at your SQL server or your toad or SQL from you ran one command which is update dpt1 dpt set location equal to Boston where department number is equal to 10. Now what will happen is this is a change which is made to the database. Now this buffer this particular buffer gets returned into the database buffer. Okay, so so the thing is don't think that once you make the changes, so we have to understand guys one thing internally how database work. So just like your car when you turn on your car ignition in so you see that your car is getting started right? Your ignition is getting turned but internally there are there are a lot of things which happens right and then your car ignition turns on right same thing applies to database as well. You see that your when you like update the data or select the data for you. It looks like it's happening internally. There is nothing which is happening but internally there are a lot of things which happens after that you get that information or if after that you get the any transaction getting applied. So the thing is you update any record for example, so that buffer internally gets dirty. So that sorry that buffer is called as dirty buffer. So when you select what happens is that select statement goes to database buffer cache, but it's not dirty any change data, which is there in buffer. If there is any change data, then the buffer is called as dirty. So dirty buffer refers to change the buffer buffer with the change data. So say for example, you update the record. So what happens is data from data files will go to both database buffer cache. Once the data is there in database buffer cache. Once it is dirty. The copy of the dirty buffer gets returned into the redo log book. The copy of the dirty buffer gets logged into the redo log book. Now data from redo log buffer gets returned to the redo log file with help of this log writer. So log writer process writes the dirty buffer from redo log buffer to redo log file. Now your log writer process writes the data from redo log buffer to redo log file under these conditions. Whenever there is commit the data from redo log buffer to redo log file will be written or when your redo log buffer is one third full or when there is one mb of reading or every three seconds the data from redo log buffer gets returned to redo log file or before database writer process writes the data from database buffer cache to data file. So under these conditions your log writer process writes the data from redo log buffer to redo log. Okay. Now the question comes again. If the data directly has to be returned from database buffer cache to data file with help of database writer process. So guys once you update the record, so the change data gets written here. However, the data which has been changed directly gets returned from database buffer cache to data file with help of this database writer process. Now the question comes what is the need of this redo log file? If the data is directly getting returned into the data file, then why can't database writer regularly read the data from database buffer cache to data file. What is the purpose of the redo log? So guys the purpose of these redo log files is to redo the data. I'll give you one example very good example. Okay. Now suppose today in your account you have $10,000. Okay $10,000 or 10,000 Indian rupee. Now you decided to deposit $5,000 more into your account. Okay, so you went to the bank you went to the cashier over there you handed over your $5,000 and ask her to deposit into your account. Okay. Now the cashier is accessing the front end. Okay, so from front end he or she will mention that this account number has to be credited with $5,000 already, right? So the updated balance should be 15,000. So at the front end they made their made the change at the front end the changes made you are given the receipt and you went home considering that the amount is will be reflected in your account. But what happened was at the front end the request was sent. However before it could be applied on to the target database before that particular transaction could be applied on the trunk on the target database the database crash due to some reason it could be power failure. It could be any other reason, right? So what happens now is you come home you found out still it's $10,000 in your account and you are flabbergasted to know this because it should be 15,000 and you are losing 5,000 currency, right? So what happens in this scenario if you lose this money bank will lose its credibility. It will lose its customer. It will lose its goodwill right along with that. It's also the database issue. Now. The thing is what happens is now your redo part comes into picture. So the thing was the front end request was sent the dirty buffer was already made the data from redo log buffer to redo log file was already done. But before because when you see the actual data, you see the actual data from the data file. However, what happened was before the database and writer process could write the data from database buffer cache a to the data file your server was that right your server got crashed. So now what is how can you then apply those transactions which were missed for that purpose? You already have those redo or archive, right? So what happens is when the power failure due to which your server went down when your server comes up after the issue, what happens is before the database could be restarted recovery is made from the available reduce or other. So once that recovery will be made during that time your your updated balance in your account would be shown as 15,000. Okay, so that's the whole purpose of this redo. So redo are used by all the change data capture tool, not only Golden Gate any Golden Gate competitor like Shell SharePlex attunity or anyone any other product when we talk about each product any change data capture uses your reduce your your basically the transaction logs to apply those changes which were missed so Golden Gate uses these redo log files or archives for capturing the change data because your reduce contains only the change that whatever changes are happening to the database. In any of the object those changes gets returned into the redo log file and again, I repeat I have listened to people saying that redo log files or archives contains only committed transaction guide. That's not true. That is a misconception among or I can say misunderstanding as well that these redo log files or your transaction logs contains only committed transaction. That's not true. Your transaction logs contains committed as well as uncommitted transaction. Okay, so for Golden Gate your redo log files or backup of redo log files which are called as archive log files. They can they access source the Golden Gate or any change data capture to take capture the changes from the file. And once these changes are captured, they action accordingly. So the thing is for Golden Gate or any change data capture tool to work. First of all, you have to ensure that the changes are getting logged into the transaction log. Remember if the change is not getting logged into the transaction log, then Golden Gate or any change data capture to go to be able to capture the reason being the source of any source of Golden Gate or any database is the transaction log. Okay, so for Golden Gate to capture the data, the data should be first of all logged into these redo log files which subsequently would be logged into your archive log. Okay, so this is about Oracle database. Now when we talk about any other database, each database has its own transaction log. So when we talk about SQL Server, MySQL Postgres or any other database, each database has its own transaction log. In case of Oracle, these transaction logs are called as redo log or backup of redo logs, which are called as archive log.