Oracle flashback query failed with ORA-01555?

if you deleted/updated data by mistake in oracle. and try to recover data from flashback query.  Your query may fail with ora-01555 error:

 

SQL> l
  1  declare
  2  cursor c is select * from testt2 as of scn 5385449;
  3  begin
  4  for i in c loop
  5  null;
  6  end loop;
  7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at line 4

 

if you have no backup , then you can not recover data in this case .

 

we provide a better flashback service  ,  it can recover part of deleted data . for example .

 

SQL> set serveroutput on;

SQL> exec better_flashback_table_save('TEST2','TESTT2',2843925,'MYTVSAVE3');

table TEST2.TESTT2 @ scn 2843925 find   5568 rows , copied to  TEST2.MYTVSAVE3

PL/SQL procedure successfully completed.
the service step :
1. first expand your undo_retention parameter :
alter system set undo_retention=86400;
expand all undo datafiles
alter database datafile ‘undofile’ resize BIGGER_SIZE;
the first step avoid undo extent reused by system
2. we will provide procedure better_flashback_table_query  , check how many rows can be recovered
3. we will provide procedure better_flashback_table_save  , which will recover data and stored in new table .
4.  we can also take advantage prm-dul undelete function to help you  recover data .   pls check  https://youtu.be/hIYutqNcVBI
We provide above as a service .  contact   [email protected]

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *