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:tempexp1.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:tempexp1.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
Comments