Can oracle RMAN recover a single table?

Posted on 2003-03-31
Medium Priority
Last Modified: 2008-01-16
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.
Question by:tonyckx
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Expert Comment

ID: 8238511
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.
LVL 10

Accepted Solution

SDutta earned 600 total points
ID: 8240137
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question