ORADATA - How to restore oracle database from this ? 1

[SimonPeh]

Greetings,

Can anybody help me with how to restore the oracle instance/database from just the ORADATA file in the same server but in a different directory?

Some of the tables are corrupted and we do not have the export Oracle. So we are restoring from OS using ufsrestore which restore the whole of ORADATA directory.

Please help explain how to go about restoring the database from ORADATA. Only then can i export out the needed table and import back to the production database.

Thank you and appreciate your help.

SP

 

[hoinz]

Hello,

in order to help you, or even in order to find out whether there is any help at all, we will need a lot of information first:

Which operating system?

You are not clear about your ORADATA. First you call it ORADATA file, and later on ORADATA directory.

How was this ORADATA created? As you say 'in a different directory', I gather it might be some kind of copy or backup. But how was that done exactly? By Oracle commands, or by operating system command? While your database was down, or up and running? How old?

How much of your database is damaged? Just a few tables, or all of it? Is it running now? Any error messages?

I suppose more questions will arise, when you have answered these.

regards

 

[SimonPeh]

IT is in solaris 5.9.

The Oradata is a directory with all the datafiles of the database.

Just 1 table is corrupted by the user. We need this particular table.

I'm thinking how to restore from the ORADATA files which we can restore from our backup tape. If we have the dmp file, is it possible to extract just 1 table from the dmp file ?

For info, the original database is still running. only that 1 table data is corrupt as they overwrite it incorrect data.

 

[hoinz]

Hi,

still not quite sure about your problem; what do you mean by 'corrupted' ?
Did your users delete or overwrite large parts of your table? It seems so.
Or are there Oracle error messages regarding corrupt blocks ?

Unfortunately it is not possible to restore just one table from a backup. You will have to restore your complete database. And by the way, datafiles are not sufficient for this.
You didn't tell us details about how your backup was done, but let's assume all needed files are there.

The best way seems to restore it to another server; do you have a test server at hand? Otherwise you will have to overwrite your current system, and your users won't like the idea of a long downtime. You may take a backup from your current system before overwriting it, and restore it later.
Once you have restored it, you may export the the needed table, for import later on.

I am not going to give details of doing a restore here; it was already done by more gifted persons in other threads.

And depending on how much data is missing in your table, and on how long a downtime your users are willing to accept, you might look for other ways of regaining the lost data, perhaps from printouts?

hope this helps?

 

[KenCunningham]

ufsrestore will restore the whole of whatever filesystems are specified using ufsdump. I can't recall whether ufsdump saves files using relative pathnames, but I think it does (you can check using the -t option so that it doesn't actaully write the files, just displays them). If so, you can restore your files to another area of your server, rename them and try to start up using a backup controlfile (suitably amended) from the production area.

Sorry if this is a little sketchy, as I don't have too much time today - you might get more details doing a keyword search for clone in this forum and other Oracle fora on Tek-Tips.

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

 

[SantaMufasa]

If we have the dmp file, is it possible to extract just 1 table from the dmp file ?

In Oracle, we refer to the results of an export as a "dump file" (or, as you call it, a "dmp" file). The problem with using a dump file is that it allows you to only "restore" a table, not "recover" a table. This means that if you use that version of your corrupted table's data that you will be pushing that table "back" in time to its state at the time that you did the export.


If that is acceptable to you and your organisation, then importing that single table is certainly the easiest scenario for restoring that table.

Another condition for your importing that table to be an acceptable scenario is that the "corruption" that you are experiencing cannot be a "hard corruption" on your disk media. That is, the problem that you had with the corrupted table must not have resulted from a physical defect on the magnetic surface of the disk drive.

The other issue with which you must deal on the corrupted table is that of referential integrity: Are there child tables that have Foreign Keys that point to the corrupted table? If so, you must alter those child tables to get rid of their Foreign Keys that point to the corrupted table:

ALTER <child-table-name> DROP CONSTRAINT <FK-constraint name>;

You must later re-create the Foreign Key constraint once we have restored the parent table.

If you have met all of the above conditions, then, yes, you can restore a single table from your dump file. You should first DROP the corrupted table:

DROP TABLE <corrupted-table name>;

Next, (not from SQL, but from the operating-system prompt) you issue an import command (in a form similar to the following), on a single o/s command line:

[o/s prompt] imp buffer=15000000 grants=y feedback=1000 tables=<name of corrupted table> file=<fully qualified dump-file name> userid=<username/password of exporting user>

Once the import is complete, re-create the Foreign Keys on the child tables (as I mentioned above) and you should be good to go.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SimonPeh]

Hi SantaMufasa,

Using the code you had provided, I was able to import back the table I needed. Thank you.

AS for restoring from ORADATA, I realise you need a copy of control file.
Modify the control file to create the actual database.

Then startup nomount and run the modify control file to create the database. From there, continue to do whatever is necessary.

Thanks Guys for all the great help.