ORA-01552: non-system tablespace 'xxxx 'cannot use system rollback segments

ORA-01552: non-system tablespace 'xxxx 'cannot use system rollback segments


Fault analysis:
The undo tablespace data file is damaged, causing the update operation to fail.


Troubleshooting:
The new undo tablespace is set to default and auto. The database is opened normally.


Microsoft Windows [version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.


C: \ Documents ents and Settings \ Administrator> sqlplus "/As sysdba"


SQL * Plus: Release 10.2.0.1.0-production on Tuesday August 26 15:27:45 2014


Copyright (c) 1982,200 5, Oracle. All rights reserved.




Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options


SQL> select open_mode from V $ database;


Open_mode
----------
Read Write


SQL> show parameter undo


Name type value
-----------------------------------------------------------------------------
Undo_management string Manual
Undo_retention integer 900
Undo_tablespace string undotbs1
SQL> alter system set undo_management = auto scope = spfile;


The system has been changed.


SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> startup
The Oracle routine has been started.


Total system global area 272629760 bytes
Fixed size 1248476 bytes
Variable Size 88081188 bytes
Database buffers 176160768 bytes
Redo buffers 7139328 bytes
The database has been loaded.
ORA-00607: Internal error when changing data blocks
ORA-00600: Internal error code, parameter: [4194], [19], [13], [], [], [], [], [], []




SQL> shutdown abort
The Oracle routine has been disabled.
SQL> Conn/As sysdba
Already connected to the idle routine.


SQL> startup Mount
The Oracle routine has been started.


Total system global area 272629760 bytes
Fixed size 1248476 bytes
Variable Size 88081188 bytes
Database buffers 176160768 bytes
Redo buffers 7139328 bytes
The database has been loaded.
SQL> show parameter undo


Name type value
-----------------------------------------------------------------------------
Undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs1


SQL> alter system set undo_management = manual scope = spfile;


The system has been changed.


SQL> shutdown immediate;
ORA-01109: the database is not open




The database has been detached.
The Oracle routine has been disabled.
SQL> startup
The Oracle routine has been started.


Total system global area 272629760 bytes
Fixed size 1248476 bytes
Variable Size 88081188 bytes
Database buffers 176160768 bytes
Redo buffers 7139328 bytes
The database has been loaded.
The database has been opened.
SQL> select name from V $ datafile;


Name
--------------------------------------------------------------------------------


E: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ system01.dbf
E: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ undotbs01.dbf
E: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ sysaux01.dbf
E: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ users01.dbf
E: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ example01.dbf
E: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ ccen. ora


You have selected 6 rows.


SQL> Create undo tablespace undotbs2 datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \
Oracle \ undotbs02.dbf 'size 200 m;


The tablespace has been created.


SQL> show parameter undo;


Name type value
-----------------------------------------------------------------------------
Undo_management string Manual
Undo_retention integer 900
Undo_tablespace string undotbs1
SQL> alter system set undo_tablespace = undotbs2 scope = spfile;


The system has been changed.


SQL> alter system set undo_management = auto scope = spfile;


The system has been changed.


SQL> show parameter spfile;


Name type value
-----------------------------------------------------------------------------
Spfile string E: \ oracle \ product \ 10.2.0 \ db_1 \
Database \ spfileoracle. ora
SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> startup
The Oracle routine has been started.


Total system global area 272629760 bytes
Fixed size 1248476 bytes
Variable Size 88081188 bytes
Database buffers 176160768 bytes
Redo buffers 7139328 bytes
The database has been loaded.
The database has been opened.
SQL> show parameter undo


Name type value
-----------------------------------------------------------------------------
Undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs2
SQL>

ORA-01552: non-system tablespace 'xxxx 'cannot use system rollback segments