Offline System Rollback Segment
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Offline System Rollback Segment

  1. #1
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    We had a consultant to review our database, who came up with some mostly stupid suggestions. One of them was to take the system rollback segment offline, which I didn't think that this was possible. When I try I get:

    "ORA-01597: cannot alter system rollback segment online or offline"

    Before I demand the money back, can anyone confirm whether this is the case or not ?


  2. #2
    Join Date
    Sep 2001
    Posts
    112
    I was under the impression that 1 system rollback segment must remain on-line.

  3. #3
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    it depends on what you want to do, but technically speaking a rollback segment can be online or offline ...

    alter rollback segment ... online/offline

    nevertheless, an offline rollback segment is not that useful, offline is mostly used for maintenance tasks such as dropping and recreating a rollback when you first need to offline the rbs

  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Do you know what the consultant's intent/goal was?
    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    I think the idea was that we had to have a system rollback segment in order to create the database. But, once the database is created, we should take it offline to ensure that it's never used and that we are always using the non-system segments.

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    you need a rollback segment to create an instance, but the system rollback segment (USN=0) cannot be taken offline

  7. #7
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    It is only an assumption, but isn't a rollback segment required for the data dictionary tables?

    I haven't seen it in print, but this seems like a function for the SYSTEM tablespace.

    David Knight
    OCP DBA 8i, 9i, 10g

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by nealh
    We had a consultant to review our database, who came up with some mostly stupid suggestions. One of them was to take the system rollback segment offline, which I didn't think that this was possible. When I try I get:

    "ORA-01597: cannot alter system rollback segment online or offline"

    Before I demand the money back, can anyone confirm whether this is the case or not ?

    If he knows some magic trick about how to take the SYSTEM RBS offline, could he share it with us :-))

    Where from did you find that consultant!




  9. #9
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    Why even try to turn it off. If you try to use it for a dml statement you will recieve.

    bitbom:/home/oracle> oerr ora 1552
    01552, 00000, "cannot use system rollback segment for non-system tablespace '%s'"
    // *Cause: Tried to use the system rollback segment for operations involving
    // non-system tablespace. If this is a clone database then this will
    // happen when attempting any data modification outside of the system
    // tablespace. Only the system rollback segment can be online in a
    // clone database.
    // *Action: Create one or more private/public segment(s), shutdown and then
    // startup again. May need to modify the INIT.ORA parameter
    // rollback_segments to acquire private rollback segment. If this is
    // a clone database being used for tablspace point in time recovery
    // then this operation is not allowed.
    Doug

  10. #10
    Join Date
    Mar 2001
    Posts
    314
    My 2c's

    To give the consultant his due (???) - maybe he meant the additional RBS that oracle suggests we create in the SYSTEM tablespace, that needs to be taken offline ???

    Now, to the point - the SYSTEM RBS can't be taken offline period. However, its usage is controlled internally by oracle. At its (Oracle's) discretion, oracle may use the SYSTEM RBS for normal user operations too (if a situation arises say, when the load on the other RBS's become too much in Oracle's opinion).

    You can use it as a normal RBS, the limiting factor here is that the object you are trying to manipulate must be in the SYSTEM tablespace, e.g

    CREATE TABLE test (c1 NUMBER) TABLESPACE system;
    SET TRANSACTION USE ROLLBACK SEGMENT system;
    INSERT INTO test VALUES (1);
    COMMIT;

    The above will go through. However, the following will not:

    CREATE TABLE test (c1 NUMBER) TABLESPACE users;
    SET TRANSACTION USE ROLLBACK SEGMENT system;
    INSERT INTO test VALUES (1);

    ---- You get ORA 01552 Error here

    -amar


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