How to dorp/recreate your Undo Tablespaces

> It is better to put the database in the mount mode while doing these activities.
> If the database is up and running you may face "ORA-30013: undo tablespace '%s' is currently in use." error while dropping the old Undo tablespace, this error indicates that you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transactions to be committed or rolled back. Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain "allocated" from the OS perspective until the database restart.

1- Collect the required data about your Undo Tablespaces:

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='UNDO';

TABLESPACE_NAME
------------------------------
APPS_UNDOTS1

 SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS1
SQL>

SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

FILE_NAME                 FILE_ID TABLESPACE_NAME            BYTES      BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES    MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/PROD/db/apps_st/data/undo01.dbf    379 APPS_UNDOTS1           4194304000      512000 AVAILABLE        379 YES 3.4360E+10      4194302     12800 4194238464      511992 ONLINE
/oracle/PROD/db/apps_st/data/undo02.dbf     19 APPS_UNDOTS1           4404019200      537600 AVAILABLE         19 YES 3.4360E+10      4194302     12800 4402970624      537472 ONLINE

SQL>

- Total size of the Undo Tablespace:

SQL> SELECT sum(bytes)/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

SUM(BYTES)/1024/1024/1024
-------------------------
        8.0078125

SQL> !ls -ltrh /oracle/PROD/db/apps_st/data/undo0*
-rw-r----- 1 orstage dba 4.2G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo02.dbf
-rw-r----- 1 orstage dba 4.0G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo01.dbf

SQL>

2- Create a new undo tablespace:

SQL> create undo tablespace APPS_UNDOTS2 datafile '/oracle/PROD/db/apps_st/data/undoNew.dbf' size 1000M;

3- Set the new tablespace as the undo tablespace to be used:

SQL> alter system set undo_tablespace=APPS_UNDOTS2 scope=spfile;

SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS2
SQL>   

4- Drop the old undo tablespace:

    SQL> drop tablespace APPS_UNDOTS1 including contents and datafiles;

5- Recreate the old undo tablespace with a smaller size:

    SQL> create undo tablespace APPS_UNDOTS1 datafile '/oracle/PROD/db/apps_st/data/undo01.dbf' size 10240M '/oracle/PROD/db/apps_st/data/undo02.dbf' size 10240M;

6- Set the old tablespace as the undo tablespace to be used:

    SQL> alter system set undo_tablespace=APPS_UNDOTS1 scope=spfile;

 7- Drop the temp undo tablespace:

    SQL> drop tablespace APPS_UNDOTS2 including contents and datafiles;

Regards,
Mohamed

Troubleshooting Concurrent Request ORA-20100 error

When running "GMFRLAYR module: GMF Recreate Batch / Period Layers" Concurrent request we got the below error:

ORACLE error 20100 in FDPSTP Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o1152922.tmp in the directory, /usr/tmp. You will find more information in the request log. ORA-06512: at "APPS.FND_FILE", line 417 

- All configurations seems to be OK:

As the DB owner:

SQL> sho parameter utl_file_dir

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string     /usr/tmp, /usr/tmp, /Test/FINT
                         EST/db/tech_st/11.1.0/appsutil
                         /outbound/FINTEST_erpmigrate02
                         , /usr/tmp
SQL> 

As the MT owner:

[apfintest@erpmigrate02 ~]$ echo $APPLPTMP
/usr/tmp
[apfintest@erpmigrate02 ~]$ ls -l /usr/|grep tmp
lrwxrwxrwx   1 root root     10 Nov  4 13:01 tmp -> ../var/tmp
[apfintest@erpmigrate02 ~]$ ls -l /var|grep tmp
drwxrwxrwx  3 root root  4096 Nov 13 11:31 tmp
[apfintest@erpmigrate02 ~]$ 

- We have enough space for new logs:

[apfintest@erpmigrate02 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             254G  5.7G  235G   3% /
/dev/sdb2             943G  348G  547G  39% /Oracle
/dev/sdb1             946G  348G  551G  39% /Test
/dev/sda1             289M   32M  243M  12% /boot
tmpfs                 7.9G   17M  7.9G   1% /dev/shm
[apfintest@erpmigrate02 ~]$

- Trying to use the package used by the application to write the file:

SQL> sho user
USER is "APPS"
SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'testing /usr/tmp folder');

PL/SQL procedure successfully completed.

SQL> !ls -ltr|tail
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:40 o1152928.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:40 l1152928.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152959.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152959.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152946.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152946.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152916.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152916.tmp
-rw-r--r-- 1 orfintest oinstall      0 Nov 13 11:41 o1154916.tmp
-rw-r--r-- 1 orfintest oinstall     24 Nov 13 11:41 l1154916.tmp

SQL> !cat l1154916.tmp
testing /usr/tmp folder

SQL>

- At last I found that the /usr/tmp folder is shared between two instances! and the same file name got generated by both instances!

- At this point you have two options:

a)

1. Stop all MT services.
2. Change $APPLPTMP value to another directory "ensure it is the first value listed by utl_file_dir DB parameter"

3. Run autoconfig on the MT nodes

4. Restart all services
5. Re-test the failed process

b)

As a quick solution "should not be applied unless you really need an urgent solution"

[root@erpmigrate02 tmp]# chmod 777 /usr/tmp/*
[root@erpmigrate02 tmp]# 

re-run your CR again...

Regards,

Mohamed