Question:
I accidentally dropped a table column in production, and I will
be fired for sure unless I can restore the column before by boss
finds out that I dropped the column.
I see nothing in the Oracle documentation on restoring a dropped
table column, but there must be some way to recover a dropped
column. I tried flashback but it seems that flashback doesn't
support recovery of deleted column.
How would you restore a column that was dropped accidentally?
Answer: Yes, DBAs get fired for dropping production table columns, so
act quickly! DDL like an
alter table drop column
cannot be
rolled back.
The best way to remover this is to restore your last production
backup into a test database, and roll-forward the table in question.
Then, re-create the column in production and copy-over the column
data using a db_link!
You still have several options for recovering a dropped table
column, restoring the production database into a test area and
recovering the column separately:
-
Data pump exports - If you care
about the data in this table, you would have exported it
nightly.
-
RMAN - You can recover a
single dropped table by restoring the entire database into your
test environment using RMAN, and then roll forward. You can then
extract the table and copy it from test to production using CTAS
over a database link and then re-add the indexes and
constraints.
-
LogMiner - Oracle LogMiner
can used to recover a dropped table. See here, the
steps to recover a dropped table using LogMiner.
-
Flashback - It's easy to recover a
dropped table with
these steps in flashback.
