Donald K. Burleson Oracle Tips

Watch out for the UNRECOVERABLE clause

Be very careful using UNRECOVERABLE clause with CREATE INDEX or CREATE TABLE AS SELECT commands. 

Many Oracle professionals use UNRECOVERABLE because the actions runs fast because the Oracle redo logs are bypassed.  However, this can be quite dangerous if you need to roll-forward through this time period during a database recovery.

It is not possible to roll forward through a point in time when an UNRECOVERABLE operation has taken place.  This can be a CREATE INDEX UNRECOVERABLE, CREATE TABLE AS SELECT UNRECOVERABLE, or an UNRECOVERABLE table load.

The UNRECOVERABLE clause is a wonderful tool since it often halves run times, but you need to remember the danger.  For example, a common practice is to reorganize very large tables is to use CTAS:

Create table

   new_customer

tablespace

   new_ts

UNRECOVERABLE

as

   select * from customer;

Drop table customer;

Rename new_customer to customer;

However, you must be aware that a roll-forward through this operation is not possible, since there are no images in the archived redo logs for this operation.  Hence, you MUST take a full backup after performing any UNRECOVERABLE operation.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: