Backing Up and Restoring Oracle dbf

I had a brief excursion into installing Oracle and playing around with dbf files and figured to understand how to move the actual dbf file as a backup of the database and then restore it in a completely new installation.  These are notes I made using default parameters on Oracle and I'm not crazy enough to go into detail on what the hundreds of customized Oracle installs are available out there.

An important step before copying is to shutdown your Oracle instance and database.  So use your sysdba privileges and shut it down.  Then, basically, I copied the entire oradata folder from my ORACLE_BASE path into an external hard drive.  Along with that, I also copied the flash_recovery_area which contained the RMAN information as well as the revision of the database.  NOTE: the database from oradata must match the control.ctl file from flash_recovery_area.  If you startup the database again, the ctl file will be different and the backup won't work.

After reinstalling Oracle, I moved the oradata and flash_recovery_area from the fresh install into another folder (as precaution) and copied over the ones from my hard drive.  Then, I did:

SQL> startup mount;

SQL> recover database until cancel;

SQL> alter databse open resetlogs;

Then you can log into SQL*Plus and verify that the data is intact.  Afterwards, shut the database down.  This seems to take a long while to do but it will finish eventually.

Better Oracle Exception Handling

While working with Oracle, I've learned more and more on better ways of handling exceptions.  There are some major specifics that need to be taken note of.  raise_application_error only works with custom error numbers.  You cannot use it to make Oracle throw just any exception.  Normally, you would use a predefined name like this: 

begin

      some select statement...

exception when no_data_found then

      --handle exception

end;

Sometimes, the Oracle error you want to handle is just not predefined.  So, you'd need a better way of handling errors and throwing some meaningful error messages back.

var b_err_code number;

begin

      some select statement...

exception when others then

      :b_err_code = SQLCODE;

      if :b_err_code = '-00904' then

          --handle exception

      else

          raise_application_error(-20000, 'Some Customized Error Message: ' || :b_err_code || ' Actual SQL Error: ' || SQLERRM);

      end if;

end;

Some points of interest here.  Using when others then allows you to handle all other cases.  Over here, we store the SQL error number to the bind variable b_err_code.  In Oracle, SQLCODE returns exactly that and in our example, we are expecting to handle ORA-00904 via the if condition.  We also have an else part where we generate our own application error.  We also use SQLERRM to return a human readable version of the SQL error message received, that would otherwise had gotten lost in the call stack.  Hope this little bit helps other Oracle and SQL developers.

Random Thoughts: Linux stuff

I've had to deal with some Oracle apps I've downloaded the other day and I needed JDK which I normally do not like to install.  I ended up installing openjdk7-jdk (don't use jre), and I needed to figure out where the Java path is.  It's in /usr/lib/jvm/java-7-openjdk-xxx/.

The linux command pstree -p is good at showing a list of commands running.  I had a problem with apt-get updating packages when it got stuck on flash.  Killing package-data-do got it sorted.

Finally, I loved Mass Effect 3.  Great game!

ORA-01873

Well, I came across some curious issue with Oracle where in I had to increment a timestamp by a certain number of days.  At first, I thought it would be as simple as something like:

SQL> select systimestamp + 2 from dual;

I quickly realized though that the result of this would effectively drop the time component.  Makes sense though as Oracle implicitly converts values so perhaps it was being converted into date (without the time data) before the addition operation.

After some research, I found that preserving dates means using interval in this fashion:

SQL> select systimestamp + interval '2' day from dual;

Well, while this seems to work, the timestamp column I have on my table doesn't work.  Instead, I get this error!

ERROR at line 1:

ORA-01873: the leading precision of the interval is too small

Good grief.  After mucking around with Oracle and reading some stuff up, I came across a rather helpful article by Philip Greenspun.  What I ended up doing was roughly like this:

SQL> select some_timestamp + interval '2' day(3) from (...)

Damn Oracle and their implicit datatype conversions!

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

I ran into this issue today and thought it was worth blogging about to keep track of things.  Basically, this just means that the Oracle index is unusable, broken, loading, whatever.  To resolve this, you might need to rebuild the index or drop the index and create a new one.

Some useful SQL:

select owner, index_name 

  from all_indexes 

where domidx_status != 'VALID' or domidx_opstatus !='VALID';

This will list down the bad Oracle indices for you to fix.

drop index [index_name];

alter index [index_name] rebuild;

create index [index_name] on [expression];

I think these are self-explanatory.

Reading Oracle Exception Errors

Something I've never really been accustomed to is deciphering Oracle's cryptic feedback.  Those OCI_NO_DATA errors are downright frustrating, but at least I know better now.  Always have exception handlers (exception when no_data_found) your blast triggers.  And learn how to read Oracle's line numbering!