Oracle RMAN备份脚本

一、单独备份

1、经典整库备份：backup as compressed backupset database include current controlfile plus archivelog delete all input;

2、tablespace：backup tablespace 名字;

3、数据文件：backup datafile n; (n:具体的数据文件编号select file_name,file_id,tablespace_name from dba_data_files;)

4、控制文件：backup current controlfile;或者backup database include current controlfile;

5、日志文件：backup archivelog all;或者 database plus archivelog;

6、参数文件：backup spfile;

二，备份实战

备份策略

1，小型库一般都是每周做一次全备，周天晚上12点。

2，每半年6月1号晚上0点做一个数据库的全备份（包括所有的数据和只读表空间） 

     每一个月1号晚上0点做一次零级备份（不包含只读表空间）

     每个星期三晚上0点做一次一级备份

     每天晚上0点做一次二级备份

备份前准备，查看环境。

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TEST are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/TEST/db/tech_st/11.1.0/dbs/snapcf_TEST.f'; # default

1，小型库全备：

[oracle@hand ~]$ mkdir orazabbix_rman
[oracle@hand ~]$ cd orazabbix_rman/
[oracle@hand orazabbix_rman]$ mkdir scripts

在/home/oracle/orazabbix_rman/scripts下创建如下脚本(注意这里orazabbix_rman命名规范)

脚本命名为backup.rcv

run {

  # Hot database level 0 whole backup

  allocate channel t1 type disk;

  backup

      incremental level 0

      skip inaccessible

      format '/orabak/orazabbix_rman/backupfiles/lev0/back_%s_%p_%T_%d'

  #AS COMPRESSED backupset

      database plus archivelog

      format '/orabak/orazabbix_rman/backupfiles/lev0/arclogback_%s_%p_%t_%d'

  delete input;

  delete obsolete;

  release CHANNEL t1 ;

}

这里阐述0级增加备份和全备份的区别，level 0级的就是一个full backup。

1，全备份备份了used and unused block

2，0级增量备份只备份了used block

设置小库全备执行脚本注意本地化

[oracle@hand ~]$ echo $ORACLE_SID
ams
[oracle@hand ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1

命名为orazabbix_backup.sh

#!/bin/sh

export ORACLE_SID=ams

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export TIMESTAMP=`date +'%Y_%m_%d_%H:%M'`

rman target / nocatalog cmdfile=/home/oracle/orazabbix_rman/scripts/backup.rcv log=/orabak/orazabbix_rman/logs/log_hecprod_${TIMESTAMP}.log

编辑完成之后授予执行权限

[oracle@hand scripts]$ chmod +x orazabbix_backup.sh

使用oracle用户执行如下命令，在crontab中配置定时任务。

[oracle@hand ~]$ crontab -e

0 0 * * 0 /home/oracle/orazabbix_rman/scripts/orazabbix_backup.sh

小型库一般都是每周做一次全备，周天晚上0点。

2，大库全备：

全库备份

备份脚本all_backup.rcv

 run{

    configure retention policy to recovery window of 200 days;            #设置两百天备份过期              

  configure controlfile autobackup on;

    configure controlfile autobackup format for device type disk to '/orabak/orazabbix_rman/backupfiles/all/%F.bak';

   allocate channel c1 type disk;

    allocate channel c2 type disk;

    allocate channel c3 type disk;

    backup full tag 'dbfull' format '/orabak/orazabbix_rman/backupfiles/all/full%u_%s_%p' database;

    sql 'alter system archive log current';

    backup filesperset 3 format '/orabak/orazabbix_rman/backupfiles/all/arch%u_%s_%p'

    archivelog all delete input;                                #备份归档可选，可以单独定期备份

    crosscheck backup;

    delete noprompt expired backup;

    report obsolete;

    delete noprompt obsolete;

     release channel c1;

     release channel c2;

     release channel c3;

    }

全库备份执行脚本all_backup.sh

#!/bin/sh

export ORACLE_SID=ams

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export TIMESTAMP=`date +'%Y_%m_%d_%H:%M'`

rman target / nocatalog cmdfile=/home/oracle/orazabbix_rman/scripts/all_backup.rcv log=/orabak/orazabbix_rman/logs/log_all_${TIMESTAMP}.log

编辑完成之后授予执行权限

[oracle@hand scripts]$ chmod +x all_backup.sh

使用oracle用户执行如下命令，在crontab中配置定时任务。

[oracle@hand ~]$ crontab -e

0 0 1 6,12 0 /home/oracle/orazabbix_rman/scripts/orazabbix_backup.sh

每半年6或12月1号晚上0点做一个数据库的全备份（包括所有的数据和只读表空间）

0级增量备份：

备份脚本level0_backup.rcv如下

run {

  # Hot database level 0 whole backup

 configure retention policy to recovery window of 180 days;

    configure controlfile autobackup on;

    configure controlfile autobackup format for device type disk to '/orabak/orazabbix_rman/backupfiles/lev0/%F.bak';

  allocate channel t1 type disk;

  allocate channel t2 type disk;

  allocate channel t3 type disk;

  backup as compressed backupset

      incremental level 0 tag 'orazabbix_level0'

      skip inaccessible

      format '/orabak/orazabbix_rman/backupfiles/lev0/bak_%d_%T_%s_%p_db_level0'

  #AS COMPRESSED backupset

  database

  plus archivelog

  format '/orabak/orazabbix_rman/backupfiles/lev0/arclogback_%s_%p_%t_%d'

  delete input;

   crosscheck backup; 

    delete noprompt expired backup;

    report obsolete;

  delete noprompt obsolete;

  release channel t1;

  release channel t2;

  release channel t3;

}

0级增量备份执行脚本orazabbix_level0_backup.sh

#!/bin/sh

export ORACLE_SID=ams

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export TIMESTAMP=`date +'%Y_%m_%d_%H:%M'`

rman target / nocatalog cmdfile=/home/oracle/orazabbix_rman/scripts/level0_backup.rcv log=/orabak/orazabbix_rman/logs/log_lev0_${TIMESTAMP}.log

编辑完成之后授予执行权限

[oracle@hand scripts]$ chmod +x orazabbix_level0_backup.sh

使用oracle用户执行如下命令，在crontab中配置定时任务。

[oracle@hand ~]$ crontab -e

0 0 1 * 0 /home/oracle/orazabbix_rman/scripts/orazabbix_level0_backup.sh

每一个月1号晚上0点做一次零级备份（不包含只读表空间）

1级增量备份：

备份脚本level1_backup.rcv

run {

  # Hot database level 1 whole backup

 configure retention policy to recovery window of 180 days;

    configure controlfile autobackup on;

    configure controlfile autobackup format for device type disk to '/orabak/orazabbix_rman/backupfiles/lev1/%F.bak';

  allocate channel t1 type disk;

  allocate channel t2 type disk;

  allocate channel t3 type disk;

  backup as compressed backupset

      incremental level 1 tag 'subsdb_level1'

      skip inaccessible

      format '/orabak/orazabbix_rman/backupfiles/lev1/bak_%d_%T_%s_%p_db_level1'

  #AS COMPRESSED backupset

  database plus archivelog

  format '/orabak/orazabbix_rman/backupfiles/lev1/arclogback_%s_%p_%t_%d'

  delete input;

  crosscheck backup;

    delete noprompt expired backup;

    report obsolete;

  delete noprompt obsolete;

  release channel t1;

  release channel t2;

  release channel t3;

}

1级增量备份执行脚本orazabbix_level1_backup.sh

#!/bin/sh

export ORACLE_SID=ams

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export TIMESTAMP=`date +'%Y_%m_%d_%H:%M'`

rman target / nocatalog cmdfile=/home/oracle/orazabbix_rman/scripts/level1_backup.rcv log=/orabak/orazabbix_rman/logs/log_lev1_${TIMESTAMP}.log

编辑完成之后授予执行权限

[oracle@hand scripts]$ chmod +x orazabbix_level1_backup.sh

使用oracle用户执行如下命令，在crontab中配置定时任务。

[oracle@hand ~]$ crontab -e

0 0 * * 3 /home/oracle/orazabbix_rman/scripts/orazabbix_level1_backup.sh

每个星期三晚上0点做一次一级备份（都是压缩备份）

2级增量备份：

备份脚本level2_backup.rcv

run {

  # Hot database level 2 whole backup

 configure retention policy to recovery window of 180 days;

    configure controlfile autobackup on;

    configure controlfile autobackup format for device type disk to '/orabak/orazabbix_rman/backupfiles/lev2/%F.bak';

  allocate channel t1 type disk;

  allocate channel t2 type disk;

  allocate channel t3 type disk;

  backup as compressed backupset

      incremental level 2 tag 'subsdb_level2'

      skip inaccessible

      format '/orabak/orazabbix_rman/backupfiles/lev2/bak_%d_%T_%s_%p_db_level2'

  #AS COMPRESSED backupset

  database

  plus archivelog

  format '/orabak/orazabbix_rman/backupfiles/lev2/arclogback_%s_%p_%t_%d'

  delete input;

  backup current controlfile format '/orabak/orazabbix_rman/backupfiles/lev2/bak_%d_%T_%s_ctl.ctl';

crosscheck backup;

    delete noprompt expired backup;

    report obsolete;

  delete noprompt obsolete;

  release channel t1;

  release channel t2;

  release channel t3;

}

2级增量备份执行脚本orazabbix_level2_backup.sh

#!/bin/sh

export ORACLE_SID=ams

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export TIMESTAMP=`date +'%Y_%m_%d_%H:%M'`

rman target / nocatalog cmdfile=/home/oracle/orazabbix_rman/scripts/level2_backup.rcv log=/orabak/orazabbix_rman/logs/log_lev2_${TIMESTAMP}.log

编辑完成之后授予执行权限

[oracle@hand scripts]$ chmod +x orazabbix_level2_backup.sh

使用oracle用户执行如下命令，在crontab中配置定时任务。

[oracle@hand ~]$ crontab -e

0 0 * * * /home/oracle/orazabbix_rman/scripts/orazabbix_level2_backup.sh

 每天晚上0点做一次二级备份（都是压缩备份）

归档日志备份：

备份脚本log_backup.rcv

run {

 configure retention policy to recovery window of 180 days; 

  allocate channel t1 type disk;

  backup

    skip inaccessible

    format '/orabak/orazabbix_rman/backupfiles/log/arclogback_%s_%p_%t_%d'

    #AS COMPRESSED backupset

   (archivelog all delete input);

  report obsolete;

  delete noprompt obsolete;

  release CHANNEL t1 ;

}

归档日志备份执行脚本orazabbix_log_backup.sh

#!/bin/sh

export ORACLE_SID=ams

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export TIMESTAMP=`date +'%Y_%m_%d_%H:%M'`

rman target / nocatalog cmdfile=/home/oracle/orazabbix_rman/scripts/log_backup.rcv log=/orabak/orazabbix_rman/logs/log_log_${TIMESTAMP}.log

编辑完成之后授予执行权限

[oracle@hand scripts]$ chmod +x orazabbix_log_backup.sh

使用oracle用户执行如下命令，在crontab中配置定时任务。

[oracle@hand ~]$ crontab -e

0 0 1 6,12 * /home/oracle/orazabbix_rman/scripts/orazabbix_log_backup.sh

 每年6月12月1日0点做一次归档日志（不是压缩备份）