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

实验环境

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

实验模拟

系统表空间文件损坏或丢失

案例模拟

模拟system表空间数据文件损坏或丢失情况：

[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:26:42 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> col tablespace_name for a30SQL> 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/system01.dbfSQL> drop table test;Table dropped.SQL> create table test tablespace system as select username from dba_users;create table test tablespace system as select username from dba_users                                                            *ERROR at line 1:ORA-01116: error in opening database file 1ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3

案例恢复

--尝试直接进行rman恢复：[oracle@node1 ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 6 07:30:57 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---------- -------- --------- ------------- -------1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missingRMAN> advise failure;List of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missinganalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=51 device type=DISKanalyzing automatic repair options completeMandatory Manual Actions========================1. If file /u01/app/oracle/oradata/PROD/disk5/system01.dbf was unintentionally renamed or moved, restore it2. Automatic repairs may be available if you shutdown the database and restart it in mount mode3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repairOptional Manual Actions=======================no manual actions availableAutomated Repair Options========================no automatic repair options available   -- RMAN提示没有自动修复选项RMAN> exitRecovery Manager complete.--将数据库重启后再次尝试rman恢复：[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:31:42 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> shutdown immediate;ORA-01116: error in opening database file 1ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3SQL> shutdown abort;ORACLE instance shut down.SQL> startup;ORACLE instance started.Total System Global Area  836976640 bytesFixed Size          1339740 bytesVariable Size         662703780 bytesDatabase Buffers      167772160 bytesRedo Buffers            5160960 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf'SQL> select status from v$instance;STATUS------------MOUNTEDSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@node1 ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 6 07:32:26 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.connected to target database: PROD (DBID=352761597, not open)RMAN> list failure;using target database control file instead of recovery catalogList of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missingRMAN> advise failure;List of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missinganalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=21 device type=DISKanalyzing automatic repair options completeMandatory Manual Actions========================no manual actions availableOptional Manual Actions=======================1. If file /u01/app/oracle/oradata/PROD/disk5/system01.dbf was unintentionally renamed or moved, restore itAutomated Repair Options========================Option Repair Description------ ------------------1      Restore and recover datafile 1    Strategy: The repair includes complete media recovery with no data loss  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3184259017.hmRMAN> repair failure;Strategy: The repair includes complete media recovery with no data lossRepair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3184259017.hmcontents of repair script:   # restore and recover datafile   restore datafile 1;   recover datafile 1;Do you really want to execute the above repair (enter YES or NO)? yesexecuting repair scriptStarting restore at 06-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 00001 to /u01/app/oracle/oradata/PROD/disk5/system01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_06/o1_mf_nnndf_TAG20170506T072607_djt2jj5l_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_06/o1_mf_nnndf_TAG20170506T072607_djt2jj5l_.bkp tag=TAG20170506T072607channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15Finished restore at 06-MAY-17Starting recover at 06-MAY-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 06-MAY-17repair failure completeDo you want to open the database (enter YES or NO)? yesdatabase openedRMAN> exitRecovery Manager complete.[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:33:37 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 status from v$instance;STATUS------------OPENSQL> create table test tablespace system as select username from dba_users;Table created.SQL> select count(1) from test;  COUNT(1)----------    10SQL> --至此数据system表空数据文件恢复已经完成。

从上面的测试发现，当数据文件损坏的是系统表空间的时候，无法进行在线恢复，需要将数据库重启，并且重启后由于损坏的是系统表空间，数据库无法启动到open状态，只能启动到mount状态，在mount状态下使用RMAN 可以通过repair advise获得自动恢复方法.