RMAN Backup Performance Tuning

 RMAN Backup Performance Tuning


Common Reason for RMAN Backup Slowness:

·       Single backup channel

·       Large datafiles

·       Slow storage

·       No incremental backup strategy

·       Compression overhead

·       High I/O usage

·       Archive log accumulation

·       Poor backup destination

 

Step 1: Check Current RMAN Configuration

RMAN> show all;

This gives you information about:

  • Parallelism
  • Backup type
  • Compression
  • Retention policy
  • Backup optimization

 

Step 2: Check RMAN Backup History

SET LINESIZE 200
SET PAGESIZE 100
COLUMN START_TIME           FORMAT A20
COLUMN END_TIME             FORMAT A20
COLUMN STATUS               FORMAT A10
COLUMN INPUT_SIZE_GB        FORMAT 99990.99
COLUMN OUTPUT_SIZE_GB       FORMAT 99990.99
COLUMN COMPRESSION_PERCENT  FORMAT 990.99
SQL> SELECT   TO_CHAR(START_TIME, 'DD-MON-YY HH24:MI') AS START_TIME,    TO_CHAR(END_TIME,   'DD-MON-YY HH24:MI') AS END_TIME,    STATUS,    INPUT_BYTES / 1024 / 1024 / 1024  AS INPUT_SIZE_GB,     OUTPUT_BYTES / 1024 / 1024 / 1024 AS OUTPUT_SIZE_GB,    CASE        WHEN INPUT_BYTES > 0 THEN            ROUND((1 - OUTPUT_BYTES / INPUT_BYTES) * 100, 2)        ELSE            NULL    END AS COMPRESSION_PERCENT FROM    V$RMAN_BACKUP_JOB_DETAILS ORDER BY     START_TIME DESC;

 

This helps identify:

  • Backup duration
  • Backup size
  • Compression ratio
  • Backup failures

 

Step 3: Enable Parallel Channels

Increase parallelism:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

 

Or allocate channels manually:

RMAN> RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch4 DEVICE TYPE DISK;
BACKUP DATABASE;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
}


Step 4: Use SECTION SIZE for Large Datafiles

BACKUP DATABASE SECTION SIZE 50G; 

This divides large files into smaller pieces and allows parallel processing.

This is useful for:

  • Large databases
  • Data warehouse systems
  • High I/O systems


Step 5: Enable Compression (If Required)

CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
BACKUP AS COMPRESSED BACKUPSET DATABASE;

 

Step 6: Use Incremental Backups

Level 0 Backup:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

Level 1 Backup:

BACKUP INCREMENTAL LEVEL 1 DATABASE;

 

Step 7: Check RMAN Backup Pieces

SET LINESIZE 200
SET PAGESIZE 100
COLUMN HANDLE            FORMAT A60
COLUMN BACKUP_TYPE       FORMAT A10
COLUMN STATUS            FORMAT A12
COLUMN START_TIME        FORMAT A20
COLUMN COMPLETION_TIME   FORMAT A20

SQL> SELECT    BP.HANDLE,    BS.BACKUP_TYPE,    BP.STATUS,    TO_CHAR(BP.START_TIME, 'DD-MON-YY HH24:MI')      AS START_TIME,    TO_CHAR(BP.COMPLETION_TIME, 'DD-MON-YY HH24:MI') AS COMPLETION_TIME FROM    V$BACKUP_PIECE BP JOIN     V$BACKUP_SET BS ON     BP.SET_STAMP = BS.SET_STAMP AND BP.SET_COUNT = BS.SET_COUNT ORDER BY     BP.START_TIME DESC;

 

Step 8: Monitor Backup Progress

SQL> SELECT SID, SERIAL#, OPNAME,  SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND TOTALWORK != 0;

 

Step 9: Enable Backup Optimization

CONFIGURE BACKUP OPTIMIZATION ON;

 

Step 10: Check Archive Log Backup

SQL> SELECT  SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# DESC;

Comments

Post a Comment

Popular posts from this blog

 Exadata  Oracle Exadata Smart Flash Cache * Exadata cell -> Flash LUN -> Cell Disk -> Flash Cache/Flash Log  * CELL_FLASH_CACHE - – DEFAULT (LRU), KEEP, NONE * ALTER TABLE <TABLE_NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) * ALTER INDEX <INDEX NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) Smart Flash Cache Mode: 1. Write Through Flash Cache Mode (Default):  Write on Disk -> Copy to Flash Cache  * excellent for absorbing repeated random reads  * commonly found in OLTP applications. * dcli -g cell_servers -l celladmin cellcli -e "list cell attributes flashcachemode" * Steps to enable Write-Through Mode CellCLI> ALTER FLASHCACHE ALL FLUSH CellCLI> DROP FLASHCACHE CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV CellCLI> ALTER CELL flashCacheMode=WriteThrough CellCLI> ALTER CELL STARTUP SERVICES CELLSRV CellCLI> CREATE FLASHCACHE ALL 2. Write Back Flash Cache Mode:  Write on Flash -> Copy to Disk  * best ...
Read more

Database Network : Configuration Parameters

LSNRCTL> command [listener_name] lsnrctl @file_name  #to execute in batch mode. REM or # to identify comments in the batch script Listener.ora file parameters: ·          PASSWORDS_listener_name  #an unencrypted password ·          LSNRCTL> change_password #to establish an encrypted password ·          LSNRCTL> SAVE_CONFIG  #Any changes are stored in listener.ora ·          LSNRCTL> SET PASSWORD  #If a password is set, then issue SET PASSWORD command before changing the password. Use the command SET PASSWORD prior to privileged Listener Control utility commands ·          Reload : the database services, instances, service handlers, and listening endpoints that were dynamically registered with the listener will be unregistered and subseque...
Read more

Undo Segments : Drop Problematic Undo Segments

ERROR about to recover undo segment 98 status:6 inst:0 mark undo segment 98 as available status:6 ret:1  about to recover undo segment 46 status:6 inst:0 mark undo segment 46 as available status:6 ret:1   SQL> select * from V$FAST_START_TRANSACTIONS; USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID ---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- 46 46 2313064 RECOVERING 505 24992423 77 5586 0 0 0 10001000684B2300 0000000000000000 1 0 98 25 1352150 RECOVERING 0 226231 78 5586 0 0 0 30001900D6A11400 0000000000000000 1 0   SQL> SELECT segment_name, tablespace_name  FROM dba_rollback_segs  WHERE segment_id IN (98, 46); SEGMENT_NAME               TABLESPACE_NAME ------------------------------ ----------------------------...
Read more