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

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.  

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

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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
KongAWS Certified Solutions Architect - ProfessionalCommented:
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.
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
atifaminukAuthor Commented:
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.