How to recover database when Temp file lost i.e remove from disk.
SQL> select * from abc order by 1;
select * from abc order by 1
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/home/oracle/10.2.0/product/oradata/orcl/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Add a new datafile in temporary tablespace and drop old datafile
SQL> select file_name ,status from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/home/oracle/10.2.0/product/oradata/orcl/temp01.dbf
AVAILABLE
SQL> alter tablespace temp add tempfile '/home/oracle/10.2.0/product/oradata/orcl/temp02.dbf' size 200m ;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/home/oracle/10.2.0/product/oradata/orcl/temp01.dbf' ;
Tablespace altered.
SQL> select * from abc order by 1;
5 comments:
Hi Vanita,
We are getting the below error at DB level.
ORA-01116: error in opening database file 21
ORA-01110: data file 21: '/oracle/CD1/sapdata1/sr3usr_1/sr3usr.data1'
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied:
Could you please advise to resolve this Issue
Regards,
Ram
Hi Vanitha,
I have executed the SQL statement as below.
SQL> select * frm v$recover_file;
Error at Line 1:
ORA-00210: Cantnot open the specified control file.
ORA-00202: control file: '/oracle/CD1/origlogA/cntrl/cngrlCD1.dbf''
Linux-x86_64 Error :13 : Permission denied.
Additional information:3
Due to this issue we backup is getting failed.
Could you please help us ...
Regards,
Ram.
Hi Vanitha,
I have executed the SQL statement as below.
SQL> select * frm v$recover_file;
Error at Line 1:
ORA-00210: Cantnot open the specified control file.
ORA-00202: control file: '/oracle/CD1/origlogA/cntrl/cngrlCD1.dbf''
Linux-x86_64 Error :13 : Permission denied.
Additional information:3
Due to this issue we backup is getting failed.
Could you please help us ...
Regards,
Ram.
Hi Vanitha,
SQL> select file_name ,status from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
STATUS
-------
/oracle/CD1/sapdata1/temp_1/temp.data1
ONLINE
Can i delete the temp.data1 file. after adding the new datafile.
can you please advise is there any impact to the system.
Reagards,
Ram.
Hi Vanitha,
While adding the datafile to the temp table space i am getting the below error.
kindly advise.
SQL> alter tablespace temp add tempfile '/oracle/CD1/sapdata1/temp_1/temp1.data1' size 200m ;
alter tablespace temp add tempfile '/oracle/CD1/sapdata1/temp_1/temp1.data1' size 200m
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist
Regards,
Ram
Post a Comment