本文介绍如何使用RMAN基于catalog的方式进行数据库备份与恢复，包括创建备份脚本、执行备份及不同场景下的恢复操作。
--=================================
-- 基于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 控制文件的备份与恢复
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 基于用户管理恢复的处理(详细描述了介质恢复及其处理)
有关RMAN的恢复与管理请参考：
有关Oracle体系结构请参考：
Oracle 实例和Oracle数据库(Oracle体系结构)
Oracle 联机重做日志文件(ONLINE LOG FILE)
扫一扫
3210
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
