RMAN备份与恢复系列之非系统表空间恢复

实验环境

• 操作系统 Redhat5.4 x86
• 数据库版本 oracle 11gR2 (11.2.0.1.0)
• 实验前已经做了RMAN全量备份包括controlfile、spfile

实验模拟

案例模拟

手动删除已存在的TEST表空中的数据文件，模拟表空间中数据文件损坏情况：

[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 23:03:21 2017Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITESQL> SQL>col tablespace_name for a20SQL> col file_name for a60SQL> set line 180SQL> select tablespace_name, file_name from dba_data_files;TABLESPACE_NAME      FILE_NAME-------------------- ------------------------------------------------------------SYSTEM           /u01/app/oracle/oradata/PROD/disk5/system01.dbfSYSAUX           /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbfUNDOTBS1         /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbfUSERS            /u01/app/oracle/oradata/PROD/disk2/users01.dbfEXAMPLE          /u01/app/oracle/oradata/PROD/disk5/example_01.dbfEXAM             /u01/app/oracle/oradata/PROD/disk1/exam_01.dbfTEST             /u01/app/oracle/oradata/PROD/disk5/testFREE_LIST        /u01/app/oracle/oradata/PROD/disk1/free_listUSERSS           /u01/app/oracle/oradata/PROD/disk2/users_01.dbf9 rows selected.SQL> !rm /u01/app/oracle/oradata/PROD/disk5/testSQL> create table test tablespace test as select * from dba_data_files;create table test tablespace test as select * from dba_data_files                                                   *ERROR at line 1:ORA-01116: error in opening database file 8ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD/disk5/test'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

案例恢复

由于损坏的是非系统表空间，该表空间损坏只会影响到对该表空间中该数据文件的读写操作，对其它表空间以及该表空间的其他数据文件的操作没有影响。
因此，可以进行rman在线恢复：

[oracle@node1 ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 5 23:06:19 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.connected to target database: PROD (DBID=352761597)RMAN> list failure;using target database control file instead of recovery catalogList of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------1479       HIGH     OPEN      05-MAY-17     One or more non-system datafiles are missingRMAN> advise failure;List of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------1479       HIGH     OPEN      05-MAY-17     One or more non-system datafiles are missinganalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=45 device type=DISKanalyzing automatic repair options completeMandatory Manual Actions========================no manual actions availableOptional Manual Actions=======================1. If file /u01/app/oracle/oradata/PROD/disk5/test was unintentionally renamed or moved, restore itAutomated Repair Options========================Option Repair Description------ ------------------1      Restore and recover datafile 8    Strategy: The repair includes complete media recovery with no data loss  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1211353359.hmRMAN> repair failure;Strategy: The repair includes complete media recovery with no data lossRepair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1211353359.hmcontents of repair script:   # restore and recover datafile   sql 'alter database datafile 8 offline';   restore datafile 8;   recover datafile 8;   sql 'alter database datafile 8 online';Do you really want to execute the above repair (enter YES or NO)? yesexecuting repair scriptsql statement: alter database datafile 8 offlineStarting restore at 05-MAY-17using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/PROD/disk5/testchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_05/o1_mf_nnndf_TAG20170505T225602_djs4n3b1_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_05/o1_mf_nnndf_TAG20170505T225602_djs4n3b1_.bkp tag=TAG20170505T225602channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 05-MAY-17Starting recover at 05-MAY-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 05-MAY-17sql statement: alter database datafile 8 onlinerepair failure completeRMAN> exitRecovery Manager complete.[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 23:07:11 2017Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>  create table test tablespace test as select * from dba_data_files;Table created.SQL> select count(1) from test;  COUNT(1)----------     9SQL> 

至此，非系统表空间的恢复已经完成，可以对该表空间进行正常的读写操作了。