Pythian Blog: Technical Track

How to mine an RMAN Log

by Michael Dinh on Jun 24, 2019 12:00:00 AM

I wanted to compare Oracle Recovery Manager (RMAN) backup to an existing NFS versus a NEW backup appliance. I performed a Proof Of Concept (POC) to compare and contrast NFS and NEW, backed up both, then mined the logs to compare the results. I performed the same backup, changing only the backup tag. [code] connect target; show all; backup as compressed backupset check logical incremental level 0 section size 32G database tag NEW_LEVEL0 filesperset 8; list backup summary; exit [/code] Channel configured for NFS backup: [code] CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/mnt/backup_nfs/bkp/%d_%I_%T_%U' MAXOPENFILES 1; [/code] There are four mounts for NEW backup vs. one mount for NFS backup. Channel configured for NEW backup: [code] CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/mnt/backup_new_0/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/mnt/backup_new_1/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/mnt/backup_new_2/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT '/mnt/backup_new_3/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 5 DEVICE TYPE DISK FORMAT '/mnt/backup_new_0/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 6 DEVICE TYPE DISK FORMAT '/mnt/backup_new_1/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 7 DEVICE TYPE DISK FORMAT '/mnt/backup_new_2/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 8 DEVICE TYPE DISK FORMAT '/mnt/backup_new_3/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 9 DEVICE TYPE DISK FORMAT '/mnt/backup_new_0/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 10 DEVICE TYPE DISK FORMAT '/mnt/backup_new_1/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 11 DEVICE TYPE DISK FORMAT '/mnt/backup_new_2/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 12 DEVICE TYPE DISK FORMAT '/mnt/backup_new_3/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 13 DEVICE TYPE DISK FORMAT '/mnt/backup_new_0/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 14 DEVICE TYPE DISK FORMAT '/mnt/backup_new_1/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 15 DEVICE TYPE DISK FORMAT '/mnt/backup_new_2/bkp/%d_%I_%T_%U' MAXOPENFILES 1; CONFIGURE CHANNEL 16 DEVICE TYPE DISK FORMAT '/mnt/backup_new_3/bkp/%d_%I_%T_%U' MAXOPENFILES 1; [/code] Results for NFS vs NEW: [code] ### Backup duration NFS Starting backup at 2019-06-10 08:56:34 Finished backup at 2019-06-10 10:59:04 ### Backup duration NEW Starting backup at 2019-06-20 15:32:21 Finished backup at 2019-06-20 17:35:32 ### Backup piece at target NFS (there are 141 backuppiece and only 1 listed for example) 141 piece handle=/mnt/backup_nfs/bkp/DB_NAME_105164007_20190610_npu3o1e7_10_1 tag=NFS_LEVEL0 comment=NONE ### Backup piece at target NEW (there are 141 backuppiece and only 4 listed for example) 142 piece handle=/mnt/backup_new_0/bkp/DB_NAME_105164007_20190620_0ju4j4c9_11_1 tag=NEW_LEVEL0 comment=NONE piece handle=/mnt/backup_new_1/bkp/DB_NAME_105164007_20190620_0ju4j4c9_10_1 tag=NEW_LEVEL0 comment=NONE piece handle=/mnt/backup_new_2/bkp/DB_NAME_105164007_20190620_0ju4j4c9_12_1 tag=NEW_LEVEL0 comment=NONE piece handle=/mnt/backup_new_3/bkp/DB_NAME_105164007_20190620_0ju4j4c9_15_1 tag=NEW_LEVEL0 comment=NONE ### Backup sets created NFS 140 ### Backup sets created NFS 141 ### Backup data files for DB (there are 172 data files and only 2 list for example) 172 input datafile file number=00001 name=system.296.970681911 input datafile file number=00191 name=undotbs2.570.970680249 ### Might be wondering why there are only 172 data files when the range is 1-191? There are gaps. input datafile file number=00049 input datafile file number=00066 input datafile file number=00072 input datafile file number=00081 ### Backup size at target NFS (1 mount) 768G /mnt/backup_nfs/bkp 768G total ### Backup size at target NEW (4 mounts) 194G /mnt/backup_new_0/bkp 174G /mnt/backup_new_1/bkp 188G /mnt/backup_new_2/bkp 212G /mnt/backup_new_3/bkp 766G total [/code] Script used: compare_backup_logs.sh [code] #!/bin/sh ls -lht ~/working/dinh/backup_rman* export log1=~/working/dinh/backup_rman.log.nfs export log2=~/working/dinh/backup_rman.log.new echo "### Backup duration NFS" egrep 'Starting backup|Finished backup' $log1 echo "### Backup duration NEW" egrep 'Starting backup|Finished backup' $log2 echo "### Backup piece at target NFS" grep -c "piece handle" $log1 grep "piece handle" $log1| sort echo "### Backup piece at target NEW" grep -c "piece handle" $log2 grep "piece handle" $log2| sort echo "### Backup sets created NFS" grep -c "specifying datafile(s) in backup set" $log1 echo "### Backup sets created NEW" grep -c "specifying datafile(s) in backup set" $log2 echo "### Backup datafile at DB" grep -c "input datafile file number" $log1| sort -u grep "input datafile file number" $log2| sort -u echo "### Backup size at target NFS" du -shc /mnt/backup_nfs/bkp echo "### Backup size at target NEW" du -shc /mnt/backup_new_?/bkp exit [/code] Having sufficient data gathered and analyzed, it's easy to compare performance and decide if there is any performance gain and if the existing backup should be migrated to a new appliance or not.
Topics: Oracle Oracle Database Technical Track Oracle Flashback

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.