TEMP tablespace and TEMP file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: TEMP tablespace and TEMP file

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Question

    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]

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    do alter database backup controlfile to trace and see where in the filesystem add tempfile Oracle is adding to....

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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]

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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

  7. #7
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  8. #8
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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
  •  



Click Here to Expand Forum to Full Width