Printable View
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 ?
I was under the impression that 1 system rollback segment must remain on-line.
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
Do you know what the consultant's intent/goal was?
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.
you need a rollback segment to create an instance, but the system rollback segment (USN=0) cannot be taken offline
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.
If he knows some magic trick about how to take the SYSTEM RBS offline, could he share it with us :-))Quote:
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 ?
Where from did you find that consultant!
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.
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
as a practice dbas may create a separate rollback segment after the database is created to handle the jobs of creating tablespaces, rollback segments and running the scripts. after all these jobs are run that rollback segment is taken off line for the simple reason that the second rbs is created in system tablespace. if the name of that 2nd rbs is 'systemrbs' that is ok. but the rbs created by oracle for the maintenance of meta data tables at the time of creating the database can not be taken offline.
Sorry about not completely covering all the bases with using the system rollback segment for transactions. Yes, of course there was a way, as proved above. But if you follow Oracle's rules on the system tablespace you should never run into this problem. That rule being never to create objects in the system tablespace.