Purge R12.2 log files in Linux OS

Folder structure in R12.2 file system is more complicated than in R12.1, because RUN and PATCH may switch their location. After an adop cutover, the path to log files will be changed to a different one. Below are folders that have a growing number of log files. "ls" can be used to list the log files but "cd" do not work to reach the folder, while the path uses "*" in it.

$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*.log*

$EBS_DOMAIN_HOME/servers/oacore*/logs/*.log*

$EBS_DOMAIN_HOME/servers/form*/logs/*.log*

$EBS_DOMAIN_HOME/servers/AdminServer/logs/*.log*

Fortunately, "find" works with path having "*" in it. Below lines work in cron to purge/delete old log files (on web/forms node).

$ crontab -l

05 05 * * * . /u07/app/.profile; /usr/bin/find $IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*log* -type f -mtime +30 -exec rm -f {} \;

05 15 * * * . /u07/app/.profile; /usr/bin/find $EBS_DOMAIN_HOME/servers/oacore*/logs/*log* -type f -mtime +30 -exec rm -f {} \;

Other logs or their locations:

$ADOP_LOG_HOME/*                   <== each ADOP session ID has a sub-folder

$INST_TOP/admin/log/MMDDHHMM/adconfig.log

$LOG_HOME/appl/admin/log/*      <== adstrtal.sh & adstpall.sh 

$LOG_HOME/appl/rgf/javacache.log 

$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/* 

$EBS_DOMAIN_HOME/sysman/log/* 

$EBS_DOMAIN_HOME/servers/oac*/adr/diag/ofm/EBS_domain*/oac*/incident/* 

$EBS_DOMAIN_HOME/servers/forms_s*/adr/diag/ofm/EBS_domain*/forms_s*/incident/*

$INST_TOP/logs/ora/10.1.2/reports/cache/*.*

Locations or files for configuration and setups:

$IAS_ORACLE_HOME/instances/EBS_web*/config/OHS/EBS_web* 

$FMW_HOME/webtier/instances/EBS_web*/config/OPMN/opmn/* 

$CONTEXT_FILE

$INST_TOP/appl/admin/*.properties

$INST_TOP/ora/10.1.2/forms/server/default.env    (AutoConfig overwrites it)

$FND_TOP/fndenv.env

$EBS_DOMAIN_HOME/config/config.xml

$EBS_APPS_DEPLOYMENT_DIR/*/APP-INF/node_info.txt

.profile file (for account runs the crontab jobs on RHEL)

$ more .profile

PATH=/bin:/usr/bin:/usr/local/bin

export PATH

MANPATH=/usr/share/man:/usr/local/share/man:/usr/X11R6/man

export MANPATH

EDITOR=/bin/vi

export EDITOR

. /u07/app/EBSDEV/EBSapps.env RUN

isMaster="no"

if [ ! -z $APPS_VERSION ] && [ ${APPS_VERSION:0:4} == "12.2" ]

then

s_status=`cat $CONTEXT_FILE | grep -i s_adminserverstatus`

isMaster="${s_status:60:7}"

fi

if [ $isMaster == "enabled" ]

then

if [ `echo -n ${HOSTNAME%%.*} | tail -c -1` != "p" ]

then

PS1=$'

\e[0;31m$USER@${HOSTNAME%%.*}[$TWO_TASK]\e[m$PWD

-->$ '

else

PS1=$'

\e[0;31m$USER@${HOSTNAME%%.*}[$TWO_TASK]\e[m\E[32m$PWD \E[0m

-->$ '

fi

else

if [ `echo -n ${HOSTNAME%%.*} | tail -c -1` != "p" ]

then

PS1='

$USER@${HOSTNAME%%.*}[$TWO_TASK]$PWD

-->$ '

else

PS1=$'

$USER@${HOSTNAME%%.*}[$TWO_TASK]\E[32m$PWD \E[0m

-->$ '

fi

fi

alias rm='rm -i'

stty erase ^?

EBS steps when database is upgraded to 19c using Export/Import

In some special situations, database can not be upgraded to 19c by Database Upgrade procedure. For example, run hcheck as sysdba (Doc ID 136697.1). If get below error, you have to fix the problem before moving forward with database upgrade.

Problem Summary

---------------------------------------------------

HCKW-0027: System Tablespace does not have relative file number of 1  (Doc ID 2364065.1)

Problem Description

---------------------------------------------------

Running hcheck.sql

Returned warning:

 - SystemNotRfile1 ... 1201000200 > 902000000 03/04 16:03:08 WARN

Confirm file names caused the mess-up:

SQL> select RELATIVE_FNO fno, file_id, substr(file_name,1,50) fileName 

from dba_data_files where file_name like '%sys%';

FNO  File_ID  FILENAME

  16              1   /oradata/u10/EBSDEV/system01.dbf

    8              8   /oradata/u10/EBSDEV/system06.dbf

  87            87   /oradata/u10/EBSDEV/system07.dbf

etc … … 

SQL> select RELATIVE_FNO, file_id, substr(file_name,1,50) fileName 

from dba_data_files where file_id =16;

FNO  File_ID  FILENAME

  16           16   /oradata/u11/EBSDEV/APPS_TS_TX_IDX36.dbf

Steps on EBS apps side when database export & import are used to upgrade the database:

1. Make sure all pre-requisite patches were applied to apps side 

A list of EBS patches are needed before 19c database upgrade. See Doc ID 2552181.1 (Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle  Database 19c).

2. Run through patch cycle to complete adop cleanup

On the current run file system:

Run fs_clone first (if no pending ADOP session exists). It will screen out problems and help to fix potential issues (for example, fs_clone was not executed right after ssl cert renewal):

$ adop phase=fs_clone

If troubleshooting was performed, make sure nothing is running on PATCH (say, fs2) file system and log onto EBS site to do a sanity check.

$ ps -ef | grep fs2

Starting a new OS session on primary node:

$ adop phase=prepare

$ adop phase=actualize_all

$ adop phase=finalize finalize_mode=full

$ adop -status

... ...

===============================================================

Node Name  Node Type      Phase           Status          Started              Finished             Elapsed

--------------- ---------- --------------- --------------- -------------------- -------------------- ------------

node1    master    PREPARE       COMPLETED       2022/X/14 20:32:32  2022/X/14 20:38:05  0:05:33

                            APPLY             ACTIVE

                            FINALIZE       COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                            CUTOVER      NOT STARTED

                            CLEANUP       NOT STARTED

node2    slave      PREPARE       COMPLETED       2022/X/14 20:32:31  2022/X/14 20:38:07  0:05:36

                            APPLY            NOT STARTED

                            FINALIZE      COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                            CUTOVER     NOT STARTED

                            CLEANUP     NOT STARTED

node3    slave      PREPARE      COMPLETED       2022/X/14 20:32:31  2022/X/14 20:39:39  0:07:08

                           APPLY             NOT STARTED

                           FINALIZE      COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                           CUTOVER      NOT STARTED

                           CLEANUP      NOT STARTED

May see concurrent job "Online Patching In Progress"

$ adop phase=cutover

On the new run file system:

$ adop phase=cleanup cleanup_mode=full 

Also run SQL scripts by apps:

@$AD_TOP/sql/ADZDSHOWOBJS.sql - to check which objects still need to be tidied up.

@$AD_TOP/sql/ADZDSHOWLOG.sql  - to make sure that it ran through properly

@$AD_TOP/sql/ADZDSHOWED.sql     - to get the list of Editions

Shutdown apps services

3. DBA upgrades database to 19c by using Export and Import

4. Modify apps files after DBA provides new TNS entry. 

Below is an example of the new TNS entry. 

EBSDEV=

        (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs_db1d.domain.com)(PORT=1560))

            (CONNECT_DATA=

                (SERVICE_NAME=ebs_EBSDEV)

                (INSTANCE_NAME=CEBSDEV)

            )

        )

To modify the TNS entries, edit the $TNS_ADMIN/tnsnames.ora files to specify the CDB instance name. 

$ cd $TNS_ADMIN

$ vi tnsnames.ora

replace every INSTANCE_NAME to CDBEDEV

replace every SERVICE_NAME to ebs_EBSDEV (if it is different from 12c)

$ sqlplus apps/appsPWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> 

$ tnsping ebsdev

... ...

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsDB1d.domain.com)(PORT=1560)) (CONNECT_DATA= (SERVICE_NAME=ebs_EBSDEV) (INSTANCE_NAME=CDBEDEV)))

OK (0 msec)

5. Edit $CONTEXT_FILE on both RUN and PATCH file systems

Update the following values in the context file.

Variable Name                         Value

s_dbhost                                          New database hostname (if changed)

s_dbport                                         New database port (if changed)

s_dbGlnam                                      CDBEDEV    (Seems autoconfig does not keep it)

s_apps_jdbc_connect_descriptor NULL (<jdbc_url oa_var="s_apps_jdbc_connect_descriptor"/>)

s_dbport                                         New database port

s_applptmp                                 Defined in UTL_FILE_DIR (e.g. /path/to/EBSDEV/utl_dir)

6. Run AutoConfig on RUN and PATCH file systems in all nodes

SQL> select * from FND_OAM_CONTEXT_FILES;     

SQL> select * from fnd_nodes;         <== only one row for db node

$ ./adautocfg.sh

Notes: 

a). AutoConfig will fail on jtfictx.sh because jtfictx.sql requires AD_CTX_DDL, which is created in a later step. AutoConfig will be run again after AD_CTX_DDL has been created. 

b). After AutoConfig, s_apps_jdbc_connect_descriptor will get new value.

c). AutoConfig will overwrite variable s_dbGlnam ?

Table FND_OAM_CONTEXT_FILES might be cleaned during database upgrade. If CONTEXT FILE records for PATCH system are missing, run AutoConfig on PATCH file system as well to avoid ADOP session failure in the future.

To run AutoConfig on PATCH file system, disable a trigger first (and afterwards enable it).

SQL> conn system/systemPWD

Connected.

SQL> alter trigger ebs_logon disable;

Trigger altered.

SQL> select * from FND_OAM_CONTEXT_FILES;     <== entries for both RUN and PATCH

7. Create the CTX objects

Certain Oracle Text objects are not preserved by the import process. The consolidated export/import utility patch that were applied to the administration server node in Section 4 of Doc ID 2552181.1 contains a perl script, dpost_imp.pl, that will generate an AutoPatch driver file. Use this driver file to call the scripts that create these objects.

$ cd $AU_TOP/patch/115/bin

$ mkdir 6924477

$ perl dpost_imp.pl u6924477.drv 12      <== it creates file u6924477.drv

$ mv u6924477.drv 6924477                   <== repeat above steps on other node(s)

$ adop phase=apply hotpatch=yes patchtop=$AU_TOP/patch/115/bin patches=6924477 options=forceapply

8. DBAs run some reports, compile invalid objects and make necessary grants.

9. Run AutoConfig in 2nd time on all apps nodes and start all EBS services.

All shall work by now.

OPTIONAL actions: 

1. If WF agent does not start, the issue is with table AQ$_WF_CONTROL_P. Build WF queue on fly.

$ sqlplus applsys/PWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> set pages 10000

SQL> select instance_name from v$instance;

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62483

SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'WF_CONTROL',force => TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62446

SQL> select * from all_objects where object_name like'AQ$WF_CONTROL%';

no rows selected

SQL> select * from all_objects where object_name like'AQ$_WF_CONTROL%';

no rows selected

$ sqlplus apps/appsPWD

SQL> @/path/to/EBSPGRND/utl_dir/wfctqrec_new.sql APPLSYS appsPWD

Connected.                                               (see below Notes)

PL/SQL procedure successfully completed.

Commit complete.

$ sqlplus applsys/PWD

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62474

SQL> column object_name format a25

SQL> column object_type format a10

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$_WF_CONTROL%';

OBJECT_NAME                      OBJECT_TYP

------------------------------------- ----------

AQ$_WF_CONTROL_E          QUEUE

AQ$_WF_CONTROL_F           VIEW

AQ$_WF_CONTROL_G          TABLE

AQ$_WF_CONTROL_H          TABLE

AQ$_WF_CONTROL_I           TABLE

AQ$_WF_CONTROL_L          TABLE

AQ$_WF_CONTROL_N          SEQUENCE

AQ$_WF_CONTROL_S          TABLE

AQ$_WF_CONTROL_T          TABLE

AQ$_WF_CONTROL_V          EVALUATION

                                                       CONTEXT

10 rows selected.

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$WF_CONTROL%';

OBJECT_NAME               OBJECT_TYP

------------------------- ----------

AQ$WF_CONTROL               VIEW

AQ$WF_CONTROL_R          VIEW

AQ$WF_CONTROL_S           VIEW

Some database objects are created on "fly" after EBS services are started:

$ cd $ADMIN_SCRIPTS_HOME

$ ./adstrtal.sh apps/appsPWD

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$_WF_CONTROL%';

OBJECT_NAME               OBJECT_TYP

------------------------- ----------

AQ$_WF_CONTROL_D          TABLE

AQ$_WF_CONTROL_E          QUEUE

AQ$_WF_CONTROL_F           VIEW

AQ$_WF_CONTROL_G          TABLE

AQ$_WF_CONTROL_H          TABLE

AQ$_WF_CONTROL_I           TABLE

AQ$_WF_CONTROL_L          TABLE

AQ$_WF_CONTROL_N          SEQUENCE

AQ$_WF_CONTROL_P          TABLE

AQ$_WF_CONTROL_S          TABLE

AQ$_WF_CONTROL_T          TABLE

AQ$_WF_CONTROL_V          EVALUATION

                                                       CONTEXT

12 rows selected.

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$WF_CONTROL%';

OBJECT_NAME                     OBJECT_TYP

------------------------------------ ----------

AQ$WF_CONTROL               VIEW

AQ$WF_CONTROL_R          VIEW

AQ$WF_CONTROL_S           VIEW

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62487

Three Concurrent Managers shall be started and in running:

   . Workflow Mailer Service

   . Workflow Agent Listener Service

   . Workflow Document Web Services Service

But "Workflow Notification Mailer" may still show Down status due to a wrong SMTP server.

Notes: If running wfctqrec.sql gets error, delete some rows and save it as wfctqrec_new.sql for execution.

$ ls -altr wfctqrec*.sql

-rwxr-xr-x 1 user group 3734 Apr 12  2021 wfctqrec.sql

-rwxr-xr-x 1 user group 2865 Jul 14 17:16 wfctqrec_new.sql

$ diff wfctqrec.sql wfctqrec_new.sql

<   BEGIN

<     dbms_aqadm.stop_queue( queue_name => 'WF_CONTROL', wait => FALSE);

<   EXCEPTION

<     WHEN no_queue THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

<   BEGIN

<     dbms_aqadm.drop_queue(queue_name=> 'WF_CONTROL');

<   EXCEPTION

<     WHEN no_queue THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

<   BEGIN

<     dbms_aqadm.drop_queue_table( queue_table => 'WF_CONTROL', force => TRUE);

<   EXCEPTION

<     WHEN queue_table_not_exists THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

2. After 19c upgrade, three AZ tables for EBS iSetup becomes missing in 19c database.  Follow 

Oracle Doc ID 832459.1 (How To Cleanup Invalid Oracle iSetup (AZ) Tables And Recreate) to re-create them

3. In R12.2, when below line was used to hold concurrent jobs for a period of time for database upgrade completion and then from GUI forms to cancel the HOLD, some concurrent jobs got "No Manager" status.

SQL> update  apps.fnd_concurrent_requests  set hold_flag = 'Y' 

where status_code in ('Q','I') and hold_flag = 'N';

The problem may be column EDITION_NAME of table fnd_concurrent_requests was populated with invalid data. The fix is to find the current edition and run an update to the column.  

SQL> select edition_name from all_editions;

EDITION_NAME

-----------------------

ORA$BASE

SQL> update fnd_concurrent_requests

set edition_name='ORA$BASE'

where request_id in (228633x, 228633x, 228632x, 228632x, 228624x, 228624x)

;

How to capture a HEAP dump for oacore process in EBS Weblogic

When users could not log onto EBS site (with error code 502 for URL /OA_JAVA/oracle/apps/fnd/jar/fndewt.jar), we saw oacore processes used very high CPU and WLS Admin site show oacore1 status is unknown (blank). We had to shut oacore1 down and then start it up in WLS Admin Console.

After checking all EBS logs, Oracle Support did not find anything that might cause the problem and so asked us to capture a HEAP dump when the issue occurs. 

Use the following steps to customize the managed server configuration (of each OACORE) via the WebLogic Server Administration Console.

1. Log on to the WebLogic Server Administration Console.

2. Click on the 'Servers' link. This link takes you to a page containing a summary of the WebLogic Administration Server and all managed servers.

3. Click on the managed server whose configuration needs to be updated. A page containing various tabs for the settings of the managed server appears (e.g. oacore_server1).

4. Navigate to Configuration Tab > Server Start Tab > You can see the Arguments Section.

5. Click on Lock and Edit button in the 'Change Center' panel.

6. Update the Heap setting to append

-XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/u02/app/temp/HEAP_trace

(add above line to the end of the long string with a single space in between. No service bounce is needed. /u02/app/temp/HEAP_trace can be changed to a different location.)

7. Click the 'Save' button to save the configuration changes.

8. Once the customizations are complete and saved, click the 'Activate Changes' button in the 'Change Center' panel to activate the changes.

9. Next time Out of Memory error occurs (or service bounce), a *.hprof dump file will be generated in /u02/app/temp/HEAP_trace.  Compressed it by gzip and upload it to Oracle SR.

$ grep hprof $EBS_DOMAIN_HOME/servers/oacore_server1/logs/oacore_server1.out00033

Dumping heap to /u02/app/temp/HEAP_trace/java_pid21982.hprof

More details in the log file:

java.lang.OutOfMemoryError: Java heap space

Dumping heap to /u02/app/temp/HEAP_trace/java_pid21982.hprof ...

Heap dump file created [5475772387 bytes in 33.025 secs]

java.lang.OutOfMemoryError: Java heap space

        at org.apache.xmlbeans.impl.store.Locale.weakCur(Locale.java:2711)

        ... ...

R12.2 Autoconfig Fails On Patch file system with error ORA-12514

When I cloned a R12.2 instance, "perl adcfgclone.pl appsTier dualfs" gave an error at the final stage:

... ...

AutoConfig has completed with errors for .patch

Do you want to startup the Application Services for EBSDEV? (y/n) [n] : n

Before fixing the error, I did not have problem in setting up the instance and starting all apps services in RUN file system. The website was available to the user while I did troubleshooting in the PATCH file system. 

The log file for cloning RUN file system does not have any error. But log file $INST_TOP/admin/log/clone/patch/RCloneApplyAppstier_10281250.log from cloning PATCH file system has a lot of errors beginning with: 

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : ($FND_TOP/patch/115/bin/txkSetUnicodeEncodingSecFilter.pl)

TIME    : Fri Oct 28 13:01:35 2022

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

Invalid Connect string - cannot connect to database

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I ran AutoConfig in PATCH environment, it failed and gave similar error in the log:

*******FATAL ERROR*******

PROGRAM : ($FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl)

TIME    : Wed Nov  2 11:44:02 2022

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

Invalid Connect string - cannot connect to database

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I tried "sh adadminsrvctl.sh start forcepatchfs" in PATCH environment and it worked, which is required by ADOP later, even AutoConfig failed on PATCH.

I compared tnsnames.ora and listener.ora in both RUN and PATCH file systems and did not see any difference. After I read Doc ID 2378192.1 (12.2 Autoconfig Fails On PatchFS with error "ORA-12514: TNS:listener does not currently know of service requested"),  I made a change to database parameter SERVICE_NAMES:

SQL> show user

USER is "SYSTEM"

SQL> show parameter service_name

NAME                           TYPE        VALUE

------------------------------------ ----------- ------------------------------

service_names              string      EBSDEV

SQL> alter system set service_names='EBSDEV,EBSDEV_ebs_patch';

System altered.

SQL> show parameter service_name

NAME                          TYPE        VALUE

------------------------------------ ----------- ------------------------------

service_names              string      EBSDEV,EBSDEV_ebs_patch

After that, I ran AutoConfig in PATCH file system again, and got the same error. So, that was not the fix.

Then,  I found database parameter LOCAL_LISTENER might be incorrect. After I make below change (and maybe plus the change to service_names),  AutoConfig worked successfully in PATCH file system !

SQL> show parameter local_listener

NAME                          TYPE        VALUE

--------------------------------- ----------- ------------------------------

local_listener                string

SQL> alter system set local_listener='EBSDEV_LOCAL';

System altered.

SQL> show parameter local_listener

NAME                          TYPE        VALUE

------------------------------- ----------- ------------------------------

local_listener                string      EBSDEV_LOCAL

After AutoConfig worked in PATCH, I ran "adop phase=fs_clone" (successfully) in RUN file system to over-write the PATCH files. Then I added 2nd node to the instance without issue.

UTL_FILE_DIR in 19c database

Starting in Oracle Database 18c, initialization parameter UTL_FILE_DIR is deprecated and no longer supported. It can not be altered at DB level "alter system set utl_file_dir='/path/to/utl_dir;". Instead, specify the name of a directory object. 

UTL_FILE_DIR is no longer listed in V$SYSTEM_PARAMETER and related views:

SQL> SELECT value FROM v$system_parameter WHERE name='utl_file_dir' ;

no rows selected

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         unknown

But, view v$parameter has it:

SQL> column name format a15

SQL> column value format a55

SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir' ;

VALUE

--------------------------------------------------------------------------------

/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

SQL> SELECT value FROM apps.v$parameter WHERE name='utl_file_dir' ;

VALUE

--------------------------------------------------------------------------------

/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

SQL> select name, value from apps.v$parameter2 where name = 'utl_file_dir';

NAME            VALUE

--------------- --------------------------------------------------

utl_file_dir    /path/to/utl_dir

utl_file_dir    /home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

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

With Oracle Database 19c, you can specify the directories that you want Oracle E-Business Suite to use for PL/SQL file I/O in the supplemental UTL_FILE_DIR parameter within the apps.v$parameter and apps.v$parameter2 views. Each directory specified in that parameter must also have a corresponding directory object defined within the database. When you upgrade your Oracle E-Business Suite database from Oracle Database 11g or Oracle Database 12c to Oracle Database 19c, you use a script called txkCfgUtlfileDir.pl to migrate the directory paths formerly specified in the UTL_FILE_DIR database initialization parameter to the new supplemental UTL_FILE_DIR parameter and to create the corresponding directory objects. After the upgrade, you also use the same script to add, modify, or delete directory paths in the supplemental UTL_FILE_DIR parameter.

SQL> show parameter event

NAME                 TYPE        VALUE

--------------------- ----------- ------------------------------

event                     string      10946 trace name context forever, level 8454144

xml_db_events     string      enable

SQL> show user

apps

SQL> set serveroutput on

SQL> exec FND_FILE.PUT_LINE(fnd_file.output, 'Hello output!');

SQL> exec FND_FILE.close;

This shall create a .tmp file in $APPLPTMP.

FND_FILE uses the UTL_FILE package, which can only write to directories specified in init.ora (before 19c database)). APPLPTMP is a special directory used only for PLSQL temporary files. When a concurrent program uses the FND_FILE package, the concurrent manager uses APPLPTMP as the directory to which it writes temporary files. Thus, APPLPTMP must be one of the directories listed in init.ora in order for FND_FILE to work

We had a concurrent program calling a PL/SQL procedure owned by a custom schema xxdoc failing with errors:

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.

Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri

The procedure in custom schema called by a concurrent program is similar to:

CREATE OR REPLACE PROCEDURE xxdoc.proc_test1(errbuf out varchar2, retcode out number) as

BEGIN

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Test CP from custom procedure');

END;

CREATE OR REPLACE SYNONYM proc_test1 for xxdoc.proc_test1;   (by APPS)

The fix: Grant permission on directory EBS_DB_DIR_UTIL to XXDOC

Check a directory by " select * from dba_directories where directory_name = 'EBS_DB_DIR_UTIL'; " 

SQL> sho pdbs    (for SYSDBA. Or, select * from v$pdbs; )

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

---------- ------------------------------ ------------------- ----------

         2   PDB$SEED                      READ ONLY     NO

         3   EBSDEV                          READ WRITE   NO

SQL> alter session set container=EBSDEV;

Session altered.

SQL> sho pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

---------- ------------------------------ ------------------- ----------

         3   EBSDEV                           READ WRITE   NO

SQL> set lines 200 pages 1234

SQL> col directory_name for a35

SQL> col directory_path for a60

SQL> col owner for a20

SQL> select * from dba_directories where directory_name like '%UTIL%';

OWNER  DIRECTORY_NAME  DIRECTORY_PATH  ORIGIN_CON_ID

-----------  ---------------------------  --------------------------  -----------------------

SYS       EBS_DB_DIR_UTIL      /path/to/utl_dir             3

SQL> GRANT READ,WRITE on directory EBS_DB_DIR_UTIL to XXDOC;

Grant succeeded.

SQL> col grantee for a12

SQL> col owner for a15

SQL> col table_name for a35

SQL> col grantor for a20

SQL> col privilege for a20

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE 

from dba_tab_privs 

where table_name like '%UTIL%' and TYPE in ('DIRECTORY') 

order by owner,table_name,GRANTEE,privilege;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE 

-------------- ----------- ------------------- --------------- ----------------

APPS       SYS  EBS_DB_DIR_UTIL SYSTEM  READ 

APPS       SYS  EBS_DB_DIR_UTIL SYSTEM  WRITE 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          EXECUTE 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          READ 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          WRITE 

XXDOC  SYS  EBS_DB_DIR_UTIL SYS          READ 

XXDOC  SYS  EBS_DB_DIR_UTIL SYS          WRITE 

NOTES: 19c database has UTL directories. Seems all of them point to the folder on db host.

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from dba_tab_privs 

where table_name like '%UTL%' and TYPE in ('DIRECTORY') 

order by owner,table_name,GRANTEE,privilege;

REFERENCES:

- Concurrent Requests Fail With Error ORA-20100 (Doc ID 2723960.1)

- Creating a Custom Application in Oracle EBS Release 12.1.3 and above (Doc ID 1577707.1)

- Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)

  [ Section 2.2.1: Granting Privileges to Editioned Objects

    Section 1.1.4: Tools and Scripts for Edition-Based Development ]