Recover dropped user using Flashback database

Here I demonstrated How to recovered dropped user using flashback database without data loss.

Condition:

• Archivelog Mode
• Flashback Mode Enabled
• Time at which user dropped should be within the db_flashback_retention_target and all the flashback and archivelogs should be available
• There should not be any NOLOGGING operation for any of the objects in that user’s schema.

Step 1: Check Flashback Mode  & Retention Period

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> show parameter flash;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     1440

Step 2: Create the user

SQL> create user flashtest identified by flashtest default tablespace testdb;

User created.

SQL> grant connect,resource,dba to flashtest;

Grant succeeded.

SQL> conn flashtest/flashtest;
Connected.
SQL> create table emp(ename varchar2(20),city varchar2(20));

Table created.

SQL> insert into emp values('azar','riyadh');

1 row created.

SQL> insert into emp values('kareem','dubai');

1 row created.

SQL> insert into emp values('azmi','chennai');

1 row created.

SQL> insert into emp values('idress','riyadh');

1 row created.

SQL> insert into emp values('ajmal','chennai');

1 row created.

SQL> commit;

Commit complete.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
18-DEC-10 12.14.21.137000 PM +03:00

Step 3: Drop the user

SQL> conn / as sysdba
Connected.
SQL> drop user flashtest cascade;

User dropped.

Step 4: startup mount stage & then flashback the database with timestamp

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             335544784 bytes
Database Buffers          192937984 bytes
Redo Buffers                5804032 bytes
Database mounted.
SQL>  flashback database to timestamp to_date('18-DEC-10 12.14.22','DD-MM-YY HH24:MI:SS');

Flashback complete.

Step 5: Open database read only mode

SQL> alter database open read only;

Database altered.

SQL> conn flashtest/flashtest;
Connected.
SQL> select * from emp;

ENAME                CITY
-------------------- --------------------
azar                 riyadh
kareem               dubai
azmi                 chennai
idress               riyadh
ajmal                chennai

Step 6: Export USER data

C:\Users\mazar>exp flashtest/flashtest file=d:\backup\emp.dmp log=d:\backup\emp.log direct=y consistent=y statistics='none'

Export: Release 11.2.0.1.0 - Production on Sat Dec 18 12:17:52 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user FLASHTEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user FLASHTEST
About to export FLASHTEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export FLASHTEST's tables via Direct Path ...
. . exporting table                            EMP          5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Step 7: shutdown & startup mount , recover database

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             335544784 bytes
Database Buffers          192937984 bytes
Redo Buffers                5804032 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> conn flashtest/flashtest;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Step 8: create user again

SQL> conn / as sysdba
Connected.
SQL> create user flashtest identified by flashtest default tablespace testdb;

User created.

SQL> grant connect,resource,dba to flashtest;

Grant succeeded.

Step 9: Import User data

C:\Users\mazar>imp flashtest/flashtest file=d:\backup\emp.dmp log=d:\backup\emp.log full=y

Import: Release 11.2.0.1.0 - Production on Sat Dec 18 12:20:06 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via direct path
import done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set
. importing FLASHTEST's objects into FLASHTEST
. . importing table                          "EMP"          5 rows imported
Import terminated successfully without warnings.

Step 10: Check Data Either avilable or not.

C:\Users\mazar>

SQL> conn flashtest/flashtest;
Connected.
SQL> select * from emp;

ENAME                CITY
-------------------- --------------------
azar                 riyadh
kareem               dubai
azmi                 chennai
idress               riyadh
ajmal                chennai

SQL>

Okay.Now I successfully recovered dropped user without data loss.