Troubleshooting Apps DBA

FND_NO_DATABASE_CONNECTION

noreply@blogger.com (Sundeep Dubey) — Tue, 01 Mar 2011 11:27:00 +0000

This is the very common error in R12 instance "FND_NO_DATABASE_CONNECTION", but when you retry you won't get this error. To overcome this issue Oracle has come up with patch "9908921:R12.FWK.B Details".

Error Stack :
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_DATABASE_CONNECTION.
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1251)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:2195)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
at _OA._jspService(_OA.java:212)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:734)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:280)
at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:68)

Happy Troubleshooting !!!

Session Time out Setting in R12

noreply@blogger.com (Sundeep Dubey) — Fri, 11 Feb 2011 11:50:00 +0000

Many occassions we have seen end users reporting session timed out even though they were working on. This is for sure its an issue from client end and JRE version which client is running on is the culprit for the same. Try to have the same version of JRE which is running on your server or latest one.
Even if this doesn't help try at client end following setting with running JRE:
1. Navigate to: Start > Control Panel > Java > Advanced > Java Plug-in
2. De-select the option "Enable the next-generation Java Plug-in "
3 Restart the browser
4-Retest the issue

Next if you want to increase timeout setting from server end. Do the following:
1. Change profile option ICX: Session Timeout to 60 min from 30, which is default.
2. change s_oc4j_sesstimeout value to 60 again in context file and run adconfig.
3. Bounce the apps services and check.

Happy Troubleshooting !!!

OPP jobs failing with time out

noreply@blogger.com (Sundeep Dubey) — Fri, 11 Feb 2011 11:38:00 +0000

Hi All, I am back after long time. Never late then never. Now onwards I may focus on R12 issues which I have faced in my project. Here is first one.

Log file : The Output Post-processor is running but has not picked up this request. No further attempts will be made to post-process this request, and the request will be marked with Warning status.Setting the profile option Concurrent: OPP Response Timeout to a higher value may be necessary.

Solution:
Increase the value of following profiles:
Concurrent:OPP Process Timeout
Concurrent:OPP Response Timeout

Happy Troubleshooting !!!

12.1.2 to 12.1.3 upgrade on Solaris

noreply@blogger.com (Sundeep Dubey) — Thu, 07 Oct 2010 09:15:00 +0000

We have recently upgrade our instances to 12.1.3. Here is what we did.

1) 10.1.3.5.0 patch 8626084
2) From Apps tier Source $INST_TOP/ora/10.1.3/sid_hostname.env file to set $ORACLE_HOME as 10.1.3 home.
3) cd /8626084/Disk1
4) ./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc
5) On fourth screen it will ask for oc4jadmin password. Kindly put "oafm".
6) Stop opmn processes, keeping same 10.1.3 env.
$ORACLE_HOME/opmn/bin/opmnctl stopall
7) Unzip patch p6880880_101000_SOLARIS64.zip in 10.1.3 and 10.1.2 OH to have latest opatch version.
8) Run Autoconfig on both Apps nodes.
9) apply patch p8999551_101350_Generic.zip in 10.1.3 home via opatch :
opatch apply
10) Confirm patchset version:
Run the command 'opatch lsinventory -detail'
Look for 'Oracle Application Server PatchSet' in the 'Product' column. 'Version' should show 10.1.3.5.0.
11) Now apply apps patches, enable Maintenance Mode via adamin
12) Apply patch p9239089_R12.AD.B_R12_SOLARIS64.zip
Create $ORACLE_HOME/appsutil/admin on the database server.
Copy adgrants.sql (UNIX) from this patch directory to
$ORACLE_HOME/appsutil/admin
Set the environment to point to ORACLE_HOME on the database server
Login as sysdba and run
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql APPS
13) Apply patch 9239090 12.1.3 patch. (Take 5-6 hrs)
14) Apply online help patch p9239095_R12_GENERIC.zip
15) Apply post patches p9817770_R12.ATG_PF.B_R12_SOLARIS64.zip and p9966055_R12.FND.B_R12_GENERIC.zip
16) Run autoconfig on both Apps tier
17) Run the admkappsutil.pl utility to create the file appsutil.zip in the
/admin/out directory
perl $AD_TOP/bin/admkappsutil.pl
18) On Database tier copy this file
19) And unzip in OH cd
unzip -o appsutil.zip
20) Run autoconfig on DB Tier
21) Run adpreclone on both DB Tier and Apps Tier
perl adpreclone.pl dbTier
perl adpreclone.pl appsTier
22) Disable Maintenance Mode via adamin
23) Needs to deploy forms before starting services on apps tier
24) Stop all the Application Services
cd $ADMIN_SCRIPTS_HOME
adstpall.sh
25) Back up the original system-jazn-data.xml file.
cp $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml.orig
26) Add the following :
vi $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml
========= file snippet original =================

oc4jadmin

OC4J Administrator

oc4jadmin

OC4J Administrator

OC4J Administrator
{903}9VrhYTuhd7DyBJf7J/4KwbSEIlETQJOO

================================================
=========== with change ============================

oc4jadmin

OC4J Administrator

oc4jadmin

OC4J Administrator

OC4J Administrator
!welcome

===================================================
:wq!
27) Back up the original opmn.xml file
cp $INST_TOP/ora/10.1.3/opmn/conf/opmn.xml $INST_TOP/ora/10.1.3/opmn/conf/opmn.xml.orig
28) Add the following line:
ipaddr remote="realhost.domain.com" request="virtualhost.domain.com"
as

notification-server interface="ipv4"
ipaddr remote="realhost.domain.com" request="virtualhost.domain.com"
port local="#local_port" remote="#remote_port" request="#request_port"
29) Run the deployment script
$FND_TOP/bin/txkrun.pl -script=CfgOC4JAppEnter
Application name for re-deployment ? forms
Enter Oc4j Instance password for re-deployment ? welcome
Run Autoconfig ? No
30) Restore both system-jazn-data.xml and opmn.xml from the backup taken under step 25 and 27 respectively
31) Run autoconfig
$ADMIN_SCRIPTS_HOME/adautocfg.sh
32) Start the services.
$ADMIN_SCRIPTS_HOME/adstrtal.sh apps/appspwd

Once done, you will be able to access forms now on 12.1.3.
Happy Troubleshooting !!!

Finding XML Publisher Version in R12

noreply@blogger.com (Sundeep Dubey) — Thu, 07 Oct 2010 09:08:00 +0000

Its being long I have posted something. Got Stuck in major milestone in our project and 12.1.3 upgrade.

1) The following SQL statement will show which patches and thus with version of Oracle XML Publisher is installed on an Oracle E-Business Suite Release 12.0 and 12.1 environment:

SELECT DECODE (bug_number, '4440000', '5.6.3') PATCH, bug_number
FROM ad_bugs WHERE bug_number IN('4440000');
PATCH BUG_NUMBER
----- ------------------------------
5.6.3 4440000

2) The version of a specific Java Class has a one to one relationship with the currently installed version of Oracle XML Publisher. This method is suitable for both Oracle E-Business Suite 11 and Release 12.

$ adident Header $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class:
$Header MetaInfo.java 120.6 2006/08/17 01:25:30 bgkim noship $

Metainfo.class XML Publisher Patch Reference Included in ... R12
120.6 5.6.3 4440000 Note 422508.1

Happy Troubleshooting !!!

Unable to start OACORE OC4J in R12

noreply@blogger.com (Sundeep Dubey) — Fri, 18 Jun 2010 07:48:00 +0000

We were doing JRE upgrade today and had issue in one of the instance not coming up. Basically OACORE OC4J service was unable to get start. Script adstrtal.sh was failing to start this service and erroing out:
==================================================
Executing service control script:/Path/admin/scripts/adoacorectl.sh start
Timeout specified in context file: 100 second(s)
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception
You are running adoacorectl.sh version 120.13
Starting OPMN managed OACORE OC4J instance ...
****************************************************
===================================================

After looking at log file :
===================================================
06/18/10-01:32:04 :: adoacorectl.sh: Starting OPMN managed OACORE OC4J instance
opmnctl: starting opmn managed processes...
================================================================================opmn id=tsgsd2107:6204
0 of 1 processes started.
ias-instance id=XXXXXXXXXXXXX.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--------------------------------------------------------------------------------
ias-component/process-type/process-set:
default_group/oacore/default_group/
Error
--> Process (index=1,uid=237255567,pid=24554)
failed to start a managed process after the maximum retry limit
Log:
/Path/logs/ora/10.1.3/opmn/default_group~oacore~default_group~1.log
06/18/10-01:36:43 :: adoacorectl.sh version 120.13
06/18/10-01:36:43 :: adoacorectl.sh: Stopping OPMN managed OACORE OC4J instance
==============================================
OPMN log says this:

==============================================
10/06/18 01:35:32 [libopmnoc4j] Host and Port information for port type jms not sent by the OC4J process
10/06/18 01:35:32 [libopmnoc4j] WARNING: OC4J did not send the protocol value for port id: jms
10/06/18 01:35:32 [libopmnoc4j] Port information in the ONS notification is incorrect for proc: 237255567.
Some of the reasons for the failure are:
1. ajp, rmi and jms ports could not be bound by the process.
2. If a port value for a certain port ID is handed by OPMN to the OC4J process and this value is not returned back in the ONS notifications.
For example, this can happen for certain bad JGroups related configurations.
10/06/18 01:35:32 [libopmnoc4j] Process Start Error: default_group~oacore~default_group~1 (237255567:24554)
10/06/18 01:35:34 [pm-process] Stopping Process: default_group~oacore~default_group~1 (237255567:24554)
10/06/18 01:35:34 [libopmnoc4j] Process Stop Error: default_group~oacore~default_group~1 (237255567:24554)
10/06/18 01:35:34 [libopmnoc4j] Failed to construct stop command for proc: 237255567
10/06/18 01:35:34 [libopmnoc4j] Forcefully Terminating Process: default_group~oacore~default_group~1 (237255567:24554)
10/06/18 01:35:36 [pm-requests] Request 7 Completed. Command: /start?process-type=oacore
=======================================

Now big thing is solution. Here you go:
Go to $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/oacore_default_group_1 on application tier and look for jms.state and *.lock files. If you find them in this location once all you services are stop. Delete or rename them. Once you are done try again. Now OACORE OC4J service will come up.

Happy Troubleshooting !!!

11gR2 upgrade with 12.1.2

noreply@blogger.com (Sundeep Dubey) — Wed, 31 Mar 2010 12:23:00 +0000

Steps for 11gR2 Upgrade
Pre-Upgrade Task
Remove DB from archive log mode
Complete Backup of the Database & Application Binaries
Apps Patches:
9062910 -- 11g Release 2 interoperability patch for Release 12.1
7651166 -- 12.1 TXK Delta 2 patch
Adconfig Patches
8966480 -- FWDPORT 8942827 - 11.2DB:APPS11I:NEED CHANGES TO TEMPLATE FILES TO SUPPORT 11GR2
9366993 -- ADBLDXML AND AUTOCONFIG COMPLETES WITH NJNI11:JAVA.LANG.UNSATISFIEDLINKERROR

Set the path of oraInventory in /var/opt/oracle/oraInst.loc file.
Make entry for the oracle home that needs to be upgrade in /var/opt/oracle/oratab file.
Take a backup of the existing RDBMS oraInventory folder.

Software Installation Task
Set the path for ORACLE_SID and ORACLE_BASE env variable
Create a 11.2.0 directory under the $ORACLE_BASE.
Execute the runInstaller to start the 11.2.0 Software only installation.
Click the Ignore all checkbox on Perform Prerequisite Checks when the checks fail for OS Patch - 124861-15 as this is an optional patch.
Run root.sh through root user when prompted.
Install the 11g Examples CD

"After the installation, make sure that: "
The ORACLE_SID environment variable must be set accordingly.
The ORACLE_BASE environment variable must be set accordingly.
The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])

Example:
ORACLE_SID=test11g; export ORACLE_SID
ORACLE_BASE=/test11g/oracle; export ORACLE_BASE
ORACLE_HOME=/test11g/oracle/11.2.0; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:/usr/dt/lib:/usr/openwin/lib; export LD_LIBRARY_PATH
PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0; export PERL5LIB
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

Create nls/data/9idata directory
From 11.2.0 Oracle Home run the following:
perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Check the directory created --- $ORACLE_HOME/nls/data/9idata
set the env variable ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Additional RDBMS Patch
Apply RDBMS Patch - 9218789
Note: Do not run any of the post install instructions as those will be done after the upgrade
Stop the Application Processes and database listener.
From old ORACLE_HOME --Connect DB as sysdba and Drop SYS.ENABLED$INDEXES
SQL> drop table sys.enabled$indexes;

Prepare for DB Upgrade
Copy utlu112i.sql from 11.2.0 ORACLE_HOME/rdbms/admin to a temporary folder.
"From Old ORACLE_HOME, connect DB as SYSDBA"
SQL> SPOOL upgrade_info.log
SQL> @utlu112i.sql
SQL> SPOOL off
Check for the output of the script.
Add space to SYSAUX Tablespace.
SQL> alter database datafile '//dbdata/data1/sysaux01.dbf' resize 600M;

add the following paraneter to init.ora file:
java_pool_size=128M
comment out the following parameters from init.ora file:
#plsql_native_library_dir = //oracle/11.1.0/plsql/nativelib
#plsql_native_library_subdir_count = 149

Restart the Database
Connect database as SYSDBA and execute the following queries:
SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
SQL> SELECT * FROM dba_2pc_pending;
SQL> PURGE DBA_RECYCLEBIN;
Re-execute utlu112i.sql

DB Upgrade Task
Run netca from old 11.1.0 Oracle Home and delete the listener.
Shutdown the database and comment out the following parameter in 11.1.0 Oracle Home's init.ora # local_listener='_LOCAL'

Make sure the following envs' are set properly:
$ export ORACLE_SID=
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
$ export TNS_ADMIN=$ORACLE_HOME/network/admin
Run netca from new 11.2.0 Oracle Home and create the new listener.
Run database upgrade assistant as follows:
"$ dbua -initParam ""_disable_fast_validate=TRUE"""

Modify initialization parameters
Use the init.ora parameter setting for the 11.2.0 as per NoteID:396009.1
"Ensure the parameter ""_disable_fast_validate=TRUE"" is set.."

Restart the Database.
Start the Database listener.
Connect with SYS and run only catmgdidcode.sql and utlrp.sql as part of the post install instructions in patch 9218789. The other scripts were already run as part of the dbua upgrade.
SQL> conn / as sysdba
SQL> @?/md/admin/catmgdidcode.sql
SQL> @?/rdbms/admin/utlrp.sql

Post DB Upgrade Tasks
Copy $APPL_TOP/admin/adgrants.sql from admin node to temporary folder.
Run as follows:
"$ sqlplus ""/ as sysdba"" @adgrants.sql apps"

Copy $AD_TOP/patch/115/sql/adctxprv.sql from admin node to temporary folder.
Run as follows:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
"$ sqlplus ""/ as sysdba"""
"SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');"

Login to Admin Node and execute the following script connected with APPS schema.
$ sqlplus apps/[APPS password] @$FND_TOP/patch/115/sql/wfaqupfix.sql APPLSYS APPS

Implement and run AutoConfig
Make sure all the above pre-req Patches are applied mentioned in lines 8-13
Create appsutil.zip
Login to Application Tier and Source the env
perl $AD_TOP/bin/admkappsutil.pl
the scripts will create appsutil.zip in $INST_TOP/admin/out
Copy AutoConfig to the RDBMS ORACLE_HOME
Copy or FTP the appsutil.zip file to the
cd
unzip -o appsutil.zip

Install JRE on the Database tier
Download Latest Update of JRE 6.0
http://java.sun.com/products/archive/
Ftp the file to $ORACLE_HOME/appsutil
$ cd $ORACLE_HOME/appsutil
$ sh jre1.6.0._latest_update#.sh
$ mv jre1.6.0._latest_update# jre

Generate the Database Context File
$ cd $ORACLE_HOME/appsutil/bin
$ perl adbldxml.pl
"Note: After creating the XML context file, do the following changes:"
1. Backup the existing context_file.
2. rename the existing context_file (_.xml) to _.xml [E.g SID_hostname.xml -> sid_vhost.xml]
3. Open the _.xml and change all the occurance of to vi sid_vhost.xml
4. ensure variable s_jretop points to a proper JRE directory i.e. $ORACLE_HOME/jdk/jre.
5. ensure variable s_db_tnsadmin points to $ORACLE_HOME/network/admin/$CONTEXT_NAME

Run AutoConfig on the Database tier
$ cd $ORACLE_HOME/appsutil/bin
$ ./adconfig.sh contextfile=

Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from admin node to temporary folder.
Note: adstats.sql has to be run in restricted mode.
Make sure that you have at least 1.5 GB of free default temporary tablespace.
"$ sqlplus ""/ as sysdba"""
SQL> alter system enable restricted session;
SQL> @adstats.sql
"$ sqlplus ""/ as sysdba"""
SQL> alter system disable restricted session;
SQL> exit;

Re-create custom database links (conditional)
Re-create grants and synonyms
"Run ""Recreate grants and synonyms for APPS schema"" through ADADMIN."

Restart Applications server processes
Synchronize Workflow views
"Log on to Oracle E-Business Suite with the ""System Administrator"" responsibility"
Run the request with the following parameteres:
Request Name = Workflow Directory Services User/Role Validation
p_BatchSize = 10000
p_Check_Dangling = Yes
Add missing user/role assignments = Yes
Update WHO columns in WF tables = No

Refernce Metalink Notes:
ID 1058763.1 Interoperability Notes EBS R12 with Database 11gR2.
ID 387859.1 Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12.
ID 396009.1 Database Initialization Parameters for Oracle Applications Release 12.
ID 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
ID 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12

------------------------------------------
Happy Troubleshooting !!!

12.1.2 CU patchset

noreply@blogger.com (Sundeep Dubey) — Thu, 18 Feb 2010 05:58:00 +0000

We have recently upgraded our 12.1.1 env to 12.1.2, it has only one AD patch prereq nothing else. It went smoothly with no errors.

We are planning to move our 11gR1 database to 11gR2 soon once minimum patch requirement for OS level met. Will keep you posted.

Happy Troubleshooting !!!

Ten Great Tips For 2010

noreply@blogger.com (Sundeep Dubey) — Thu, 31 Dec 2009 13:16:00 +0000

Ten Great Tips For 2010

1. Stay out of Trouble.

2. Aim for Greater Heights.

3. Stay Focused on your Job.

4. Exercise to Maintain Good Health.

5. Practice Team Work.

6. Rely on your Trusted Partner to Watch your Back. Take your Time Trusting Others.

7. Save for Rainy Days.

8. Rest and Relax.

9. Always Take Time to Smile.

AND

10. Realize That Nothing is Impossible.

Wishing You and Your Family A Very Happy, Prosperous and Healthy New Year 2010!

Export issue in 11g (11.1.0.7) with R12 (12.1.1)

noreply@blogger.com (Sundeep Dubey) — Tue, 15 Dec 2009 17:59:00 +0000

I got one request to export entire R12 (12.1.1) database. I thought it going to be easiest task for me, but it turn to be most challenging for me. This is how proceed:
1. Create Directory in database:
create directory SUNEXP as '/sundeep/exp';
2. Grant read, write on SUNEXP to DBA role, as I want to start expdp as system.
grant read,write on directory EXPDIR to "DBA";
3. Ran expdp full export:
expdp system/password DIRECTORY=SUNEXP FULL=Y COMPRESSION=ALL DUMPFILE=exp_sundeep.dmp LOGFILE=exp_sundeep.log

I thought I am done and it will gona take its own sweet time. But it failed at very first step:
----------------------------------------------------------------------------------
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_AW_EXP".SCHEMA_CALLOUT(:1,0,1,'11.01.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 923
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 7834
---------------------------------------------------------------------------------------
Now the issue is from where to start troubleshooting it. Then I thought of running this as normal exp command:
exp system/password FULL=Y FILE=exp_sundeep.dmp LOG=exp.log statistics=none feedback=1000000 buffer=2000000 compress=Y FILESIZE=4294967296 resumable=N

It started gracefully and end was not that graceful and I got the same error:
----------------------------------------------------------------------------------------
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 33272 encountered
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], [], [], [], [], []ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 933
ORA-06512: at "SYS.DBMS_AW", line 1295
ORA-06512: at "SYS.DBMS_AW_EXP", line 575
ORA-06512: at "SYS.DBMS_AW_EXP", line 697
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
---------------------------------------------------------------------------------------
Yup now there seems to be some issue. I focused on "ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened" which is my key words to start working on this issue.

SQL> select aw_name, aw_version from all_aws where owner='ZPB';

AW_NAME AW_V
------------------------------ ----
ZPBANNOT 9.1
ZPBCODE 10.2
ZPBDATA 9.1

See here was the issue, ZPBANNOT was 9.1 version and my db was 11.1. I thought of export is alone and delete it so that I can proceed and once done I can import it back. Following are the command do the same:
set serveroutput on
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT');
exec dbms_aw.execute('limit name to all');
exec dbms_aw.execute('allstat');
exec dbms_aw.execute('export all to eif file ''SUNEXP/ZPB.ZPBANNOT_old.eif'' ');

When I ran first command it failed:
set serverout on
SQL> exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW');
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW')
*
ERROR at line 1:
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [],[], [], [], [], [], []ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
----------------------------------------------------------------------------------------
One of the note says ZPBDATA or ZPBANNOT are obsolete objects and we can delete them.
I did this to delete them

set serveroutput on
exec dbms_aw.execute('aw delete zpb.zpbdata')
exec dbms_aw.execute('aw delete zpb.zpbannot')

Then started the same expdp command which I used earlier and this time it worked. It looks really simple but it ate my 1.5 days to resolve this issue.

Happy Troubleshooting !!!

OPP on Virtual Host

noreply@blogger.com (Sundeep Dubey) — Wed, 09 Dec 2009 14:10:00 +0000

In one of my earlier post I have mentioned how to use Virtual host for Concurrent manager. In this post I am specifically talking about issue you might encounter while using report output as xml and it doesn't show the actual output rather it shows html tags. So just understand the concept how OPP is being used in xml reports and then look for solution.

An Application user submits an XML Publisher based report.
The Standard Concurrent Manager process the request.
The XML data file is generated by the standard concurrent manager.
This can be done by various methods:
Oracle Reports - Report Definition File (RDF)
XML Publisher Data Template - XML data template linked to the Data Definition
Any other process that produces XML output
A Post processing action defines that the output needs to be generated by the Output Post Processor hence it is triggered by the standard manager.
The Output Post Processor generates the final report and informs the standard Concurrent Manager whether that was successful.
The Standard Concurrent Manager finalizes the concurrent request.

Now the error in report:
----------------------------------
One or more post-processing actions failed. Consult the OPP service log for details.

Now the error in OPP log:
----------------------------------
oracle.apps.fnd.cp.util.RemoteFileException: An Error occured during file transmission in the FND File Server. The Applications File Server could not open the file 12345333 for read.

Now the resolution:
----------------------------------
Was worried why OPP is not able to read the file, we have Shared APPL_TOP too. Then though it might be due to virtual host. Then came across one note: 824887.1, it was like sure shot for me and it worked. There is a profile option "Concurrent: Force Local Output File Mode" it was set to No I made it Yes and checked the failed request. Vola it worked as expected and my team mates were jumping. For more detail about this profile look at metalink note mentioned earlier.

Happy Troubleshooting !!!

Installing VMware with Oracle Enterprise linux 64 bit

noreply@blogger.com (Sundeep Dubey) — Sun, 29 Nov 2009 14:02:00 +0000

Last few days I was busy with installating R 12 (12.1.1) on VMware. It was very hectic but learning experience for me. In my previous post I have put screens for installing VMware on Windows 7 and Oracle Enterprise linux over and above VM. Here are the explaination of all figures.
Fig 1 : This is the first screen which will see after you click on installation file for VMware 2.0.1, which I downloaded from http://www.vmware.com/download/server.
Fig 2 : It ask for location. I installed it on my USB external drive so I have it as F drive.
Fig 3 : Now Enter FQDN and change VM Storage Path too.
Fig 4 : Installation in Progress.
Fig 5 : Once the installation is done, you will have an icon for VMware to connect. Just double click on it and make sure you added hostname.domainname in the windows/system32/driver/etc/hosts file. And in IE trusted domain too. Make sure you have username and password for the user you installed on windows session. As it takes Windows username and password for access. Once you are in we have to create Virtual Machine. This is the first screen where you have to give VM name. I gave it RAC1.
Fig 6 : Select Guest operating system, I selected Linux and in version other linux 64 bit.
Fig 7 : Give Memory size, I gave it 1024 MB.
Fig 8 : Create VM disk, I have size of 10GB for this.
Fig 9 : Network Connection keep default.
Fig 10 : Select the place where you will have your Guest OS. As I have DVD for the same I selected E drive.
Fig 11 : I didn't added Floppy Drive and Added USB Controller.
Fig 12 : That's the Final screen and select Finish.
Fig 13 : Once you login now you will see RAC1 (VM), but you have to install some add in for VM connection, so look at the screen and install it.
Fig 14 : Add-in getting installed.
Fig 15 : Once you power on the VM and click on that, make sure you have DVD in your Laptop, as it will going to install OS now.
Fig 16 : Select yes on this message.
Fig 17 : Keep selection as shown in fig.
Fig 18 : Select yes on this figure.
Fig 19 : Add hostname and IP manually.
Fig 20 : Add Root Password.
Fig 21 : Click next to start installation.
Fig 22 : Progress and you are done with installation. It will ask for reboot and some more question to answer and you are done with installation.

Happy Troubleshooting !!!

Installing VMware with Oracle Enterprise Linux 64 bit

noreply@blogger.com (Sundeep Dubey) — Sun, 29 Nov 2009 12:05:00 +0000

Couldn't open ICX Application Message file

noreply@blogger.com (Sundeep Dubey) — Sun, 18 Oct 2009 14:10:00 +0000

One off my team mate reported an issue while opening forms in Purchasing Super user he encountered error message:

"Message Dictionary couldn't open the ICX application message file. Cannot open file /u02/apps/apps_st/appl/icx/12.0.0/mesg/US.msb for reading."

When I checked the location it was not there. Then I though running generate message file from adadmin but it didn't help. Here is the solution :

We have run Concurrent request "Generate Messages" for "Oracle iProcurement" and it will generate US.msb file under required location.

Happy Troubleshooting !!!

R12 New features

noreply@blogger.com (Sundeep Dubey) — Sun, 18 Oct 2009 13:35:00 +0000

While using R12 I come across few things which might help you guys too:

1. It doesn't require to change apps password in any config files. So no need to run autoconfig if you used to do it.

2. All startup files are in $INST_TOP/admin/scripts

3. While creating Share Appl Top we can keep all other tops as read only except INST_TOP.

4. While running $INST_TOP/admin/scripts/adautocfg.sh it require apps password only. No need to provide Context file location any more.

5. CONTEXT File location is $INST_TOP/appl/admin

6. If you want to install R12 on virtual host start rapidwiz as "rapidwiz -servername virtual"

7. Just change s_cphost to physical hostname and run autoconfig, otherwise ICM and WF Mailer container components will have issue while starting.

I will keep on posting stuff as and when I come across. Till then

Happy Trobleshooting !!!

How to change Oracle user ORACLE_HOME

noreply@blogger.com (Sundeep Dubey) — Sun, 18 Oct 2009 13:24:00 +0000

We have to change our ora user $ORACLE_HOME from /u02/oracle to /u02/oracle/11.1.0. Here are steps which I have followed and done this.

1. Run adpreclone.pl on dbtier as oracle user

perl adpreclone.pl dbTier

2. Shutdown down database and listener.

3. mv all files from /u02/oracle to /u02/oracle/11.1.0

4. run adcfgclone.pl for binaries only to avoid db creation

perl adcfgclone.pl dbTechStack

It will ask you certain info, please provide it accordingly.

5. update profiles from $OH/appsutil/install/

sqlplus "/ as sysdba" @adupdlib.sql so

6. change directory to $OH/appsutil/clone/bin

perl adcfgclone.pl dbconfig $OH/appsutil/context_file.xml

7. run autoconfig on appstiers too.

8. Start all services and check.

Happy Troubleshooting !!!

MWA server in R12

noreply@blogger.com (Sundeep Dubey) — Sun, 18 Oct 2009 13:10:00 +0000

If you are suppose to run MWA server in R12 you might feel like something is missing here from 11i. So just read it and you will be able to handle it.

Prior to R12:

1. The MWA server startup files were located under "$MWA_TOP/bin" directory.

2. The MWA Server configuration files were located under "$MWA_TOP/secure" directory.

In R12

1. Scripts are under $INST_TOP/admin/scripts

2. Config files are under $INST_TOP/admin/install

Here are few commands and which we used to use in 11i, this is how we are going to use them in R12:

1. Startup the MWA server

mwactl.sh start

2. Shutdown the MWA server

mwactl.sh -login adminuid/pwd stop_force

3. Startup MWA Dispatcher

mwactl.sh start_dispatcher

4. Shutdown MWA Dispatcher

mwactl.sh stop_dispatcher

By default these scripts were not included in adstpall.sh and adstrtal.sh scripts. To add them enable

1. s_other_service_group_status

2. s_mwastatus

in context file and run autoconfig.

If you want to check port number for mwa. Look at following paramters in context file:

1. s_mwaPortNo.

2. s_mwaTelnetPortNo.

Happy Troubleshooting !!!

Rename Temp datafiles in 11G

noreply@blogger.com (Sundeep Dubey) — Sun, 18 Oct 2009 12:31:00 +0000

Sorry all its big gap I took to post any update here. Biggest problem is office security. Now I can't access my blog site from office network. So I have to wait till I get some time at home. And as you might know this is festival season here in India to took so long. Any ways come to the point. Recently we have installed R 12.1.1 fresh installation on solaris and had to change many things after install. Temp datafiles location is one of them. But I got confused initially. Becuase when I was checking tablespace_name, file_name from dba_temp_files, I was getting TEMP1 and TEMP2 as tablespace names. But when I checked temporary_tablespace in dba_users I saw it as TEMP. So was worried how come? where is the link between these two miss matched things. Temporary Tablespace Group were the cause for it. Or I would say solution for this.

Here is the brief information about them:

1. A temporary tablespace group must consist of at least one tablespace. There is no explicit max number of tablespaces.

2. If you delete all members from a TTG, the group is automatically deleted as well.

3. When you assign a temporary tablespace to a user, you can use the TTG name instead of the actual temporary tablespace name. you can also use the TTG name when you assign the default temporary tablespace for the database.

This is how we can create one TTG;

1. Create Temporary Tablespace temp1 tempfile '/u02/oracle/temp1_01.dbf' size 100M tablespace group temp;

2. Add more via : Alter tablespace temp2 tablespace group temp;

3. Setting group as default temporary tablespace for the database:

Alter database Default Temporary Tablespace Temp;

4. Query dba_tablespace_groups for information about them.

------------------------------------------------------------------

Now coming to our topic how to rename tempfiles:

1. Create another temp tablespace to make it default.

Create Temporary Tablepspace temp_sd tempfile '/u02/oracle/temp/temp_sd_01.dbf' size 2000M;

2. Make this as default Temp Tablespace:

Alter database default temporary tablespace temp_sd;

3. Check database properties to be sure:

Select property_name, property_value from database_properties where property_name like '%Default_TEMP_TABLESPACE%';

4. Alter database tempfile '/u02/oracle/temp01.dbf' offline;

5. Copy file from old location to new location using OS commands.

cp /u02/oracle/temp01.dbf /u02/oracle/temp/temp01.dbf

6. Rename file

Alter database rename file '/u02/oracle/temp01.dbf' to '/u02/oracle/temp/temp01.dbf';

7. Bring it online

Alter database tempfile '/u02/oracle/temp/temp01.dbf' online;

8. Again change the default:

Alter database default temporary tablespace temp;

9. Drop the newly create temp tablespace

Drop Tablespace temp_sd including contents and datafiles;

Happy Troubleshooting !!!

Happy Diwali !!!

Getting Tech Stack Information on 11i and R12

noreply@blogger.com (Sundeep Dubey) — Thu, 17 Sep 2009 05:16:00 +0000

We have to collect this information whenever boss ask :-). In Oracle Apps 11i and R12 instances we have txkInventory.pl and TXKScript.pl which can help us to get this information.
As applmgr user execute below command:
perl $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPL_TOP -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$OA_HTML/apps_techinfo.html

As Oracle user execute below command:
perl $ORACLE_HOME/appsutil/bin/TXKScript.pl -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$ORACLE_HOME/appsutil/temp/oracle_techdb.html

Happy Troubleshooting !!!

R12 (12.1.1) Installed on Solaris 10

noreply@blogger.com (Sundeep Dubey) — Wed, 12 Aug 2009 05:23:00 +0000

After some initial struggle with server we were able to finish our 12.1.1 installation on Solaris 10.
I am trying to list mistakes we made or issues we encountered.
1. We started 12.1.1 on Solaris 8, which was mistake. As 11g database is not certified on Solaris 8.
2. After moving to Solaris 10, our team forget to bounce Server after setting Kernal parameters.
3. As we have one disk and it was damn slow, so took 17 hrs to complete.
4. Make sure you have atleast 250GB free space for Vision instance installation.
5. 47 GB binaries will take and 60GB stage area.

Anyways after creating stage area from edelivery binaries, we were able to complete Vision instance installation.

Happy Troubleshooting !!!

R12 12.1.1 Installation on Solaris

noreply@blogger.com (Sundeep Dubey) — Mon, 27 Jul 2009 04:30:00 +0000

You might be wondering where this guy disappear. I am getting released from my current engagement that's why busy in transition and other works. And got another engagement in hand for R12 (12.1.1) installation on solaris. I am working with team to get this done. Will update all with the status and issues faced.
Until then Stay tuned.
Happy Troubleshooting.

IDM 10.1.4.3 installed

noreply@blogger.com (Sundeep Dubey) — Tue, 30 Jun 2009 09:21:00 +0000

Just to update everyone those who ere waiting for IDM 10.1.4.3 patchset results. We have successfully implemented this is our test environment and the issue which were reported during 10.1.4.0.1 installation with Kerberose related to "Administer Partner Application" is resolved in 10.1.4.3 patch. Moreover one more issue we found in older version, there were huge error files generation in $ORACLE_HOME/hostname_domain.com_IDM10143/sysman/recv/errors which we didn't found in this patch. I guess those who were waiting can install this one.

Happy Troubleshooting !!!

Responsibility not Appearing

noreply@blogger.com (Sundeep Dubey) — Thu, 11 Jun 2009 13:25:00 +0000

I have some strange issue with one user. This user has 4 resposibilites assigned to him in dev, due to security issue we have to end his two responsibility. Now user want this "Sysadmin Inquiry" responsibility again. I did end date and told him about that. Now after 24 hrs he came back and asked what is the status. That means he is not able to see this responsbility under his ID.I did enable and disable other responsibilities under his id, he can see immediate impact of that. But not this one. Now for checking I did assigned this responsibility to my id and can see it without any issue. I did ran all these
1. Navigate to the define user form (FNDSCAUS).
2. Query up the user (that is having the issue) and insure the responsibility is NOT end dated.
3. END DATE the USER (not the responsibility) and save the record.
4. UNEND DATE the USER and save the record.
5. Run the request 'Sync responsibility role data into the WF table'.6
. Run the request "Synchronize WF LOCAL tables".
7. Run the request "Workflow Directory Services User/Role Validation" (with parameters 10000, yes, yes, yes).
8. Have the user log off and back on and verify that the responsibility appears.
but no help. Not sure why only one responsbility is creating problem for this user. After this got good help from one of the friend in Oracle Forum, he suggested to read note 466135.1, Man it reaaly helped a lot to resolve this issue. Thanks Hussein

Happy Troubleshooting !!!

ActiveChgImp:Error in Mapping EngineODIException: DIP_GEN_INITIALIZATION_EXCEPTION

noreply@blogger.com (Sundeep Dubey) — Tue, 19 May 2009 10:34:00 +0000

Seems like this week will go to resolve OID issues for us. We had another issue in our dev OID setup, where synchronization with AD is not happening and log says this:
=======================================================
ActiveChgImp:Error in Mapping EngineODIException: DIP_GEN_INITIALIZATION_EXCEPTION
java.lang.NullPointerException
at oracle.ldap.odip.util.DirUtils.getLastChgNum(DirUtils.java:48)
at oracle.ldap.odip.gsi.LDAPReader.initAvailableChgKey(LDAPReader.java:884)
at oracle.ldap.odip.gsi.LDAPReader.initialise(LDAPReader.java:250)
at oracle.ldap.odip.engine.AgentThread.readerInitialise(AgentThread.java:460)
at oracle.ldap.odip.engine.AgentThread.mapInitialise(AgentThread.java:509)
at oracle.ldap.odip.engine.AgentThread.execMapping(AgentThread.java:375)
at oracle.ldap.odip.engine.AgentThread.run(AgentThread.java:237)
ActiveChgImp:about to Update exec status
Updated Attributes
orclodipLastExecutionTime: 20090518024937
orclodipConDirLastAppliedChgNum: 0
orclOdipSynchronizationStatus: Mapping Failure, Agent Execution Not Attempted
orclOdipSynchronizationErrors: Profile Initialization Failure
============================================================
Metalink note: 276481.1 does help to understand the issue and that is with dipassistance. After checking $ORACLE_HOME/ldap/odi/conf/ActiveChgImp.cfg file we realized the issue. As this is not the correct file. To make is correct. I have to stop our second odiserv instance and disable ActiveChgImp profile from oidadmin.
Once that is done. We again modified the Import Connector profile with correct cfg file. and enable ActiveChgImp profile and start odiserv instance and vola now log file looks better.

Happy Troubleshooting !!!

APP-FND-01564: ORACLE error 31202 in changepassword

noreply@blogger.com (Sundeep Dubey) — Mon, 18 May 2009 11:08:00 +0000

We had this issue in our production this morning. Our dedicated sysadmin team were not able to change any user password from frontend. They were receiving following error "ORA-20001: Unable to call fnd_ldap_wrapper.update_user".
Issue came to us and we tried changing via FNDCPASS and in log we saw following message"
===============================================================
APP-FND-01564: ORACLE error 31202 in changepassword
Cause: changepassword failed due to ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. Password Policy Error :9000: GSL_PWDEXPIRED_EXCP :Your Password has expired. Please contact the Administrator to change your password.ORA-06512: at "APPS.FND_LDAP_UTIL",.
The SQL statement being executed at the time of the error was: begin :r := fnd_web_sec.change_password(:u,:p); end; and was executed from the file &ERRFILE.
================================================================
Message was clear that we had some issue with our OID account. Metalink note id 401836.1 helped us to crack this issue. Issue was appsDN password in OID got expired and we have to reset the password. Here is how to resolve this error:

To implement the solution, login to the E-Business Suite database as the 'APPS' user and execute the following steps:
(1) SQL> select fnd_preference.get('#INTERNAL', 'LDAP_SYNCH','USERNAME') Apps_Instance_OID_Account from dual;
Example:
APPS_INSTANCE_OID_ACCOUNT
-------------------------------------------------------------------------------- orclApplicationCommonName="MYAPPSINSTANCE",cn="EBusiness",cn=Products,cn=OracleContext,dc=mycompany,dc=com (2) SQL> select fnd_preference.eget('#INTERNAL', 'LDAP_SYNCH','EPWD','LDAP_PWD') Apps_Password from dual;
Example:
APPS_PASSWORD
-------------------------------------------------------------------------------- mypassword1
(3) Now test the above credentials with ldapbind,
Example:
> ldapbind -h -p -D orclApplicationCommonName="MYAPPSINSTANCE",cn="EBusiness",cn=Products,cn=OracleContext,dc=mycompany,dc=com -w mypassword1 invalid credentials
Password Policy Error :9000: GSL_PWDEXPIRED_EXCP :Your Password has expired.
Note: If above returns the same password expired message, then login to oidadmin (Oracle Directory Manager) and expand Entry Management and navigate to the Apps User DN entry expanding backwards on the DN, ie:
+ dc=com
+ dc=mycompany
+ cn=OracleContext
+ cn=Products
+ cn="EBusiness"
+ orclApplicationCommonName="MYAPPSINSTANCE"
And type the same password returned from the sql query on step 2 as value for userpassword attribute, then click Apply.
(4) Then retest the previous ldapbind and it should now work:
> ldapbind -h -p -D orclApplicationCommonName="MYAPPSINSTANCE",cn="EBusiness",cn=Products,cn=OracleContext,dc=mycompany,dc=com -w mypassword1
bind successful

Happy Troubleshooting !!!