How to disable parallel transaction recovery when the parallel transaction recovery process is executing
How to Disable Parallel Transaction Recovery if Parallel Txn Recovery is Active (Doc ID 238507.1)
Suitable for:
Oracle database-enterprise edition-version 8.1.5.0 to 11.2.0.4 [Release 8.1.5 to 11.2]
Oracle database-personal edition-version 8.1.5.0 to 11.2.0.4 [Release 8.1.5 to 11.2]
Information in this document applies to any platform.
Symptoms:
Parallel Transaction Recovery took a very long time
You can use the USED_UBLK column of the v$transaction view to estimate how long the rollback takes, but there is no formula to calculate the time.
Let's say you've started rollback. then shutdown database. The rollback will start again where it stops.
You can take a look at the two columns in the V$fast_start_transactions View Control: Undoblocksdone and Undoblockstotal
Changes:
A large transaction was killed or rolled back.
Reason:
Parallel transaction recovery (parallel transaction recovery) is not as good as the example of serial rollback fast. The reason is that the PQ slaves process interferes with each other (interfer)
This depends on the type of rollback that is required, usually on the roll back Parallel index update.
How to resolve:
Online to change the parallel recovery to serial. If cluster environment, need to change at the same time on all instances
1. Find Smon's Oracle PID (note not OS pid)
Sql> Select PID, program from v$process where program like '%smon% ';
PID Program
---------- ------------------------------------------------
6 [email protected] (Smon)
2. Disable Smon transaction cleanup
sql> oradebug setorapid ' Smon ' s Oracle PID ';
Sql> oradebug Event 10513 Trace name Context forever, Level 2
3. Kill the PQ slave process that is running parallel transaction recovery from the OS level.
Able to find these PQ slave processes through v$fast_start_servers
Select SPID from V$process where PID in (select PID from v$fast_start_servers);
Then kill the query result from the above SELECT statement from the OS level: kill-9 spid
4. Close Fast_start_parallel_rollback
alter system set FAST_START_PARALLEL_ROLLBACK=FALSE;
5. Start transaction recovery again (transaction recovery)
sql> oradebug setorapid ' Smon ' s Oracle PID ';
Sql> oradebug Event 10513 Trace name context off
"Translated from MoS article" the method of disabling parallel transaction recovery when the parallel transaction recovery process is executing