Recover rollback datafile belong to rollback tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Recover rollback datafile belong to rollback tablespace

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    Hi all,

    I am in the process of testing to recover the datafile belong to non system rollback tablespace and I have some problem. Below is what I have done in the process.

    1. assume that all of the rollback datafiles belong to rollback tablespace are gone.
    2. shutdown the database
    3. startup mount pfile=c:\oracle\ora817\admin\test\pfile\inittest.ora
    4. alter database datafile 'c:\oradata\test\rollback01.ora' OFFLINE;
    5. SVRMGR> alter database open;
    alter database open
    *
    ORA-00604: error occurred at recursive SQL level 2
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'C:\ORADATA\HOUSTON\ROLLBACK01.ORA'

    Why did I got this error??? I thought I took the rollback datafile offline before I open the database

    Please advise

    thanks





  2. #2
    Join Date
    Aug 2001
    Posts
    75
    Do you have rollback_segments=(rbs01,...)
    statement in init.ora
    If yes, then comment out.

    One more point, before taking the rollback tablespace offline , make sure that you also take rollback segments offline,

    e.g. First get all the online segments.
    select SEGMENT_NAME from DBA_ROLLBACK_SEGS
    where status='ONLINE';

    You should take offline all rollback segments except SYSTEM.
    and then take rollback tablespace offline,
    then simulate your rollback datafiles loss.


    Sanjay

  3. #3
    Join Date
    Jan 2002
    Posts
    474
    Sanjay,

    Thanks for your reply.

    I did comment out the rollback_segments in the init.ora but I still have problem and here what I did

    Per your advise, I need to run this query:

    SVRMGR> startup mount pfile=c:\oracle\ora817\admin\test\pfile\inittest.ora

    ORACLE instance started.
    Total System Global Area 85776412 bytes
    Fixed Size 75804 bytes
    Variable Size 59408384 bytes
    Database Buffers 26214400 bytes
    Redo Buffers 77824 bytes
    Database mounted.
    SVRMGR> alter database datafile 'c:\oradata\test\rollback01.ora' offline;
    Statement processed.
    SVRMGR> select SEGMENT_NAME from DBA_ROLLBACK_SEGS
    2> where status='ONLINE';
    select SEGMENT_NAME from DBA_ROLLBACK_SEGS
    *
    ORA-01219: database not open: queries allowed on fixed tables/views only
    SVRMGR>

    I can't run this query at mount stage obviously. do you have any other advises ????

    Thanks


    [Edited by ashley75 on 04-23-2002 at 10:52 PM]

  4. #4
    Join Date
    Aug 2001
    Posts
    75
    Try these steps :-

    If you are ABSOLUTELY POSITIVE that the database was cleanly shutdown,
    i.e., it was closed with either shutdown NORMAL or IMMEDIATE, then
    the simplest solution is to offline drop the missing datafile, open the
    database in restricted mode, and then drop and recreate the rollback
    tablespace to which the file belonged. DO NOT follow this procedure
    if the database was shut down ABORT or if it crashed.

    1. Make sure the database was last cleanly shut down.

    Check the alert.log file for this instance. Go to the bottom of
    the file and make sure the last time you shut the database down
    you got the messages:

    "alter database dismount
    Completed: alter database dismount"

    This also includes the case of a clean shutdown followed by a
    failed attempt to startup the database. In that case, Oracle will
    issue error messages and shut itself down abort. For the purposes
    of this solution, though, this counts as a clean shutdown.

    If that is not the case, i.e., if the last time YOU shut the database
    down it was in abort mode, or the database crashed itself, it is
    NOT safe to proceed. You should follow the instructions for
    case I.B below.

    2. Remove all the rollback segments in the tablespace to which the lost
    datafile belongs from the ROLLBACK_SEGMENTS parameter in the init.ora
    file for this instance. If you are not sure about which rollbacks are
    in that tablespace, simply comment out the whole ROLLBACK_SEGMENTS entry.

    3. Mount the database in restricted mode.

    STARTUP RESTRICT MOUNT

    4. Offline drop the lost datafile.

    ALTER DATABASE DATAFILE '' OFFLINE DROP;

    5. Open the database.

    ALTER DATABASE OPEN

    If you receive the message "Statement processed," move on to Step 7.

    If instead you get ORA-604, ORA-376, and ORA-1110, go to Step 6.

    6. Since opening the database failed, shut the database down and edit the
    init.ora file for this instance. Comment out the ROLLBACK_SEGMENTS
    parameter and add the following line:

    _corrupted_rollback_segments = ( ,...., )

    i.e., the above list should contain all the rollbacks originally listed in
    the ROLLBACK_SEGMENTS parameter.

    WARNING: Use this parameter ONLY IN THIS SPECIFIC SCENARIO or as
    instructed by Oracle Customer Support.

    Then startup the database in restricted mode:

    STARTUP RESTRICT

    7. Drop the rollback tablespace to which the datafile belonged.

    DROP TABLESPACE INCLUDING CONTENTS;

    8. Recreate the rollback tablespace with all its rollback segments.
    Remember to bring the rollbacks online after you create them.

    9. Make the database available to all users.

    ALTER SYSTEM DISABLE RESTRICTED SESSION;

    10. Reinclude the rollbacks you just recreated in the ROLLBACK_SEGMENTS
    parameter in the init.ora file for this instance. If you had commented
    out the whole ROLLBACK_SEGMENTS entry, simply uncomment it now. If you had
    to go through Step 6, REMOVE THE _CORRUPTED_ROLLBACK_SEGMENTS PARAMETER
    NOW.


    I.B. THE DATABASE WAS NOT CLEANLY SHUT DOWN
    -------------------------------------------

    This is the situation where the database was last shut down abort or crashed.
    In this case, it is almost certain that the rollback segments that had
    extents in the lost datafile still contain active transactions. Therefore,
    the file cannot be offlined or dropped. You must restore the lost datafile
    from a backup and apply media recovery to it. If the database is in
    NOARCHIVELOG mode, you will only succeed in recovering the datafile if the
    redo to be applied is within the range of your online logs. If a
    backup of the datafile is not available, please contact Oracle Customer
    Support.

    These are the steps:

    1. Restore the lost file from a backup.

    2. Mount the database.

    3. Issue the following query:

    SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

    If the status of the file you just restored is "OFFLINE," you must
    online it before proceeding:

    ALTER DATABASE DATAFILE '' ONLINE;

    4. Issue the following query:

    SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
    FROM V$LOG V1, V$LOGFILE V2
    WHERE V1.GROUP# = V2.GROUP# ;

    This will list all your online redolog files and their respective
    sequence and first change numbers.

    5. If the database is in NOARCHIVELOG mode, issue the query:

    SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

    If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your
    logs, the datafile can be recovered. Just keep in mind that all the
    logs to be applied will be online logs, and move on to step 6.

    If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs,
    the file cannot be recovered. Your options at this point include
    restoring a full backup if one is available or forcing the database
    to open in an inconsistent state to get a full export out of it.
    For further details and to assist you in your decision, please
    contact Oracle Customer Support.

    6. Recover the datafile:

    RECOVER DATAFILE ''

    7. Confirm each of the logs that you are prompted for until you
    receive the message "Media recovery complete". If you are prompted for a
    non-existing archived log, Oracle probably needs one or more of the
    online logs to proceed with the recovery. Compare the sequence number
    referenced in the ORA-280 message with the sequence numbers of your online
    logs. Then enter the full path name of one of the members of the redo group
    whose sequence number matches the one you are being asked for. Keep entering
    online logs as requested until you receive the message "Media recovery
    complete".

    8. Open the database.


    II. THE DATABASE IS UP
    ----------------------

    If you have detected the loss of the rollback datafile and the database
    is still up and running, DO NOT SHUT IT DOWN. In most cases, it is
    simpler to solve this problem with the database up than with it down.

    Two approaches are possible in this scenario:

    A) The first one involves offlining the lost datafile, restoring it from
    backup, and then applying media recovery to it to make it consistent
    with the rest of the database. This method can only be used if
    the database is in ARCHIVELOG mode.

    B) The other approach involves offlining all the rollback segments in the
    tablespace to which the lost datafile belongs, dropping the tablespace,
    and then recreating it. You may have to kill sessions that have
    transactions in the rollbacks involved to force the rollbacks to go
    offline.

    In general, approach II.A is simpler to apply. It will also be faster
    if the datafile and the necessary archived logs can be quickly restored
    from backup. However, more user transactions will error out and be rolled
    back than with approach II.B. Because of read-consistency, queries against
    certain tables may fail with approach II.A, since the rollback extents from
    which Oracle would retrieve the data may be in the offlined datafile.


    APPROACH II.A: RESTORING THE DATAFILE FROM BACKUP
    -------------------------------------------------

    As mentioned before, this approach can only be followed if the database is
    in ARCHIVELOG mode. Here are the steps:

    1. Offline the lost datafile.

    ALTER DATABASE DATAFILE '' OFFLINE;

    NOTE: Depending on the current amount of database activity,
    you may have to create additional rollback segments in a different
    tablespace to keep the database going while you take care of the
    problem.

    2. Restore the datafile from a backup.

    3. Issue the following query:

    SELECT V1.GROUP#, MEMBER, SEQUENCE#
    FROM V$LOG V1, V$LOGFILE V2
    WHERE V1.GROUP# = V2.GROUP# ;

    This will list all your online redolog files and their respective
    sequence numbers.

    4. Recover the datafile:

    RECOVER DATAFILE ''

    5. Confirm each of the logs that you are prompted for until you
    receive the message "Media recovery complete". If you are prompted for a
    non-existing archived log, Oracle probably needs one or more of the
    online logs to proceed with the recovery. Compare the sequence number
    referenced in the ORA-280 message with the sequence numbers of your online
    logs. Then enter the full path name of one of the members of the redo group
    whose sequence number matches the one you are being asked for. Keep entering
    online logs as requested until you receive the message "Media recovery
    complete".

    6. Bring the datafile back online.

    ALTER DATABASE DATAFILE '' ONLINE;


    APPROACH II.B: RECREATING THE ROLLBACK TABLESPACE
    -------------------------------------------------

    This approach can be used regardless of the archival mode of the database.
    The steps are:

    1. Try to offline all the rollback segments in the tablespace to which
    the lost datafile belongs.

    ALTER ROLLBACK SEGMENT OFFLINE;

    Repeat this statement for all rollbacks in the tablespace.

    NOTE: Depending on the current amount of database activity,
    you may have to create additional rollback segments in a different
    tablespace to keep the database going while you take care of the
    problem.

    2. Check the status of the rollbacks.

    They must all be offline before they can be dropped.
    Issue the query:

    SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS
    WHERE TABLESPACE_NAME = '';

    3. Drop all offlined rollback segments.

    For each rollback returned by the query in step 2 with status
    "OFFLINE," issue the statement:

    DROP ROLLBACK SEGMENT ;

    4. Handle the rollbacks that remain online.

    Repeat the query in step 2.

    If any of the rollbacks you tried to offline still has an "ONLINE"
    status, it means there are still active transactions in it. You
    may confirm that by issuing the query:

    SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
    FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
    WHERE TABLESPACE_NAME = '' AND SEGMENT_ID = USN;

    If the above query returns no rows, it means all the rollbacks in
    the affected tablespace are already offline. Repeat the query in
    step 2 to retrieve the names of the rollbacks that just became
    offline and then drop them as described in step 3.

    If the above query returns one or more rows, they should show
    status "PENDING OFFLINE". Next, check the ACTIVE_TX
    column for each rollback. If it has a value of 0, it implies
    there are no pending transactions left in the rollback, and it
    should go offline shortly. Repeat the query in step 2 a few
    more times until it shows the rollback being offline and then
    drop it as described in step 3. Move on to step 6.

    If any of the "pending offline" rollbacks has a value of 1 or
    greater in the ACTIVE_TX column, move on to step 5.

    5. Force rollbacks with active transactions to go offline.

    At this point, the only way to move forward is to have the
    "pending offline" rollbacks released. The active transactions
    in these rollbacks must either be committed or rolled back.
    The following query shows which users have transactions assigned
    to which rollbacks:

    SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
    FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
    WHERE R.NAME IN ('', ... , '')
    AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;

    You may directly contact the users with transactions in the
    "pending offline" rollbacks and ask them to commit (preferably)
    or rollback immediately. If that is not feasible, you can force
    that to happen by killing their sessions. For each of the entries
    returned by the above query, issue the statement:

    ALTER SYSTEM KILL SESSION ', ';

    where and are those returned by the previous
    query. After the sessions are killed, it may take a few minutes
    before Oracle finishes rolling back and doing cleanup work. Go back
    to step 2 and repeat the query in there periodically until all
    rollbacks in the affected tablespace are offline and ready to be
    dropped.

    6. Drop the rollback tablespace.

    DROP TABLESPACE INCLUDING CONTENTS;

    If this statement fails, please contact Oracle Customer Support.
    Otherwise, proceed to step 7.

    7. Recreate the rollback tablespace.

    8. Recreate the rollback segments in the tablespace and bring them online.

    OCP 8i

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    Are you sure that if I loose my rollback datafile and I don't have enough Archived Log file to
    apply then I cannot recover my database?

    Vijay.
    Say No To Plastics

  6. #6
    Join Date
    Aug 2001
    Posts
    75
    Read carefully the above post. The above posting is from oracle. It says if you have cleanly shutdown your db i.e. with either immediate , normal, transactional then you could recover from the loss of rollback datafile.
    Hope this helps.

    Originally posted by oravijay
    Hi,

    Are you sure that if I loose my rollback datafile and I don't have enough Archived Log file to
    apply then I cannot recover my database?

    Vijay.
    OCP 8i

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