Automatic Transaction Quarantine

Automatic Transaction Quarantine is a mechanism introduced in Oracle Database 26ai to enhance database availability by isolating transactions that fail during recovery. When a transaction cannot be recovered due to corruption or internal errors, the database quarantine the transaction instead of allowing the failure to propagate. Such transactions are left unrecovered and must be manually addressed by the database administrator (DBA) to release the row locks they hold.

For example:
A transaction updating a customer record encounters an ORA-00600 error during rollback. Oracle quarantines the transaction. The affected rows remain locked, preventing other sessions from modifying them. The DBA must intervene to resolve the underlying issue and drop the quarantine.

Failure During Transaction Recovery

Transaction recovery may fail for several reasons, including:

• Physical data corruption (e.g., ORA-01578, ORA-28304)
• Logical data corruption (e.g., ORA-00600)
• Memory corruption (e.g., ORA-00602, ORA-07445)
• State corruption (e.g., ORA-00600)

In previous releases, such failures could render the entire container database (CDB) unavailable. With the quarantine feature, only the affected transaction is isolated, and the database remains operational.

For example:
A transaction references a data block that has become physically corrupted. During recovery, Oracle encounters ORA-01578 and cannot complete rollback. Rather than crashing the instance, Oracle quarantines the transaction, allowing other transactions to recover normally.

Monitoring Quarantined Transactions

Database administrators can monitor quarantined transactions through the following data dictionary views:

• DBA_QUARANTINED_TRANSACTIONS: Displays quarantined transactions within a pluggable database (PDB).
• CDB_QUARANTINED_TRANSACTIONS: Displays quarantined transactions across the entire CDB.

These views provide critical information, including:

• Undo segment number (USN)
• Slot number (SLT)
• Sequence number (SQN)
• Start SCN of the transaction
• Reason for quarantine
• Associated trace file name

Additionally, alerts are generated in:

• The persistent alert queue (SYS.ALERT_QUE), reflected in DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY
• The attention log (introduced in Oracle 21c)
• The alert log, with corresponding incident details

Resolving Quarantined Transactions

To resolve a quarantined transaction, the DBA must first identify the root cause using the REASON column in the quarantine views. Oracle Support provides detailed remediation instructions in MOS Note 3005962.1.

After resolve a quarantined transaction, query USN,SLT and SQN information of transaction by querying DBA_QUARANTINED_TRANSACTIONS , then drop quarantined transaction :

Step 1 :

SQL> SELECT USN,SLT,SQN,REASON FROM DBA_QUARANTINED_TRANSACTIONS;

USN  SLT  SQN  REASON

8    20   275   ORA-00600 [ktubko_1]

The DBA consults the referenced MOS note to determine the corrective action, which may involve applying a patch, correcting data corruption, or performing other recovery steps.

Step 2 :

Once the underlying issue has been resolved, the quarantine must be explicitly dropped to allow transaction recovery to retry. The following DDL syntax is used:

ALTER DATABASE DROP TRANSACTION QUARANTINE <usn> <slt> <sqn>;

To drop the quarantine for transaction with XID 8.20.275:

ALTER DATABASE DROP TRANSACTION QUARANTINE 8 20 275;

After execution, Oracle will attempt to recover the previously quarantined transaction during the next recovery cycle.

Transaction Quarantine Escalation

To prevent systemic inconsistency, Oracle imposes a default quarantine limit of three per PDB. When this threshold is reached, the PDB is automatically shut down (using SHUTDOWN ABORT) on all RAC instances, provided archivelog mode is enabled. This escalation mechanism is triggered when failures are not confined to isolated transactions but instead indicate broader issues such as:

• Multiple block corruption
• PDB-level SGA corruption
• Widespread logical corruption

When escalation occurs:

1. The PDB is terminated.
2. Transaction recovery is automatically disabled for that PDB.
3. Alerts are published to all configured alert channels.

To restore normal operation:

1. Open the PDB.
2. Query DBA_QUARANTINED_TRANSACTIONS to identify all quarantined transactions.
3. Resolve and drop each quarantine.
4. Re-enable transaction recovery using:

ALTER SYSTEM SET TRANSACTION_RECOVERY = ENABLED SID = '*';

The SCOPE clause defaults to BOTH for PDBs and for CDB$ROOT when a server parameter file (SPFILE) is used, ensuring persistence across restarts.

Impact on Data Guard and Replication

The behavior of transaction quarantine metadata in Data Guard environments depends on the replication method:

• Logical Standby: Quarantine metadata is not replicated. The contents of quarantine views on the standby may differ from those on the primary.
• Physical Standby (Active Data Guard): Physical replication ensures that both the quarantined transaction and its catalog metadata are replicated to the standby database, maintaining consistency.