Recover Dropped table - Available Solutions - Oracle Database


Recent discussion was how to recover a dropped table and various factors were touched upon and I thought of sharing my insights which may be useful for someone who is heading a largepool of DBA and Developers.

For anyone who’s into the IT industry and supporting production databases for more than 10 year would have came across various complex, challenging scenarios in BAU support or during database migration as access or performance issues. Table drop that too from production database is not something that would happen everyday and administrators with due diligence are managing databases. But we can’t write-off saying that the scenario never happens.

When being reported about a table drop few hours from production database; as an accountable person you may need to update stakeholders who would be directly affected by the incident. Rather than reporting this as an incident, the recommendation is to suggest optimal solutions handy.

Scenario : Table dropped from the database few hours before.

Recovery Options :

1 : Recovering from recyclebin. Option can be carried out from oracle version 10g onwards.

SQL > flashback table <DRPTAB> to before drop;

2. In case the table is not available in recyclebin:

We need to select different options in this case

A) Database has Flashback enabled at Primary and Standby Database.
Follow the below steps carefully ONLY in STANDBY database:

Ø Cancel the Managed recovery process in the Standby database.
Ø Create a restore point at that point:  ***

SQL > CREATE RESTORE POINT <customised name> GUARANTEE FLASHBACK DATABASE;

Ø Flashback the database few minutes before table drop

SQL>FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('<timestamp_before_drop>', 'DD-MM-YYYY HH24:MI:SS');

Ø Open the database in read only mode
Ø Identify and export the dropped table
Ø Shutdown with immediate option and startup mount the database
Ø Flashback the database to above restore point create above
Ø FLASHBACK DATABASE TO RESTORE POINT <customised name>;
Ø Once the flashback completes, drop the restore point.
Ø Enable MRP / real-time apply process and keep monitoring DB sync
Ø The exported table can be imported in to the primary database


B) Flashback enabled at primary alone.

Depending on the criticality of the table, downtime allowed, RTO/RPO perform above steps in A)  relevant to Primary database  

If downtime is not allowed the option 3 need to be followed:

3. Table not available in recycle bin and Flashback not enabled at database :
Ø Ensure that the RMAN backups of the table.
Ø Necessary configurations are done RMAN connection to TARGET and ANCILLARY database


RMAN > RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
UNTIL TIME '<beforeDropTime>' <= here we can go for UNTIL SEQUENCE or UNTIL SCN
AUXILIARY DESTINATION '<destinationForDatafiles>'
    DATAPUMP DESTINATION '<destinationForExportBackup>'
    DUMP FILE '<dumpfilenanme>'
    NOTABLEIMPORT;

A solution is always available so we DBAs never be the nay Sayers.

Recommendation : High-level steps are provided. Derive the Step-by-Step procedure in a Sand Box and keep it ready in case of emergency.

Comments

Post a Comment

Popular posts from this blog

AI - Natural Language Processing - Lexical Processing

What is NLP ? In previous article I was talking about Training Computer Systems or Machines like how we are training kids. The computer system or the Machine should perform the task based on the interaction with the human being and the instructions provided. These interaction can be in the form of inputs which are not precoded and can be given in the form of Text, Speech, Action, Gesture, sound etc., These are part of NLP or the Natural Language Processing and so Machines can not only receive processing instructions in the form of Machine Language but also in the form of Natural Language. Text for of Natural Language contains Words, Grammar, Sentences and also context that are powerful enough to change the meaning and the processing of same can be taken care by Lexical Processing. What is Lexical Processing ? Words are the building blocks of comprehension and properties the word represent provides primary data. The retrieval of these data is what Lexical Processing does. ...
Read more

Machine Learning - A High level overview

Image
Machine Learning is automated learning through algorithms by machines or by computer systems. It'd data analytics technique and is a subset of Artificial Intelligence. It is a methodology that makes computer system or machine to learn from experience like how human beings do. In CRISP - DM the Cross-Industry Standard Process framework for Data Mining the fourth stage is the Modeling stage. In the Modeling stage Machine Learning Models are prepared.   And where the Machine Learning models are being used? Machine learning is applied where there is need for computer system to learn and to interpret without precoded programs. Machine Learning Models are used in Predictive analytics. In day today life we are asking Apple ’s Siri or Google Assistant in the mobile phone “Get me the route to Central Library” and we have the route map displayed on the screen from the current location. The mobile phone has recognized the voice and it responded. The process of recognizing vo...
Read more