Oracle11gR2——RMAN备份完整实施

软件环境：RHEL6.4 Oracle11gR2

备份环境：目标数据库处于归档模式，RMAN使用了目录数据库，使用nfs让RMAN直接备份到远程机器

备份策略：周末实施lv0增量备份，工作日实施lv1增量备份

目标数据库（存放rman备份信息的数据库）的IP为172.26.181.102，主机名为rman-database，数据库实例名为testdb

目录数据库（要备份的数据库）的IP为172.26.181.101，主机名为rman-backup，数据库实例名为catalog

0、设置nfs

配置nfs的方法查看另一篇blog

http://blog.csdn.net/wang_san_shi/article/details/41848459

注意不要忘记在目标数据库端设置开机挂载nfs，以及oracle用户对nfs目录的操作权限。

1、注册目录数据库

为了保证控制文件丢失备份仍然可用，需要创建目录来保存RMAN信息。

首先使用SQLPLUS登录目录数据库，创建rman目录的表空间及用户并授权：

SQL> create tablespace rmancat datafile '/u01/app/oracle/oradata/catalog/rmancat01.dbf' size 200m;

Tablespace created.

SQL> create user rman identified by rman default tablespace rmancat quota unlimited on rmancat;

User created.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

然后我们在目标端使用RMAN同时登录目标数据库（本地）及目录数据库：

[oracle@rman-database ~]$ rman target / catalog rman/rman@172.26.181.101:1521/catalog

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 17 09:00:30 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2649780859)
connected to recovery catalog database

RMAN>

同时登录上目标和目录数据库以后，可以创建目录并注册数据库：

RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

此时rman目录注册完毕，并且已经将控制文件中的备份信息同步到目录数据库中了。

2、设置控制文件自动备份及保留策略

将控制文件自动备份打开，那么在每次使用RMAN执行backup语句时，都会自动备份控制文件。

注册完目录后，继续在rman中执行下面的语句打开控制文件自动备份：

RMAN> configure controlfile autobackup on;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

还要指定控制文件自动备份的路径，这里的路径/u01/backup即为nfs：

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/backupset/ctl_%d_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/backupset/ctl_%d_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

配置保留策略为恢复窗口14天，此策略下可以将数据库最多恢复到14天前：

RMAN> configure retention policy to recovery window of 14 days;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

查看是否配置成功：

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/backupset/ctl_%d_%F';
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 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_testdb.f'; # default

可以看到控制文件自动备份已打开，且自动备份路径已设置。

这里文件名中的符号含义如下：
%d：数据库名称
%F：一个基于DBID 唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD 为日期，QQ 是一个1-256 的序列
其中%F为必选项，否则会报错，另外如果设置了其他格式例如%s%p等，在自动备份时也会报错导致无法备份。

3、打开block change tracking

因为这里用到了增量备份，为了提高备份速度，打开这个功能可以使RMAN更快地找到更改过的数据块，且实践表明开启该功能对数据库性能的影响并不明显。

使用SQLPLUS登录目标数据库，执行下面的语句：

SQL> alter database enable block change tracking using file '/u01/app/oracle/oradata/testdb/change_tracking.dbf';

Database altered.

4、完成备份脚本

这里的备份策略为周日执行全备份，工作日执行增量备份，这就需要两个rman脚本，另外还需要两个sh脚本来调用这两个脚本，也就是一共4个脚本。

周日全备的level0.rman脚本内容如下：

connect target /
connect catalog rman/rman@172.26.181.101:1521/catalog
run{
    allocate channel c1 type disk;

    backup as backupset incremental level 0 database format '/u01/backup/backupset/df0_%d_%s_%p_%T' plus archivelog delete all input format '/u01/backup/backupset/arch_%d_%s_%p_%T';

    crosscheck backup;

    delete noprompt expired backup;

    release channel c1;
    }

工作日增量备份的level1.rman脚本内容如下：

connect target /
connect catalog rman/rman@172.26.181.101:1521/catalog
run{
    allocate channel c1 type disk;

    backup as backupset incremental level 1 database format '/u01/backup/backupset/df1_%d_%s_%p_%T' plus archivelog delete all input format '/u01/backup/backupset/arch_%d_%s_%p_%T';

    crosscheck backup;

    delete noprompt expired backup;

    release channel c1;
    }

周日执行的weekends.sh脚本内容如下:

#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=testdb
PATH=$PATH:$ORACLE_HOME/bin
export PATH
export ORACLE_SID
export ORACLE_HOME
rman cmdfile=/u01/backup/script/level0.rman msglog=/u01/backup/log/level0_backup.log

工作日执行的workday.sh脚本内容如下：

#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=testdb
PATH=$PATH:$ORACLE_HOME/bin
export PATH
export ORACLE_SID
export ORACLE_HOME
rman cmdfile=/u01/backup/script/level1.rman msglog=/u01/backup/log/level1_backup.log

5、配置归档到远程

归档文件本身就是备份，如果这些文件因为数据库服务器的完全故障而丢失，那么将意味着自上一次备份以后的数据都会丢失，所以这里修改归档目录到nfs。

但是这样的话如果nfs服务不可用将会导致数据库不可用。即多了一个故障点。

使用SQLPLUS登录目标数据库，并执行下面的命令：

SQL> alter system set log_archive_dest_1 = "location=/u01/backup/archive" scope=both;

System altered.

SQL> alter system archive log current;

System altered.

然后可以在相应目录下就可以看到归档日志了。

6、配置crontab自动执行备份脚本

crontab配置如下：

[oracle@rman-database script]$ crontab -e
30 23 * * 7 /u01/backup/script/weekends.sh
30 23 * * 1-6 /u01/backup/script/workday.sh
~
~

周日晚23：00执行level0备份，周1-6每天晚上23：00执行level1增量备份。

7、检查备份

以下备份是通过修改contab中配置的时间立即完成的。

RMAN> list backup;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1619    1.01M      DISK        00:00:00     17-DEC-14      
        BP Key: 1621   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T144642
        Piece Name: /u01/backup/backupset/arch_TESTDB_64_1_20141217

  List of Archived Logs in backup set 1619
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    31      1110399    17-DEC-14 1111912    17-DEC-14
  1    32      1111912    17-DEC-14 1112470    17-DEC-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1620    Incr 0  968.56M    DISK        00:00:06     17-DEC-14      
        BP Key: 1622   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T144643
        Piece Name: /u01/backup/backupset/df0_TESTDB_65_1_20141217
  List of Datafiles in backup set 1620
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 1112488    17-DEC-14 /u01/app/oracle/oradata/testdb/system01.dbf
  2    0  Incr 1112488    17-DEC-14 /u01/app/oracle/oradata/testdb/sysaux01.dbf
  3    0  Incr 1112488    17-DEC-14 /u01/app/oracle/oradata/testdb/undotbs01.dbf
  4    0  Incr 1112488    17-DEC-14 /u01/app/oracle/oradata/testdb/users01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1688    3.50K      DISK        00:00:00     17-DEC-14      
        BP Key: 1693   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T144651
        Piece Name: /u01/backup/backupset/arch_TESTDB_66_1_20141217

  List of Archived Logs in backup set 1688
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    33      1112470    17-DEC-14 1112496    17-DEC-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1754    Full    9.70M      DISK        00:00:00     17-DEC-14      
        BP Key: 1756   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T144654
        Piece Name: /u01/backup/backupset/ctl_TESTDB_c-2649780859-20141217-05
  SPFILE Included: Modification time: 17-DEC-14
  SPFILE db_unique_name: TESTDB
  Control File Included: Ckp SCN: 1112524      Ckp time: 17-DEC-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2010    1.83M      DISK        00:00:00     17-DEC-14      
        BP Key: 2014   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T152004
        Piece Name: /u01/backup/backupset/arch_TESTDB_68_1_20141217

  List of Archived Logs in backup set 2010
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    34      1112496    17-DEC-14 1114155    17-DEC-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2011    Incr 1  4.17M      DISK        00:00:01     17-DEC-14      
        BP Key: 2015   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T152005
        Piece Name: /u01/backup/backupset/df1_TESTDB_69_1_20141217
  List of Datafiles in backup set 2011
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 1114173    17-DEC-14 /u01/app/oracle/oradata/testdb/system01.dbf
  2    1  Incr 1114173    17-DEC-14 /u01/app/oracle/oradata/testdb/sysaux01.dbf
  3    1  Incr 1114173    17-DEC-14 /u01/app/oracle/oradata/testdb/undotbs01.dbf
  4    1  Incr 1114173    17-DEC-14 /u01/app/oracle/oradata/testdb/users01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2087    3.00K      DISK        00:00:00     17-DEC-14      
        BP Key: 2092   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T152008
        Piece Name: /u01/backup/backupset/arch_TESTDB_70_1_20141217

  List of Archived Logs in backup set 2087
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    35      1114155    17-DEC-14 1114179    17-DEC-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2160    Full    9.70M      DISK        00:00:00     17-DEC-14      
        BP Key: 2162   Status: AVAILABLE  Compressed: NO  Tag: TAG20141217T152010
        Piece Name: /u01/backup/backupset/ctl_TESTDB_c-2649780859-20141217-06
  SPFILE Included: Modification time: 17-DEC-14
  SPFILE db_unique_name: TESTDB
  Control File Included: Ckp SCN: 1114207      Ckp time: 17-DEC-14
 
以上备份是通过修改contab中配置的时间立即完成的。
可以看到每次备份（lv0或lv1）包含4个备份集，开始备份之前首先归档当前日志并且备份归档，然后备份数据文件，然后再一次备份归档，最后自动备份控制文件（包含参数文件）。

8、验证备份文件有效性

验证lv0备份文件有效性：

RMAN> restore database validate;

Starting restore at 17-DEC-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/backup/backupset/df0_TESTDB_65_1_20141217
channel ORA_DISK_1: piece handle=/u01/backup/backupset/df0_TESTDB_65_1_20141217 tag=TAG20141217T144643
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
Finished restore at 17-DEC-14

验证lv1备份文件有效性：

RMAN> validate backupset 2011;

Starting validate at 17-DEC-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/backup/backupset/df1_TESTDB_69_1_20141217
channel ORA_DISK_1: piece handle=/u01/backup/backupset/df1_TESTDB_69_1_20141217 tag=TAG20141217T152005
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished validate at 17-DEC-14

此处指定备份集的编号。

验证归档日志备份文件有效性：

RMAN> restore archivelog all validate;

Starting restore at 17-DEC-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/backup/backupset/arch_TESTDB_64_1_20141217
channel ORA_DISK_1: piece handle=/u01/backup/backupset/arch_TESTDB_64_1_20141217 tag=TAG20141217T144642
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/backup/backupset/arch_TESTDB_66_1_20141217
channel ORA_DISK_1: piece handle=/u01/backup/backupset/arch_TESTDB_66_1_20141217 tag=TAG20141217T144651
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/backup/backupset/arch_TESTDB_68_1_20141217
channel ORA_DISK_1: piece handle=/u01/backup/backupset/arch_TESTDB_68_1_20141217 tag=TAG20141217T152004
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/backup/backupset/arch_TESTDB_70_1_20141217
channel ORA_DISK_1: piece handle=/u01/backup/backupset/arch_TESTDB_70_1_20141217 tag=TAG20141217T152008
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 17-DEC-14


9、Windows下的脚本

周日全备的level0.rman脚本内容如下：

run{
    allocate channel c1 type disk;
    backup as backupset incremental level 0 database format '/u01/backup/backupset/df0_%d_%s_%p_%T' plus archivelog delete all input format '/u01/backup/backupset/arch_%d_%s_%p_%T';
    delete obsolete;
    release channel c1;
    }
exit

工作日增量备份的level1.rman脚本内容如下：

run{
    allocate channel c1 type disk;
    backup as backupset incremental level 1 database format '/u01/backup/backupset/df1_%d_%s_%p_%T' plus archivelog delete all input format '/u01/backup/backupset/arch_%d_%s_%p_%T';
    release channel c1;
    }
exit

bat文件格式可能如下：

rman target / nocatalog CMDFILE 'D:\level1.rman' LOG 'D:\rman_backup_%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%".log'

最后将bat文件加入到计划任务即可。

另外，在windows下要使用共享驱动器的方法来远程备份，需要执行如下步骤：

需要修改监听及数据库服务的服务登录属性（打开服务管理界面，右键服务名，点击登录），为administrator，并输入密码。

脚本以及rman配置中的路径都要使用绝对路径如：\\172.19.4.4\d\dbbackup\df0_%d_%s_%p_%T，而不能使用映射后的本地盘符。