从oracle db 11gR2 RAC RMAN全备中恢复单个表空间

在日常工作中，有时我们会遇到一个大型数据库，需要进行RMAN恢复，业务上只需要恢复其中一个或几个用户表空间，就能满足业务需求，这样可以节省恢复目标库的存储空间，同时可以加快数据库恢复速度。

下面我们通过实例演示如何从oracledb 11g RAC RMAN全备中恢复到单个用户表空间MTA（单实例）过程。

设置目标机器环境变量

ORACLE_BASE=/oracle/app/db

ORACLE_SID=MIS

ORACLE_HOME=/oracle/app/db/product/11.2.0/db_1

连接RMAN

oracle@CRSZDR01>rman target /

创建数据库初始化参数文件

RMAN>startupnomount

RMAN>restorespfile to pfile '/oracle/app/db/product/11.2.0/db_1/dbs/initMIS.ora'from '/backup/zhang/MIS_c-2127504240-20111023-00';

Starting restore at 05-DEC-11

using target database control file insteadof recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=130 devicetype=DISK

channel ORA_DISK_1: restoring spfile fromAUTOBACKUP /backup/zhang/MIS_c-2127504240-20111023-00

channel ORA_DISK_1: SPFILE restore fromAUTOBACKUP complete

Finished restore at 05-DEC-11

初始化参数文件根据实际环境进行相应调整。

设置DBID，恢复控制文件

RMAN>shutdownimmediate

RMAN>startupnomount

executing command: SET DBID

RMAN>restorecontrolfile from '/backup/zhang/MIS_c-2127504240-20111023-00';

Starting restore at 05-DEC-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1112 devicetype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=/data/MIS/controlfile/control01.ctl

Finished restore at 05-DEC-11

RMAN>alterdatabase mount;

database mounted

released channel: ORA_DISK_1

重定向备份集位置

RMAN>catalogstart with '/backup/zhang/';

searching for all files that match thepattern /backup/zhang/

List of Files Unknown to the Database

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

File Name:/backup/zhang/AL_MIS_20111023_S1055_P1

……

Do you really want to catalog the abovefiles (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /backup/zhang/AL_MIS_20111023_S1055_P1

…….

crosscheck backup

RMAN>crosscheckbackup;

数据文件重命名

在这一步只需要重命名systemsysaux undo user mta表空间中的数据文件。

run

{

SET NEWNAME FOR DATAFILE 1 TO'/data/MIS/datafile/system.259.733922231';

SET NEWNAME FOR DATAFILE 22 TO '/data/MIS/datafile/mta_01.dbf';

SET NEWNAME FOR TEMPFILE 1 TO'/data/MIS/datafile/temp.263.723294127';

restore tablespaceSYSTEM,SYSAUX,UNDOTBS1,UNDOTBS2,USERS,MTA until TIME "TO_DATE('2011-10-2410:00:00', 'yyyy-mm-dd hh24:mi:ss')" ;

sql "alter database datafile6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23 offline drop";

SWITCH DATAFILE ALL;

}

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 05-DEC-11

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting datafilebackup set restore

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

channel ORA_DISK_1: restoring datafile00001 to /data/MIS/datafile/system.259.733922231

(结果太多，略去部分)

部分表空间数据恢复

在下面这条命令中要跳过不需要恢复的表空间。

RMAN>recover database until TIME"TO_DATE('2011-10-24 10:00:00', 'yyyy-mm-dd hh24:mi:ss')" skip tablespaceHRMP,LSM,EHS,PRMS,HRWSP,QTN,CDI,CMS,INVITE,SURVEY,CLASS60,ZIA,PORTAL,INFORM,TRSCIS,SCANVOTE,SSMS,TEMP;

Starting recover at 05-DEC-11

using channel ORA_DISK_1

using channel ORA_DISK_2

(结果太多，略去部分)

archived log filename=/data/MIS/archive/1_1478_733922224.dbf thread=1 sequence=1478

archived log file name=/data/MIS/archive/2_2578_733922224.dbfthread=2 sequence=2578

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

Finished recover at 05-DEC-11

重建控制文件

oracle@CRSZDR01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0Production on Mon Dec 5 16:22:46 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0- 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL>alter database backup controlfile to trace;

Database altered.

SQL>shutdownimmediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>startup nomount

ORACLE instance started.

Total System Global Area 2350841856 bytes

Fixed Size 2225944 bytes

Redo Buffers 8192000 bytes

SQL>@/home/oracle/CreateControlfileReuse.sql;

Control file created.

文件内容如下：

oracle@CRSZDR01> more CreateControlfileReuse.sql

CREATE CONTROLFILE REUSE DATABASE"MIS" RESETLOGS

ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/data/MIS/redo01.dbf' SIZE 50M,

GROUP 2 '/data/MIS/redo02.dbf' SIZE 50M,

GROUP 3 '/data/MIS/redo03.dbf' SIZE 50M

DATAFILE

'/data/MIS/datafile/system.259.733922231',

'/data/MIS/datafile/mta_01.dbf'

CHARACTER SET AL32UTF8

;

SQL>alterdatabase open resetlogs;

Database altered.

SQL>ALTERTABLESPACE TEMP ADD TEMPFILE '/data/MIS/temp01.dbf' size 50m;

Tablespace altered.

SQL> select OPEN_MODE from v$database;

OPEN_MODE

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

READ WRITE

到此，我们完成了恢复一个数据库用户表空间MTA。