Oracle GoldenGate 23ai Security Strategies

Oracle University Podcast

GoldenGate 23ai takes security seriously, and this episode unpacks everything you need to know. GoldenGate expert Nick Wagner breaks down how authentication, access roles, and encryption protect your data. Learn how GoldenGate integrates with identity providers, secures communication, and keeps passwords out of storage. Understand how trail files work, why they only store committed data, and how recovery processes prevent data loss. Whether you manage replication or just want to tighten security, this episode gives you the details to lock things down without slowing operations. Oracle GoldenGate 23ai: Fundamentals: https://mylearn.oracle.com/ou/course/oracle-goldengate-23ai-fundamentals/145884/237273 Oracle University Learning Community: https://education.oracle.com/ou-community LinkedIn: https://www.linkedin.com/showcase/oracle-university/ X: https://x.com/Oracle_Edu Special thanks to Arijit Ghosh, David Wright, Kris-Ann Nansen, Radhika Banka, and the OU Studio Team for helping us create this episode. -------------------------------------------------------------- Episode Transcript:

00:00

Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started!

00:25

Lois: Hello and welcome to the Oracle University Podcast! I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Team Lead: Editorial Services.

Nikita: Welcome, everyone! This is our fourth episode on Oracle GoldenGate 23ai. Last week, we discussed the terminology, different processes and what they do, and the architecture of the product at a high level. Today, we have Nick Wagner back with us to talk about the security strategies of GoldenGate.

00:56

Lois: As you know by now, Nick is a Senior Director of Product Management for GoldenGate at Oracle. He’s played a key role as one of the product designers behind the latest version of GoldenGate. Hi Nick! Thank you for joining us again. Can you tell us how GoldenGate takes care of data security?

Nick: So GoldenGate authentication and authorization is done in a couple of different ways. First, we have user credentials for GoldenGate for not only the source and target databases, but also for GoldenGate itself. We have integration with third-party identity management products, and everything that GoldenGate does can be secured.

01:32

Nikita: And we must have some access roles, right?

Nick: There's four roles built into the GoldenGate product. You have your security role, administrator, operator, and user. They're all hierarchical. The most important one is the security user. This user is going to be the one that provides the administrative tasks.

This user is able to actually create additional users and assign roles within the product. So do not lose this password and this user is extremely important. You probably don't want to use this security user as your everyday user. That would be your administrator.

The administrator role is able to perform all administrative tasks within GoldenGate. So not only can they go in and create new extracts, create new replicats, create new distribution services, but they can also start and stop them. And that's where the operator role is and the user role.

So the operator role allows you to go in and start/stop processes, but you can't create any new ones, which is kind of important. So this user would be the one that could go in and suspend activity. They could restart activity. But they can't actually add objects to replication.

The user role is really a read-only role. They can come in. They can see what's going on. They can look at the log files. They can look at the alerts. They can look at all the watches and see exactly what GoldenGate is doing. But they're unable to make any changes to the product itself.

02:54

Lois: You mentioned the roles are hierarchical in nature. What does that mean?

Nick: So anything that the user role does can be done by the operator. Anything that the operator and user roles can do can be done by the administrator. And anything that the user, operator, and administrator roles do can be done by the security role.

03:11

Lois: Ok. So, is there a single sign-on available for GoldenGate?

Nick: We also have a password plugin for GoldenGate Connections. A lot of customers have asked for integration with whatever their single sign-on utility is, and so GoldenGate now has that with GoldenGate 23ai. So these are customer-created entities. So, we have some examples that you can use in our documentation on how to set up an identity provider or a third-party identity provider with GoldenGate. And this allows you to ensure that your corporate standards are met.

As we started looking into this, as we started designing it, every single customer wanted something different. And so instead of trying to meet the needs for every customer and every possible combination of security credentials, we want you to be able to design it the way you need it. The passwords are never stored. They're only retrieved from the identity provider by the plugin itself.

04:05

Nikita: That’s a pretty important security aspect…that when it’s time to authenticate a user, we go to the identity provider.

Nick: We're going to connect in and see if that password is matching. And only then do we use it. And as soon as we detect that it's matched, that password is removed. And then for the extract and replicats themselves, you can also use it for the database, data source, and data target connections, as well as for the GoldenGate users. So, it is a full-featured plugin.

So, our identity provider plugin works with IAM as well as OAM. These are your standard identity manager authentication methods. The standard one is OAuth 2, as well as OIDC. And any Identity Manager that uses that is able to integrate with GoldenGate.

04:52

Lois: And how does this work?

Nick: The way that it works is pretty straightforward. Once the user logs into the database, we're going to hand off authentication to the identity provider. Once the identity provider has validated that user's identity and their credentials, then it comes back to GoldenGate and says that user is able to log in to either GoldenGate or the application or the database. Once the user is logged in, we get that confirmation that's been sent out and they can continue working through GoldenGate.

So, it's very straightforward on how it works. There's also a nice little UI that will help set up each additional user within those systems. All the communication is also secured as well. So any communication done through any of the GoldenGate services is encrypted using HTTPS. All the REST calls themselves are all done using HTTPS as well.

All the data protection calls and all the communication across the network when we send data across a distribution service is encrypted using a secure WebSocket. And there's also trail file encryption at the operating system level for data at REST. So, this really gives you the full level of encryption for customers that need that high-end security. GoldenGate does have an option for FIPS 140-2 compliance as well. So that's even a further step for most of those customers.

06:12

Nikita: That’s impressive! Because we want to maintain the highest security standards, right? Especially when dealing with sensitive information. I now want to move on to trail files. In our last episode, we briefly spoke about how they serve as logs that record and track changes made to data. But what more can you tell us about them, Nick?

Nick: There's two different processes that write to the trail files. The extract process will write to the trail file and the receiver service will write to the trail file. The extract process is going to write to the trail file as it's pulling data out of that source database. Now, the extract process is controlled by a parameter file, that says, hey, here's the exact changes that I'm going to be pulling out. Here's the tables. Here's the rows that I want. As it's pulling that data out and writing it to the trail files, it's ensuring that those trail files have enough information so that the replicat process can actually construct a SQL statement and apply that change to that target platform.

And so there's a lot of ways to change what's actually stored in those trail files and how it's handled. The trail files can also be used for initial loads. So when we do the initial load through GoldenGate, we can grab and write out the data for those tables, and that excludes the change data. So initial loads is pulling the data directly from the tables themselves, whereas ongoing replication is pulling it from the transaction logs.

07:38

Lois: But do we need to worry about rollbacks?

Nick: Our trail files contain committed data only and all data is sequential. So this is two important things. Because it contains committed data only, we don't need to worry about rollbacks. We also don't need to worry about position within that trail file because we know all data is sequential. And so as we're reading through the trail file, we know that anything that's written in a prior location in that trial file was committed prior to something else. And as we get into the recovery aspects of GoldenGate, this will all make a lot more sense.

08:13

Lois: Before we do that, can you tell us about the naming of trail files?

Nick: The trail files as far as naming, because these do reside on the operating system, you start with a two-letter trail file abbreviation and then a nine-digit sequential value. So, you almost look at it as like an archive log from Oracle, where we have a prefix and then an affix, which is numeric. Same kind of thing. So, we have our two-letter, in this case, an ab, and then we have a nine-digit number.

08:47

Transform the way you work with Oracle Database 23ai! This cutting-edge technology brings the power of AI directly to your data, making it easier to build powerful applications and manage critical workloads. Want to learn more about Database 23ai? Visit mylearn.oracle.com to pick from our range of courses and enroll today!

09:12

Nikita: Welcome back! Ok, Nick. Let’s get into the GoldenGate recovery process.

Nick: When we start looking at the GoldenGate recovery process, it essentially makes GoldenGate kind of point-in-time like. So on that source database, you have your extract process that's going to be capturing data from the transaction logs. In the case of Oracle, the Oracle Database is actually going to be reading those transaction logs from us and passing the change records directly to GoldenGate. We call them an LCR, Logical Change Record.

And so the integrated extract and GoldenGate, the extract portion tells the database, hey, I'm now going to be interested in the following list of tables. And it gives a list of tables to that internal component, the log mining engine within the database. And it says, OK, I'm now pulling data for those tables and I'm going to send you those table changes.

And so as the extract process gets sent those changes, it's going to have checkpoint information. So not only does it know where it was pulling data from out of that source database, but what it's also writing to the trail file. The trail files themselves are all sequential and they have only committed data, as we talked about earlier.

The distribution service has checkpoint information that says, hey, I know where I'm reading from in the previous trail file, and I know what I've sent across the network. The receiver service is the same thing. It knows what it's receiving, as well as what it's written to the trail file and the target system.

The replicat also has a checkpoint. It knows where it's reading from in the trail file, and then it knows what it's been applying into that target database.

This is where things start to become a little complicated. Our replicat process in most cases are parallel, so it'll have multiple threads applying data into that target database. Each of those threads is applying different transactions.

And because of the way that the parallelism works in the replicat process, you can actually get situations where one replicat thread might be applying a transaction higher than another thread. And so you can eliminate that sequential or serial aspect of it, and we can get very high throughput speeds to the replicat. But it means that the checkpoint needs to be kind of smart enough to know how to rebuild itself if something fails.

11:32

Lois: Ok, sorry Nick, but can you go through that again? Maybe we can work backwards this time?

Nick: If the replicat process fails, when it comes back up, it's going to look to its checkpoint tables inside that target database. These checkpoint tables keep track of where each thread was at when it crashed. And so when the replicat process restarts, it goes, oh, I was applying these threads at this location in these SCNs.

It'll then go and read from the trail file and say, hey, let me rebuild that data and it only applies transactions that it hasn't applied yet to that target system. There is a synchronized replicat command as well that will tell a crashed replicat to say, hey, bring all your threads up to the same high watermark. It does that process automatically as it restarts and continues normal replication. But there is an option to do it just by itself too.

So that's how the replicat kind of repairs and recovers itself. It'll simply look at the trail files. Now, let's say that the replicat crashed, and it goes to read from the trail files when it restarts and that trail profile is missing. It'll actually communicate to the distribution, or excuse me, to the receiver service and say, hey, receiver service, I don't have this trail file. Can you bring it back for me?

And the receiver service will communicate downstream and say, hey, distribution service, I need you to resend me trail find number 6. And so the distribution service will resend that trail file so that the replicat can reprocess it. So it's often nice to have redundant environments with GoldenGate so we can have those trail files kind of around for availability.

13:13

Nikita: What if one of these files gets corrupted?

Nick: If one of those trail files is corrupt, let's say that a trail file on the target site became corrupt and the replicat can't read from it for one reason or another. Simply stop the replicat process, delete the corrupt trail file, restart the replicat process, and now it's going to rebuild that trail file from scratch based on the information from the source GoldenGate environment. And so it's very recoverable. Handles it all very well.

13:40

Nikita: And can the extract process bounce back in the same way?

Nick: The extract process can also recover in a similar way. So if the extract process crashes, when it restarts itself, there's a number of things that it does. The first thing is it has to rebuild any open transactions. So it keeps all sorts of checkpoint information about the oldest transaction that it's keeping track of, any open transactions that haven't been committed, and any other transactions that have been committed that it's already written to the trail file.

So as it's reprocessing that data, it knows exactly what it's committed to trail and what hasn't been committed. And there's a number of ways that it does this.

There's two main components here. One of them is called bounded recovery. Bounded recovery will allow you to set a time limit on transactions that span a certain length of time that they'll actually get flushed out to disk on that GoldenGate Hub. And that way it'll reduce the amount of time it takes GoldenGate to restart the extract process.

And the other component is cache manager. Cache manager stores uncommitted transactions. And so it's a very elegant way of rebuilding itself from any kind of failure. You can also set up restart profiles so that if any process does crash, the GoldenGate service manager can automatically restart that service an x number of times across y time span.

So if I say, hey, if my extract crashes, then attempt to restart it 100 times every 5 seconds. So there's a lot of things that you can do there to make it really nice and automatic repair itself and automatically resilient.

15:18

Lois: Well, that brings us to the end of this episode. Thank you, Nick, for going through the security strategies and recovery processes in such detail. Next week, we’ll look at the installation of GoldenGate.

Nikita: And if you want to learn more about the topics we discussed today, head over to mylearn.oracle.com and take a look at the Oracle GoldenGate 23ai Fundamentals course. Until next time, this is Nikita Abraham…

Lois: And Lois Houston signing off!

15:44

That’s all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We’d also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.