Monday, December 28, 2009

How do you manually regenerate a form in Release 12?

1. Check the FORMS_PATH env variable. Assuming these are US forms the FORMS_PATH should include
$AU_TOP/resource and $AU_TOP/forms/US.

2. To compile a form manually from the command line try either of these two command line arguments (replace FNDFBMAS.fmb with the form of your choice.): module=FNDFBMAS.fmb userid=apps/ module_type=form batch=yes compile_all=yes

frmcmp_batch module=FNDFBMAS.fmb userid=apps/ module_type=form batch=yes compile_all=yes

Tuesday, December 22, 2009

The Function Is Not Available Under The Responsibility

After applying the ATG RUP 7 When attempting to navigate to a function which has been newly added to a menu the following error occurs:

The Function Is Not Available Under The Responsibility

The issue can be reproduced at will with the following steps:
1. Query menu:

2. Add function: and Save.
3. Verify that the menu compilation request (FNDSCMPI module: Compile Security) completed successfully.
4. Log into e-Business Suite and try to navigate to function: .
5. The error occurs.

This issue is caused by launching the menu item before the Compile Security has completed.

When a function is added to a menu , a concurrent program 'Compile Security' is submitted which loads the data in Fnd_Compiled_Menu_Functions table.

Whenever a function is accessed, security code checks Fnd_Compiled_Menu_Functions table to determine whether that function is accessible under the given security context or not. So if this function is accessed before the 'Compile Security' program is finished, the function cache gets loaded with stale data and the reported error message is thrown.

So after modifying the 2 menu definitions associated with 'System Administrator' and 'Application Developer' make sure that 2 concurrent requests submitted for the above 2 modifications completed normally and then try to access the functions.

Further once this error occurs, it will be cached which means that even after the menu item has been flagged as available, it the error will be returned from the cache.

-- To implement the solution, please execute the following steps::
1) Manually run the 'Compile Security' concurrent program, set the parameter to YES.
2) Shut down Middle Tier (Apache)
3) Delete the contents of $OA_HTML/_pages directory (11.5.10 only).
4) Restart Middle tier
5) Test the menu item

Wednesday, December 16, 2009

How to recover from a corruption of an object in system tablespace

Table IDL_UB2$ who's owner is SYS got corrupted. This was noticed while trying to compile a package EAM_PROCESS_WO_PVT.

SQL> alter package EAM_PROCESS_WO_PVT compile package;
Warning: Package altered with compilation errors.
SQL> show errors;
No errors.
SQL> alter package EAM_PROCESS_WO_PVT compile body;

Warning: Package Body altered with compilation errors.
SQL> show errors;
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [phdite:node kind]
9361/5 PL/SQL: SQL Statement ignored
9362/32 PL/SQL: ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 2, block # 40099)
ORA-01110: data file 2:
9366/46 PL/SQL: Statement ignored


As a first step. we found the object that was in question and its type.



then performed the following steps:

SQL> shutdown immediate
SQL> startup upgrade
SQL> truncate table idl_ub1$;
SQL> truncate table idl_char$;
SQL> truncate table idl_ub2$;
SQL> truncate table idl_sb4$;
SQL> @?/rdbms/admin/utlirp
SQL> shutdown immediate
SQL> startup upgrade
processes to connect to dbs
SQL> @?/javavm/install/rmjvm
SQL> shutdown immediate
SQL> exit <-- important!<<<<<<<<<<<<<<<<SQL> startup upgrade
processes to connect to dbs
SQL> alter system set "_system_trig_enabled"=false scope=memory;
SQL> @?/javavm/install/initjvm.sql
SQL> shutdown immediate
SQL> exit <-- important! <-- important!<<<<<<<<<<<<<<<<SQL> startup upgrade
processes to connect to dbs
SQL> @?/rdbms/admin/utlrp
SQL> shutdown immediate
SQL> startup


The corruption was fixed.

Tuesday, December 15, 2009

RMAN cloning using duplicate command

1) Make a copy of the following TARGET directories under
DB Tier:
Archive log backup
Control file backuup
Database full backup
Apps Tier:Apps tech

2) Configure the Oracle Home
Target – Apps/DB

Overwrite the dbs, appsutil, network/admin directories with the previously copied directories.
Rename the $ORACLE_HOME/admin/ PROD_perp to DBA_mperp.

3) Copy the Apps file system to target.
This can be done along with copying the database Oracle Home files.

4) Edit the init_SID.ora
Add a parameter remote_login_passwordfile=EXCLUSIVE
Add following parameters:db_file_name_convert=('/u02/oracle/PROD/db/apps_st/data','/u02/oracle/DBA/db/apps_st/data') log_file_name_convert=('/u02/oracle/PROD/db/apps_st/data','/u02/oracle/DBA/db/apps_st/data')

5) Create the password file
Target –DB
orapwd file=orapwPROD password=oracle entries=3

6) Target –DB
cd $ORACLE_HOME/appsutil/clone/binperl dbTechStack

7) Startup the target instance in NOMOUNT state.
Target –DB
Startup nomount.

8) Connect to source database in target machine
rman tee restore.log

9) Connect to RMAN in the target machine

10) Run the duplicate command
Note: If we don’t use set until then we need to make sure PROD doesn’t generate any archive logs after the restore if it’s generated then it’ll look for it also which is not available with us.

run {
duplicate target database to “DBA”;

11) Update the libraries (for both)
cd $ORACLE_HOME/appsutil/install/Context_namesqlplus / as sysdba @adupdlib.sql so

12) Configure the database
cd ora_home/appsutil/clone/binperl dbconfig

13) Once the db tiers are up, clone the apps tier
cd common_top/clone/binperl adcfgclone appsTier

14) Remove the old $COMMON_TOP/admin/log and out directories to clear out space.
Truncate fnd_concurrent_requests tables


Is DBVERIFY Detects Corrupt Blocks With Redolog Files or Control Files ?

1)You ran DBVIERY utility on the redo log files or control files
2) DBVERITY is reporting every page as corrupt

Example :
You ran
dbv file=/usr/oracle/redo/logfile1.dbf log blocksize=512
and got following error message

DBVERIFY: Release - Production on Thu Jun 10 21:24:25 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting :
FILE = /usr/oracle/redo/logfile1.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad header found during dbv:
Data in bad block -
type: 0 format: 0 rdba: 0x00000001
last change scn: 0x0000.1f7fda67 seq: 0x22 flg: 0xcc
consistency value in tail: 0x00000000
check value in block header: 0x920, computed block checksum: 0x0
spare1: 0x2f, spare2: 0x11, spare3: 0x0

DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is ONLY for use with datafiles, it will NOT work against control files or redo logs

No fix. It is expected behavior with redo log and control files.

How To Run Dbv In Parallel On A Single data file.

To run dbv in parallel on a single datafile. Steps -----
1) Identify the file# and name of the datafile.
2) Identify the number of blocks in the datafile.

3) Determine how many parallel sessions has to be used.
4) Start Parallel dbv sessions.

1) I dentify the file# and name of the datafile.
select * from v$dbfile where name like '%datafile_name%'; (OR) select * from v$dbfile ;
2) Identify the number of blocks in the datafile.

1.A Find Data Block Size
eg: SQL>show parameter db_block_size
----------------------------------- ------- ------
db_block_size integer 8192
1.B Calculate the the number of blocks.
SQL> select BYTES/8192 from v$datafile where FILE#=5;
So the file# 5 contains 5120 blocks

3) Determine how many parallel sessions has to be used.
This example shows three parallel sessions.
4) Start Parallel dbv sessions.
$dbv FILE=filename START=1 END=2000
$dbv FILE=filename START=2001 END=3500 $dbv FILE=filename START=3501 (Note:-END defaults to the last block of the file)

How to check to see if a specific Forms patch is applied to your Oracle Applications?

For Oracle Applications 11i forms version 6.0.8.xCheck the readme of the patch. The patch readme will have instructions similar to the following: Backup the Forms class files, %cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6643292

Look in the directory for something like : PRE_BUG##### to determine if the forms patch has been applied.

For Oracle Applications R12 forms version 10g
Look in the opatch inventory for the patch number. cd $ORACLE_HOME/OPatch export $PATH=`pwd`:${PATH} opatch lsinventory -detail > 1012_inventory.txt check if patch ###### is listed Or you can review the patch readme for similar information as documented above for the 6.0.8.x forms patchsets.

R12: Patch(es) , conflict with the patch currently being installed (PATCH)

You are applying a technologies patch to the R12 Ebusiness Suite 10.1.2.X.X or 10.1.3.X.X home and receive the following message during opatch application. These steps are also relevant for non Ebusiness suite technologies patches as the theory is the same. Conflicting patches: ,
Patch(es) , conflict with the patch currently being installed (PATCH BEING APPLIED).
If you continue, patch(es) , will be rolled back and the new patch (PATCH BEING APPLIED) will be installed.
Note: If the patch currently being installed (PATCH BEING APPLIED) is rolled back, it is recommended that the fixes being rolled back here (, ) are reinstalled.
If a merge of the new patch (PATCH BEING APPLIED) and the conflicting patch(es) , is required, contact Oracle Support Services and request

You are unsure on whether to proceed or quit the patch.
How does one determine if the patch being applied can safely override the existing patch?

When there is such a conflict between and existing patch and the patch being applied you should compare the bugs fixed in both the patches. The conflict arises when a patch being applied contains a file that is also included in another patch manually applied on top of the current installed patchset. As there is no version information the conflicting patch message appears.
Obtain a copy of each patch and compare the 'inventory' file or the 'readme' file. These files contain a list of bugs fixed by each patch. These files exist within the patch zip file. Extract the patch zip to view either of these files
Compare the bugs listed. What you are looking for is to make sure that the patch being applied has all the bugs mentioned that are fixed in the existing patch. In other words, before rolling back the existing patch you want to make sure the bugs it fixes are included in the new patch being applied.
If all the patches listed in the existing patch are included in the patch being applied you can safely continue the patch.
In some cases the patch being applied may not contain all the bug fixes that are included with the existing patch. If you do not require the missing patches you can safely continue patch application.
If you do require the patches then you should stop the patch application and log a service request with Oracle Support requesting a "Merge Label Request, MLR" of the existing patch and the patch being applied.

Where To Download The BI Publisher Desktop for Windows? (Template Builder)

The latest version of BI Publisher Desktop for Windowsis available here:

How to start the Output Post Processor (OPP)?

The OPP is not started by default.It needs to be activated when BI Publisher is setup.

To activate the OPP please do this:
The Profile Option "Concurrent: GSM Enabled" must be set to Y
1. Login to Apps with sysadmin responsibility
2. Navigate to: Concurrent -> Managers -> Define
3. Query for Manager = 'Output Post Processor'or Short Name = FNDCPOPP
4. Check the checkbox "Enable" .
5. Click on 'Work Shifts button
6. see Work Shift of the OPP and
Processes = 1
Parameters = oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
Sleep Second = 30
7. Save
8. Navigate to:_ Concurrent -> Managers -> Administer
9. Make sure the Output Post Processor Status is not 'Deactivate'Set Status to 'Activate' if necessary.
10. Stop and Start the Concurrent Managers using

Friday, December 11, 2009

Gather Schema errors out with ORA-20005:

When running gather schema statistics, the following error occurs.
Error #1: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.AQ$_WF_CONTROL_P***ORA-20005: object statistics are locked (stattype = ALL)***
Error #2: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_GSM_IPC_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #3: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_GSM_OPP_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #4: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_TM_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #5: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_TM_RET_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #6: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.WF_SMTP_O_1_TABLE***ORA-20005: object statistics are locked (stattype = ALL)***

This can happen with Advance Queue tables.
In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather is locked (disabled) on these queue.

The following statement can be used to check the tables which have statistics locked:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Unlock statistics gathering on those queues running the commands below.
- To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');
To unlock individual tables (need to run for all tables individually):
exec dbms_stats.unlock_table_stats('table_owner','table_name');

SQL> exec dbms_stats.unlock_schema_stats ('AR');

SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');