Don't Forget to Add Tempfiles Back After Restoring 9i Database

  • by

If your database is 10g onward, you don't have to worry about it, the control file will recreate the tempfiles after database restore, but for 9i, you have to do it by yourself. Otherwise, the users will call you soon or later according to this error:

ORA-25153: Temporary Tablespace is Empty

For 9i database, the control file database knows the created temporary tablespace, but it does NOT record any tempfile information. That's why you have to add them back by yourself.

SQL> alter tablespace temp add tempfile '/oracle/oradata/temp/temp01.dbf' size 32212254720 reuse autoextend on next 104857600 maxsize unlimited;

Tablespace altered.

SQL> alter tablespace temp add tempfile '/oracle/oradata/temp/temp02.dbf' size 32212254720 reuse autoextend on next 104857600 maxsize unlimited;

Tablespace altered.

Can we set the tempfile locations in the pfile or spfile, just like control files or archive destinations? The answer is no, there's no such parameter to set.

For more information about how to resolve ORA-25153, you may refer to my post: How to Resolve ORA-25153: Temporary Tablespace is Empty

Leave a Reply

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