DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Datafile Error

  1. #1
    Join Date
    Aug 2002
    Posts
    176

    Datafile Error

    Hi all,
    We have a test database , the one and only one datafile of the users tablespace was replaced with a old backupfile backedup before the resetlogs. now we are not able to recover this file. So what can be done to continue using the tablespace. We added another datafile. but still users are not able to create tables onm this datafile. it stills points the error to the corrupt datafile only. Can we drop the tablespace and create new tablespace. Or is there any other method , pls do advice

    thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: Datafile Error

    Originally posted by Rohit
    Can we drop the tablespace and create new tablespace.
    Yes you can drop the tablespace and recreate it.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Aug 2002
    Posts
    176
    Why does the tablespace not continue to work .Becaue i have added a new datafile and only creatung new tables. Its obvious we cannot access the old ones, but why creation of new segments generating errors refering to the corrupted datafile.

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Code:
    Tablespace user before adding datafile      Tablespace user after adding datafile
            ---------                                  ---------------------
            |       |                                  |                   |
            |       |                                  |                   |
            |       |                                  |                   |
            |       |                                  |                   |
            |       |                                  |                   |
            |       |                                  |                   |
            ---------                                  ---------------------
    
          Datafile                                         Two Datafiles
    
            -----                                         -----     -----
            |   |                                         |   |     |   |
            |   |                                         |   |     |   |
            |   |                                         |   |     |   |
            -----                                         -----     -----
    
    ___________________________________________________________________________
    
        USER Tablespace                                  USER Tablespace
    
    You try to create a table 
    
    This is ---------                                  ---------------------
    corrupt |_      |                      This is     |_                  |
      ----->|_|     |                      still ----> |_|                 |
            |       |                      corrupt     |                   |
            |       |                                  |                   |
            |       |                                  |                   |
            |       |                                  |                   |
            |       |                                  |                   |
            ---------                                  ---------------------
    I believe that ORACLE is trying to access the same block for creating the table which is corrupt.

    HTH
    Last edited by adewri; 03-11-2003 at 12:07 PM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    BTW what error message are you getting on create table command ?
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Aug 2002
    Posts
    176
    ORA-00376: file 3 cannot be read at this time
    ORA-01110: data file 3: 'D:\ORACLE\ORADATA\GLOBAL\USERS01.DBF'

    but i see one more thing if i loginto the sever form a client and create a table it is successfully creating , sitting in the same system and creating does not allow .

    thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    What is the status of the datafile ??
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Since you cannot recover that datafile DROP the tablespace and create again. That is the correct way.
    But if you still want keep the corrupt file do OFFLINE DROP for that file and add a new datafile.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Sanjay:

    he is able to create tables from client but not from server...will that suggest that datafile may be corrupted...i guess the datafile have gone offline due som reason...
    S it may be corrupted but in that case, how is he able to create tables from client?


    as amar said , post the status of datafile...

    select status from DBA_DATA_FILES where FILE_NAME='D:\ORACLE\ORADATA\GLOBAL\USERS01.DBF'

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by abhaysk
    Sanjay:

    he is able to create tables from client but not from server...will that suggest that datafile may be corrupted...
    No it doesn't. The datafile is unusable because he restored it from the backup taken before the RESETLOGS.
    i guess the datafile have gone offline due som reason...
    S it may be corrupted but in that case, how is he able to create tables from client?
    call it corrupted or unusable, fact is he can't create any segment in that file, neither from client connection nor from local connection.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

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