Automatic recreation of temp files on startup
- From: "April Sims" <sims@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Apr 2010 09:58:42 -0600
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
Other related posts:
