Waiting for smon to disable tx recovery SMON in alert log

Waiting for smon to disable tx recovery SMON

In Alert Log: Waiting for smon to disable tx recovery
killing a large running transaction, either by killing the process or aborting the database,
the database may appear to hang and/or the SMON background process is taking all the available CPU.

Step 1: Monitor the progress of transaction recovery:

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;

Note: If first SQL is not work. You can try the following:

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD';

Step 2: In order to speed up SMON recovery of the large transaction.
There are cases where parallel transaction recovery is not as fast as serial transaction recovery.
Check the Parallel Recovery processes and their state, run the following query:

select * from v$fast_start_servers;

Column STATE shows the state of the server being IDLE or RECOVERING, if only 1 process is in
state RECOVERING while the other processes are in state IDLE, then you should disable Parallel Transaction Recovery.

Step 3: Find smon pid:

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

Step 4: Disable SMON transaction cleanup:

SQL> oradebug setorapid 'SMON's Oracle PID';
SQL> oradebug event 10513 trace name context forever, level 2

Step 5: Kill the PQ slaves that are doing parallel transaction recovery. You can check V$FAST_START_SERVERS to find these.

select SPID from V$PROCESS where PID in (select PID from V$FAST_START_SERVERS);

Step 6: Then kill these processes from the OS level:

kill -9 n

Step 7: Turn off fast_start_parallel_rollback.

alter system set fast_start_parallel_rollback=false;

Step 8: Re-enable transaction recovery as it was disabled in step 2.

SQL> oradebug setorapid 'SMON's Oracle PID';
SQL> oradebug event 10513 trace name context off

Note: If all the processes are in state RECOVERING, then you can benefit from adding more processes:
SQL> alter system set fast_start_parallel_rollback = high;

If you reboot the system you can also set following as an alternative:
Increase the parameter ‘_cleanup_rollback_entries’ to 400. The default is 100.

_cleanup_rollback_entries = 400

This parameter cannot be changed dynamically.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.