What's inside FND_LOBS ?

FND_LOBS table is one of the largest objects in an E-Business Suite instance. In our largest instance the size of this table is 160 GB.

FND_LOBS table contains all the attachments which were attached by users in all the modules of E-Business Suite, since the instance was created.

To know which Forms provide Attachment feature
----------------------------------------------------------------------

SQL>select *

from fnd_attachment_functions

where function_name like '%FND_%';

You can see the contents of fnd_lobs through this query:

select file_name, file_id,
to_char(upload_date,'dd-mon-rr hh24:mi:ss'),
to_char(expiration_date ,'dd-mon-rr hh24:mi:ss'),
file_content_type,
dbms_lob.getlength(file_data) size_byte
from applsys.fnd_lobs
--where file_id= ;

Metalink Note 338651.1 has more information about FND_LOBS:

FND_LOBS stores information about all LOBs managed by the Generic File Manager (GFM).
Each row includes the file identifier, name, content-type, and actual data. Each row also includes the dates the file was uploaded and will expire, the associated program name and tag, and the language and Oracle characterset.

The file data, which is a binary LOB, is stored exactly as it is uploaded from a client browser, which means that no translation work is required during a download to make it HTTP compliant.

Therefore uploads from non-browser sources will have to prepare the contents
appropriately (for instance, separating lines with CRLF).

The program_name and program_tag may be used by clients of the GFM for any purpose,
such as striping, partitioning, or purging the table if the program is de-installed.
They are otherwise strictly informative.

These columns and the expiration date are properly set when the
procedure FND_GFM.CONFIRM_UPLOAD is called. If not called, the column
expiration_date remains set, and will eventually be purged by the procedure
FND_GFM.PURGE_EXPIRED.

FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database. If the user elects to link an OLE Object to the document, this table stores the information necessary for Oracle Forms to activate the OLE server, and it saves a bit-mapped image of the OLE server's contents.
If the user does not elect to link an OLE Object, the entire document will be stored in this table.

FND_DOCUMENTS_LONG_TEXT stores information about long text documents.

FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.

Relaying denied 550 5.7.1

Relaying denied error was received during this test:

Login to appserver.justanexample.com
telnet pluto.justanexample.com 25
MAIL FROM:pink.panther@justanexample.com
250 2.1.0 pink.panther@justanexample.com... Sender ok
RCPT TO:anthony.gonsalves@justanexample.com
550 5.7.1 anthony.gonsalves@justanexample.com... Relaying denied

In sendmail log in /var/log/sylog, the corresponding log entry was:

Feb 10 09:11:12 pluto sendmail[13783]: [ID 801593 mail.info] n1AEB3Ea013783: Authentication-Warning: pluto.justanexample.com: physicalpluto.justanexample.com [192.168.9.22] didn't use HELO protocol
Feb 10 09:11:21 pluto sendmail[13783]: [ID 801593 mail.notice] n1AEB3Ea013783: ruleset=check_rcpt, arg1=anthony.gonsalves@justanexample.com, relay=appserver.justanexample.com[192.168.4.1], reject=550 5.7.1 anthony.gonsalves@justanexample.com Relaying denied

I googled on ruleset=check_rcpt, and found this from sendmail.org:
check_rcpt
The address supplied through the RCPT command can be checked against the check_rcpt ruleset. On first look, this ruleset doesn't make much sense. Why check the recipient? sendmail does this anyway when trying to deliver, esp. for local recipients. However, this ruleset can be used to check whether your system is (mis)used as a relay. The check_compat ruleset, which seems to be better suited for this purpose, since it gets both addresses (sender and recipient) as parameters, is called too late. To reject a misuse at the earliest moment (and save your bandwidth etc), you can refer to the address of the sending system, which is available in the macro ${client_addr}. However, to use it in a rule, you have to refer to it as: $(dequote "" $&{client_addr} $) so sendmail defers evaluation and tokenizes it. The old solution is based on a proposal from Chin Huang: But since there is a problem with these rules, here is a new solution. First, we check whether it is a local client: it can do whatever it want. Next, we remove the local part, maybe repeatedly. If it still has routing information in it, it seems to be a relay attempt. (There are also a more detailed explanation of this ruleset and some additional thoughts.)
So list in the class

F{LocalIP} /etc/mail/LocalIP

the IP addresses of the local clients you will allow to relay through your mail server, for example

134.245 127.0.0.1

A client which connects from one of these IP numbers can send mail through your relay anywhere.

Scheck_rcpt # first: get client addr R$+ $: $(dequote "" $&{client_addr} $) $| $1 R0 $| $* $@ ok no client addr: directly invoked R$={LocalIP}$* $| $* $@ ok from here # not local, check rcpt R$* $| $* $: $>3 $2 # remove local part, maybe repeatedly R$+ $:$>removelocal $1 # still something left? R$*<@$*>$* $#error $@ 5.7.1 $: 550 we do not relay Sremovelocal # remove RelayTo part (maybe repeatedly) # R$*<@$*$={RelayTo}.>$* $>3 $1 $4 R$*<@$=w.>$* $: $>removelocal $>3 $1 $3 R$*<@$*>$* $@ $1<@$2>$3 # dequote local part R$+ $: $>3 $(dequote $1 $) R$*<@$*>$* $: $>removelocal $1<@$2>$3

The trailing $* after $={LocalIP} matches incompletely specified IP addresses on octet boundaries, as can be seen by 134.245 which matches a whole class B subnet.

If you relay mail for other systems (e.g., the secondary MX of a system points to your mailhost or your server is the primary MX, but you forward the mail to another system), use also:

F{RelayTo} /etc/mail/RelayTo

to list all hosts you relay mail to or accept mail for. For example, we put

uni-kiel.de

in RelayTo. Then enable the rule

R$*<@$*$={RelayTo}.>$* $>3 $1 $4

The leading $* will match subdomains of those domains in RelayTo too. You can also use a map instead of a class, if you slightly change the rules.

If you encounter a problem with this ruleset, try to find out which options you're missing or debug it directly.

However adding /etc/mail/LocalIP didn't help as our sendmail version was 8.13.8 and most probably this feature is available in Sendmail 9. I searched inside sendmail configuration file sendmail.cf for filenames which are checked:

# grep /etc/mail /etc/mail/sendmail.cf
Fw/etc/mail/local-host-names
FR-o /etc/mail/relay-domains
O AliasFile=/etc/mail/aliases
#O ErrorHeader=/etc/mail/error-header
O HelpFile=/etc/mail/helpfile
O StatusFile=/etc/mail/statistics
#O UserDatabaseSpec=/etc/mail/userdb
#O ServiceSwitchFile=/etc/mail/service.switch
#O DefaultAuthInfo=/etc/mail/default-auth-info
Ft/etc/mail/trusted-users

/etc/mail/local-host-names stood out. So I seached on that. and found this comment on http://www.ozzu.com/unix-linux-forum/how-setup-relay-host-sendmail-t29690.html

/etc/mail/relay-domains contains a list of hosts which are allowed to relay mail through your mail server. This list may consist of either specific hosts or whole domains.

/etc/mail/sendmail.cw (after sendmail version 8.10, this file is local-host-names) contains a list of domains for which your mail server will accept mail. This list is usually the domains hosted by your machine.

NOTE: put a CR/LF at the end of the last domain. Otherwise it won't work.

So I added appserver.justanexample.com in /etc/mail/local-host-names and bounced sendmail:

svcadm restart sendmail

The relay denied error stopped coming after this change.

dms0/Spy dms1/Spy oprocmgr-service http-403 http-404 in apache access log

Recently we saw these HTTP-404 and HTTP-403 errors in Apache Logs:

/oprocmgr-service?cmd=Getprocs HTTP/1.1" 404 222 0

192.168.9.2 - - [09/Feb/2009:13:54:46 -0500] "GET /dms1/Spy?recurse=all&format=xml&operation=get&value=false&units=true&description=true&name=/&noop=/ HTTP/1.1" 403 218 0
192.168.2.33 - - [09/Feb/2009:13:54:46 -0500] "GET /dms0/Spy?recurse=all&format=xml&operation=get&value=false&units=true&description=true&name=/&noop=/ HTTP/1.1" 403 218 0


Metalink Note 579106.1 tells us the cause and resolution:

Cause

These messages are generated when you use the OEM Grid Control to monitor the HTTP server or other application server components from it .

The reason is the Grid control is not certified to monitor iAS 1.0.2.2.

Solution

To implement the solution , please execute the following steps to avoid generation of these messages :

1. Stop the OEM Grid agent.

2. Remove the monitoring for the iAS 1.0.2.2 used by E-Business Suite

3. Bounce Apache server.

RDA standalone

RDA or Remote Diagnostic Agent is a set of diagnostic scripts provided by Oracle support which provides them with valuable information about your instance. Recent versions of RDA come bundled with Software Configuration Manager which runs as a cron job in your system and periodically collects configuration information about your instance and sends it to Oracle.

Metalink Note 330472.1 has a link for standalone RDA without Software Configuration Manager.

Metalink Note: 330363.1 (RDA4 FAQ)

Good queries on FND tables for Apps11i

1. TO FIND APPLICATION DETAILS
===============================

This will provide you application id alongwith shortname and base path of it:

SELECT *
FROM fnd_application;


2. TO FIND PRODUCT INSTALLATION DETAILS
========================================

SELECT application_id,
product_version,
status,
patch_level
FROM fnd_product_installations;

Note: Here application_id would be id as per query no. 1, product_version could
be '11.5.0', status could be 'I' / 'S' / 'N' and patch_level could be '11i.AD.I'

3. TO FIND THE TABLE DETAILS
=============================

SELECT application_id,
table_id,
table_name,
user_table_name,
description
FROM fnd_tables
WHERE table_name LIKE UPPER('%&table_name%');

Note: Enter the table_name for which you want to find out details. You can put
application_id to filter tables.


4. TO FIND TABLE COLUMNS
=========================

Make sure to enter the table name in CAPS only:

SELECT application_id,
table_id,
column_id,
column_name,
user_column_name,
column_sequence,
column_type,
width,
description
FROM fnd_columns
WHERE table_id =
(SELECT table_id
FROM fnd_tables
WHERE table_name LIKE UPPER('&table_name'));

Note: Enter the table_name for which you want to find out column details.


5. TO FIND VIEWS DETAILS
=========================
SET LONG 1000

SELECT application_id,
view_id,
view_name,
description,
text
FROM fnd_views
WHERE view_name LIKE UPPER('%&view_name%');


6. TO FIND VIEW COLUMNS
========================

SELECT application_id,
view_id,
column_sequence,
column_name
FROM fnd_view_columns
WHERE view_id =
(SELECT view_id
FROM fnd_views
WHERE view_name LIKE UPPER('&view_name'));

Note: Enter the view_name for which you want to find out view column details.

7. TO FIND CURRENCY DETAILS
===========================

SELECT currency_code,
symbol,
enabled_flag,
currency_flag,
description,
precision,
extended_precision,
minimum_accountable_unit,
start_date_active,
end_date_active
FROM fnd_currencies
WHERE currency_code LIKE '%¤cy_code%';

Note: Here currency_code could be 'USD', 'GBP' etc.

8. TO FIND THE EXECUTABLE DETAILS
==================================

SELECT application_id,
executable_id,
executable_name,
execution_file_name,
subroutine_name,
icon_name,
execution_file_path
FROM fnd_executables
WHERE application_id = &application_id AND executable_name LIKE '%&
executable_name%';

Note: Here application_id could be id as per query no. 1 and executable_name
could be 'APXPBFOR'

9. TO FIND INDEX DETAILS
========================
SELECT dba.status,
fnd.application_id,
fnd.table_id,
fnd.index_id,
fnd.index_name,
fnd.description
FROM fnd_indexes fnd,
dba_indexes dba
WHERE table_id =
(SELECT table_id
FROM fnd_tables
WHERE table_name LIKE UPPER('&table_name')) and fnd.index_name = dba.
index_name;

Note: Enter the table_name for which you want to find out index details.

10. TO FIND INDEX COLUMNS
==========================

SELECT application_id,
table_id,
index_id,
column_sequence,
column_id
FROM fnd_index_columns WHERE table_id =
(SELECT table_id
FROM fnd_tables
WHERE table_name = UPPER('&table_name'));

Note: Enter the table_name for which y.
ou want to find out index columns. If
you are aware of index_id from query no. 9 above then specify that in the where
clause instead of table_name.

11. TO KNOW PRIMARY KEY DETAILS
===============================

SELECT application_id,
table_id,
primary_key_id,
primary_key_name,
description,
enabled_flag
FROM fnd_primary_keys
WHERE table_id =
(SELECT table_id
FROM fnd_tables
WHERE table_name LIKE UPPER('&table_name'));

Note: Enter the table_name for which you want to find out primary key details.

12. TO FIND SEQUENCE DETAILS
============================

SELECT application_id,
sequence_id,
sequence_name,
start_value,
increment_by,
min_value,
max_value,
cache_size,
cycle_flag,
order_flag,
description
FROM fnd_sequences
WHERE sequence_name = UPPER('&sequence_name');


13. TO FIND PROFILE OPTION DETAILS
==================================

SELECT application_id,
profile_option_id,
profile_option_name,
site_enabled_flag,
resp_enabled_flag,
user_enabled_flag
FROM fnd_profile_options
WHERE profile_option_name LIKE UPPER('%&profile_option_name%');


14. TO FIND FOLDERS DETAILS
============================

Note: Here the NAME is completely case sensitive and hence needs to be passed
as defined in FOLDER:

SELECT folder_id,
object,
name,
public_flag,
autoquery_flag,
where_clause,
order_by
FROM fnd_folders
WHERE name like '%&name%';.

Version Conflicts utility failed

Mohan pinged me about one issue in R12 upgrade.  He had successfully installed R12 on DB node and primary node file system.  However he was facing issues on secondary node.

This was a shared application file system.  This was the error:

RW-50016: Error: - APPL_TOP environment file was not created:
File = /R12/appl/apps_st/R12appl/$CONTEXT_NAME.env
ADOVARS environment file passes instantiated variables test:
File = $APPL_TOP/admin/adovars.env

APPSCONFIG passes instantiated variables test:
File = $APPL_TOP/admin/adconfig.txt

in autoconfig log file is see this error

Starting AutoConfig at Thu Feb 5 13:22:01 2009
Using adconfig.pl version 120.16.12000000.8

Classpath : $OA_JAVA/lib/appsborg2.zip:$OA_JAVA/classes

ERROR: Version Conflicts utility failed.
Terminate.

A quick search on metalink yielded note 559806.1

Here are the steps for the workaround:
1. Do the installation using the setting that you require. This will fail due to the bug. (if you have a current failed installation then use that)
2. Copy the config file ($INST_TOP/conf_.txt) from the failed Installation file to a different location and modify the s_javatop variable with the correct value :
For example :
change from:
s_javatop=/u00/TEST/apps/apps_st/comn/java/classes
to:
s_javatop=/u00/TEST/maxcomn/java/classes

(Change other variables if they are incorrect if any)

3. Delete the failed install but preserve the modified conf_.txt . ie delete evrything but the altered config file.
4. Point the /etc/oraInst.loc to a new location to create a new oraInventory, or delete if you are using a single oraInventory for each installation. If this is the only installation on this Node then delete the current oraInventory.
5. Install using the modified config file as follows :
rapidwiz -silent -config .txt
For Example :
$ rapidwiz -silent -config /u00/TEST/conf_TEST.txt

The current s_javatop in config file was
/R12/appl/apps_st/apps/apps_st/comn/java/classes

This was changed to

/R12/appl/apps_st/R12comn/java/classes

Mohan said that it is really a bug.  He had checked config file for the other two nodes and s_javatop was correctly set on those,  but for 3rd node it is set differently

Mohan deleted the sid_host directory from inst_top and started rapidwiz

It ran fine and generated the env file.