Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Saturday, December 29, 2007

How to find out what patch/patchset to be applied ?

I replied one of the most commonly asked questions in Oracle Apps Upgrade Metalink forum:

From: Teri Erck 28-Dec-07 21:22
Subject: How to find out what patch/patch set should I apply

How to find out what patch/patch set should I apply

Hi,

We use public sector AP, GL, PO. Our system is updated to 11.5.10.2. We have not applied any functional patch for a long time. Following is the current functional level on each module:

11i.GL.G; 11i.PO.I; 11i.AP.M

My question is HOW TO FIND OUT WHICH FUNCTIONAL PATCH WE NEED TO REVIEW AND EVALUATE?

Thank you for your help!!

From: Vikram Das 30-Dec-07 05:11
Subject: Re : How to find out what patch/patch set should I apply

Hi,

Follow these steps:

Login to metalink
Click on patches and updates tab
On the line which reads "eBusiness Suite Recommended Patch List for Release 11i and R12" click 11i
Select the following
Maintenance Release: 11.5.10
Consolidated Update (CU): CU2
Click on the check box of Financials and select the value given below:
Financials : FIN_PF.F

Click Continue button.

You should now get the Recommended Patch List for Financials modules.

- Vikram
http://oracleappstechnology.blogspot.com

From: Vikram Das 30-Dec-07 05:21
Subject: Re : How to find out what patch/patch set should I apply

The last Financials Family pack was FIN_PF.G (patch 3653484). You should plan on applying that if you haven't applied it already. In addition you should apply AP.O (patch 4551936). In addition to getting the recommended patch list, you should also run the patchsets.sh script which can be downloaded from: ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

This script is updated every night and should give you a very good idea about what patchset is current for each of your products.

- Vikram
http://oracleappstechnology.blogspot.com

Prevent the failure of worker running apfiawie.wft during 11.5.10.2 maintenance pack

While applying 11.5.10.2 maintenance pack, you can prevent the worker running apfiawie.wft from failing if you modify the driver file as per metalink note 423145.1:

This error can occur on ANY workflow patch that upgrades the apfiewie.wft file if the customer has lowered the access level of the existing workflow file because they have made slight customizations to the workflow and they do no want these changes to be overwritten.

However, in order to upgrade the workflow and get the patch to apply sucessfully it is necessary to first edit the Udriver, in this case U5948386.drv, and change the WFLOAD command from UPGRADE mode to FORCE mode

1) Locate the following command in the driver file:

exec fnd bin WFLOAD bin &phase=daa+52 checkfile:ap:patch/115/import/US:apfiawie.wft &ui_apps 0 Y UPGRADE @SQLAP:patch/115/import/US/apfiawie.wft013
and change this to:

exec fnd bin WFLOAD bin &phase=daa+52 checkfile:ap:patch/115/import/US:apfiawie.wft &ui_apps 0 Y FORCE @SQLAP:patch/115/import/US/apfiawie.wft013

2) Save the changes

Friday, December 28, 2007

Adventures with PL/SQL native compilation settings

Recently we enabled PL/SQL native compilation in an upgraded 11.5.10.2/10.2.0.3 instance. Some of the packages were not compiling and giving these errors:

SQL> show err;
Errors for PACKAGE BODY EXAMPLE_NATIVE_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00923: native compilation failed: C compilation or linking
failed:spdzj.c:Could not invoke program
/opt/SunProd/SUNWspro8/bin/cc:No such file or directory

To solve this error you will have to configure gcc as the primary compiler:

Login as root user
cd /opt
find . -name cc
If the find command returns the path of cc, copy it

Check if primary compiler exists:

ls -ltr /opt/SunProd/SUNWspro8/bin/cc.

If the above file doesn't exist, that means primary compiler is missing. As an alternative, we have to now configure gcc as the primary compiler. Login as oracle user. Change directory to your 10.2.0 ORACLE_HOME

cd $ORACLE_HOME/plsql
vi spnc_commands

Comment line 21 in the file:
#/opt/SunProd/SUNWspro8/bin/cc %(src) -xO1 -Xa -xstrconst -dalign -xF -mr -xildoff -errtags=yes -xarch=v9 -xchip=ultra3 -W2,-AKNR_S -Wd,-xsafe=unboundsym -Wc,-Qiselect-funcalign=32 -xcode=abs44 -Wc,-Qgsched-trace_late=1 -Wc,-Qgsched-T5 -KPIC -I$(ORACLE_HOME)/plsql/include -I$(ORACLE_HOME)/plsql/public -G -o %(so)

Uncomment line 27:
/usr/local/bin/gcc -B/usr/ccs/bin %(src) -c -fPIC -mhard-float -mhard-quad-float -mcpu=v9 -DSLU8NATIVE -DSLS8NATIVE-D_REENTRANT -DSOLARIS -DSLMXMX_ENABLE -DSLTS_ENABLE -D_SVID_GETTOD -DEO_SUPPRESS_DEBUG -DEO_SUPPRESS_TRACE -O1 -m64 -I$(ORACLE_HOME)/plsql/include -I$(ORACLE_HOME)/plsql/public -o %(obj)

In line 27, the last few characters are (obj) change this to (so). Line 27 should read:
/usr/local/bin/gcc -B/usr/ccs/bin %(src) -c -fPIC -mhard-float -mhard-quad-float -mcpu=v9 -DSLU8NATIVE -DSLS8NATIVE-D_REENTRANT -DSOLARIS -DSLMXMX_ENABLE -DSLTS_ENABLE -D_SVID_GETTOD -DEO_SUPPRESS_DEBUG -DEO_SUPPRESS_TRACE -O1 -m64 -I$(ORACLE_HOME)/plsql/include -I$(ORACLE_HOME)/plsql/public -o %(so)

Unless you do these steps, since plsql native compilation is enabled, you'll get this kind of errors while compiling packages/procedures:

SQL> alter package EXAMPLE_NATIVE_PKG compile body;
Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY EXAMPLE_NATIVE_PKG
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00923: native compilation failed: C compilation or linking
failed:spdzj.c:Could not invoke program
/opt/SunProd/SUNWspro8/bin/cc:No such file or directory

If you do not change (obj) with (so) as per metalink note 429524.1, you'll get ORA-600 :

alter package EXAMPLE_NATIVE_PKG compile
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

In Alert log:
ORA-00600: internal error code, arguments: [skkxerrp], [skkxdllwri], [SlfFopen], [$ORACLE_HOME/plsql_nativelib/EXAMPLE_NATIVE_PACKAGE__B__1824152.so], [No such file or directory], [], [], []

Changes in some init.ora parameter meanings in 10g

SHARED_POOL_SIZE
In previous releases, the amount of shared pool memory that was allocated was equal to the value of the SHARED_POOL_SIZE initialization parameter plus the amount of internal SGA overhead computed during instance startup. Starting with Oracle Database 10g release 10.1, the value of SHARED_POOL_SIZE must now also accommodate this shared pool overhead.

SESSION_CACHED_CURSORS
In previous Oracle Database releases, the number of SQL cursors cached by PL/SQL was determined by the OPEN_CURSORS initialization parameter. Starting with Oracle Database 10g release 10.1, the number of cached cursors is determined by the SESSION_CACHED_CURSORS initialization parameter.

LOG_ARCHIVE_FORMAT
Starting with Oracle Database 10g release 10.1, if the COMPATIBLE initialization parameter is set to 10.0.0 or higher, then archive log file names must contain each of the elements %s (sequence), %t (thread), and %r (resetlogs ID) to ensure that all archive log file names are unique. If the LOG_ARCHIVE_FORMAT initialization parameter is set in the parameter file, then make sure the parameter value contains the %s, %t, and %r elements.

PGA_AGGREGATE_TARGET
Starting with Oracle Database 10g release 10.1, Automatic PGA Memory Management is now enabled by default (unless PGA_AGGREGATE_TARGET is explicitly set to 0 or WORKAREA_SIZE_POLICY is explicitly set to MANUAL). PGA_AGGREGATE_TARGET defaults to 20% of the size of the SGA, unless explicitly set. Oracle recommends tuning the value of PGA_AGGREGATE_TARGET after upgrading.
Until 9iR2, PGA_AGGREGATE_TARGET parameter controls the sizing of workareas for all dedicated server connections, but it has no effect on shared server (aka MTS) connections and the *_AREA_SIZE parameters will take precedence in this case. In 10g, PGA_AGGREGATE_TARGET controls workareas allocated by both dedicated and shared connections.

QUERY_REWRITE_ENABLED
The default value of the initialization parameter QUERY_REWRITE_ENABLED has changed. By default it is TRUE in 10.1 and above. Prior to 10.1 the default is FALSE.

REMOTE_LOGIN_PASSWORDFILE
There are multiple modes in which a remote_login_passwordfile can be set to. The different modes are SHARED, EXCLUSIVE and NONE. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified which means that one cannot add users to a SHARED password file.
Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error.
In 9.2 the default value of REMOTE_LOGIN_PASSWORDFILE is EXCLUSIVE , the same is set to SHARED by default in 10g.

FILESYSTEMIO_OPTIONS
This parameter was an under score parameter _FILESYSTEMIO_OPTIONS Prior to 9.2. Starting 9.2 it is not an under score parameter. The parameter is FILESYSTEMIO_OPTIONS. The value may be any of the following:
- asynch - This allows asynchronous IO to be used where supported by the OS.
- directIO - This allows directIO to be used where supported by the OS. Direct IO bypasses any Unix buffer cache. As of 10.2 most platforms will try to use "directio" option for NFS mounted disks (and will also check NFS attributes are sensible).
- setall - This enables both ASYNC and DIRECT IO.
- none - This disables ASYNC IO and DIRECT IO so that Oracle uses normal synchronous writes, without any DIRECT IO options.

ON Solaris and 10.2 the default is asynch.

This parameter is very important for I/O Performance. The value of FILESYSTEMIO_OPTIONS is set depending upon the design of underlying storage and OS. If the underlying storage or filesystem is designed for asynchronous IO then the value needs to be set asynch. If the underlying storage or filesystem is designed for directio then the value needs to set to "directIO".

PARALLEL_ADAPTIVE_MULTI_USER
In 9i default value of parallel_adaptive_multi_user is derived from parallel_automatic_tuning which defaults to false. If parallel_automatic_tuning is true Oracle will set the value of parallel_adaptive_multi_user parameter to true. If parallel_automatic_tuning is false, parallel_adaptive_multi_user will be false.
In 10g default value of parallel_adaptive_multi_user is true and parallel_automatic_tuning has been deprecated in 10g.


PARALLEL_MAX_SERVERS
The Default value of PARALLEL_MAX_SERVERS has changed in 10g. 10g default is derived using the following formula:
CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5
In 9.2 If PARALLEL_AUTOMATIC_TUNING is false, the default value of PARALLEL_MAX_SERVERS is 5. If PARALLEL_AUTOMATIC_TUNING is TRUE, the default value of PARALLEL_MAX_SERVERS is CPU x 10.

SKIP_UNUSABLE_INDEXES
The default value of SKIP_UNUSABLE_INDEXES is TRUE IN 10g.
SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with unusable indexes or index partitions. In earlier releases prior to 10.1, SKIP_UNUSABLE_INDEXES was a session parameter only. In Oracle Database 10g release 10.1 and later, it is now an initialization parameter and defaults to true. The true setting disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions.

Source: Oracle Database Upgrade advisor

aiprfp: product 'izu' not in release 'R115'

While applying patch 3180164 which is a pre-req for maintenance pack 3480000 to upgrade an 11.5.9 instance to 11.5.10.2, you'll get this error:

ERROR

Connecting to APPS......Connected successfully.

AutoPatch error:
aiprfp: product 'xxz' not in release 'R115'

Error running SQL and EXEC commands in parallel

You should check the file
//admin//log/adpatch.log


A search on metalink will return two notes 457886.1 and 334445.1. Note 457886.1 recommends modifying the $APPL_TOP/admin/topfile.txt and removing the line which is there for izu. However note 334445.1 advises applying patch 3865683 (RELEASE 11.5.10 PRODUCTS NAME PATCH) as a solution.

This error occurs because IZU or Oracle Diagnostics was a product which was introduced much after 11.5.10.2 maintenance pack was released. So if you have a pre-11.5.10 instance on which you have installed Oracle Diagnostics, you will get this error. This is because 11.5.10 or its pre-reqs are unaware of this product. So logically speaking, the products name patch 3865683 should solve this by updating the relevant drivers so that 11.5.10.2 and its pre-reqs become aware of IZU. If even after applying the product names patch, the error occurs, then you have no option but to remove IZU from topfile.txt and apply the patch, after which you can restore the original topfile.txt

Thursday, December 20, 2007

Startup DB in restrict mode before running adgrants.sql

adgrants.sql is a script which is usually run after every AD.I patch or ATG_PF.H Rollup. From prior experience I have seen this script hang for a long time whenever it is executed on a running Apps 11i instance. You should shutdown all service first. Shutdown database. Startup database in restrict mode and then run adgrants.sql. In RESTRICT mode, it executes quickly.

To Enable Restricted Session

Alter system enable restricted session;

To Disable Restricted Session

Alter system disable restricted session;

To Start the Database in Restricted Mode

STARTUP RESTRICT

Wednesday, December 19, 2007

paupg103.sql waits in adworker log Enter value for 1

While applying a merged patch which contains patches Projects Family Pack M(PJ_PF.M) and PJ_PF.M Rollup 2, PJ_PF.M Rollup 3 and PJ_PF.M Rollup 4(5644830) one adworker showed this message in logs:

Time when worker started job: Thu Oct 11 2007 15:34:58

Start time for file is: Thu Oct 11 2007 15:34:58

sqlplus -s APPS/***** @$PA_TOP/11.5.0/patch/115/sql/paupg103.sql
Enter value for 1:

Trying to run this sql manually also gave the same result. However this sql was run correctly by other workers:

adwork001.log:sqlplus -s APPS/***** @$PA_TOP/patch/115/sql/paupg103.sql &un_pa &batchsize 10 12

We tried restarting the worker with no success. So we skipped it and the merged patch completed successfully. I logged an SR to find out why this was happening and how we can avoid this in the next iteration. Oracle Development replied:

If the following SQL

select count(*)
from pa_proj_fp_options pfo,
pa_fin_plan_types_b fino,
pa_fin_plan_types_b finwp
where NVL(pfo.fin_plan_type_id,-99)
NOT IN (fino.fin_plan_type_id,finwp.fin_plan_type_id)
and pfo.fin_plan_type_id is not null
and pfo.use_planning_rates_flag is null
and fino.fin_plan_type_code = 'ORG_FORECAST'
and finwp.use_for_workplan_flag='Y';
.
returns count>0 then it means that there are some rows which are not yet upgraded by paupg103.sql

In that case the script paupg103.sql uploaded to bugftp can be given to the customer and it can be run at SQL prompt. This does not require any parameters. After the script is executed, the above SQL should return 0 records

As to why this occurred, this is what they said:

parrallel processing feature is added to paupg103.sql in PJ_PF.M RUP4 and with this the udriver command of the ARU patch is changed. The merged patch contains RUP4 patch as well as other patches where the parallel processing feature is not added to paupg103.sql. Hence the merged patch contains 2 different uDriver commands for the same file and it would have resulted in the above said error. Not merging PJ_PF.M RUP4 patch with other patches is a solution for this.

I decided to test this, by removing the PJ_PF.M RUP4 patch from the merged patch and remerge it. But the error still occurred after removing PJ_PF.M RUP4 patch. I have now removed PJ_PF.M RUP2 and PJ_PF.M RUP3 from the merged patch as they are already included in RUP4, which we are planning to apply in isolation as per Development's previous advice. I removed the base PJ_PF.M patch and the error stopped. However, I was not convinced as I did all this testing without refreshing the instance. So I did a fresh install and applied the merged patch which had the base PJ_PF.M patch. No errors. If I apply PJ_PF.M RUP4 patch separately, no errors. So development was right. I have asked them to fix this in RUP5 but they are non-commital with only the promise of documenting this behavior in a new metalink note. The created a note 555001.1, but with incorrect information. Instead of mentioning PJ_PF.M RUP4 patch 5150810, they have mentioned Projects Family Pack M 3485155 as the patch which should not be merged with other patches. I have asked them to correct it, but they haven't done so yet.

Since it is very important for us to minimize downtime during patching, I am proceeding ahead with another plan. I have asked Oracle whether we can follow this plan:


1. We merge PJ_PF.M RUP4 5150810 with merged patch
2. We comment out paupg103.sql from the u driver of the merged patch
3. We run paupg103.sql manually after the completion of merged patch.

Peculiar problem of Data Mining and OLAP status in dba_registry

As part of the 10g upgrade, we have the step of installing OLAP and Data Mining. For Data Mining following SQL is run:

@$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP;

For OLAP, the following SQL is run:

@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;

Whenever these two sqls were run one after the other, we would end of with OLAP and Data Mining invalids with the status of Data Mining showing up as INVALID and OLAP showing up as LOADING.

To investigate, we checked the contents of dminst.sql:

@@odmcrt.sql &&1 &&2

execute sys.dbms_registry.loading('ODM','Oracle Data Mining','validate_odm','DMS
YS',NULL,NULL);
==========================================
@@odmproc.sql

alter session set current_schema = "DMSYS";

@@catodm.sql

execute sys.dbms_registry.loaded('ODM');

execute sys.validate_odm;
===========================================

Notice the line in bold. It is setting the current schema to DMSYS. That means if you run olap.sql without disconnecting your session from sqlplus, the sql statements in olap.sql are going to run on DMSYS schema, thus invalidating DMSYS as well as OLAP. So the way out is to do this:

sqlplus /nolog
connect / as sysdba
@$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP;
exit

In the above line you may exit sqlplus or reconnect by giving command: connect / as sysdba thus disconnecting the existing session.

sqlplus /nolog
connect / as sysdba
@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;

Mystery of missing parameters in ASCP concurrent program Planning Data Pull

We have planned multiple iterations of 11.5.9 /9.2.0.8 to 11.5.10.2/10.2.0.3 upgrade, before going live. Our second iteration began recently. One of the users remarked that there was a difference in the parameters of the ASCP concurrent program: Planning Data Pull. The old instance had 3 extra parameters: Sales Channel, Fiscal Calendar, Payback Demand/Supply. The new instance did not have these 3 parameters. We were on Collections Rollup #21 in old instance. In the new instance we had applied Collections Rollup #22 patch.

I followed Metalink Note 179522.1 which says:

The recommended first steps are:
1. Install the latest Data Collections patch as noted in Note 223026.1 – List of High Priority Patches for the APSNote 223026.1 – List of High Priority Patches for the APS Suite

Note 223026.1 further says:

IV. Data Collections for ATP and all the APS Applications (MSC)

11.5.10 or 11iSCM_PF.J - only for this release

Collections Cumulative Patch #23 - 6447137 - Released 07-Dec-2007

OPM Customers - Please open an SR with the product Oracle Process Manufacturing Process Planning (Code 737) and they can provide the latest OPM Integration patch numbers for the OPM ASCP Planning UI rollup patch and OPM Data Collections rollup patch

Readme of patch 6447137 says:

USER EXPERIENCE changes on applying this patch on site at level rup16 or below:
-------------------------------------------------------------------------------
A) New profile 'Refresh Site to Region Mappings Event' is introduced by bugfix 5717270.
B) Following 3 new entities will be seen on the collections parameters screen. Their default values are 'no'(old behavior):
#1)Sales Channels
#2)Fiscal Calendars
#3)Payback Supply/Demand
#1,#2 need to be collected only by users using product 'Demantra' also. It is recommended that customers don't collect #3 until they uptake the engine 11510 rup#23 ARU that instructs collection of this entity for a new Borrow/payback feature.

I applied the patch on both Source and Destination instances, as instructed in patch readme. However the 3 new parameters refused to appear.

I followed bug # 4877132 for this issue which advises running this command:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $MSC_TOP/patch/115/import/US/mscprg.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

After running the above command, the 3 missing parameters appeared.

This also created 2600+ invalid objects. So I ran utlrp.sql which fixed all of them.

SQL*Plus COPY command

Two days back we were investigating a discrepancy in parameters of ASCP Planning Data Pull concurrent program. The instance with Collection rollup 22 had 3 extra parameters and the instance with rollup 23 did not have these. We decided to compare the ad_bugs table of the two instances. There were more than 200,000 rows in ad_bugs. I thought of the SQL*Plus COPY command. Using this command I was able to copy the more than 200,000 rows in less than 5 seconds. Here's the command:

COPY FROM SCOTT/TIGER@LOCAL_DB TO SCOTT/TIGER@REMOTE_DB -
CREATE EMP_COPY USING -
SELECT * -
FROM EMP;

The hyphens at the end of each line are needed to let sql*plus know that the command is not yet complete.

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. If you type copy on sql prompt without any parameters, it will give you the syntax.

Thursday, December 13, 2007

Pre-requisites prompted by patch 3480000

Since AD.I, adpatch doesn't check for pre-requisites of the patch being applied, unless you give the argument options=prereq.

When we applied the maintenance pack 3480000 with

adpatch options=prereq

It came out saying that these 3 patches needed to be applied on our instance:

4665900 (OM Prerequisite Patch for 11510 customers)
4244449 (CST_NO_CG_ACCTS ERROR WHEN TXN COST)
4946700 (ITS concurrent process errors out transferring)

The interesting part is that if you go through the readme of the above patches, all of them require you to be on 11.5.10, that means, the pre-req of these patches is the maintenance pack patch 3480000. This creates a loop. Pre-req of 3480000 are 4665900, 4244449 and 4946700, but pre-req of these 3 patches is patch 3480000. What's the way out. Simple, merge these patches with 3480000.

Metalink Note 305920.1, also recommends applying 4199081 if Oracle Time and Labor is being used. As per this note, if the query below returns any rows, the creation of index HXC_APPROVAL_STYLES_UK1 will fail during the maintenance pack even if you are not using Oracle Time and Labor:

SELECT NAME, BUSINESS_GROUP_ID, count(*)
FROM HXC.HXC_APPROVAL_STYLES
GROUP BY NAME, BUSINESS_GROUP_ID
HAVING count(*)>1;

It would be a good idea to run this query in advance and check for any duplicate data.

If you find any duplicate data, you should apply the patch 4199081, before applying the maintenance pack patch 3480000, so that the duplicate data is fixed. This way the creation of index will not fail during maintenance pack.

11.5.10.2 MP Mandatory co-requisite patch for Service module

If you are planning to upgrade from a pre-11.5.10 version to 11.5.10, and are using Service module, there is a mandatory patch 5204153 which needs to be merged with the 11.5.10 maintenance pack patch 3480000. This came to light, when our team was checking all the post 11.5.10 functional patches. Readme of this patch says: "Customers who are upgrading to 11.5.10 from prior releases should merge this patch with 11.5.10 before applying 11.5.10 maintenance pack to avoid data corruptions". Since we missed this patch in our first iteration, I had a long SR with Oracle to find out why we can't apply this after applying the maintenance pack. Some excerpts from the SR:

Question: If this patch is not applied does it cause MP/CU2 to fail ?
Answer: No, there won't be any build failures.
.
Question: Can customer handle this post 11510.2 upgrade either thr' a documented post step in 11510.2 or thr' RPL.
Answer: No. This needs to be merged with the 11i10/Cu1/Cu2 patches. The script in question populates a value in one of the columns if the column value is null. CU2 version populates a wrong value.

This patch is a mandatory pre-req for all Service customers using Charges functionality. Not required for Service customers if they do not use Charges module. In that sense it is conditional.

Let me know if you have any additional questions.

1. Question: In case someone does a fresh install from CDs, how is that person supposed to know about this patch. There is no mention of this patch in the 11.5.10.2 installation guide or the maintenance pack readme.

Answer:
There is currently a documentation Bug logged to update the README of all 11.5.10 patches to inclulde Patch 5204153 as a pre-req.

Bug 5259247, entitled, 3480000 SHOULD HAVE Patch 5204153 ADDED AS A PREREQUISITE contains the following:
"3480000 11.5.10.CU.2 should have Patch 5204153 added as a prereq.
.
5204153 has been added to the Recommended Patch list.
What appears to happen is that an upgrade script, csxchest.sql which is contained in 3480000 has a major flaw. They have issued a new Patch 5204153 as the repair to this patch.
With instructions to merge this new patch with the 3480000. We feel that this patch should in that case be included as a prerequisite for Patch 3480000. Please see the readme instructions at the bottom on this page that they have included on Patch 5204153.
.
"This patch (5204153)needs to be downloaded prior to applying any one of the following patches:
.
a. Patch 11i.SCM_PF.J(3384350)
b. Oracle Applications Release 11.5.10 (3140000)
c. 11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1 (3240000)
d. 11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2 (3460000)
e. ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK (3480000)
.
After downloading this patch please run the admrgpch utility to merge the current Standalone patch driver(s) and the Maintenance Pack driver(s)."

2. Question: Also if a client wants to implement service module later, how is he supposed to apply this patch.
Answer: As far as I understand this Patch is specific to making sure data from pre-11.5.10 is migrated correctly. Therefore, a fresh install or new data will not be impacted if this patch is not applied.

3. Question: What is the problem if this patch is not applied ? Do we lose some functionality?
Answer:
Patch 5204153 includes the following 8 files:
cs patch/115/sql cs00aaa.sql 115.6.11510.2 +
cs patch/115/sql cs00aau.sql 115.5.11510.2 +
cs patch/115/sql cs00sca.sql 115.6.11510.2 +
cs patch/115/sql cs00sia.sql 115.6.11510.2 +
cs patch/115/sql cs00snc.sql 115.5.11510.2 +
cs patch/115/sql cs00ssr.sql 115.8.11510.2 +
cs patch/115/sql cs22gchu.sql 115.8.11510.2 +
cs patch/115/sql csxchest.sql 115.0.11510.2 +

The first 7 files are specific to upgrading pre-11i data.
csxchest.sql contains the following:

REM | FILENAME |
REM | csxchorg.sql |
REM | |
REM | DESCRIPTION |
REM | Populate new org_id column |
REM | |
REM | This script populates the new org_id column on the Charges |
REM | table CS_ESTIMATE_DETAILS. The value is retrieved from the |
REM | parent Service Request. |
REM | The following new columns were added for 11.5.9 upgrade. |
REM | Transaction_Inventory_Org: |
REM | The ware house specified in OM order headers table. |
REM | Charge_Line_Type: |
REM | Lookup code which indicates type of charge line. |
REM | A value of 'ACTUAL' populated for every charge line. |
REM | Ship_To_Contact_Id: |
REM | Ship to contact for specified ship to party site |
REM | from OE Lines. |
REM | Bill_To_Contact_Id: |
REM | Invoice to contact for specified bill to party site |
REM | from OE Lines. |
REM | Generated_By_Bca_Engine_Flag: |
REM | Default will be 'N' for every Charge Line. |
REM | Bill_To_Party_Id: |
REM | The party id for the specified bill to org id in Charges form. |
REM | Ship_To_Party_Id: |
REM | The party id for the specified ship to org id in Charges form. |
REM | List_Price: |
REM | This is the selling price of the charge line item. |
REM | Line_Submitted: |
REM | Values are 'N' and Null. A value of 'N' is populated for all ACTUAL|
REM | Charge Lines which are NOT submitted to OM,all others will be |
REM | populated with NULL. |
REM | This column is only to improve performance in the context of ACTUAL|
REM | lines, and do not have any functional significance. |
REM | A NULL does not necessarily indicate Line was submitted to OM. |
REM | |
REM | |
REM | sql abc patch/115/sql csxchorg.sql none none |
REM | none sqlplus phase=upg batchsize |



My questions for you:
A. Are you doing a fresh install of 11.5.10 or are you upgrading?
B. If you are upgrading, what version are you upgrading from?

When I hear back from you I will also post a question to development requesting an update
on the ER and also to confirm if the patch is required if doing a Fresh Install
vs. an Upgrade. As far as I can see the Patch is specific to making sure data
is correct when doing an UPGRADE from pre 11.5.10 and not a fresh install.

Thanks,
Adriana

We are doing an upgrade from 11.5.9 -> 11.5.10.2

I posted to development the issue you are encountering in addition to the following questions:
"1. For an upgrade from 11.5.9 -> 11.5.10, should this patch have been applied before the upgrade?
2. If so, what can the customer do to fix the incorrect data?
3. Does this patch need to be applied for a Fresh Install (looking at the 8 files included in the patch, it doesn't look like it, but I would like to have confirmation)?"

Development responded with the following:
1. Yes.
2.Its very dificult to guess as they have missed a vital step. At this point we suggest them to take a backup of all tables touched by all these script and run these in a test instance.
3. Even for a fresh instance if these files are run should not cause any issue

As per mising this mandatory steps I would suggest to take a backup and try out all these script one at a time. Also try to see what each script is doing and in case of doubt consult development."

Therefore, can you try the suggestion made by development.
Please update the Tar if you encounter any problems so I can forward this to them.
Let me know if you have any questions.

Hi Adriana,

Isn't there any conceivable way to mitigate this, if the 5204153 patch is missed during upgrade ? Can't we have a data fix for the data which is not migrated properly from 11.5.9 to 11.5.10 ?

- Vikram

Hi Vikram,

I will ask development to see if they are able to provide a data fix, if so, I will then proceed to log a bug. I'll let you know what I hear back.

Regards,
Adriana

Hi Vikram,

Development made the following update with respect to this request:
"This patch is very critical and that is the reason we have put that on recommended patch list. If they missed this patch it will be very difficult to help them out here as the 115102 process will update the data here as mentioned in the bug 5259247 . Are they already in 115102 production ? Redoing 1159 to 11510 upgrade in the correct way is only I can suggest here. Figuring out what makes and what breaks will be extremely difficult here."

Therefore, it looks like there is no alternative if the patch was missed other than redoing the up
grade. Let me know if you have any other questions.

Thanks,
Adriana

QUESTION
========
The Read me for Patch 5204153 says: "Customers who are upgrading to 11.5.10 from prior releases should merge this patch with 11.5.10 before applying 11.5.10 maintenance pack
to avoid data corruptions".
Can this patch be applied after applying the maintenance pack or is there any alternative available?

ANSWER
======
The Patch cannot be applied after the upgrade nor are their any alternative fixes available if
the Patch was not applied before the upgrade.

The reason is that this Patch is responsible for specific data updates and if not done, the fixes for this data and any other data that it can be affect cannot be determined.

If the patch was not applied there is no alternative other than re-doing the upgrade and applying the patch when required.

Tuesday, December 11, 2007

Drop invalid FPA objects after 11.5.10 upgrade if you are not using Project Portfolio Analysis

After applying maintenance pack patch 3480000 to an 11.5.9 instance, we had several invalid FPA views. Since Project Portfolio Analysis(FPA) is a new product introduced in 11.5.10 and we are not using it currently, as advised in metalink note 400964.1, we dropped the invalid FPA views.

After applying PJ_PF.M RUP4 patch 5644830, the following FPA objects are invalid:

SQL> select object_type,object_name from dba_objects
where object_name like 'FPA%'
and owner='APPS'
and status='INVALID' 2 3 4
5 /

OBJECT_TYPE OBJECT_NAME
-----------------------------------------
PACKAGE BODY FPA_MAIN_PROCESS_PVT
PACKAGE BODY FPA_PLANNINGCYCLE_PVT
PACKAGE FPA_PORTFOLIO_PROJECT_SETS_PVT
PACKAGE BODY FPA_PORTFOLIO_PROJECT_SETS_PVT
PACKAGE BODY FPA_PORTFOLIO_PVT
PACKAGE BODY FPA_PROCESS_PVT
PACKAGE BODY FPA_PROJECT_PVT
PACKAGE BODY FPA_SCENARIO_PVT
PACKAGE BODY FPA_SCORECARDS_PVT
PACKAGE BODY FPA_UTILS_PVT
PACKAGE BODY FPA_VALIDATION_PROCESS_PVT

11 rows selected.

I logged an SR with Oracle to find out how to fix them. This is what they said:

ACTION PLAN
============
Hi Vikram,
Are you using Oracle Project Portfollio Analysis? If not, then you can ignore the or drop the invalid objects (see Note 436956.1).

Thanks,
Glade


Hi Glade,

We are not using Project Portfolio Analysis. We are only interested in getting the invalids fixed. If we can drop them, it would bring down the invalid count and meet our requirement of having
zero invalids after this upgrade. However I am unable to access metalink note 436956.1. Maybe this is not yet released to general public. Please paste the contents of note 436956.1 for our reference.

- Vikram


11-DEC-07 21:29:36 GMT


UPDATE
======
HI Vikram,
Here is the Note:

Article-ID: Note 436956.1
Title: FPA Invalid Objects After Applying Projects RUP4 Patch 5644830

Applies to:
Oracle Project Portfolio Analysis - Version: 11.5.10.2
Information in this document applies to any platform.

Goal

After applying Oracle Projects Family Pack M (11i.PJ_PF.M) Rollup 4 have three following FPA invalid objects :


Package Body FPA_MAIN_PROCESS_PVT
Package FPA_SCENARIO_PVT
Package Body FPA_SCENARIO_PVT FPA

These packages did not exist prior to applying these patches
Do not have Portfolio Analysis installed

Could these FPA invalid objects be dropped?

Solution
The FPA Invalid Objects can be Ignored or Dropped if Portfolio Analysis Product is not used.

Thanks,
Glade


11-DEC-07 21:29:47 GMT

Hi Glade,

The Note refers to only 3 objects:

Package Body FPA_MAIN_PROCESS_PVT
Package FPA_SCENARIO_PVT
Package Body FPA_SCENARIO_PVT FPA

But we have 11 invalids, is it ok to drop all 11 ?

- Vikram

ERROR: APPLYING PORDLVMR.XML TO THE WRONG RELEASE

While upgrading 11.5.9 to 11.5.10.2 in one of our environments, we got these errors:

Error: Applying PORDLVMR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVMR.xml,
v115.20
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVMR.xml, v1.1

Error: Applying PORDLVSR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVSR.xml,
v115.34
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVSR.xml, v1.1

Error: Applying PORSPAMT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPAMT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPAMT.xml, v1.1

Error: Applying PORSPGDS.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPGDS.xml,
v115.16
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPGDS.xml, v1.1

Error: Applying PORSPRAT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPRAT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPRAT.xml, v1.1

### Steps to Reproduce ###
Apply patch 3480000 on an 11.5.9 CU2 instance, the following error results:

Error: Applying PORDLVMR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVMR.xml,
v115.20
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVMR.xml, v1.1

Error: Applying PORDLVSR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVSR.xml,
v115.34
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVSR.xml, v1.1

Error: Applying PORSPAMT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPAMT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPAMT.xml, v1.1

Error: Applying PORSPGDS.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPGDS.xml,
v115.16
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPGDS.xml, v1.1

Error: Applying PORSPRAT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPRAT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPRAT.xml, v1.1

I raised an SR with Oracle to find out what needs to be done. This is what they said:

The XML files referenced in the errors are obsoleted in 11.5.10 / Supply Chain Management Family Pack J and can be ignored.

PORDLVMR.xml
PORDLVSR.xml
PORSPAMT.xml
PORSPGDS.xml
PORSPRAT.xml

These files are related to iProcurement requisition creation process in 11.5.9 and lower; these have been replaced by OA Framework pages in 11.5.10 code level.

Thanks,
Anthony
Global Customer Services


Hi Anthony,

Does that mean that we are already on the most current version and don't need to copy the files manually from patch 3480000 ? The versions are strange (115.34 vs 1.1) but the dates with version is higher for on-site files:

Error: Applying PORDLVSR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVSR.xml,
v115.34
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVSR.xml, v1.1

Please clarify that the onsite files in $ICX_TOP/html are higher version 1.1 and we do not need to do anything with v115.34 files

- Vikram


12-DEC-07 00:55:48 GMT

.
UPDATE
=======
Hi Vikram,

The message "Applying PORDLVSR.xml to the wrong release" pretty much says it all.

Page definitions:

PORDLVMR.xml - Enter Delivery Information for Selected Line Item page
PORDLVSR.xml - Delivery Information page
PORSPAMT.xml - Non Catalog Request : Goods Or Services Billed by Amount
PORSPGDS.xml - Non Catalog Request : Goods Billed by Quantity
PORSPRAT.xml - Non Catalog Request : Rate

The above are obsolete with 11.5.10 code level iProcurement. You do not need to copy these.

Thanks,
Anthony

Friday, December 7, 2007

Low level logging (debug) is turned on

Akhilesh told me that whenever he logs in to the instance in Framework only mode, the following message is diaplsyed: "Low level logging (debug) is turned on. The cause is described in Metalink note 390139.1:

1. Logging turned on at profile level

FND: Debug Log Enabled = Yes
FND: Debug Log Level = Statement
FND: Diagnostics = Yes


2. Logging turned on on the OS - $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties
wrapper.bin.parameters=-DAFLOG_ENABLED=TRUE
wrapper.bin.parameters=-DAFLOG_MODULE=iby%
wrapper.bin.parameters=-DAFLOG_LEVEL=STATEMENT
wrapper.bin.parameters=-DAFLOG_FILENAME=/var/tmp/iby.log

Commenting out these 4 parameters removed the problem.

Help About option in forms displays blank for Forms version and session

Akhilesh told me about a problem in one of his Apps instances which was recently upgraded to 10g. He said that if we go to any forms session and click on Help menu - > About, usually it displays information about the Application version, Forms version, DB session id and a lot of other details. However in this particular instance, he said that the Forms version was blank and the session information was also blank. He had even logged an SR with Oracle without any success. Metalink Note 436110.1 describes the very same issue in R12, however our instance was 11i. This is what it says:

Symptoms

On fresh install of release 12.0.1, the forms server version is not shown in the About Oracle Applications window.

EXPECTED BEHAVIOR
Expect the version to be displayed

STEPS
The issue can be reproduced at will with the following steps:
1. Navigate to the About Oracle Applications window.
2. Scroll down to the "Forms Server" section.

BUSINESS IMPACT
Due to this issue, users cannot determine the version of the Forms server.

Changes

Fresh install of Release 12 with RUP1

Cause

Appears the System Profile Option "FND: Diagnostics is set to No

Solution

SQL> select fnd_profile.value('FND_DIAGNOSTICS') from dual;
If it returns a value of No then set this to 'Y' and retry.

Once you change System Profile Option "FND: Diagnostics" to " Y, you are able to see the Forms Server Information from Help About Oracle Applications.


I changed FND:Diagnostics to Y on site level. Closed the forms session and launched it again from self service. The forms version information was now visible .

Wednesday, December 5, 2007

Adventures with DBUA

In a 9.2.0.8 to 10.2.0.3 upgrade we used DBUA (Database Upgrade Assistant). Unfortunately after running smoothly till the progress bar reached 50%, we got the following errors which we ignored:

Error ignored: ORA-04045: errors during recompilation/revalidation of SYS.KU$_FHTABLE_VIEW ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select
Error ignored: ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 4120 bytes of shared memory ("shared
Error ignored: ORA-04031: unable to allocate 216 bytes of shared memory ("shared
Error ignored: ORA-04031: unable to allocate 768 bytes of shared memory ("shared
Error ignored: ORA-04031: unable to allocate 64 bytes of shared memory ("shared pool","declare
Error ignored: ORA-00900: invalid SQL statement
Error ignored: ORA-04031: unable to allocate 64 bytes of shared memory ("shared pool","declare
Error ignored: ORA-00900: invalid SQL statement
Error ignored: ORA-04031: unable to allocate 64 bytes of shared memory ("shared pool","declare
Error ignored: ORA-00900: invalid SQL statement
Error ignored: ORA-29532: Java call terminated by uncaught Java exception:

DBUA completed with errors in CATPROC and Spatial. We ran the statements which failed due to low shared pool after increasing shared pool manually. We also ran @?/rdbms/admin/catproc.sql. After this even CATALOG became INVALID. We re-ran DBUA and it corrected the catalog, catproc and spatial components. This is the first time, we experienced that DBUA could actually be run twice in case of errors and it correctly fixes those errors in the second time.

The shared pool size should be 900M in case you are using 24 as degree of parallelism and 600M if you are using 12 as degree of parallelism.

Procedures to validate Oracle Text, Intermedia, Spatial, Data Mining

SQL> select object_name from dba_objects
2 where object_name like 'VALIDATE%';

OBJECT_NAME
--------------------------------------------------------------------------------
VALIDATE_CONTEXT
VALIDATE_ODM
VALIDATE_ORDIM
VALIDATE_SDO
VALIDATE_ORDIM_WITH_DETAILS

You can execute these procedures to validate the components:

Procedure Name Component
VALIDATE_CONTEXT Oracle Text
VALIDATE_ODM Oracle Data Mining
VALIDATE_ORDIM Oracle Intermedia
VALIDATE_SDO Oracle Spatial
VALIDATE_ORDIM_WITH_DETAILS Oracle Intermedia

Tuesday, December 4, 2007

Undecipherable strings in pl/sql during Oracle DB creation

If you have ever watched an Oracle DB getting created, you might have seen many screens like this one:

CREATE OR REPLACE PACKAGE dbms_repcat_sna_utl wrapped
2 a000000
3 1
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 abcd
19 9
20 41fe b06
21 mbt5sPmynQOp/DlAlZbh0ZEW7uEwg9dxDNBdGy//MpLquAVBFqPpremIQ2cFtM1fshzdAOtJ
22 oEJo8RaXG7UkcEuy8Cyp2rdU7ezq6sx3ToxNGW/Jckuij8EsqvhW+eRD0E8ic2witfmh5HOx
23 ZKbCavumTsC81XtuCBhwaWbRuNein0DShVdEmyZ8U2flP07X6ltLpchRcOMK/gbiraiS5xYG
24 pJBaT0cJYmyIl9oYXf2DdMDMKmJrceLjwsEL9DjW4Zx2QTllSk1RrfMJ7+PEofKbGCr6S3TO
25 uRJLoTlnPyd1fKzBFgoki02pTNcCi87OoL6VsKEI+dmo/xasdr1AONn/RgoBDPJFMxEaW8wj
26 ZeyQQeejjO4Ug18hshXPpys+c2oHYssJ72JWa5uus8h2QnbKmAaKWb0bpAVdK9h9NVJqTEwg
27 8iGT3cE4embbE4oXBQMr5BulMktV1ZwnTDLiWoT+0B4BLXftWoivcXt0A+GjMWOpKoSoo1Sa
28 FK1zqWU/kWFuLeDhwqKkyfU/eesyGsQaxH8KjOfRXNqXcFLuoJmRCfrm+y1W/SK3YsekaCGT
29 K8Vq2L4LoEGpQ/A+2+1WlICh2ws8DKinEx/t0F89IXWgdTEICzD6KaWdARiQ1HV4xor/brI1
30 8u5cS7nbMJXaSkqSH+QuE0D1cUmHn421h/zQHRdhqFjJXu96wr0DjmBk4KxcayNNuPnfP/1T
31 8TaRyqdg/Caa8YS2ukJ2c/7IgBHkuIrHAqUtsaODISEiVmgaWHA4qac+J+Pbozax6i9AFIFm
32 zXTx5Cj73V+5Mmdesk+jWjhqSCfP8mcZDvo2ywt6Sce4aN5faoWjz4Y4IhMW0Z/xwr56krBP
33 r3vtmik2EwYeM/y8xvjcA1bWNWCGYF8v8+xTjWbUb+ynGlYK8mNNwHOEuOIdP0gIhFddfLiG
34 4wPP4ZARMCVer+gQRCO3KanNf07oDYZXyKRlFBMZeglJ70IsSwxsIJM7Wfl3Z65Ps+IsLDQa
35 7ZdqCVUqtPoso0B1tkUBQCFljG3Op6xN1eRuZ581iSafXFfm4qE5HjBVEWWueHjorUekrbvg
36 E5T4X2VXCu9HXR+GLgZj1jJcw7Yz8hFkLmZGU31fWuWwU6mmxJVUR7NtjtwDl5YVxDEl3hAZ
37 zHltxRN5X/9NOxV0zCIaiJOyblm2B1EAvvHTPYNhFeRaDZv1KCgRdUw4WmRRFXaNa94FFl0V
38 dNB1ibUZDgBpxZjp/A2AeDe1rzPs7N1xXUJiV3qkQ+q9c9Vx/FD7NZQr06GDNacatsKR91hz
39 BG+DWnD43eK2SPQam8NOusCrEjLM5zcU81j/cY73jlKcwmDRENqvs7HxRdRuQ6DU8yS3z60A
40 flrIz85L1Tp+UcfWz77gNorFh0XXIXYUVaXCuqeIa77rbF7gmFd8DVDxw8BbKMYbgLdU/kMz
41 GXEzg058AVf+uUpOLWjGzGxjB6DkfDORhk25tysGmxh5T5zLf+KvCvW5rGaf3P9i/DEdZ0OS
42 4DQhkwLuC8uIHQPgUfbHFgyj8J8OpduEJdfUIG34nCwcC/MHjG+Qr/Vkm96zibK+0mvf6+69
43 mvLs4YpGjuxK3MhrramrZ+nxTehNM5IyjCQBNg+OdmTAr/R054XnIsuYbVgjEWx22v8PkVO7
44 1sMYKy/FQbOBXDyr+PxRcmzePq9FvuN9dQSIOdo5+J0yx+7oDmLmgq7Trs4j4sPq6AKTZO4F
45 bddGEtLWEnUqlG0yeSR3f+S9PJgljD8zkCVJy354W8LWannq0cI+scFIOD2BXQB9EzfXQaEF
46 PYG40r/AkSXwsKpPL6GXJaGhniYdj4yhAip084Tlo2SnjEaY9KNeYGv6r/dGPH3pX0sXm+Xh
47 7ZBa1zffESdH9tN1aqxpfs3uY2zYaIxk15OkyEt/TjBqOCZfkosXXn8bCmiKFK7KLC+t/+F6
48 Yi04mbthZe7Bfzc0ckH2CRg08Y0lOs2gwNIVs05cyWBS0fY5I67y+gw+wD7w2YkJBA/kd/M7
49 gAFAZhyxsgc0pSqxo0qSJU7u7q9EPbogTt1LM9UeeSs8OdUg2T005jL8V5dznxaHpKXDSj62
50 KsXlxH2v/zO+dqEL3pCSj7l+AsVrfH6AXfVIaURzff/aY26IaMywyODayr0vUiu4gG7h7rwi
51 FI6JIbOiM30rzm7rOGHRt0rJFrJzSfv5FF9P9VK39W9aqbXcqG1B+DLYZVEaQFpKsbwDMBM4
52 u7NpHW9kOAco/clBuQA15HHwMlvsZ9efzLdEJ1hkLF++LDkCTRc7gZn2FzR3JzP4MJ+xULsc
53 G/ZPrefJwnOzB6Am+WqK8NtHxumFR0sS5fKFTHVXjLv0WeRerpc16Uz3UcQqhkbCm0du/+Vx
54 XsB2CugV5qE87sUecwQa3DrWUB10HtyWpFHqgJI4+n1q8NYAwfXz324XfxOWWabbZce4iofJ
55 y4OQBYs5XQ0sW/m5XFjWB/5l/QXomj87NMKNHELlzMottc7i2CRCnPl7R99CAcs7Ls50spra
56 9UK7UIvthE/+y/AZVZAZ2qAjHxLt81ZAEyAb0bnXgTi8gSSJtK9s+SREPApkPlm5k3sZ76X+
57 2xCFlHJwu1VrnXi5/jlzGeOa/TsShCpW3AVKbeyPCVp4PLH5y8VYPFWjnwTW/YUl0PDx6xVr
58 Uvlf24fVQZOkqoIi9qiaxXPWvi0aPGd5Vt41EUCi+wp7M//bQjQiiBLNWJ4p3PS9j2GNCyKB
59 VHel5k2Suyi49DOV+OJwDyxTYFe/9VHo19qHJBAIKDejStA=
60
61 /

Package created.

Notice that all such packages would have the keyword WRAPPED in their CREATE statement.

Oracle hides (obfuscates) its PL/SQL source code. Traditionally this has been done using the WRAP utility, but Oracle 10g Release 2 also allows this to be done dynamically using the DBMS_DDL package. This article in ORACLE_BASE presents examples of both methods of PL/SQL source obfuscation.

Monday, December 3, 2007

noexec_user_stack, Solaris and Oracle 10g

Metalink Note 395334.1 explains Why do we need to set NOEXEC_USER_STACK to 1 before Oracle installation as a mandatory step:

Sometimes programs allocate buffers but do not always check the size of the data coming into these buffers. An unwanted program can overflow such a buffer by placing a pointer to its own code and take control of the application with the privileges assigned to that application. Solaris 9 introduced the option to disable execution of user programs on the stack and assist in preventing this type of issue. This feature is only available on SPARC and AMD64 systems because it requires hardware support that is not available in current Intel 32bit CPUs. The protection is setup by employing the system wide parameter 'set noexec_user_stack=1' in the /etc/system file.

I got additional information from Solaris manuals:

A number of security bugs are related to default executable stacks when permissions are set to read, write, and execute. Although the SPARC and Intel application binary interface (ABI) mandates that stacks have execute permissions, most programs can function correctly without using executable stacks.

The Solaris 2.6 release provides a noexec_user_stack variable, which enables you to specify whether stack mappings are executable or not. By default, the value for the variable is zero, which provides ABI-compliant behavior. If the variable is set to non-zero, the system marks the stack of every process in the system as readable and writable but not executable.

Disabling Programs from Using Executable StacksUse the following steps to disable programs from using executable stacks:
1. Become superuser.
2. Add the line set noexec_user_stack=1 to the /etc/system file.
3. Type init 6 to reboot the system.

When the variable is set to a non-zero value, programs that execute code on their stack are sent a SIGSEGV signal, which usually terminates the program with a core dump. Such programs also generate a warning message, which includes the name of the program, the PID, and the UID of the user who ran the program, as shown in the following example:
a.out[347] attempt to execute code on stack by uid 555

The message is logged by the syslogd(1M) daemon when the syslog kern facility is set to notice level. This logging is set by default in the syslog.conf file, which means that the message is sent to both the console and to the /var/adm/messages file.

When you have set the noexec_user_stack variable, you can monitor these messages to observe potential security problems. You can also monitor the messages to identify valid programs that depend on executable stacks and have been prevented from correct operation. You can explicitly mark program stacks as executable by using the mprotect function.

Because of hardware limitations, executable stack problems can be caught and reported only on sun4m, sun4d, and sun4u platforms.

Developers sometimes allocate buffers in applications but do not always check the size of the data coming into these buffers. An attacker can overflow such a buffer and by placing a pointer to a piece of code under the attacker's control can take control of the application with the privileges assigned to that application. Buffer overflow exploits are most common on the stack and not very common on the heap (since heap memory allocation is different and hence harder to exploit). Solaris 9 introduced the option to disable execution of user programs on stack and assist in lowering the likely hood of exploit from stack based buffer overflows, this feature is only available on SPARC and AMD64 systems because it requires hardware support that is not available in current Intel 32bit CPUs. This can be done by employing a system wide parameter for example. Introducing the line 'set noexec_user_stack=1' in the /etc/system file. Another option is to have the application's stack be defined non-executable using the new mapfile segment descriptor.

The ability to prevent stack buffer overflow exploits along with Process Rights Management introduced in Solaris 10 provides a powerful mechanism to strengthen the security environment for applications. It relieves the IT administrator from the burden of worrying whether an application on one of the systems can compromise the rest of the system and the network due to buffer overflow exploits. It provides the IT administrator with a global switch to disallow even poorly written applications to be susceptible to stack buffer overflow attacks.

The Process Rights Management feature combined with the noexec_user_stack switch introduced in Solaris 7, provides some of the most powerful mechanisms of preventing and limiting the damage caused by hackers when they exploit application vulnerabilities. It is important to note that N1 Grid Containers have lower maximum privilege sets and for example, don't have access to devices.

Sunday, December 2, 2007

Some Responsibilities do not launch forms interface from Self Service

After applying ATG RUP4 on an instance, some responsibilities do not launch forms from Self Service. However everything works fine if you go through the direct URL containing f60cgi/dev60cgi. On closer inspection, it was found that only those responsibilities did not launch which had brackets ( ) in their name, For example Payables Administrator (Head Office). This is due to the fact that the context variable s_f60blockurlchar contains brackets. As a result, ATG blocks any responsibility which contains brackets ( ).

This is a known issue and is described in unpublished Metalink Note 413561.1:

Symptoms
Some forms don't start if executed from SSWA from some custom responsibilities, for instance:

SEC - VIE - Personnel Administrator (incl. Element entry)
KOS - PRI - Personnel Administrator (incl. Element entry)
SRM - BLG - Personnel Administrator (incl. Element entry)

The forms are correctly launched if you change the responsibility (inside classic Navigator / Forms)

Changes
Installation of 11i.ATG_PF.H RUP4 (as part of CPUOct2006)

Cause
The url generated contains brackets in the responsibility parameter (for instance gv3=KOS_PRI_PERS%20(EL)
and FORMS60_BLOCK_URL_CHARACTERS variable exludes brackets.

Customer has this error from Firefox browser when trying to use any of these responsibilities because the responsibility name has brackets in it, for instance "KOS - PRI - Personnel Administrator (incl. Element entry)" :

Oracle Forms Web CGI: Error detected
Your request cannot be serviced at this time due to the following error:
ERROR: URL Contains One or More Restricted Characters
http:// ...

FORMS60_BLOCK_URL_CHARACTERS value is :

%0a,%0d,!,%21,",%22,(,%28,),%29,;,[,%5b,],%5d,{,%7b,|,%7c,},%7d,%7f,>,%3c,<,%3e thus do not allow bracket characters ()

Solution
To implement the solution, please execute the following steps: Use OAM or Editcontext to update s_f60blockurlchar in context xml file i.e. to change: from: %0a,%0d,!,%21,",%22,(,%28,),%29,;,[,%5b,],%5d,{,%7b,|
,%7c,},%7d,%7f,>,%3c,<,%3e


to:

%0a,%0d,!,%21,",%22,%28,%29,;,[,%5b,],%5d,{,%7b,|
,%7c,},%7d,%7f,>,%3c,<,%3e


i.e. remove the bracket characters (, ) .

then run Autoconfig and retest.

I found Metalink Note 732586.1 which informs us that this is changed in the file

$IAS_CONFIG_HOME/Apache/Apache/conf. So, if you don't want to run autoconfig, you can manually search for FORMS60_BLOCK_URL_CHARACTERS in this file and change it also.

Friday, November 30, 2007

Values for context variable s_appserverid_authentication

In E-Business Suite 11.5.10 the default value of context variable, s_appserverid_authentication, is SECURE. In previous E-Business Suite 11i versions, this was set to OFF.

There are 3 Modes of authentication:

OFF - Server security is not checked. Any application server machine can access the database. Code IDs are also not checked. Use this option on test systems or if you have full control over the software on all machines which can physically access your database.

ON - Some level of trust is required to access the database. Either the application server must be registered with the database or the code must pass a module and version ID known to be trusted. Use this option only if you wish to maintain compatibility with application servers that you cannot yet patch to the code level required for best security.

SECURE - Full trust is required for access to the database. Only registered application server machines may connect to the database, and only trusted code modules may connect to the database.

If you are on Oracle Applications 11.5.10.2, the context variable, s_appserverid_authentication, is the parameter that allows you to skip the framework login. Setting this parameter to "OFF" mode is only to be used for debugging purposes. Any other use, such as leaving it in the "OFF" mode for regular login, is not supported.

Thursday, November 29, 2007

adadmin errors out with ORA-00942: TABLE OR VIEW DOES NOT EXIST

After a clone in one of the environments, whenever adadmin was run the following error appeared:

AD Administration error:
The following ORACLE error:
ORA-00942: table or view does not exist
occurred while executing the SQL statement:
SELECT to_char(APPLSYS.AD_SESSIONS_S.NEXTVAL) FROM SYS.DUAL

Unable to get the current value of sequence APPLSYS.AD_SESSIONS_S

We tried to check for the ownership and existance of the sequence AD_SESSIONS_S:

SQL> SELECT SUBSTR(OWNER,1,12)
OWNER,SUBSTR(OBJECT_NAME,1,45) NAME, SUBSTR(OBJECT_TYPE,1,12) TYPE
FROM DBA_OBJECTS where object_name = 'AD_SESSIONS_S' ;


OWNER NAME TYPE
------------ --------------------------------------------- ------------
APPLSYS AD_SESSIONS_S SEQUENCE
APPS AD_SESSIONS_S SYNONYM
WAVESET AD_SESSIONS_S SYNONYM
APPS_WEB AD_SESSIONS_S SYNONYM

Also tried to execute the query manually:

SELECT to_char(APPLSYS.AD_SESSIONS_S.NEXTVAL) FROM SYS.DUAL;
TO_CHAR(APPLSYS.AD_SESSIONS_S.NEXTVAL)
----------------------------------------
1852

We tried recreating grants and synonyms but it did not work. So we followed note 149987.1 and recreated AD_SESSIONS_S:

Run adodfcmp adutil.odf to re-create the AD_SESSIONs_S
Run adodfcmp for both tables and sequences.

Navigate to $AD_TOP/admin/odf and run the adodfcmp command similar to the
following

$ adodfcmp mode=tables touser=apps/apps priv_schema=system/manager \
> odffile=adutil.odf userid=applsys/apps changedb=yes

$ adodfcmp mode=sequences touser=apps/apps priv_schema=system/manager \
> odffile=adutil.odf userid=applsys/apps changedb=yes

That fixed the error.

Wednesday, November 28, 2007

ORA-02083:database name has illegal character '.'

Recently I was asked for help in an issue which is related to ASCP.

In OLTP instance, from OM Order Administrator Primary responsibility
1.Open ATP Inquiry Form
2.Give the Item name and Qty
3.click on the Global Availability Button.
4.Following Error message appears and user is unable to view Global Availability:

ORA-02083:database name has illegal character '.'
ORA-06512:at "SYS.DBMS_SYS_SQL", line 909
ORA-06512:at "SYS.DBMS_SQL", line 39
ORA-06512:at "APPS.MSC_SCH_WB", line 842

Official documentation states the cause of ORA-02083 as:

The database name supplied contains an invalid character. For example, there can be no periods, semicolons, single quotes, double quotes, blanks, or non-printable characters in the database name.


First I checked for the global name of OLTP and ASCP instances:
OLTP:
SQL> select * from global_name;
oltp11i.production.example.com

The global name still had the domain for production. So I changed it to:

SQL> alter database rename global name to oltp11i.development.example.com;
Database Altered
SQL> select * from global_name;
oltp11i.development.example.com

ASCP:
SQL>select * from global_name;
ascp11i

If you query dba_db_links table, you'll find that it automatically attaches the domain name derived from global name of the instance.

I dropped and recreated the ASCP hookup DB links without domain name.
ASCP Instance:
SQL> drop database link ASCP2OLTP.DEVELOPMENT.EXAMPLE.COM;
SQL> create database link ASCP2OLTP connect to apps identified by apps using 'oltp11i';

OLTP Instance:
SQL>drop database link OLTP2ASCP.DEVELOPMENT.EXAMPLE.COM;
SQL> create database link OLTP2ASCP connect to apps identified by apps using 'ascp11i';

I checked for the DB link names in MRP_AP_APPS_INSTANCES:

SQL> select M2A_DBLINK,A2M_DBLINK from mrp_ap_apps_instances
2 /

M2A_DBLINK A2M_DBLINK
--------------------------------- --------------------------------
ASCP2OLTP.DEVELOPMENT.EXAMPLE.COM OLTP2ASCP.DEVELOPMENT.EXAMPLE.COM

The DB links had been specified with domain name. I removed the domain names altogether, as the '.' (period) is not allowed in the base db name by the following statement.

SQL> update mrp_ap_apps_instances
2 set m2a_dblink='ASCP2OLTP',A2M_DBLINK='OLTP2ASCP';
1 row updated.

SQL> COMMIT;
Commit complete.

SQL> select M2A_DBLINK,A2M_DBLINK from mrp_ap_apps_instances
2 /

M2A_DBLINK A2M_DBLINK
--------------------------- -----------------------
ASCP2OLTP OLTP2ASCP

After these changes the error changed to this:

ORA-02019: connection description for remote database not found
ORA-06512: at "APPS.MSC_ATP_PROC", line 3519
ORA-06512: at line 1
ORA-06512: at "APPS.MSC_ATPSOURCES_NEW", line 53
ORA-06512: at "APPS.MSC_SCH_WB", line 660
ORA-06512: at "APPS.ORDER_SCH_WB", line 54

------------------------------------------------
FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-02019.

Tracing the error from forms side or the session from DB side is not logging this error. Still investigating this one.

Akhilesh solved this one for me. This is what he did:

On OLTP instance:

Checked the value of profile option MRP:ATP Database Link and it was oltp2ascp.development.example.com. So we removed the domain name and made it oltp2ascp

On ASCP Instance:

Logged in with Advanced Planning Administrator responsibility
Admin > Instances

The DB link values in the fields "From Source to APS" and "From APS to Source" had the dblink with domain name. Removed the domain name. Saved.

Voila, the issue is solved. Error message no longer appears and Global Availability form opens on clicking the button.

Tuesday, November 27, 2007

ld.so.1: vi: fatal: relocation error

While trying to solve the issue in the previous post, I had modified the LD_LIBRARY_PATH to include /usr/ucblib directory. This was the result:

$vi
ld.so.1: vi: fatal: relocation error: file /usr/bin/vi: symbol cur_term: referenced symbol not found

unset LD_LIBRARY_PATH

vi worked fine. /usr/ucblib directory should not be in the $LD_LIBRARY_PATH. If it is, you'll get this error.

libucb.so.1 open failed: No such file or directory

I got a mail from Sandeep today reporting this error:

$ adfrmctl.sh start

You are running adfrmctl.sh version 115.37

ld.so.1: FNDSVCRG: fatal: libucb.so.1: open failed: No such file or directory
Killed
Starting forms server for example11i on port 9000.
ld.so.1: FNDSVCRG: fatal: libucb.so.1: open failed: No such file or directory
Killed

adfrmctl.sh: exiting with status 0

I advised him to relink the FND and AD binaries, however adadmin resulted in this error:

$ adadmin
ld.so.1: adadmin: fatal: libucb.so.1: open failed: No such file or directory
Killed


We did a truss to find out in which directories it was searching for libucb.so.1:

$ truss adadmin
execve("/example11i/erpapp/appl/ad/11.5.0/bin/adadmin", 0xFFBEAFAC, 0xFFBEAFB4) argc = 1
resolvepath("/usr/lib/ld.so.1", "/usr/lib/ld.so.1", 1023) = 16
open("/var/ld/ld.config", O_RDONLY) = 3
fstat(3, 0xFFBEA808) = 0
mmap(0x00000000, 992, PROT_READ, MAP_SHARED, 3, 0) = 0xFF390000
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libclntsh.so.1.0", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libclntsh.so.1.0", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libclntsh.so.1.0", 0xFFBEA890) = 0
resolvepath("/example11i/erpapp/8.0.6/lib/libclntsh.so.1.0", "/example11i/erpapp/8.0.6/lib/libclntsh.so.1.0", 1023) = 43
open("/example11i/erpapp/8.0.6/lib/libclntsh.so.1.0", O_RDONLY) = 3
mmap(0x00000000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE, 3, 0) = 0xFF380000
mmap(0x0D3731A8, 4595712, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFEC00000
mmap(0xFEC00000, 4322968, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFEC00000
mmap(0xFF02E000, 144324, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 4317184) = 0xFF02E000
mmap(0xFF052000, 64584, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED|MAP_ANON, -1, 0) = 0xFF052000
munmap(0xFF020000, 57344) = 0
memcntl(0xFEC00000, 445716, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libnsl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libnsl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libnsl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libnsl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libnsl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libnsl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libnsl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libnsl.so.1", 0xFFBEA890) = 0
resolvepath("/usr/lib/libnsl.so.1", "/usr/lib/libnsl.so.1", 1023) = 20
open("/usr/lib/libnsl.so.1", O_RDONLY) = 3
mmap(0xFF380000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF380000
mmap(0x15AFA2C0, 655360, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFF280000
mmap(0xFF280000, 582238, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF280000
mmap(0xFF310000, 33256, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 589824) = 0xFF310000
mmap(0xFF31A000, 23312, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED|MAP_ANON, -1, 0) = 0xFF31A000
memcntl(0xFF280000, 84064, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libsocket.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libsocket.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libsocket.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libsocket.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libsocket.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libsocket.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libsocket.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libsocket.so.1", 0xFFBEA890) = 0
resolvepath("/usr/lib/libsocket.so.1", "/usr/lib/libsocket.so.1", 1023) = 23
open("/usr/lib/libsocket.so.1", O_RDONLY) = 3
mmap(0xFF380000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF380000
mmap(0x15AF9ED8, 114688, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFF360000
mmap(0xFF360000, 40558, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF360000
mmap(0xFF37A000, 4365, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 40960) = 0xFF37A000
munmap(0xFF36A000, 65536) = 0
mmap(0x00000000, 8192, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_ANON, -1, 0) = 0xFF350000
memcntl(0xFF360000, 14496, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libgen.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libgen.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libgen.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libgen.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libgen.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libgen.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libgen.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libgen.so.1", 0xFFBEA890) = 0
resolvepath("/usr/lib/libgen.so.1", "/usr/lib/libgen.so.1", 1023) = 20
open("/usr/lib/libgen.so.1", O_RDONLY) = 3
mmap(0xFF380000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF380000
mmap(0x1516F4D0, 98304, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFF330000
mmap(0xFF330000, 23073, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF330000
mmap(0xFF346000, 2335, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 24576) = 0xFF346000
munmap(0xFF336000, 65536) = 0
memcntl(0xFF330000, 6932, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libdl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libdl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libdl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libdl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libdl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libdl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libdl.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libdl.so.1", 0xFFBEA890) = 0
resolvepath("/usr/lib/libdl.so.1", "/usr/lib/libdl.so.1", 1023) = 19
open("/usr/lib/libdl.so.1", O_RDONLY) = 3
mmap(0xFF380000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF380000
mmap(0x15B00850, 8192, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFF270000
mmap(0xFF270000, 2302, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF270000
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libc.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libc.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libc.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libc.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libc.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libc.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libc.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libc.so.1", 0xFFBEA890) = 0
resolvepath("/usr/lib/libc.so.1", "/usr/lib/libc.so.1", 1023) = 18
open("/usr/lib/libc.so.1", O_RDONLY) = 3
mmap(0xFF380000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF380000
mmap(0x15B042E8, 802816, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFF180000
mmap(0xFF180000, 704200, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF180000
mmap(0xFF23C000, 24772, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 704512) = 0xFF23C000
munmap(0xFF22C000, 65536) = 0
memcntl(0xFF180000, 113528, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libaio.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libaio.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libaio.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libaio.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libaio.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libaio.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libaio.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libaio.so.1", 0xFFBEA890) = 0
resolvepath("/usr/lib/libaio.so.1", "/usr/lib/libaio.so.1", 1023) = 20
open("/usr/lib/libaio.so.1", O_RDONLY) = 3
mmap(0xFF380000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF380000
mmap(0x0295B288, 106496, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFF160000
mmap(0xFF160000, 28909, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF160000
mmap(0xFF178000, 1584, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 32768) = 0xFF178000
munmap(0xFF168000, 65536) = 0
memcntl(0xFF160000, 7184, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libm.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libm.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libm.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libm.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libm.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libm.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libm.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libm.so.1", 0xFFBEA890) = 0
resolvepath("/usr/lib/libm.so.1", "/usr/lib/libm.so.1", 1023) = 18
open("/usr/lib/libm.so.1", O_RDONLY) = 3
mmap(0xFF380000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF380000
mmap(0x016884F8, 294912, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON, -1, 0) = 0xFF110000
mmap(0xFF110000, 225354, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 0) = 0xFF110000
mmap(0xFF156000, 7292, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 221184) = 0xFF156000
munmap(0xFF148000, 57344) = 0
memcntl(0xFF110000, 10604, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
close(3) = 0
stat("/example11i/erpapp/8.0.6/network/jre11/lib/sparc/native_threads/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/appl/cz/11.5.0/bin/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/dt/lib/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/openwin/lib/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/opt/SUNWcluster/lib/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/example11i/erpapp/8.0.6/lib/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
stat("/usr/lib/libucb.so.1", 0xFFBEA890) Err#2 ENOENT
ld.so.1: adadmin: fatal: libucb.so.1: open failed: No such file or directory
write(2, " l d . s o . 1 : a d a".., 77) = 77
munmap(0xFF380000, 8192) = 0
lwp_self() = 1
*** process killed ***

Based on the above it is clear that it is looking for libucb.so.1 in $LD_LIBRARY_PATH.

This is what we did as a workaround:

cd $ORACLE_HOME/lib
ln -s /usr/ucblib/libucb.so.1 libucb.so.1

After this both adadmin and adfrmctl.sh worked fine.

However the question still remains why it happened. I compared adadmin of this particular instance and a vision instance:

$ ldd /vision11i/erpapp/appl/ad/11.5.0/bin/adadmin
libclntsh.so.1.0 => /vision11i/erpapp/8.0.6/lib/libclntsh.so.1.0
libnsl.so.1 => /usr/lib/libnsl.so.1
libsocket.so.1 => /usr/lib/libsocket.so.1
libgen.so.1 => /usr/lib/libgen.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libc.so.1 => /usr/lib/libc.so.1
libaio.so.1 => /usr/lib/libaio.so.1
libm.so.1 => /usr/lib/libm.so.1
libmp.so.2 => /usr/lib/libmp.so.2
/usr/platform/SUNW,Sun-Fire-V240/lib/libc_psr.so.1


$ ldd /example11i/erpapp/appl/ad/11.5.0/bin/adadmin
libclntsh.so.1.0 => /example11i/erpapp/8.0.6/lib/libclntsh.so.1.0
libnsl.so.1 => /usr/lib/libnsl.so.1
libsocket.so.1 => /usr/lib/libsocket.so.1
libgen.so.1 => /usr/lib/libgen.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libc.so.1 => /usr/lib/libc.so.1
libaio.so.1 => /usr/lib/libaio.so.1
libm.so.1 => /usr/lib/libm.so.1
libucb.so.1 => /gpswms76/erpapp/8.0.6/lib/libucb.so.1
libresolv.so.2 => /usr/lib/libresolv.so.2
libelf.so.1 => /usr/lib/libelf.so.1
libmp.so.2 => /usr/lib/libmp.so.2
/usr/platform/SUNW,Sun-Fire-V240/lib/libc_psr.so.1

The list of libraries being referenced is not same. Clearly something was wrong in the environment when the binaries were relinked.

Metalink Note 164279.1 describes a similar problem:

PROBLEM DESCRIPTION
====================

You receive the following error message when invoking an oracle executable
on Sun Solaris. This applies to all executbles including Database, Networking,
Tools,and Applications.

ld.so.1: : fatal: libucb.so.1: open failed:
No such file or directory
Killed


PROBLEM EXPLANATION
======================

The executable was built using the wrong linker ('ld'). The Solaris
operating system comes with two linkers, the SVR4 linker (/usr/ccs/bin/ld)
and SunOS 4.x compatibility linker (/usr/ucb/ld). Oracle requires the use of
the /usr/ccs/bin utitlities to properly link the executables and
if /usr/ucb/ld is used instead of /usr/ccs/bin/ld, executables will fail with
the above error message.

NOTE: Please see the appropriate product Installation Guide for
O/S requirments.


SOLUTION DESCRIPTION
=====================

To correct the problem, you'll need to modify your PATH environment variable
so that the /usr/ccs/bin directory comes before /usr/ucb directory, and then
relink the executables that were linked with the wrong linker.

1. Correct the PATH environment variable.

csh
-----
% setenv PATH /usr/ccs/bin:${PATH}

sh, ksh or bash
-----------
% PATH=/usr/ccs/bin:$PATH
% export PATH

2. Ensure the PATH is set correctly.

% which ld
/usr/ccs/bin/ld <== This should be returned if the the PATH is
correct.

3. Relink the executables:

See Note 131321.1 for Data Server relinking instructions.
I am not sure if PATH was incorrectly set when the binaries were relinked. However, I have advised the DBA team to relink all binaries based on the above analysis.

Monday, November 26, 2007

ORA-1403 when forms is launched from self service

In one of our instances, we are getting this error in Firefox whenever we try to launch forms from self service modules:

ORA-01403: no data found Content-type: text/html; charset=UTF-8 Location: http://justanexample.com:8000/oa_servlets/oracle.apps.fnd.sso.AppsLogin?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F%2F11ioltp.justanexample.com%3A8013%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&errText=Your%20session%20is%20no%20longer%20valid.%20Please%20login%20again.

This error only appears when we try the option from Firefox. Internet Explorer is silent and doesn't show any error.

I have asked the DBA team to enable trace on ORA-01403 in init.ora through this event to find out:

event = "1403 trace name errorstack: 10046 trace name context forever, level 12"

Some data purging had been done by DBAs, after which this error is occuring.

After trace was enabled, this is what we found in trace file:

PARSING IN CURSOR #3 len=272 dep=1 uid=173 oct=3 lid=173 tim=5586109696263 hv=1880614960 ad='9ab642f8'
SELECT FPOV.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES FPOV, FND_PROFILE_OPTIONS FPO WHERE FPO.PROFILE_OPTION_NAME =
'AFCORE_LOGGING_ENABLED' AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID AND FPOV.LEVEL_ID = 10001 AND FPO.APPLICATION_ID
= FPOV.APPLICATION_ID
END OF STMT
PARSE #3:c=0,e=165,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=5586109696257
BINDS #3:
EXEC #3:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=5586109696528
FETCH #3:c=0,e=101,p=0,cr=5,cu=0,mis=0,r=0,dep=1,og=4,tim=5586109696670
*** 2007-11-26 15:39:02.788
ksedmp: internal or fatal error
ORA-01403: no data found
Current SQL statement for this session:
begin
owa.user_id := :userid;
owa.password := :passwd;
owa.ip_address := :ipaddress;
owa.hostname := :hostname;
owa.init_cgi_env(:n__, :nm__, :v__);
if (owa_custom.authorize = TRUE) then
:authorized := 'yes';
else
:authorized := 'no';
end if;
:realm := owa.protection_realm;
end;
----- PL/SQL Call Stack -----
object line object
handle number name
398b4f6f0 23 package body APPS.FND_CORE_LOG
398b4f6f0 158 package body APPS.FND_CORE_LOG
39cbe1d38 213 package body APPS.FND_PROFILE
39cbe1d38 1548 package body APPS.FND_PROFILE
39cbe1d38 1720 package body APPS.FND_PROFILE
39cbe1d38 2357 package body APPS.FND_PROFILE
398b8bbd0 35 package body APPS.FND_WEB_CONFIG
398b8bbd0 214 package body APPS.FND_WEB_CONFIG
398b8bbd0 411 package body APPS.FND_WEB_CONFIG
39b7cdf08 33 package body APPS.OWA_CUSTOM
39b7d8b00 7 anonymous block
----- Call Stack Trace -----

Still investigating this one.

Wednesday, November 21, 2007

ORA-20002, Error '-21525 while compiling flexfields

Sadiq reported this error today:

While compiling the flexfields, the procedure wf_event.raise failed with following

parameters:

event_name = oracle.apps.fnd.flex.dff.compiled

event_key = FND.$SRS$.FNDSCARU

The error raised is:

ORA-20002: 3825: Error '-21525 - ORA-21525: attribute number or (collection element at index) violated its constraints' encountered during execution of Rule function 'fnd_bes_proc.process_event' for event 'oracle.apps.fnd.flex.dff.compiled' with key 'FND.$SRS$.FNDSCARU'.

There is a similar error described in Metalink Note 430477.1 which suggests applying patch 5625612 (which is superseded by ATG_PF.H RUP5 which is again superseded by RUP6). However this patch was already applied in the instance where the above error occurred.

This was solved by increasing the open_cursors in init.ora reportedly.

Oracle Openworld 2007 presentations available for download

Openworld 2007 Content Catalog

Userid: cboracle
Password: oraclec6

You’ll be asked for userid and password when you click on the attachment icon for a given session

Tuesday, November 20, 2007

ORA-600 errors after upgrade from 9.2.0.8 to 10.2.0.3

1.ORA-00600: internal error code, arguments: [16203], [], [], [], [], [], [], []
2.ORA-00600: internal error code, arguments: [17281], [1001], [0x3AE64C028], [], [], [], [], []
3.ORA-00600: internal error code, arguments: [kcbzwb_4], [], [], [], [], [], [], []

1. Possibly bug 5108130:
Making a reference in a PL/SQL block to a synonym at a remote site R1 when
the referent of the synonym was on yet another site R2 could have resulted
in an ORA-600 [16203] error. The PL/SQL block must have been compiled by
a 9.2.0.X or earlier compiler.

2. Possibly bug 5482174:
Deinstantiation of a large number of PLSQL objects that have opened cursors
when session_cached_cursors is non-zero might hit an ORA-600 [17281] [36]
with a call stack of the form
'ksupop->kgiCallStateCleanUp->kgiLRUResize'
repeated within it a number of times.

Workaround:
Set session_cached_cursors to zero.

3. Possibly bug 5028099:
ORA-600 [kcbzwb_4] can occur when using in memory undo (IMU)
for private buffers.

Workaround:
Set "_in_memory_undo"=FALSE

Friday, November 16, 2007

LOB indexes cannot be renamed, rebuilt, or modified

Today, Anand asked me if it is possible to rebuild lob indexes. I knew that it is not possible, but wanted to explore why ? This is what Tom Kyte says in one of his responses in asktom.oracle.com:

You have a table with a LOB column in it.

A LOB is simply a pointer. It points to an index. the index points to the chunks that make up the LOB.

Hence when you create a LOB, you will always get a lob index created (to find the chunks
for the lob fast) and a segment that holds the lob data (chunks).

So by design, LOB indexes can not be renamed, rebuilt or modified.

If you ever try to issue alter index rebuild command on a LOB index you'll get an
ORA-02327 cannot create index on column with datatype LOB

Thursday, November 15, 2007

Visit to a data center

I had the privilege of visiting a data center with a group of colleagues for a tour. The Data Center manager was very passionate about his job. He showed us the generators, the UPS, the chillers which make the whole thing tick. Then he showed us the server rooms, Mainframes, SANs. He would use the phrase "everything under the sun" is under this roof. It was really good to see the machines on which we work everyday. There were very few people in the data center. Physical access is not needed unless there is a problem which can't be solved remotely. The tape libraries were all automatic operated by robots. It was very futuristic indeed. What amazed me was that the Data Center manager was more passionate about the Generators than about the servers. Come to think of it, we also worry more about the things which make E-Business Suite work, instead of the product itself.

Wednesday, November 14, 2007

GPL Ghostscript 8.54: Unrecoverable error, exit code 1

Leslie asked me about this error today:

GPL Ghostscript 8.54: Unrecoverable error, exit code 1

This error comes whenever trying to use ghostscript.

It is possible that this error is appearing because ghostscript is unable to find fonts. This is what I found when I googled:

Fonts are distributed (and installed) independently from GS since they are provided by others (not Artifex Software or the developers of Ghostscript).

Fonts will have the .pfb suffix, so a find . -name "*.pfb" -print from some top level directory (such as /usr or /app) will locate them for you. Then you can either:

1) Symbolic link to the font directory as the expected dir for example:

ln -s /usr/local/share/ghostscript/fonts /app/ghostscript-8.00/share/ghostscript/fonts

2) Tell gs where to find the fonts using the -I option to add a search path. For example:

gs -I/usr/local/share/ghostscript/fonts ...

3) Set the GS_LIB environment variable to specify the dir For example (bin/sh or bin/ksh or bin/bash):

export GS_LIB=/usr/local/share/ghostscript/fonts

Of course, if you don't already have the fonts installed, then do
so first.

Regards,
Ray Johnston
Artifex Software Inc.

Based on the above information, this is what I did:

# /usr/local/bin/pdf2ps /tmp/justanexample.pdf - | lp -d HPDLC
Error creating job: check spooling directory: /var/spool/print

$ /usr/local/bin/pdf2ps /tmp/justanexample.pdf
Error: /invalidfont in findfont
Operand stack:
--dict:5/5(L)-- F1 10 --dict:8/8(L)-- --dict:8/8(L)-- ModenaCondensedLight --dict:12/12(L)-- Helvetica-Narrow Helvetica-Narrow Font Helvetica-Narrow 1426219 Helvetica-Narrow --nostringval-- Helvetica-Narrow NimbusSanL-ReguCond
Execution stack:
%interp_exit .runexec2 --nostringval-- --nostringval-- --nostringval-- 2 %stopped_push --nostringval-- --nostringval-- --nostringval-- false 1 %stopped_push 1 3 %oparray_pop 1 3 %oparray_pop 1 3 %oparray_pop --nostringval-- --nostringval-- 2 1 5 --nostringval-- %for_pos_int_continue --nostringval-- --nostringval-- --nostringval-- --nostringval-- %array_continue --nostringval-- false 1 %stopped_push --nostringval-- %loop_continue --nostringval-- --nostringval-- --nostringval-- --nostringval-- --nostringval-- --nostringval-- --nostringval-- 9 13 %oparray_pop 10 13 %oparray_pop --nostringval-- --nostringval-- --nostringval-- --nostringval-- --nostringval-- false 1 %stopped_push 13 14 %oparray_pop --nostringval-- --nostringval-- --nostringval-- 1 -1 1 --nostringval-- %for_neg_int_continue
Dictionary stack:
--dict:1120/1686(ro)(G)-- --dict:2/20(G)-- --dict:75/200(L)-- --dict:75/200(L)-- --dict:105/127(ro)(G)-- --dict:253/347(ro)(G)-- --dict:21/24(L)-- --dict:4/6(L)-- --dict:20/20(L)-- --dict:1/1(ro)(G)-- --dict:1/1(ro)(G)-- --dict:1/1(ro)(G)-- --dict:8/8(L)-- --dict:17/17(ro)(G)-- --dict:1120/1686(ro)(G)--
Current allocation mode is local
Last OS error: 2
GPL Ghostscript 8.54: Unrecoverable error, exit code 1
dasvi@tsgsd1008 #

gs –h tells me the font directory:

dasvi@tsgsd1008 # gs -h
GPL Ghostscript 8.54 (2006-05-17)
Copyright (C) 2006 artofcode LLC, Benicia, CA. All rights reserved.
Usage: gs [switches] [file1.ps file2.ps ...]
Most frequently used switches: (you can use # in place of =)
-dNOPAUSE no pause after page | -q `quiet', fewer messages
-gx page size in pixels | -r pixels/inch resolution
-sDEVICE= select device | -dBATCH exit after last file
-sOutputFile= select output file: - for stdout, |command for pipe,
embed %d or %ld for page #
Input formats: PostScript PostScriptLevel1 PostScriptLevel2 PostScriptLevel3 PDF
Default output device: x11
Available devices:
bbox bit bitcmyk bitrgb bj10e bj200 bjc600 bjc800 bmp16 bmp16m bmp256
bmp32b bmpgray bmpmono bmpsep1 bmpsep8 cdeskjet cdj550 cdjcolor cdjmono
cljet5 cljet5c deskjet devicen djet500 epswrite faxg3 faxg32d faxg4 ijs
jpeg jpeggray laserjet lj5gray lj5mono ljet2p ljet3 ljet3d ljet4 ljet4d
ljetplus nullpage pbm pbmraw pcx16 pcx24b pcx256 pcxcmyk pcxgray pcxmono
pdfwrite pgm pgmraw pgnm pgnmraw pj pjxl pjxl300 pkm pkmraw pksm pksmraw
png16 png16m png256 png48 pngalpha pnggray pngmono pnm pnmraw ppm ppmraw
ps2write psdcmyk psdrgb psgray psmono psrgb pswrite pxlcolor pxlmono
spotcmyk tiff12nc tiff24nc tiff32nc tiffcrle tiffg3 tiffg32d tiffg4
tiffgray tifflzw tiffpack tiffsep uniprint x11 x11alpha x11cmyk x11gray2
x11gray4 x11mono xcf
Search path:
. : /usr/local/share/ghostscript/8.54/lib :
/usr/local/share/ghostscript/8.54/Resource :
/usr/local/share/ghostscript/fonts
For more information, see /usr/local/share/ghostscript/8.54/doc/Use.htm.
Report bugs to bug-gs@ghostscript.com, using the form in Bug-form.htm.

I checked for font directory

$ ls -ltr /usr/local/share/ghostscript/fonts
/usr/local/share/ghostscript/fonts: No such file or directory
$ ls -ld /usr/local/share/ghostscript/fonts
/usr/local/share/ghostscript/fonts: No such file or directory

The problem is occurring because the fonts directory does not exist.

To fix this I created a soft link:

# cd /usr/local/share/ghostscript
# ln -s /usr/sfw/share/ghostscript/fonts fonts
# /usr/local/bin/pdf2ps /tmp/justanexample.pdf

**** Warning: Fonts with Subtype = /TrueType should be embedded.
The following fonts were not embedded:
ModenaCondensedLight
ModenaCondensedMedium
ModenaCondensedRegular

**** This file had errors that were repaired or ignored.
**** The file was produced by:
**** >>>> Actuate XML to PDF Converter 1.0 <<<< **** Please notify the author of the software that produced this **** file that it does not conform to Adobe's published PDF **** specification. # /usr/local/bin/pdf2ps /tmp/hutch_bill_Aug.pdf - | lp -d HPDLC **** Warning: Fonts with Subtype = /TrueType should be embedded. The following fonts were not embedded: ModenaCondensedLight ModenaCondensedMedium ModenaCondensedRegular **** This file had errors that were repaired or ignored. **** The file was produced by: **** >>>> Actuate XML to PDF Converter 1.0 <<<< **** Please notify the author of the software that produced this **** file that it does not conform to Adobe's published PDF **** specification. request id is HPDLC-10 (1 file) # ls -ltr justanexample.ps -rw-r--r-- 1 root root 201770 Nov 15 15:32 justanexample.ps The command is not erroring out any more and the expected output of .ps file is created. I did all this as root user. Lets test this as a normal user: dasvi@tsgsd1008 # /usr/local/bin/pdf2ps /tmp/hutch_bill_Aug.pdf **** Warning: Fonts with Subtype = /TrueType should be embedded. The following fonts were not embedded: ModenaCondensedLight ModenaCondensedMedium ModenaCondensedRegular **** This file had errors that were repaired or ignored. **** The file was produced by: **** >>>> Actuate XML to PDF Converter 1.0 <<<< **** Please notify the author of the software that produced this **** file that it does not conform to Adobe's published PDF **** specification. $ /usr/local/bin/pdf2ps /tmp/hutch_bill_Aug.pdf - | lp -d HPDLC_ Error creating job: check spooling directory: /var/spool/print

This could be due to the fact that lp can't be used by normal users. Let us check the permissions on lp binary:

$ which lp
/usr/bin/lp

$ sudo su - root
# ls -ltr lp
-rwxr-xr-x 1 root lp 28092 Jan 22 2005 lp

These permissions do not allow normal users to print. setuid needs to be on the lp binary to allow normal users to print. So I changed the permissions:

# chmod 4511 lp
# ls -ltr lp
-r-s--x--x 1 root lp 28092 Jan 22 2005 lp
# exit
$ /usr/local/bin/pdf2ps /tmp/hutch_bill_Aug.pdf - | lp -d HPDLC_G

**** Warning: Fonts with Subtype = /TrueType should be embedded.
The following fonts were not embedded:
ModenaCondensedLight
ModenaCondensedMedium
ModenaCondensedRegular

**** This file had errors that were repaired or ignored.
**** The file was produced by:
**** >>>> Actuate XML to PDF Converter 1.0 <<<< **** Please notify the author of the software that produced this **** file that it does not conform to Adobe's published PDF **** specification. request id is HPDLC-11 (1 file) Now it works for any normal user.