ORA-00947: not enough values when refreshing materialized views



This is typical scenario for customers using materialized views to replicate below error -


SQL> exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);
BEGIN dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00947: not enough values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1



Base Table -

create table sample_tb(id number primary key,name varchar2(10),val number);


Insert data -

insert into sample_tb values(1,'ABC',99);

Materialized View -

create materialized view sample_tb_mv
as
select * from sample_tb;

Check data -

select * from sample_tb;
        ID NAME              VAL
---------- ---------- ----------
         1 ABC                99


Check metadata -

SQL> set lines 170
set pages 1200
set long 9999999
select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','SAMPLE_TB_MV') from dual;SQL> SQL> SQL>

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','SAMPLE_TB_MV')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."SAMPLE_TB_MV" ("ID", "NAME", "VAL")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255


Drop column from base-table 

alter table sample_tb drop column val;

Check data for materialized view again -

select * from sample_tb_mv;
        ID NAME              VAL
---------- ---------- ----------
         1 ABC                99

Try to manually refresh again-

exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);

SQL> exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);
BEGIN dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00947: not enough values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

SQL>


Check metadata (mv metadata is still the same)- 

set lines 170
set pages 1200
set long 9999999
select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','SAMPLE_TB_MV') from dual;
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','SAMPLE_TB_MV')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."SAMPLE_TB_MV" ("ID", "NAME", "VAL")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255


Solution -


Drop and recreate materialized view -

SQL> drop  materialized view sample_tb_mv;

Materialized view dropped.

SQL> create materialized view sample_tb_mv
as
select * from sample_tb;


Try to refresh and check metadata again -

SQL> exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);

PL/SQL procedure successfully completed.


SQL> set lines 170
set pages 1200
set long 9999999
select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','SAMPLE_TB_MV') from dual;SQL> SQL> SQL>

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','SAMPLE_TB_MV')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."SAMPLE_TB_MV" ("ID", "NAME")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
................



We can conclude that for ORA-00947: not enough values, on materialized views, we should follow below approach -

1. Check for metadata of both base table, materialized view log and materialized view.

2. Compare columns of base table and materialized view.


Before wrapping up, 2 clauses worth noticing for materialized views are -
a. preserve table-
This clause will be used with 'drop materialized view' command to make sure that materialized view is preserved as a stand alone table and this will save us some time when recreating materialized views.

b. on prebuilt table-
When recreating materialized view, we need to use this clause so that 'prebuilt table' that was preserved during 'delete materialized view' command is used.



Implications of latest Oracle WebLogic Server connection filters for Oracle EBS r12.2 customers



Above screenshot might click many Apps DBAs who have recently applied -
1. April 2019 CPU Patch
2. Upgraded to Latest AD and TXK level (C.11)


Recently I applied Oracle E-Business Suite Technology Stack Delta 11 on an EBS r12.2.7 implementation.
After applying patch we went for sanity checks and when trying to open Weblogic Server console I got this -
The Server is not able to service this request: [Socket:000445]Connection rejected, filter blocked Socket, weblogic.security.net.FilterException: [Security:090220]rule 2

Below are metalink ids ideally Apps DBAs should refer to for resolving this-
Alternative Methods to Allow Access to Oracle WebLogic Server Administration Console from Trusted Hosts for Oracle E-Business Suite Release 12.2 (Doc ID 2542826.1)
ORA-12547 While Client Connecting Via SSH Tunnel (Doc ID 454252.1)

Oracle Community also has few tips for this-
https://community.oracle.com/message/15413970#15413970


Troubleshooting Part -



cd $FMW_HOME/user_projects/domains/EBS_domain_<CONTEXT_NAME>/config/
cat config.xml | grep connection-filte
    <connection-filter>oracle.apps.ad.tools.configuration.wls.filter.EBSConnectionFilterImpl< connection-filter>
    <connection-filter-rule>appsnode * * allow</connection-filter-rule>
    <connection-filter-rule>0.0.0.0/0 * * deny</connection-filter-rule>




We will be exploring all the 3 scenarios with real-time usecases.

Option 1: Adding Specific Trusted Hosts

1. This can be done by using context variable - s_wls_admin_console_access_nodes
A comma seperated set of ips/hostnames(fqdns) can be used like as follows to allow set of system administrators/weblogic administrators
to access console -

<s_wls_admin_console_access_nodes oa_var="s_wls_admin_console_access_nodes">host1.domain.com,host2.domain.com</s_wls_admin_console_access_nodes>

2. Execute autoconfig on run filesystem.
3. Stop and start Oracle Weblogic admin server
 adadminsrvctl.sh stop
 adadminsrvctl.sh start
4. perform fs_clone to synchronize filesystems
 adop phase=fs_clone




Option 2: Allowing an IP Range

This option will be available after applying  Patch 29781255:R12.TXK.C.
There will be requirements where you need to provide IP range and it is important to
first understandd how CIDR works.

According to CIDR ruling, you can have a factor set to 4^n
This implies we can have ip range as
4,16,64,256

Sample example to narrow down IP range -
195.168.1.32/24 ---> 256  IP Hosts
195.168.1.32/26 --->  64  IP Hosts
195.168.1.32/28 --->  16  IP Hosts
195.168.1.32/30 --->  04  IP Hosts


I first checked for patch if already applied as a standard practice.

Query 1-
set lines 1000
col APPLIED_FILE_SYSTEM_BASE for a40
SELECT b.bug_number, asp.adop_session_id, asp.bug_number patch#,
       asp.session_type, asp.applied_file_system_base,
       asp.start_date, asp.end_date
FROM ad_bugs b, ad_patch_run_bugs prb, ad_patch_runs pr,
     ad_patch_drivers pd, ad_adop_session_patches asp
WHERE b.bug_id = prb.bug_id
  AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
  AND pr.patch_run_id = asp.patchrun_id
  AND prb.applied_flag = 'Y'
  AND b.bug_number = '&bug_num';
Enter value for bug_num: 29781255
old  11:   AND b.bug_number = '&bug_num'
new  11:   AND b.bug_number = '29781255'

no rows selected

Query 2-
SELECT adb.bug_number,ad_patch.is_patch_applied('122', 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (29781255);
Query 3-
select ad_patch.is_patch_applied('R12',-1,29781255) from dual;


Once patch was applied, we update CONTEXT file on run fs as follows -

cat $CONTEXT_FILE | grep wls | grep nodes
<s_wls_admin_console_access_nodes oa_var="s_wls_admin_console_access_nodes">195.168.1.32/30</s_wls_admin_console_access_nodes>

This can me below set of 4 ips -
195.168.1.32-to-195.168.1.35

Note: Easy way to calculate range of Ips is using online calculator -
https://www.ipaddressguide.com/cidr

Executed autoconfig and started admin server to reflect changes.



Option 3: Adding Specific Trusted Hosts

SSH tunneling is pre-requisite here and I achieved it using putty.
For lab environment using static ips, this can be simply achieved using below -
1. Ssh -> Logging
Provide Destination IP address and keep port for ssh as 22.

Save it with some name to it can be loaded later for future reference.

2. Connection -> SSH -> Tunnels
Provide Source port, client machine's port which is open and not blocked or used by any other application.
In my case it was 81.

Provide Destination Hostname:Port and click on 'Add'

3. Go back to session and save it.

4. If you are not intending to logon to server, you can use option under 'Ssh' -

5. Login to saved session and monitor Event log for putty Session -

5. Few more settings are required on your web-browser, I used Firefox here -





127.0.0.1 is for Localhost.

Clear browser cache and try console again -


Query to Check AD and TXK code levels in your EBS environment


Below query can be very handy in finding out current AD and TXK code levels.


SQL> col ABBREVIATION for a10
SQL> set lines 1000
SQL> col NAME for a50
SQL> col CODELEVEL for a20
SQL> SELECT ABBREVIATION,NAME,codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');
ABBREVIATI NAME                                               CODELEVEL
---------- -------------------------------------------------- ------------
ad         Oracle Applications DBA                            C.11
txk        Oracle Applications Technology Stack               C.11

Job Role Separation (JRS) - RAC/Non-RAC environments


Job Role Separation (JRS) environment when deploying Oracle Grid Infrastructure is about having 2 different users for GI and DB installation.
In our scenario, we will have oracle for database installation and oraasm for Grid Infrastructure installation


Checklist for Job Role Separation (JRS) environment-


1. setuid bit - As root, change the file permissions of the oracle executable under the <Grid_Home>/bin and the <db_home>/bin,
to 6751
This is basically used on binaries when you would like OS user A to execute binary X which owned by user B preserving privileges of user B.

# cd <Grid_Home>/bin
# ls -l oracle
# chmod 6751 oracle
# ls -l oracle
-rwsr-s--x 1 grid oinstall 173515905 May 21 17:04 oracle

This is the setuid bit, and this must be set in order for users, other than "Grid" user to have it work.
(same applies to the db_home)
# cd <ORACLE_Home>/bin
# ls -l oracle
# chmod 6751 oracle
# ls -l oracle


2. Both users (oracle,oraasm) should be part of oinstall,asmdba,asmadmin groups.
We perform Grid Infrastructure installation first and when installing database, you will not be able to locate diskgroups.
This happens when oracle user is not secondary member of group which is assigned when configuring oracleasm.
In my scenario,
ls -ltr /dev/oracleasm/disks
total 0
brw-rw---- 1 oraasm asmadmin 253,  7 Aug 17 14:27 PLNDWSTG_DATA8

id oracle
uid=1006(oracle) gid=1013(oinstall) groups=1013(oinstall),1006(dbaplstg),1015(asmdba),1017(racdb)

id oraasm
uid=1002(oraasm) gid=1013(oinstall) groups=1013(oinstall),1002(asmadmin),1003(dbadvetl),1004(dbadvndw),1005(dbaplpub),1006(dbaplstg),1007(dbautpub),1008(dbautstg),1015(asmdba),1016(asmoper),1017(racdba)

We added asmadmin as secondary group to oracle user here.


3. Check for /etc/fstab to make sure grid home filesystem was mounted with option 'nosuid'

4. /etc/oracle/olr.loc must have correct GI home location.
This can occur during upgrades and we must make sure we have correct location under olr.loc for CRS_HOME and not old one.

5. Correct permissions for -
- GI_BASE
- GI_HOME

6. sqlnet.ora under GI_HOME should have below -
- DIAG_ADR_ENABLED=ON
- ORA_CLIENTTRACE_DIR to any valid directory that is having write permissions for oraasm in your GI_HOME
7. RDBMS_HOME/dbs/init.ora should only contain diskgroup name for db_create_online_log_dest_n.
To avoid below error, you can have init.ora parameter file checked to point to 'diskgroup name' and not complete path.


8. Both RDBMS_HOME/lib and GRID_HOME/lib must have correct permissions (755)


9. Check if CRS_HOME or ORA_CRS_HOME environments are set and make sure they are point to right GI_HOME. (typically during upgrades).

10. Last but not the least, we should check asm instance status using crsctl -
./crsct stat res -t


Reference notes -


ASM Diskgroup Can Not Be Shown When Creating Database With DBCA (Doc ID 1269734.1)
DBCA Does Not Display ASM Disk Groups In 11.2 (non-Windows environments) (Doc ID 1177483.1)
Remote Diagnostic Agent (RDA) - Getting Started (Doc ID 314422.1)
ORA-01261: Parameter Db_create_online_log_dest_1 Destination String Cannot Be Translated (Doc ID 2369000.1)




Lessons learnt from fresh ebs r12.2 installation on Oracle Linux 7



1. Preinstaller rpms are great and your files are backed up under /var/log/oracle-ebs-server-R12-preinstall/backup
sysctl.conf
resolv.conf
grub.cfg

2. orakernel.log often ignored should be reviewed after installing both rpms
oracle-rdbms-server-12cR1-preinstall
oracle-ebs-server-R12-preinstall

3. applmgr and oracle have oinstall as common primary OS group. applmgr does not have dba as secondary group.

4. Starting from OL7, user limits are not stored in /etc/security/limits.conf. There will be .conf files created under /etc/security/limits.d 
that are read by systemd during server startup 

5. resolve.conf file will be overridden if you have DNS1/2 directives in your network configuration files -
/etc/sysconfig/network-scripts/ifcfg-*
You can make resolv.conf immutable to changes or remove directives DNS1/2 in your network configuration files.

Forms Abnormal Termination - FORMS_CATCHTERM, FORMS_RECORD_GROUP_MAX & FORMS_MMAP






When we are facing performance issues with frmweb sessions, below CONTEXT_FILE variables must be considered –

FORMS_CATCHTERM –

This variable decides enabling/disabling of forms abnormal termination handler(FATH).
FATH basically writes diagnostic information into dumpfile or form server log. Default value is '1'.

FORMS_RECORD_GROUP_MAX –

This is a customized environment variable when using in servlet mode while we can directly set this in socket.env with socket mode.

FORMS_MMAP –

This is mainly monitored in socket mode. When set to 0 will not put .fmx files in shared memory mapped files resulting in more memory consumption. Recommended value for this variable is 1.

Scenario -

Common frmweb process issues are with high CPU and memory consumption. This results in slowing down application server where forms services are running. Whenever an end user is working LOVs fetching huge number of records, these records are loaded in memory and result in paging. CPU consumption then increases paging process in and out of memory.
Core dumpfiles or form server logs are located under -
$FORMS_TRACE_DIR

Note that these settings are same in r12.1 and r12.2 as forms version did not change.
We will not be discussing about setting these values as it is already mentioned in 745711.1. We will understand implications post setting these values –

Case – 1 (Default)

FORMS_CATCHTERM=1
This implies Forms abnormal Termination handler is enabled and as part of post implementation, we as APPS DBAs must consider setting it to 0 as recommended in 745711.1.

Case – 2 (Socket Mode)

Reference - 1382442.1
socket.env under INST_TOP is updated in socket mode and below are 3 variables that should be modified.
FORMS_CATCHTERM
FORMS_MMAP
FORMS_RECORD_GROUP_MAX

Case – 3 (Servlet Mode)

Reference - 745711.1
"s_forms_catchterm" is name for FORMS_CATCHTERM in CONTEXT_FILE.
While FORMS_RECORD_GROUP_MAX does not have any autoconfig predefined name. We require additional customization for the same.
FORMS_CATCHTERM
FORMS_RECORD_GROUP_MAX

  

Implications of Setting FORMS_RECORD_GROUP_MAX


This variable can impact forms of different module to limit number of records to be fetched. Below is summarized list of doc ids and recommendations given by Oracle. It is advisable to set this variable optimally according to your environment.
You can set it to 10000, 20000 or 30000 as per Oracle. There are some rare occasions when even 30000 will not suffice. In such rare occasions you can always raise a ticket with Oracle Support.

Oracle Support internally should request development team to modify standard form and release it as a patch for your environment.

Unable To Add Item In ASCP Planning Workbench When Item List > FORMS_RECORD_GROUP_MAX Environment Variable (Doc ID 2294933.1)
-          Download, review and apply patch 26407840

In Sales Order form Query by Held By Field, LOV does not show all options (Doc ID 2478176.1)
-          Download, review and apply patch 28521267

EBS: Searching LOV has shown error Choices in list, limited by maximum rows: 30000 (Doc ID 2190037.1)
-          FORMS_RECORD_GROUP_MAX=50000

'Menus' LOV from 'Responsibilities' form (FNDSCRSP.fmb) using a filter shows no result or shows part of the existing menus (Doc ID 2390886.1)
-          Download, review and apply patch 27362828

When Query Manufacturers Part Numbers Via Form INVIDMPN Error Message "Choices in the list, limited by maximum rows: 30000" (Doc ID 1272217.1)
-          Download, review and apply patch 12983688

CSXSRISR.FMB NEEDS FILTER BEFORE DISPLAY = YES ON LOV'S (Doc ID 2322101.1)
-          Download, review and apply patch 26522836

Lov Constraint On Project Field In 'Release Sales Order For Picking' Form (Doc ID 1094523.1)
-          Download, review and apply patch 9434838

Cannot Add Functions To Menus : Some Functions Are Not Available In The List Of Values (Doc ID 1961346.1)
-          Increase value of variable FORMS_RECORD_GROUP_MAX to 30000

Task Assignee LOV Is Restricted To Show Limited Number of Records (Doc ID 1142284.1)
-          Increase value of variable FORMS_RECORD_GROUP_MAX to 30000 

Forms Abnormal Termination - RTI Files


WHAT ARE .RTI FILES?


For the starters, we referred - What Are .RTI And .FLI Files? (Doc ID 470850.1) and summarized as follows -
Format-    em_<PID>.rti
These files are generated by the forms runtime processes.
Used by Oracle Application Manager "OAM" and the Enterprise Manager "EM" for Forms Monitoring services

The rti files should automatically be cleaned up/removed when the user logs out of the Oracle Applications in a normal logout fashion.
However, if a user does not logout, and just uses the 'X' button to close the form or kills the browser session before logging out of forms, then the rti file may not get cleared.
If the files are not automatically being removed, then you can write a Unix shell script to remove them periodically.

We opened a forms session from ebs r12.1.3 to simulate -
1. Normal Logout Fashion for forms
2. Forms Abnormal termination

We used below environment for our test-case -

Normal Logout Fashion for forms



Suppose client IP is 195.1.1.171
OS level "ps" command can easily help us get process id of frmweb session as follows -

[applprod@prod forms]$ ps -ef | grep frmweb | grep 195.1.1.171
applprod 21246 22589  0 14:25 ?        00:00:00 /data/prod/apps12/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,195.1.1.171

SQL Prompt –
SQL> select v.MACHINE,v.PROGRAM, v.MODULE, v.ACTION, v.CLIENT_IDENTIFIER
from v$session v
where v.process = '21246';


We then checked $FORMS_TRACE_DIR -
[applprod@prod forms]$ ls -ltr | tail
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:20 em_20068.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:21 em_20240.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:22 em_20479.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:23 em_20782.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:25 em_21246.rti
-rw-r--r-- 1 applprod proddba  132 Jul  7 14:25 em_21357.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:31 em_23143.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:33 em_24700.rti
-rw-r--r-- 1 applprod proddba 1701 Jul  7 14:40 Servlet_RTI.log_22589
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:40 em_27036.rti


There were 2 files updated -

[applprod@prod forms]$ tail Servlet_RTI.log_22589
20068 frmweb server webfile=HTTP-0,0,1,default,195.1.1.137
20240 frmweb server webfile=HTTP-0,0,1,default,195.1.1.150
20479 frmweb server webfile=HTTP-0,0,1,default,195.1.9.136
20782 frmweb server webfile=HTTP-0,0,1,default,195.1.1.177
21246 frmweb server webfile=HTTP-0,0,1,default,195.1.1.171
21357 frmweb server webfile=HTTP-0,0,1,default,195.1.2.90
23143 frmweb server webfile=HTTP-0,0,1,default,195.1.1.159
27036 frmweb server webfile=HTTP-0,0,1,default,195.1.1.225
27984 frmweb server webfile=HTTP-0,0,1,default,195.1.5.160
28143 frmweb server webfile=HTTP-0,0,1,default,195.1.2.142
[applprod@prod forms]$ cat em_21246.rti
listener = 1
config = default
pid = 21246
connect = Sun Jul  7 14:25:06 2019 +04
ip = 195.1.1.171
user = APPLSYSPUB
db_string = PROD
[applprod@prod forms]$
[applprod@prod forms]$



From front-end, we went for a normal exit of Oracle Forms.
Closed form window as follows –




Normal termination automatically cleared file em_21608.rti from backend.
[applprod@prod forms]$ ls -ltr em_21246.rti
ls: em_21246.rti: No such file or directory
[applprod@prod forms]$


Even entry in file Servlet_RTI.log_22589 was cleared
[applprod@prod forms]$ cat Servlet_RTI.log_22589 | grep 21246
[applprod@prod forms]$



Forms Abnormal Termination -

Later we opened a new form session for abnormal termination.
We abnormally terminated this form by closing browser directly and cleared web browser cache,
reopened web browser and started form session again

[applprod@prod forms]$ ls -ltr em_21246.rti
ls: em_21246.rti: No such file or directory
[applprod@prod forms]$ ls -ltr | tail
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:20 em_20068.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:22 em_20479.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:23 em_20782.rti
-rw-r--r-- 1 applprod proddba  132 Jul  7 14:25 em_21357.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:31 em_23143.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:40 em_27036.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:42 em_27984.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:43 em_28143.rti
-rw-r--r-- 1 applprod proddba 1642 Jul  7 14:49 Servlet_RTI.log_22589
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:49 em_30121.rti
[applprod@prod forms]$
[applprod@prod forms]$
[applprod@prod forms]$ cat em_30121.rti
listener = 1
config = default
pid = 30121
connect = Sun Jul  7 14:49:00 2019 +04
ip = 195.1.1.171
user = APPLSYSPUB
db_string = PROD
[applprod@prod forms]$

[applprod@prod forms]$ tail Servlet_RTI.log_22589
20068 frmweb server webfile=HTTP-0,0,1,default,195.1.1.137
20479 frmweb server webfile=HTTP-0,0,1,default,195.1.9.136
20782 frmweb server webfile=HTTP-0,0,1,default,195.1.1.177
21357 frmweb server webfile=HTTP-0,0,1,default,195.1.2.90
23143 frmweb server webfile=HTTP-0,0,1,default,195.1.1.159
27036 frmweb server webfile=HTTP-0,0,1,default,195.1.1.225
27984 frmweb server webfile=HTTP-0,0,1,default,195.1.5.160
28143 frmweb server webfile=HTTP-0,0,1,default,195.1.2.142
30121 frmweb server webfile=HTTP-0,0,1,default,195.1.1.171
30236 frmweb server webfile=HTTP-0,0,1,default,195.1.1.214
[applprod@prod forms]$



We closed browser directly without closing form session to make sure there was abnormal termination for forms.
[applprod@prod forms]$ ls -ltr | tail
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:22 em_20479.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:23 em_20782.rti
-rw-r--r-- 1 applprod proddba  132 Jul  7 14:25 em_21357.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:31 em_23143.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:40 em_27036.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:42 em_27984.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:43 em_28143.rti
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:49 em_30121.rti
-rw-r--r-- 1 applprod proddba 1701 Jul  7 14:49 Servlet_RTI.log_22589
-rw-r--r-- 1 applprod proddba  133 Jul  7 14:49 em_30236.rti
[applprod@prod forms]$ cat em_30121.rti
listener = 1
config = default
pid = 30121
connect = Sun Jul  7 14:49:00 2019 +04
ip = 195.1.1.171
user = APPLSYSPUB
db_string = PROD
[applprod@prod forms]$


Concluding our use-case, em_30121.rti will reside on server forever unless cleared manually as part of house-keeping. 

We can set a cron job to perform this, a shell script with below code will clear .rti files older than 15 days -


cd $FORMS_TRACE_DIR

find . -name "em_*.rti" -mtime +15 |wc -l >> /data/prod/apps12/apps/scripts/file_count.lst;
echo "\n number of forms  logfiles older than 15 days - `cat /data/prod/apps12/apps/scripts/file_count.lst | tail -1`"


find . -name "em_*.rti" -mtime +15 -exec rm -f {} \;