【Oracle】oracle用rman异机恢复部分表空间方案

RMAN异机恢复部分表空间

RMAN单独恢复表空间(异机)并不是简单的restore tablespace tbs;recover tablespace tbs;
会报错如下：
RMAN-03002: failure of switch command at 11/12/2014 10:36:09
RMAN-20230: datafile copy not found in the repository
RMAN-06015: error while looking up datafile copy name: /u01/oracle11gR2/oracle/oradata/ora11g/system.dbf

正确恢复步骤如下：
1、恢复控制文件，启到mount状态
2、查询v$datafile,v$tempfile,v$tablespace，确认表空间、数据文件信息
3、恢复指定的表空间，但同时要恢复system、sysaux（若只需要测试验证业务数据，可以不恢复，则其状态就为offline，生产环境恢复当然要恢复它）、undo表空间、指定要恢复的业务表空间。
4、alter database open resetlogs;

操作如下：
目标：生产环境有全备及归档备份，现需要将其中一个业务表空间恢复到测试服务器查看数据。

（1）编辑参数文件
vi /home/oracle/pfile_aa.ora

*.audit_file_dest='/oracle/tmp_test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/tmp_test/control01.ctl','/oracle/tmp_test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='/oracle/tmp_test/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/oracle/tmp_test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.114)(PORT=1523))'
*.log_archive_dest_1='location=/oracle/tmp_test/'
*.open_cursors=300
*.pga_aggregate_target=228589568
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=915406848
*.undo_tablespace='UNDOTBS1'

（2）启库到nomount状态

startup nomount pfile='/home/oracle/pfile_aa.ora';

（3）恢复控制文件

restore controlfile from '/oracle/rman/cntrl_44_1c018s3t_1_1_20210613';

（4）启库到mount状态

alter database mount;

（5）查询数据文件、表空间相关信息

set line 400
col name for a60
select file#,name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /oracle/test/datafile/test/system01.dbf
         2 /oracle/test/datafile/test/sysaux01.dbf
         3 /oracle/test/datafile/test/undotbs01.dbf
         4 /oracle/test/datafile/test/users01.dbf
         5 /oracle/test/datafile/test/tbs01.dbf


01:25:21 SQL> select * from v$tempfile;

no rows selected

01:25:15 SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 TBS01                          YES NO  YES

6 rows selected.

（6）恢复表空间

RMAN> run
{
 SET NEWNAME FOR DATABASE to '/oracle/tmp_test/datafile/%b';
 SET NEWNAME FOR TEMPFILE 1 TO '/oracle/tmp_test/datafile/temp01.dbf' ;
 set until time "to_date('2021-01-13 01:10:00','YYYY-MM-DD HH24:MI:SS')"; 
 restore tablespace system,sysaux,UNDOTBS1,tbs01;
 SWITCH DATAFILE 1;
 SWITCH DATAFILE 2;
 SWITCH DATAFILE 3;
 ##SWITCH DATAFILE 4;   ---users表空间
 SWITCH DATAFILE 5;
 switch tempfile all;
 recover database skip tablespace tbs02,tbs03,tbs04,USERS;
}
RMAN> alter database open resetlogs;
database opened

（7）查看表空间

FILENAME                                                        |CREATED           |TBSNAME           |STATUS      | INITMB|  CURMB|  MAXMB|AUTOEXT
----------------------------------------------------------------|------------------|------------------|------------|-------|-------|-------|--------
/oracle/tmp_test/datafile/sysaux01.dbf                          |2013-08-24 11:37  |SYSAUX            |ONLINE      |      0|    520|  32777|YES
/oracle/tmp_test/datafile/system01.dbf                          |2013-08-24 11:37  |SYSTEM            |SYSTEM      |      0|    770|  32777|YES
/oracle/tmp_test/datafile/tbs01.dbf                             |2021-06-13 00:56  |TBS01             |ONLINE      |     10|     10|     10|NO
/oracle/test/datafile/test/tbs02.dbf                            |2021-06-13 01:37  |TBS02             |OFFLINE     |     50|       |       |
/oracle/test/datafile/test/tbs03.dbf                            |2021-06-13 01:37  |TBS03             |OFFLINE     |     50|       |       |
/oracle/test/datafile/test/tbs04.dbf                            |2021-06-13 01:37  |TBS04             |OFFLINE     |     50|       |       |
/oracle/tmp_test/datafile/undotbs01.dbf                         |2013-08-24 12:07  |UNDOTBS1          |ONLINE      |      0|    215|  32777|YES
/oracle/tmp_test/datafile/users01.dbf                           |2013-08-24 11:37  |USERS             |OFFLINE     |      0|       |       |

8 rows selected.

（8）若不恢复sysaux表空间，则其状态为offline

若不恢复sysaux表空间，则如下：
[oracle@rhel11g ~]$ orafile

FILENAME                                                        |CREATED           |TBSNAME           |STATUS      | INITMB|  CURMB|  MAXMB|AUTOEXT
----------------------------------------------------------------|------------------|------------------|------------|-------|-------|-------|--------
/oracle/tmp_test/datafile/sysaux01.dbf                          |2013-08-24 11:37  |SYSAUX            |OFFLINE     |      0|       |       |
/oracle/tmp_test/datafile/system01.dbf                          |2013-08-24 11:37  |SYSTEM            |SYSTEM      |      0|    770|  32777|YES
/oracle/tmp_test/datafile/tbs01.dbf                             |2021-06-13 00:56  |TBS01             |ONLINE      |     10|     10|     10|NO
/oracle/test/datafile/test/tbs02.dbf                            |2021-06-13 01:37  |TBS02             |OFFLINE     |     50|       |       |
/oracle/test/datafile/test/tbs03.dbf                            |2021-06-13 01:37  |TBS03             |OFFLINE     |     50|       |       |
/oracle/test/datafile/test/tbs04.dbf                            |2021-06-13 01:37  |TBS04             |OFFLINE     |     50|       |       |
/oracle/tmp_test/datafile/undotbs01.dbf                         |2013-08-24 12:07  |UNDOTBS1          |ONLINE      |      0|    215|  32777|YES
/oracle/tmp_test/datafile/users01.dbf                           |2013-08-24 11:37  |USERS             |OFFLINE     |      0|       |       |

8 rows selected.

Elapsed: 00:00:00.02
[oracle@rhel11g ~]$ 

总结：
RMAN异机恢复指定的表空间，需要同时恢复system、sysaux（若只需要测试验证业务数据，可以不恢复，则其状态就为offline，生产环境恢复当然要恢复它）、undo表空间、指定要恢复的业务表空间。