The following scenario describes what happens when a nonsystem datafile is lost.
Before proceeding be sure to have a complete backup of your test database and be sure your database is in ARCHIVELOG mode.
Let's start. My database is already running.
[oracle@localhost orcl]$ ps -ef|grep smon
oracle 7200 2820 0 06:57 pts/1 00:00:00 grep smon
And it's in ARCHIVELOG mode
[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> startup
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
Connecting through RMAN I can receive information about my datafiles
[oracle@localhost orcl]$ rman target /
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 831 SYSTEM *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2 1105 SYSAUX *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 40 UNDOTBS1 *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4 225 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5 82 EXAMPLE *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6 7 APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
I'm of course able to query the dictionary tables and see in which datafiles some tables are located.
SQL> set pagesize 999
SQL> set linesize 180
SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables
2 where owner = 'HR';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ -----------------------
HR REGIONS USERS
HR LOCATIONS USERS
HR DEPARTMENTS USERS
HR JOBS USERS
HR EMPLOYEES USERS
HR JOB_HISTORY USERS
HR COUNTRIES
What does it happens when I delete the datafile where USERS tablespace is based on.
[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf
I'm still able to query the dictionary tables for example but...
SQL> select count(*) from dba_tables;
COUNT(*)
----------
3013
I obtain an error when I try to select some rows from the HR.EMPLOYEES table.
SQL> select count(*) from hr.EMPLOYEES;
select count(*) from hr.EMPLOYEES
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Looking at the log file, there's the same clear error: the systen is not able to obtain status information on file '/home/oracle/app/oracle/oradata/orcl/users01.dbf'.
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
...
Thu Jul 19 07:07:49 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_7476.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...
If you try to connect with RMAN the REPORT SCHEMA command is now unable to correctly size the USERS tablespace. RMAN says its size is 0.
[oracle@localhost orcl]$ rman target /
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 831 SYSTEM *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2 1105 SYSAUX *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 40 UNDOTBS1 *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4 0 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5 82 EXAMPLE *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6 7 APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
Because the USERS tablespace is not a system tablespace, we can recover it just putting it in offline mode.
RMAN> sql 'alter database datafile 4 offline';
sql statement: alter database datafile 4 offline
RMAN> restore datafile 4;
Starting restore at 19-07-2012 07:30:12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
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 00004 to
/home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
tag=TAG20120717T090114
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-07-2012 07:31:10
RMAN> recover datafile 4;
Starting recover at 19-07-2012 07:31:20
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-07-2012 07:31:22
RMAN> sql 'alter database datafile 4 online';
sql statement: alter database datafile 4 online
RMAN>
In the alert log you can see how the recovery process proceedes
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
...
Thu Jul 19 07:30:01 2012
alter database datafile 4 offline
Completed: alter database datafile 4 offline
Thu Jul 19 07:30:01 2012
Starting background process SMCO
Thu Jul 19 07:30:02 2012
SMCO started with pid=46, OS id=8257
Thu Jul 19 07:31:03 2012
Full restore complete of datafile 4
/home/oracle/app/oracle/oradata/orcl/users01.dbf. Elapsed time: 0:00:47
checkpoint is 13569948
last deallocation scn is 13511135
Thu Jul 19 07:31:21 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 4
Thu Jul 19 07:31:35 2012
alter database datafile 4 online
Completed: alter database datafile 4 online
...
Just note how long the restore/recover process lasted: it began at Thu Jul 19 07:30:01 2012 to end at Thu Jul 19 07:31:35 2012 for a TOTAL TIME of 1:34 (one minute and 34 seconds). I'll compare this result with another restore/recover approach in the next recovery scenario.
Now I'm able to query again my hr.employees table
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
and even RMAN is able to size correctly the USERS tablespace
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 831 SYSTEM ***
/home/oracle/app/oracle/oradata/orcl/system01.dbf
2 1105 SYSAUX ***
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 40 UNDOTBS1 ***
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4 225 USERS ***
/home/oracle/app/oracle/oradata/orcl/users01.dbf
5 82 EXAMPLE ***
/home/oracle/app/oracle/oradata/orcl/example01.dbf
6 7 APEX_1930613455248703 ***
/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767
/home/oracle/app/oracle/oradata/orcl/temp01.dbf
In the next recovery scenario I'll use an image copy previously created from RMAN to restore and recover the same tablespace of today.
That's all.