非系統資料檔案被刪除恢復(IZ0-053, Q574)

574.A user sends you an email with the following error message: create table idtable(id number) *
ERROR at line 1: ORA- 01110: data file 4: ,,/oracle01/oradata/orcl/users01.dbf ORA-27041:
01116: error in opening database file 4 ORA-unable to open file Linux Error: 2: No such file or directory
Additional information: 3 You can choose from the following steps:
Which is the correct order of these steps in this case?
A. Restore the missing database datafiles.
B. Take the missing datafile offline.
C. Shut down the database.
D. Issue the recover tablespace USERS command.
E. Issue the Startup Mount command to mount the database.
F. Bring the USERS tablespace online.
G. Issue the alter database open command.
H. b, a, d, f
I. c, a, e, b, d, f, g
J. c, e, d, g
K. b, d, f
L. e, d, g
Answer: A

驗證過程：
[oracle@db01 inst1]$ rm /u01/app/oracle/oradata/inst1/users01.dbf
[oracle@db01 inst1]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 7 10:52:14 2016


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




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


SQL> create table scott.test02(pid number) tablespace users;


Table created.


SQL> insert into scott.test02 values(1);
insert into scott.test02 values(1)
                  *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/inst1/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
發出INSERT語句時，出現錯誤。此時可以用RMAN來獲取恢復的語句。

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
142        HIGH     OPEN      07-MAR-16     One or more non-system datafiles are missing
RMAN> advise failure;


List of Database Failures
=========================


Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
142        HIGH     OPEN      07-MAR-16     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=33 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/inst1/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/inst1/inst1/hm/reco_818494555.hm

[oracle@db01 inst1]$ cat /u01/app/oracle/diag/rdbms/inst1/inst1/hm/reco_818494555.hm
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';


可以看到，只需要offline=>restore=>recover=>online即可完成恢復

參考：http://blog.csdn.net/rlhua/article/details/13773877

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2050696/，如需轉載，請註明出處，否則將追究法律責任。