How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active (Doc ID 238507.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.5.0 to 9.2.0.3 [Release 8.1.5 to 9.2]
Oracle Database - Personal Edition - Version 8.1.5.0 to 9.2.0.3 [Release 8.1.5 to 9.2]
Information in this document applies to any platform.

SYMPTOMS

Parallel Transaction Recovery is taking too long.

You can use V$TRANSACTION USED_UBLK to estimate how long the rollback is going 
to take but there is no formula for this.  If you shutdown the database after 
rollback has started, it will begin where it left off.  

You can also look at V$FAST_START_TRANSACTIONS for UNDOBLOCKSDONE 
versus UNDOBLOCKSTOTAL.

CHANGES

 A large transaction got killed or rolled back.

CAUSE

There are cases where parallel transaction recovery is not as fast as serial 
transaction recovery, because the pq slaves are interfering with each other.  
This depends mainly on the type of changes that need to be made during rollback 
and usually may happen when rolling back INDEX Updates in parallel.  

SOLUTION

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

  

Related

   

 

Products

   

• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Enterprise Edition > RDBMS
• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Personal Edition > RDBMS

 

Keywords

   

V$FAST_START_SERVERS;V$FAST_START_TRANSACTIONS;V$PROCESS;V$TRANSACTION