SMON Transaction Recovery hanging up your database?

When a large transaction got killed, terminated we all know that smon will do the rollback of the transaction, “smon: enable tx recovery”  which can evident in alert log.

Some time due to fast_start_parallel_rollback =high , lot of parallel sessions invoked and may complete hung your database or not permit any other session to work.

I came across similarly with same due to a large import got failed with undo 🙁 (more than 40gb import dump of 50gb table), this is in middle of the business day and hampering the system load peaking to 14,15, 18 of averages respectively.

Error in parallel slave trace:-

Parallel Transaction recovery coordinatorcaught exception 10388

So is there a way to disable the smon transaction recovery for sometime and continue later ?

Yes, accordingly to metalink note 238507.1

If your database is up and running and smon kicked in transaction recovery as like mine ,

SQL> select pid, program from v$process where program like '%SMON%';

PID PROGRAM
---------- ------------------------------------------------
9 oracle@test (SMON)

-- Disable SMON transaction cleanup:

SQL> oradebug setorapid <SMON's Oracle PID>

SQL> oradebug event 10513 trace name context forever, level 2

-- To enable the transaction recovery again:

SQL> oradebug setorapid <SMON's Oracle PID>

SQL> oradebug event 10513 trace name context off

If your database is not running and while opening the transaction recovery is happening, you can set the event in pfile and start your database.

event="10513 trace name context forever, level 2"

PS:- Ensure you have started the smon transaction recovery later, to protect your data integrity or the business logic.

SQL> oradebug setorapid <SMON's Oracle PID>ITPUB个人空间5o.S"r8i~&M1z
SQL> oradebug event 10513 trace name context off

BTW, the following query helps you to monitor the transaction recovery progress

set linesize 100

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"from v$fast_start_transactions;

Hope this helps

-Geek DBA