在日常工作中，有时我们会遇到一个大型数据库，需要进行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
RMAN> setdbid=2127504240
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表空间中的数据文件。
RMAN>
run
{
SET NEWNAME FOR DATAFILE 1 TO'/data/MIS/datafile/system.259.733922231';
SET NEWNAME FOR DATAFILE 2 TO'/data/MIS/datafile/sysaux.260.733922235';
SET NEWNAME FOR DATAFILE 3 TO '/data/MIS/datafile/undotbs1.261.733922239';
SET NEWNAME FOR DATAFILE 4 TO '/data/MIS/datafile/undotbs2.263.733922261';
SET NEWNAME FOR DATAFILE 5 TO'/data/MIS/datafile/users.264.733922267';
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
Variable Size 2290092264 bytes
Database Buffers 50331648 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/sysaux.260.733922235',
'/data/MIS/datafile/undotbs1.261.733922239',
'/data/MIS/datafile/undotbs2.263.733922261',
'/data/MIS/datafile/users.264.733922267',
'/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。
