Fixed - ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

how to drop and recreate undo tablespace in oracle 11g

/dev/mapper/vg_root-lv_oracle           6.8G  6.3G  155M  98% /u01

[root@oracle-db-server11 XE]# pwd
/u01/app/oracle/oradata/XE
[root@oracle-db-server11 XE]# ls -ltrh
total 4.8G
-rw-r-----. 1 oracle dba  21M Sep  6 00:23 temp.dbf
-rw-r-----. 1 oracle dba 581M Sep  6 04:25 system.dbf
-rw-r-----. 1 oracle dba 101M Sep  6 04:25 users.dbf
-rw-r-----. 1 oracle dba 791M Sep  6 04:30 sysaux.dbf
-rw-r-----. 1 oracle dba 3.3G Sep  6 04:32 undotbs1.dbf
-rw-r-----. 1 oracle dba 9.4M Sep  6 04:33 control.dbf

SQL>  drop tablespace UNDOTBS1 including contents and datafiles;
 drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/oracle/oradata/XE/users.dbf

SYSAUX
/u01/app/oracle/oradata/XE/sysaux.dbf

UNDOTBS1
/u01/app/oracle/oradata/XE/undotbs1.dbf

SQL> alter tablespace undotbs1  offline;
alter tablespace undotbs1  offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/XE/undotbs2.dbf' size 100M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

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

System altered.

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
[root@oracle-db-server11 XE]# /etc/init.d/oracle-xe stop
Stopping oracle-xe (via systemctl):                        [  OK  ]
[root@oracle-db-server11 XE]# /etc/init.d/oracle-xe start
Starting oracle-xe (via systemctl):                        [  OK  ]

[root@oracle-db-server11 XE]# sqlplus system/oracle@xe;

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 6 05:12:15 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2


SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME
------ ------------------------------ ------------------------------
STATUS
----------------
SYS    SYSTEM                         SYSTEM
ONLINE

PUBLIC _SYSSMU10_2569484742$          UNDOTBS1
OFFLINE

PUBLIC _SYSSMU1_3789641169$           UNDOTBS1
OFFLINE


If any one the above segment is online then change it to offline 
SQL>alter rollback segment "_SYSSMU10_2569484742$" offline;


Now Drop 

SQL>  drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.


SQL> exit

FIND BACKUP RMAN HISTORY:

1. SQL> col STATUS format a9  
2. SQL> col hrs format 999.99  
3. SQL> select SESSION_KEY, INPUT_TYPE, STATUS, 
4. to_char(START_TIME,'mm/dd/yy hh24:mi')
5. start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
6.  elapsed_seconds/3600 hrs
7.  from V$RMAN_BACKUP_JOB_DETAILS order by session_key;  

RMAN SIZE AND TIMINGS:

col "Total Time" for a30;

col size for a30;

set lines 999;

select session_key "Session",

       input_type "Type",

       status "Status",

       to_char(start_time,'yyyy-mm-dd hh24:mi') "Start Time",

       to_char(end_time,'yyyy-mm-dd hh24:mi')  "End Time",

       output_bytes_display "Size",

       time_taken_display "Total Time"

from v$rman_backup_job_details

where to_char(end_time,'yyyy-mm-dd hh24:mi') >= to_char(sysdate-7,'yyyy-mm-dd hh24:mi') 

and STATUS='COMPLETED'

order by session_key asc;

col TYPE for a20 Heading "Backup Type"
col completion_time Heading "Completion Time"
col MB for 99999999999  Heading "Size (MB)"
col BCPTIME for 99999999999 Heading "Backup Time (minutes)"

SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time
       , TYPE
       , ROUND (SUM (bytes) / 1048576)          MB
       , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME
    FROM (SELECT CASE
                    WHEN s.backup_type = 'L' THEN 'Archive Log'
--                    WHEN s.controlfile_included = 'YES' THEN 'Control File'
                 WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'
                    WHEN s.backup_type = 'I' THEN 'Incemental (Level ' || s.incremental_level || ')'
                    ELSE s.backup_type
                 END
                    TYPE
               , TRUNC (s.completion_time) completion_time
               , p.tag
               , p.bytes
               , s.elapsed_seconds
            FROM v$backup_piece p, v$backup_set s
           WHERE status = 'A' AND p.recid = s.recid
          UNION ALL
          SELECT 'Datafile Copy' TYPE, TRUNC (completion_time), tag, output_bytes, 0 elapsed_seconds FROM v$backup_copy_details)
GROUP BY tag, TO_CHAR (completion_time, 'YYYY-MON-DD'), TYPE
ORDER BY 1 ASC, 2, 3;