Automatic Transaction Quarantine

In this episode, Lois Houston and Nikita Abraham explore the Automatic Transaction Quarantine feature with Senior Principal Database & MySQL Instructor, Bill Millar. Bill explains that this feature isolates transactions that could potentially cause system crashes, preventing them from impacting the entire container database. They also discuss the key advantages of automatic transaction quarantine in maintaining database stability and availability.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/140830/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, 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:26 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. 00:51 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? 01:27 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. 02:27 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? 04:12 Lois: Bill, what’s the workflow a DBA would follow when a transaction is quarantined? Bill: So in the system, when that transaction recovery failure is, OK, I've found this dead transaction. I'm going to quarantine. I'm going to say, hey, you have something you need to take care of for that. So it's not recovered by the SMON. So what's going to happen? So there is also is going to be a limit. So if it does reach that limit and the limit is three, then you're going to have to step in and try to take care of that very quickly. The shut down abort will be performed on the PDB. So the good news there is that it's going to keep it from impacting the entire container. If the limit isn't reached, well, then, OK, hey, we have this bad transaction that's going to quarantine, is going to populate. There's a couple of views that you can go out and look at. There's a CDB quarantine transactions or a DBA quarantine transactions. Those views you can look at. And then once we determine that, what are we going to do to try to recover it? If we're going to try to recover it, then we can go ahead and drop that bad transaction. It'll help free up the rows. That way, everything can start working again. That PDB can be opened. 05:30 Nikita: What can you tell us about monitoring quarantined transactions? What specific views or logs should DBAs monitor? Bill: So you can view. You'll see these quarantine transactions in several different places. One is the alert queue. It's going to be sent to the alert queue. That is what is going to notify Enterprise Manager Cloud Control, also populates it within the AWR. Back in 21c, we added the attention log. It shows critical events. Hey, you need to take a look at this. It also can populate it. It will populate it to the alert log. So remember you have the V$DIAG_ALERT that you can look at. Or, if you're familiar with or you use the ADRCI, automatic diagnostic repair recovery advisor, so you can also look at the alert log there. So there are two new views, the CDB_QUARANTINED_TRANSACTION, the DBA_QUARANTINE_TRANSACTIONS working with multi-tenant. The CDB, I can see all the quarantine transactions from the root container, the DBA_QUARANTINE_TRANSACTIONS what I see if I'm in a specific PDB. But it's going to give me the information. 06:52 Lois: What about resolving quarantined transactions? Bill: Monitoring is a must to be able to identify, hey, we have bad transactions that we need to-- quarantine transactions we need to take care of. You can apply the appropriate MOS note if you're not sure what to do. Like anything else, if something happens-- and hopefully, you're not getting quarantined transactions daily or anything like that. But once we start doing a few things, we remember how to do them. 07:21 Lois: And, how do we take care of this? Bill: Well, you always have the ability to go to My Oracle Support. There is a view called-- that CDB quarantine transaction that we talked about that we can look at, hey, here's the reason. And we might use that to go out there and search My Oracle Support and/or contact Oracle Support. 07:49 Do you have an idea for a new course or learning opportunity? We’d love to hear it! Visit the Oracle University Learning Community and share your thoughts with us on the Idea Incubator. Your suggestion could find a place in future development projects! Visit mylearn.oracle.com to get started.  08:09 Nikita: Welcome back! Bill, what are some of the common causes of quarantined transactions? Could you share some examples with us? And how do you resolve them? Bill: One could be physical corruptions. It could either be logical or physical. So maybe because media failed. Hardware bits get flipped. So that might be able to be easily fixed by using the RMAN Block Media Recovery. And that's the lowest level of recovery that we can apply. And then there's logical corruptions. This is the recommended order when trying to resolve logical corruptions. First level is the Block Media Recovery. And then, after that, if the Block Media Recovery fails, then possibly, how about re-creating that data segment? So either truncate or drop it, and then recover it from another source. So once you drop the segment, the transaction then is going to skip trying to recover it. It's no longer there. So it's, OK, hey, I'm successful now. And then, the last resort type method is to drop that undo segment. There's an offline rollback segment that you can use. But it's recommended-- it's best to avoid that-- again, kind of a last-ditch effort to try to fix something. There are other options that you might try. However, these options do end up being a loss of data. Why? Because we're going to do a point-in-time recovery. So we can go back to a table point-in-time recovery. So we start with the Block Media Recovery. OK, we can't. OK, so how about if we go back before that transaction and try to recover the table at that time? So it will be a loss of data. Then, the next level is, we can't do the table. Can we do the entire tablespace? That might be an option. Might flashback the database if we are using-- if we have Flashback Database on. Again, that's just another method of point-in-time recovery. And then also do a database point-in-time recovery. If we can do the database point-in-time recovery flashback at the PDB level, so that way it's not impacting the entire container, hopefully, we don't have to try to do a point-in-time recovery at the database level. So we wouldn't want to do that. That would something really drastic would have to happen to force us to do the entire container. But we want to do that at the PDB level. 10:54 Lois: Ok. So the issue is resolved. What happens next? Bill: So once we have the issue resolved that caused that, SMON is still going to try to do transaction recovery because why? That quarantined transaction says, hey, I've still got this bad transaction there. So once that transaction has been fixed, we need to drop that quarantined transaction. So that way, SMON says, hey, I have this transaction. I need to recover. SMON will keep from trying to do that. So there is a DDL command to drop that quarantined transaction. So remember, from the views, the quarantined transaction views, that's where we saw the undo segment. We saw the slot number. We saw the quarantined transaction slot number. So that way, we can drop that transaction by using that. 11:51 Nikita: How does the escalation process work for quarantined transactions? And why is it important to protect the PDB and the container database? Bill: So quarantined transaction escalation-- we might have multiple transactions fail, depending on the corruption level. It might have multiple blocks for that that have failed. So just to quarantine a bad transaction may not help whatsoever. It depends on what the root cause is for the failures and how many are happening at that time. So the database with these bad transactions will continuously run in an inconsistent state. So it could be dangerous if we have multiples of the same issue and that. So with that system running in an inconsistent state, things will continue to spread. Things will continue to get worse. That's why, once that level of 3 is reached, we go ahead, and we do a shut down abort on that PDB. Because if a transaction can't be recovered, there's no need in trying to do any other type of shutdown. So with this escalation process, it does benefit us because, again, SMON is going to continuously try to recover that bad transaction for that. OK, SMON's going to keep trying. It's not going to work. And at some point, it might cause it to crash. So by stopping it before it continues getting worse, damaging more, we're going to go ahead and say we're escalating this issue to where we're shutting down the PDB. Fault tolerance, so meaning that we have higher availability of the rest of the container. So it's not going to crash the entire container. So the PDB can continue to operate when we are trying to resolve transactions except in the case where it exceeds the amount, and it does a shutdown abort on that PDB. So with that escalation, we reach that limit of 3 for that. We do a Shutdown Abort on that PDB. That transaction recovery is disabled. OK. Don't try to recover any transactions. Why? Because we know we have a few of them. So it's shut down, so we're going to go out and look at our quarantine transactions views, what's the reason for that, how many do we have? And then, once we resolve the issue, we are going to enable recovery again because it turns off the recovery option before it allows us to open that PDB. It's not going to be in a consistent state, though. So now we can go ahead and alter the system and, OK, go ahead and allow recovery of transactions again. 14:42 Lois: Thank you, Bill, for walking us through the details of automatic transaction quarantine and telling us how to manage and resolve these complex scenarios. Nikita: Yeah, thanks Bill! To learn more about what we discussed today, visit mylearn.oracle.com and search for the Oracle Database 23ai New Features for Administrators course. Join us next week for a discussion on some more Oracle Database 23ai new features. Until then, this is Nikita Abraham… Lois: And Lois Houston signing off! 15:13 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.