Custom files are created in $ORACLE_HOME/dbs after upgrade to 19c

[Artyom]

Hi Erman!

We faced with the following issue: after upgrading our OEBS 12.2 database from 12.1 to 19c, we observe lots of custom files and folders creating in $ORACLE_HOME/dbs directory. I think it is connected with UTL_FILE_DIR usage in EBS 12.2 with the special event='10946 trace name context forever, level 8454144'. Did you have some experience with that? Are there any ways to debug files creation?

Regards,
Artyom

[ErmanArslansOracleBlog]

What type of file are they?
What are their names?
*Please send me a sample ls -al output , so I can see a bunch of them.

*I suppose your OS is linux.. So in Linux , you can use auditd for monitoring file creations..

auditd, which is a userspace component to the Linux Auditing System.

Manual of auditd:
http://man7.org/linux/man-pages/man8/auditd.8.html

auditd configuration file -> /etc/audit/auditd.conf
audit rules file called /etc/audit/audit.rules

[ErmanArslansOracleBlog]

Also send me the output of the following->

sqlplus "/as sysdba"
SQL> show parameter utl_file_dir

[Artyom]

Hi,

thank you very much for the reply! The files are primarily directories, for example:

drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Текнолоджи_770501001_2020.01.23
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_165045001_2020.01.23
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_667145001_2020.01.23
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_770401001_2020.01.21
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_770401001_2020.01.22
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_770401001_2020.01.23
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_770501001_2020.01.23
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_770501001_2020.01.24
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_780445001_2020.01.23
drwxr-xr-x   2 oracle oinstall      4096 Apr 13 13:06 Технологии_780445001_2020.01.24

I think it is created from custom programs that use UTL_FILE functionality.

utl_file_dir is unset since it is 19c database
SQL> show parameter utl

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
create_stored_outlines               string

[ErmanArslansOracleBlog]

Yes.. utl_file_dir is obsoleted.
So if they are customer, they may be created through Database directories.
You can check those db directories, which are mapped to OS directories,  using -> dba_directories view.

[Artyom]

Yes I checked dba_directories, there are no directories defined as $ORACLE_HOME/dbs. Since in EBS 12.2 there is a special event 10946 that turns on UTL_FILE functionality back, there should be the way to debug it...

[ErmanArslansOracleBlog]

1)If you don't have any directory objects, then please check apps.v$parameter and apps.v$parameter2 views.
These views are for providing a supplemental UTL_FILE_DIR parameter.

Reference Oracle Support ->, Oracle E-Business Suite has introduced new apps.v$parameter and apps.v$parameter2 views in the APPS schema that provide a supplemental UTL_FILE_DIR parameter. This new parameter can be referenced similarly to the way the former UTL_FILE_DIR database initialization parameter was referenced. The views in the APPS schema contain the same columns and rows as the standard v$parameter and v$parameter2 views in the SYS schema and also contain an additional row or rows for the supplemental UTL_FILE_DIR parameter. The apps.v$parameter view contains a row storing the UTL_FILE_DIR value as a comma-separated list of directories, while the apps.v$parameter2 view contains a separate row for each directory.

2)It may help checking the content of those files.. Their content may give us some clue.

3)Also check any enabled debug profiles which is related to tracing the database sessions..  These kinds of forgotten debug enabling settings, may also decrease EBS performance, we recommend regularly checking the debug profiles to see whether there are any enabled debug profiles left, and we recommend not using the debug profiles unless it is necessary.

4)Check your initialization parameters.. Ensure they are compatible with the EBS aware initialization settings documented in the interoperability notes.. Any wrong trace-related init parameter may result these kinds of issues.. We don't normally expect any trace files under $ORACLE_HOME/dbs.. So some setting is missing there.  It can be in any layer. Apps or DB.. For instance -> If the CORE_DUMP_DEST is not used, the default will be the <ORACLE_HOME>/dbs directory.

5)If you are on Linux, using auditd may help auditing that activity -> https://ermanarslan.blogspot.com/2016/06/linux-auditing-in-linux-auditd-setfacl.html