Oracle Recover a lost datafile:

Recover a lost datafile:

Create a test table to verify the data is not lost from the datafile:

SQL>  select df.name "Datafile_Name",ts.name "Tablespace_Name" from v$datafile df,v$tablespace ts where df.ts#=ts.ts# and ts.name='USERS';

Datafile_Name                                       Tablespace_Name
-----------------------------------------------------------------------------------------------------------------------
+DATA2/orcl/users01.dbf                          USERS

SQL> create table hr.regions2 tablespace  users as select * from hr.regions;

Table created.

SQL> select * from hr.regions2 ;

REGION_ID REGION_NAME

---------- -------------------------

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

SQL> alter database datafile '+DATA2/orcl/users01.dbf' offline;

Database altered.

Delete the datafile:

SQL> alter database datafile '+DATA2/orcl/users01.dbf' online;

alter database datafile '+DATA2/orcl/users01.dbf' online

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '+DATA2/orcl/users01.dbf'

SQL> alter database datafile '+DATA2/orcl/users01.dbf' offline;

Database altered.

ASMCMD> mkalias '+DATA2/orcl/datafile/USERS.263.886783809' users01.dbf

ASMCMD> ls -l users01.dbf

Type      Redund  Striped  Time             Sys  Name

DATAFILE  UNPROT  COARSE   AUG 04 16:00:00  N    users01.dbf => +DATA2/ORCL/DATAFILE/USERS.263.886783809

SQL> alter database datafile '+DATA2/orcl/users01.dbf' online;

alter database datafile '+DATA2/orcl/users01.dbf' online

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '+DATA2/orcl/users01.dbf'

SQL> recover datafile 4;

Media recovery complete.

SQL> alter database datafile '+DATA2/orcl/users01.dbf' online;

Database altered.

SQL> select * from hr.regions2 ;

REGION_ID REGION_NAME

---------- -------------------------

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

ASMCMD> ls -l users01.dbf

Type           Redund  Striped  Time             Sys  Name

DATAFILE       UNPROT  COARSE   AUG 04 15:00:00  N    users01.dbf => +DATA2/ORCL/DATAFILE/USERS.263.886783809

ASMCMD>