Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, August 11, 2015

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>


No comments: