Improve company productivity with a Business Account.Sign Up


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

Posted on 2003-11-05
Medium Priority
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.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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 750 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

587 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