Oracle Goldengate Capablities and Components

So hi everyone. Welcome to Oracle GoldenGate session nine. So today let us discuss about Oracle GoldenGate concepts. So Oracle GoldenGate can support number of different business requirements like business continuity and high availability. So GoldenGate ensures that your business continuity is always maintained and your database is highly available. So for instance, you're replicating your database from primary location to secondary location. So your primary server is kept in Dallas, secondary is in New York. So if your Dallas server is down due to some reason, due to power failure. So whatever data is, whatever transactions are happening in Dallas, they're getting replicated to New York server using Oracle GoldenGate. So GoldenGate ensures your business continuity is not hampered and your database is highly available. GoldenGate supports zero downtime data migration and upgradation. So with GoldenGate, you can upgrade and migrate the database with zero downtime. GoldenGate support decision support system and data warehousing. So GoldenGate along with ODA is one of the best business intelligence tool in the market. So GoldenGate with GoldenGate, you can also create data warehouse. So data from multiple sources can be loaded into single target. You can integrate the data and consolidate the data using GoldenGate as well. So you can broadcast, you can do data consolidation using Oracle GoldenGate. So these are the solutions which are supported by GoldenGate. So unidirectional. So your secondary instance could serve as a reporting instance. So when we talk about big organizations like SBI, which is 43 TB database, now it runs your OLTP transactions as well as it runs your reporting jobs. Now, what happens is the reporting jobs consumes a lot of CPU. Reporting jobs, when they interfere with OLTP transactions, it tend to hamper the performance. It tend to degrade the performance. It tend to slow down the response of the database. So what you can do, you can use the target server as a reporting instance as well. So your primary server will run your OLTP transactions and your secondary server can run the reporting jobs. So the secondary server is replicating the data in real time. So whatever transactions are happening on source, they are getting replicated on the target server. So what you can do, you can keep your reporting jobs, OLTP jobs running on the primary server and reporting jobs can run on the data which is five to seven seconds later. So your reporting jobs can run from the secondary server. Or you can use GoldenGate in bi-directional environment that is active-active. So your application can be connected to both the sides. Whatever side the transactions are happening using GoldenGate, it could be replicated to the other side. You can use GoldenGate for P2P, load balancing and high availability as well. So data from A to B, B to A in bi-directional. B to C, C to A in bi-directional. And A to C and C to A in bi-directional. You can broadcast the data that is you can have one source and multiple targets as well. So in GoldenGate it is possible to broadcast the data as per your requirement. So on source, you have single source and multiple targets. So when we talk about one is to three, that is one to many it is. So when you have one source and three targets. So for instance, if there are 100 tables on source, you may want to replicate all 100 tables to target one, all 100 tables to target two and all 100 tables to target three. Or as per your requirement, what you can do, you can send filter data. Like 100 tables are there on source, you want to send table number one to 30 to target one, table number 31 to 60 to target two, table number 61 to 100 to target three. It is possible in GoldenGate. So in this scenario, when you're broadcasting, when you're broadcasting the data, how many GoldenGate processes you will require? How many GoldenGate processes are required to achieve the same? So how many processes would we need to configure to achieve this? So prior to discuss how many processes would be required or how many instances would be required, let us discuss about trail file read concept. So when we look at the architecture of GoldenGate, your capture process is capturing the data and writing the data to local trail. Data pump is reading the data from local trail, sending it over TCP IP network and writing it to remote trail. Then replicate is reading the data from remote trail and apply it to target database. So remember, a source can serve as a source for multiple processes. However, a process can write data to only single target. So what do I mean by that is, this, if you look here, there are three processes involved in GoldenGate architecture. Capture, pump and deliver. Each process has a source to read from and a target to write to. So capture process sources, source database, capture process target is local trail. Pump process source is local trail and target is remote trail. Delivery or replicate process sources, remote trail and target is target database. So each process has a source to read from and a target to write to. Now, a source can serve as a source for multiple processes. That means that this source database could be read by multiple extract processes. So for instance, I have created exe1, exe2, exe3, exe4. All four different processes can read data from single database. However, a single capture process can read data only from one source database. It cannot read data from multiple database. Like if you have pdb1, pdb2, pdb3. So one extract process can read data only from one database. It cannot read data from multiple databases. Similarly, a capture process can write data to only one local trail. If it is writing data to lt, it cannot write data to any other local trail file. Like aa, ab, ac, it cannot write. It can write data to only one local trail. Now this local trail can serve as a source for multiple pump processes. That is, pump1, pump2, pump3, pump4 can read data only from one local trail. However, one data pump process can read data only from one local trail and write data to only one remote trail. Now same case applies for remote trail as well. So this remote trail could be read by multiple replicate processes. That is, rep1, rep2, rep3, rep4. These four different replicate process can read data from single remote trail. However, single replicate database can read data only from one remote trail. So if you have, say, rt, at, bt, ct, one remote trail could be read by only one replicate process. So replicate process can read data only from one remote trail and apply data to target data. So if you see here, if we sum up what we have discussed, each process can have only one source and one target. That is, ext1, if it is reading data from pdb1, it cannot read data from pdb2, pdb3. And if it is writing data to lt, it can write data only to lt. It cannot write data to at, bt, ct. Same case applies for pump. A single local trail can be read by multiple pumps. That is, if there is local trail lt, it could be read by pump1, pump2, pump3. However, pump1 can read data only from single local trail. If you have pump1 process, it cannot, it won't be like it is reading data from rt as well as at as well as bt. Similarly, pump process can write data to only rt. It cannot write data to multiple remote trail, rt, st, dt, it cannot do it like that. It can write data only to rt. Then delivery process, if it is reading data from rt, it can read data only from rt. It cannot read data from any other remote trail file. And then it can apply data to only one target data. If we now discuss the broadcast scenario, what we are discussing. So in broadcast, you have one source and multiple targets. That is three targets. So when we talk about one is to three, our main motive is to identify how many minimum GoldenGate processes would be needed to configure the GoldenGate in this kind of environment. So remember, each instance of GoldenGate, each server will have its own GoldenGate instance. I repeat, each server will have its own GoldenGate instance. So on source, you have one server, on target, you have three different servers. Each server will have a corresponding GoldenGate instance. So source will have one GoldenGate instance, target will have three GoldenGate instances. Three separate for three servers. Now each GoldenGate instance can have one and only one manager process. So source will have one manager, target will have three manager processes individually. Now, how many processes would be required? So on source, you have 100 tables. Now, all 100 tables can be read by single extract process. So you can create single extract process ext1, which will capture the data to all 100 tables. Now remember, each extract process will have corresponding local trail and only one local trail will be there. So if your ext1 is capturing the data of all 100 tables, local trail lt will have the data for those 100 tables. Now once local trail is generated, now remember, a single local trail can be read by multiple pump processes. A pump process can read data only from single local trail. However, single local trail can be read by multiple pumps. So you have single local trail generated over here. So each, now how many pump processes would be required? So pump process, if you look at the data, if you look at the pump parameter file, if I do view param dpump, so if you see view param dpump, you will find the remote host location over here. The remote host location signifies that to which target server this pump process will connect to. Now remember, here you can give only one remote host location. In a pump parameter file, you can give only one remote host location. That is, a pump process can connect to only one target. A pump process, single pump process cannot connect to multiple targets. So that means that in case of broadcast, what we are discussing right now, one is to three scenario. In this scenario, you will have one source and three targets, one source and three targets. So for each target, you will have a corresponding pump process because a single pump process can connect to single target. So since you have three different targets now, as a result, you will have to create three different pump. So now what will happen? One extract process will generate one local trail. Now a single local trail can be read by multiple data pump processes. So as a result, one extract process, one local trail, and three data pump process will read data from that particular local trail. Now, a pump process can write data only to one remote trail. So each pump process will have a corresponding remote trail associated to it. So that is, pump one will generate RT, pump two will generate AT, pump three will generate BT. So now you have three different set of remote trails landing on targets. So a single remote trail can be read by one replicate process. However, one replicate process cannot read data from multiple remote trails. So as a result, each remote trail will have a corresponding replicate associated to it. So now on target, you will have at least three replicate process. So I repeat, in case of broadcast, what we are discussing here, one extract process will have corresponding one local trail. Now a local trail can be read by multiple pumps, and there are three pumps which you need to configure at least for three targets. So once you have three pumps configured, now three pumps will have three remote trails on target system, and three remote trails means three replicate processes. So you will have one extract process, three pumps, and three replicate, at least in one SO3 server. Now it is also possible to consolidate the data from many sources to single target. So you have source one, source two, and source three. Now, how many GoldenGate instances would be required in this scenario? When you're consolidating the data, how many GoldenGate instances would be required here? So on source, each server will have its own GoldenGate instance. That is, three GoldenGate instances would be required on source, and one GoldenGate instance on target. Now, each instance of GoldenGate has at least one manager process. So source will have three manager processes, one each for each server, and target will have one manager process. Now, you can consolidate the data using Oracle GoldenGate to single target. So for instance, you have table one to 30 on source one, table number 31 to 60 on source two, and table number 61 to 100 on source two. You want to consolidate the data from all the sources and send it to single target. So what you can do is, you will install three GoldenGate on source and single GoldenGate on target. Each GoldenGate instance will have one and only one manager. So now since on source, you have three GoldenGate instance, as a result, three managers would be there and single manager on target. Now, table number one to 30 will be read by ext1. Table number 31 to 60 will be read by ext2. And table number 61 to 100 will be read by ext2. So what you'll do, you will create three extract processes on source. Now, three extract processes means three separate set of remote trails. Now, since three separate set of remote trails, each remote trail will have a corresponding datapump process associated to it. Now, once you have three datapump process, sending the data to single target. So what will happen? Three datapump process will have three replicator, three remote trails. Now, as a result, three separate pumps will generate three separate set of remote trails. Now, those remote trails, for reading the data out of those remote trails, you will have to create replicator process. Each remote trail will have a corresponding replicator process associated to it. So as a result, in case of many to one, that is here we have three to one. So you will have to create at least three managers on source, one manager on target. Then three extract process, three datapump process and three replicator process and data consolidation. Or you can also use the data cascading in Oracle GoldenGate. That is, when you are not allowed to install GoldenGate on the server where it is installed, where database is installed, then you can create GoldenGate or you can install GoldenGate on a secondary server. And from there, GoldenGate can distribute the data to multiple. So now let us discuss about Oracle GoldenGate components. What we have discussed so far, now let us discuss in detail. So different components and processes that make up our GoldenGate configuration are manager process, extract process, replicator process, datapump process, trail files and a collector process. So these are the six processes, which six components I should say, which typically make up your GoldenGate environment. So manager process, as the name suggests, it manages the GoldenGate resources. So all the GoldenGate resources are your extract, replicate, datapump, trail files and collector. Who manages these? It is managed by the manager process. Extract, what does your extract process do? It does the data extraction part from source transaction logs, or it receives them from 12c only. So basically it does the data capture mechanism. It is the data capture mechanism. Replicate process is a data apply mechanism. So extract process always runs on the source system and replicate process will always run on the target system. Datapump process, it is kind of a FTP. It sends the data over TCP IP network. So remember, it does not transfer the trail file. It is not transferring the trail file from source server to target server. Always keep that in mind. It transfers the data over the TCP IP network. So if you see in this diagram here, first one is initial load, which we'll be discussing during our next session. So this one we are discussing right now, online chain synchronization. So if you look at the architecture right now, it is mentioned that datapump is optional. So it is true that in your GoldenGate environment, datapump is an optional component. Datapump is nothing but secondary extract process. However, it's an optional component in your GoldenGate environment. You can always choose to remove datapump from your GoldenGate configuration. It is not mandatory to use datapump in your GoldenGate configuration. It is an optional component. So what is the work of datapump? Datapump reads the data from local trail, sends it over TCP IP network, and writes it to remote trail. So if you choose to remove datapump from your GoldenGate configuration, then in that scenario, what will happen is when datapump is not there, then your local trail is also not required basically. When datapump is not there, what does your datapump do? It reads the data from your local trail. So if you're choosing to remove datapump from your GoldenGate configuration, then there is no need to have the local trail in your environment. Then what will happen? Whatever primary extract process is capturing, it will keep that in its own memory. It will not store it anywhere. It will just keep it in memory buffer and then sends it over TCP IP network and writes it to remote trail. However, when your datapump is present, extract process captures and writes to local trail from where it is read by datapump and then datapump writes it to remote trail. So datapump process is an optional component. It is always recommended to be used by Oracle. I repeat, as per Oracle recommendation, we should always use datapump in our GoldenGate configuration. However, if you choose to remove datapump from your GoldenGate configuration, in that scenario, your primary extract process has to do double work. Apart from extracting the data from source database, it has to contact with target manager process and then target manager process invoke the collector process on target. Collector process, as the name suggests, it collects the data. Collector process collects the data which is being sent over the TCP IP network and writes it to remote trail. So if you see here, there are six GoldenGate components in our environment. First is manager, second is extract, third is replicate, fourth is datapump, fifth is trail file, and sixth is collector. So, so far we have seen manager, extract, replicate, datapump, trail files, but we haven't seen so far collector process, why? The reason behind this is collector is a background process which always runs on the target system. So how does datapump establishes the connectivity with the target manager? So if you see here, if we look at the datapump parameter file again, so here comes your datapump parameter file. So in the datapump parameter file, you don't provide anything apart from your remote host location. In remote host, you can always provide hostname or IP address, anything you suggest. And the target manager port. So 7810 is the target manager port. So once you start the datapump process, what happens is it tries to establish the connectivity on the manager port specified over here on the specified date. Once the connectivity is established between source and target, then the target manager starts the collector process, which is a background process and it is automatically started. So once the target manager starts the collector process, whatever datapump sends over the TCP IP network, it is collected by the collector process and then collector process writes it to remote. But again, I repeat, as per Oracle recommendation, we should always use datapump. Datapump is a secondary extract process. So let us now discuss each process in detail. So manager process must be running on both source as well as target system before the extract or replicate process can be started. So manager process, as the name suggests, it manages the GoldenGate processes in your environment. It manages the GoldenGate resources in your environment, I should say. So manager process manages the resources, whatever resources it has. So when we talk about GoldenGate resources, so you have extract, replicate, datapump, trail files and collector process. So all these resources, who manages this? It's manager process. If you have to housekeep any trail file, who will housekeep them? It would be the manager process. If you have to monitor, if you have to generate any report files, et cetera, who will do that? It's the manager process. So manager process performs number of functions, including monitoring, starting other GoldenGate processes, managing the trail files and also perform the reporting. Extract process, it always runs on the source system only and is the data capture mechanism. Extract process captures the committed transaction from source database. Whatever it captures, writes it to local trail. Now you can configure GoldenGate extract process for initial load of source data, as well as to synchronize the chain data on the source with the target as well. So if you see, so far we have discussed how to perform online chain synchronization using Oracle GoldenGate. So whenever you do, what do we mean by online chain synchronization? As soon as you do transaction on source database, how quickly it is reflecting on target. So that is online chain synchronization. So, so far we have done that. However, apart from that, GoldenGate capability is to perform initial load as well. GoldenGate can help you in performing the initial load. Just like at the database level, you have export-import, you have ARM and duplicate, you have transportable tables. Similarly, GoldenGate also provides you the capability of performing the initial load. Say for instance, the source database is running for around two years right now, and the database size is 500 GB. However, you decided to build a secondary site, which is getting replicated using Oracle GoldenGate in real time. So prior to starting the GoldenGate replication, you should have that 500 GB of data available on the target database. So that process of loading the source data onto the target database is known as initial load. That process of loading the data initially, process of loading that 500 GB of data which is present on source to target is known as initial. So GoldenGate also provides you the capability to do initial load. So that's what it is mentioned over here. Extract process can be configured both for initial loading of source data, as well as to synchronize the change data on the source with the target. Extract, so how to perform initial load using Oracle GoldenGate, we'll be discussing in our upcoming session. So far we have discussed how to synchronize the change data on the source with the target. Also, you can configure Oracle GoldenGate to propagate any DDL changes on those database where DDL support is enabled. So you can use the extract process for enabling the DDL changes as well. Replicate process, it runs on the target system and is the data apply mechanism. So what it does is it reads the data from your remote trail and apply it to target data. So like the extract process, replicate process can also be configured for initial load as well as change synchronization as well. Now collector process, as we discussed, is a background process. And it is automatically started by the target manager. So as soon as you start the data pump process, immediately once the connection is established, your collector process is started in the background. So as we discussed, it is a background process which is automatically started by manager. So if your data pump process is running, you can always check the ggserr.log on target site if it says that connectivity with data pump is established, then you can ensure that the collector process is running on the target. So there are two types of collector. One is dynamic collector and second is static collector. It again, I say, it is again a background process. So when manager process starts the collector process automatically, it is known as dynamic collector. Or you can also configure to start it manually. However, if you ask me personally, and as per Oracle recommendation as well, you should always be using dynamic collector. So far I have implemented GoldenGate in 65 projects, I have never used static collector. It has always been the dynamic collector in which the target manager automatically starts the collector process. That is, when you start the data pump process, automatically the target manager invoke the collector process on the target server once the connectivity with the data pump on source is established. So collector is nothing but interface between data pump and remote frame. It is assisting the data pump process to write the data to remote frame. So as soon as the connectivity is established, the target manager invoke the collector process in the background and whatever data is sent over the TCP IP network by the data pump process. Collector process collects that data and writes it to. Trail files. Trail files are binary format files. The format of these files is similar to archive log files. The trail files are the series of files that GoldenGate temporarily stores on disks and these files are returned to by the extract process and read by the replicate process. So there are two types of trail files. One is local trail and second is remote trail. So local trail is read by primary, local trail is written by primary extract process and remote trail is read by the replicate process. Now these trail files are platform independent. These trail files are the reason why GoldenGate can work on Linux, Sun Solaris, Windows, AIX, Exadata or whatever OS it supports. The reason being the trail files are not platform dependent. These trail files are completely platform independent. The format of trail files if you're generating on Windows is same. If you're generating on Linux is always same. If you're generating on Exadata, the format of trail file will never differ. So depending on the configuration chosen, these trail files can exist on the source as well as on target system. So if it exists on the local system, it will be known as local trail and if it exists on target system, it is known as. Now these trail files will continue to increase in size just like archive log files when we talk about. So archive log file until you housekeep them, they will continue to increase. Similarly, your trail files will also continue to increase. So you need to housekeep these trail files. Who performs the housekeeping part of these trail files? It's the manager process. So there is a parameter which we'll discuss how to housekeep the trail files in your environment. Next is data format. So datapump is file transfer method. It is sending the data from local trail and writing it to remote trail. But remember datapump are nothing but secondary extract mechanism. Capture is primary extract mechanism and datapump is secondary extract mechanism. Datapump is an optional component. That is you can always choose to remove datapump from your GoldenGate configuration. When datapump is configured and primary extract process will write it to local trail and then this local trail is read by datapump and data is sent over the network to remote trail on target system. So as we have discussed, if datapump is configured in your environment, so what will happen? The primary extract process will write that data, whatever it has captured to the local trail and then datapump will write it to remote trail and then replicate will apply to target datapump. However, what happens in absence of datapump? The data that the extract process extracts resides in the memory alone and there is no storage of this data anywhere on the source system. In case of network of target failure, there could be cases that their primary extract process can abort or event. So when we talk about this scenario, when datapump is not configured. So consider a scenario when datapump is configured. So in GoldenGate, the main advantage is each process is independent of each other. If your extract process is down, your datapump and replicate can continue to run. If your datapump is down, your extract and replicate can continue. If your extract is down, your extract and datapump can continue to run. So why does datapump goes down? What does datapump do is, it tries to establish the connectivity with the target manager on the specified port. If the connectivity is established, datapump will run. However, if the connectivity is not established, datapump will event. So say for instance, the network between source and target is not working. There is some issue between the network on both source as well as target. So what will happen? Datapump will not be able to establish connectivity with the target manager. Since datapump will not be able to establish the connectivity, what will happen? Datapump will not run. Datapump will show that it is abandoned. Now if datapump is abandoned, then local trail cannot be read by this datapump and send over the TCP and IP. However, remember since extract process is just responsible for extracting the data and writing it to local trail. As a result, even if the network is down, even if your network is down, what will happen is your extract process will continue to run if datapump is present. If datapump is present, extract will continue to run and continue to write data to local trail. So consider a scenario wherein your datapump or network is down from 7 a.m. to 5 p.m., that is for 10 hours. So what will happen? Your datapump will not run during that time. However, your application is not down, remember. Application is continuously making changes to database. It's only network problem. The application will continue to make the change. Since application will continue to make the change, so extract process is already capturing it and writing it to local trail. Now say for instance, during the timing of 7 to 5 p.m., around 50 GB of trail files have been generated. So what will happen is when at 5 p.m. you will start the datapump process, what will happen is automatically it will start from the point where it is stopped. So datapump will start sending the data by reading the local trail where it was stopped. So in this scenario, what will happen is only datapump, only the datapump process is not able to send the data. However, since extract and datapumps are individual, as a result, extract was able to perform the extraction part and write it to local trail. Now consider a same scenario when datapump is, when you have chosen not to use datapump in your application. So what will happen in that scenario? So now extract will solve dual task. First is, it will do the capture and also it will send that data over the TCP IP network, where with the help of collector process, it gets returned to the remote trail. So now what will happen when datapump is not there? As a result, datapump is not required and along with it, local trail is also an optional component. So now whatever extract process captures, it keeps that in its memory alone, send it over TCP IP network and writes it to remote trail with the help of collector process. Now, since extract process is doing the double task, that is, it is capturing the data as well as it is sending the data over TCP IP network. So say for instance, from seven to five, your network is down. So again, when you don't have datapump configured, your primary extract process tries to establish the connectivity with the target manager on the specified port. If network is down, it will not be reachable as a result, extract process will stop. It will also halt the extraction of data from source database. So this is the advantage of having individual process as datapump. If you have datapump in your environment, it is always recommended to be used by Boracal Golden. It is always recommended to be used by Boracal. So in the absence of datapump, what will happen? The data that the extract process extracts, it resides in the memory alone and there is no storage of this data anywhere on the source system. As a result, if network of target failure is, if there is any failure while connecting to the target, there could be cases when primary extract process can abort a bit. Hence, it will halt the extraction part of it. Datapump can also be useful in those cases where we are doing complex filtering and transformation, as well as we are consolidating data from many sources to central target. So there are various advantages. One of the major advantage of GoldenGate is you can do data filtration and transformation. And also remember, data filtration and transformation could be done at any level you want, be it extract, datapump or data. So Boracal always recommends datapump to be used in its environment. So if you look at the architecture once again of initial load as well as online chain synchronization, you will find that datapump is an optional component in your environment. So what do you do? Your extract process does the capture part, writes to local trail. Datapump reads the datapump local trail, writes it to remote trail with the help of collector. Collector process, as the name suggests, it collects the data which is being sent over a TCP IP network by the datapump process of source. And then it writes it to the remote trail from where it gets returned to the target database with help of replica processes. So there are six GoldenGate processes in your environment, six GoldenGate components, which makes your GoldenGate configuration, manager, extract, replica, datapump, trail files and collector process. So GoldenGate also has the capability to perform the initial load. With GoldenGate, you can always perform the initial load. So we'll be discussing about zero downtime strategy, as well as how to perform initial load using Oracle GoldenGate in our next session. And how GoldenGate initial load is different from database utilities like export import or Carmen duplicate or transportable table space. So this all we'll be discussing in our next upcoming session.