ORACLE - RMAN Tuning

# QUERYS - VERIFY I/O
# ----------------------------------------------------------

# Here is an example of a query against V$BACKUP_ASYNC_IO # and its results after a database backup has been completed:# V$BACKUP_SYNC_IO # V$BACKUP_ASYNC_IO

select device_type "Device" , type , filename , to_char(open_time, 'mm/dd/yyyy hh24:mi:ss') open , to_char(close_time,'mm/dd/yyyy hh24:mi:ss') close , elapsed_time ET , effective_bytes_per_second EPS from v$backup_async_io where close_time > sysdate - 1 order by close_time desc;

# To determine whether there is an I/O problem, we can look at # the ratio of I/Os to long waits (LONG_WAITS/IO_COUNTS)

select io_count , ready , short_waits , long_waits , long_waits/io_count , filename from v$backup_async_io ;

# RMAN DEBUG
# ----------------------------------------------------------
rman target /@target catalog /@catalog trace=/tmp/rman.trcRMAN>
run { DEBUG ON;
DEBUG OFF; }

# LONGOPS
# ----------------------------------------------------------
clear breaksclear computesset pages 1200 lin 400 trimspoo oncol username for a10col osuser for a10col message for a40 wrapcol sid for 99999999999

SELECT sid , serial# , context , sofar , totalwork , round(sofar/totalwork*100,2) "% Complete" FROM v$session_longops WHERE opname LIKE 'RMAN%' AND opname NOT LIKE '%aggregate%' AND totalwork != 0 AND sofar <> totalwork;

# PROCESS
# ----------------------------------------------------------
SELECT sid , spid , client_info FROM v$process p , v$session s WHERE p.addr = s.paddr AND client_info LIKE '%id=rman%';

# WAITS
# ----------------------------------------------------------
SELECT sid , seconds_in_wait AS sec_wait , event FROM v$session_wait WHERE wait_time = 0 ORDER BY sid;

# TAPE PARAMETERS
# ----------------------------------------------------------
BACKUP_TAPE_IO_SLAVES = true

# LARGE POOL
# ----------------------------------------------------------
For disk channels
LARGE_POOL_SIZE = (number_of_allocated_channels * (16 + 4)) +16 Mb
For tape channels:
LARGE_POOL_SIZE = (number_of_allocated_channels * (16 + 4()) +16 Mb

# DBWR_IO_SLAVES
# ----------------------------------------------------------
# If You Use Synchronous Disk I/O, Set DBWR_IO_SLAVES# Note: By setting DBWR_IO_SLAVES, the database writer processes will use slaves as well.# You may need to increase the value of the PROCESSES initialization parameter.
DBWR_IO_SLAVES

# MAXOPENFILES
# ----------------------------------------------------------
# To reduce the amount of memory used by RMAN set - MAXOPENFILES = # EG - Before maxopenfiles # 4*100(files)*8192*64 # After maxopenfiles = 4# 4*4(files)*8192*64
MAXOPENFILES

# BACKUP_DISK_IO_SLAVES
# ----------------------------------------------------------
# where is the number of disk i/o slaves to start.# Oracle recommends that no more than 4 disk slaves are started. In this case,# extra channels should be considered.# For Oracle 8i/9i/10g, set the "init.ora" parameter# DBWR_IO_SLAVES > 0
BACKUP_DISK_IO_SLAVES =

# ORACLE REFERENCES (METALINK)
# ----------------------------------------------------------
Note:579158.1 Advise On How To Improve Rman Performance
Note:311068.1 RMAN Performance Tuning Diagnostics
Note:237083.1 Using V$BACKUP_ASYNC_IO / V$BACKUP_SYNC_IO to Monitor RMAN Performance
Note:247611.1 Known RMAN Performance Problems
Note:336313.1 How to Calculate Rman Memory Allocation In Large Pool