Extract data from Oracle DBF files

Question

I'm completely new to Oracle and I was provided with what appears to be an Oracle database backup (170 DBF files and 3 CTL files).

I'd like to extract the information using SSIS or Pentaho's Kettle but I'm not sure if that's possible

Can you please let me know if that's possible, and if so, what data provider should I use?

I don't have Oracle installed, so I guess it'll be hard and expensive to restore the DBFs

Thanks in advance

• Thronk: If you are new to Oracle, I have to assume this is not for a production workload. You can use a trial version to restore the database and extract the data. Also, if you have AWS account, you could use RDS to use oracle short term at fairly inexpensive price.
• Philᵀᴹ: Your best bet is to find out which version of Oracle the datafiles were created using, then download that version of Oracle and extract the table data from a running database. Extracting from raw .dbf files requires very expensive tools and can be error prone
• Justin Cave: You would need to have exactly the same version of Oracle running on the same operating system to restore the backup in order to extract information (excluding spending crazy money on emergency data extract tools). You say in comments that you have Oracle 9.2.0.6. You're not going to be able to get that without having a support contract with Oracle, requesting media, doing an install, installing patchsets, etc. And 9.2.0.6 is so old that you'll probably need an ancient version of Windows to support it.
• Justin Cave: It would make much, much more sense to get a logical backup (export either DataPump or classic) that you could load into whatever version of Oracle you want. I'm guessing that 170 dbf files implies a pretty large database so you probably can't use the express edition but depending on what you are trying to accomplish you may be able to get a relatively cheap personal edition license to cover your conversion efforts.

Answer 1

If you happen to be using the same platform (cpu/OS) it is fairly simple to get this up and running, if you know from which version of Oracle this backup is AND the backup is taken in the correct way. In your case, since all you have is ctl and dbf files, it should have been a COLD backup.

If all of the above is not in place: forget it.

If all of the above is in place:

1. create a init.ora file containing the dbname, db_unique_name, and control_files parameter. The latter one points to at least one of your ctl files. With a little luck you can guess the dbname from the file names.
2. install the correct oracle rdbms version in a location called ORACLE_HOME
3. export ORACLE_HOME=/where/your/Oracle/product/version/home_1 is
4. export PATH=$ORACLE_HOME/bin:$PATH
5. sqlplus / as sysdba
6. startup nomount pfile=your_full_name_of_init.ora from step 1
7. alter database mount - this accessed the ctl files and checks dbname. Fix dbname in init.ora if reported wrong and shutdown abort the instance to start over at step 5.
8. now you are able to look into the original files locations. If your files are in exactly the same locations as the original database thi sis easy: alter database open read only;

If any error: hire a dba to help you. If step-8 gives problems because of incorrect file locations: you can rename the files one by one, or create a text dump of the controlfile, edit that dump for the correct locations and use it to create a new controlfile.

Oracle is very flexible and many situations can be handled. In your case, your database is 9.2.0.6 on Windows. You could try to request a download from oracle support and perform an upgrade. This is the easiest is your dbf locations are the same as on the original system (they are in the controlfile)

• Daniel Martinez: Hi, the version is 9.2.0.6.0, which I guess is 9i. I noticed 9i is no longer available in Oracle's webpage. Do you know if the steps above can be achieved using a later version (11g or 12c)? Thanks
• user953: doubt it. Should start with 10g. What platform?
• Daniel Martinez: It's Windows Server 2003, should I use the same exact platform? I was going to try it using Windows Server 2008 R2 Thanks
• user953: Chips and OS. if you can run 9.2 on that, it is OK. I happen to have a 10.2.0.1 for Win32 installer. 655M. Do you have the original file locations for the dbf's, in that case you could try an upgrade.
• user953: you could use support.oracle.com/epmos/faces/ui/km/… to request a download url for 10.2.0.1, if you have a support contract.