afdbprf.sh fails with ORA-12899: value too large for column

Vickie faced this error while running adconfig on a newly upgraded 10.2.0.4 home:

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/03122349/adconfig.log

In the autoconfig log $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/03122349/adconfig.log shows:

afdbprf.sh started at Thu Mar 12 23:49:43 EDT 2009
Executable : $ORACLE_HOME/bin/sqlplus


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 23:49:44 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : FND_DB_WALLET_DIR
Level Id : 10001
New Value : $ORACLE_HOME/appsutil/wallet
Old Value : $9.2.0_ORACLE_HOME/appsutil/wallet
Updated profile option value - 1 row(s) updated
Application Id : 174
Profile Name : ECX_UTL_XSLT_DIR
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
Updated profile option value - 1 row(s) updated
Application Id : 174
Profile Name : ECX_UTL_LOG_DIR
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : BIS_DEBUG_LOG_DIRECTORY
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
declare
*
ERROR at line 1:
ORA-12899: value too large for column
"APPLSYS"."FND_PROFILE_OPTION_VALUES"."PROFILE_OPTION_VALUE" (actual: 480,
maximum: 240)
ORA-06512: at line 44
ORA-06512: at line 139


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ERRORCODE = 1 ERRORCODE_END
.end std out.

.end err out.
****************************************************
Hope this helps!



Metalink Note 458511.1 gives a solution:

Cause

Script afdbprf.sql called by afdbprf.sh is having the following included which caused the issue:

-- Set up UTL_FILE_LOG profile option
--
set_profile(1, 'UTL_FILE_LOG',
10001, 0,
'%s_db_util_filedir%',
NULL);

As we see in the above code the UTL_FILE_LOG profile option is filled with the value of the s_db_util_filedir

The value of utl_file_dir is over 240 characters , FND_PROFILE_OPTION_VALUES:PROFILE_OPTION_VALUE varchar2(240) is limited to 240 characters.

According to Bug 6404909 the FND_PROFILE_OPTION_VALUES:PROFILE_OPTION_VALUE varchar2(240) can not be (easily) changed because there are many, many locations within C-code user exits and other places in which a hardcoded value of 240 for this column exist.
Solution

To implement the solution, please execute the following steps:

1 - Change the value dbutilfiledir in the database context file ($ORACLE_HOME/appsutil/$CONTEXT_NAME.xml) to a value less the 240 characters

2 - run autoconfig again.

This solved the problem.

Results of dba_registry and utlu102s.sql differ

After a roller coaster upgrade from 9.2.0.8 to 10.2.0.4, I finally achieved this:

select comp_name,version,status
from dba_registry

SQL> /
Oracle Database Catalog Views
10.2.0.4.0 VALID

Oracle Database Packages and Types
10.2.0.4.0 VALID

JServer JAVA Virtual Machine
10.2.0.4.0 VALID

Oracle Database Java Packages
10.2.0.4.0 VALID

Oracle XDK
10.2.0.4.0 VALID

Oracle Text
10.2.0.4.0 VALID

Oracle Real Application Clusters
10.2.0.4.0 INVALID

Spatial
10.2.0.4.0 VALID

Oracle XML Database
10.2.0.4.0 VALID

Oracle interMedia
10.2.0.4.0 VALID


10 rows selected.

However utlu102s.sql still gives Oracle Database Server status as INVALID:

SQL> @utlu102s.sql
.
Oracle Database 10.2 Upgrade Status Utility 03-12-2009 18:21:06
.
Component Status Version HH:MM:SS
Oracle Database Server INVALID 10.2.0.4.0 01:01:55
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:15:19
Oracle XDK VALID 10.2.0.4.0 00:11:22
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:47
Oracle Text VALID 10.2.0.4.0 00:02:42
Oracle XML Database VALID 10.2.0.4.0 00:03:17
Oracle Real Application Clusters INVALID 10.2.0.4.0 00:00:03
Oracle interMedia VALID 10.2.0.4.0 00:06:36
Spatial VALID 10.2.0.4.0 00:06:56
.
Total Upgrade Time: 02:16:51

PL/SQL procedure successfully completed.

Metalink Note 456845.1 describes this issue. However I went a little deeper and figured out a way to correct this:

utlu102s.sql reads results from dba_registry_log. A dbms_metadata query on dba_registry_log shows this:

SQL> select dbms_metadata.get_ddl('VIEW','DBA_REGISTRY_LOG','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY_LOG','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY_LOG" ("OPTIME", "NAMESPACE",


SQL> SET LONG2000
SQL> /

DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY_LOG','SYS')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY_LOG" ("OPTIME", "NAMESPACE",
"COMP_ID", "OPERATION", "MESSAGE") AS
SELECT optime,
namespace, cid,
DECODE(operation, 0, 'INVALID',
1, 'VALID',
2, 'LOADING',
3, 'LOADED',
4, 'UPGRADING',
5, 'UPGRADED',
6, 'DOWNGRADING',
7, 'DOWNGRADED',
8, 'REMOVING',
9, 'OPTION OFF',
10, 'NO SCRIPT',
99, 'REMOVED',
100, 'ERROR',
NULL),
errmsg
FROM registry$log

SQL>

So the view dba_registry_log is actually based on registry$log table.

SQL> desc registry$log
Name Null? Type
----------------------------------------- -------- ----------------------------
CID VARCHAR2(30)
NAMESPACE VARCHAR2(30)
OPERATION NOT NULL NUMBER
OPTIME TIMESTAMP(6)
ERRMSG VARCHAR2(1000)

SQL> select cid,operation from registry$log;
SQL> /
UPGRD_BGN -1
JAVAVM 1
CATPROC 1
RDBMS 1
XML 1
CATJAVA 1
CONTEXT 1
XDB 1
RAC 0
ORDIM 1
SDO 1
UPGRD_END -1
UTLRP_BGN -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1

23 rows selected.

It is clear from above that 1 is the code for VALID and 0 is the code for INVALID.

So I passed this update statement.

SQL> update registry$log
2 set operation=1
3 where cid in ('CATPROC','RDBMS');

2 rows updated.

SQL> commit;

Commit complete.

SQL> @?/rdbms/admin/utlu102s.sql
.
Oracle Database 10.2 Upgrade Status Utility 03-12-2009 18:29:19
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 01:01:55
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:15:19
Oracle XDK VALID 10.2.0.4.0 00:11:22
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:47
Oracle Text VALID 10.2.0.4.0 00:02:42
Oracle XML Database VALID 10.2.0.4.0 00:03:17
Oracle Real Application Clusters INVALID 10.2.0.4.0 00:00:03
Oracle interMedia VALID 10.2.0.4.0 00:06:36
Spatial VALID 10.2.0.4.0 00:06:56
.
Total Upgrade Time: 02:16:51

PL/SQL procedure successfully completed.

SQL>

Metalink down but classic metalink available

New Metalink is down today.  Following message comes:

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, you@your.address and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

However we can access the classic metalink at https://metalink2.oracle.com

Classic Metalink is going to be retired by the end of this year.

Couldn't set locale correctly

Srinivas pinged me about this error which comes when you login as applmgr on a server:

couldn't set locale correctly

I checked the $HOME/.profile file of the user and found an environment variable LANG:

LANG=/usr/lib/locale/en_US.UTF-8
export LANG

On manually setting this variable, we get the same error.

$ LANG=/usr/lib/locale/en_US.UTF-8
couldn't set locale correctly

AventX support had asked the DBAs to set this environment variable, as they were unable to register the license key.

The error was coming because the file en_US.UTF-8 didn't exist:

$ ls -ld /usr/lib/locale/en_US.UTF-8
/usr/lib/locale/en_US.UTF-8: No such file or directory

$ cd /usr/lib/locale
$ ls -ltr
total 52
-rw-r--r-- 1 root bin 1848 Dec 8 2004 lcttab
-rw-r--r-- 1 root bin 270 Dec 8 2004 geo
drwxr-xr-x 8 root bin 512 Mar 26 2008 C
lrwxrwxrwx 1 root root 3 Mar 26 2008 POSIX -> ./C
drwxr-xr-x 3 root bin 512 Mar 26 2008 iso_8859_15
drwxr-xr-x 4 root bin 512 Mar 26 2008 iso_8859_1
drwxr-xr-x 5 root bin 512 Mar 26 2008 common
drwxr-xr-x 3 root bin 512 Mar 26 2008 en.UTF-8
drwxr-xr-x 3 root bin 512 Mar 26 2008 en_CA
drwxr-xr-x 9 root bin 512 Mar 26 2008 en_CA.ISO8859-1
drwxr-xr-x 10 root bin 512 Mar 26 2008 en_CA.UTF-8
drwxr-xr-x 3 root bin 512 Mar 26 2008 en_US
drwxr-xr-x 9 root bin 512 Mar 26 2008 en_US.ISO8859-1
drwxr-xr-x 9 root bin 512 Mar 26 2008 en_US.ISO8859-15
drwxr-xr-x 3 root bin 512 Mar 26 2008 en_US.ISO8859-15@euro
drwxr-xr-x 3 root bin 512 Mar 26 2008 es
drwxr-xr-x 3 root root 512 Mar 26 2008 es.UTF-8
drwxr-xr-x 3 root bin 512 Mar 26 2008 es_MX
drwxr-xr-x 8 root bin 512 Mar 26 2008 es_MX.ISO8859-1
drwxr-xr-x 9 root bin 512 Mar 26 2008 es_MX.UTF-8
drwxr-xr-x 3 root bin 512 Mar 26 2008 fr
drwxr-xr-x 3 root bin 512 Mar 26 2008 fr.UTF-8
drwxr-xr-x 3 root bin 512 Mar 26 2008 fr_CA
drwxr-xr-x 8 root bin 512 Mar 26 2008 fr_CA.ISO8859-1
drwxr-xr-x 9 root bin 512 Mar 26 2008 fr_CA.UTF-8

I'll update this post once I learn more.

Relaying denied revisited

Recently I was called about complaints of workflow mailer failing to send outgoing mails, after the database server failed over. We use the sendmail daemon of the database server as the mail server. On checking the /etc/hosts file of the server, I found that it did not have the entries for the application tier servers. Sendmail always checks if a host which is trying to send mail is present in the mail server's /etc/hosts file. After adding the servers, relaying denied was resloved.

As discussed in a previous post, Sendmail does these checks:

1. Checks whether the host trying to send mail is in /etc/hosts
2. Does a reverse DNS lookup on the IP of the host trying to send mail, to see if the name is same as that reported by the host. For example if the host reports its name as appserver.justanexample.com (based on /etc/hosts of mail server), but a reverse DNS lookup shows that the name of the server is appserver.dev.justanexample.com, then Sendmail will reject it with Relay Denied error

CLASSPATH and AF_CLASSPATH after JDK 6

If you are on JDK 5 or 6, to avoid some weird problems in Oracle Apps, ensure that your CLASSPATH and AF_CLASSPATH includes the necessary JDK 6 libraries:

[JDK60_TOP]/lib/dt.jar,
[JDK60_TOP]/lib/tools.jar,
[JDK60_TOP]/jre/lib/rt.jar, and
[JDK60_TOP]/jre/lib/charsets.jar

where JDK60_TOP is $COMMON_TOP/util/jdk1.6.0_xx

Metalink Note 184714.1 has some pointers for troubleshooting CLASSPATH issues.