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

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

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

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.

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.

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

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 .

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

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.

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.

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.