Applications

Expand all | Collapse all

EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

  • 1.  EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    Posted 04-13-2021 01:37 PM
    I have upgraded our on-prem EBS 12.1.3 database from 12c to 19c and as part of that process the UTL_FILE_DIR setting is deprecated and replaced by dba_directories.  That seems to have introduced a problem with XML Publisher Report Bursting Program and our T4 PDF report (Canada) as they seem to be still trying to use /usr/tmp even though I have changed it to to be <19c Oracle Base>/temp/<PDB NAME> per the documentation.  Specifically I am talking about "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)".
    I have updated $XDO_TOP/resource/xdo.cfg and "system-temp-dir" value along with $XDO_TOP/resource/xdodelivery.cfg and the "ds-temp-dir" with the new setting but the request file still shows it using /usr/tmp here:
    Start bursting process..
    [041321_104619269][][STATEMENT] /usr/tmp

    So eventually I get this java.io error:
    [041321_104619553][oracle.apps.xdo.template.FOProcessor][EXCEPTION] java.io.FileNotFoundException: /app/oracle/temp/XXXX/xdoaV2r5bExEy041321_1046195520.fo (No such file or directory)

    I have looked at a few Doc ID's like Doc ID 1679230.1 for example and and I don't have a xdodebug.cfg file.  I see talk about if territory is assigned but not sure what that means.

    So has anybody run into this issue before and solved it?  I have a couple of SR's open with Oracle just looking for any first hand experience if possible.


    ------------------------------
    Mark Schlechte
    DBA
    City of Regina
    Regina SK
    ------------------------------


  • 2.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    CO19 Speaker
    Posted 04-14-2021 07:41 AM
    Edited by Kai Glittenberg 04-14-2021 07:45 AM

    Hi Mark,

    usually you need to have proper setup in $XDO_TOP/resource in the files xdo.cfg and xdodelivery.cfg

    Already crosschecked if this etup is correct?

    Bursting needs to be set to the name setup in xdodelivery.cfg


    Coming to your error:

    [041321_104619553][oracle.apps.xdo.template.FOProcessor][EXCEPTION] java.io.FileNotFoundException: /app/oracle/temp/XXXX/xdoaV2r5bExEy041321_1046195520.fo (No such file or directory)

    Is this a shared dir across DB and apps tier? (/app/oracle/temp)



    Thanks
    Kai

    PS: 
    Additionally you could check Temp Dir setup in: XML Publisher Admistrator > Admistration > Properties > General

    See screenshot:

    ------------------------------
    Kai Glittenberg
    Solution Architect Cloud Services
    Apps Associates
    Dortmund
    (491) 603-655544
    ------------------------------



  • 3.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    Posted 04-14-2021 10:59 AM
    Hey Kai, thanks for the reply here.  I have checked my setup and the entries so seem to match.
    ->grep temp xdo.cfg
    <property name="system-temp-dir">/app/oracle/temp/EBSCLN</property>
    ->grep temp xdodelivery.cfg
    <property name="ds-temp-dir">/app/oracle/temp/EBSCLN</property>

    I had another fellow set me up with XML Publisher and the temporary directory was still pointing to /usr/tmp so I changed it to my new setting and had him test.  The output of the .req file now at least shows the right value as in:
    Start bursting process..
    [041421_082914876][][STATEMENT] /app/oracle/temp/EBSCLN

    But then it seems to be generating a bit of a different error under -Exception:
    --Exception
    Please specify the tmpDir value </app/oracle/temp/EBSCLN> or setup the default configuration with system temp directory information correctly
    java.io.FileNotFoundException: Please specify the tmpDir value </app/oracle/temp/EBSCLN> or setup the default configuration with system temp directory information correctly.

    You asked if this is a shared directory across db and app tier and no it just exists on the db tier.  Does it need to be shared to the app tier (ie. NFS)?

    Mark


    ------------------------------
    Mark Schlechte
    DBA
    City of Regina
    Regina SK
    ------------------------------



  • 4.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    CO19 Speaker
    Posted 04-14-2021 11:04 AM

    Hey Mark!

    XML Publisher resides on the App Server and is accessing directory from java process on App Server.

    The directory need to ba available on the App Server.

    Thanks
    Kai



    ------------------------------
    Kai Glittenberg
    Solution Architect Cloud Services
    Apps Associates
    Dortmund
    (491) 603-655544
    ------------------------------



  • 5.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    Posted 04-14-2021 12:38 PM
    I'm just waiting for some confirmation from the client here but after creating that directory on the app tier a basic test did work so I guess I just didn't make the connection before and am hopeful you have solved my predicament Kai. 😃

    ------------------------------
    Mark Schlechte
    DBA
    City of Regina
    Regina SK
    ------------------------------



  • 6.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    SIG Leader
    Posted 04-15-2021 08:05 AM
    Own of the changes in dealing with replacing UTL_FILE_DIR with directories is replacing /usr/tmp with temp under ORACLE_BASE (I believe that is how it picks the location).  Using /usr/tmp dates back to when most people installed on a single server, but at the OS level it is not supposed to be a shared mount.  The setting of APPLPTMP on the apps tier is required to be a shared mount with the database tier.  Depending on the modules used, some companies have gotten lucky with this being a separate mount point.  APPLPTMP is supposed to be where the apps tier can pick up results generated by a PL/SQL executable (which runs in the database and outputs on the database tier).

    ------------------------------
    Michael Brown
    Database Administrator
    BlueStar
    Burlington NC
    (859) 371-4423 x3353
    ------------------------------



  • 7.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    Posted 04-15-2021 03:59 PM
    Thanks for the feedback Mike.  I'll just state for the record all I needed was to actually have the directory created on the app tier as Kai suggested.  I was used to APPLPTMP on the app tier referring to the UTL_FILE_DIR value which was on the DB server.  I guess in my case since both servers have /usr/tmp the issue I was having was kind of masked.

    ------------------------------
    Mark Schlechte
    DBA
    City of Regina
    Regina SK
    ------------------------------



  • 8.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    SIG Leader
    Posted 04-15-2021 04:57 PM
    That's correct you only need it on the app tier until you have output directly produced from a PL/SQL executable usually in a multi step process.  The output will be written on the database server and read on the apps tier which means that the location in $APPLPTMP needs to be a shared mount with the database server.  This tends to result in a "What the heck is going on?" moment when implementing a new module.  Where the log for request 1 says /app/oracle/temp/file1.txt is written, and the log for request 2 says /app/oracle/temp/file1.txt does not exist.

    I think I know of far more companies where there is not a shared mount than the ones who actually have a shared mount because the same directory name made everything work, but all of them are gambling on getting an error in the future.

    In a single node database, this is an easy issue to fix by just NFS exporting the directory from the database server and mounting it the apps tier(s) which will avoid any chance of having an issue in the future (since the database has to be up for the apps tier to have any value there is no change in availability by doing this).  In a RAC enviroment, you need an enterprise level solution to have the same mount on all database and apps tiers.  If you are single node, I suggest fixing this in your next maintenance window (that is exporting /app/oracle/tmp to the apps tier and replacing the local directory on the apps tier with the NFS mount).  If you are RAC, then it is a more involved discussion between database, system, and storage administrators to make sure your solution does not cause a drop in reliability/availability and you can live with them being different locations for now.

    ------------------------------
    Michael Brown
    Database Administrator
    BlueStar
    Burlington NC
    (859) 371-4423 x3353
    ------------------------------



  • 9.  RE: EBS 12.1.3 with 19c database - utl_file_dir deprecated and issue now with XML Publisher Report

    Posted 04-16-2021 09:56 AM
    You know we are not live in production yet so I think I will implement the NFS share as you suggest in our Test environment.  We are in a simple single node scenario.  Thanks again!

    ------------------------------
    Mark Schlechte
    DBA
    City of Regina
    Regina SK
    ------------------------------