How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active

Dynamically switch from parallel recovery to serial. If you are in a clustered
environment you will need to do this on all instances at the same time:

1. Find SMON’s Oracle PID:

Example:

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

       PID PROGRAM
---------- ------------------------------------------------
         6 oracle@stsun7 (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 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

where n is the SPID’s returned from the previous statement .

4. Turn off fast_start_parallel_rollback:

alter system set fast_start_parallel_rollback=false;

5. 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