11g Data Recovery Advisor: How to recover from missing datafile [ID 465946.1]
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
SQL> select count(*) from ygzhou.admin;
COUNT(*)
----------
213008
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/racdb/system01.dbf
/u01/app/oradata/racdb/undotbs01.dbf
/u01/app/oradata/racdb/sysaux01.dbf
/u01/app/oradata/racdb/users01.dbf
/u01/app/oradata/racdb/ygzhou01.dbf
/u01/app/oradata/racdb/ygzhou02.dbf ----mv ygzhou02.dbf ygzhou02.dbf_bak
/u01/app/oradata/hsfis01.dbf
/u01/app/oracle/oradata/racdb/stream01.dbf
SQL> select count(*) from ygzhou.admin;
select count(*) from ygzhou.ygzhou
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oradata/racdb/ygzhou02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate
rman target /
RMAN>startup mount
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
325 HIGH OPEN 02-NOV-11 One or more non-system datafiles are missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2 HIGH OPEN 02-NOV-11 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=20 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oradata/racdb/ygzhou02.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 6
Strategy: The repair includes complete media recovery with no data loss
Repair script.: /u01/app/diag/rdbms/racdb/racdb/hm/reco_711923871.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script. /u01/app/diag/rdbms/racdb/racdb/hm/reco_711923871.hm
contents of repair script.:
# restore and recover datafile
restore datafile 6;
recover datafile 6;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 02-NOV-11
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 00006 to /u01/app/oradata/racdb/ygzhou02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.0/dbhome_1/dbs/01mqkm8d_1_1
channel ORA_DISK_1: piece handle=/u01/app/product/11.2.0/dbhome_1/dbs/01mqkm8d_1_1 tag=TAG20111102T081716
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 02-NOV-11
Starting recover at 02-NOV-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 02-NOV-11
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened
total 1.1G
-rw-r----- 1 oracle oinstall 9.7M Nov 2 09:02 control01.ctl
-rw-r----- 1 oracle oinstall 9.7M Nov 2 09:02 control02.ctl
-rw-r----- 1 oracle oinstall 9.7M Nov 2 09:02 control03.ctl
-rw-r----- 1 oracle oinstall 51M Nov 2 09:02 redo02.log
-rw-r----- 1 oracle oinstall 121M Nov 2 09:01 sysaux01.dbf
-rw-r----- 1 oracle oinstall 21M Nov 2 09:01 temp01.dbf
-rw-r----- 1 oracle oinstall 376M Nov 2 09:00 undotbs01.dbf
-rw-r----- 1 oracle oinstall 26M Nov 2 08:58 ygzhou02.dbf
-rw-r----- 1 oracle oinstall 341M Nov 2 08:55 system01.dbf
-rw-r----- 1 oracle oinstall 51M Nov 2 08:50 redo01.log
-rw-r----- 1 oracle oinstall 51M Nov 2 08:50 redo03.log
-rw-r----- 1 oracle oinstall 5.1M Nov 2 08:50 users01.dbf
-rw-r----- 1 oracle oinstall 11M Nov 2 08:50 ygzhou01.dbf
-rw-r----- 1 oracle oinstall 26M Nov 2 08:29 ygzhou02.dbf_bak1
-rw-r----- 1 oracle oinstall 26M Nov 2 08:19 ygzhou02.dbf_bak
SQL> select count(*) from admin;
COUNT(*)
----------
213008