How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active (Doc ID 238507.1)_oracle parallel transaction recovery smon-CSDN博客

 
  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.
 CAUSEThere 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.  
 SOLUTIONDynamically 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