RMAN 备份与恢复深入解析(二)

时间:2022-06-13 14:37:30

RMAN 备份与恢复深入解析(一)  http://space.itpub.net/26686207/viewspace-760869

更多精彩内容尽在 www.leonarding.com

RMAN备份与恢复深入解析》

2.用示例说明两种增量备份的差别

Incremental增量备份:中心思想就是减少备份的数据量,我们不需要在从头开始备份了,只需要备份自上次已备份之后的数据块即可。

关于Incremental增量备份级别:

Oracle 9i  共有五种级别 0 1 2 3 4,0级最高-4级最低,0级是1级的基础以此类推。

Oracle 10g官方文档明确指出增量备份只有0和1两种级别(太多增量级别其实没有太大的意义),不过实际执行增量操作时,仍然能够指定多个级别,最大能够支持4级增量备份。

Oracle 11g 增量备份只有0和1两种级别。

Level 0级就是对数据库一个全库备份,增量备份必须从0级开始,也就是说必须要有一个全库备份当基础。

如果你是用“backup full database”命令做全库备份oracle也不认为这是level 0的全库备份,尽管是一样的也要单独做一次level 0。有了level 0当基础才能有后面的level 1 level 2 level 3 level 4。如果你一上来比较冲动直接发出level 1备份,oracle会自动在level1前面加一个level 0,顺序是先进行level 0备份再做level 1备份,一共做两次备份。

关于Incremental增量备份类型:

(1)差异增量备份:备份上级及同级备份以来所有变化的数据块,差异增量是默认增量备份方式

特点:触发条件小于等于当前级别<=N     备份时间快,恢复时间慢

RMAN 备份与恢复深入解析(二)

(2)累积增量备份:备份上级备份以来所有变化的块。

特点:小于当前级别<N       备份时间慢,恢复时间快    C:代表累积

RMAN 备份与恢复深入解析(二)

我们系统的备份策略(周策略),仅供参考,如有雷同心心相印,欧巴~阿加西
日期               差异增量          累积增量
星期天                 0                      0
星期一                 2                      2
星期二                 2                      2
星期三                 1                      1
星期四                 2                      2
星期五                 2                      2
星期六                 2                      2
星期日                 0                      0

下面我们实验差异增量备份与累积增量备份~备份时间与恢复窗口优略性

差异增量备份

零级备份

backupincremental level 0 database;

创建表空间test1

创建表test1

一级差异备份

backupincremental level 1 database;

创建表空间test2

创建表test2

一级差异备份

backupincremental level 1 database;

记住当前的scn号(10000),我们删除表test1和表test2,然后恢复到scn=10000那一刻,检查表test1和表test2是否完整恢复回来,恢复窗口:恢复三个备份集 0+1+1+redo

begin go

0级全库备份

RMAN> backup incremental level 0 database;

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 0 datafile backup set  数据文件列表

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafile file number=00002name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafile file number=00003name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafile file number=00005name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafile file number=00004name=/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 10-MAY-13

channel ORA_DISK_1: finished piece 1 at 10-MAY-13

piece handle= /u02/app/oracle/backup/DB_0lo99q5p_1_1 tag=TAG20130510T150112 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

Finished backup at 10-MAY-13

控制文件自动备份

Starting Control File and SPFILE Autobackup at 10-MAY-13

piece handle= /u02/app/oracle/backup/control/cf_c-1692458681-20130510-00 comment=NONE

Finished Control File and SPFILE Autobackup at 10-MAY-13

操作系统侧面检查,顺利备份没有问题 good

[oracle@leonarding1 backup]$ ll

total 983928

drwxr-xr-x 2 oracle oinstall      4096 May 10 15:02 control

-rw-r----- 1 oracle asmadmin 1007534080 May 10 15:02 DB_0lo99q5p_1_1

[oracle@leonarding1 control]$ ll

total 9632

-rw-r----- 1 oracle asmadmin 9863168 May 10 15:02cf_c-1692458681-20130510-00

创建test1表空间

SYS@LEO1>create tablespace test1 datafile'/u02/app/oracle/oradata/LEO1/test1_01.dbf' size 20m autoextend off;

Tablespace created.

创建test1表

SYS@LEO1>conn leo1/leo1

Connected.

LEO1@LEO1>create table test1 tablespace test1 as select * fromdba_objects;

Table created.

LEO1@LEO1>select checkpoint_change# from v$database;       每做完一个动作我们都记录下SCN号

CHECKPOINT_CHANGE#

------------------

1079203

1级差异备份

RMAN> backup incremental level 1 database;

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafile file number=00002name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafile file number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafile file number=00005name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafile file number=00006name=/u02/app/oracle/oradata/LEO1/test1_01.dbf       这次多了test1表空间

input datafile file number=00004name=/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 10-MAY-13

channel ORA_DISK_1: finished piece 1 at 10-MAY-13

piece handle=/u02/app/oracle/backup/DB_0no99r4r_1_1 tag=TAG20130510T151746comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15

Finished backup at 10-MAY-13

Starting Control File and SPFILE Autobackup at 10-MAY-13

piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130510-01comment=NONE

Finished Control File and SPFILE Autobackup at 10-MAY-13

创建test2表空间

LEO1@LEO1>create tablespace test2 datafile'/u02/app/oracle/oradata/LEO1/test2_01.dbf' size 20m autoextend off;

Tablespace created.

创建test2表

LEO1@LEO1>create table test2 tablespace test2 as select * fromdba_objects;

Table created.

LEO1@LEO1>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

1079213

再次1级差异备份

RMAN> backup incremental level 1 database;

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafile file number=00002name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafile file number=00003name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafile file number=00005name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafile file number=00006name=/u02/app/oracle/oradata/LEO1/test1_01.dbf

input datafile file number=00007 name=/u02/app/oracle/oradata/LEO1/test2_01.dbf    这次又多了test1表空间

input datafile file number=00004name=/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 10-MAY-13

channel ORA_DISK_1: finished piece 1 at 10-MAY-13

piece handle=/u02/app/oracle/backup/DB_0qo99s79_1_1 tag=TAG20130510T153609comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05

Finished backup at 10-MAY-13

Starting Control File and SPFILE Autobackup at 10-MAY-13

piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130510-03comment=NONE

Finished Control File and SPFILE Autobackup at 10-MAY-13

SYS@LEO1>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

1084254

SYS@LEO1>alter system switch logfile;         我们做一次日志切换从2 -> 3

System altered.

SYS@LEO1>archive log list

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /u02/app/oracle/archdata

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

SYS@LEO1>alter system switch logfile;         为了保险我们再切一次,当前日志序号为4

System altered.

SYS@LEO1>conn leo1/leo1

Connected.

LEO1@LEO1>drop table test1 purge;             我们删除test1和test2表

Table dropped.

LEO1@LEO1>drop table test2 purge;

Table dropped.

现在我们想恢复到没删除之前的状态,有2种方法(1)恢复到scn=1084254 (2)恢复到sequence=3

检查一下备份集集

RMAN> list backupset;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

19      Incr 0  960.85M   DISK        00:01:21     10-MAY-13

BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T150112

Piece Name:/u02/app/oracle/backup/DB_0lo99q5p_1_1

List of Datafiles in backup set 19

File LV Type Ckp SCN    Ckp Time Name

---- -- ---- ---------- -------------

1    0 Incr 1081294    10-MAY-13/u02/app/oracle/oradata/LEO1/system01.dbf

2    0 Incr 1081294    10-MAY-13/u02/app/oracle/oradata/LEO1/sysaux01.dbf

3    0 Incr 1081294    10-MAY-13/u02/app/oracle/oradata/LEO1/undotbs01.dbf

4    0 Incr 1081294    10-MAY-13/u02/app/oracle/oradata/LEO1/users01.dbf

5    0 Incr 1081294    10-MAY-13/u02/app/oracle/oradata/LEO1/leo1.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

20      Full    9.39M     DISK        00:00:01     10-MAY-13

BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T150238

Piece Name:/u02/app/oracle/backup/control/cf_c-1692458681-20130510-00

SPFILE Included: Modificationtime: 30-APR-13

SPFILE db_unique_name: LEO1

Control File Included: Ckp SCN:1081329      Ckp time: 10-MAY-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

21      Incr 1  19.38M    DISK        00:02:08     10-MAY-13

BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T151746

Piece Name:/u02/app/oracle/backup/DB_0no99r4r_1_1

List of Datafiles in backup set 21

File LV Type Ckp SCN    Ckp Time Name

---- -- ---- ---------- -------------

1    1 Incr 1083510    10-MAY-13/u02/app/oracle/oradata/LEO1/system01.dbf

2    1 Incr 1083510    10-MAY-13/u02/app/oracle/oradata/LEO1/sysaux01.dbf

3    1 Incr 1083510    10-MAY-13/u02/app/oracle/oradata/LEO1/undotbs01.dbf

4    1 Incr 1083510    10-MAY-13/u02/app/oracle/oradata/LEO1/users01.dbf

5    1 Incr 1083510    10-MAY-13/u02/app/oracle/oradata/LEO1/leo1.dbf

6    1 Incr 1083510    10-MAY-13/u02/app/oracle/oradata/LEO1/test1_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

22      Full    9.39M     DISK        00:00:01     10-MAY-13

BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T152002

Piece Name: /u02/app/oracle/backup/control/cf_c-1692458681-20130510-01

SPFILE Included: Modificationtime: 10-MAY-13

SPFILE db_unique_name: LEO1

Control File Included: Ckp SCN:1083563      Ckp time: 10-MAY-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

23      Full    9.39M     DISK        00:00:02     10-MAY-13

BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T152849

Piece Name: /u02/app/oracle/backup/control/cf_c-1692458681-20130510-02

SPFILE Included: Modificationtime: 10-MAY-13

SPFILE db_unique_name: LEO1

Control File Included: Ckp SCN:1084027      Ckp time: 10-MAY-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

24      Incr 1  9.90M     DISK        00:02:00     10-MAY-13

BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T153609

Piece Name:/u02/app/oracle/backup/DB_0qo99s79_1_1

List of Datafiles in backup set 24

File LV Type Ckp SCN    Ckp Time Name

---- -- ---- ---------- -------------

1    1 Incr 1084375    10-MAY-13/u02/app/oracle/oradata/LEO1/system01.dbf

2    1 Incr 1084375    10-MAY-13 /u02/app/oracle/oradata/LEO1/sysaux01.dbf

3    1 Incr 1084375    10-MAY-13/u02/app/oracle/oradata/LEO1/undotbs01.dbf

4    1 Incr 1084375    10-MAY-13/u02/app/oracle/oradata/LEO1/users01.dbf

5    1 Incr 1084375    10-MAY-13/u02/app/oracle/oradata/LEO1/leo1.dbf

6    1 Incr 1084375    10-MAY-13/u02/app/oracle/oradata/LEO1/test1_01.dbf

7    1 Incr 1084375    10-MAY-13/u02/app/oracle/oradata/LEO1/test2_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

25      Full    9.39M     DISK        00:00:01     10-MAY-13

BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T153814

Piece Name:/u02/app/oracle/backup/control/cf_c-1692458681-20130510-03

SPFILE Included: Modificationtime: 10-MAY-13

SPFILE db_unique_name: LEO1

Control File Included: Ckp SCN:1084436      Ckp time: 10-MAY-13

从这个size大小上就可以看出 960.85M->19.38M->9.90M咱们做的都是增量备份,那么好现在开始恢复

SYS@LEO1>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@LEO1>startup mount                                启动mount状态恢复

ORACLE instance started.

Total System Global Area  471830528bytes

Fixed Size                  2214456bytes

Variable Size             184550856 bytes

Database Buffers          276824064bytes

Redo Buffers                8241152bytes

Database mounted.

RMAN  进行基于SCN号不完全恢复

RMAN> restore database until scn 1084254;

Starting restore at 10-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

creating datafile file number=7name=/u02/app/oracle/oradata/LEO1/test2_01.dbf

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to/u02/app/oracle/oradata/LEO1/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to/u02/app/oracle/oradata/LEO1/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to/u02/app/oracle/oradata/LEO1/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to/u02/app/oracle/oradata/LEO1/leo1.dbf

channel ORA_DISK_1: reading from backup piece /u02/app/oracle/backup/DB_0lo99q5p_1_1    从0级备份开始恢复

channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_0lo99q5p_1_1tag=TAG20130510T150112

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:45

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00006 to/u02/app/oracle/oradata/LEO1/test1_01.dbf

channel ORA_DISK_1: reading from backup piece /u02/app/oracle/backup/DB_0no99r4r_1_1    再恢复1级

channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_0no99r4r_1_1tag=TAG20130510T151746

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 10-MAY-13

RMAN> recover database until scn 1084254;                    同步scn号

Starting recover at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001:/u02/app/oracle/oradata/LEO1/system01.dbf

destination for restore of datafile 00002:/u02/app/oracle/oradata/LEO1/sysaux01.dbf

destination for restore of datafile 00003:/u02/app/oracle/oradata/LEO1/undotbs01.dbf

destination for restore of datafile 00004:/u02/app/oracle/oradata/LEO1/users01.dbf

destination for restore of datafile 00005:/u02/app/oracle/oradata/LEO1/leo1.dbf

channel ORA_DISK_1: reading from backup piece/u02/app/oracle/backup/DB_0no99r4r_1_1

channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_0no99r4r_1_1tag=TAG20130510T151746

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file/u02/app/oracle/archdata/1_1_814107939.dbf

archived log file name=/u02/app/oracle/archdata/1_1_814107939.dbf thread=1sequence=1

media recovery complete, elapsed time: 00:00:02

Finished recover at 10-MAY-13

RMAN> alter database open resetlogs;         非一致性打开

database opened

累积增量备份

零级备份

backup incremental level 0 cumulative database;

创建表空间test3

创建表test3

一级累积备份

backup incremental level 1 cumulative database;

创建表空间test4

创建表test4

一级累积备份

backup incremental level 1 cumulative database;

记住当前的sequence号,我们删除表test3和表test4,然后恢复到sequence那一刻,检查表test3和表test4是否完整恢复回来,恢复窗口:恢复两个备份集 0+1(最后一次备份)+redo

下面我们就做的快一点啦

RMAN> backup incremental level 0 cumulative database;                 0级累积增量备份

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafile file number=00002name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafile file number=00003name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafile file number=00005name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafile file number=00006name=/u02/app/oracle/oradata/LEO1/test1_01.dbf

input datafile file number=00007name=/u02/app/oracle/oradata/LEO1/test2_01.dbf

input datafile file number=00004name=/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 10-MAY-13

channel ORA_DISK_1: finished piece 1 at 10-MAY-13

piece handle=/u02/app/oracle/backup/DB_0to99v4v_1_1 tag=TAG20130510T162607comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

Finished backup at 10-MAY-13

Starting Control File and SPFILE Autobackup at 10-MAY-13

piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130510-05comment=NONE

Finished Control File and SPFILE Autobackup at 10-MAY-13

创建表空间和表test3

LEO1@LEO1>create tablespace test3 datafile'/u02/app/oracle/oradata/LEO1/test3_01.dbf' size 20m autoextend off;

Tablespace created.

LEO1@LEO1>create table test3 tablespace test3 as select * fromdba_objects;

Table created.

1级累积备份

RMAN> backup incremental level 1 cumulative database;

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafile file number=00002name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafile file number=00003name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafile file number=00005name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafile file number=00006name=/u02/app/oracle/oradata/LEO1/test1_01.dbf

input datafile file number=00007name=/u02/app/oracle/oradata/LEO1/test2_01.dbf

input datafile file number=00008name=/u02/app/oracle/oradata/LEO1/test3_01.dbf

input datafile file number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 10-MAY-13

channel ORA_DISK_1: finished piece 1 at 10-MAY-13

piece handle= /u02/app/oracle/backup/DB_0vo99vgi_1_1 tag=TAG20130510T163217 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05

Finished backup at 10-MAY-13

Starting Control File and SPFILE Autobackup at 10-MAY-13

piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130510-06comment=NONE

Finished Control File and SPFILE Autobackup at 10-MAY-13

创建表空间和表test4

LEO1@LEO1>create tablespace test4 datafile'/u02/app/oracle/oradata/LEO1/test4_01.dbf' size 20m autoextend off;

Tablespace created.

LEO1@LEO1>create table test4 tablespace test4 as select * fromdba_objects;

Table created.

再一次1级累积备份

RMAN> backup incremental level 1 cumulative database;

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafile file number=00002name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafile file number=00003name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafile file number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafile file number=00006name=/u02/app/oracle/oradata/LEO1/test1_01.dbf

input datafile file number=00007name=/u02/app/oracle/oradata/LEO1/test2_01.dbf

input datafile file number=00008 name=/u02/app/oracle/oradata/LEO1/test3_01.dbf

input datafile file number=00009name=/u02/app/oracle/oradata/LEO1/test4_01.dbf

input datafile file number=00004name=/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 10-MAY-13

channel ORA_DISK_1: finished piece 1 at 10-MAY-13

piece handle= /u02/app/oracle/backup/DB_11o99vpa_1_1 tag=TAG20130510T163657 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55

Finished backup at 10-MAY-13

Starting Control File and SPFILE Autobackup at 10-MAY-13

piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130510-07comment=NONE

Finished Control File and SPFILE Autobackup at 10-MAY-13

SYS@LEO1>drop table test3 purge;

SYS@LEO1>drop table test4 purge;

切换2次日志

SYS@LEO1>alter system switch logfile;

System altered.

SYS@LEO1>alter system switch logfile;

System altered.

SYS@LEO1>archive log list

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /u02/app/oracle/archdata

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

我们只需恢复到sequence=4即可

RMAN  进行基于sequence号不完全恢复

SYS@LEO1>shutdown immediate

SYS@LEO1>startup mount

RMAN> restore database until sequence 4 thread 1;                一直恢复到4号归档日志

Starting restore at 10-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to/u02/app/oracle/oradata/LEO1/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to/u02/app/oracle/oradata/LEO1/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to/u02/app/oracle/oradata/LEO1/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to/u02/app/oracle/oradata/LEO1/leo1.dbf

channel ORA_DISK_1: restoring datafile 00006 to/u02/app/oracle/oradata/LEO1/test1_01.dbf

channel ORA_DISK_1: restoring datafile 00007 to/u02/app/oracle/oradata/LEO1/test2_01.dbf

channel ORA_DISK_1: reading from backup piece/u02/app/oracle/backup/DB_0to99v4v_1_1

channel ORA_DISK_1: piece handle= /u02/app/oracle/backup/DB_0to99v4v_1_1 tag=TAG20130510T162607

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:05

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00008 to/u02/app/oracle/oradata/LEO1/test3_01.dbf

channel ORA_DISK_1: restoring datafile 00009 to/u02/app/oracle/oradata/LEO1/test4_01.dbf

channel ORA_DISK_1: reading from backup piece/u02/app/oracle/backup/DB_11o99vpa_1_1

channel ORA_DISK_1: piece handle= /u02/app/oracle/backup/DB_11o99vpa_1_1 tag=TAG20130510T163657

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 10-MAY-13

RMAN> recover database until sequence 4 thread 1;

Starting recover at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001:/u02/app/oracle/oradata/LEO1/system01.dbf

destination for restore of datafile 00002:/u02/app/oracle/oradata/LEO1/sysaux01.dbf

destination for restore of datafile 00003: /u02/app/oracle/oradata/LEO1/undotbs01.dbf

destination for restore of datafile 00004:/u02/app/oracle/oradata/LEO1/users01.dbf

destination for restore of datafile 00005:/u02/app/oracle/oradata/LEO1/leo1.dbf

destination for restore of datafile 00006: /u02/app/oracle/oradata/LEO1/test1_01.dbf

destination for restore of datafile 00007:/u02/app/oracle/oradata/LEO1/test2_01.dbf

channel ORA_DISK_1: reading from backup piece/u02/app/oracle/backup/DB_11o99vpa_1_1

channel ORA_DISK_1: piece handle= /u02/app/oracle/backup/DB_11o99vpa_1_1 tag=TAG20130510T163657

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file/u02/app/oracle/archdata/1_3_815069267.dbf

archived log file name=/u02/app/oracle/archdata/1_3_815069267.dbf thread=1sequence=3

media recovery complete, elapsed time: 00:00:01

Finished recover at 10-MAY-13

RMAN> alter database open resetlogs;         打开数据库

database opened

LEO1@LEO1>select count(*) from test3;        完整恢复回来啦

COUNT(*)

----------

71894

LEO1@LEO1>select count(*) from test4;

COUNT(*)

----------

71895

大家肯定会发现一个有趣的现象,我们备份集顺序DB_0to99v4v_1_1->DB_0vo99vgi_1_1->DB_11o99vpa_1_1

-rw-r----- 1 oracle asmadmin 1023426560 May 10 16:27 DB_0to99v4v_1_1

-rw-r----- 1 oracle asmadmin  10493952 May 10 16:34 DB_0vo99vgi_1_1

-rw-r----- 1 oracle asmadmin  20193280 May 10 16:38 DB_11o99vpa_1_1

而我们恢复顺序DB_0to99v4v_1_1->DB_11o99vpa_1_1,没有中间的DB_0vo99vgi_1_1,这就是累积增量备份的特色,当进行备份时它要去找比它自己级别小的基准点进行增量备份,因此DB_11o99vpa_1_1是从0级一直备到最新状态(包括DB_0vo99vgi_1_1)又根据Oracle优先使用备份集恢复,用完所有备份集后,在应用归档日志恢复,因为备份集效率比归档快很多,这一原则,我们可以直接使用DB_11o99vpa_1_1备份集进行恢复了。

小结:情深深雨蒙蒙~风吹草低见牛羊,示例说明两种增量备份的差别实验已经完成,大家从这些实验中学习到东西了嘛!应该说这两种增量各有千秋,适合不同的应用场景,如何更好的使用它们,要根据你项目能够接受的备份及恢复程度而定,好了我们明天见goodbye 亲。

3.演示用catalog数据库作为RMAN的资料库对数据库进行一次全备份

上面我们说过RMAN元数据可以放在控制文件中,其实还可以放在catalog数据库中,catalog库是个什么东东呢?我们简单的阐述一下它个功能。

1.  catalog库统一管理RMAN备份信息即元数据信息,如果没有catalog库,就把RMAN备份信息写入控制文件

2.  目标数据库必须注册到catalog库中才能使用

3.  控制文件恢复成本越来越低,降低了catalog库使用。

4.  控制文件不能保留超过一年以上的备份信息,catalog可却可以。

5.  catalog库可以同时保存n台数据库的备份信息,降低管理成本,提升管理效率

参考官方文档

【参】Books->Backup and Recovery Reference -> CREATE CATALOG和REGISTER

【参】Books-> Backup and Recovery Advanced User’s Guide -> 10 Managing the RecoveryCatalog -> Creating a Recovery Catalog

1.创建表空间catalog1 用于存储rman备份信息,创建表空间代表【catalog库】

LEO1@LEO1>create tablespace catalog1 datafile'/u02/app/oracle/oradata/LEO1/catalog1_01.dbf' size 20m autoextend off;

Tablespace created.

2.创建用户cl_admin并指定默认的表空间catalog1

LEO1@LEO1>create user cl_admin identified by cl_admin defaulttablespace catalog1;

User created.

3.将recovery_catalog_owner角色授予给cl_admin用户,才能提供维护和查询恢复目录的权限

LEO1@LEO1>grant connect,resource,recovery_catalog_owner to cl_admin;

Grant succeeded.

LEO1@LEO1>select * from dba_roles where role like '%RECOVER%';

ROLE                          PASSWORD AUTHENTICAT

------------------------------ -------- -----------

RECOVERY_CATALOG_OWNER        NO       NONE

LEO1@LEO1>conn / as sysdba

Connected.

SYS@LEO1>select * from role_sys_privs whererole='RECOVERY_CATALOG_OWNER';    查看这个角色具有哪些系统权限->11个权限

ROLE                          PRIVILEGE                               ADM

------------------------------ -------------------------------------------

RECOVERY_CATALOG_OWNER        CREATE SYNONYM                          NO

RECOVERY_CATALOG_OWNER        CREATE CLUSTER                          NO

RECOVERY_CATALOG_OWNER         ALTERSESSION                            NO

RECOVERY_CATALOG_OWNER        CREATE DATABASE LINK                    NO

RECOVERY_CATALOG_OWNER        CREATE SESSION                          NO

RECOVERY_CATALOG_OWNER        CREATE TABLE                            NO

RECOVERY_CATALOG_OWNER        CREATE SEQUENCE                         NO

RECOVERY_CATALOG_OWNER         CREATEPROCEDURE                         NO

RECOVERY_CATALOG_OWNER        CREATE VIEW                             NO

RECOVERY_CATALOG_OWNER        CREATE TYPE                             NO

RECOVERY_CATALOG_OWNER        CREATE TRIGGER                           NO

11 rows selected.

4.进入rman创建恢复目录catalog库

[oracle@leonarding1 backup]$ rman catalog cl_admin/cl_admin             以恢复目录所有者身份登录rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 11 09:06:292013

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

connected to recovery catalog database                                 连接到恢复目录数据库

RMAN> create catalog tablespace catalog1;                    用catalog1表空间代表catalog库

recovery catalog created

5.target库注册到catalog库

[oracle@leonarding1 backup]$ rman target sys/oracle catalogcl_admin/cl_admin

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 11 09:15:102013

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

connected to target database: LEO1 (DBID=1692458681)  必须以SYS身份登录,不能以普通用户身份登录,否则不能注册

connected to recovery catalog database                同时也连接到了catalog库

RMAN> register database;                把target库->注册->catalog库,谁注册了谁的备份信息才能存到catalog库

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

6.在数据库中确认注册成功,看看哪些数据库注册到catalog库了

[oracle@leonarding1 ~]$ sqlplus cl_admin/cl_admin

CL_ADMIN@LEO1>col name for a6

CL_ADMIN@LEO1>select * from rc_database;         从这个视图上我们就可以看到leo1库已经注册到catalog中了

DB_KEY  DBINC_KEY       DBID NAME   RESETLOGS_CHANGE# RESETLOGS

---------- ---------- ---------- ------ ----------------- ---------

2          4 1692458681 LEO1             1086803 10-MAY-13

7.取消注册

RMAN> unregister database;                                          从恢复目录库中取消目标数据库的注册

8.全库备份

RMAN> backup as compressed backupset full database include currentcontrolfile plus archivelog delete all input;

Starting backup at 11-MAY-13

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=6 RECID=38 STAMP=814107533

input archived log thread=1 sequence=7 RECID=36 STAMP=814105467

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_15o9bs9s_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_6_814098124.dbf RECID=38STAMP=814107533

archived log file name=/u02/app/oracle/archdata/1_7_814098124.dbf RECID=36STAMP=814105467

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=39 STAMP=815067081

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_16o9bs9t_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_1_814107939.dbf RECID=39STAMP=815067081

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=8 RECID=37 STAMP=814105539

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_17o9bsa1_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_8_814098124.dbf RECID=37STAMP=814105539

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=2 RECID=40 STAMP=815067673

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_18o9bsa2_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_2_814107939.dbf RECID=40STAMP=815067673

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=43 STAMP=815070347

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_19o9bsa3_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_1_815069267.dbf RECID=43STAMP=815070347

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=41 STAMP=815067752

input archived log thread=1 sequence=4 RECID=42 STAMP=815069267

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1ao9bsa4_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_3_814107939.dbf RECID=41STAMP=815067752

archived log file name=/u02/app/oracle/archdata/1_4_814107939.dbf RECID=42STAMP=815069267

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=2 RECID=44 STAMP=815070349

input archived log thread=1 sequence=3 RECID=45 STAMP=815071289

input archived log thread=1 sequence=4 RECID=46 STAMP=815071321

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1bo9bsa5_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_2_815069267.dbf RECID=44STAMP=815070349

archived log file name=/u02/app/oracle/archdata/1_3_815069267.dbf RECID=45STAMP=815071289

archived log file name=/u02/app/oracle/archdata/1_4_815069267.dbf RECID=46STAMP=815071321

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=48 STAMP=815130519

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1co9bsa9_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_1_815071959.dbf RECID=48STAMP=815130519

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=47 STAMP=815071959

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1do9bsag_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_5_815069267.dbf RECID=47STAMP=815071959

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=2 RECID=49 STAMP=815132985

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1eo9bsai_1_1 tag=TAG20130511T094947comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_2_815071959.dbf RECID=49STAMP=815132985

Finished backup at 11-MAY-13

Starting backup at 11-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafile file number=00002name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafile file number=00003name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafile file number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafile file number=00006name=/u02/app/oracle/oradata/LEO1/test1_01.dbf

input datafile file number=00007name=/u02/app/oracle/oradata/LEO1/test2_01.dbf

input datafile file number=00008 name=/u02/app/oracle/oradata/LEO1/test3_01.dbf

input datafile file number=00009name=/u02/app/oracle/oradata/LEO1/test4_01.dbf

input datafile file number=00010name=/u02/app/oracle/oradata/LEO1/catalog1_01.dbf

input datafile file number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1fo9bsaj_1_1 tag=TAG20130511T095011comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1go9bsdi_1_1 tag=TAG20130511T095011comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 11-MAY-13

Starting backup at 11-MAY-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=50 STAMP=815133109

channel ORA_DISK_1: starting piece 1 at 11-MAY-13

channel ORA_DISK_1: finished piece 1 at 11-MAY-13

piece handle=/u02/app/oracle/backup/DB_1ho9bsdm_1_1 tag=TAG20130511T095150comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u02/app/oracle/archdata/1_3_815071959.dbf RECID=50STAMP=815133109

Finished backup at 11-MAY-13

Starting Control File and SPFILE Autobackup at 11-MAY-13

piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130511-01comment=NONE

Finished Control File and SPFILE Autobackup at 11-MAY-13

这些备份信息就会保存在catalog数据库中,你可以同时保存n台数据库的备份信息,请注意一点,catalog库也会有crash风险,如果这些备份信息丢失那么所有备份集就会失效,因此catalog库也需要备份。

小结:备份与恢复是数据库的一个核心模块,几乎没有见过无需备份的库,RMAN是oracle专用级备份与恢复工具,几乎可以完成所有的备份任务,是我们DBA的利器,因此花些时间学习RMAN是很有意义的。