Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45

    Exclamation Unanswered: Can't open database

    Hi all,

    Would really welcome some help with this one.

    Running 8.0.4 on UNIX.

    Have a temp file which is corrupt, and am getting the below errors when attempting to startup the database:
    ------------------------------
    SVRMGR> startup
    ORACLE instance started.
    Total System Global Area 10297568 bytes
    Fixed Size 45280 bytes
    Variable Size 6082560 bytes
    Database Buffers 4096000 bytes
    Redo Buffers 73728 bytes
    Database mounted.
    ORA-01113: file 100 needs media recovery
    ORA-01110: data file 100: '/temp/DEV/temp00.dbf'
    SVRMGR> recover datafile '/temp/DEV/temp00.dbf'
    ORA-00279: change 196127329 generated at 01/19/04 16:10:24 needed for thread 1
    ORA-00289: suggestion : /usr/oracle8/8.0.4/dbs/arch1_2821.dbf
    ORA-00280: change 196127329 for thread 1 is in sequence #2821
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00308: cannot open archived log '/usr/oracle8/8.0.4/dbs/arch1_2821.dbf'
    ORA-27037: unable to obtain file status
    IBM AIX RISC System/6000 Error: 2: No such file or directory
    Additional information: 3
    ---------------------------------------------
    There is no arch1_2821.dbf file.

    The problem occured when I tried to perform an offline drop of the temp datafile. Is there any way I can drop the temp tablespace without the db being open. This is becuase I cannot open the database.

    Quite confused what to do here? I don't care if I trash the temp files, just need to get the database open.

    Thanks in advance,
    Rob.

  2. #2
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    I have also tried dropping the temp tablespace, but I believe the database has to be open to do this:
    --------------------
    SVRMGR> drop tablespace temp;
    drop tablespace temp
    *
    ORA-01109: database not open
    SVRMGR> startup force
    ORACLE instance started.
    Total System Global Area 10297568 bytes
    Fixed Size 45280 bytes
    Variable Size 6082560 bytes
    Database Buffers 4096000 bytes
    Redo Buffers 73728 bytes
    Database mounted.
    ORA-01113: file 100 needs media recovery
    ORA-01110: data file 100: '/temp/DEV/temp00.dbf'
    SVRMGR> drop tablespace temp;
    drop tablespace temp
    *
    ORA-01109: database not open
    -------------------------------------
    Rgds,
    Rob.

  3. #3
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    Hi ,

    Probably you can try this..

    1. startup mount
    2. alter database datafile '/temp/DEV/temp00.dbf' offline drop;
    3. alter database open
    4. drop tablespace temp


    If that doesnt work out then try recovery as follows

    1. startup
    2. it will fail after mounting the database
    3. then give 'recover database' command.
    4. then it will suggest some archive log file location with sequence number. Now dont press enter.. give the path of the ur online redo log which is of the sequence number mentioned. if you are not sure, give anyone location of your logfiles. then try one by one of ur redo log files.
    5. after that give 'alter database open' command.

    If that also doesnt work,

    instead of mentioning the redo log location, type cancel.
    then issue 'alter databse open resetlogs'.. if it opens then take the full database backup as previous backups will not be valid...
    Regards
    Suneel

  4. #4
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    I can't seem to find any log files here, would they still have the extension .dbf?

    Thanks,
    Rob.

  5. #5
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    After mounting the database, query v$logfile view to check the log file names...
    Regards
    Suneel

  6. #6
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    Thanks for the reply Suneel,

    however when I try and perform the database recovery it states that its succeeded....but then when I try and open it, it states that the file is still corrupt!
    So I can't seem to drop the file, or the tablespace, is there someway to manually edit the control file so that it does not see the damaged datafile?

    Thanks,
    Rob.
    ----

    SVRMGR> startup mount
    ORACLE instance started.
    Total System Global Area 10297568 bytes
    Fixed Size 45280 bytes
    Variable Size 6082560 bytes
    Database Buffers 4096000 bytes
    Redo Buffers 73728 bytes
    Database mounted.
    SVRMGR> alter database open
    2> /
    alter database open
    *
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00376: file 100 cannot be read at this time
    ORA-01110: data file 100: '/temp/DEV/temp00.dbf'
    SVRMGR> recover database
    Media recovery complete.
    SVRMGR> alter database open
    2> /
    alter database open
    *
    ORA-00704: bootstrap process failure
    ORA-00705: inconsistent state during start up; shut down the instance, then restart it
    SVRMGR> shutdown immediate
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.
    SVRMGR> startup
    ORACLE instance started.
    Total System Global Area 10297568 bytes
    Fixed Size 45280 bytes
    Variable Size 6082560 bytes
    Database Buffers 4096000 bytes
    Redo Buffers 73728 bytes
    Database mounted.
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00376: file 100 cannot be read at this time
    ORA-01110: data file 100: '/temp/DEV/temp00.dbf'

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    I would certainly not try to hack the controlfile to remove the temp file.

    What errors do you get when you try to do an offline drop of the temp datafile?

  8. #8
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    Originally posted by SkyWriter
    I would certainly not try to hack the controlfile to remove the temp file.

    What errors do you get when you try to do an offline drop of the temp datafile?
    None, it says statement processed, then when I try and open the database it state that there is an inconsistency, and that I must restart.
    But on restart it states that it can't read the temp file again.

    I am now stumped, there must be some way to offline a tablespace without database being open?

    Thanks and Regards
    Rob

    ORA-00604: error occurred at recursive SQL level 1
    ORA-00376: file 100 cannot be read at this time
    ORA-01110: data file 100: '/temp/DEV/temp00.dbf'
    SVRMGR> alter database datafile '/temp/DEV/temp00.dbf' offline drop;
    Statement processed.
    SVRMGR> alter database open
    2> ;
    alter database open
    *
    ORA-00704: bootstrap process failure
    ORA-00705: inconsistent state during start up; shut down the instance, then restart it
    SVRMGR>

  9. #9
    Join Date
    Jan 2004
    Posts
    370
    I don't know - I'm stumped too.

    You can't offline a tablespace unless the database is open, but you should be able to offline drop a datafile. You should then be able to open the database and drop the tablespace.

    What's in the alert log?
    Any ORA-600?

  10. #10
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    Originally posted by SkyWriter
    I don't know - I'm stumped too.

    You can't offline a tablespace unless the database is open, but you should be able to offline drop a datafile. You should then be able to open the database and drop the tablespace.

    What's in the alert log?
    Any ORA-600?
    There was an ORA-0600 about 3 hours after the temp datafile in question was addeded. They had got the size wrong and therefore multiple ORA-1119 errors were present. They had obviously not checked size of disk before using the OEM to resize it.
    The db had not fallen over when the ORA-0600 occured though. Only when I tried to offline drop the temp datafile.

    But still anyone know how to get rid of the temp tablespace without database being open? Or any work around? Offline drop is just not working although it states that it is!!!!!

    Help is greatly appreciated..

    Thanks,
    Rob

  11. #11
    Join Date
    Jan 2004
    Posts
    370
    Did the ORA-600 occur when the datafile was resized?

    Are you saying the database crashed at the point you tried to offline drop the datafile?

    The data dictionary thinks this file needs recovery, even after an offline drop. That isn't good.

  12. #12
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    Originally posted by SkyWriter
    Did the ORA-600 occur when the datafile was resized?

    Are you saying the database crashed at the point you tried to offline drop the datafile?

    The data dictionary thinks this file needs recovery, even after an offline drop. That isn't good.
    No, not directly, the ORA-0600 occured a number of hours later. But it's very coincidental if it's not related.

    The database did'nt crash. I was able to shut it down normally, the datafile must have corrupted as I was attempting to offline drop it.
    The datafile resize was done Dec 16 '03 and I'm just discovering this now....

  13. #13
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    You can try recreating the control file with the complete list of datafiles and logfiles. We dont have to add temp file location to 'create control file' command. After creating the control file, then recover the database using' recover database using backup controlfile' command and try opening the database.
    Regards
    Suneel

  14. #14
    Join Date
    Jan 2004
    Posts
    99
    that bootstrap message is bad news, it's something to do with the SYS owned objects in the SYSTEM tablespace!!

  15. #15
    Join Date
    Jan 2004
    Posts
    370
    Originally posted by zaki_mtk
    that bootstrap message is bad news, it's something to do with the SYS owned objects in the SYSTEM tablespace!!
    I agree - it does look like some form of dictionary corruption.

    I'd suggest you contact Oracle Support rather than recreate a controlfile to fool Oracle into starting up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •