Oracle中的差异增量备份和累积增量备份的区别、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级增量备份:

[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脚本



---------- 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;
}









目录
相关文章
|
1月前
|
Oracle 安全 关系型数据库
Oracle与GreatSQL差异:更改唯一索引列
【11月更文挑战第1天】本文介绍了在 Oracle 和 GreatSQL 中更改唯一索引列的方法及差异。Oracle 需要手动删除和重建索引,过程复杂且可能影响数据一致性;而 GreatSQL 可以自动维护索引,直接修改列值即可,操作简便且更安全。
|
18天前
|
SQL Oracle 关系型数据库
Oracle与GreatSQL差异:更改唯一索引列
【11月更文挑战第11天】本文介绍了在 Oracle 和 GreatSQL 中修改唯一索引列的操作。Oracle 需要先删除索引、修改列值,再重新创建索引,步骤较为繁琐。而 GreatSQL 在满足一定条件下支持在线 DDL 操作,可以直接修改列值,操作相对简单。两者都需要考虑数据完整性和表上的其他约束条件。
|
3月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
593 18
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
4月前
|
机器学习/深度学习 Oracle 关系型数据库
Oracle 19c单机一键安装脚本分享
Oracle 19c单机一键安装脚本分享
219 2
|
5月前
|
Oracle 安全 关系型数据库
|
5月前
|
存储 Oracle 关系型数据库
|
4月前
|
Oracle 关系型数据库 数据库
Oracle数据库备份脚本分享-Python
Oracle数据库备份脚本分享-Python
95 0
|
4月前
|
Oracle 安全 关系型数据库
Oracle安装部署再也不用头疼了,分享一个实用的一键部署脚本,建议收藏!
Oracle安装部署再也不用头疼了,分享一个实用的一键部署脚本,建议收藏!
164 0
|
5月前
|
SQL Oracle 关系型数据库
关系型数据库Oracle设置 RMAN 环境:
【7月更文挑战第25天】
72 2
|
5月前
|
监控 Oracle 算法

推荐镜像

更多