基于catalog 的RMAN 备份与恢复

--=================================

-- 基于catalog 的RMAN 备份与恢复

--=================================

RMAN的备份与恢复存储仓库的数据通常存放于控制文件或恢复目录中，本文主要讲述基于catalog的备份与恢复。

关于catalog的创建请参考：RMAN catalog的创建和使用

catalog方式的RMAN备份与恢复只不过是将备份恢复信息数据放在catalog目录内，普通的rman方式则是存放在控制文件中

catalog方式可以存储常用或特定的备份与恢复的脚本

有关catalog方式RMAN存储脚本请参考：基于 catalog 创建RMAN存储脚本

一、基于catalog来备份数据库(目标数据库orcl，恢复目录数据库asmdb)

首先基于catalog创建备份脚本

--连接到RMAN

[oracle@oradb ~]$ rman target sys/redhat@orcl catalog rman/rman@asmdb

connected to target database: ORCL (DBID=1263182651)

connected to recovery catalog database

--创建全局删除废弃备份的脚本

RMAN> create global script global_del_obso comment 'A script for obsolete backup and delete it' {

2> allocate channel ch1 device type disk;

3> delete obsolete recovery window of 7 days;

4> release channel ch1;

5> }

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

created global script global_del_obso

--创建全局归档日志的备份脚本

RMAN> create global script global_arch

2> comment ' A script for archivelog and delete it '

3> {

4> allocate channel ch1 device type disk;

5> sql " alter system archive log current";

6> set limit channel ch1 readrate=10240;

7> set limit channel ch1 kbytes=2048000;

8> backup as compressed backupset archivelog all delete input

9> format='/u01/bk/rmbk/arch_%d_%U'

10> tag='Archbk';

11> release channel ch1;

12> }

created global script global_arch

--创建全局0级增量备份脚本

RMAN> create global script global_inc0

2> comment ' A script for backup database using incremental level 0'

3> {

4> execute global script global_del_obso;

5> allocate channel ch1 device type disk;

6> set limit channel ch1 readrate=10240;

7> set limit channel ch1 kbytes=2048000;

8> backup as compressed backupset incremental level 0 database

9> format='/u01/bk/rmbk/inc0_%d_%U'

10> tag='Inc0';

11> release channel ch1;

12> execute global script global_arch;

13> }

created global script global_inc0

--列出已经创建的全局脚本

RMAN> list global script names;

List of Stored Scripts in Recovery Catalog

Global Scripts

Script Name

Description

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

global_arch

A script for archivelog and delete it

global_del_obso

A script for obsolete backup and delete it

global_inc0

A script for backup database using incremental level 0

--启用控制文件的自动备份功能

RMAN> configure controlfile autobackup on;

RMAN> configure controlfile autobackup format for device type disk to '/u01/bk/rmbk/auto_ctl_%d_%f';

--删除以前的备份

RMAN> delete noprompt backupset;

--备份前验证归档日志是否存在

RMAN> crosscheck archivelog all;

--删除无效的归档日志信息

RMAN> delete noprompt expired archivelog all;

--使用0级增量备份数据库

RMAN> run { execute global script global_inc0;}

--查看刚刚完成的备份情况

RMAN> list backupset summary;

RMAN> list backupset ;

RMAN> list backup of controlfile ;

RMAN> list backup of archivelog all;

RMAN> list backup of database;

RMAN> list backup of datafile n ;

二、恢复操作

1.非系统表空间的恢复步骤

a. alter datafile n offline immediate; | alter tablespace tbs_name offline immediate;

b. restore

c. recover

d. alter datafile n online; | alter tablespace tbs_name online

--删除非系统表空间users的数据文件(数据库位于open 状态)

[oracle@oradb orcl]$ pwd

/u01/oracle/oradata/orcl

[oracle@oradb orcl]$ rm users01.dbf

lion@ORCL> select * from tb2;

select * from tb2

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/oracle/oradata/orcl/users01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

RMAN> run {

2> allocate channel ch1 device type disk;

3> sql " alter tablespace users offline immediate ";

4> restore tablespace users;

5> recover tablespace users;

6> sql " alter tablespace users online ";

7> }

lion@ORCL> select * from tb2;

ID NAME

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

1 Robinson

也可以使用下面的恢复方式来完成恢复

RMAN> run {

2> allocate channel ch1 device type disk;

3> sql " alter database datafile 4 offline ";

4> restore datafile 4;

5> recover datafile 4;

6> sql " alter database datafile 4 online ";

7> }

如果介质恢复时，需要用的日志尚未归档，需要指定联机重做日志文件所在的位置

注意

表空间几种不同的脱机方式

offline normal :

表空间脱机的缺省方式，将位于SGA中且与该表空间所有的数据文件相关的数据块(blocks)写入到数据文件之后再进行脱机

再次联机时不需要做介质恢复。

offline temporary :

实施检查点进程，即同样将SGA中且与该表空间所有的数据文件相关的数据块(blocks)写入到数据文件之后再进行脱机

不保证所有的数据能够写入到数据文件。再次联机时要做介质恢复。

offline immediate :

不保证表空间的数据可用，也不实施检查点进程而直接脱机，再次联机时要做介质恢复。

offline temporary ，offline immediate 脱机方式不适用于数据文件

lion@ORCL> alter database datfile 4 offline immediate;

alter database datfile 4 offline immediate

*

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

lion@ORCL> alter database datfile 4 offline temporary;

alter database datfile 4 offline temporary

*

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABAS

更多关于表空间与数据文件的管理，请参考：Oracle 表空间与数据文件

对于表空间存在多个数据文件的情况,而单个或较少的数据文件受损,应尽可能使用第二种方式来恢复.即resotre & recover datafile n

2.UNDO表空间的恢复

关于UNDO表空间的管理请参考：Oracle 回滚(ROLLBACK)和撤销(UNDO)

恢复步骤(undo丢失后)

alter database datafile 2 online;

alter database datafile 2 offline;

select * from v$recover_file;

restore datafile 2; --用RMAN 完成

recover datafile 2 ;

alter database datafile 2 online;

--首先做一些操作，将数据填充到undo表空间

lion@ORCL> select * from tb2;

ID NAME

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

1 Jack

lion@ORCL> insert into tb2 select 2,'Jackson' from dual;

lion@ORCL> commit;

lion@ORCL> delete from tb2 where id=1;

lion@ORCL> select * from tb2;

ID NAME

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

2 Jackson

lion@ORCL> ho rm $ORACLE_BASE/oradata/orcl/undotbs01.dbf

注意：undo表空间不能被offline,也不能被readonly

使用alter database datafile 2 online | offline强制执行该步骤,以告知oracle undo表空间被损坏,执行后会话被终止,如下

lion@ORCL> alter database datafile 2 online;

alter database datafile 2 online

*

ERROR at line 1:

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/u01/oracle/oradata/orcl/undotbs01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

lion@ORCL> alter database datafile 2 offline;

ERROR:

ORA-03114: not connected to ORACLE

alter database datafile 2 offline

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

--接下面再来进行恢复

RMAN> run {

2> allocate channel ch1 device type disk;

3> restore datafile 2;

4> recover datafile 2;}

starting media recovery --还原被成功执行,介质恢复失败，且会话被终止

media recovery failed

ORA-00603: ORACLE server session terminated by fatal error

--重新登录到数据库服务器并查看v$recover_file视图以及进行介质恢复

sys@ORCL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

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

2 OFFLINE OFFLINE UNKNOWN ERROR 493982 23-NOV-10

sys@ORCL> recover datafile 2; --进行介质恢复，恢复后可以看到提示回话被终止

ORA-00603: ORACLE server session terminated by fatal error

sys@ORCL> conn / as sysdba --再次连接

Connected.

sys@ORCL> select name,status from v$datafile where name like '%undo%'; --undo表空间已经处于offline状态

NAME STATUS

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

/u01/oracle/oradata/orcl/undotbs01.dbf OFFLINE

sys@ORCL> alter database datafile 2 online;

Database altered.

sys@ORCL> select * from lion.tb2;

ID NAME

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

2 Jackson

可以看到表中ID为的记录已经丢失,事实上在undo的数据文件丢失前,该事务并没有提交,由此可以推断，该事务进行了隐式提交.

3.系统表空间的恢复(system ,sysaux)

系统表空间只能在Mount状态下来完成恢复，步骤如下

startup mount;

restore datafile 1;

recover datafile 1;

alter database open;

--创建新表tb3，并插入记录。其数据字典信息则位于system表空间，数据内容位于users表空间

lion@ORCL> create table tb3 tablespace users as select * from tb2;

lion@ORCL> insert into tb3 select 1,'Johnson' from dual;

lion@ORCL> commit;

lion@ORCL> select * from tb3 order by id;

ID NAME

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

1 Johnson

2 Jackson

--删除表空间system01.dbf， sysaux01.dbf

sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/system01.dbf

sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/sysaux01.dbf

sys@ORCL> startup mount force;

RMAN> run {

2> allocate channel ch1 device type disk;

3> restore datafile 1,3;

4> recover datafile 1,3;

5> alter database open;

6> release channel ch1;}

sys@ORCL> select * from lion.tb3 order by id; --成功恢复后表tb3也被恢复

ID NAME

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

1 Johnson

2 Jackson

4.控制文件的恢复

步骤

connect to target db and catalog(nocatalog) db

startup nomount

restore controlfile [from autobackup]

alter database mount

recover database

alter database open resetlogs

由于控制文件采取了自动备份策略，因此在每次备份或重大系统结果发生变化时，控制文件将被自动备份

sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/*.ctl --删除所有的控制文件*/

sys@ORCL> select file#,status from v$datafile; --查看v$datafile视图时，系统已检测到错误发生

select file#,status from v$datafile

*

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/oracle/oradata/orcl/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

sys@ORCL> shutdown abort;

--重新连接到RMAN，注意连接target时使用/,否则提示TNS无法解析

[oracle@oradb dbs]$ uniread rman target / catalog rman/rman@asmdb

connected to target database: orcl (not started)

connected to recovery catalog database

RMAN> startup nomount;

RMAN> run {

2> allocate channel ch1 device type disk;

3> restore controlfile;

4> sql " alter database mount ";

5> recover database;

6> sql " alter database open resetlogs ";

7> release channel ch1;}

注：在此处有可能不要介质恢复，如果提示需要介质恢复，直接在RMAN或SQLPlus下执行recover database即可

使用open resetlogs之后，一个新的incarnation将被生成，再此建议立即全备数据库。

更多关于控制文件

Oracle 控制文件(CONTROLFILE)

Oracle 控制文件的备份与恢复

5.联机重做日志文件的恢复(online redo log )

当数据库置为mount状态，且将要转换为open状态时，数据文件，联机日志文件被打开，因此联机日志的丢失可以在mount状态完成

恢复步骤

a. 启动到mount状态(startup mount force)

b. 还原数据库(restore database)

c. 恢复数据库(recover database)

下面对删除日志并进行恢复

lion@ORCL> select * from tb2;

ID NAME

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

2 Jackson

lion@ORCL> select current_scn from v$database; --查看数据库当前的SCN

CURRENT_SCN

-----------

1020638

lion@ORCL> insert into tb2 select 1,'Johnson' from dual; --为表tb2新增一条记录

lion@ORCL> commit;

lion@ORCL> select current_scn from v$database; --数据库当前的SCN发生了变化为

CURRENT_SCN

-----------

1020685

lion@ORCL> select file#, checkpoint_change# from v$datafile_header; --数据文件头部的checkpoint_change

FILE# CHECKPOINT_CHANGE#

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

1 1020368

2 1020368

3 1020368

4 1020368

5 1020368

6 1020368

lion@ORCL> ho rm -f $ORACLE_BASE/oradata/orcl/*.log --删除所有的日志文件 */

lion@ORCL> insert into tb2 select 2,'wilson' from dual; --为表插入新记录

lion@ORCL> commit;

lion@ORCL> select current_scn from v$database; --数据库当前的SCN发生了变化为

CURRENT_SCN

-----------

1020708

lion@ORCL> alter system archive log current; --对日志进行归档时提示错误发生

alter system archive log current

*

ERROR at line 1:

ORA-16038: log 1 sequence# 1 cannot be archived

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/orcl/redo01.log'

lion@ORCL> conn / as sysdba

sys@ORCL> startup mount force;

[oracle@oradb ~]$ uniread rman target / catalog rman/rman@asmdb --退出RMAN后并重新连接

RMAN> run {

2> allocate channel ch1 device type disk;

3> restore database;

4> recover database;

5> release channel ch1;}

RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 1020365

sys@ORCL> recover database until cancel; --回到SQLPlus直接使用until cancel来进行恢复

sys@ORCL> alter database open resetlogs; --执行opensetlogs打开数据库

sys@ORCL> select * from lion.tb2; --在日志未完成自动归档前，删除日志的后数据全部丢失

ID NAME

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

2 Jackson

关于单个日志文件丢失或日志文件组受损，请参考：Oracle 联机重做日志文件(ONLINE LOG FILE)

6.所有数据全部丢失的处理

步骤

a.启动到nomount状态(startup nomount)

b.还原控制文件(restore controlfile from autobackup)

c.还原数据(restore database)

d.将数据切换到mount状态(alter database mount)

e.恢复数据库(recover database using backup controlfile until cancel)

f.使用open resetlogs打开数据库(alter database open resetlogs)

--下面演示数据文件、日志文件、控制文件全部丢失的处理

sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/* --删除所有的数据文件、日志文件、控制文件*/

sys@ORCL> startup nomount ;

[oracle@oradb ~]$ uniread rman target / catalog rman/rman@asmdb

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> run {

2> allocate channel ch1 device type disk;

3> restore database ;

4> sql " alter database mount ";

5> recover database ;}

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target database incarnation not found in

recovery catalog

idle> recover database using backup controlfile until cancel; --使用SQLPlus来完成恢复操作

idle> alter database open resetlogs;

三、更多参考

有关基于用户管理的备份和备份恢复的概念请参考：

Oracle 冷备份

Oracle 热备份

Oracle 备份恢复概念

Oracle 实例恢复

Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考：

RMAN 概述及其体系结构

RMAN 配置、监控与管理

RMAN 备份详解

RMAN 还原与恢复

有关Oracle体系结构请参考：

Oracle 实例和Oracle数据库(Oracle体系结构)

Oracle 表空间与数据文件

Oracle 密码文件

Oracle 参数文件

Oracle 数据库实例启动关闭过程

Oracle 联机重做日志文件(ONLINE LOG FILE)

Oracle 控制文件(CONTROLFILE)

Oracle 归档日志