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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

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: