RDBMS -- restore/recover a dropped table column, ORA-01466, 12C rman recover table option

One day , you may accidentally drop a column from one of your critical column in your Production database. I hope you won't be in this situation, but here is the facts that you need to know foır restoring/recovering a dropped column in Oracle.

• Flashback techniques doesn't apply for dropped columns. That is, you can not use flasback query or flashback table to restore a dropped column. You will get "ORA-01466 : Unable to read data -table definitions has changed"  As for the Flashback Table, it cannot rewind tables through structural changes even after a truncate table operation.
• imp(import) or impdp can be used for restoring the dropped column, as we can import the whole table from a backup dump file and then import it to another schema and lastly get the data from that table to our target table. On the other hand, you  eed to have a backup procedure, that regularly takes logical backups of the database using exp or expdp. (you can even roll forward the imported column data by analyzing the archivelogs and extracting dml operations from there using log miner or something)
• Flashback database can be used, but with this option, you will flashback your whole database. So it is not an applicable method in most of the cases.
• If the datafiles are on storage, storage snapshots can be used to create a new luns from the snapshot of the lun that contains the production's database files.  After mounting the lun created from the snapshot, the database can be opened with a different name from that lun and the table(before alter drop column operation) can be gathered from there.
• Rman full restore can be used. That is , the database backup can be restored to a different server or different location and recovered just before the table drop operation. The column data can be done gathered from that restored database using db links. However, this is a heavy and time consuming process.
• Performing RMAN Tablespace Point-in-Time Recovery (TSPITR) is also an option, but this is also heavy and it makes all the objects of that tablespace rolled back/forward to a specified point in time.
• Opening standby read/write by creating a guaranteed restore point (for taking it back) can be used, if the dropped column is not dropped from standby database yet.
• Lastly, if you are on 12C, then you have the option to restore a table using rman from rman backups. This is a new feature introduced in 12C . as in 12c RMAN has an option for 'recover table'. See: RMAN RECOVER TABLE Feature New to Oracle Database 12c Note 1521524.1 (My favorite one :)

                Rman recover table feature works with the following;

• It finds the rman backup that includes the table to be recovered (based on the point in time recovery specified for the recovery)
• It create an auxiallary database and it recovers the table until the specified point in time, into this auxiliary database.
• It then creates a export dump file that contains the recovered table.
• Lastly, it imports this export dump file into the target instance. (optional) --By default, RMAN imports the recovered tables or table partitions, which are stored in the export dump file, into the target database. However, you can choose not to import the recovered tables or table partitions by using the NOTABLEIMPORT clause of the RESTORE command.

                Here are the limitations:

• Tables and table partitions belonging to SYS schema cannot be recovered.
• Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.
• Single table partitions can be recovered only if your Oracle Database version is Oracle Database 11g Release 1 or later.
• Tables and table partitions on standby databases cannot be recovered.
• Tables with named NOT NULL constraints cannot be recovered with the REMAP option. 

Before closing this topic, I want to shed a light on a few things and share my comments on them.

Why rman creates an auxilary database and then take an export from there, must be to isolate the mission critical target environments from the restore works. Also It seems like "rman recover table" option is just like an expansion pack for the rman, because still rman restores the backup datafiles in order to restore a table from a backup.