Automatic Transaction Quarantine
Description
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!
Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Team Lead: Editorial Services with Oracle University, and with me is Lois Houston, Director of Innovation Programs.
Lois: Hi there! In our last episode, we looked at an Oracle Database 23ai new feature called Automatic Transaction Rollback, and we spoke about why it is such an important feature for database administrators.
Nikita: Today, we’re going to talk about another new feature called Automatic Transaction Quarantine. We’ll discuss what it is, go through the steps to monitor and identify quarantine transactions, explore how an issue is resolved once a quarantined transaction has been identified, and end by looking at quarantined transaction escalation, and how it helps to protect not only your PDB, but also your container database.
Lois: Back with us is Bill Millar, our Senior Principal Database & MySQL Instructor with Oracle University. Hi Bill! What is automatic transaction quarantine and why do we need it?
Bill: The good news is that starting in 23c with the database quarantines, it's going to isolate a transaction or transactions that could possibly cause a system crash, so you can avoid crashes. It's going to isolate those transactions that potentially could cause a problem. However, those transactions must be manually resolved by the DBA so that the row locks are released from those bad transactions.
A transaction recovery basically is going to isolate failure and also identify what is the cause of that corruption. So when a system restarts, transaction can fail to recover while the other transactions can be recovered. So with the transaction recovery, basically, we know when the system recovers, the SMON is going to use the redo and the undo.
Nikita: Can you explain that in a little more detail? How does transaction recovery work and why is it so critical for database stability?
Bill: It does the redo to roll forward the database. However, at that point, it'll go ahead and open the database, allow it to start being used while it is applying the undo. And when it cannot apply that undo, that's when the system is going to mark that transaction as bad for that.
That is what is transaction recovery. Whereas instance recovery is basically the same thing, except now you're in a RAC environment. And it's unable to be recovered on one of the instances within your RAC environment.
Because it can be, it'll have those rows locked, and it can affect the other instances. So SMON might be unable to perform that recovery, so it could cause that PDB or the CDB to crash. OK, now, nobody can access any information.
So once if that entire container crashes, recovery is going to stop. If it has a bad transaction, recovery stops. So it might be because of physical data, might be because of the index is corrupt, might be logical corruption.
So it stops that interactive transaction recovery process. So not only does it stop the recovery of the transaction that is trying to be recovered by SMON, it's going to stop the rest of the inactive transactions. Those row locks are held.
And it can impact critical operations. Yeah, if my system can't do anything, yes, it's going to have an impact. The DBAs must resolve what is that bad transaction, how to get rid of it, how we're going to get around it?
<span style= "mso-bid
United States
