How to create UTL_FILE Directory in 19C Database

By -


UTL_FILE

UTL_FILE is a PL/SQL package that allows programs to read and write operating system text files.

It is commonly used to write custom log files.

Examples

  • General debug and temporary files

  • Log and output files generated by Concurrent Processing

In Oracle Database 11g R2 and 12c Release 1, the UTL_FILE_DIR database initialization parameter must be configured to define the directory or directories where these files are located.

In Oracle Database 18c and later, the UTL_FILE_DIR database initialization parameter is desupported. Instead, Database Directory Objects are used to specify the locations used for PL/SQL file I/O.

Advantage

There is no need for a database bounce, and directory changes are reflected immediately.

Steps to Create the UTL_FILE Directory

Step 1: Query to Find the Existing UTL Directory

SELECT name, value
FROM apps.v$parameter
WHERE name LIKE 'utl%';

Step 2: Create a New Directory at OS Level

Example:

mkdir /utlfile/common/sanjeev

Step 3: Export PERL and PERL5LIB

[oracle@vtebs db]$ export PERL5LIB=/oracle/product/19.0.0/db/perl/lib/site_perl/5.28.1:/oracle/product/19.0.0/db/perl/lib/site_perl/5.28.1:/oracle/product/19.0.0/db/appsutil/perl

Step 4: Add the New Directory Using txkCfgUtlfileDir.pl

[oracle@vtebs appsutil]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl \
-contextfile=$CONTEXT_FILE \
-oraclehome=$ORACLE_HOME \
-outdir=$ORACLE_HOME/appsutil/log \
-mode=addUtlFileDir

Sample Execution:

Enter the full path of Context File:
/oracle/product/19.0.0/db/appsutil/TEST_vtebs01.xml

Enter the APPS Password:

Enter the SYSTEM Password:

Enter the new OS path to be added to UTL_FILE_DIR:
/utlfile/common/ercebs9d/Sanjeev

Script Name    : txkCfgUtlfileDir.pl
Script Version : 120.0.12020000.11

Started        : Fri Jan 14 12:26:08 EST 2022

Log File       : /oracle/product/19.0.0/db/appsutil/log/TXK_UTIL_DIR_Fri_Jan_14_12_25_45_2022/txkCfgUtlfileDir.log

Context file: /oracle/product/19.0.0/db/appsutil/TEST_vtebs01.xml exists.

Completed      : Fri Jan 14 12:26:20 EST 2022

Successfully Completed the script

ERRORCODE = 0 ERRORCODE_END

[oracle@vtebs appsutil]$

Step 5: Synchronize the Modified UTL_FILE_DIR Value with the Database Context File

After adding the new directory path, synchronize the modified UTL_FILE_DIR value with the database context file.

$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl \
-contextfile=$CONTEXT_FILE \
-oraclehome=$ORACLE_HOME \
-outdir=$ORACLE_HOME/appsutil/log \
-mode=syncUtlFileDir

This step ensures that the database configuration and context file remain synchronized and helps avoid inconsistencies during future AutoConfig executions and maintenance activities.

Important Parameters






ParameterDescription
txkCfgUtlfileDir.pl                                                                             Used to migrate existing UTL_FILE_DIR settings to the new parameter during database upgrades and to update the supplemental UTL_FILE_DIR parameter after the upgrade. It can also be used to create database directory objects.

getUtlFileDirRetrieves directory path values from the source UTL_FILE_DIR database initialization parameter.

setUtlFileDirStores the directory paths for the supplemental UTL_FILE_DIR parameter in Oracle E-Business Suite tables underlying the APPS.V$PARAMETER and APPS.V$PARAMETER2 views.

skipdirvalidation=YesIf the script cannot validate the Oracle 19c Home directory, use the -skipdirvalidation=Yes parameter while executing txkCfgUtlfileDir.pl.

addUtlFileDirAdds a new directory path to the supplemental UTL_FILE_DIR parameter.

syncUtlFileDirSynchronizes the modified UTL_FILE_DIR values from the database to the Oracle E-Business Suite context file.




Reference

Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)

Comments

Post a Comment

Popular posts from this blog

How to troubleshoot long running concurrent request in R12.2

By -
We frequently encounter issues with concurrent programs running long and concurrent requests not picking up jobs. These problems are often expected from the development and functional team we need to address this issue To solve this, we should take the following steps: Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance?   Troubleshooting Steps for Concurrent Requests Check for Locks and Blocking sessions Identify if the request is running but blocked by database locks/Blocking sessions. Use DBA tools or queries to identify and release locks if necessary. Review Parameters and Data ...
Read more

How to find EBS URL in R12.2

By -
There are different way to find the EBS URL Method 1 Login the R12.2 application server Source the Env File [oracle@san ~]$ grep login $CONTEXT_FILE          <login_page oa_var="s_login_page">http://dev1.apps.com:8001/OA_HTML/AppsLogin</login_page> [oracle@san~]$ Method 2: Connect sqlplus apps user SQL> SELECT home_url  FROM icx_parameters; HOME_URL -------------------------------------------------------------------------------- http://<Hostname>:8007/OA_HTML/AppsLogin How to find Weblogic URL and PORT number: Login the R12.2 application server Source the Env File [oracle@sanprod ~]$ grep s_wls_adminport $CONTEXT_FILE          <wls_adminport oa_var="s_wls_adminport" oa_type="PORT" base="7001" step="1" range="-1" label="WLS Admin Server Port"> 7002 </wls_adminport> [oracle@sanprod ~]$ Weblogic Port Number:7002  Weblogic URL:http://<...
Read more

JSP Compilation in R12.2

By -
Sometime after upgrade, cloning and patching front end login page is not opening showing blank page or showing “ java.lang.StringIndexOutOfBoundsException ” error. This issue was occurring due to cache is not cleared after upgrade/Cloning JSP Compilation steps Step 1: Shutdown the application services Step 2: Take backup for the _Page(class file) folders $ cd $OA_HTML/WEB-INF/classes $ ls -lrt $ mv _pages _pages_bkup<date> a $ cd $OA_HTML/WEB-INF/classes $ ls -lrt $ mv _pages _pages_bkup<date> $ mkdir _pages $ ls -lrt Step 3: Run the below command $ perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 99 Step 4: Start the application and clear browser cache. After completed the JSP compilation home page not coming please clear the image and style sheet cache also Cabo Images and style sheets can be corrupted or out of sync in the cabo caches, you may need to clear the related directories after backup: Step1: Shutdown the services...
Read more