"deleted" files still take disk space

We received alerts on disk space used by EBS is 97% full. When I use "du" to check the space, it shows only 60% of the space is used. But "df -h" shows it is 97% full. The problem is that some files deleted by EBS process will not really release the space back yet until the process is stopped. See https://access.redhat.com/solutions/2316

Below line will find if there are "deleted" files that still hold space, assuming the partition is /u02/app:

$ lsof | grep /u02/ | grep app | grep delete | more
rwrun     18759   applMgr   13u      REG              253,3 3140558848    3082366 $INST_TOP/temp/dat000418759 (deleted)
rwrun     18759   applMgr   15u      REG              253,3  745730048    3082368 $INST_TOP/temp/idx000518759 (deleted)
rwrun     18759   applMgr   17u      REG              253,3          0    3082375 $INST_TOP/temp/000718759 (deleted)

3140558848 is the size by the file. You may see the size is increasing if process 18759 keeps running. But you will not see the file by "ls" command.

$ ps -ef | grep 18759
$ORACLE_HOME/bin/rwrun mode=character ARRAYSIZE=5 P_CONC_REQUEST_ID=21334937 P_MAIL_INDICATOR='ALL' report=$APPL_TOP/aear/reports/US/XXXXRREG.rdf userid=APPS batch=yes destype=file desname=$APPLCSF/out/o21334937.out desformat=$FND_TOP/reports/PD pagesize=2050x66

We found concurrent report "XXXX Receipt Register" in request ID 21334937 ran for 15 hours. After it is cancelled, the "hidden" space is released back.

The same problem happens to /tmp file partition, when parameter forms_tmpdir points to /tmp.

$ grep forms_tmpdir $CONTEXT_FILE

         <forms_tmpdir oa_var="s_forms_tmpdir" osd="UNIX">/tmp</forms_tmpdir>

$ lsof | grep /tmp | grep delete   <== it takes longer

$ lsof /tmp | grep delete             <== it runs quick

frmweb      398              applMgr    86u      REG              253,5    5303826        365 /tmp/filer6nHW3.TMP (deleted)

frmweb      570              s044150   86u      REG              253,5   41307666        144 /tmp/file4FWO2C.TMP (deleted)

$ ps -ef | grep 570

applMgr     570 31251  0 Mar15 ?        00:07:39 frmweb server webfile=HTTP-0,0,1,default

It is better to change forms_tmpdir to $APPLTMP, $INST_TOP/temp or $NE_BASE/EBSapps/log. /tmp is for server logs and if it is full, the server may crash and go down.

NOTES: EBS Profile option "Attachment File Directory" may also point to/tmp (and make /tmp space filled up).

FND: Diagnostics to find the true error

After log onto EBS R12.1.3 homepage, click on Preferences and get a generic error:

You have encountered an unexpected error. Please contact the System Administrator for assistance.

The error seems just on one page, not on all pages. I had the same error message before when the database having issue, such as invalid package. But the error does not tell what is the real cause.

The key is to enable Profile option "FND: Diagnostics". After re-login, the error message will come with a link "Click here for exception details"

That leads to find the true error is an ORA-00600 error on SQL statement:

SELECT * FROM (select t.territory_short_name territory_name, t.nls_territory
from   fnd_territories_vl t, v$nls_valid_values v
where  t.nls_territory = v.value
and    v.parameter = 'TERRITORY') QRSLT  ORDER BY territory_name
;
ORA-00600: internal error code, arguments: [1350], [3], [23], [60], [AMERICAN], [0], [], [], [], [], [], []

EBS hits ORA-28040: No matching authentication protocol

After database was upgraded to 12.1.0.2, clone script to refresh a R12.1.3 instance got a error:

$ perl adcfgclone.pl appsTier
... ...
Target System Port Pool [0-99] : 27
Checking the port pool 27
done: Port Pool 27 is free
Report file located at $INST_TOP/admin/out/portpool.lst
Complete port information available at $INST_TOP/admin/out/portpool.lst
RC-40201: Unable to connect to Database EBSQA.

The detail error messages in log file are

---------------------------------------------------------------
                   ADX Database Utility
---------------------------------------------------------------
getConnectionUsingAppsJDBCConnector() -->
    APPS_JDBC_URL=''
    Trying to get connection using SID based connect descriptor
getConnection() -->
    sDbHost    : ebsdb1q
    sDbDomain  : domain.com
    sDbPort    : 1527
    sDbSid     : EBSQA
    sDbUser    : APPS
    Trying to connect using SID...
getConnectionUsingSID() -->
    JDBC URL: jdbc:oracle:thin:@ebsdb1q.domain.com:1527:EBSQA
    Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol
    Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA)))
    Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol
    Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA.domain.com)))
    Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA.domain.com)))
    Connection could not be obtained; returning null
-------------------ADX Database Utility Finished---------------
Exception occurred while preseeding variables in the context file: java.sql.SQLException: Could not get connection to the database

StackTrace:
java.sql.SQLException: Could not get connection to the database

Because of the error, it failed to generate TNS files:

##########################################################################
                   Generate Tns Names
##########################################################################
Logfile:  $INST_TOP/admin/log/NetServiceHandler.log
Classpath: $COMMON_TOP/java/lib/appsborg2.zip:$COMMON_TOP/java/classes
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated
adgentns.pl exiting with status 2
ERRORCODE = 2 ERRORCODE_END
.end std out.
.end err out.
Result             : FAILED

It seems next steps in the clone script use Sqlplus to make database connection and make this error ignorable. So, even with the error, the cloned instance worked fine.

To avoid this JDBC error, before running the clone script, add two lines to file $TNS_ADMIN/sqlnet.ora on database server and then bounce database listener (Doc. 2125856.1):
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

The setting 8 (or 10, 11) has nothing to do with the Oracle database version, it is authentication level used by the JDBC. But, it is a temporary solution, because it leads to use old and unsupported Oracle drivers. The best solution though is to upgrade the Oracle driver on client side.

One solution after the clone worked is to customize file $ORA_CONFIG_HOME/10.1.3/j2ee/forms/config/server.xml with using two .jar files in $COMMON_TOP/java/lib.

I did not test the solution by Doc. 2228803.1 which will modify Perl script to use two newer .jar files.

If clone script does not get error, the log should like this:

---------------------------------------------------------------
                   ADX Database Utility
---------------------------------------------------------------
getConnection() -->
    sDbHost    : ebsdb1q
    sDbDomain  : domain.com
    sDbPort    : 1547
    sDbSid     : EBSQA
    sDbUser    : APPS
    Trying to connect using SID...
getConnectionUsingSID() -->
    JDBC URL: jdbc:oracle:thin:@ebsdb1q.domain.com:1547:EBSQA
    Connection obtained
-------------------ADX Database Utility Finished---------------

Every EBS Forms got a popup FRM-40735 error

After I apply January 2018 CPU patch to R12.1.3, every single forms gives a popup window with error:

ORA-01403: no data found
FRM-40735 WHEN-NEW-ITEM-INSTANCE trigger raised unhandled exception ORA-06502

I compiled below items using adadmin. But the problem is still there.

Forms PL/SQL library (.pll)
Forms menu
Generate message files
Generate product JAR files
Forms executable

I also tried "Help > Diagnostics > Trace > Trace with Binds and Waits" to enable forms trace, but did not find any ORA- error in the trace log.

The problem was that one file CUSTOM.pll in $AU_TOP/resource was not owned by appl_mgr, but was owned by a developer who copied it to the server. Because of that, adpatch did not compile it during patching and later adadmin refused to compile it also when I ran adadmin to compile all library files.

After I manually compile it, the popup window does not show up on any forms! 

$ export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource
$ cd $AU_TOP/resource
$ frmcmp_batch.sh module=CUSTOM.pll userid=apps/appsPWD module_type=library compile_all=special

CUSTOM.pll timestamp will change and its file size may increase after compilation. Use command to check and compare file contents:

$ adident Header $AU_TOP/resource/CUSTOM.pll

adpatch: Drop FND_INSTALL_PROCESSES table [No] ?

When using adpatch to apply a patch, it reports an issue and I had to make the choice: 

Creating the AD_UTIL_PARAMS table...
Table AD_UTIL_PARAMS already exists, so dropping the table and recreating it.
Creating FND_INSTALL_PROCESSES table...
The table FND_INSTALL_PROCESSES created by AD Administration already exists.
If you are sure you do not want to keep the information from the failed
AD Administration session,you may drop FND_INSTALL_PROCESSES table and
continue with AutoPatch.
If you choose not to drop FND_INSTALL_PROCESSES table, AutoPatch
cannot continue successfully.
Drop FND_INSTALL_PROCESSES table [No] ?

The problem is an error from a previous session of adadmin. But the error was ignored.

After I answered Yes in above adpatch session, the session completed with the real error. 

AutoPatch error:
The following ORACLE error:
ORA-04045: errors during recompilation/revalidation of SYSTEM.LOGIN_AUDIT
ORA-01031: insufficient privileges
ORA-01434: private synonym to be dropped does not exist

occurred while executing the SQL statement:
DROP SYNONYM AD_DEFERRED_JOBS
AutoPatch error:
Error dropping FND_INSTALL_PROCESSES table

You should check the file
$APPL_TOP/admin/<SID>/log/xxxxx.log
for errors.

I checked back adadmin log and saw adadmin had the same error which is caused by custom trigger SYSTEM.LOGIN_AUDIT in the database. If the problem is not fixed, AD utilities (adadmin, adpatch, etc) will keep getting the error. After I dropped the trigger from database, the problem does not show up again.

The lesson is I should check adadmin log carefully even for a simple run.