Problem solve Get help with specific problems with your technologies, process and projects.

TEMP tablespace, but no TEMP.dbf file

I have a clone of a Oracle database and I get a copy of the production data from a hot backup. Before I start the recover process (startup mount), I get a trace file from the DBW0 trace file that it cannot find the temp.dbf file. This file is a locally managed *.dbf file and cannot be backed up by production, so I get ORA-01157, ORA- 01110 and ORA-27037. So I start my recovery and everything is fine, but my question is though the tablespace TEMP gets created, there is no *.dbf file to go with it. So I create a temp.dbf file for it. Why the error messages? And do I lose any info that my be in the TEMP.dbf file, and how do I find out what object are being used by the production temp.dbf?

As you are aware, you are getting these errors because you do not have the datafile for the TEMP tablespace. And if you are using *tempfiles* for your TEMP tablespace, then you should not be backing those files up.

In Oracle 8 and earlier, TEMP tablespaces were made of normal datafiles. This also meant that certain activity in those datafiles incurred redo. This could place undo burden on the database. Oracle 8i introduced tempfiles. The tempfiles only contain temporary sorting workspace information. They do not contain any data so they don't need backing up. And they don't need to create any redo information either. So not having those files backed up isn't that important.

So now the question is how do you restore without these tempfiles? Let's step through an example:

SVRMGR> select file_name,status from dba_temp_files;
ORA-01157: cannot identify/lock data file 81 - see
DBWR trace file
ORA-01110: data file 81:
Here, you can see that I'm getting the same ORA-1157 and ORA-1110 errors you got. Since there was no real data in the tempfile, it really doesn't need to be recovered. So we just drop the tablespace and recreate it!
SVRMGR> drop tablespace temp;
Statement processed.
SVRMGR> create temporary tablespace temp
     2> tempfile
'/edcsns18/oradata7/gast/temp_gast01.dbf' size 200m;
Statement processed.
I had no problems just dropping the tablespace and recreating it! Now my original query that failed will work:
SVRMGR> select file_name,status from dba_temp_files;
1 row selected.
So tempfiles don't need normal recovery like regular datafiles. Just drop the temporary tablespace and recreate it.

Dig Deeper on Oracle database design and architecture