Sweet glad you told me about this, I will test it when I get back. Joe Sent from the crackberry, so please excuse the typos and terseness. ----- Original Message ----- From: "April Sims" [sims@xxxxxxx] Sent: 04/22/2010 09:58 AM CST To: <oracle-l@xxxxxxxxxxxxx> Subject: Automatic recreation of temp files on startup Just thought I would pass on a tip that most people don't seem to know about.. Oracle recreates the tempfiles if they are missing upon startup. Asked quite a few people at the IOUG Conference including Oracle but no one seemed to know about this feature. I have done these steps on a 11.2.0.1 database but this is reported to work starting with 10g. One of many tips found in my new book... How to replicate: 1. Remove or just simply move a tempfile at the operating system level. > mv temp01.dbf temp01.bad 2. Shutdown database 3. Startup - If you are using a spfile (this was the behavior in 11.2.0.1) then an event will be inserted preventing the database from mounting. 4. Create pfile from spfile, edit and remove the event (missing file 1110 line). You should be able to restart at this point. SQL> create pfile from spfile; 5. Note in the alert log how the tempfile is recreated, this is an excerpt. INSV started with pid=19, OS id=5487 Successfully onlined Undo Tablespace 1. SMON: enabling tx recovery Re-creating tempfile /u01/oradata/TTEST/temp01.dbf 2010-04-22 09:43:30.611000 -06:00 Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) 2010-04-22 09:43:32.398000 -06:00 WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected. Completed: ALTER DATABASE OPEN 2010-04-22 09:43:35.560000 -06:00 Starting background process CJQ0 CJQ0 started with pid=22, OS id=5499 6. Verify the filesize and location is identical to the original file at the operating system level. > ls -altr *temp* -rw-r----- 1 oracle dba 1081090048 Apr 21 22:00 temp01.bad -rw-r----- 1 oracle dba 1081090048 Apr 22 09:43 temp01.dbf April Sims OCP 8i, 9i, 10g DBA Southern Utah University Oracle Database 11g - Underground Advice for Database Administrators sims@xxxxxxx 940-484-4276 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l