Processing of the loss of files corresponding to the temporary tablespace and the Undo tablespace in Oracle

The temporary and undo files of the database do not need to be backed up. They can be restored after they are lost, and data will not be lost.

1. After files in the temporary tablespace are lost, they are automatically created after the database is started. No intervention is required.

Simulation: After the database is shut down, the temporary files are deleted and automatically created upon startup.

2. The file corresponding to the Undo tablespace is lost.

Simulation: After the database is shut down, the Undo file is deleted and an error occurs during startup:

ORA-01157: cannot identify/lock data file 2-see dbwr trace file
ORA-01110: data file 2: 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ undotbs01.dbf'

Recovery Procedure: Because the Undo file does not save data, you can directly drop and recreate it.

1. Drop the Undo file.

SQL> alter database datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ undotbs01.dbf' offline drop;
Database altered.

2. Change undo management to manual.

SQL> alter system set undo_management = 'manual' scope = spfile;

System altered.

3. Restart the database.

SQL> shutdown immediate;
ORA-01109: Database not open
Database dismounted.
Oracle instance shut down.

SQL> startup
Oracle instance started.
Total system global area 612368384 bytes
Fixed size 1250428 bytes
Variable Size 264244100 bytes
Database buffers 339738624 bytes
Redo buffers 7135232 bytes
Database mounted.
Database opened.
4. Drop the Undo tablespace of the lost file and recreate it.

SQL> drop tablespace undotbs1;
Tablespace dropped.

SQL> Create undo tablespace undotbs1 datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ undotbs01.dbf' size 20 m;
Tablespace created.
5. To view the current database undo settings, change undo_management to auto.
SQL> show parameter undo
Name type value
-----------------------------------------------------------------------------
Undo_management string Manual
Undo_retention integer 900
Undo_tablespace string undotbs1

SQL> alter system set undo_management = 'auto' scope = spfile;
System altered.

6. Restart and restore the database to undo for automatic management.

SQL> shutdown immediate;
SQL> startup

** However, if the Undo file is deleted after Shutdown abort (not immediate), the above process cannot be restored.