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

实验环境

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

实验模拟

案例模拟

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

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 23:03:21 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> 
SQL>col tablespace_name for a20
SQL> col file_name for a60
SQL> set line 180
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
-------------------- ------------------------------------------------------------
SYSTEM           /u01/app/oracle/oradata/PROD/disk5/system01.dbf
SYSAUX           /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
UNDOTBS1         /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
USERS            /u01/app/oracle/oradata/PROD/disk2/users01.dbf
EXAMPLE          /u01/app/oracle/oradata/PROD/disk5/example_01.dbf
EXAM             /u01/app/oracle/oradata/PROD/disk1/exam_01.dbf
TEST             /u01/app/oracle/oradata/PROD/disk5/test
FREE_LIST        /u01/app/oracle/oradata/PROD/disk1/free_list
USERSS           /u01/app/oracle/oradata/PROD/disk2/users_01.dbf

9 rows selected.

SQL> !rm /u01/app/oracle/oradata/PROD/disk5/test

SQL> 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 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD/disk5/test'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With 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 2017

Copyright (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 catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1479       HIGH     OPEN      05-MAY-17     One or more non-system datafiles are missing

RMAN> 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 missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk5/test was unintentionally renamed or moved, restore it

Automated 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.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1211353359.hm

contents 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)? yes
executing repair script

sql statement: alter database datafile 8 offline

Starting restore at 05-MAY-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/PROD/disk5/test
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_05/o1_mf_nnndf_TAG20170505T225602_djs4n3b1_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_05/o1_mf_nnndf_TAG20170505T225602_djs4n3b1_.bkp tag=TAG20170505T225602
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 05-MAY-17

Starting recover at 05-MAY-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 05-MAY-17

sql statement: alter database datafile 8 online
repair failure complete

RMAN> exit


Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 23:07:11 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table test tablespace test as select * from dba_data_files;

Table created.

SQL> select count(1) from test;

  COUNT(1)
----------
     9

SQL> 

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