Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

Oracle Data Recovery

Go to solution
Marty Metras
Super Advisor

Oracle Data Recovery

I was wondering?
With Oracle you can import/Export to to recover data. And you can recreate a control file to let the database know the data is in another place after a media failure.
Here is my question. If some one deleted a mess of history data that the only copy was in the tablesspace you have backed up as datafile, Could you make Tablespace/Datafiles part of another database so you could extract the data?
Here is my thinking.
Create a small database.
Then create a new controlfile adding a datafile where the datafile you are adding belongs to a diffent database.
This way you could just write a script to copy that data to the orginal database with out having to disrup the production database.
Could you do that?
The only thing that always remain the same are the changes.
Sanjay Kumar Suri
Honored Contributor

Re: Oracle Data Recovery

No chance.

CKPT writes control information on the each of the datafile and the control file during the checkpoint.

The suggested way will not work under any condition.

Database recovery will work only if you have all/some data files + matching control file and archive & redo log files.

More post on this may clear further.

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Hein van den Heuvel
Honored Contributor

Re: Oracle Data Recovery

If you had known before hand that you were going to accidently delete data, then you could have recovered much long the lines you describe using "Transportable Tablespaces".

But then just maybe you would not have deleted it huh?


At this point you pretty much have no choice but to restore the entire database, either on a parallel system, or 'next to' the original one. (hopefully you room!). Startup (maybe changing file names / controlfile ). Export the missing data, shutdown, import in production database.

Hmmm... actually... you might get away with restoring control files, redo, system, the target data (and index). Any other tablespaces could be left un-restored and set offline (but that may be more hassle than it is worth).

Hey, consider it a good recover/restore practice!

I would encourage you to read up on transportable tablespaces. They are a neat tool to move data between database instances.
But you need a matedata export to be able to integrate the data defintions/layout with the datafile, and you do not have that dump now do you?

fwiw, I have used it to carry forward statspack data from experiment to experiment accross full DB restores. Many other possibilities!

Indira Aramandla
Honored Contributor

Re: Oracle Data Recovery

Hi Marty,

Yes there are possible ways of recovering the lost datafile if you have a backup.

One of the options would be:-

If the datafile that is lost is DATA or INDEX, then in your database Offline the tablespace the tablespace for which the datafile is lost and then start the database. Then restore from your previous backup to a separate location (preferrably on a DEV/TRAINING server) and start the database.

Then export the objects in the affected tablespace ( using User or table level export). Create the tablespace in the original database. Import the objects exported above.
Note: If the database is 8i or above, you can also use Transportable tablespace feature.

The other alternative is, if you have the datafile that was lost, system datafile and the redo logs backedup then you can restore only this datafile / tablespace into a different location (preferable a DEV/TRAINING SERVER) and get the data.
On the DEV/TRAINING server restore the redo logs, system datafile and the required datafile. On the original database backup controlfile to trace, as
Alter database backup controlfile to trace;

This command will create a trace file in the USER_DUMP directory. You will have to edit the file to remove the comment lines and also edit the lines where the datafiles are listed. Make sure you keep only the redo logs, system datafile and the datafile that you wanted recovered and save it as an sql file. Then logon to svrmgrl and execute this saved script. Then you can export the data from this database and import. This is silimar to the above option only thing you are not restoring the whole database, only restoring the datafile (will be handy when low disk space).

The third option will be, if your database is not mission critical and you can efford some down time, then you can backup the control file to trace. If your backedup datafile is not too old then, shutdown the database, and restore the missing datafile. And then try to startup the database. The databse will be mounted and ask for a media recovery for the datafile as
ORA-01113: file 7 needs media recovery
ORA-01110: data file

Then recover the datafile that you restored.
as svrmgrl> recover datafile 'name.dbf'
Then open the database.

Of all the three options 1 and 2 are a lot safer and better options.

I hope this helps.

Indira A

Never give up, Keep Trying
Marty Metras
Super Advisor

Re: Oracle Data Recovery

All of these are good ideas.
I just thought of another idea.
Could I take a backup controlfile and strip out the other datafiles and create a database then leave recovery off as the data I want to extract is in this datafile/Tablespace. Oh, did I tell you that the 2 datafiles only have one tablespace. And I am still on Oracle8 not 8i until next month.
I also have a Standby database that I could have pulled the lost data from if had cought it quicker. I have Archive logs and the datafile backups so I have not lost the data yet. I just cant get to it yet.
Here is an idea!
Since there ia only one tablespace I could reload the datafiles and recover tablespace.
I would have the old data and the new data.
Then after a full backup I could delete the right data.
In this case I should restore the datafiles for the tablespace tables and Indexes for this table.
What you think?
The only thing that always remain the same are the changes.
Indira Aramandla
Honored Contributor

Re: Oracle Data Recovery

Hi Marty,

Yes you could use any of those options depending on your requirement (hot soon you want the data and what down time you can efford) and the availability (how much disk space).

The idea of creating the controlfile to trace and striping the lines with the other datafiles is one of the options that I mentioned you you already.

The other idea that you have to recover the tablespace from the other databse and get the data is a safer and better option.

it does not matter how many datafiles you have, the tablespace can have one or more datafiles defined. The olny thing you cannot do is create a different database (different name) and then recover the datafile from the original datafile as the datafile header will carry the database name id.

You can follow any of the mentioned options.

Indira A
Never give up, Keep Trying
Marty Metras
Super Advisor

Re: Oracle Data Recovery

Thanks Indira A
I underall the options now.

Thanks for all your help.
The only thing that always remain the same are the changes.
Honored Contributor

Re: Oracle Data Recovery

hi marty,

Please also have a look to this great link:

hope this helps too!
best regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)