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.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

839 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