Recover the Data without Backup !

So I had one interview, wherein got below question which left me confused. As per interviewer there is a solution but at that time I was not aware of the solution.

The question goes like...

There is a database which is having size in TBs and accidentally one of the developer deleted all rows of a business critical table and committed the changes. The table is not huge, it was not having many rows at that time. There is no flashback enabled. Now you've to recover deleted rows. So what would you do to recover the data in minimal time?

So lets create the scenario in our Virtual Environment,

Will make sure that FLASHBACK is disabled in the database:

18:54:08 PROD01 > select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
--------- -------------------- ------------ ------------------
PROD01    READ WRITE           ARCHIVELOG   NO

For simulation purpose we'll use ORDERENTRY_METADATA table, it has 4 rows.

19:03:33 PROD01 > SELECT COUNT(9) FROM soe.orderentry_metadata;

  COUNT(9)
----------
         4

Now lets simulate the actual scenario which was asked in the interview:

19:03:33 PROD01 > SELECT COUNT(9) FROM soe.orderentry_metadata;

  COUNT(9)
----------
         4

19:03:44 PROD01 > DELETE FROM soe.orderentry_metadata;

4 rows deleted.

19:05:23 PROD01 > commit;

Commit complete.

19:08:21 PROD01 > SELECT COUNT(9) FROM soe.orderentry_metadata;

  COUNT(9)
----------
         0

19:08:24 PROD01 >

Scenario is produced now, lets work on the solution part. 

Here still FLASHBACK TABLE will work, yes even if the FLASHBACK is off.

We'll try to do using FLASHBACK TABLE, but before that we'll see if AS OF TIMESTAMP works or not.

19:08:24 PROD01 > SELECT COUNT(9) FROM soe.orderentry_metadata
19:12:34   2  AS OF TIMESTAMP TO_TIMESTAMP('04-06-2021 19:03:33', 'DD-MM-YYYY HH24:MI:SS');

  COUNT(9)
----------
         4

19:13:13 PROD01 > -- It Worked !!!!!!!!! Lets just make sure that FLASHBACK is still off
19:13:39 PROD01 > SELECT flashback_on, name from v$database;

FLASHBACK_ON       NAME
------------------ ---------
NO                 PROD01

19:13:51 PROD01 >

So AS OF TIMESTAMP worked, now lets FLASHBACK the table so that our business critical table is restored with all of its data :)

19:15:10 PROD01 > FLASHBACK TABLE soe.orderentry_metadata TO TIMESTAMP TO_TIMESTAMP('04-06-2021 19:03:33', 'DD-MM-YYYY HH24:MI:SS');
FLASHBACK TABLE soe.orderentry_metadata TO TIMESTAMP TO_TIMESTAMP('04-06-2021 19:03:33', 'DD-MM-YYYY HH24:MI:SS')
                    *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


19:15:50 PROD01 > ALTER TABLE soe.orderentry_metadata ENABLE ROW MOVEMENT;

Table altered.

19:16:02 PROD01 > FLASHBACK TABLE soe.orderentry_metadata TO TIMESTAMP TO_TIMESTAMP('04-06-2021 19:03:33', 'DD-MM-YYYY HH24:MI:SS');

Flashback complete.

19:16:09 PROD01 > SELECT COUNT(9) FROM soe.orderentry_metadata;

  COUNT(9)
----------
         4

19:16:13 PROD01 > SELECT flashback_on, name from v$database;

FLASHBACK_ON       NAME
------------------ ---------
NO                 PROD01

19:16:21 PROD01 >

We've got the solution for this problem. But now question you must be having is how the FLASHBACK worked even if the FLASHBACK was off. 

Explanation:

This will depend upon the value we've set for undo_retention parameter. It will recover the data from undo and it will have the data till the time it has specified in the parameter. (Keeping in mind that the undo block which has the rows is not overwritten)

So now we've answered the question and also got the explanation. Lets validate this if this in fact is true or not. We'll perform below steps in order to validate.

1. Check the value of undo_retention
2. Set the value to minimum, in our simulation we'll set the value to 3 mins
3. Perform delete on a table
4. Wait for 3 mins
5. Now we'll validate if we're able to FLASHBCK the table using any of the 2 methods which we tried above.

20:32:48 PROD01 > col name for a15
20:32:50 PROD01 > SELECT name, value/60 Value_Mins FROM v$parameter WHERE  name = 'undo_retention';

NAME            VALUE_MINS
--------------- ----------
undo_retention           5

20:32:54 PROD01 > ALTER SYSTEM SET undo_retention=180 scope=both;

System altered.

20:32:58 PROD01 > SELECT name, value/60 Value_Mins FROM v$parameter WHERE  name = 'undo_retention';

NAME            VALUE_MINS
--------------- ----------
undo_retention           3

20:33:02 PROD01 > CREATE TABLE soe.orderentry_metadata_1 AS
20:33:06   2  SELECT * FROM soe.orderentry_metadata;

Table created.

20:33:11 PROD01 > SELECT count(9) FROM soe.orderentry_metadata_1;

  COUNT(9)
----------
         4

20:33:15 PROD01 > DELETE FROM soe.orderentry_metadata_1;

4 rows deleted.

20:33:18 PROD01 > commit;

Commit complete.

20:33:22 PROD01 > SELECT count(9) FROM soe.orderentry_metadata_1;

  COUNT(9)
----------
         0

20:33:24 PROD01 > exec dbms_lock.sleep(180);

PL/SQL procedure successfully completed.

20:36:31 PROD01 > SELECT Count(9) FROM soe.orderentry_metadata_1
20:36:38   2  AS OF timestamp to_timestamp('04-06-2021 20:33:11', 'DD-MM-YYYY HH24:MI:SS');
SELECT Count(9) FROM soe.orderentry_metadata_1
                         *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


20:37:16 PROD01 > FLASHBACK TABLE soe.orderentry_metadata_1
20:37:38   2  TO TIMESTAMP to_timestamp('04-06-2021 20:33:11', 'DD-MM-YYYY HH24:MI:SS');
FLASHBACK TABLE soe.orderentry_metadata_1
                    *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


20:38:02 PROD01 >

From explanation to validation looks like the data is retrieved from undo tablespace only. As in validation part it is clear that until we've undo blocks in undo tablespace, we can recover the data. But only DML operations will be able to recovered.