8 Replies Latest reply: Apr 17, 2013 10:34 PM by sulimo RSS

    Cannot drop undo tablespace

    msses
      Hello:

      I've created a new undo tablespace undotbs2. I've set it to the default undo tablespace. When I've tried to drop the old one, I get the following error:

      SQL> conn / as sysdba
      Connected.
      SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
      DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES

      ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU7$' found, terminate dropping tablespace

      This is a production database. The service is down because the partition with the Oracle tablespaces: system, undo, temp, etc is full.

           
      After shutting down the database the problem persist: I cannot drop the old undo tablespace because of the same error.

      I've followed the note 1321093.1. Here the query results:

      Select segment_name, status, tablespace_name, rs.segment_id
      from dba_rollback_segs rs
      where status not in ('ONLINE','OFFLINE');

      _SYSSMU7$ PARTLY AVAILABLE UNDOTBS1 7


      SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags
      FROM x$ktuxe
      WHERE ktuxesta !='INACTIVE' AND ktuxeusn =7;


      7 47 382687 ACTIVE DEAD

      SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;

      No rows


      SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;

      No rows

      As you can see, there is no pending transactions. No application is connected to the database (service is down), however the database is appling rollbacks since 2 days.

      A lot of archivelogs are being created at rolling back (30 GB archivelogs per hour). Here a tail of the alertlog file:

      ...
      Wed Apr 17 06:19:53 2013
      Thread 1 advanced to log sequence 7382
      Current log# 5 seq# 7382 mem# 0: /ora2/origlogs/webfrbr/redo05_m1.log
      Current log# 5 seq# 7382 mem# 1: /ora3/mirrorlogs/webfrbr/redo05_m2.log
      Wed Apr 17 06:21:16 2013
      Thread 1 advanced to log sequence 7383
      Current log# 6 seq# 7383 mem# 0: /ora2/origlogs/webfrbr/redo06_m1.log
      Current log# 6 seq# 7383 mem# 1: /ora3/mirrorlogs/webfrbr/redo06_m2.log
      Wed Apr 17 06:22:41 2013
      Thread 1 advanced to log sequence 7384
      Current log# 4 seq# 7384 mem# 0: /ora2/origlogs/webfrbr/redo04_m1.log
      Current log# 4 seq# 7384 mem# 1: /ora3/mirrorlogs/webfrbr/redo04_m2.log
      Wed Apr 17 06:24:02 2013
      Thread 1 advanced to log sequence 7385
      Current log# 5 seq# 7385 mem# 0: /ora2/origlogs/webfrbr/redo05_m1.log
      Current log# 5 seq# 7385 mem# 1: /ora3/mirrorlogs/webfrbr/redo05_m2.log
      Wed Apr 17 06:25:24 2013
      Thread 1 advanced to log sequence 7386
      Current log# 6 seq# 7386 mem# 0: /ora2/origlogs/webfrbr/redo06_m1.log
      Current log# 6 seq# 7386 mem# 1: /ora3/mirrorlogs/webfrbr/redo06_m2.log
      Wed Apr 17 06:26:46 2013
      Thread 1 advanced to log sequence 7387
      Current log# 4 seq# 7387 mem# 0: /ora2/origlogs/webfrbr/redo04_m1.log
      Current log# 4 seq# 7387 mem# 1: /ora3/mirrorlogs/webfrbr/redo04_m2.log



      Oracle Database - Enterprise Edition 10.2.0.3
      Linux x86-64 Oracle Linux 5



      Please, consider the output of the following queries too:

      select * from V$FAST_START_TRANSACTIONS

      USN 7 -- Undo segment number of the transaction
      SLT 47 -- Slot within the rollback segment
      SEQ 382687 -- Incarnation number of the slot
      STATE RECOVERING --
      UNDOBLOCKSDONE 0 -- Number of undo blocks completed on the transaction
      UNDOBLOCKSTOTAL 2079151 -- Total number of undo blocks that need recovery
      PID 15 -- ID of the current server it has been assigned to
      CPUTIME 1153 -- Time for which recovery has progressed (in seconds)
      PARENTUSN 0 -- Undo segment number of the parent transaction in PDML
      PARENTSLT 0 -- Slot of the parent transaction in PDML
      PARENTSEQ 0 -- Sequence number of the parent transaction in PDML
      XID 07002F00DFD60500 -- Transaction ID
      PXID 0000000000000000 -- Parent transaction ID
      RCVSERVERS 1 -- Number of servers used in the last recovery

      select * from x$ktuxe where ktuxecfl = 'DEAD';

      ADDR 00002B3E808B9BD8
      INDX 145
      INST_ID 1
      KTUXEUSN 7
      KTUXESLT 47
      KTUXESQN 382687
      KTUXERDBF 2
      KTUXERDBB 2080076
      KTUXESCNB 424110769
      KTUXESCNW 0
      KTUXESTA ACTIVE
      KTUXECFL DEAD
      KTUXEUEL 3739
      KTUXEDDBF 0
      KTUXEDDBB 0
      KTUXEPUSN 0
      KTUXEPSLT 0
      KTUXEPSQN 0
      KTUXESIZ 2079151

      select * from v$fast_start_servers;

      RECOVERING 0 15 07002F00DFD60500
      IDLE 0 16 0000000000000000
      IDLE 0 17 0000000000000000
      IDLE 0 18 0000000000000000
      IDLE 0 19 0000000000000000
      IDLE 0 20 0000000000000000
      IDLE 0 21 0000000000000000
      IDLE 0 22 0000000000000000
      IDLE 0 25 0000000000000000
      IDLE 0 26 0000000000000000
      IDLE 0 27 0000000000000000
      IDLE 0 28 0000000000000000
      IDLE 0 29 0000000000000000
      IDLE 0 30 0000000000000000
      IDLE 0 31 0000000000000000
      IDLE 0 32 0000000000000000


      Any ideas to solve this issue?

      Thank you in advance

      Edited by: albrotar on Apr 17, 2013 1:48 AM

      Edited by: albrotar on Apr 17, 2013 1:49 AM

      Edited by: albrotar on Apr 17, 2013 1:51 AM

      Edited by: albrotar on Apr 17, 2013 1:52 AM

      Edited by: albrotar on Apr 17, 2013 1:54 AM
        • 1. Re: Cannot drop undo tablespace
          Fran
          a workaround:
          http://davidalejomarcos.wordpress.com/2010/11/09/ora-01548-active-rollback-segment-syssmu1xxxx-found-terminate-dropping-tablespace-while-deleting-undotbs/
          • 2. Re: Cannot drop undo tablespace
            Hemant K Chitale
            You should immediately log an SR with Oracle Support.


            See if configuring FAST_START_PARALLE_ROLLBACK to FALSE and restarting the instance (you'd have to SHUTDOWN ABORT and STARTUP) helps.
            I suggest that you log an SR with Oracle Support first and get their advice on your options.


            Hemant K Chitale
            • 3. Re: Cannot drop undo tablespace
              877785
              hey

              Try to Shut down instances that use the active rollback segments in the tablespace and then drop the tablespace.

              Regards
              • 4. Re: Cannot drop undo tablespace
                msses
                Hello,

                I've logged a SR the last monday. The Oracle support told me that the simple way and workaround to solve the issue is to shutdown the database instances that use the active rollback segments and then drop the tablespace, but it did not help.

                They told me that otherwise I can follow the steps clarified in the note ID 1321093.1 Unfortunately, it did not help too.

                At this time, they are downloading/reviewing the situation and will update the SR as soon as possible. The problem is that this is a production database, and the users of the application are affected.


                Best regards,

                Marco Antonio Serrano
                • 5. Re: Cannot drop undo tablespace
                  msses
                  I've found some errors in alert log file occurred at shutting down the database:

                  +...+
                  Control autobackup written to DISK device
                  handle '/orabck/rman/webfrbr/ora_cfc-3587726327-20130414-00'
                  Completed: CREATE UNDO TABLESPACE undotbs2 DATAFILE '/orabck/undo/undotbs02.dbf'
                  SIZE 500M reuse autoextend ON NEXT 5M maxsize 32767M
                  Sun Apr 14 15:46:43 2013
                  Successfully onlined Undo Tablespace 5.
                  Undo Tablespace 1 moved to Pending Switch-Out state.
                  *** active transactions found in undo tablespace 1 during switch-out.
                  Sun Apr 14 15:46:44 2013
                  ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;
                  Sun Apr 14 16:11:42 2013
                  ALTER ROLLBACK SEGMENT "_SYSSMU7$" offLINE
                  Sun Apr 14 16:11:42 2013
                  Completed: ALTER ROLLBACK SEGMENT "_SYSSMU7$" offLINE
                  Sun Apr 14 16:12:36 2013
                  DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
                  Sun Apr 14 16:12:36 2013

                  +...+

                  Thread 1 advanced to log sequence 5107
                  Current log# 4 seq# 5107 mem# 0: /ora2/origlogs/webfrbr/redo04_m1.log
                  Current log# 4 seq# 5107 mem# 1: /ora3/mirrorlogs/webfrbr/redo04_m2.log
                  Sun Apr 14 19:00:28 2013
                  Stopping background process CJQ0
                  Sun Apr 14 19:00:29 2013
                  Stopping background process QMNC
                  Sun Apr 14 19:00:31 2013
                  Stopping background process MMNL
                  Sun Apr 14 19:00:32 2013
                  Stopping background process MMON
                  Sun Apr 14 19:00:34 2013
                  Shutting down instance (immediate)
                  License high water mark = 31
                  Sun Apr 14 19:00:34 2013
                  Stopping Job queue slave processes
                  Sun Apr 14 19:00:34 2013
                  Job queue slave processes stopped
                  All dispatchers and shared servers shutdown
                  Sun Apr 14 19:00:42 2013
                  ALTER DATABASE CLOSE NORMAL
                  Sun Apr 14 19:04:28 2013

                  +...+

                  Thread 1 advanced to log sequence 5118
                  Current log# 6 seq# 5118 mem# 0: /ora2/origlogs/webfrbr/redo06_m1.log
                  Current log# 6 seq# 5118 mem# 1: /ora3/mirrorlogs/webfrbr/redo06_m2.log
                  Sun Apr 14 20:11:21 2013
                  Errors in file /ora1/admin/webfrbr/bdump/webfrbr_smon_4629.trc:
                  +ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] [Invalid permissions for mapped object] [0x2B6B66003000] [] []+*
                  Sun Apr 14 20:11:21 2013
                  Errors in file /ora1/admin/webfrbr/bdump/webfrbr_smon_4629.trc:
                  ORA-00600: internal error code, arguments: [999], [0x107C14015], [], [], [], [], [], []*
                  +ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] [Invalid permissions for mapped object] [0x2B6B66003000] [] []+*
                  Sun Apr 14 20:11:22 2013

                  +...+

                  Sun Apr 14 20:11:48 2013
                  Errors in file /ora1/admin/webfrbr/bdump/webfrbr_pmon_4617.trc:
                  ORA-00474: SMON process terminated with error
                  Sun Apr 14 20:11:48 2013
                  +...+


                  The instance could start up, but after this, it is recovering undo and generating redo since 2 days
                  • 6. Re: Cannot drop undo tablespace
                    msses
                    I attach here the trace file of the SMON process

                    /ora1/admin/webfrbr/bdump/webfrbr_smon_6939.trc
                    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP and Data Mining options
                    ORACLE_HOME = /opt/oracle/product/10.2
                    System name:     Linux
                    Node name:     WEB-BDNEW
                    Release:     2.6.18-164.el5xen
                    Version:     #1 SMP Thu Sep 3 04:41:04 EDT 2009
                    Machine:     x86_64
                    Instance name: webfrbr
                    Redo thread mounted by this instance: 1
                    Oracle process number: 8
                    Unix process pid: 6939, image: oracle@WEB-BDNEW (SMON)

                    *** 2013-04-14 20:19:09.264
                    *** SERVICE NAME:() 2013-04-14 20:19:09.264
                    *** SESSION ID:(494.1) 2013-04-14 20:19:09.264
                    SMON: about to recover undo segment 7
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7
                    SMON: mark undo segment 7 as available
                    SMON: about to recover undo segment 7

                    ...
                    • 7. Re: Cannot drop undo tablespace
                      Hemant K Chitale
                      This is something you need to follow up with Oracle Support.


                      Hemant K Chitale
                      • 8. Re: Cannot drop undo tablespace
                        sulimo
                        Have you tried:

                        1) disable SMON transaction rollback/recovery (event 10513)
                        2) Kill the PQs doing parallel transaction recovery
                        3) Disable parallel rollback (alter system set fast_start_parallel_rollback=false;)
                        4) Enable SMON to do serial recovery again (event 10513 off)