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

Posted on 2007-10-11
Last Modified: 2012-05-05
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?
Question by:gerodim
    LVL 18

    Expert Comment

    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)
    LVL 22

    Expert Comment

    by:Steve Wales
    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.
    LVL 5

    Accepted Solution

    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?
    LVL 5

    Expert Comment

    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
    LVL 34

    Expert Comment

    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?
    LVL 5

    Expert Comment

    9i R2 also can do Flashback Query -

    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.

    LVL 34

    Expert Comment

    Yes, that is correct: Flashback Query is also supported in Oracle9.2.
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
    The viewer will learn how to back up in Windows 7 with native tools. This should be the first step. Third party tools should also be used. Access the Backup and Restore options: Click on the windows 7 start ball in the lower left corner of the scree…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now