Oracle中的差异增量备份和累积增量备份的区别、rman全备+增量备份常用脚本
Tags： Oraclerman全备全量备份增量备份备份恢复差异增量备份累积增量备份脚本脚本分享
👉 本文共约1427个字，系统预计阅读时间或需6分钟。
差异增量备份和累积增量备份的区别
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模式时，才可以将其恢复到上次提交事务处理时的状态。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
全库rman备份的脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | [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级增量备份：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 | [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 " daimm@sf-express.com < $RMAN_LOG_FILE exit $RSTAT |
其它rman脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | ---------- 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; } |
支持Markdown