实验环境

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

实验模拟

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

案例模拟

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

[oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:26:42 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> col tablespace_name for a30 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/system01.dbf SQL> 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 1 ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3

案例恢复

--尝试直接进行rman恢复： [oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 6 07:30:57 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 ---------- -------- --------- ------------- ------- 1604 CRITICAL OPEN 06-MAY-17 System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missing RMAN> 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 missing analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=51 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== 1. If file /u01/app/oracle/oradata/PROD/disk5/system01.dbf was unintentionally renamed or moved, restore it 2. Automatic repairs may be available if you shutdown the database and restart it in mount mode 3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair Optional Manual Actions ======================= no manual actions available Automated Repair Options ======================== no automatic repair options available -- RMAN提示没有自动修复选项 RMAN> exit Recovery Manager complete. --将数据库重启后再次尝试rman恢复： [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:31:42 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> shutdown immediate; ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 SQL> shutdown abort; ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 836976640 bytes Fixed Size 1339740 bytes Variable Size 662703780 bytes Database Buffers 167772160 bytes Redo Buffers 5160960 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' SQL> select status from v$instance; STATUS ------------ MOUNTED 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 [oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 6 07:32:26 2017 Copyright (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 catalog 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 missing RMAN> 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 missing analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 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/system01.dbf was unintentionally renamed or moved, restore it Automated 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.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_3184259017.hm contents 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)? yes executing repair script Starting restore at 06-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 00001 to /u01/app/oracle/oradata/PROD/disk5/system01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_06/o1_mf_nnndf_TAG20170506T072607_djt2jj5l_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_06/o1_mf_nnndf_TAG20170506T072607_djt2jj5l_.bkp tag=TAG20170506T072607 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 06-MAY-17 Starting recover at 06-MAY-17 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 06-MAY-17 repair failure complete Do you want to open the database (enter YES or NO)? yes database opened RMAN> exit Recovery Manager complete. [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:33:37 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 status from v$instance; STATUS ------------ OPEN SQL> create table test tablespace system as select username from dba_users; Table created. SQL> select count(1) from test; COUNT(1) ---------- 10 SQL> --至此数据system表空数据文件恢复已经完成。

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