OraFAQ Forum: Backup & Recovery » recover old datafile

Home » RDBMS Server » Backup & Recovery » recover old datafile

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

recover old datafile [message #150395]

Wed, 07 December 2005 13:36

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.

Report message to a moderator

Re: recover old datafile [message #150398 is a reply to message #150395]

Wed, 07 December 2005 13:58

Mahesh Rajendran
Messages: 10708
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 >

Report message to a moderator

Re: recover old datafile [message #150404 is a reply to message #150395]

Wed, 07 December 2005 14:29

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.

Report message to a moderator

Re: recover old datafile [message #150411 is a reply to message #150395]

Wed, 07 December 2005 16:01

longduck
Messages: 13
Registered: November 2005

Junior Member

Mahesh...do you have any suggestions? Thanx in advance.

Report message to a moderator

Re: recover old datafile [message #150413 is a reply to message #150411]

Wed, 07 December 2005 16:32

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Previous Topic:	Recover database problem from a hot backup
Next Topic:	backup problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 16 07:02:39 CST 2025