Space release problem in OS level while Dropping Temporary Tablespace

Today I have faced one new problem which I would like to share to you all.

In one of my production database, the temporary tablespace is defined as autoextend on, max size is unlimited. Suddenly I got a mail with below error.

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Then I have checked total temporary tablespace usage, i.e  32GB, because I used Smallfile Tablespace (default) which is limited to 32GB Max size. Now I created new temporary tablespace (TEMP01), make it as a default and crosschecked and confirmed all the users the default TEMPORARY TABLESPACE changed to TEMP01. Altered old temporary tablespace(TEMP) availability to OFFLINE.

In non business hours I dropped old TEMP tablespace (TEMP) logically. When I checked in OS level the space not got released.

I thought "Some process has been locked that tempfile at OS level", then I followed below steps to release space from OS level.

1) First I find what process ID has locked that tempfile using '/usr/sbin/lsof' command.

   $ /usr/sbin/lsof
  
      Then I have seen so many OS locked files, there I found below tempfile related processes.
 
  Output:  oracle    17709 oracle   53u      REG              104,9 4047511552    920075 /Oracle/oradata/PRODDB/TEMP01.DBF (deleted)
                oracle    17709 oracle   54u      REG              104,9 4047511552    920075 /Oracle/oradata/PRODDB/TEMP01.DBF (deleted)

 2) Next I checked which background process has been locked that processID, There I found the background process is "JXXX" (Some Job), then I simply killed that session.

      $ ps -ef|grep 17709

      Output:  oracle   17709     1  3 Jan13 ?        3-15:42:12 ora_j000_PRODDB

      $ kill -9 17709

Now space got released from OS level. 

Hope this will help you... :)

Best Regards,

Export Logical Database Backup Dumps to ASM Disk

I have created both directory and folder within the ASM diskgroup.

bash-3.2$ echo $ORACLE_HOME
/oracle/grid_home
bash-3.2$
bash-3.2$ echo $ORACLE_SID
+ASM1

bash-3.2$ asmcmd -p
ASMCMD [+] >

ASMCMD [+] > ls
DATA/
FRA/

ASMCMD [+] > cd FRA
ASMCMD [+FRA] >

ASMCMD [+FRA] > mkdir dpdump
ASMCMD [+FRA] >

ASMCMD [+FRA] > cd dp*
ASMCMD [+FRA/dpdump] >

ASMCMD [+FRA/dpdump] > pwd
+FRA/dpdump
ASMCMD [+FRA/dpdump] >

bash-3.2$ sqlplus / as sysdba

SQL> create directory test_dir as '+FRA/dpdump';

Directory created.

SQL>
SQL> select * from dba_directories;

OWNER          DIRECTORY_NAME  DIRECTORY_PATH
--------------   --------------                ----------------------------------
SYS                TEST_DIR                     +FRA/dpdump
SYS                DATA_PUMP_DIR        /U04/dpdump

SQL>

bash-3.2$
bash-3.2$ expdp directory=TEST_DIR dumpfile=test_full.dmp logfile=test_full.log full=y exclude=statistics content=metadata_only

Export: Release 11.2.0.2.0 - Production on Thu Sep 26 15:43:15 2013

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

bash-3.2$

Here we have seen an error "unable to open log file".

So now the gimmick is, to use existing directory, such as DATA_PUMP_DIR which is placed on a local filesystem and point the logfile to that directory. Using the syntax 'logfile=directory:logfile'. An alternative option is to use the expdp parameter to nologfile=yes

bash-3.2$
bash-3.2$ expdp directory=TEST_DIR dumpfile=test_full.dmp logfile=DATA_PUMP_DIR:test_full.log full=y exclude=statistics content=metadata_only

Export: Release 11.2.0.2.0 - Production on Thu Sep 26 15:48:48 2013

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_05":  /******** AS SYSDBA directory=TEST_DIR dumpfile=test_full.dmp logfile=DATA_PUMP_DIR:test_full.log full=y exclude=statistics content=metadata_only
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
.
.
.
Dump file set for SYS.SYS_EXPORT_FULL_05 is:
  +FRA/dpdump/test_full.dmp
Job "SYS"."SYS_EXPORT_FULL_05" successfully completed at 16:00:52
bash-3.2$

Hope this help you.

Best Regards,