|
LESSON 23
How to recover a missing table using RMAN backup
You have a user that
dropped its table at 8:30am on March 3, 2014. He comes to you and ask
you to recover the lost table using RMAN backup.
Assumptions:
Oracle12c or higher
Your database is in
the archivelog mode
You have a good RMAN
backup
Your username:
scott
His/her tablename:
mytable
Notice that on the
Oracle12c, you can perform any SQL statements at RMAN without using SQL.
Once you have the time,
check the SCN (System Change Number) associated with the date and time
that your user provided to you.
SQL> select
timestamp_to_scn(to_date('03-04-2014 08:31:00','MM-DD-YYYY
HH24:MI:SS')) SCN from v$database;
This output shows the snapshot from a specific date and time
('03-04-2014 08:31:00').
The date and time has to
be before he dropped the table.
Make an auxiliary
destination so RMAN can perform its data manipulation to recover table.
Notice that RMAN will export the data from backup and then import it to
your database. Therefore, it needs space to do so.
# mkdir /recovery_area/recover_table
Login to RMAN and
recover table using created auxiliary destination and SCN.
# . ./.profile
# rman target /
RMAN> RECOVER
TABLE scott.mytable UNTIL SCN 2733999 DESTINATION "/recovery_area/recover_table";
Starting recover at "
Performing export of tables "
Performing import of tables "
Auxiliary instance file " Deleted
Finished recover at "
RMAN> exit
Now, call the user to
check the table was recovered successfully.
SQL> connect /
as sysdba
SQL> SELECT
count(*) FROM scott.mytable;
Good Luck!
|