Adding a path in utl_file_dir

Please add this path (/db/vision/temp) in utl_file_dir parameter.
You will get above details from ticket or business communication.

Bring down all middle tier services if you are using Oracle Application Ebs.

[applvision@apps ~]$ ps -fu applvision
UID        PID  PPID  C STIME TTY          TIME CMD
501       5747  5739  0 23:41 ?        00:00:00 sshd: applvision@pts/3
501       5748  5747  1 23:41 pts/3    00:00:00 -bash
501       5835  5748  0 23:41 pts/3    00:00:00 ps -fu applvision

Crosscheck whether the given path is exists in utl_file_dir.

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /usr/tmp, /usr/tmp, /db/vision
                                                 /db/tech_st/11.1.0/appsutil/ou
                                                 tbound/vision_apps, /usr/tmp
SQL>

Note:UTL_FILE_DIR parameter is static ,we have to bounce the database in order to add the path.

How to check whether a parameter is static or dynamic?

SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%utl_file_dir%';

ISSYS_MOD
---------
FALSE              ===>static 

SQL>

SQL>  select ISSYS_MODIFIABLE from v$parameter where name like '%log_archive_dest%';

ISSYS_MOD
---------
IMMEDIATE        ====>dynamic


Create pfile from spfile and open pfile add the path and start your database with your modified
pfile.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /db/vision/db/tech_st/11.1.0/d
                                                 bs/spfilevision.ora
SQL> create pfile='/db/vision/initaddpath.ora' from spfile;

File created.

SQL>

Bring down the database
========================
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


Add path in pfile in utl_file_dir parameter
=============================
[dbvision@apps temp]$ grep -i /db/vision/temp /db/vision/initaddpath.ora
*.utl_file_dir='/usr/tmp','/usr/tmp','/db/vision/db/tech_st/11.1.0/appsutil/outbound/vision_apps','/usr/tmp','/db/vision/temp'
[dbvision@apps temp]$


Start the database with pfile
======================
[dbvision@apps temp]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Sep 22 00:02:47 2013

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

Connected to an idle instance.

SQL> startup nomount pfile='/db/vision/initaddpath.ora'
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1318172 bytes
Variable Size             440402660 bytes
Database Buffers          616562688 bytes
Redo Buffers               13049856 bytes
SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /usr/tmp, /usr/tmp, /db/vision
                                                 /db/tech_st/11.1.0/appsutil/ou
                                                 tbound/vision_apps, /usr/tmp,
                                                 /db/vision/temp
SQL> create spfile from pfile='/db/vision/initaddpath.ora';

File created.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1318172 bytes
Variable Size             440402660 bytes
Database Buffers          616562688 bytes
Redo Buffers               13049856 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /db/vision/db/tech_st/11.1.0/d
                                                 bs/spfilevision.ora
SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /usr/tmp, /usr/tmp, /db/vision
                                                 /db/tech_st/11.1.0/appsutil/ou
                                                 tbound/vision_apps, /usr/tmp,
                                                 /db/vision/temp
SQL>