How to do a Time-based incomplete recovery in Oracle 9i

Posted on 2003-11-05
Last Modified: 2008-01-09
My Oracle database is in archive log mode. I have deleted some data from the database and have used the 'Recover database until time' and specified the time just before the data was deleted. I get a 'media recovery complete' message after which I do an 'alter database open resetlogs'. The data I deleted doesn't appear in the table I deleted it from.

Can someone help me please?

Thanks in advance.
Question by:atifaminuk

Expert Comment

ID: 9686264
Obviousily the data should be there if you recover just before the data was deleted.
Since you did a resetlogs you options are very limited.  I am not an expert in this scenario but I think we will need to have a time line of all types of backup of before and after the deletion before suggesting a solution.  First thing is to take a backup now.  


Author Comment

ID: 9686289
For future sake how can I recover the data to just before it was deleted?


Expert Comment

ID: 9686677
Assuming you deleted the data on 11/04/2003 at 17:00. Today you want to get that data back. Realize that is iincomplete recovery

1. BACKUP current database. You MUST do this.
2. Restore datafiles prior to deletion.  Do not restore controlfiles or redo logs files
3. connect internal or sysdba
4. startup mount
5. recover database until time 'time of latest archivelog before deletion';
6. alter database open resetlogs;
7. BACKUP database

Remember whatever data entered afterwards is lost.  You could export the table in question and restore from backup in Step 1 then import table back so you would not have any data loss.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 9686734
This probably is a stupid question but what do you mean by 'time of latest archivelog before deletion'

LVL 23

Expert Comment

ID: 9690061
That's a much better to recover the data you deleted with a short window of time IF you are in ORACLE9i.

There is a package called DBMS_FLASHBACK which is designed to recover the DMLs mistakes.
In order to use that, you have to check the "UNDO_RETENTION" parameter set to a value in the init<SID>.ora. but the default value is 10800 (seconds), that means, you can recover any data you accidently deleted within 3 HOURS with this value.
Of course you can jack this number up , but you have to realize DBMS_FLASHBACK is using UNDO segment to do that, so you will consume more UNDO space.

For example:
say at the time 10-OCT-2003 9:03 AM you delete all the records in the EMP at scott schema:

SQL> delete * from emp;

at  the time of 11:30 AM, you still can do this to recover the data:

CURSOR emp_ref is
      SELECT * FROM emp;
l_emp_ref emp%ROWTYPE;
      Dbms_flashback.enable_at_time('10-OCT-03 9:00:00');
      Open emp_ref;
            Fetch emp_ref INTO v_emp_ref;
            Exit when emp_ref%NOTFOUND;
            INSERT INTO emp VALUES (
                                                v_emp_ref.hiredate );
      END LOOP;
      CLOSE entry_ref;

Now if you do a select on emp, youwill see all deleted data reappear..

SQL>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 HOB_KEN    SALESMAN        7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT          0 17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Expert Comment

ID: 9690112
Or... you could use log miner and get the undo statements...

DrJekyll to perform an incomplete recovery after resetlogs using backup prior to resetlogs you'll need to include the controlfile from the backup - the current controlfile will have a lower SCN than the datafiles.

Expert Comment

ID: 9690297
You would have to know last good approximate time before you dropped the table.
MetaLink explains it a lot better than I.

Doc ID:  Note:114199.1
Subject:  Performing Incomplete Recovery

SVRMGR> desc v$log_history;
Column Name                    Null?    Type
------------------------------ -------- ----
RECID                                   NUMBER
STAMP                                  NUMBER
THREAD#                               NUMBER
SEQUENCE#                            NUMBER
FIRST_CHANGE#                     NUMBER
FIRST_TIME                            DATE
NEXT_CHANGE#                      NUMBER

Author Comment

ID: 9693064
Thanks seazodiac, I have tried flashback queries on a test database and they work fine but I would like to know how to do a time-based incomplete recovery on lost data. I have tried DrJekyll's solution on a test database but I cannot recover deleted data. I am still not clear on 'recover database until time of latest archivelog before deletion'. Does this mean I can only recover the database to the time of the latest archive log which could be a day old. Isn't there any way of doing an time based incomplete recovery using redo logs as they contain all transactions carried out?

Thanks in advance.

Accepted Solution

DrJekyll earned 250 total points
ID: 9697128
Unless you have something like logminer that will tell exact date, you have to use best conservative guess. Of course you would restore to a clone database export the table
then import into original table.  Another option is to us Tablespace Point-In_Time Recovery(TSPITR).  I also do an export nightly on my smaller databases for this type of event.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
grant user/role question 11 36
Read XML values 8 53
Oracle cursor lifecycle inside procedure. 2 24
format dd/mm/yyyy parameter 16 48
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

685 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