Recover the rollback datefiles
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Recover the rollback datefiles

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    I am in the process of practice to recover the rollback datafiles, I followed these steps from the backup and recovery book but it's wrong. The name of the book is Oracle 8i backup & recovery by RAMA VELPURI.

    any way , here are the summary:

    1. I created a table
    2. set transaction use rollback segment rb1
    3. insert one record into this table
    4. shutdown abort
    5. del rbs1orcl.ora datafile
    6. copy the rbs1orcl.ora from the backup
    7. uncomment out the ROLLBACK_SEGMENTS in the init.ora
    8. startup mount;
    9. alter database datafile 'c:\oradata\rbs1.dbf' offline;
    10. alter database open
    and I got the errors:

    *
    ORA-00604: error occured at recursive SQL level 2
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'c:\ORADATA\RBS1.DBF'


    please advise b/c the book is not right. According to the book, I should be able to open the database.

    Please help


  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    RBS Recovery

    Hi

    5. del rbs1orcl.ora datafile
    6. copy the rbs1orcl.ora from the backup

    ie Youa re deleting the datafile rbs1orcl.ora and copying the same from the backup.

    again

    alter database datafile 'c:\oradata\rbs1.dbf' offline; => I don't understand why you making offline dbs1.dbf instead of rbs1orcl.ora. You upposed to offline the 'missing' datafile.. then startup the database.

    Please try again.

    Thomas

    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Jan 2002
    Posts
    474
    Thanks for your reply.

    RBS1.DBF is the missing rollback datafiles whereas RB1 is the rollback segment of the database. I understand that I have to take the missing datafiles offline, that's exactl what I did but it's not working and I got the errors below.


  4. #4
    Join Date
    Jan 2002
    Posts
    474
    Any Advises ?????

    Please help


  5. #5
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by ashley75
    I am in the process of practice to recover the rollback datafiles, I followed these steps from the backup and recovery book but it's wrong. The name of the book is Oracle 8i backup & recovery by RAMA VELPURI.

    any way , here are the summary:

    1. I created a table
    2. set transaction use rollback segment rb1
    3. insert one record into this table
    4. shutdown abort
    5. del rbs1orcl.ora datafile
    6. copy the rbs1orcl.ora from the backup
    7. uncomment out the ROLLBACK_SEGMENTS in the init.ora
    8. startup mount;
    9. alter database datafile 'c:\oradata\rbs1.dbf' offline;
    10. alter database open
    and I got the errors:

    *
    ORA-00604: error occured at recursive SQL level 2
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'c:\ORADATA\RBS1.DBF'


    please advise b/c the book is not right. According to the book, I should be able to open the database.

    Please help

    The file has the name rbs1.dbf, and when you say you have copied from backup you mention the name rbs1orcl.ora which is not the same. Rename it and try again.

    Anyway I don't understand why you are trying to place offline this file after mount the database. Just try to open with the backed file, probably it will prompt you for recover.

    Cheers

    Angel

  6. #6
    Join Date
    Jan 2002
    Posts
    474
    Thomas,

    Sorry, I mistyped the datafiles, I understand that you have to take the missing datafiles offline and comment out the rollbackup segement in the init.ora and open the database. It's just the typo when I posted my mesaage in here, I did take the missing datafile offline and comment out the rollback segments but it's not wokring.


    Angel,

    it's not working.


    what bother me was I comment out the rollback segments in the init.ora but it's seems like it's not affecting anything whether I comment it out or not before the database mount and it give me the same problem.

    any one had anyother advises.

    Thanks

  7. #7
    Join Date
    Jan 2002
    Posts
    474
    Jeff and Julian and other senior,

    Do you have any advises????

    Thanks

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Originally posted by ashley75
    I am in the process of practice to recover the rollback datafiles, I followed these steps from the backup and recovery book but it's wrong. The name of the book is Oracle 8i backup & recovery by RAMA VELPURI.

    any way , here are the summary:

    1. I created a table
    2. set transaction use rollback segment rb1
    3. insert one record into this table
    4. shutdown abort
    5. del rbs1orcl.ora datafile
    6. copy the rbs1orcl.ora from the backup
    7. uncomment out the ROLLBACK_SEGMENTS in the init.ora
    8. startup mount;
    9. alter database datafile 'c:\oradata\rbs1.dbf' offline;
    10. alter database open
    and I got the errors:

    *
    ORA-00604: error occured at recursive SQL level 2
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'c:\ORADATA\RBS1.DBF'


    please advise b/c the book is not right. According to the book, I should be able to open the database.

    Please help

    Ashley
    you shud have..
    first taken the datafile offline
    and then done the physical copy to the original location.
    Then,
    connecting as sys/pwd as sysdba
    done the recover datafile '/blah/blah'
    applied the log's subsequently.and then
    after media recovery complete.
    alter database datafile 'blah/blah' online
    and then validate your records in the db.
    It worked fine for me a few days back...
    shtu down and repeat in that sequence.

    Tarry
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by ashley75
    Jeff and Julian and other senior,

    Do you have any advises????

    Thanks
    When you lose a data file in Rollback Segment, there are 2 scenarios: you shutdown cleanly or abort/crash. Things are very much related to your SHUTDOWN ABORT. In this case, it is almost certain according to Oracle that the RBS that has extents in the lost data file still has active transactions. Thus, the file cannot be taken offline or dropped. You must restore the datafile from backup and apply media recovery. If your database is in NOARCHIVELOG mode you will be able to succeed only if the redo that needs to be applied is within the range of your online logs.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Jan 2002
    Posts
    474
    Tarry,

    I did follow your procedure:

    1. take the missing datafile offlinel
    2. copy the datafile from the backup to the original location.

    and it still didn't work, I got the same error message, I did comment out the rollback segments in the init.ora but I have the feeling that it still tryied to use these rollback segment so that when it open the database it bombs and it looks for the missing datafile.


    Julian,


    Could you please give some instruction of how to fix this problem????

    Basically, the database crashed ( since we shutdown abort) and you lost the rollback database and the rollback segment still had the active transaction.

    How do you fix this problem????

    Please help


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