• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2580
  • Last Modified:

Can oracle RMAN recover a single table?

Hi, currently I'm using rman to backup my database.  I can use it to recover entire database, datafile, tablespace.  If I drop a table in my database, is RMAN can recover it?  If yes, please help to provide me the script.  Beside that RMAN can recover entire database, datafile, tablespace, what additional feature RMAN can do?  (Refer to Oracle 8i (8.1.7) version)

Thanks a lot.
1 Solution
The answer is no. If your DB runs in archivelog use the following method

• The current time is 12:00 p.m. on 9-Mar-99.
• Your training DBA just told you he dropped the
employee (EMP) table.
• The table was dropped around 11:45 a.m.
• Database activity is minimal because most staff are
currently in a meeting.
• The table must be recovered.

1 If the database is opened, shut it down by using either the NORMAL or IMMEDIATE or TRANSACTIONAL options.
2 Mount the database.
3 Restore all data files from backup (the most recent if possible):
UNIX> cp /disk1/backup/*.dbf /disk1/data/
UNIX> cp /disk2/backup/*.dbf /disk2/data/
UNIX> ...
NT> copy c:\backup\*.dbf c:\data\
NT> copy d:\backup\*.dbf d:\data\
NT> ...
4 You may need to restore archived logs. If there is enough space available, restore to the LOG_ARCHIVE_DEST location or use the ARCHIVE SYSTEM ARCHIVE LOG START TO <LOCATION> or SET LOGSOURCE <LOCATION> to change the location.
5 Recover the database:
SQL> recover database until time ‘1999-03-09:11:44:00’;
ORA-00279: change 148448 ...02/29/98 17:04:20
needed for thread
ORA-00289: suggestion : /disk1/archive/
ORA-00280: change 148448 for thread 1 is in
sequence #6
Log applied.
Media recovery complete.
6 To synchronize data files with control files and redo logs, open
database by using RESETLOGS option:
SQL> alter database open resetlogs;
SQL> archive log list;
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
7 Before performing the whole closed database backup, query the EMP table to make sure it exists. If you get the following error:
ORA-00942: table or view does not exist
then locate the schema for the table by using:
SQL> select table_name, owner
2 from dba_tables where table_name = 'EMP';
------------------ ---------------------
1 row selected.
Note: If no rows are selected, then the table has not been restored correctly. You will need to recover to an earlier point-in-time, otherwise perform the backup.
Note: LogMiner can help you to find the right time of the DROPstatement.
8 When recovery is successful and the backup has completed, notify users that the database is available for use, and any data entered after the recovery time (11:44 a.m.) will need to be reentered.

Another idea is to use LOG MINER utility.
The view V$LOGMNR_CONTENTS can be queried by the session that performed the analysis to view the log information.
SQL_UNDO explains how to undo the operation performed in the statement specified in SQL_REDO.
If you know which tablespace the dropped table is in, you can use RMAN to do a full database point in time recovery on another server skipping the other unneeded data tablespaces. This will avoid bringing down your production server for the recovery.

If you identify logseq # nnnn as the point of recovery, your RMAN script would have :

run {  
set until logseq nnnn thread 1;
# Do the tape channel allocates etc.
allocate channel d1 type disk;
# Restore must be done separately from replicate for controlfile
# Also, disk channel must be allocated immediately before restore – Oracle Bug 864173
restore controlfile to 'c:\ctl_restored.ora' from tag hot_db_bk_level0;
replicate controlfile from 'c:\ctl_restored.ora';
sql "alter database mount";  
# set newname for datafiles if required
restore database skip tablespace <skipped_tbsp1>, <skipped_tbsp2>;
switch datafile all;  
sql "alter database datafile <skipped_dtfl1> offline drop";
sql "alter database datafile <skipped_dtfl2> offline drop";
recover database skip tablespace <skipped_tbsp1>, <skipped_tbsp2>;
# sql "alter database rename file .... etc.
sql "alter database open resetlogs";
sql "alter tablespace <skipped_tbsp1> offline immediate";
sql "alter tablespace <skipped_tbsp2> offline immediate";
release channel d1;
release channel t1;
release channel t2;

Once you are able to see your table, you can export it and import it back into your original database.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now