Wednesday, November 2, 2011

Parallel transaction recovery and SMON Monitoring

A very nice article to read when SMON is the actual blocker on other Database Sessions.

http://www.dba-oracle.com/t_parallel_transaction_recovery_undo_cpu.htm

Now in case the transaction rollback involves index block or when you querying the V$FAST_START_SERVERS you find only one server to be working then you may force disable the parallel rollback.

Follow these steps (If in RAC you must apply to all instances) :
1) Find SMON PID
select pid, program from v$process where program like '%SMON%';
2) on SQLPLUS as sysdba disable SMON transaction rollback/recovery

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

3) Kill the PQ slaves that are doing parallel transaction recovery.

select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;', 'kill -9 '||spid
from v$session s, (select * from v$process
 where pid in (select pid from v$fast_start_servers)) p
 where s.paddr=p.addr;

4)  Disable parallel rollback
alter system set fast_start_parallel_rollback=false;
5) Enable SMON to do serial recovery again

oradebug setorapid <SMON's Oracle PID>
oradebug event 10513 trace name context off
oradebug wakeup 13
6) Use the follwoing query to verify serial recovery and estimate when it will finish

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;

REFERENCES :

Metalink Note Ids: 414242.1, 238507.1




No comments:

Post a Comment