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 ?

Back to index of questions


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.


Back to top

Back to index of questions