We help IT Professionals succeed at work.

Extract  Data from DBF file

Good Morning,
I need to extract some data, a specific table if possible from a backup of our oracle servers DBF files.  If anyone has any help on this it would be appreciated, i spent the better part of yesterday working on this.

Ive heard its possible to link the dbf files into an ms Access database, which would be fine.
Ive read its possible for MS SQL data transformation services to convert from DBF, but cannot figure out how.
I used a porgram yesterday called DBView, using it i was able to see all the data I wanted in the table was there, but in order to export out I needed to pay for the full version. If necessary we may need to go down that route, but i am quite capable of doing this myself. If someone can help give me a nudge in the right direction it would be greatly appreciated.

Comment
Watch Question

Commented:
Do I understand you to say you've successfully taken an ORACLE (binary) data file (.dbf), and read table content with a shareware tool?  Sorry, I can understand your success with Access, DBase, or such PC-based files.  Oracle?  Please confirm?
Thats correct using the dbview.exe (http://www.recovery-for-oracle.com/EN/) I am able to see the schema structure, and open up the tables, but there is no way to export the table data.  You first point the tool to the .ora initialization file, then point it to the dbf.

I have a complete backup of the oracle directory, but from the sounds of it they may not be backing it up properly,  ie backing up while the database is running.

Any other ways to go about this?  
such as

1. Create an DBlink thru ODBC Access and Oracle. then open all dbfs in
access and upload from their to your oracle table.

2. Create CTL and text file of your DBF and and upload it trhu sql loder.

Or
http://oracle.ittoolbox.com/documents/popular-q-and-a/rebuild-database-from-dbf-files-3733
Top Expert 2008
Commented:
To extract some data from running Data base in Oracle there are 3 known ways:

1. Use Data Pump utility and extract the data in binary file and after that import it with the Data Pump
2. Use clasic Export/Import Utility and do export/Import
3. use SPOOL in SQL*Plus or UTIL_FILE PL/SQl package and make a CSV file
that is eligible for the SQL*Loader utility of Oracle to insert the data.

For all methods there is plenty information hre, on this thread.

Good luck!

Author

Commented:
Thanks for your help schwertner.  The database server is running at the colocation center, but it doesnt contain the data we need.  The backup should contain *some* of the missing information.  My issue is how to access the file directly, bypassing oracle entirely.

Commented:
hi,

i suggest to use this


http://www.ora600.be/


thanks.


 
 
Top Expert 2008

Commented:
To access data on the backup (there are different backups in Oracle - physical, logical) you can
install a new Oracle instance (the easiest way is to use DBCA utility, but the best way is to use spare computer with the same directory structure) best with the same Instance name on different computer and to restore the DB. After that using Data Pump or Export/Import move the schemata or tables.

Author

Commented:
Thanks again Schwertner, may i pose another question to you, or any other Oracle guru's.

I spoke with someone who is supposed to be quite familiar with oracle, and we tried to setup a workstation exactly as the server is setup but we ran into issues with the configuration.

He suggested doing the following steps, and im looking for someone to verify if a) it will work and b) is it safe to do, is there any chance of not being able to get the server back up.

1. Stop the oracle services
2. Rename,  the folder containing the current .dbf and control files. ie(from asop to asop1)
3. Move the asop directory from the backup from last week to the same directory where the original was.
4.Start the oracle services
5. exp data from table
6.stop the oracle services again
7. Delete the asop directory from the backup, and rename the asop1 back to asop.
8. Start oracle services again.

This sounds like it would work in theory, but due to my lack of experience with oracle, and databases in general, i would like some confirmation one way or another.

Thanks for everyones help.
-mm
Top Expert 2008

Commented:
Normally this is a good way and is called cold ofline backup/restore. But I will add to the set you store also the password file and SPFILE (this is the parameter file.
One disadvantage of this method is the big size of the files - you will store also the unused space ...

Commented:
My opinion: spilt supportsolutions {23050650} and schwertner {23050729}.

Commented:
Wisdomforce FastReader extracts the data directly from data dbf file. It called DIrect method extract.
Here is a link to fastreader http://www.wisdomforce.com/products-FastReader.html