1 Reply Latest reply: Dec 18, 2012 12:30 PM by mseberg RSS

    Backup database incremental and restore db from control file

    791423
      Hi all,

      At first sorry for my bad English. Now i need to backup my database daily and weekend. Here is my script but i'm not sure does it miss something. Can you tell me which i miss on my script?

      This one is for weekend:
      #!/bin/ksh
      ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
      ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
      BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
      PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
      /u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target sys/xxxx@xx.xxx.x.xx:1521/vvib log /home/oracle/backup_schedule/backup_log/vvib_weekend.log append <<EOF
      RUN {
      BACKUP INCREMENTAL LEVEL 0 TAG 'incr' DATABASE FILESPERSET 4 NOT BACKED UP SINCE TIME 'SYSDATE-1';
      BACKUP ARCHIVELOG ALL TAG 'incr_vvib';
      DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';
      DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
      }
      EXIT;
      EOF

      =======================
      This one is for daily
      #!/bin/ksh
      ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
      ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
      BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
      PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
      /u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target sys/xxxx@xx.xxx.x.xx:1521/vvib log /home/oracle/backup_schedule/backup_log/vvib_daily.log append <<EOF
      RUN {
      RECOVER COPY OF DATABASE WITH TAG 'incr_vvib';
      BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_vvib' DATABASE FILESPERSET 4 NOT BACKED UP SINCE TIME 'SYSDATE -1 ';
      BACKUP ARCHIVELOG ALL TAG 'incr_vvib';
      DELETE archivelog until time 'sysdate-7';
      DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
      }
      EXIT;
      EOF

      ==========================


      And i still have a problem that i need to restore db from the scratch. I droped my database and recreate a new db with the same sid. Then i try to restore database from backuped up file. My steps are:
      1. set dbid and restore control fiie from my backup
      2. restore database
      3. recover database
      4. open database with resetlogs option

      But when i try to recover database i got the problem like :
      archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_12_18/o1_mf_1_5_8f16ltvz_.arc thread=1 sequence=5
      unable to find archived log
      archived log thread=1 sequence=1
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of recover command at 12/18/2012 23:31:24
      RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 993734

      I don't know what did i miss in the steps to restore database. Can you suggest me how do i need to do?

      Thanks in advance!
        • 1. Re: Backup database incremental and restore db from control file
          mseberg
          Hello;

          Will probably break this into two pieces.

          Off the top of my head the backup would be :

          ( I left the log piece off, but you need to test several times before proceeding )

          ( Also I did not include obsolete as I have no idea what you policy is set to )
          #!/bin/ksh
          
          #
          # Set the Environmental variables
          #
          
          
          export ORACLE_BASE=/u01/app/oracle
          export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
          export PATH=$ORACLE_HOME/bin:$ORACLE_BASE/dba_tool/bin:/bin:/usr/bin:/etc:/etc/X11/xserver/
          
          
          echo $DBNAME
          
          echo "Starting RMAN..."
          $ORACLE_HOME/bin/rman target / catalog <catalog_owner>/<password>@<catalog_db> << EOF
          
          run { allocate channel d1 type disk; 
          allocate channel d2 type disk;
          allocate channel d3 type disk; 
          
          
          backup INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET database include current
          controlfile;
          
          sql "alter system archive log current";
          backup AS COMPRESSED BACKUPSET archivelog all;
          
          release channel d1;
          release channel d2;
          release channel d3;
          
          }
          
          
          delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-7';
          
          DELETE noprompt BACKUP COMPLETED BEFORE 'SYSDATE-2' DEVICE TYPE DISK;
          
          exit
          EOF
          
          echo `date`
          #
          echo
          echo "RMAN Full  Complete"
          With minor changes for my test system it works.

          Samples from output :
          Starting backup at 18-DEC-12
          starting full resync of recovery catalog
          full resync complete
          allocated channel: ORA_DISK_1
          channel ORA_DISK_1: SID=213 device type=DISK
          channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
          channel ORA_DISK_1: specifying datafile(s) in backup set
          
          Finished backup at 18-DEC-12
          
          Starting Control File and SPFILE Autobackup at 18-DEC-12
          piece handle=/u01/app/oracle/flash_recovery_area/RECOVER2/autobackup/2012_12_18/o1_mf_s_802354298_8f1dgtv2_.bkp comment=NONE
          Finished Control File and SPFILE Autobackup at 18-DEC-12
          
          RMAN> 
          
          RMAN> 
          Starting backup at 18-DEC-12
          current log archived
          using channel ORA_DISK_1
          channel ORA_DISK_1: starting compressed archived log backup set
          channel ORA_DISK_1: specifying archived log(s) in backup set
          input archived log thread=1 sequence=2620 RECID=3505 STAMP=799797643
          input archived log thread=1 sequence=2621 RECID=3506 STAMP=799797816
          input archived log thread=1 sequence=2622 RECID=3507 STAMP=799870618
          Barked on the release channel pieces ( Caused by configure issue on my test )



          Best Regards

          mseberg

          Edited by: mseberg on Dec 18, 2012 12:17 PM