Recover the table data back in time with Flashback Query
Flashback is a pretty useful feature in the Oracle 9i. It can save a lot of time and energy. To use this feature the UNDO_MANAGEMENT should be set to AUTO in the database. And offcourse the UNDO_RETENTION should be large enough so the Flashback Query can recove the data back in time.
We recovered one of the table data. Some records were updated incorrectly and was commited. Then we recovered the old data just before the update statement took place. we used the following procedure and we followed the metlaink document number 174425.1. We went 60 minutes back in time from the current time.
Procedure to recover data back in time with Flashback feature of Oracle 9i:
DECLARE
CURSOR FLASH_RECOVER IS
select * from LIST_GENERATION;
list_generation_recover_rec LIST_GENERATION%ROWTYPE;
begin
DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate - 60/1440);
open FLASH_RECOVER;
DBMS_FLASHBACK.DISABLE;
loop
FETCH FLASH_RECOVER INTO list_generation_recover_rec;
EXIT WHEN FLASH_RECOVER%NOTFOUND;
insert into LIST_GENERATION
values
(list_generation_recover_rec.PARTY_ID ,
list_generation_recover_rec.TRANS_DATE ,
list_generation_recover_rec.CALL_FLAG ,
list_generation_recover_rec.CAMPAIGN_SCHEDULE_ID ,
list_generation_recover_rec.LIST_TYPE ,
list_generation_recover_rec.REQUEST_ID ,
list_generation_recover_rec.CREATION_DATE ,
list_generation_recover_rec.CREATED_BY ,
list_generation_recover_rec.LAST_UPDATE_DATE ,
list_generation_recover_rec.LAST_UPDATED_BY );
end loop;
CLOSE FLASH_RECOVER;
commit;
end;
PL/SQL procedure successfully completed.
apps-sql>select count(*) from LIST_GENERATION;
COUNT(*)
----------
5693
This blog contains different things for Oracle DBA, APPS DBA and Middleware technologies. All these things should be tested first on test environments before applying in the Prod environments, please follow the standard Change control to apply all the PreProd and Prod changes. Also to make this blog more useful, please give your comments and share your experience. Thanks
Search This Blog
Subscribe to:
Post Comments (Atom)
Oracle 19c - patch 19.22 unavailability for Windows platform
Oracle 19c - patch 19.22 unavailability for Windows platform Having upgraded the Oracle 11g 11.2.0.4 database to Oracle 19c 19.3.0.0.0 whi...
-
Oracle 12c Installer error - [INS-10102] Installer initialization failed - no oraInstaller in java.library.path - make sure oraInst.lo...
-
Introduction: This post is related to describe the Report server issue with Oracle 9i AS R2. For this release of Application Server, the X S...
-
Introduction: In this article we will see, the impact of "Parallel" clause of rebuilding a partition index. We would also enable t...
No comments:
Post a Comment