Waiting for smon to disable tx recovery SMON in alert log

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;

if not work use 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.

Find smon pid:
select pid, program from v$process where program like ‘%SMON%’;

Disable SMON transaction cleanup:
SQL> oradebug setorapid ‘SMON’s Oracle PID’;
SQL> oradebug event 10513 trace name context forever, level 2

Kill the PQ slaves that are doing parallel transaction recovery.
You can check V$FAST_START_SERVERS to find these.
e.g.
select SPID from V$PROCESS where PID in (select PID from V$FAST_START_SERVERS);
=then kill these processes from the OS level with : kill -9 n

Turn off fast_start_parallel_rollback:
alter system set fast_start_parallel_rollback=false;

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
fast_start_parallel_rollback=false

This parameter cannot be changed dynamically.

Advertisements

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 )

w

Connecting to %s

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