This is a very interesting post as you would find yourself in this situation quite often. There would be a case where User/Application guy reports an accidentally dropped table and asks DBA to restore it and suppose in that case you don’t have a latest logical backup (via datapump), neither using recyclebin or even if using recyclebin the user has dropped the table with purge option and on the top of it you do not have flashback enabled :). In this case your only option is to restore the database just before the point where the table was dropped either on the same database or at another server and then after restore export – import the table to the original database.

You’ll find several documents, articles for this case but the place where you’ll face problem is (assuming the user does not have the record for time so you don’t have the luxury to used Performing Time-Based Recovery), if Performing SCN-Based Recovery then how to find the SCN just when the table was dropped. And trust me there are very few articles documents describing that they just assume that you are smart enough to figure that out, well I was not that smart during the first time I faced this situation, so for all other guys like me, here it goes:-

STEP 1:- Specify a set of archived redo log files for LogMiner to analyze. As we do have a approximate time (but we can’t afford to miss even one archive redo log file as it contains hell lot of changes than we think it has) we can feed the archive logs generated during that time range (select sequence#, first_change#, first_time from v$log_history order by first_time;) to the Logminer.

• SQL> connect sys/foo as sysdba
• SQL> exec dbms_logmnr.add_logfile(- logfilename=>'<Log file name with path>’, – options=>dbms_logmnr.addfile);

You can use the dbms_logmnr.add_logfile procedure as many times as you want to add all the suspected archived redo log files where you think the Drop DDL and hence the SCN would be. To finally see the list of logfiles that will be mined by logminer use (Select filename from v$logmnr_logs;).

STEP 2:- Start Logminer by using the current data dictionary.

• exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

STEP 3:- Query the v$logmnr_contents view to find the SCN for the transaction of interest (In this case DROP).

• select operation, scn from v$logmnr_contents where table_name='<TABLE NAME>’ and operation=’DROP’;

The output would give you the required SCN number.

STEP 4:- End the logminer session.

• SQL> exec dbms_logmnr.end_logmnr();

As now once we have received the SCN of interest we can proceed with Performing SCN-Based Recovery

I hope this post would be helpful in scenarios when you know the recovery process but as there is lot of stress on DBAs during recovery scenarios even googling at that time for the syntax is an overhead, hence the idea is to make a repository that cover all possibile recovery scenarios step by step with syntax of commands at a single destination. I would try to cover all the scenarios in my subsequent posts. Please comment if any of the curious readers are interested to know the internals of the recovery process like which process does that, what information is in redo that is applied to the datafiles to recover them to point in time and etc.

PLEASE COMMENT IF YOU FACE ANY ERRORS WHEN PERFORMING THIS RECOVERY SO THAT WE CAN WORK ON THOSE AND ENHANCE THIS REPOSITORY FOR OTHER READERS.