Kevin@灼华
Oracle中的差异增量备份和累积增量备份的区别、rman全备+增量备份常用脚本
编辑
2024-07-14
数据相关
00
请注意，本文编写于 283 天前，最后修改于 283 天前，其中某些信息可能已经过时。

目录

差异增量备份和累积增量备份的区别
全库rman备份的脚本
rman增量备份的脚本
其它rman脚本

简介： Oracle中的差异增量备份和累积增量备份的区别、rman全备+增量备份常用脚本

差异增量备份和累积增量备份的区别

Oracle数据库备份可以分为完全备份和增量备份。完全数据文件备份是包含文件中所有已用数据块的备份。RMAN将所有块复制到备份集或映像副本中，仅跳过从未使用的数据文件块。完全映像副本可准确地再现整个文件的内容。完全备份不能成为增量备份策略的一部分，它也不能作为后续增量备份的基础。

增量备份就是将**那些与前一次备份相比发生变化的数据块复制到备份集中。**通过RMAN可以为单独的数据文件、表空间、或者整个数据库进行增量备份。增量备份是0级备份，其中包含数据文件中除从未使用的块之外的所有块；或者是1级备份，其中仅包含自上次备份以来更改过的那些块。0级增量备份在物理上与完全备份完全一样。唯一区别是0级备份可用作1级备份的基础，但完全备份不可用作1级备份的基础。要使用增量备份，必须先执行0级增量备份。需要注意的是，若没有执行0级增量备份，直接执行1级增量备份，那么Oracle也会先自动执行0级增量备份，再执行1级增量备份的。这在数据库比较大的时候，一定要注意磁盘空间的情况。

通过BACKUP命令中的INCREMENTAL关键字可指定增量备份，可以指定INCREMENTAL LEVEL[0|1]。在RMAN中建立的增量备份可以具有不同的级别，每个级别都使用一个不小于0的整数来标识，也就是在BACKUP命令中使用LEVEL关键字指定的，例如LEVEL = 0表示备份级别为0，LEVEL = 1表示备份级别为1。每次进行增量备份仅操作那些发生了“变化”的数据块。

RMAN中增量备份有两种：差异增量备份（DIFFERENTIAL）和累计增量备份（CUMULATIVE），它们的区别如下表所示：

方式关键字默认说明
差异增量备份DIFFERENTIAL是将备份上次进行的同级或低级备份以来所有变化的数据块，有同级备份同级，无同级备份低级
累积增量备份CUMULATIVE否将备份上次进行的低级备份以来所有变化的数据块

差异增量备份和累计增量备份如下图所示：

  • 执行0级增量备份的命令为：RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;
  • 执行1级差异增量备份的命令为：RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;
  • 执行1级累积增量备份的命令为：RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

可以对处于NOARCHIVELOG模式的数据库执行任何类型的备份（完全或增量）。当然，前提条件是数据库处于未打开状态。需要注意的是，数据库只能恢复到上次备份时的状态。只有当数据库处于ARCHIVELOG模式时，才可以将其恢复到上次提交事务处理时的状态。

全库rman备份的脚本

[oracle@rhel6lhr ~]$ crontab -l
2 12 * * 1 /home/oracle/lhr/rman/rman_backup_full.sh

[oracle@rhel6lhr ~]$ more /home/oracle/lhr/rman/rman_backup_full.sh
export ORACLE_SID=orclasm
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
MYDATE=`date +'%Y%m%d%H%M%S'`
BACKUP_DIR=/u05/oracle/oracle_bk/$ORACLE_SID

 
rman target /  log /home/oracle/lhr/rman/log/rman_full_$MYDATE.log append <<EOF
run
{
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 backup database filesperset 4 format  '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
 sql 'alter system archive log current';
 backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
 backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
}
EOF

rman target /  log /home/oracle/lhr/rman/log/rman_delete_$MYDATE.log append <<EOF
 allocate channel for maintenance type disk;
 allocate channel for maintenance type sbt_tape;
 crosscheck copy; 
 crosscheck archivelog all;
 crosscheck backup; 
 delete noprompt obsolete;
 delete noprompt expired backup;
EOF

rman增量备份的脚本

下面给出增量备份的脚本，备份策略为周日0级全备，周四为1级增量备份，其它为2级增量备份：

bash
[oracle@rhel6lhr ~]$ crontab -l
40 11 * * * /home/oracle/lhr/rman/run_rman_incremental.sh

[oracle@rhel6lhr ~]$ more /home/oracle/lhr/rman/run_rman_incremental.sh
sh /home/oracle/lhr/rman/rman_incremental.sh 2>&1  &
[oracle@rhel6lhr ~]$ more  /home/oracle/lhr/rman/rman_incremental.sh
########################################################################
##   incremental_database_backup.sh      ##
##   created by lhr                      ##
## sun:0 Thu:1 others:2                  ##
#########################################################################
#!/bin/ksh 


export LANG=en_US
MYDATE=`date +'%Y%m%d%H%M%S'`
MYDATE2=`date +'%Y-%m-%d %H:%M:%S'`
CUSER=`id|cut -d "(" -f2|cut -d ")" -f1` 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export RMAN=$ORACLE_HOME/bin/rman
export ORACLE_SID=orclasm
export ORACLE_USER=oracle 
BACKUP_DIR=/u05/oracle/oracle_bk/$ORACLE_SID
RMAN_LOG_FILE=$BACKUP_DIR/log/rman_incremental_"$ORACLE_SID"_$MYDATE.log


echo "-----------------$MYDATE2-------------------">$RMAN_LOG_FILE  
echo "ORACLE_SID: $ORACLE_SID">>$RMAN_LOG_FILE
echo "ORACLE_HOME:$ORACLE_HOME">>$RMAN_LOG_FILE
echo "ORACLE_USER:$ORACLE_USER">>$RMAN_LOG_FILE
echo "==========================================">>$RMAN_LOG_FILE
echo "BACKUP DATABASE BEGIN......">>$RMAN_LOG_FILE
echo "                   ">>$RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
 
WEEK_DAILY=`date +%a`
 
case  "$WEEK_DAILY" in
       "Mon")
            BAK_LEVEL=2
            ;;
       "Tue")
            BAK_LEVEL=2
            ;;
       "Wed")
            BAK_LEVEL=2
            ;;
       "Thu")
            BAK_LEVEL=1
            ;;
       "Fri")
            BAK_LEVEL=2
            ;;
       "Sat")
            BAK_LEVEL=2
            ;;
       "Sun")
            BAK_LEVEL=0
            ;;
       "*")
            BAK_LEVEL=error
esac
export BAK_LEVEL=$BAK_LEVEL 
echo "Today is : $WEEK_DAILY  incremental level= $BAK_LEVEL">>$RMAN_LOG_FILE
 
RMAN_RUN_STR="
        export BAK_LEVEL=$BAK_LEVEL 
        export ORACLE_HOME=$ORACLE_HOME 
        export ORACLE_SID=$ORACLE_SID
        export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss' 
        $RMAN nocatalog TARGET sys/lhr log $RMAN_LOG_FILE append <<EOF
        set echo on;
        run
        {
                allocate channel c1 type disk;
                allocate channel c2 type disk;
                backup  incremental level= $BAK_LEVEL  skip inaccessible filesperset 5 Database format='$BACKUP_DIR/datafile_lev"$BAK_LEVEL"_%d_%I_%U_%T.bak'  tag='bk_df_lev"$BAK_LEVEL_$MYDATE"' ;
                sql 'alter system archive log current';
                backup archivelog all format='$BACKUP_DIR/arch_%d_%I_%e_%U_%T.bak'  tag='bk_arc_$MYDATE'  skip inaccessible  filesperset 5 not  backed up 1 times  delete input;
                backup current controlfile format='$BACKUP_DIR/ctlfile_%d_%I_%U_%T.bak'  tag='bk_ctlfile_$MYDATE' ;
                sql \"alter database backup controlfile to trace as ''$BACKUP_DIR/ctlfile_trace_"$ORACLE_SID"_$MYDATE.txt'' \";
                backup spfile  format='$BACKUP_DIR/spfile_%d_%I_%U_%T.bak' tag='bk_spfile_$MYDATE';
                release channel c2;
                release channel c1;
        }
        allocate channel for maintenance device type disk; 
        report obsolete; 
        crosscheck copy; 
        crosscheck archivelog all;
        delete noprompt obsolete; 
        crosscheck backup; 
        delete noprompt expired backup;
        list backup summary;
        release channel;
        exit
        EOF
"
 
# Initiate the command string 
if [ "$CUSER" = "root" ] 
then 
    echo "Root Command String: $RMAN_RUN_STR" >> $RMAN_LOG_FILE     
    su - $ORACLE_USER -c "$RMAN_RUN_STR" >> $RMAN_LOG_FILE 
    RSTAT=$? 
else 
    echo "User Command String: $RMAN_RUN_STR" >> $RMAN_LOG_FILE     
    /bin/sh -c "$RMAN_RUN_STR" >> $RMAN_LOG_FILE 
    RSTAT=$? 
fi 
 
# --------------------------------------------------------------------------- 
# Log the completion of this script. 
# --------------------------------------------------------------------------- 
if [ "$RSTAT" = "0" ] 
then 
    LOGMSG="ended successfully" 
else 
    LOGMSG="ended in error" 
fi 
 
echo >> $RMAN_LOG_FILE 
echo Script $0 >> $RMAN_LOG_FILE 
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE 
echo >> $RMAN_LOG_FILE 
##/bin/mailx -s "RMAN Backup SID " [email protected] < $RMAN_LOG_FILE 
 
exit $RSTAT

其它rman脚本

bash

---------- rman备份 全备 ---------------------------------------------------------



alter database enable block change tracking using file '/home/oracle/bct.log';
select * from v$block_change_tracking;


backup database include current controlfile plus archivelog delete input ;
backup as backupset database  format  '/u05/oracle/oracle_bk/orclasm/full%n%T%t%s%p.bak' include current controlfile plus archivelog;
backup  as compressed backupset  format  '/arch/oracle_bk/ora2lhr/full%n%T%t_%s.bak' database include current controlfile plus archivelog delete input ;

run
{
 backup as compressed backupset database;
 sql 'alter system archive log current';
 backup archivelog all ;
 backup current controlfile;
 backup spfile;
}

run
{
 backup as compressed backupset database format '/home/oracle/bk/%d%U.full';
 sql 'alter system archive log current';
 backup as compressed backupset archivelog all  format '/home/oracle/bk/%d%U.arc';
 backup current controlfile format '/home/oracle/bk/%d_%U.ctl';
 backup spfile format '/home/oracle/bk/spfile_%d_%U.ora';
}

run
{
 backup INCREMENTAL LEVEL 0 as compressed backupset database  format '/home/oracle/bk/%d%U.full';
 sql 'alter system archive log current';
 backup archivelog all  format '/home/oracle/bk/%d%U.arc';
 backup current controlfile  format '/home/oracle/bk/%d%U.ctl';
 backup spfile format '/home/oracle/bk/spfile_%d_%U.ora';
}

create table lhr.test0917 as select * from dba_objects;

run
{
 backup INCREMENTAL LEVEL 1 as compressed backupset database  format '/home/oracle/bk/%d%U.full';
 sql 'alter system archive log current';
 backup archivelog all  format '/home/oracle/bk/%d%U.arc';
 backup current controlfile  format '/home/oracle/bk/%d%U.ctl';
 backup spfile format '/home/oracle/bk/spfile_%d_%U.ora';
}

create table lhr.test0917_2 as select * from dba_objects;
run
{
 backup as compressed backupset INCREMENTAL  from scn 6310032 database  format '/home/oracle/bk/%d%U.full';
 sql 'alter system archive log current';
 backup archivelog all  format '/home/oracle/bk/%d%U.arc';
 backup current controlfile  format '/home/oracle/bk/%d%U.ctl';
 backup spfile format '/home/oracle/bk/spfile_%d_%U.ora';
}


restore database  until sequence 12;
recover database  until sequence 12;


BACKUP INCREMENTAL FROM scn 127848227154 DATABASE FORMAT '/home/oracle/bk/full_scn_%d_%U.full';



-- CUMULATIVE 表示总是备份比自己低级的备份

run
{
 backup INCREMENTAL LEVEL 1 as compressed backupset CUMULATIVE database  format '/home/oracle/bk/%d%U_inc.full';
 sql 'alter system archive log current';
 backup as compressed backupset archivelog from time 'sysdate-1/24' format '/home/oracle/bk/%d%U_inc.arc' section size 100G;
 backup current controlfile  format '/home/oracle/bk/%d%U_inc.ctl';
}






---- windows下备份-----------------------------------------------------------


set yyyy=%DATE:~0,4%%-%%DATE:~5,2%%-%%DATE:~8,2%
set baklog='d:\dbback\%yyyy%.log'
set nls_date_format=yyyy-mm-dd hh24:mi:ss
rman target / cmdfile="d:\dbback\full_bak.txt" msglog=%baklog%
run {
sql 'alter system archive log current';
backup as compressed backupset FILESPERSET 2 database format 'd:\dbback\%T_db%s%U.dbf' plus archivelog delete input;
sql 'alter system archive log current';
backup current controlfile format 'd:\dbback\%T_ctl%s%U.dbf';
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
}



run {
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
sql 'alter session set NLS_LANGUAGE="AMERICAN"';
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
allocate channel c11 type disk;
allocate channel c12 type disk;
backup as compressed backupset FILESPERSET 1 database format 'd:\dbback\FULL%d%U.full' section size 100G;
sql 'alter system archive log current';
backup as compressed backupset archivelog all  format 'd:\dbback\ARC%d%U.arc' section size 100G;
backup current controlfile format 'd:\dbback\cf%d%U.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
}

本文作者:Kevin@灼华

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！