The Oracle (tm) Users' Co-Operative FAQ
Every time when I restart the database I have to bring all my rollback segments back online manually. Why ?
| Author's name: Norman Dunbar
Author's Email: Oracle@bountifulsolutions.co.uk |
Date written: 08/03/2002 Oracle version(s): 7.3.4 onwards |
| None of my rollback segments come on-line when I startup my database. What do I have to do to make sure that they do ? |
In order to have a private rollback segment come on-line at database startup, you must ensure that the rollback segment names are mentioned in the initSID.ora file as follows, assuming you have already created 4 rollback segments named r01, r02, r03 and r04 :
rollback_segments = (r01, r02, r03, r04)
Once the database is open :
SQL> select segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE R01 ONLINE R02 ONLINE R03 ONLINE R04 ONLINE SQL>
You can see that the rollback segments mentions in the initSID.ora have indeed been brought on-line.
You must never put the SYSTEM rollback segment name in the list - it always comes on-line, as can be seen from the above output. You must only put the names of already created rollback segments in the list. If you have a name that does not represent a rollback segment, the database will not start correctly - as shown below :
SVRMGR> startup ORACLE instance started. Total System Global Area 65322004 bytes Fixed Size 76820 bytes Variable Size 47828992 bytes Database Buffers 16384000 bytes Redo Buffers 1032192 bytes Database mounted. ORA-01534: rollback segment 'OOPS' doesn't exist SVRMGR>
If the rollback_segments parameter is missing from iniSID.ora, then the database will, by default, use any public rollback segments that it knows about by bringing them on-line at startup. The following shows this in action :
SQL> create public rollback segment public_rbs 2 tablespace rbs; Rollback segment created. SQL> select segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE PUBLIC_RBS OFFLINE R01 ONLINE R02 ONLINE R03 ONLINE R04 ONLINE 6 rows selected. SQL>
After the instance has been 'bounced' we find the following :
SQL> select segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE PUBLIC_RBS ONLINE R01 ONLINE R02 ONLINE R03 ONLINE R04 ONLINE 6 rows selected. SQL>
Private and Public Rollback Segments
If you are not running Parallel Server, then a private and public rollback segment are effectively the same - except that a public one comes on-line at startup without any further action from the DBA.
If you are running Parallel Server, then many instances can access a single database. Each instance can have its own private rollback segments - which are used by itself, and can also acquire a public rollback segment from the pool which is shared between all the instances.
Further reading:
Oracle Reference manual, chapter 1, Initialization Parameters
Oracle Administrators Guide, chapter 2 - Parameters, and chapter
18 Managing Rollback Segments
Oracle Concepts Manual.
