On development server a team member try to access the data from a table got below error messages as datafile not find after searching we found that datafile is removed
Physically our database is in archive mode and database is open
SQL> insert into abc select * from abc;
insert into abc select * from abc
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/10.2.0/product/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Now if we try to bring the tablespace offline normally but it shows error datafile is not exists
At itís location.
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/10.2.0/product/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Hence we bring tablespace offline using immediate option.
SQL> alter tablespace users offline immediate;
Tablespace altered.
Start recovery
SQL> recover tablespace users;
ORA-00279: change 1730264 generated at 04/08/2013 10:54:45 needed for thread 1
ORA-00289: suggestion :
/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_mf
1_46%u_.arc
ORA-00280: change 1730264 for thread 1 is in sequence #46
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_mf
1_46%u_.arcORA-00308: cannot open archived log
‘/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_m
f’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_m
fORA-00308: cannot open archived log
‘1_46%u_.arc/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/20
13_04_08/o1_m’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_mf_1_46_8p4ojowq_.arc
ORA-00308: cannot open archived log
‘f/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_
mf_1_46_8p4ojowq_.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
Bring tablespace online .
SQL> alter tablespace users online;
Tablespace altered.
Chek the status of datafile
SQL> select file_name,status from dba_data_files ;
FILE_NAME
STATUS
/home/oracle/10.2.0/product/oradata/orcl/users01.dbf
AVAILABLE
/home/oracle/10.2.0/product/oradata/orcl/sysaux01.dbf
AVAILABLE
/home/oracle/10.2.0/product/oradata/orcl/undotbs01.dbf
AVAILABE
/home/oracle/10.2.0/product/oradata/orcl/system01.dbf
AVAILABLE
/home/oracle/10.2.0/product/oradata/orcl/example01.dbf
AVAILABLE
/home/oracle/10.2.0/product/oradata/orcl/mytbs.dbf
AVAILABLE
6 rows selected.
