Offline System Rollback Segment

[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 ?

[UNIX DBA]

I was under the impression that 1 system rollback segment must remain on-line.

[pipo]

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

[dknight]

Do you know what the consultant's intent/goal was?

[nealh]

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.

[pipo]

you need a rollback segment to create an instance, but the system rollback segment (USN=0) cannot be taken offline

[dknight]

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.

[julian]

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!

[molonede]

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.

[amar]

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