dropping tablespace is not releasing space in red hat

issue :

Drop tablespace including contents and datafiles . The space is not released at disk level

SQL> select distinct owner from dba_segments where tablespace_name='APPCHE_DATA';

no rows selected

SQL> drop tablespace APPCHE_DATA  INCLUDING CONTENTS AND DATAFILES ;

Tablespace dropped.

SQL> select BYTES/1024/1024,FILE_NAME,autoextensible,maxbytes/1024/1024 as mb from dba_Data_files where TABLESPACE_NAME='APPCHE_INDEX';

BYTES/1024/1024 FILE_NAME                                                                                            AUT         MB
--------------- ---------------------------------------------------------------------------------------------------- --- ----------
            100 /appl/oradata/MyappP/db01/MyappP_APPCHE_index_01.dbf                                               YES       8192
            100 /appl/oradata/MyappP/db02/MyappP_APPCHE_index_02.dbf                                               YES       8192
            100 /appl/oradata/MyappP/db03/MyappP_APPCHE_index_03.dbf                                               YES       8192
            100 /appl/oradata/MyappP/db04/MyappP_APPCHE_index_04.dbf                                               YES       8192

SQL> select distinct owner from dba_segments where tablespace_name='APPCHE_INDEX';

no rows selected

SQL>  drop tablespace APPCHE_INDEX  INCLUDING CONTENTS AND DATAFILES ;

Tablespace dropped.

SQL> exit


> df -h |grep db
                      3.9G  804M  3.1G  21% /appl/oradata/xdb01
                      3.9G  804M  3.1G  21% /appl/oradata/xdb02
/dev/mapper/vg04-db01
                       50G   35G   15G  71% /appl/oradata/MyappP/db01
/dev/mapper/vg06-db02
                       50G   35G   15G  71% /appl/oradata/MyappP/db02
/dev/mapper/vg07-db03
                       50G   35G   15G  71% /appl/oradata/MyappP/db03
/dev/mapper/vg08-db04
                       50G   35G   15G  71% /appl/oradata/MyappP/db04

solution:

check whether the removed files are waiting using lsof command. In our case one of the process is using those files


> /usr/sbin/lsof | grep deleted
oracle_64 64540    oracle  282u      REG              253,3 22548586496       20 /appl/oradata/MyappP/db02/MyappP_APPCHE_data_02.dbf (deleted)
oracle_64 64540    oracle  283u      REG              253,6 22548586496       21 /appl/oradata/MyappP/db01/MyappP_APPCHE_data_01.dbf (deleted)
oracle_64 64540    oracle  284u      REG              253,1 22548586496       20 /appl/oradata/MyappP/db04/MyappP_APPCHE_data_04.dbf (deleted)
oracle_64 64540    oracle  285u      REG              253,2 22548586496       20 /appl/oradata/MyappP/db03/MyappP_APPCHE_data_03.dbf (deleted)
oracle_64 64540    oracle  286u      REG              253,3   104865792       21 /appl/oradata/MyappP/db02/MyappP_APPCHE_index_02.dbf (deleted)
oracle_64 64540    oracle  287u      REG              253,6   104865792       22 /appl/oradata/MyappP/db01/MyappP_APPCHE_index_01.dbf (deleted)
oracle_64 64540    oracle  288u      REG              253,1   104865792       21 /appl/oradata/MyappP/db04/MyappP_APPCHE_index_04.dbf (deleted)
oracle_64 64540    oracle  289u      REG              253,2   104865792       21 /appl/oradata/MyappP/db03/MyappP_APPCHE_index_03.dbf (deleted)

Now find the session and get the connection closed

oracle@ovmp1[LSNR_ADMIN]:/appl/oradata/MyappP/db01
> . oraenv
ORACLE_SID = [LSNR_ADMIN] ? MyappP
The Oracle base remains unchanged with value /appl/oracle
oracle@ovmp1[MyappP]:/appl/oradata/MyappP/db01
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 21 13:33:17 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


SQL>  select addr from v$process where spid=64540 ;

ADDR
----------------
00000000DE25ACF0

SQL> select sid,username,program,machine from v$session where paddr='00000000DE25ACF0';

       SID USERNAME
---------- ------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
        37 SYS
sqlplus@ovmp1.telecom.tcnz.net (TNS V1-V3)
ovmp1.telecom.tcnz.net


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
oracle@ovmp1[MyappP]:/appl/oradata/MyappP/db01
 
This will release the space back

RMAN-06136: ORACLE error from auxiliary database: ORA-19502

Issue:  duplicate failing during open reset logs (duplicate failed with RMAN-06136:, )

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/15/2017 12:53:31
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-19502: write error on file "/appl/oradata/xdb01/TestT/TestT_redo04a.rdo", block number 616449 (block size=512)
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 28: No space left on device
Additional information: 4294967295
Additional information: 1048576

Recovery Manager complete.

check the database for unused log files


oracle@OVM2[TestT]:/appl/oracle/admin/TestT/rman/rman_restore
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 15 13:04:16 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM     CON_ID
---------------- ------------- -------- ------------ -------- ----------
         1          1          0  536870912        512          2 YES
UNUSED                       0                     0                   0

         4          1          0  536870912        512          2 YES
CLEARING_CURRENT             0                     0                   0

         3          1          0  536870912        512          2 YES
UNUSED                       0                     0                   0


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM     CON_ID
---------------- ------------- -------- ------------ -------- ----------
         2          1          0  536870912        512          2 YES
UNUSED                       0                     0                   0


drop that unused logfile

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2 ;

Database altered.

SQL> select member from v$logfile ;

MEMBER
--------------------------------------------------------------------------------
/appl/oradata/xdb02/TestT/TestT_redo04b.rdo
/appl/oradata/xdb01/TestT/TestT_redo04a.rdo
/appl/oradata/xdb01/TestT/TestT_redo03a.rdo
/appl/oradata/xdb02/TestT/TestT_redo03b.rdo

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option


remove the deleted log files manually

oracle@OVM2[TestT]:/appl/oracle/admin/TestT/rman/rman_restore
> cd /appl/oradata/xdb02/TestT/
oracle@OVM2[TestT]:/appl/oradata/xdb02/TestT
> ls -lrt
total 1572900
-rw-r--r--. 1 oracle oinstall       842 May 17  2016 drop_tn.sql
-rw-r--r--. 1 oracle oinstall      5094 May 17  2016 drop_tn.log
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:52 TestT_redo01b.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo02b.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo03b.rdo
oracle@OVM2[TestT]:/appl/oradata/xdb02/TestT
> rm TestT_redo01b.rdo TestT_redo02b.rdo
oracle@OVM2[TestT]:/appl/oradata/xdb02/TestT
> cd ..
oracle@OVM2[TestT]:/appl/oradata/xdb02
> cd ..
oracle@OVM2[TestT]:/appl/oradata
> cd xdb01
oracle@OVM2[TestT]:/appl/oradata/xdb01
> cd TestT
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> ls -lrt
total 1594764
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:52 TestT_redo01a.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo02a.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo03a.rdo
-rw-r-----. 1 oracle oinstall  22396928 Sep 15 13:05 TestT_control01.ctl
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> rm TestT_redo01a.rdo TestT_redo02a.rdo


try open resetlogs, you may face errors like this if some logs are  uncleared.


oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 15 13:05:30 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: TestT (DBID=1412574424, not open)

RMAN>  Alter clone database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/15/2017 13:05:36
RMAN-06136: ORACLE error from auxiliary database: ORA-00392: log 4 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 4 thread 1: '/appl/oradata/xdb02/TestT/TestT_redo04b.rdo'
ORA-00312: online log 4 thread 1: '/appl/oradata/xdb01/TestT/TestT_redo04a.rdo'

RMAN> exit


clear the redo log 


oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 15 13:05:40 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

SQL> alter database clear logfile group 4 ;

Database altered.

SQL> exit

now try open resetlogs


oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 15 13:06:15 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: TestT (DBID=1412574424, not open)

RMAN>  Alter clone database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 15 13:06:49 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

SQL> select open_mode from v$database ;

OPEN_MODE
--------------------
READ WRITE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option