Posted on

RMAN performance tuning best practices


Recently I got a call from customer related to backups. They say that backup is taking longer time and wanted us to investigate more details on what is going on.
Here are some steps we have taken to come up with a solution
1) Get details from v$rman_backup_job_Details view to get details on start_time, end_time, backup size
spool rman_backup_job_Details.html
set markup html on
select * from v$rman_backup_job_Details;
set markup html off
spool off
2) Once we suspect an issue we can generate trace details on the rman command. This is a oracle 12c environment and we can execute sql statements directly from RMAN prompt. IF you are in oracle 11g or earlier version, still run the commands that are related to sql prompt like select with sql clause as prefix
rman target /
RMAN> alter session set events ‘10046 trace name context forever, level 12’;
RMAN> select ‘RMAN SQL TRACE FILE’ from dual;
RMAN> backup database;
RMAN> exit;
Based on the trace file, we use tkprof to generate output. It turned out that the issue is related to their new network card in backup server. Upon replacing the network card the issue is fixed
3) In addition to this as a best practice we recommended them to create more than one mount point with different physical disk drives. With more than one mount point we recommended the customer to allocate two or more channels to perform database backup and this is found to increase the performance of oracle database
4) Customer’s database server had 4 cores (CPU) and such hardware offers great flexibility and can support improved hardware performance
5) As this customer had more than one physical disk, more CPU’s enabling parallelism at rman backups improved performance of RMAN backups
6) Backup strategy enhancement – This customer had weekly once full backup, once in 6 hours incremental backup strategy. Being heavy transaction critical production database incremental backups did cost them performance. We recommended block change tracking implementation that will improve the performance of incremental backups as the changed blocks are tracked making this process less resource intensive
Upon implementing block change tracking the time_taken_display column from v$rman_backup_job_Details view showed improvement in incremental backup timing
7) Proper memory allocation sizing of PGA and SGA – With automatic memory management in latest version of oracle database completely being automated using two parameters memory_target and memory_max_target, it is essential to know that RMAN jobs dies make use of memory buffers mainly in PGA areas. If disk and tape I/O slaves are not used then memory is allocated from SGA. V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO views can be used to obtain information on RMAN buffer allocation