-
Hi,
I have a question about TEMP tablespace, I am using Oracle 9iR2 and Win XP.
The scenario is
I took the complete database backup using RMAN.
I shutdown the database.
I renamed datafiles related to SYSTEM, USERS and TEMP tablespaces, so that during next startup Oracle will not find these files.. (just to generate an error).
As expected I got the error..
I restored datafiles related to SYSTEM and USERS tablespace from rescent backup.
I could able to start the database, everything worked well..
.... My temp datafile is still renamed..
Status of all tablespaces is ONLINE (Even TEMP)
If I query v$tempfile, it shows original name of tempfile which I renamed before restoring database, so logically the file which is displayed by v$tempfile doesn't exist anymore.
As all tablespaces are LOCALLY managed, nothing is stored in data dictionary.. It wasn't also included in RMAN backup.
So in this case which tablespace is getting used for sorts??? because my temp file is still renamed... Where is TEMP file and whats the relation with TEMP tablespace????
Thanks
Sameer
[Edited by Sameer on 09-28-2002 at 09:42 AM]
-
well tempfiles are not restored or recovered since they are not even backed up if you use RMAN (and they are not supposed to be backup, usesless, if you are backup manually)
if you do alter backup controlfile to trace you can actually see what Oracle does when recovering the database
Code:
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_2/oradata/dev920/temp01.dbf' REUSE;
# End of tempfile additions.
#
it simply adds a tempfile so no matter what you do, rename it or delete it, it will still able to get a new tempfile
-
Originally posted by pando
Code:
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_2/oradata/dev920/temp01.dbf' REUSE;
# End of tempfile additions.
#
it simply adds a tempfile so no matter what you do, rename it or delete it, it will still able to get a new tempfile
Oh.. I wasn't aware of this... Thanks for the reply...
But then why can't I see this newly added tempfile on file system? (I mean physical file). My database starts and works perfectly....
Sameer
-
do alter database backup controlfile to trace and see where in the filesystem add tempfile Oracle is adding to....
-
Initially before restoring files, the control file backup at trace..
Code:
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
# End of tempfile additions.
#
But now new backup controlfile at trace says...
Code:
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# No tempfile entries found to add.
#
Sameer
[Edited by Sameer on 09-28-2002 at 03:02 PM]
-
Originally posted by Sameer
Originally posted by pando
Code:
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_2/oradata/dev920/temp01.dbf' REUSE;
# End of tempfile additions.
#
it simply adds a tempfile so no matter what you do, rename it or delete it, it will still able to get a new tempfile
Oh.. I wasn't aware of this... Thanks for the reply...
But then why can't I see this newly added tempfile on file system? (I mean physical file). My database starts and works perfectly....
Sameer
Oracle does not add any tempfile physically, it just adds pointer to controlfile.. If you notice the controlfile trace, you can see that tempfile is being added using REUSE clause... It means, the file should be there allready..
-nagarjuna
-
Originally posted by pando
it simply adds a tempfile so no matter what you do, rename it or delete it, it will still able to get a new tempfile
Originally posted by nagarjuna
Oracle does not add any tempfile physically, it just adds pointer to controlfile.. If you notice the controlfile trace, you can see that tempfile is being added using REUSE clause... It means, the file should be there allready..
But my controlfile dumped at trace says
Code:
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# No tempfile entries found to add.
#
So where is my tempfile.. FILE IS NOT PHISICALLY PRESENT ON THE DISK.... I have renamed it.... as I have said it before.. so logically tempfile displayed by v$tempfile view doesn't PHYSICAL EXIST ON DISK AND ALSO NEW FILE IS NOT PHYSICALLY CREATED
Sameer
-
Originally posted by Sameer
Code:
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# No tempfile entries found to add.
#
So where is my tempfile.. FILE IS NOT PHISICALLY PRESENT ON THE DISK.... I have renamed it.... as I have said it before.. so logically tempfile displayed by v$tempfile view doesn't PHYSICAL EXIST ON DISK AND ALSO NEW FILE IS NOT PHYSICALLY CREATED
Well, later I found following error in my dbwr trace file...
Code:
*** 2002-09-28 19:05:04.000
*** SESSION ID:(2.1) 2002-09-28 19:05:04.000
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) Das System kann die angegebene Datei nicht finden.
Somehow my system could not able to create file physically on disk.. and later when I did big sort operation I got ORA-25153.. I added a tempfile and everything worked well...
Thanks
Sameer
[Edited by Sameer on 09-29-2002 at 02:45 PM]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|