使用RMAN 备份与恢复数据库

我的备份和恢复都是在归档模式下完成的，本文开头部分需要启停数据库，因此请不要在生产环境上操作，如果在生产环境上操作由此产生的任何问题与博主无关。

1.检查数据库是否为归档模式：

oracle@linux:~>sqlplus / as sysdba;

红框中所示为非归档模式，停止数据库，使用如下命令开启归档模式，并设置为自动归档：

SQL> alter database archivelog;

SQL> alter system archive log start;

2.连接RMAN

oracle@linux:~>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Sep 5 11:04:39 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ZXIN (DBID=1576697541)

RMAN>

3.检查是否已经有备份文件，注意，在非归档模式下备份的备件集在归档模式下无效。

RMAN> list backup of database;

检查是否有过期或无效的备份

4.最简单的备份：

重启数据库为mount模式
RMAN> shutdown immediate;

RMAN> startup mount;

RMAN> backup database;

5.检查刚刚的备份结果：

RMAN> list backup of database;

6.自定义备份，使用run脚本实现：

RMAN> run {
2> allocate channel d1 type disk;     //分配通道
3> backup full database                  //全库备份
4> include current controlfile           //备份控制文件
5>  format '/home/oracle/product/10.2.0/db_1/dbs/%d_%s_%p_%t_%T';  //指定备份路径及备份格式
6> release channel d1;    //释放通道
7> }  //run脚本结束

其中的变量说明：

%d 数据库名

%s 备份集号

%p 该备份集的片号（从1开始编号）

%t  备份集时间戳

%T 年月日格式(YYYYMMDD)

使用如下命令查看一下刚刚的备份结果：

RMAN> list backup;

BS Key  备份集关键字

Type 　备份类型 full为完整备份，incr为增量备份

LV        备份级别，如1级备份，0级备份

Size      备份大小

Device Type 设备类型 Disk为磁盘

Elapsed Time 消耗时间

Completion Time  完成时间

BP Key  备份片关键字

Status    备份状态 AVAILABLE代表可用

Compressed  是否压缩

Tag   标签
Piece Name  备份片物理路径

Ckp SCN Check Point SCN

Ckp time  Check Point 时间

7.删除一个数据文件，测试是否可以正常恢复：

关闭数据库再重新启动：

启动失败，找不到users01.dbf这个数据文件

使用rman恢复：

RMAN> restore database;
RMAN> recover database;

打开数据库：

一.模拟控制文件丢失：

数据库已经无法正常打开

使用RMAN恢复：

RMAN> set dbid 1576697541;

executing command: SET DBID

RMAN> restore controlfile to '/home/oracle/oradata/zxin/control01.ctl' from '/home/oracle/product/10.2.0/db_1/dbs/ZXIN_21_1_921756993_20160905';

Starting restore at 2016.09.05 13:41:06
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2016.09.05 13:41:09

打开数据库：

RMAN> alter database open resetlogs;

二.模拟数据文件丢失:

oracle@linux:~/data/oradata/zxin> rm -rf zxin_data.dbf
oracle@linux:~/data/oradata/zxin>

oracle@linux:~/data/oradata/zxin> sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 11:53:57 2016

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

此时尝试启动数据库，启动失败，提示找不到数据文件。

SQL> startup
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size                  2083848 bytes
Variable Size             385876984 bytes
Database Buffers          973078528 bytes
Redo Buffers               14692352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/data/oradata/zxin/zxin_data.dbf'

SQL>

SQL> select file#,error from v$recover_file;

     FILE# ERROR
---------- -----------------------------------------------------------------
         5 FILE NOT FOUND

SQL>

SQL> select file#,name from v$datafile where file#=5;

     FILE# NAME
---------- ----------------------------------------  
         5 /home/oracle/data/oradata/zxin/zxin_data.dbf

SQL>

然后我们使用RMAN 恢复:

oracle@linux:~> rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Oct 17 12:40:20 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ZXIN (DBID=1579813944, not open)

RMAN> run{
2> sql 'alter database datafile 5 offline';
3> sql 'alter database open';
4> restore datafile 5;
5> recover datafile 5;
6> sql 'alter database datafile 5 online';
7> }

using target database control file instead of recovery catalog
sql statement: alter database datafile 5 offline

sql statement: alter database open

Starting restore at 2016.10.17 12:42:28
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /home/oracle/data/oradata/zxin/zxin_data.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/product/10.2.0/db_1/dbs/ZXIN_4_1_925470454_20161017
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/product/10.2.0/db_1/dbs/ZXIN_4_1_925470454_20161017 tag=TAG20161017T110734
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 2016.10.17 12:42:45

Starting recover at 2016.10.17 12:42:45
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 15 is already on disk as file /home/oracle/product/10.2.0/db_1/dbs/arch1_15_924523320.dbf
archive log thread 1 sequence 16 is already on disk as file /home/oracle/product/10.2.0/db_1/dbs/arch1_16_924523320.dbf
archive log thread 1 sequence 17 is already on disk as file /home/oracle/product/10.2.0/db_1/dbs/arch1_17_924523320.dbf
archive log filename=/home/oracle/product/10.2.0/db_1/dbs/arch1_15_924523320.dbf thread=1 sequence=15
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016.10.17 12:42:45

sql statement: alter database datafile 5 online

RMAN>

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                                                       STATUS
---------- ----------------------------------------------------------------------------------- -----------
         1 /home/oracle/data/oradata/zxin/system01.dbf                             SYSTEM
         2 /home/oracle/data/oradata/zxin/undotbs01.dbf                           ONLINE
         3 /home/oracle/data/oradata/zxin/sysaux01.dbf                             ONLINE
         4 /home/oracle/data/oradata/zxin/users01.dbf                               ONLINE
         5 /home/oracle/data/oradata/zxin/zxin_data.dbf                             ONLINE

SQL>

恢复完成