ORA-01552: cannot use system rollback segment for non-system tablespace

Recently I have met with an issue where the undo tablespace of the Prod database is got corrupted and we required to create the new undo tablespace coz we had no undo backup. While creating the undo tablespace we got the following error:

SQL> create undo tablespace undotbs2
  2  datafile '/u01/app/oracle/oradata/db1/undotbs01.dbf' size 100m;
create undo tablespace undotbs2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'GGATE'
ORA-06512: at line 993
ORA-01552: cannot use system rollback segment for non-system tablespace 'GGATE'

I have implemented the same test case over my DEV server which was identical in nature with the Production servers.

Following are Environment.

                    Dev1 database: source.xxx.com

                    Dev2 database: target.xxx.com

                    Database Version: 11203

                    Golden gate Version: 11.1

                    Golden Gate schema: GGATE                  

                    Replication Method: DDL ACTIVE-ACTIVE Replication

I  moved the undo datafile at OS level.

[oracle@source ~]$ cd /u01/app/oracle/oradata/db1/

[oracle@source db1]$ ls -al undo*
-rw-r----- 1 oracle oinstall 188751872 Aug  1 11:59 undotbs01.dbf
[oracle@source ~]$mv undotbs01.dbf undotbs01.dbf.bkp

SQL> startup
ORACLE instance started.

Total System Global Area 1690705920 bytes
Fixed Size                  1345380 bytes
Variable Size            1006635164 bytes
Database Buffers          671088640 bytes
Redo Buffers               11636736 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/db1/undotbs01.dbf'

Excerpts from alert log.
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_dbw0_2626.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/db1/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

Now change the undo management parameter to MANUAL and then bounce the database.

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

System altered.

SQL> shut immediate

SQL> startup mount

SQL> select FILE#,NAME,STATUS from v$datafile

  2  ;

     FILE# NAME                                               STATUS

---------- -------------------------------------------------- -------

         1 /u01/app/oracle/oradata/db1/system01.dbf           SYSTEM

         2 /u01/app/oracle/oradata/db1/sysaux01.dbf           ONLINE

         3 /u01/app/oracle/oradata/db1/undotbs01.dbf          ONLINE

         4 /u01/app/oracle/oradata/db1/users01.dbf            ONLINE

         5 /u01/app/oracle/oradata/db1/ggate_01.dbf           ONLINE

Lets drop the undo tablespace data file from database level in offline state.

SQL> alter database datafile '/u01/app/oracle/oradata/db1/undotbs01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> create undo tablespace undotbs2

  2  datafile '/u01/app/oracle/oradata/db1/undotbs02.dbf' size 100m;

create undo tablespace undotbs2

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'GGATE'

ORA-06512: at line 993

ORA-01552: cannot use system rollback segment for non-system tablespace 'GGATE'

SQL> drop tablespace undotbs1 including contents;

drop tablespace undotbs1 including contents

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'GGATE'

ORA-06512: at line 993

ORA-01552: cannot use system rollback segment for non-system tablespace 'GGATE'

SQL> !oerr ora 01552

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.  If the non-system tablespace

//         has AUTO segment space management, then create an undo tablespace.

SQL>  select segment_name, status from dba_rollback_segs;

SEGMENT_NAME                   STATUS

------------------------------ ----------------

SYSTEM                         ONLINE

_SYSSMU10_2490256178$          OFFLINE

_SYSSMU9_3593450615$           OFFLINE

_SYSSMU8_1909280886$           OFFLINE

_SYSSMU7_1924883037$           OFFLINE

_SYSSMU6_2460248069$           OFFLINE

_SYSSMU5_3787622316$           OFFLINE

_SYSSMU4_1455318006$           OFFLINE

_SYSSMU3_2210742642$           OFFLINE

_SYSSMU2_4228238222$           OFFLINE

_SYSSMU1_3138885392$           OFFLINE

11 rows selected.

SQL> select * from v$rollname;

       USN NAME

---------- --------------------------------------------------

         0 SYSTEM

Solution

To get rid of the above issues we need to disable the system triggers and then create the new undo tablespace. Once the tablespace is created now enable those triggers.

Make sure database is in OPEN mode.

SQL> alter system set "_system_trig_enabled" = FALSE;

System altered.

SQL>  alter trigger sys.cdc_alter_ctable_before DISABLE;

Trigger altered.

SQL> alter trigger sys.cdc_create_ctable_after DISABLE;

Trigger altered.

SQL> alter trigger sys.cdc_create_ctable_before DISABLE;

Trigger altered.

SQL> alter trigger sys.cdc_drop_ctable_before DISABLE;

Trigger altered.

SQL> create undo tablespace UNDOTBS2

  2  datafile '/u01/app/oracle/oradata/db1/undotbs02.dbf' size 200m;

Tablespace created.

SQL> alter system set undo_tablespace=UNDOTBS2 scope=spfile;

System altered.

SQL> drop tablespace UNDOTBS1 including contents;

Tablespace dropped.

SQL> alter trigger sys.cdc_alter_ctable_before ENABLE;

Trigger altered.

SQL> alter trigger sys.cdc_create_ctable_after ENABLE;

Trigger altered.

SQL> alter trigger sys.cdc_create_ctable_before ENABLE;

Trigger altered.

SQL> alter trigger sys.cdc_drop_ctable_before ENABLE;

Trigger altered.

SQL> alter system set "_system_trig_enabled" = TRUE;

System altered.

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

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1690705920 bytes

Fixed Size                  1345380 bytes

Variable Size            1006635164 bytes

Database Buffers          671088640 bytes

Redo Buffers               11636736 bytes

Database mounted.

Database opened.

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

SQL> select name,status from v$datafile

  2  where name like '%undo%';

NAME                                               STATUS

-------------------------------------------------- -------

/u01/app/oracle/oradata/db1/undotbs02.dbf          ONLINE

Thanks for your time.