8 Replies Latest reply: May 9, 2012 2:03 PM by 726263 RSS

    RMAN Backup Questions

    705230
      Hello,
      I have been working on a new backup script for our test databases and have a few questions...

      My Goals:
      Take Level 0 backup every Sunday at midnight
      Take Level 1 backup every other day besides Sunday at midnight
      Have 1 week of retention on the disk, every backup is transferred to CommVault disk/tape every day at 3:00am

      Questions:
      How is the retention handled, and how do I make sure only 1 week is on disk?
      Any additional recommendations?

      Here is my script that is executed every day via crontab (0 0 * * * sh /u99/backup/scripts/backup_script.shl):

      # <INSTANCE VARIABLES>
      export ORACLE_SID=
      export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
      DAY=`date +%A`
      if [ "$DAY" == "Sunday" ]; then
      export LEVEL=0
      else
      export LEVEL=1
      fi
      export USER=
      export PASS=
      # </INSTANCE VARIABLES>

      # <CONSTANT VARIABLES>
      DD=`date +%m-%d-%Y`
      export PATH=$PATH:$ORACLE_HOME/bin
      export DBA_EMAIL=
      RMAN_BACKUP=/u99/backup
      RMAN_LOG=$RMAN_BACKUP/logs/"$ORACLE_SID"_LVL_"$LEVEL"_backup_"$DD".log
      RMAN_LOG_DETAILED=$RMAN_BACKUP/logs/"$ORACLE_SID"_LVL_"$LEVEL"_rman_"$DD".log
      # </CONSTANT VARIABLES>

      set -x

      touch $RMAN_LOG
      touch $RMAN_LOG_DETAILED

      echo "******* RMAN LVL ${LEVEL} BACKUP *******" >> $RMAN_LOG
      echo "RMAN LVL ${LEVEL} Backup Start Time: `date`" >> $RMAN_LOG
      DD=`date +%m-%d-%Y`
      mkdir -p ${RMAN_BACKUP}/${ORACLE_SID}/${DD}/datafiles/
      mkdir -p ${RMAN_BACKUP}/${ORACLE_SID}/${DD}/controlfiles/
      mkdir -p ${RMAN_BACKUP}/${ORACLE_SID}/${DD}/archivelogs/


      echo $RMAN_LOG
      echo $RMAN_LOG_DETAILED

      sqlplus -s "${USER}/${PASS}@${ORACLE_SID} as sysdba" <<EOF >> $RMAN_LOG
      set feedback off;
      alter system archive log current
      /
      set serveroutput on
      declare
      x number;
      begin
      select max(SEQUENCE#) into x from gv\$archived_log where thread#=1;
      dbms_output.put_line('The Start Sequence number on instance $ORACLE_SID = '||x||'');
      sys.dbms_system.ksdwrt(2,'Note to DBA : On $ORACLE_SID RMAN Backup Starts at '||to_char(x)||' on '||to_char(sysdate,'DD/MON/YYYY HH:MM:MI'));
      end;
      /
      exit
      EOF
      rman target / nocatalog log=$RMAN_LOG_DETAILED << EOF1
      change archivelog all crosscheck;
      run {
      CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
      CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
      backup as compressed backupset incremental level ${LEVEL} database tag '${ORACLE_SID}_LVL_${LEVEL}_BACKUP' format '${RMAN_BACKUP}/${ORACLE_SID}/${DD}/datafiles/%d_%s_%p';
      backup as compressed backupset archivelog all not backed up 1 times FORMAT '${RMAN_BACKUP}/${ORACLE_SID}/${DD}/archivelogs/ARCH_%d_%s_%p';
      CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${RMAN_BACKUP}/${ORACLE_SID}/${DD}/controfiles/%F';
      delete noprompt obsolete;
      delete noprompt archivelog all completed before 'sysdate-2';
      }
      EXIT;
      EOF1
      RC=$?
      echo "\n RMAN LVL ${LEVEL} Backup End Time: `date`" >> $RMAN_LOG

      if [ $RC -ne "0"  ]; then
      mutt -s "${ORACLE_SID} RMAN LVL ${LEVEL} Failed" -a $RMAN_LOG_DETAILED -m text/plain $DBA_EMAIL < $RMAN_LOG
      else
      mutt -s "${ORACLE_SID} RMAN LVL ${LEVEL} Successful" -a $RMAN_LOG_DETAILED -m text/plain $DBA_EMAIL < $RMAN_LOG
      fi
        • 1. Re: RMAN Backup Questions
          damorgan
          Consider:

          BACKUP VALIDATE CHECK LOGICAL RECOVERY AREA;
          BACKUP VALIDATE SPFILE;

          Make sure the SPFILE is backed up.
          Make sure control file is backed up.

          Make sure TNSNAMES.ORA, LISTENER.ORA, SQLNET.ORA, password file, etc. are also backed up.

          I would not, however, run at midnight or using crontab.

          The weakness of midnight is that is when everybody runs everything. Run at 17 minutes after the hour or some other non-predictable time. Also preferably use DBMS_SCHEDULER to run the backup as an external job. The error handling and scheduling are far more robust, operating system independent, and it works with stand-alone as well as with RAC.
          • 2. Re: RMAN Backup Questions
            705230
            damorgan,
            Thank you for the suggestions you have provided, I will work on the script some more today to incorporate the below commands.
            I appreciate your response :)
            • 3. Re: RMAN Backup Questions
              705230
              damorgan,
              How can I change BACKUP VALIDATE CHECK LOGICAL RECOVERY AREA; to validate backups in my /u99/backups folder? I do not use the flash recovery area...
              Thanks!
              Stan
              • 4. Re: RMAN Backup Questions
                726263
                How is the retention handled, and how do I make sure only 1 week is on disk?
                Why not use Oracle Enterprise Manager to manage your backups and just setup your mount point as the FRA.
                RMAN manages all your backups in FRA. You will have much more benefits using this with 11g.


                KDSDBA
                • 5. Re: RMAN Backup Questions
                  705230
                  Which brings me to my next question. How should I configure my backups? Should I do level 0 backups on Sundays and then do Differential Incremental Backups/Cumulative Incremental Backups daily?
                  • 6. Re: RMAN Backup Questions
                    726263
                    Oracle Enterprise Manager allows you to set up Incremental Backups.
                    Actually there is a Oracle automated backup strategy included already that you can select.

                    Regards,
                    KDSDBA
                    • 7. Re: RMAN Backup Questions
                      705230
                      KDSDBA,
                      Ok so if I just use the "Oracle-suggested Disk Backup" solution, I can only restore 24 hours back?

                      My ideal setup would be to have a recovery window of 7 days, with compressed backups to save on disk space. Should I setup a level 0 backup for Sunday and then Level 1's for all the other days?
                      -OwnedThawte
                      • 8. Re: RMAN Backup Questions
                        726263
                        I take full backups each night that uses the optimize feature under policy settings that takes the whole database backup by skipping unchanged files such as read-only and offline datafiles that have been backed up. You could take one full backup each Sunday and then incrementials during the week.
                        That would be up to your company business policy and retention you need but it's simple to implement using Enterprise Manager.
                        Compression for backups may need a Oracle management pack. I don't use that feature.

                        Regards,
                        KDSDBA