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!
- 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).
- 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.
- You can use Oracle 10g's new Flashback technologies to revert the table to a previous point in time.
- 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.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.