Extract data from Oracle DBF files

etloraclepentahossis

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

Best Answer

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)

Related Question