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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retreiving column names in Windows but not in Unix 11 57
Oracle dataguard 5 41
Sybase and replication server 13 37
update statement in oracle 9 28
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

789 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