Parallel transaction recovery and SMON Monitoring
I have read about parallel transaction monitoring and doing task as a given below:
A very nice article to read when SMON is the actual blocker
on other Database Sessions.
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
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
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
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
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
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
