How to mine an RMAN Log

Posted in: Technical Track

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.

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

Channel configured for NFS backup:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/mnt/backup_nfs/bkp/%d_%I_%T_%U' MAXOPENFILES 1;

There are four mounts for NEW backup vs. one mount for NFS backup.
Channel configured for NEW backup:

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;

Results for NFS vs NEW:

### 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

Script used: compare_backup_logs.sh

#!/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

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.

email

Interested in working with Michael? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *