How to read Oracle Datafiles without Oracle RDBMS

In the following post we will explain how to access to the information of Oracle datafiles without the original database, only with the database files.

This is particularly useful in case that you had some kind of disaster and the information has to be recovered at any cost. Of course, making a restore with RMAN, Data Pump or other methods would be desirable… but we all know that some people just work with databases in production without a backup system right?

If you ask Oracle support how to make a recovery ONLY with some datafiles, they will tell you that this is impossible. However, by definition these files contain information, although it is encrypted. Let’s see how can we access to this information:

1. Trying to restore into another database.

There is a slight that you are able to restore a datafile from one database into another, just having the .dbf. The concept is that you need to modify the header of this datafile, to fool the database and make it belive that the datafile belongs to it.

You will still need a foresnic tool to modify the datafile and addapt it to a new database. But the advantage is that you will be able to make a clean export and restore it in a new database if you are successful.

Personally, I have never succeded with this method, although it would be technically possible.

2. DBF Viewer and Editor

There is an application for Windows that you can use to read directly the datafiles using ODBC or BDE. It’s called DBF Viewer and Editor, and you can find more information here:

http://dbf-viewer-editor.com/read-oracle-dbf-file.html

The license costs 71€, but you can try it for free, although you won’t be able to read the whole datafile.

3. BBED

For many years, BBED has been considered a secret tool, only known by a few, because it allows not only to read directly from datafiles, but also to write on them.

Nowdays, it is not included in Oracle installations anymore, but you can still use it on older versions.

The advantage of BBED is that it’s a free tool. It is really complex, and even if you are able to access the data, you can’t read this information easily, and much less perform selects. But if the information is really critical, and you don’t have any kind of backups, this could be a really valuable option.

I have personally used this tool, and I can assure that it works.

For more information, you can find a full explanation in this link:

http://orafaq.com/papers/dissassembling_the_data_block.pdf

Please keep in mind that this is the last resource, in case that restoring with RMAN or other backup methods is completely impossible.

Never try this tools in production, because overwritting a datafile will probably cause a database corruption, and you won’t be able to work with the database anymore. You should always work with a copy of the datafile.

Of course, there are more datafile foresnic methods. If you have worked with other programs and you have a better solution for this problem, please don’t hesitate to make a comment and share your knowledge with us.

Changing sys password in RAC databases

If you ever have worked with a RAC database, and you have changed a normal user password, you will notice that it has no difference between a single instance database. You just have to perform “ALTER USER xxx IDENTIFIED BY yyyy” and your password will be changed.

However, today I had a problem with a RAC database. I had to change the SYS password, and I did it the same way I would do in a single-instance database. But when I tried to connect with sys user, the error “ORA-01017: invalid username/password; logon denied” appeared. What I was doing wrong?

After a few thoughts, I found the solution. SYS password is instance specific in RAC databases, so you have to change it in every single instance. That’s all.

Grid Control: EMD upload error

Today I was installing the grid control agent in a new server, and I probably mistyped the password during the installation. I didn’t notice at the start, but the client was succesfully installed. However, it didn’t appear on my grid control maintenance window.

I connected to the brand new server and issued the following command:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl upload agent

And it produced this error:

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

This error was caused because I typed a bad password, and in order to change it, I had to remove first the password:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl unsecure agent

And then, securing the agent again:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl secure agent

It asked me for a new password, which I typed well this time, and everything started working fine.