Home » RDBMS Server » Backup & Recovery » recover old datafile
recover old datafile [message #150395] Wed, 07 December 2005 13:36 Go to next message
longduck
Messages: 13
Registered: November 2005
Junior Member
Ok, lets say I dropped a tablespace. A few months later, I want to restore that tablespace since I have a backup datafile. So, I write the control file to trace. I recreate my controlfiles to include the datafile that I'm trying to restore. Is this possible? After I recreate the controlfile, I open the database, "alter database open". The database opens with no problems. When I query the dba_tablespace, the tablespace that I try to recover does not exist. In the alert log, it says the datafile exists in the controlfile but not in the data dictionary. Has anyone seen this before? If so, I can use some help. Thanks.
Re: recover old datafile [message #150398 is a reply to message #150395] Wed, 07 December 2005 13:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
How did you drop?
THe regular drop tablespace space will remove entries from dictionary only.
Physically the files are present.
scott@9i > create tablespace mytablespace datafile '/tmp/myfile.dbf' size 5m;

Tablespace created.

scott@9i > !ls -lrt /tmp | grep dbf
-rw-r-----   1 oracle   dba      5251072 Dec  7 14:49 myfile.dbf

  1* select file_name , tablespace_name from dba_data_files where tablespace_name='MYTABLESPACE'
scott@9i > /

FILE_NAME            TABLESPACE_NAME
-------------------- ------------------------------
/tmp/myfile.dbf      MYTABLESPACE


scott@9i > drop tablespace mytablespace;

Tablespace dropped.

scott@9i > select file_name , tablespace_name from dba_data_files where tablespace_name='MYTABLESPACE';

no rows selected

scott@9i >  select ts#,name from v$tablespace where name='MYTABLESPACE';

no rows selected

scott@9i >  !ls -lrt /tmp | grep dbf
-rw-r-----   1 oracle   dba      5251072 Dec  7 14:52 myfile.dbf

scott@9i > create tablespace mytablespace datafile '/tmp/myfile.dbf' size 5m;
create tablespace mytablespace datafile '/tmp/myfile.dbf' size 5m
*
ERROR at line 1:
ORA-01119: error in creating database file '/tmp/myfile.dbf'
ORA-27038: skgfrcre: file exists


scott@9i > create tablespace mytablespace datafile '/tmp/myfile.dbf' size 5m reuse;

Tablespace created.

scott@9i >  select file_name , tablespace_name from dba_data_files where tablespace_name='MYTABLESPACE';

FILE_NAME            TABLESPACE_NAME
-------------------- ------------------------------
/tmp/myfile.dbf      MYTABLESPACE

scott@9i > drop tablespace mytablespace including contents and datafiles;

Tablespace dropped.

scott@9i > !ls -lrt /tmp | grep dbf

scott@9i >


Re: recover old datafile [message #150404 is a reply to message #150395] Wed, 07 December 2005 14:29 Go to previous messageGo to next message
longduck
Messages: 13
Registered: November 2005
Junior Member
Thanx for your reply. I was able to recreate the tablespace using the REUSE command but the tables I had from the previous datafile is not there. I want to be to restore the tablespace and the data I had from the tablespace/datafile.
Re: recover old datafile [message #150411 is a reply to message #150395] Wed, 07 December 2005 16:01 Go to previous messageGo to next message
longduck
Messages: 13
Registered: November 2005
Junior Member
Mahesh...do you have any suggestions? Thanx in advance.
Re: recover old datafile [message #150413 is a reply to message #150411] Wed, 07 December 2005 16:32 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You just reused the existing file.
That does not mean you can get all the data back!.
You need to restore the database a point in time before you dropped the tablespace or fallback to a valid backup taken before dropping the tablespace. I cannot generalized / recomend anything.
Please read about backup/recovery using RMAN.
Previous Topic: Recover database problem from a hot backup
Next Topic: backup problem
Goto Forum:
  


Current Time: Thu Mar 28 06:14:12 CDT 2024