基于catalog 的RMAN 备份与恢复

不念不忘少年蓝@ 2022-05-15 01:39 260阅读 0赞

\--=================================

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

\--=================================

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

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

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

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

有关catalog方式RMAN存储脚本请参考：[基于 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 表空间与数据文件][Oracle]

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

2.UNDO表空间的恢复

关于UNDO表空间的管理请参考：[Oracle 回滚(ROLLBACK)和撤销(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

使用alterdatabase 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 _CONTROLFILE]

Oracle 控制文件的备份与恢复

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

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

恢复步骤

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

b.还原数据库(restoredatabase)

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)][Oracle _ONLINE LOG FILE]

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

步骤

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

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

c.还原数据(restoredatabase)

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

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

f.使用open resetlogs打开数据库(alterdatabase 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 1]

[Oracle 热备份][Oracle 2]

[Oracle 备份恢复概念][Oracle 3]

[Oracle 实例恢复][Oracle 4]

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

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

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考：

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

[Oracle 表空间与数据文件][Oracle]

[Oracle 密码文件][Oracle 6]

[Oracle 参数文件][Oracle 7]

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

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

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle 9]

[RMAN catalog]: http://blog.csdn.net/robinson_0612/archive/2010/11/29/6043649.aspx
[catalog _RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/12/13/6073020.aspx
[Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle _ROLLBACK_UNDO]: http://blog.csdn.net/robinson_0612/archive/2010/07/13/5731158.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx
[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx