7 Replies Latest reply on Jan 12, 2012 4:55 AM by CKPT

    Help me! ORA-01552: cannot use system rollback segment

    910514
    910514 Jan 12, 2012 2:23 AM
      Hi everybody!

      I use Oracle Database 10gR2 in windows x86.

      Error step:
      SQL> create table hr.temp(
      2 temp1 varchar2(20));
      create table hr.temp(
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'
      ORA-06512: at line 957
      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'


      SQL> SELECT segment_name, owner, tablespace_name, segment_id, file_id, status
      2 FROM dba_rollback_segs ;

      SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
      ------------------------------ ------ ------------------------------ ----------
      FILE_ID STATUS
      ---------- ----------------
      SYSTEM SYS SYSTEM 0
      1 ONLINE

      _SYSSMU1$                      PUBLIC UNDOTBS1                                1
      2 OFFLINE

      _SYSSMU2$                      PUBLIC UNDOTBS1                                2
      2 OFFLINE


      SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
      ------------------------------ ------ ------------------------------ ----------
      FILE_ID STATUS
      ---------- ----------------
      _SYSSMU3$                      PUBLIC UNDOTBS1                                3
      2 OFFLINE

      _SYSSMU4$                      PUBLIC UNDOTBS1                                4
      2 OFFLINE

      _SYSSMU5$                      PUBLIC UNDOTBS1                                5
      2 OFFLINE


      SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
      ------------------------------ ------ ------------------------------ ----------
      FILE_ID STATUS
      ---------- ----------------
      _SYSSMU6$                      PUBLIC UNDOTBS1                                6
      2 OFFLINE

      _SYSSMU7$                      PUBLIC UNDOTBS1                                7
      2 OFFLINE

      _SYSSMU8$                      PUBLIC UNDOTBS1                                8
      2 OFFLINE


      SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
      ------------------------------ ------ ------------------------------ ----------
      FILE_ID STATUS
      ---------- ----------------
      _SYSSMU9$                      PUBLIC UNDOTBS1                                9
      2 OFFLINE

      _SYSSMU10$                     PUBLIC UNDOTBS1                               10
      2 OFFLINE

      _SYSSMU11$                     PUBLIC UNDOTBS1                               11
      2 OFFLINE


      12 rows selected.

      SQL> ALTER ROLLBACK SEGMENT "_SYSSMU1$" ONLINE;
      ALTER ROLLBACK SEGMENT "_SYSSMU1$" ONLINE
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'
      ORA-06512: at line 957
      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'


      SQL> select file#,name from v$datafile where file#=1;

      FILE#
      ----------
      NAME
      --------------------------------------------------------------------------------
      1
      E:\ORACLE\BISE1\ORADATA\BISE1DB\SYSTEM01.DBF


      SQL> select file#,name from v$datafile where file#=2;

      FILE#
      ----------
      NAME
      --------------------------------------------------------------------------------
      2
      E:\ORACLE\BISE1\ORADATA\BISE1DB\UNDOTBS01.DBF


      SQL> select file#,name,status,enabled from v$datafile where file#=2;

      FILE#
      ----------
      NAME
      --------------------------------------------------------------------------------
      STATUS ENABLED
      ------- ----------
      2
      E:\ORACLE\BISE1\ORADATA\BISE1DB\UNDOTBS01.DBF
      ONLINE READ WRITE


      SQL> recover datafile 2;
      ORA-00283: recovery session canceled due to errors
      ORA-01124: cannot recover data file 2 - file is in use or recovery
      ORA-01110: data file 2: 'E:\ORACLE\BISE1\ORADATA\BISE1DB\UNDOTBS01.DBF'


      SQL> CREATE UNDO TABLESPACE undotbs4
      2 datafile 'E:/ORACLE/bise1/oradata/bise1db/undotbs02.dbf' size 1024M reuse AUTOEXTEND ON;
      CREATE UNDO TABLESPACE undotbs4
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'
      ORA-06512: at line 957
      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'

      Please help me!
      • 2214 Views
      • Tags:
        • 1. Re: Help me! ORA-01552: cannot use system rollback segment
          sb92075
          sb92075 Jan 12, 2012 2:27 AM (in response to 910514)
          please explain how you managed to dig yourself into such a deep hole.

          When was last time when the DB worked without error?
          What changed since then?
          • 2. Re: Help me! ORA-01552: cannot use system rollback segment
            910514
            910514 Jan 12, 2012 2:43 AM (in response to sb92075)
            My database use noarchivelog mode.

            SQL> recover database until cancel using backup controlfile;
            ORA-00279: change 14430321813 generated at 01/12/2012 09:34:10 needed for
            thread 1
            ORA-00289: suggestion :
            E:\ORACLE\BISE1\FLASH_RECOVERY_AREA\BISE1DB\ARCHIVELOG\2012_01_12\O1_MF_1_36_%U
            .ARC
            ORA-00280: change 14430321813 for thread 1 is in sequence #36


            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
            cancel
            Media recovery cancelled.
            SQL> alter database open
            2 ;
            alter database open
            *
            ERROR at line 1:
            ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


            SQL> alter database open resetlogs
            2 ;

            Database altered.

            SQL> create hr.abc(abc varchar2(10));
            create hr.abc(abc varchar2(10))
            *
            ERROR at line 1:
            ORA-00901: invalid CREATE command


            SQL> create table hr.abc(abc varchar2(10));
            create table hr.abc(abc varchar2(10))
            *
            ERROR at line 1:
            ORA-00604: error occurred at recursive SQL level 1
            ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'
            ORA-06512: at line 957
            ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'

            What about change db with time?
            • 3. Re: Help me! ORA-01552: cannot use system rollback segment
              sb92075
              sb92075 Jan 12, 2012 2:48 AM (in response to 910514)
              907511 wrote:
              My database use noarchivelog mode.
              Above is a mistake.
              SQL> alter database open resetlogs
              2 ;

              Database altered.
              Above is a mistake.
              What about change db with time?
              I can almost 100% guarantee that you've rendered this DB useless.
              • 4. Re: Help me! ORA-01552: cannot use system rollback segment
                910514
                910514 Jan 12, 2012 3:17 AM (in response to sb92075)
                My DB used to test, install any application, not enough hard disk for config archivelog.
                Server don't use UPS and lose power.
                When start server, the error occurred.
                • 5. Re: Help me! ORA-01552: cannot use system rollback segment
                  sb92075
                  sb92075 Jan 12, 2012 3:24 AM (in response to 910514)
                  907511 wrote:
                  My DB used to test, install any application, not enough hard disk for config archivelog.
                  Server don't use UPS and lose power.
                  When start server, the error occurred.
                  If the data is important & folks are willing to write a LARGE check to Oracle,
                  Oracle may be able to extract table data from the now corrupt datafiles.
                  • 6. Re: Help me! ORA-01552: cannot use system rollback segment
                    910514
                    910514 Jan 12, 2012 3:37 AM (in response to sb92075)
                    Thank for your answer!
                    • 7. Re: Help me! ORA-01552: cannot use system rollback segment
                      CKPT
                      CKPT Jan 12, 2012 4:55 AM (in response to 910514)
                      ERROR at line 1:
                      ORA-00604: error occurred at recursive SQL level 1
                      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'
                      ORA-06512: at line 957
                      ORA-01552: cannot use system rollback segment for non-system tablespace 'GGS'
                      Error:  ORA 1552 
Text:   cannot use system rollback segment for non-system tablespace "<name>" 
-------------------------------------------------------------------------------
Cause:  An attempt was made to use the SYSTEM rollback segment for operations 
        involving a non-system tablespace.
Action: Create one or more rollback segments and then use ALTER ROLLBACK 
        SEGMENT 'name' online.
        It may be necessary to modify the initialization parameter 
        ROLLBACK_SEGMENTS to acquire one of the new private rollback segments.
        Before creating a rollback segment outside the SYSTEM tablespace, it 
        is necessary to first create and activate a non-system rollback 
        segment in the SYSTEM tablespace.
        
Explanation:
        Ensure there is at least one other rollback segment online
        before attempting operations in a non-SYSTEM tablespace.
        The view DBA_ROLLBACK_SEGS shows rollback segments and their
        status.