Loss of undo datafile

Down Time Required : Yes

Archive Log Mode : Yes

Data Loss : No

1. Shutdown the database and take cold backup

2. Insert some data into test table and delete the undo datafiles and shut abort the database

3. Copy a backup of undo datafile and mount the database

4. Recover the undo datafile

SQL> alter database datafile 3 offline;

SQL> alter database recover automatic datafile 3;

SQL> alter database datafile 3 online;

SQL> alter database open;

To find the datafile number, query the file_id column from dba_data_files

select file_id,file_name,tablespace_name from dba_data_files where tablespace_name like '%UNDO%';

Since the database need not be opened with resetlogs option, previous backup taken is still valid for future recoveries.

5. Check if all the records are present

That's it!!!

Regards

Charan