Skip to content
Home » Oracle » When Tempfiles are Missing

When Tempfiles are Missing

Backing up a whole database including all datafiles by RMAN does not include temp files, this is because temp files are not required to restore a database. If we missed temp files, how does the database react? Let's see the demonstration.

Rename the tempfile to simulate the situation.

[oracle@test ~]$ cd /u01/app/oracle/oradata/ORCL/
[oracle@test ORCL]$ ls -l
-rw-r----- 1 oracle oinstall  20979712 Jul 18  2011 temp01.dbf
[oracle@test ORCL]$ mv temp01.dbf temp01.dbf.bak
[oracle@test ORCL]$ ls -l
-rw-r----- 1 oracle oinstall  20979712 Jul 18  2011 temp01.dbf.bak

Startup the database.

[oracle@test ORCL]$ sqlplus / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             247467796 bytes
Database Buffers          356515840 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.

Check the status of the tempfile.

SQL> column name format a50;
SQL> select name, status from v$tempfile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ORCL/temp01.dbf          ONLINE

You can see the database works fine, there's no problem at all.

Check the file system again.

[oracle@test ORCL]$ ls -l
-rw-r----- 1 oracle oinstall  20979712 Feb 20 15:29 temp01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 18  2011 temp01.dbf.bak
[oracle@test ORCL]$

You can see the tempfile temp01.dbf is back, which means that the database will create the missing tempfile if there's none provided.

For more examples on tempfile, you can refer to the Oracle documentation: Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files.

Leave a Reply

Your email address will not be published. Required fields are marked *