Problem solve Get help with specific problems with your technologies, process and projects.

Help speeding up complicated backup process

Can "run recover database using backup controlfile until cancel" help us in this and if yes then how?

Currently we have a table in an Oracle database. The size of this table is huge (around 30 to 35 GB). Also, there are a few indexes on this table. This table grows every month by around 2-3GB of data.

We have to load the data in this table on a daily basis and that too in a sequential manner. We take the backup of this table every week. However, if by any chance we miss to load the data in this table for any day and we load data for the next day, we have to reimport the backup of an earlier week and then do the usual processing again. This really takes lot of time, sometimes even days.

Is there any way by which we can just rollback to the day before which we missed the usual process? (Note that we have a few processes which update the data loaded in this table. Hence the data load has to be committed every day.)

Can "run recover database using backup controlfile until cancel" help us in this and if yes then how? Or is there any other way? This is really critical. Your help is greatly appreciated! Thanks in advance!

There are a number of options at your disposal.
  1. You can perform a Point In Time Recovery (PITR) from a previous backup. This involves restoring the database (or tablespace) and recovering up to a point in time. If only doing this on a tablespace, this is called Tablespace Point In Time Recovery (TSPITR).
  2. One trick I've used in the past is to have two copies of the table. You can load to one copy. Then point a synonym to COPY1 (your application refers to the synonym). COPY2 will serve as a 'backup' copy of the table should you need to go back. At some time, you update COPY2 and point the synonym to this copy of the data. This will require twice the storage space, but to be honest, 35GB is not that large of a table by today's standards.
  3. You can use Oracle 10g's new Flashback technologies to revert the table to a previous point in time.
  4. After you complete a load, you can mark the tablespace READ ONLY and create a Transportable Tablespace copy of this tablespace. To quickly revert to this backup copy, drop the tablespace and then plug in the copy of tablespace. This option works best if you put this single table in a tablespace all its own.
This was last published in April 2007

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.