Mir Sayeed Hassan – Oracle Blog

How to Recover Loss of data without a Backup in Oracle database 11gR2 (11.2.0.4)

This scenario is considering as we don’t have any of database backup like RMAN, Restore point, Flashback, Recyclebin, Therefore we can recover only the DML Operation data loss

In our case the user is accidentally deleted the records from table & committed, but we should recover the data, Therefore we can recover if the UNDO_RETENTION parameter is set in database, by default the UNDO_RETENTION Parameter is set to 900sec, So we can recover the data within that particular time itself.

Database status

SQL> select instance_name,status,version from v$instance;

INSTANCE_NAME    STATUS       VERSION
----------- ------------ -----------------
testdb           OPEN         11.2.0.4.0

Now verify the backup policy set in database

DB Restore point

SQL> select * from v$restore_point;
no rows selected

Flashback

SQL> select flashback_on from v$database;

FLASHBACK_ON

-------------
NO

SQL>  show parameter recyclebin

NAME                                 TYPE        VALUE

-------------- ----------- ------------------------------
recyclebin                           string      OFF

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE

------------- ----------- ------------------------------
undo_retention                       integer         900

Therefore there is no backup policy is set in database & only the UNDO_RETENTION Parameter is set.

Create a user & perform some DML Operation

SQL> create user mir identified by mir;
User created.

SQL> grant connect,resource to mir;
Grant succeeded.

Connect to user created

SQL> conn mir/mir;
Connected.

SQ> sho user
USER is "MIR"

Create a table & do some DML Operatoin

SQL> create table mir_test(eno number(20),name varchar2(20));
Table created.

SQL> insert into mir_test values(1,'mir');
1 row created.
SQL> insert into mir_test values(2,'sayeed');
1 row created.
SQL> insert into mir_test values(3,'hassan');
1 row created.

SQL> insert into mir_test values(4,'india');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from mir_test;

       ENO NAME
------------------
         1 mir
         2 sayeed
         3 hassan
         4 india

Connect to the SYS User & perform the flush buffer_cache  —  It clear all data from the shared pool in the SGA

SQL> sho user
USER is "SYS"

SQL> alter system flush buffer_cache;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.
SQL> /
System altered.

Connect to user MIR

SQL> conn mir/mir;
Connected.

Check the current system date & time

SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "SYSDATE" from dual;
SYSDATE
----------------------
13-OCT-2019 20:53:18

Delete the records from user MIR

SQL> delete from mir_test;
4 rows deleted.

SQL> commit;
Commit complete.

SQL> select * from mir_test;
no rows selected

Solution: Connect to the sys user & perform below steps

SQL> conn / as sysdba
Connected.

SQL> flashback table mir.mir_test to timestamp TO_TIMESTAMP('13-OCT-2019 20:53:18','DD-MON-YYYY HH24:MI:SS');
flashback table mir.mir_test to timestamp TO_TIMESTAMP('13-OCT-2019 20:53:18','DD-MON-YYYY HH24:MI:SS')
                 *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

Note: The above error message clear as to the nature of the failed statement & Enable the row movement so that quicker you can recover the table.

SQL> alter table mir.mir_test enable row movement;
Table altered.

SQL> flashback table mir.mir_test to timestamp TO_TIMESTAMP('13-OCT-2019 20:53:18','DD-MON-YYYY HH24:MI:SS');
Flashback complete.

SQL> conn mir/mir;
Connected.

SQL> select * from mir_test;

       ENO NAME

--------------------
         1 mir
         2 sayeed
         3 hassan
         4 india

There the table is recover.

=====Hence tested & verified in our test env====