[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1619
  • Last Modified:

Recover a single table from a backup (or even a dump of it)

Hi, i made a serious mistake and updated a filed in a critical table with null values. We have a backup but i would not like to restore the whole DB to its yesterday state. What I would like to do is to get simply a dump of the table as it was when the backup was taken and simply update the correct values to the production table. Any ideas?
1 Solution
If there is enough space on the machine.
a. You could restore the database into a different directory,
b. Recreate the control file (change the name of the instance in the new directory).
c. Bring up the database with the new name.
d. extract the required table and transfer it (exp/imp)
Steve WalesSenior Database AdministratorCommented:
The thing to watch with the method proposed by sventhan is to make sure that there's no referential integrity issues with the table you're planning to replace by this method.

If the data in the table is referenced by other tables, part of foreign keys, etc, then you may face issues with the integrity of your data.

Carefully research any relationships between the table you're playing with and other tables in the database.
You perform a Flashback Query by using a SELECT statement with an AS OF clause. You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time by means of a timestamp or SCN. It returns committed data that was current at that point in time.

Simple example:

   TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE last_name = 'Chung';

This is from 10g doc.

Which oracle version do you use?
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

My proposal depends on your undo tablespace settings and when you done the mistake , just an option not exactly what you asked for, but if useful to help for your case
Mark GeerlingsDatabase AdministratorCommented:
If you have Oracle10, the "Flashback Query" option suggested by adrian_ang is the quickest and easiest option you have.  If you don't have Oracle10, or if your undo tablespace is not large enough and/or your value for "undo retention" is not high enough, then you will have to use a more-difficult (and time-consuming) option.

One of the disdavantages of using a backup to recover an individual table is the fact that this is not what a backup was designed to do.  Database backups are designed to help recover an entire database or at least an entire tablespace or datafile.  This is one reason why I always do nightly exports in addition to nightly backups, since an export *DOES* support recovering an individual table very easily.

So, do you have a recent full-database export?
9i R2 also can do Flashback Query - http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg08sql.htm#11388

there is a good example using dbms_flashback(last topic in this chapter - Querying Data at a Point in Time (Flashback Query) ).

Have no idea if previous Oracle versions or releases support it.

Mark GeerlingsDatabase AdministratorCommented:
Yes, that is correct: Flashback Query is also supported in Oracle9.2.
Forced accept.

EE Admin

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now