tonyckx
asked on
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.
Thanks a lot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scenario
• 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/
arch_6.rdo
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';
TABLE_NAME OWNER
------------------ ---------------------
EMP PETER
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.