Recovering Dropped User using Flashback Database

Recovering Dropped User using Flashback Database 

This article is for DBA's of all experience level.

Prerequisites

- Database Must be in Archive log mode
- Database Must be in Flashback database mode.
- 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

DETAILS

This test case consists of below steps

Step1 : To Ensure that enough flashback logs are available to do the Flashback database
Step2 : Simulation of the Human error - User Drop
Step3:  Do Flashback Database
Step4: Open the flashback database in Read Only Mode
Step5: Check for the user availability and its data
Step6: Take FULL export of the dropped Schema
Step7: Do FULL recovery of the database to Bring it to current state
Step8: Import the user in order to solve the human error - user drop

  ***  Recovery of the Dropped user without a single data loss ***

1. Make sure that the database is in flashback mode

SQL> select flashback_on from v$database;

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

SQL> show parameter flash

NAME                                          VALUE
-------------------------------      -----------
db_flashback_retention_target          1440

SQL> select oldest_flashback_scn,oldest_flashback_time from $flashback_database_log ;

OLDEST_FLASHBACK_SCN   OLDEST_FLASHBACK_TIM
---------------------------------   --------------------------------
24300236                                       29-MAY-2009 12:56:13

2. To simulate the issue lets create few tables in a exhisting user and then drop it.

SQL> conn flashback_test/flashback
Connected.

SQL> create table flashback_testing ( col1 varchar2(20));

Table created.

SQL> insert into flashback_testing values ( 'flashback testing');

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
29-MAY-2009 14:32:50

SQL> conn / as sysdba
Connected.

SQL> drop user falshback_test CASCADE;

User dropped.

3. In order to recover lost schema, use the flashback database feature to a time approximately before the user dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1290208 bytes
Variable Size 142606368 bytes
Database Buffers 142606336 bytes
Redo Buffers 2904064 bytes
Database mounted.













SQL> flashback database to timestamp to_date('29-MAY-2009 14:30:00','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

4.Open the database in Read Only mode

SQL> alter database open read only; 

Database altered.

5. Check for the user and its corresponing data

SQL> conn FALSHBACK_TEST/flashback
Connected.
SQL> select * from flashback_testing;

COL1
--------------------
flashback testing

SQL> exit

6. Take an export of the user


C:\>exp owner=falshback_test file=E:\temp\exp1.dmp

Export: Release 10.2.0.4.0 - Production on Fri May 29 20:23:40 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 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 FALSHBACK_TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user FALSHBACK_TEST
About to export FALSHBACK_TEST's objects ...
. about to export FALSHBACK_TEST's tables via Conventional Path ...
. . exporting table              FLASHBACK_TESTING          1 rows exported
. exporting synonyms
. exporting views
....
. exporting statistics
Export terminated successfully without warnings.

7. Shutdown the database and do a recovery of the database to bring it to the CURRENT state

SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 

SQL> startup mount; 
ORACLE instance started. 
Total System Global Area 289406976 bytes 
Fixed Size 1290208 bytes 
Variable Size 142606368 bytes 
Database Buffers 142606336 bytes 
Redo Buffers 2904064 bytes 
Database mounted. 

SQL> recover database;
Media recovery complete.

SQL> alter database open;

Database altered.

Now this database is in curent state without the dropped user but we have full export dump of that user before its dropped.

8.Crate the dropped user and import it back from the export dump

SQL> create user flashback_test identified by flashback  default tablespace users quota unlimited on users;

User created.
SQL> -- Grant the necessary priviledge to that user

C:\>imp full=y file=E:\temp\exp1.dmp

Import: Release 10.2.0.4.0 - Production on Fri May 29 20:26:14 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing FLASHBACK_TEST's objects into FALSHBACK_TEST
. . importing table "FLASHBACK_TESTING" 1 rows imported
Import terminated successfully without warnings.

Now this database is upto date with the dropped table. NO DATA LOSS :-)

LIMITATION:

1. If any of the flashback log or archivelog are lost then the above option won't work.
2. If there is  NOLOGGING operation after recovery those NOLOGGING transacrion blocks will be soft corrupt.Hence avoid using FLASHBACK DATABASE with a target time or SCN that coincides with a NOLOGGING operation



Note---> This informationmation taken from oracle metalink. all copy rights oracle only.