How to recover dropped tables in oracle using flashback technology.

Hi DBA-Mates,
This is very important which we all DBA must know. And hope some experiences DBA already knowing it, no doubt. Because we are human and we can do mistakes. Suppose by mistakes one of any tables dropped or deleted then what we need to do? Either in Test Server or Prod Server. As per my experiences, I am sharing the below concepts and steps to recover the either dropped or deleted tables.

1. First, we need to place the database in archivelog mode and enable flashback.
Solution:
Check the database log_mode staus and flashback status:
$sqlplus '/as sysdba'

SQL> select name,log_mode,open_mode,flashback_on from v$database;

NAME      LOG_MODE     OPEN_MODE  FLASHBACK_ON
---------      ------------            ----------         ------------------
XYZ       NOARCHIVELOG  READ WRITE      NO

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

SQL> show parameter flash

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

 We need to shut down the database and start the database in mount status to enable the Archivelog and Flashback ON, as shown below command.

a. Shuting down database:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

b. Startup the database in Mount status:
SQL> startup mount
ORACLE instance started.

Total System Global Area 1090519040 bytes
Fixed Size                  2020128 bytes
Variable Size             301993184 bytes
Database Buffers          771751936 bytes
Redo Buffers               14753792 bytes
Database mounted.

c. Enabling the archivelog:
SQL> alter database archivelog;

Database altered.

d. Setting flashback ON:
SQL> alter database flashback on;

Database altered.

e. Change the database in Open status:
SQL> alter database open;

Database altered.

f. Checking again the status of log_mode and flashback status:
SQL> select name,log_mode,open_mode,flashback_on from v$database;

NAME      LOG_MODE     OPEN_MODE  FLASHBACK_ON
---------      ------------              ----------          ------------------
XYZ            ARCHIVELOG       READ WRITE       YES

2.Now, We will create a table as shown below:
As user Scott, create a table dept1 (deptno number(10),dname varchar2(30)) and added two rows:

SQL>show user
Scott
SQL> create table dept1(deptno number(10),dname varchar2(30)) tablespace users;

Table created.

SQL> insert into dept1 values(&a,'&b');
Enter value for a: 10
Enter value for b: hr
old   1: insert into dept1 values(&a,'&b')
new   1: insert into dept1 values(10,'hr')

1 row created.

SQL> /
Enter value for a: 20
Enter value for b: finance
old   1: insert into dept1 values(&a,'&b')
new   1: insert into dept1 values(20,'finance')

1 row created.

SQL> commit;

Commit complete.

3. Now we will drop this table to recover from flashback technology, as shown below:

Drop the table dept1 and recover the table using flashback technology

SQL> select ROW_MOVEMENT from user_tables where table_name='DEPT1';
ROW_MOVE
--------
DISABLED

SQL> show user
USER is "SCOTT"

Dropping the table:

SQL> drop table dept1;
Table dropped.

See, here dropped table is moved to Recyclebin.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
DEPT1            BIN$rLNAjrzzjGPgQKjAZAJBkg==$0 TABLE        2011-09-12:04:21:34


4. Now, we will recover the Dropped table dept1 from flashback tech. as shown below:
SQL> flashback table dept1 to before drop;
Flashback complete.

Getting confirmations and details of the table dept1:
SQL> desc dept1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(10)
 DNAME                                              VARCHAR2(30)

SQL> select * from dept1;

    DEPTNO DNAME
---------- ------------------------------
        10 hr
        20 finance


Now the dropped table dept1 has been recovered.

Some more useful link:

ORACLE DATABASE
ORACLE EBS R12.2 CONCEPTS
IMPORTANT SCRIPTS

INTERVIEWS QUESTIONS & ANSWERS

CAREER PLANNING TIPS

If you have any doubt and suggestions, please comment below or write to us @ Contact US