Happy New Year

You might be wondering where I have been for so long, Officially we were busy in our Dev server movement to some other location internally and at personal front we blessed with a Baby Boy on Christmas Eve (24th Dec). So was busy or would say too busy to look at this site. Today got some time so thought of sharing good news with you all and Wish Happy New Year to all of you out there. On Funny side :
"May all your troubles last as long as your New Year's resolutions."

Happy Troubleshooting !!!

What is significance of 0 & Y while using ( FNDCPASS, FNDLOAD, WFLOAD .....) .

Another in a row for Basics:

-'0' refers to the ( Concurrent Program ) request ID , as Invocation isNOT from the concurrent program SRS where in each Concurrent Request is assigneda non-zero id.

- 'Y' is the value for a flag that indicates the method of invocation is directly from the command-line not from the SRS.

Note: SRS (Standard Request Submission)
Happy Troubleshooting !!!

Meaning of Codes in FND_CONCURRENT_REQUESTS table?

Back to Basics, we use this daily and forget what they mean internally. so Today I thought of going back to Apps DBA Basics and giving meaning of the codes in the status_code and phase_code columns of the FND_CONCURRENT_REQUESTS table?

STATUS_CODE Column:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running

Happy Troubleshooting !!!

Adding Datafile in Standby Database

We are having our DR/Standby database getting sync with Production instance. Whenever we add any datafile to Production we have to manually add datafile in Standby. Yup that's the process and we have to do that, but would like to share with you all what needs to be done step by step.:

1. Add a datafile in Production "alter tablespace TBS add datafile 'Path/file.dbf' size 2000M;"
2. Wait for log switch or do it.
3. Move the arch file to standby/DR location.
4. Recover standby "Recover database using backup controlfile until cancel;"
5. Above step will fail and give you one unamed file#. You can query the tablespace name etc like "select TB.NAME,DF.NAME from v$tablespace TB , v$datafile DF where TB.TS#=DF.TS# and DF.File#=345;"
6. Create datafile in Standby/DR instance with unamed file like "Alter database create datafile '/OH/dbs/UNNAMED00527' as '/Path/file.dbf';"
7. Recover standby/DR again "Recover database using backup controlfile until cancel;"

Happy Troubleshooting !!!

Updating FND_USER

We had some requirement to disable some good amount of users. So it will not be a wise idea to get into Oracle and end date each and every user. So next option is to update from backend. But our worry is whether should we just update end_date column for all users and forget. That raised a question about what if we have to enable them again, what will happend to WF table sync. Then we used FND_USER_PKG details provided in Metalink Note : 364898.1 and we use it in cursor and we go the desired result. Here is what we did :

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.DisableUser(all_user.user_name);
commit;
end loop;
End;

Happy Troubleshooting !!!
Note: Many Thanks to "hsawwan" for this.

Sending file from unix as email attachement

We as DBA's need to send files from server to users, mainly trace files etc. So it would be great if you can get some way to send files as email attachement. I hope following command which I use in solaris box will help many of us:

uuencode File_Name File_Name : (Kindly use Pipe sign instead off colon) mailx -s "server file" sudubey@gmail.com
You will receive File which you have mentioned in File_Name as email attachment.

Happy Troubleshooting !!!

IDM 10.1.4.2 with WNA - Kerberose

As I have mentioned earlier that we would like to upgrade ourself from IDM 10.1.4.0.1 to IDM 10.1.4.2 as we had some issues with accessing Partner Applications Administration page. Although WNA works well with older version. We had major issue with WNA once we upgraded to IDM 10.1.4.2, it actually stops working and there are no workarounds as per Oracle Support, this is what they has to say
"Current versions of OID are only certified with the shipped JDK version.Certification for JDK version 1.4.2_18 and OID is expected to be available in future AS/OID 10.1.4.3.0 Patchset (not ETA available)."

So we have to revert back to same working old version 10.1.4.0.1 till we have official release of 10.1.4.3.0

Happy Troubleshooting !!!

Force All Application users to Change their password

This is available starting in RUP4.
Requires Patch 4676589 ATG RUP 4.
The script to expire all passwords in the fnd_user table is $FND_TOP/patch/115/sql/AFCPEXPIRE.sql.
It can be executed from SQL*Plus or as a Concurrent Program: sqlplus -s APPS/ @AFCPEXPIRE.sql
or Submit concurrent request: CP SQL*Plus Expire FND_USER Passwords
This script sets the fnd_user.password_date to null for all users which causes all user passwords to expire. It can also be run as a SQL*Plus concurrent program.
The user will need to create a new password upon the next login.

Happy Troubleshooting !!!

No Responsibilities Are Available For The Given Connection Details At Login

We had some wierd issue today after upgrade. Some of the uses complaining after upgrading to Discoverer version 10.1.2.2 they were not able to login and getting error "No Responsibilities Are Available For The Given Connection Details At Login". But this strange for us too as some of them able to login and some not. I did test their login, responsibility end date etc etc. Then thought of checking some Bug and came to Metalink. And What its a bug, it has mentioned in Note 466188.1, and suggested to apply patch 6146747 (47MB). But we do not have time to apply this patch when production is live. So need to find workaround, guess what ???? nothing big, just added "Notification" responsibility to all users having only one responsibility against their username. So we made fool of Bug and it worked :-)

Happy Troubleshooting !!!

BIB-7000 Cannot access X Server

We had this issue while we did 11.5.10.2 upgrade last week. It was a hectic weekend, but with help of my patner Apps DBA we made it easy and delivered the instance 11hrs before actual outage. So come to the issue. I have mentioned in of the my previous Post regarding Xvfb setup in our production env admin node. Display variable is working fine adnd we dint' have any issues with DISPLAY, but getting "BIB-7000 Cannot access X Server" message while accessing graphs in Workflow pages from OAM.
While investigating realized this error was coming from Application tier where we do not have setting for Display, or I can say was set to somewhere else which was not working. As we didn't want to run Autoconfig again, what we did. We changed DISPLAY variable in jserv.properties file to our admin node Display setting and bounce Apache. Vola it worked.

Happy Troubleshooting !!!

WNA Config with IDM version 1.4.0.1

Last part we missed in our 10g iAS integration setup was configuring WNA (Windows Native Authentication) using Kerberose. Which help you to avoid and challenge related to credentials. Here we go:
1. Have the Solaris administrator update the krb5.conf file as below
[libdefaults]
default_realm = DOMAINNAME.COM
clockskew = 300

[realms]
DOMAINNAME.COM = {
kdc = activedirectoryservername.com
kdc = activedirectoryservername.com
}
[domain_realm]
.domainname.com = DOMAINNAME.COM
domainname.com = DOMAINNAME.COM

2. Have the AD administrator create a service account for the OracleAS Single Sign-On server in Microsoft Active Directory. AD administrator will then need to create a keytab file for the server, and map the service principal (the server) to the account name. The keytab file stores the server's secret key. This file enables the server to authenticate to the KDC. The service principal is the entity, in this case, the single sign-on server, to which the KDC grants session tickets. The Keytab file is generated using KTPASS command executed on the AD host.

Ktpass.exe /princ HTTP/@DOMAINNAME.COM
/mapuser
/pass
/mapop set
+desonly
/ptype KRB5_NT_PRINCIPAL
/kvno 1
/crypto DES-CBC-CRC
/out sso.keytab

The AD Administrator will provide the keytab file which needs to be “binary” ftp’d or copied to SSO mid-tier (where OC4J_SECURITY runs) in $ORACLE_HOME/j2ee/OC4J_SECURITY/config directory.

3. Backup following SSO files :
$ORACLE_HOME/sso/conf/policy.properties
$ORACLE_HOME/j2ee/OC4J_SECURITY/config/jazn.xml
$ORACLE_HOME/j2ee/OC4J_SECURITY/config/jazn-data.xml
$ORACLE_HOME/j2ee/OC4J_SECURITY/applications/sso/web/WEB-INF/web.xml
$ORACLE_HOME/j2ee/OC4J_SECURITY/application-deployments/sso/orion-application.xml
$ORACLE_HOME/opmn/conf/opmn.xml

4. Configure WNA using ossoca.jar

$OH/jdk/bin/java -jar $ORACLE_HOME/sso/lib/ossoca.jar wna \
-mode sso \
-oh $ORACLE_HOME \
-ad_realm DOMAINNAME.COM \
-kdc_host_port \
-verbose

Check login to https://hostname.domainname.com:4443/pls/orasso and click login. It should take you with any challenge. If it works you are done.

Happy Troubleshooting !!!

X- Windows

I have encountered the issue which I have faced almost 6-7 yrs back. We have to do the installation of 10g iAS on one of the production server and we are not able to run any X-windows like VNC etc on that box due to missing packages on that server. But we do not have time to look around for unix guys and get this fixed.
So what are the options available to finish this task. This is how you can proceed. Look at the box where VNC is running, run the following from this box to the one where vnc is not running:
ssh -X targethost -l username
it will prompt for password for that user and Vola you are in. You have to have ssh daemon running on target host otherwise it will not work. We completed are job and hope this helps others to do their job on time.

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Implementation-5

Now we are going to register Oracle Ebiz Instance with recently setup OID server. Just before we move into apps grab some information from IDM host for orasso password via following command:

$ORACLE_HOME/bin/ldapsearch -h hostname.domainname.com -p 389 -D "cn=orcladmin" -w password -b "cn=IAS,cn=Products,cn=OracleContext" -s sub -v "OrclresourceName=orasso" grep orclpasswordattribute

Once you get the orasso passsword from OID post login to Application tier of Oracle EBiz and move to $FND_TOP/bin and add $IAS_ORACLE_HOME/lib under $LD_LIBRARY_PATH variable to avoid any errors while registering.

Run this commmand and provide information required: $FND_TOP/bin/txkrun.pl -script=SetSSOReg
Enter the host name where Oracle iAS Infrastructure database is installed ?
Enter the Oracle iAS Infrastructure database port number ?
Enter the Oracle iAS Infrastructure database SID ?
Enter the LDAP Port on Oracle Internet Directory server ?
Enter Oracle E-Business apps database user password ?
Enter Oracle iAS Infrastructure database ORASSO schema password ?
Enter Oracle E-Business SYSTEM database user password ?
Enter E-Business Suite existing SSOSDK schema password or choose a password to use with the new SSOSDK schema if the schema does not exist ?
Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ?
Enter the instance password that you would like to register this application instance with ?

Once the required all aove information is provided you will see the scroll up with:
----------------------------------------------------------------------------------------
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS*** Log File = $OAD_TOP/rgf/SID/sso/txkSetSSOReg_Thu_Aug_28_04_31_58_2008.log
Program : $FND_TOP/bin/txkSetSSOReg.pl started @ Thu Aug 28 04:32:53 2008
*** Log File = $OAD_TOP/rgf/SID/sso/txkSetSSOReg_Thu_Aug_28_04_31_58_2008.log
######################## WARNING ########################################This application works with SSOSDK version 9.0.2 or higher. If lower version(3.0.9) of SSOSDK was installed in your system and you have a registeredpartner application, this process will remove the 3.0.9 version of the SSOSDKschema and install the 9.0.2 version.
######################## WARNING ########################################
Beginning input parameter validation for SSO registration. Beginning loading SSO SDK into database if necessary. Loading of SSO SDK into database completed successfully.Input parameter validation for SSO registration completed.Beginning input parameter validation for OID registration.Input parameters validation for OID registration completed.
BEGIN SSO REGISTRATION:
Beginning to register partner application.Partner application has been registered successfully.Single Sign-On partner application registered successfully.
BEGIN OID REGISTRATION:
Beginning to register Application and Service containers if necessary.Application and Service containers were created successfully.Beginning to register application in Oracle Internet Directory.Registration of application in Oracle Internet Directory completed successfully.Beginning to register instance password in Oracle Internet Directory.Registration of instance password in Oracle Internet Directory completed successfully.Beginning to test application registration in Oracle Internet Directory.Testing of application registration in Oracle Internet Directory completed successfully.Beginning to register provisioning profile in Oracle Internet Directory.Registration of provisioning profile in Oracle Internet Directory completed successfully.Application is now registered successfully with provisioning in Oracle Internet Directory.End of $FND_TOP/bin/txkSetSSOReg.pl : No Errors encountered
--------------------------------------------------------------------------------------

Great you are done registering your apps instance as patner application with OID. Now if you try to access your Apps instance you will challenged by SSO. you have to provide your AD username and password to get in.

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Implementation-4

As a next step we have to configure External Authentication Plug-In:
User passwords are maintained in Active Directory. For every AD user in enterprise LDAP directory the synchronization connector will maintain a shadow entry in Oracle Internet Directory of the type “orclADUser”.
For such users Oracle Internet Directory will need to forward authentication request to Active Directory.
On the host where OID component is installed execute the following command:
$OH/bin/dipassistant ea \
-h \
-p \
-D cn=orcladmin \
-w \
-t AD

The following configuration parameters need to be set:

· Active Directory host and port number – AD LDAP host and port,
· Active Directory failover configuration - Secondary AD LDAP host and port,
· Invocation Naming Context – OID user container
. Second failover Activer Directory - If you have.

Once this is done, you can test https://hostname.domainname.com:389/pls/orasso and login with your AD username and password and you will be able to login. Vola Setup is done for OID and AD Integration. Now in next note we will do the setup at Ebiz Apps end to register that with Patner Application with OID.

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Implementation-3

Next step after we are done importing users is to Enable ActiveChgImp profile. This is how we do in IDM version 1.4.0.1:

Access OID administration tool as “orcladmin” super-user. Navigate to “Integration Server” -> “Configuration Set 1”. Select the import profile “ActiveChgImp” and click edit. This should display the profile attributes. Enable the profile and save the modified profile

But in IDM 1.4.2, here are the steps to follow:
====================
Profiles are now managed with the Oracle Directory Integration and Provisioning Server Administration tool ...e.g.. dipassistant -gui
To incorporate these changes into the upgraded OID please do the following:
1. launch the admin tool (dipassistant -gui) and navigate to Connector Group Management and expand the tree. Notice that there are two entries:
- defaultgroup
- configset1
2. Expand configset1 to see the old 10.1.4.1 synchronization profiles.
3. Since a 'profile group' must now be supplied when starting odisrv it is recommended to rename this 'configset' as it will become confusing when starting the server. To rename the profile group:
- highlight configset1 and right click the mouse, then select rename
Give it a new name, for instance: Group1 and click OK
4. The defaultgroup contains NO profiles after the upgrade so one may wish to (re)associate any profiles previously configured and enabled to become part of the default group. To do so:
- expand the Group1
- highlight the profile to be made part of default group, then click Dissociate Profile (you will be prompted to confirm, note that when confirmed it will disappear from the list)
- highlight the defaultgroup and click Associate Profile
- highlight the profile from the list and click Select (the new profile will now appear in the defaultgroup)
NOTE: One MUST always Dissociate a profile before Associating it with a new group.
5. Start the odisrv using the additional grpID flag: For example:
oidctl connect=orcl server=odisrv instance=2 configset=1 flags="host=jdsmith-us port=13060 grpid=defaultgroup debug=63" start

Check the logs under $ORACLE_HOME/ldap/odi/log directory for synchronization errors in “ActiveChgImp.trc” and “ActiveChgImp.aud”

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Implementation-2

Now we are going to modify import connector profile so that we can import users from AD to OID.
1. Create the “mapping rules” listed her and save to a file named “ActiveChgImp.map”
--------------------------------------------------------------------------------------------
DomainRules
OU=OU_MYUSERS,DC=corp,DC=mygrp,DC=com:cn=adusers,cn=users,dc=corp, dc=mygrp, dc=com:
###
AttributeRules
# attribute rule common to all objects
objectguid: :binary: :orclobjectguid:string: :bin2b64(objectguid)
ObjectSID: :binary: :orclObjectSID:string: :bin2b64(ObjectSID)
distinguishedName: : : :orclSourceObjectDN: :orclADObject
# attribute rule for mapping windows organizationalunit
ou: : :organizationalunit:ou: : organizationalunit
# attribute rule for mapping directory containers
cn: : :container: cn: :orclContainer
# attribute rule for mapping directordomains
dc: : :domain: dc: :domain
# USER ENTRY MAPPING RULES
# attribute rule for mapping windows LOGIN id
sAMAccountName,userPrincipalName: : :user:orclSAMAccountName: :orclADUser:sAMAccountNametrunc(userPrincipalName,'@')
# attribute rule for mapping Active Directory LOGIN id
userPrincipalName: : :user:orclUserPrincipalName: :orclADUser:userPrincipalName
# Map the userprincipalname to the nickname attr by default
samAccountName,userPrincipalName: : :user:uid: :inetorgperson:sAMAccountNametrunc(userPrincipalName,'@')
# Assign the userprincipalname to Kerberos principalname
# userPrincipalName: : :user:krbPrincipalName: :orcluserv2:trunc(userPrincipalName,'@')+'@'+toupper(truncl(userPrincipalName,
'@'))
samAccountName: : :user:krbPrincipalName: :orcluserv2:samAccountName+'@'+'DOMAINNAME.COM'
# This rule is mapped as SAMAccountName is a mandatory attr on AD
# and sn is mandatory on OID. sn is not mandatory on Active Directory
sn,SAMAccountName: : :person:sn: : person:snSAMAccountName
# attributes to map to cn - normally this is the given name
cn: : :person:cn: :person:
# attribute rule for mapping entry and to create orclUserV2
# There should be a mapping rule with orcluserv2 objectclass
# without which the PORTAL may not function properly
givenName: : :user:displayName: :inetorgperson:
employeeID: : :user:employeeNumber: :inetOrgPerson:
physicalDeliveryOfficeName: : :user:physicalDeliveryOfficeName: :organizationalPerson:
title: : :user:title: :organizationalPerson:
mobile: : :organizationalperson:mobile: :inetorgperson:
telephonenumber: : :organizationalperson:telephonenumber: :inetorgperson:
facsimileTelephoneNumber: : :organizationalperson:facsimileTelephoneNumber: :inetorgperson:
l: : :user:l: :organizationalperson:
# mail needs to be assigned valid value for default settings in DAS
userPrincipalName: : :user:mail: :inetorgperson:
# GROUP ENTRY MAPPING RULES
cn: : :group:cn: :groupofuniquenames:
# displayname needs to be assigned a valid value for default settings on DAS
SAMAccountName: : :group:displayName: :orclgroup:
# Description needs tobe assigned a valid value for default settings on DAS
Description: : :group:Description: :groupOfUniqueNames:
member: : :group:uniquemember: :groupofUniqueNames:
managedby: : :group:owner: :orclprivilegegroup:
sAMAccountName: : :group:orclSAMAccountName: :orclADGroup:
-----------------------------------------------------------------------------------------
2. Create the “Import Profile Configuration” listed here and save to a file named “ActiveChgImp.cfg”
-----------------------------------------------------------------------------------------
[INTERFACEDETAILS]
Package: gsi
Reader: ActiveChgReader
SkipErrorToSyncNextChange: true
SearchDeltaSize: 500
----------------------------------------------------------------------------------------
3. Replace the “-h” OID host and “-p” port in the command below and execute:

$ORACLE_HOME/bin/dipassistant modifyprofile \
-h hostname.domainname.com \
-p 389 \
-D cn=orcladmin \
-w \
-profile ActiveChgImp \
odip.profile.condiraccount="ADUSERname" \
odip.profile.condirpassword= \
odip.profile.condirurl="activedirectoryhostname:389" \
odip.profile.configfile="ActiveChgImp.cfg" \
odip.profile.condirfilter="((objectclass=organizationalunit)(&(objectclass=user)(!(objectclass=computer))))" \
odip.profile.mapfile="ActiveChgImp.map"
4. On the IDM host where OID component is installed, replace the “-h” OID host and “-p” port in the command below and execute:

$ORACLE_HOME/bin/dipassistant bootstrap \
-h \
-p \
-D “cn=orcladmin” \
-w \
-profile ActiveChgImp

Check the bootstrap log file located in $ORACLE_HOME/ldap/odi/log directory for errors. If no errors Vola you are done importing all users in OID.

Watch out my next post for modifying ActiveChgImp profile. As it has some new steps in IDM version 1.4.2.0.

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Implementation

I guess you might be kept on waiting for my update. But here I come. Now as a next step we will create adusers container in OID so that we can load users from AD to OID under this realm.
1. Create a file “create_aduser_container.ldif” containing following lines:
dn: cn=adusers,cn=users,dc=corp, dc=mygrp, dc=com
cn: adusers
objectclass: top
objectclass: orclContainer
description: Container for Enterprise AD Users

2. On the IDM host, execute the following command after replacing the “-h” oidhost and “-p” oidport parameter:

$ORACLE_HOME/bin/ldapadd \
-c -v \
-h \
-p \
-D “cn=orcladmin” \
-w \
-f create_aduser_container.ldif

With the above step you can see container adusers in OID.

to be continue .......

Can't Publish Reports in ADI 7.2

We are using Oracle names server to connect to our database. We have issue last week regarding Publishing reports via ADI 7.2 . Getting following error :
"An error occured while attempting to establish an Applications File Server connection.There may be a network configuration problem, or the TNS listener may not be running.Nodename : Hostname"
It is clear something wrong with Apps listener, we tried bouncing it and Run one report like "Active Users" and tried viewing output. It was working, but still ADI Report Publishing was not working. We were clue less what is happening, we thought Names server might be an issue and we bounced Names server too, but that too didn't helped. As truely said if nothing works go and read the readme. We went and look at how ADI select FNDFS values, this is how
"ADI selects the node_name from the FND_CONCURRENT_REQUESTS table, appends that value to FNDFS_ and then looks in the TNSNAMES.ora file for 'directions' on what host to go
to and what port to ping for the FNDFS listener service"
So while checking for node_name value we got 2 records, one for virtual host and other one for physical host. That clears our issue, we looked at our concurrent manager node name. Again we have virtual host entry in that. For resolving this without downtime, we add one more entry in names server for virtual host FNDFS_. And vola it worked. But during weekend we took downtime to resolve concurrent manager node_name back to physical host.

Happy Troubleshooting !!!

XML Based Concurrent Program -SSLHandshake Error

We have one of the concurrent program which developed in XML and it has been used for printing checks in one of the location where we operates. This issue occure only in condition when we have combination of following setup :
1. Portal 3.0.9 login server (iAS 1.0.2.2.2).
2. Siteminder as authentication software.
3. SSL.
4. Concurrent Managers are running on Virtual Host.

The only solution which I found to overcome this situation to move our Concurrent Manger from Virtual Host to Physical Host. Other than that we do not have any other problem in using Virtual host.

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Important

I feel I took a long break here, but anyways I am back. In this section I would like to highlight some important things here which we have to take care before moving to implementation phase.
1. Understand the current Active Directory DIT (Directory Information Tree). This will help you in bulk load.
2. Get the information for AD Servers.
3. Get Read Only access to AD Servers.
4. Check for Kerberos Services are running on Server where you want to install it. Generally it is running on port 88.
5. Make sure you have fully qualified Domain Name entry added in /etc/hosts Like one below
100.2.70.234 hostname.domainname.com hostname
6. DO NOT IMPORT FND_USERS into OID.

Once you have above information with you, we are ready for Installation.

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Planning-2

I have to put a break in my earlier post to make you understand the important part of planning phase in deciding about how are we going to manage Users or we call it as User Management. Again I want to make it clear about the details, these are basis on our setup:

-----------------------------------------------------------------------------------
There are existing users in Active Directory and user accounts in Oracle E-Business Suite Rel11i installation. Users in Active Directory that access Oracle E-Business Suite Rel11i will need to exist in Oracle Internet Directory.
The process of provisioning users to Active Directory and Oracle E-Business Application Suite is in place at Our Setup. With Oracle Single Sign-On integration planned the provisioning process will remain the same. The diagram below shows Active Directory, Oracle Internet Directory and Oracle E-Business Suite and the provisioning and synchronization between the two directories.

User account will be created in Active Directory. The user account will be migrated to Oracle Internet Directory. A user account will need to be created in Oracle E-Business Application Suite using user enrollment method. Minimal information from user account in Active Directory will be unidirectionally migrated from Active Directory to Oracle Internet Directory.
The Oracle Internet Directory and Oracle E-Business suite accounts are linked via the auto-link feature.

Now we will move to another section or phase of this document. I will take a break for a while and will come back again with development phase.

Happy Troubleshooting !!!

Oracle 10g SSO Integration with E-Biz 11.5.10.2 - Planning

As I have mentioned earlier too in my Blog regarding our SSO Setup. Today I am trying to put that in this Blog so that whosoever planning to setup same in their environment will get benefitted out of it. I am going to put my document under few stages- one of them will going to be planning. Which I am going to describe in this post. This Phase will going to make you understand what is required in your setup and how to achieve this. I am going to take example of my setup and requirement, this might be possible that you too require this setup. But definately this will not going to be same for all of you.
-----------------------------------------------------------------------------------
Following is the requirement for us :
Integrate existing Oracle E-Business Suite Rel11i replacing existing Netegrity Siteminder as the authentication mechanism. Netegrity Siteminder uses enterprise Active Directory as the user directory. Install and configure Oracle10g Identity Management (10.1.4.0.1) to replace the Netegrity Siteminder as the authentication mechanism.
· Use Oracle10g Identity Management to provide Windows Native Authentication for a seamless user experience, and propagate windows authenticated user identity to Oracle10g Single Sign-On using kerberos protocol.
. Use Oracle10g Identity Management external authentication plug-in as the fallback mechanism for non-kereberos capable browsers authenticating to Oracle E-Business Suite Rel 11i applications.
-----------------------------------------------------------------------------------
To achieve the Oracle10g Single Sign-On integration the following activities need to be done:
1. Install Oracle10g Identity Management (10.1.4.0.1) server co-located with Identity MR database or distributed components.
2. Configure Oracle E-Business Suite Rel 11i using Metalink Note 233436.1 Section 6 “Implement Single Sign-On Support For the E-Business Suite”. This step configures Oracle E-Business to delegates authentication to Oracle10g Single Sign-On.
3. Bulk migrate / Synchronize minimal user information from enterprise Active Directory to Oracle Internet Directory using Oracle Directory Integration Platform (DIP).
4. Existing accounts in Active Directory and in Oracle E-Business Suite Rel 11i will need to have their FND_USERS.USER_GUID column values set to null. Enable the “Application SSO Auto Link User” or ‘link-on-the-fly’ feature to link the OID user identity with Oracle E-Business Suite Rel11i user account.
-----------------------------------------------------------------------------------
Now you might be wondering what is meant by User Account Auto-Link, Here you go :
1. In the single sign-on handshake between Oracle Single Sign-On and Oracle E-Business Suite, Oracle Single Sign-On returns the GUID of the authenticated user to Oracle E-Business Suite Release 11i.
2. Oracle E-Business Suite Release 11i uses the GUID to try to locate User’s Oracle E-Business Suite Release 11i application account.
3. If the user is trying to access Oracle E-Business Suite Release 11i for the first time, FND_USERS.USER_GUID column value is null. No application account will be found.
4. When the “Application SSO Auto Link User” is set to “Y”, Oracle E-Business Suite Release 11i will try to locate the user by the account name. If successful, it will link the two accounts by GUID. If not successful, it will redirect the user to “Account Link” page for username to associate.
-----------------------------------------------------------------------------------

Continue ........

iRec Resume Parsing issue - SSL Handshake Error

You might be wondering where I left for so many days with no update in the blog. We were busy with 10gR2 database upgrade for some off our Instance and other issues like getting error whenever we try to upload Resume via iRec Job Search. Our Jserve.log shows this (After setting Debug ) :

=======================================
javax.net.ssl.SSLException: SSL handshake failed: X509CertChainIncompleteErrat oracle.security.ssl.OracleSSLSocketImpl.startHandshake(Native Method)at oracle.security.ssl.OracleSSLSocketImpl.startHandshake(OracleSSLSocketImpl.java)at HTTPClient.HTTPConnection.getSSLSocket(HTTPConnection.java:1969)at HTTPClient.HTTPConnection.sendRequest(HTTPConnection.java:2938)at HTTPClient.HTTPConnection.handleRequest(HTTPConnection.java:2150)at HTTPClient.HTTPConnection.setupRequest(HTTPConnection.java:3561)at HTTPClient.HTTPConnection.ExtensionMethod(HTTPConnection.java:1570)at HTTPClient.HttpURLConnection.connect(HttpURLConnection.java:758)at HTTPClient.HttpURLConnection.getInputStream(HttpURLConnection.java:477)======================================================
We did the following to resolve this issue :

1. ftp server.crt to end user workstation
2. opened server.crt in MSIE
3. Exported each level as an x509 certificate.
==> Upper line = server1.cer
==>2nd line = server2.cer
==> 3rd line = server3.cer
4. ftp [ bin] server3.cer server2.cer server1.cer back to middle tier in $COMMON_TOP/admin/certs/apache/verisign directory
5. touch ca.crt
6. cat server3.cer server2.cer server1.cer >> ca.crt
7. copied the ca-bundle.crt from $IAS_ORACLE_HOME/Apache/Apache/conf/ssl.crt to $COMMON_TOP/admin/certs/apache/ssl.crt
8. copied ca.crt and server.crt from $COMMON_TOP/admin/certs/apache/verisign to $COMMON_TOP/admin/certs/apache/ssl.crt
9. Verified date on server.key in ssl.key directory
10. Bounced Apache [ adapcctl.sh [stopstart]
11. Tested upload resume > success

Happy Troubleshooting !!!

Oracle By Example (OBE)

I am using this site for quite sometime now. Thought of sharing with all who want to learn oracle products with examples. It really help to understand the product and its features with hands on examples with case study. Here is the site address :
http://www.oracle.com/technology/obe/start/index.html

Happy Troubleshooting !!!

Oracle Names Server - version 9i

Few weeks back we had some requirement to change our existing version of Oracle Names server running on 8i to upgrade to latest. I thought of going to 10g, but unfortunately for me Oracle discontinue Oracle Names server in this release with LDAP. These I looked for 9i, but for that too I got mixed resonse, whether this version has Names server or not. But I got the answers after some good response in Oracle Forum from experts (Thanks to them). This was the official last release which carries Names Server and along with that it has LDAP too. So I got what I want, but how to do that, I mean how to install that: Here is How:

1. Run the Installaer and choose custom installation, as in standard ones you will not get Names Server.
2. Once the installation is done follow this link to configure it : http://download.oracle.com/docs/cd/B10501_01/network.920/a96580/namingcf.htm#506800
3. Start names server and either you can upload all information about you instances to connect via tnsnames.ora or individually add one entry, by netmgr > choose names server > select Manage data > add > enter details > execute.
4. Once you are done with above, just modify your client sqlnet.ora to use your names server.
NAMES.DIRECTORY_PATH=(ONAMES)
5. You can test your entry via namesctl command too like this :
NAMESCTL > QUERY alias a.smd
where alias is the name of the alias being used to connect with from the client application
6. Do a tnsping from client PC too.

Happy Troubleshooting !!!

Oracle Security Issue - DMZ

We come across a issue in our External Tier (which was outside Firewall) related to security. We were able to view some of the statis pages via playing around with external tier URL. Which is a serious security threat to our environment. After Looking at parameters in url_fw.conf and metalink we came across to following Profile values:
FND Function Validation Level
FND Validation Level
Framework Validation Level
There default value is "error", as per metalink note: 390203.1 we must not change them. Althought we were running on 11.5.9, but ATG patchset 4, that's why we have these values in our system and that saves us.

Happy Troubleshooting !!!

Some Tricks with unix command 'Tar'

Most of you might be aware of this very usefull command tar in unix. As per manual it has been read as " an archiving program designed to store and extract files from an archive file known as a tarfile". Here I am not going to give you syntax for this command. But how can you make it more useful in your day to day work. i.e Online compression of tar backup.

For online compress of files with gzip
tar cvf - * : gzip > file_name.tar.gz

For testing backup files
gunzip < file_name.tar.gz : tar tvf -

For extracting files from above compress
gunzip < file_name.tar.gz : tar xvf -

For Online extracting of files from compress tar file, this way no need to uncompress it
zcat file_name.tar.Z : tar xvf -

Make sure to replace colon ":" with pipe sign

Happy Troubleshooting !!!

ADDED OR REMOVED RESPONSIBILITY NOT DISPLAYING

While doing upgrade from 11.5.9 to 11.5.10.2 we encounter one issue. i.e. Whenever we add or modifying responsibility end date, it is not showing in responsibility LOV or at Framework Homepage. We have to bounce apache to resolve this issue. That shows we are having issue with Cache. It takes a while to resolve this issue but following notes worth reading it from metalink i.e 455194.1, which explain "Diagnosing database invalidation issues with Java Cache for eBusiness Suite" Following are the main points on which this note works :

In order to identify any issues, there are several steps to go through:-
1) Gather background information about the environment
Oracle Support need to have a good idea about your environment architecture in order to understand the problem fully.
2) Check latest Java Cache patches have been applied
Release 11i ATG RUP3 patchset was the first release where Java Caching became more noticeably used throughout eBiz. Since then there have been some issues fixed in various patches. It is strongly recommended to apply these patches to resolve the known issues
3) Confirm all eBiz nodes have been configured correctly
It is sometimes possible to misconstrue the required setup, particularly in complex architectures such as through firewalls. It is important to confirm the current setup to ensure all configuration is correct
4) Identify nature and scope of the issue
It may be that Java Caching and Database invalidation itself is functioning correctly, but just one or two specific components are not working. Each different type of data is stored in its own "Component Cache" within the Java Cache and has its own code to control it. For example, profile option changes may be working, but responsibility changes are not. It is important to understand which parts work (if any)
5) Gather additional data to help identify the failure point
Once all the basic checks have been done, the next level of investigation is to to enable detailed logging information. This level of detail does require patient and thorough review to be able to identify the failure point.

Unfortunately due to the complex nature of Java Caching, this may take several iterations of testing and data gathering before any firm conclusions can be reached.

Happy Troubleshooting !!!

10giAS SSO Integration issue with 11i -- Followup

Hope you remember our running issue of Administer Patner Application with 10g iAS - IDM version 10.1.4.0.1 with WNA. Oracle metalink note : 466930.1 describe this issue and Oracle has introduced Patch 5983637 which is nothing but 10.1.4.2.0 version of IDM. We are going to apply this patch soon.

Happy Troubleshooting !!!

Deleting Patner application in IDM version 10.1.4.0.1

We are running IDM version 10.1.4.0.1 with WNA (Kerberose) which has a bug which doesn't allow users to Administer Patner Application. To overcome this we left with no other option but to run ssoreg.sh.
We have to use following commands to delete Patner Application from SSO
ssoreg.sh -oracle_home_path $ORACLE_HOME
-site_name TEST
-config_mod_osso TRUE
-mod_osso_url https://hostname.domainname.com:8000
-update_mode DELETE
-config_file $ORACLE_HOME/Apache/Apache/conf/osso/osso-https.conf
-virtualhost

Once you are done with above step, its no done yet. You have to re register your SSO Apache URL with following command:
ssoreg.sh
-oracle_home_path $ORACLE_HOME
-site_name TEST
-config_mod_osso TRUE
-mod_osso_url https://hostname.domainname.com:8000
-config_file $ORACLE_HOME/Apache/Apache/conf/osso/osso-https.conf
-virtualhost

Run the command
$ORACLE_HOME/opmn/bin/opmnctl stopall
followed by
$ORACLE_HOME/opmn/bin/opmnctl startall

Once you are done with this you will be able to access your SSO URL.

Happy Troubleshooting !!!

Killing All processes at once

Yesterday we come across a situation where we have to kill all look-a-like processes at once, this is what we followed to get rid of it.
kill -9 `ps -ef : grep -i applmgr : grep -i 889 : grep -v grep : awk '{print $2}'`
ps -efw : grep -i applmgr : grep : grep -v grep : awk '{print "kill -9 "$2}'

Note: Kindly replace colon (:) with pipe sign
Happy Troubleshooting !!!

adapcctl.sh: exiting with status 3

One of the issue reported after 11.5.10.2 upgrade. Whenever we try to start Apache it is exiting with status code 3.

$ adapcctl.sh start
adapcctl.sh version 115.54
Apache Web Server Listener is not running.
Starting Apache Web Server Listener (dedicated HTTP in SSL mode) ...
Apache Web Server Listener (PLSQL) is not running.
Starting Apache Web Server Listener (dedicated PLSQL) ...

adapcctl.sh: exiting with status 3

Here is what adapcctl.txt says:

================================================================================

Apache Web Server Listener is not running.

Starting Apache Web Server Listener (dedicated HTTP in SSL mode) ...

/oracle/EDEV/oradev/iAS/Apache/Apache/bin/apachectl startssl: httpd started

Apache Web Server Listener (PLSQL) is not running.

Starting Apache Web Server Listener (dedicated PLSQL) ...

Syntax error on line 17 of /oracle/EDEV/oradev/iAS/Apache/modplsql/cfg/plsql_pls.conf:

Cannot load /oracle/EDEV/oradev/iAS/Apache/modplsql/bin/modplsql.so into server: ld.so.1: httpd: fatal: libclntsh.so.8.0: open failed: No such file or directory

/oracle/EDEV/oradev/iAS/Apache/Apache/bin/apachectl start: httpd could not be started
========================================================================

Metalink says modplsql.so might be corrupted or you might not be having libclntsh.so.8 etc etc. We tried all options but no results. It was erroring out like that only with status code 3. After digging more I realize LD_LIBRARY_PATH which was coming from adovars.env having link to old java 1.1.8. I have to remove those entries and source the env again. After that we were able to resolve this issue. But it was a nice experience which I would like to share with you all.

Happy Troubleshooting !!!

Unable to Copy files on unix

We have some weird issue few days back. My team mate reported issue that he was not able to copy files from backup location to target location for doing database refresh. As per him he was able to see files under backup location, but whenever he tries to copy files he gets an error. After I had a look I found the issue like this. Whenever you do ls -l you can see the files like:
filea.dbf
fileb.dbf
But when we issue a command cp filea.dbf target_location throws an error. moreover if you do cp file* target_location it worked. I got the issue, which means file has some zunk characters in its name. As its a common dba error while adding dbf files we add space. So I used command cp "filea.dbf " target_location, this time I gave file name under "" with one space after dbf. It worked, but problem didn't end here, we have to rename the datafile too as it has space in it at end.

Happy Troubleshooting !!!

Update User password field Disable in Apps after 10g SSO Integration

We encountered one issue after integration 10g iAS (IDM 1.4 + SSO) with our Oracle Application Instance 11.5.10.2. We were not able to change user password via form (FNDSCAUS). Navigation as follows Security > User > Define. Although we were able to change password via FNDPASS but not via form.
The problem lies in system profile "Application SSO LDAP Synchronization" (APPS_SSO_LDAP_SYNCH). We had this proble disabled at site level, which was causing the issue. After changing this profile at site level to value enabled it worked. So make sure you have this profile enable if you like to use standard feature of password change.

Happy Troubleshooting !!!

Metalink New Look

I came across with New Metalink URL, Its really mass improvement from its old looks. Just try this

https://csm.oracle.com/CSP/ui/index.html

Happy Troubleshooting !!!

Change Global Preferences for WF Notification Style

I come across with one of such requirement where I have to change the Global Preference from Backend. Here how I did it

Changing Global preferences for notification style
=====================================================
select PREFERENCE_VALUE
from FND_USER_PREFERENCES
where USER_NAME='-WF_DEFAULT-'
and MODULE_NAME='WF'
and PREFERENCE_NAME='MAILTYPE' ;

Now Update the record :

Update FND_USER_PREFERENCES
set PREFERENCE_VALUE = 'MAILATTH'
where USER_NAME='-WF_DEFAULT-'
and MODULE_NAME='WF'
and PREFERENCE_NAME='MAILTYPE' ;

Different PREFERENCE_VALUES to select from
====================================
MAILATTH == Plain text with HTML attachment
MAILTEXT == Plain Text
SUMMARY == Plain text Summary email
MAILHTML == HTML Mail with attachments
MAILHTM2 == HTML Mail
SUMHTML == HTML Summary email

Happy Troubleshooting !!!

PDF Printing general Checks

Recently we have issue with PDF Printing, although it was not because of setup. Here are the basic steps one should check for PDF Printing. All should be done at admin node (Where concurrent manager is running).
1. Display has been set properly and you can check that via $echo $DISPLAY at server end. And to make sure from application end too, just run "Print" Concurrent program and give "DISPLAY" as parameter.
2. you must be able to run xclock from server, whether you get output (in case of VNC) or not (in case of Xvfb).
3. Must have issued xhost + to access X server.
4. Software like xpdf or something has to be installed on Server. Which will help to view PDF as output files.
5. Pasta driver must be installed and setup correctly. pasta.cfg must be having reference to PDF software installed in step 4.

Happy Troubleshooting !!!

JAVA Mailer not Starting

Today was the bad day for us, we had an outage last weekend and after we finished our job lot of things start failing. Due to DBA errors and Java mailer is one off them. Here is what log says
------------------------------------------------------------------------------------
SVC-GSM-WFMLRSVC-67585-10006 : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started.oracle.apps.fnd.cp.gsc.SvcComponentContainerException: Could not start component; performing rollback -> oracle.apps.fnd.cp.gsc.SvcComponentException: Validation failed for the following parameters -> {FRAMEWORK_USER=Unable to make a connection with the user and responsibility}. Parameters were -> {INBOUND_MAX_IGNORE_SIZE=1000, INBOUND_MAX_LOOKUP_CACHE_SIZE=100, ATTACH_IMAGES=N, ALLOW_FORWARDED_RESPONSE=Y, INBOUND_UNSOLICITED_THRESHOLD=2, NODENAME=PRODMAIL, SEND_UNSOLICITED_WARNING=N, AUTOCLOSE_FYI=N, PROCESSOR_READ_TIMEOUT_CLOSE=Y, INBOUND_PROTOCOL=IMAP, ATTACHED_URLS=WFMAIL:ATTACHED_URLS, TEST_ADDRESS=, EXPUNGE_ON_CLOSE=Y, PROCESSOR_OUT_THREAD_COUNT=1, ATTACH_STYLESHEET=N, OUTBOUND_CONNECTION_TIMEOUT=120, INBOUND_MAX_RET_EMAIL_SIZE=100, MAX_INVALID_ADDR_LIST_SIZE=100, PROCESSOR_MAX_LOOP_SLEEP=60, SEND_ACCESS_KEY=N, PROCESSOR_IN_THREAD_COUNT=1, FRAMEWORK_APP=1, CLOSED=WFMAIL:CLOSED, INBOUND_FETCH_SIZE=100, SUMMARY=WFMAIL:SUMMARY, ENABLE_STYLESHEET=N, PROCESSOR_ERROR_LOOP_SLEEP=60, OPEN_MAIL_FYI=WFMAIL:OPEN_MAIL_FYI, FRAMEWORK_RESP=20420, ALTERNATE_EMAIL_PARSER=oracle.apps.fnd.wf.mailer.DirectEmailParser, INBOUND_PASSWORD=_@8@!864^4$9#64#^@#8@@686##!#44@#!0!99@@`9+^+*#B$$@99*$, HTTP_USER_AGENT=Mozilla/4.76, RESET_NLS=N, PROCESS=Process, OPEN_MAIL_DIRECT=WFMAIL:OPEN_MAIL_DIRECT, PROCESSOR_LOOP_SLEEP=5, MESSAGE_FORMATTER=oracle.apps.fnd.wf.mailer.NotificationFormatter, REPLYTO=emailuser@edomain.com, FRAMEWORK_USER=0, CANCELED=WFMAIL:CANCELED, OUTBOUND_PROTOCOL=SMTP, ACCOUNT=emailuser, DISCARD=Discard, FROM=Expedite, PROCESSOR_DEFER_EVTDATA_READ=Y, SEND_CANCELED_EMAIL=N, WARNING=WFMAIL:WARNING, PROCESSOR_MAX_ERROR_COUNT=50, INBOUND_CONNECTION_TIMEOUT=120, HTMLAGENT=http://hostname.domainname.com:8000/pls/SID, INBOX=INBOX, OPEN_INVALID_MORE_INFO=WFMAIL:OPEN_INVALID_MORE_INFO, OUTBOUND_SSL_ENABLED=N, INBOUND_SERVER=mail.domain.com, OPEN_MORE_INFO=WFMAIL:OPEN_MORE_INFO, MAILER_SSL_TRUSTSTORE=NONE, INLINE_ATTACHMENT=N, OPEN_INVALID=WFMAIL:OPEN_INVALID, EMAIL_PARSER=oracle.apps.fnd.wf.mailer.TemplatedEmailParser, DIRECT_RESPONSE=N, OPEN_MAIL=WFMAIL:OPEN_MAIL, FRAMEWORK_URL_TIMEOUT=30, COMPONENT_LOG_LEVEL=1, SUMHTML=WFMAIL:SUMHTML, PROCESSOR_READ_TIMEOUT=10, DEBUG_MAIL_SESSION=N, INBOUND_SSL_ENABLED=N, OUTBOUND_SERVER=mailhost.domain.com}
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(SvcComponentContainer.java:2211)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(SvcComponentContainer.java:300)
at oracle.apps.fnd.wf.bes.DispatchThread.run(DispatchThread.java:57)
----------------------------------------------------
And I think I know the issue, it was because of autoconfig only, you might be asking why? Our DBA ran autconfig without clearing FND_Nodes table. And as we were running on virtual host (clustering at db tier). It created a mess there. We have duplicate server_id for virtual and active physical host. Server ID for both of them were same, that's the reason Java mailer was not able to start and failing because of above error. Now what, another downtime---- I don't think customer will agree, but this issue is critical how to resolve this without big downtimes for running autoconfig. We raised Sev 1 tar to see if Oracle Support can help on this, but hard luck, they said the same old story of running autoconfig and all. But after spending few minutes, I got the solutions with 10 min of downtime. Here is what I did :

1. Shutdown Concurrent Manager
2. Take a backup of FND_Nodes.
3. Delete Physical host from OAM (OAM > Administrator > Hosts)
4. Register same host again from OAM.
5. Start Concurrent Manager
6. Mailer was working fine after this.

Happy Troubleshooting !!!

Experience with Enterprise Linux

I tried installing Enterprise Linux on my home PC, which has decent configuration of 2.8 Ghz processer with 100GB HD and 512 MB RAM. I did download Enterprise Linux from Oracle Edelivery site. It has 5 parts zip file containing iso images, you have to cut DVD out off it.
It gave you an option of checking media (DVD which you just cut) although its not necessary. Rest all went smoother than Windows :-). I was able to finish my installation with in 35 min. I would say its easier and experience so far is good. I am planning to install 11i with 10g RAC on it. Will share my experience with that soon.
Happy Troubleshooting !!!

FRM-92101: A failure has occured on the Server

After upgrading to 11.5.10.2 from 11.5.9, we have an issue of missing AP functions and menus. We did following to get those missing objects:
FNDLOAD APPS/password 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $AP_TOP/patch/115/import/US/ap115fn.ldt
But After this when we tried to access any AP form we are getting "FRM-92101: A failure has occured on the Server" Error. To resolve this we did following

1. Recompile all invalids.
2. Regenerate all AP forms, PLL and Menus.
3. Bounce Services.

It worked after this.

Happy Troubleshooting !!!

10giAS SSO Integration issue with 11i -- Followup

I haven't been able to resolve this issue yet but Oracle says its a Bug in 10.1.4.01 IDM which we are using and it can be resolved after applying patch 5867734. and make sure GenericPageBean.class file has the date 5/9/2007 on it. To get that use following command :

cd %ORACLE_HOME%/j2ee/OC4J_SECURITY/applications/sso/web/WEB-INF/lib$ORACLE_HOME/jdk/bin/jar tvf ossoui.jar

Although it didn't resolved my issue, but I felt sharing this info.

Happy Troubleshooting !!!

DISPLAY Settings

I never thought that even DISPLAY will going to give me tough time. Our client doesn't allow us to use VNC due to security reason. So we do not have any other option other than Xvfb at server End. We did start it and as expected xclock worked and no output. So I tried testing PDF output of my Active User conc program, it failed due to lack of Windoes Manager running at server. So I have to start twm at server end give xhost +. Vola it worked !!! Now another issue which I can forsee for my production as we were using Virtual host, to avoid any confusion in production cutover I used DISPLAY=`uname -n`:1.0

Happy Troubleshooting !!!

10giAS SSO Integration issue with 11i

We have successfully integrated our 11.5.10.2 instance with 10giAS SSO with AD and WNA. Everything is working fine, but we have some issues. One of them was unable to modify or delete Partner Application. Even with orcladmin user we are not able to Administer Partner Applications. We are working with Oracle to get this resolved, but found one workaround to modify them from backend. We can found them in table "WWSSO_PAPP_CONFIGURATION_INFO$" under orasso user. You can update this table as workaround. Till I get some solution to share.

Happy Troubleshooting !!!

Windows XP Shutdown and Reboot Shortcuts

Now as I have been moved to offshore, I am accessing my PC in US remotely. Now due to some reasons I want to reboot my PC, but non off the options allow me to do that, as I was accessing it remotely. Then I thought of putting a shortcut icons for reboot and shutdown. I need reboot icon. This is what I did :

Desktop Reboot Icon
Right click on the desktop
Click [New] [Shortcut] to open the Create Shortcut Wizard
Type [shutdown -r -t 00] without the brackets.
Click [Next] and type a descriptive name of your choosing for the shortcut
Click [Finish]

Desktop Shutdown Icon
Right click on the desktop
Click [New] [Shortcut] to open the Create Shortcut Wizard
Type [shutdown -s -t 00] without the brackets.
Click [Next] and type a descriptive name of your choosing for the shortcut
Click [Finish]

Hope you might need it sometime somewhere
Happy Troubleshooting !!!

April Fool

Today one of my user system made April fool to us. We have newly setup 10giAS instance with WNA Kerberose on our 11.5.10.2 Application Instance. This user was not able to get the Apps home page whenever he clicks the URL. He gets the SSO login page, which he should not get. I looked at sso log files and couldn't retrieve any information about his session. I thought of two choices, one to reboot his system if it works then no need for other. If doesn't I will ask him to login to some other system and try.
Once he rebooted his system and tried login again it works. I am not sure of cause but took it lightly too, thought system made April fool of us !!!

Happy Troubleshooting !!!

Relocated to India

I have decided to Relocate to Delhi, India. I may not post for some time, will definately keep posting messages pretty soon.

Until then Happy Troubleshooting !!!

Load Balancer - Issue accessing some pages

Today I come across with one wierd issue with my instance. We are using Apache level load balancer and my WF mailer was down. I was trying to get into that page and its taking long time to access WF component page. Or I would say its not allowing me to that. It never happened earlier. I checked with LB team too and they too were not able to find some solution.
Although I found the workaround to get rid of this issue. Just changed the name from load balancer name to actual host name like from oracle-lb to myhost. It worked !!! Althought I was not able to find the solution why it was not allowing us to go to only WF component page but found workaround to resolve the issue.

Happy Troubleshooting !!!

Orphan processes in Oracle databases

I have many monitoring tools implemented here which keep bugging my application instances. We have one external careers site too which too increments some orphan processes in my Oracle database. Although we have script in place to take of them and kick them out frequently, but still I can see my total number of process reached close to my max process limit. Here what I have to do in that situation:

1. Run this query to find out them:

SELECT spid FROM v$process WHERE NOT EXISTS
( SELECT 1 FROM v$session WHERE paddr = addr);

2. Grep what are they and what are they doing :

SELECT '!ps -ef : grep ' spid FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

3. Kill them, if they are not required:

SELECT '!kill -9 ' :: spid FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

Happy Troubleshooting !!!

Note: Use Pipe sign in place of colon which I have mentioned in statments

Password expiring Alert

Following script will help you to get an alert if users have password longer than certain days. Lets say you set an alert for password to expire in 180 days and you want to sent an alert after 150 days:

. .env

cd /tmp
sqlplus -S /nolog <<>
connect apps/$APPSPASS
set feedback off
set pages 100
set lines 100
col "username" format a15
col "account_status" format a15
col "profile" format a15
col "LAST_PASSWD_DATE" format a16
col "CHANGE BY" format a16
spool /tmp/userpassword.log

select /*+ RULE */ dd.username,dd.account_status,dd.profile,
dd.created "CREATION_DATE", u.ptime "LAST_PASSWD_DATE",u.ptime+180 "CHANGE BY"
from dba_users dd, sys.user$ u
where dd.USERNAME = u.NAME
and u.ptime <>
and dd.account_status = 'OPEN'
and dd.USERNAME != 'APPLSYSPUB' -- Exception as this account should have the default password
order by dd.created,u.username
/
spool off
EOF
if [ -s /tmp/userpassword.log ] ; then
cat /tmp/userpassword.log mailx -s "Users with Password older than 150 days" sudubey@gmail.com
fi

Keep Bugging Users :-)

Keep Troubleshooting !!!

Having issues with JVM

As Apache is using JVM internally, we have to look at troubleshooting JVM too, before jumping onto Apache. See at jvm log files if you see "Full GC" having quiet frequently, I would suggest to make following changes and if you already have mentioned values, increase something higher then this :


1. jserv.conf
ApJServVMTimeout 180

2. jserv.properties
wrapper.bin.parameters=-verbose:gc -Xmx1024M -Xms256M -XX:MaxPermSize=256M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB

3. httpd.conf
Timeout 1800

4. ICX:Session Timeout 30 (profile value)

5. zone.propertiessession.timeout=1800000

Note : > 3,4,5 value has to be same

6. JDBC parameter changes in dbc file
FND_JDBC_BUFFER_DECAY_SIZE=20
FND_JDBC_BUFFER_MIN=20
FND_JDBC_BUFFER_MAX=50%

7. Bounce Apache after above changes

Happy Troubleshooting !!!

APP-FND-02704: Unable to alter user Apps to change password

You might encounter this error while changing apps or other product password using FNDCPASS.

+---------------------------------------------------------------------------+
Working...
APP-FND-02704: Unable to alter user APPS to change password.
+---------------------------------------------------------------------------+
Concurrent request completed

Look at two things:

1. Never start your password other than Alphabatic value.

Even if it doesn't work.

2. change your profile back to default and run FNDCPASS again.

It worked for me...

Happy Troubleshooting !!!

Good tool for troubleshooting - HTTPheaders

For Taking HTTP header trace follow the steps mentioned here:

1. Obtain the HTTP header tracer add-on:
Internet Explorer (IE): http://www.blunck.info/iehttpheaders/iehttpheaders.html
Firefox (FF): http://livehttpheaders.mozdev.org/

2. Once installed and the browser restarted, start the HTTP header tracing add-on is as follows:
(IE) View -> Explorer Bar -> ieHTTPheaders v1.6
(FF) Tools -> Live HTTP Headers

3. Ensure the HTTP capture buffer is clear before you begin.
(IE) In the "ieHTTPheaders window" -> Right-Click -> Clear
(FF) In the "Live HTTP Headers" -> Click on 'Clear'

4. Once ready, direct the browser to the login page of your environment and proceed to replicate the issue.

5. Save the resulting generated output to a file and upload the results..
(IE) In the "ieHTTPheaders window" -> Right-Click -> 'Save'
(FF) In the "Live HTTP Headers" -> Click on 'Save-All'


Other than above steps, you can type following words in your browser address bar to get cookies info:

javascript:unescape(document.cookie)

Happy Troubleshooting !!!

ORA - 600 -- Look at these Metalink notes for Help

ORA-600 Errors 1 to 3000
Note 138300.1 "ORA-600 [105]"
Note 263295.1 "ORA-600 [106]"
Note 28104.1 "ORA-600 [504]"
Note 138871.1 "ORA-600 [510]"
Note 138888.1 "ORA-600 [525]"
Note 138939.1 "ORA-600 [711]"
Note 39308.1 "ORA-600 [723]"
Note 31056.1 "ORA-600 [729]"
Note 131490.1 "ORA-600 [733]"
Note 217860.1 "ORA-600 [1013]"
Note 138123.1 "ORA-600 [1100]"
Note 41767.1 "ORA-600 [1113]"
Note 40514.1 "ORA-600 [1114]"
Note 137262.1 "ORA-600 [1115]"
Note 66387.1 "ORA-600 [1158]"
Note 138354.1 "ORA-600 [1236]"
Note 28045.1 "ORA-600 [2103]"
Note 28929.1 "ORA-600 [2662]"
Note 31057.1 "ORA-600 [2845]"
Note 138733.1 "ORA-600 [2865]"


ORA-600 Errors 3001 to 6000
Note 30866.1 "ORA-600 [3020]"
Note 93665.1 "ORA-600 [3668]"
Note 47456.1 "ORA-600 [4000]"
Note 96642.1 "ORA-600 [4036]"
Note 43914.1 "ORA-600 [4137]"
Note 28226.1 "ORA-600 [4146]"
Note 39282.1 "ORA-600 [4193]"
Note 39283.1 "ORA-600 [4194]"
Note 138822.1 "ORA-600 [4400]"
Note 29702.1 "ORA-600 [4414]"
Note 138836.1 "ORA-600 [4454]"
Note 39553.1 "ORA-600 [4511]"
Note 73455.1 "ORA-600 [4512]"
Note 27955.1 "ORA-600 [4519]"
Note 204536.1 "ORA-600 [4820]"
Note 41840.1 "ORA-600 [4882]"


ORA-600 Errors 6001 to 9000
Note 47449.1 "ORA-600 [6002]"
Note 116552.1 "ORA-600 [6034]"
Note 40640.1 "ORA-600 [6101]"
Note 99300.1 "ORA-600 [6122]"
Note 138913.1 "ORA-600 [6193]"
Note 39399.1 "ORA-600 [6731]"
Note 41719.1 "ORA-600 [6856]"


ORA-600 Errors 9001 to 15000
Note 138325.1 "ORA-600 [12209]"
Note 33174.1 "ORA-600 [12235]"
Note 138332.1 "ORA-600 [12261]"
Note 138340.1 "ORA-600 [12304]"
Note 35928.1 "ORA-600 [12333]"
Note 28229.1 "ORA-600 [12700]"
Note 67496.1 "ORA-600 [13009]"
Note:28185.1 "ORA-600 [13013]"


ORA-600 Errors 15001 to 17000
Note 138428.1 "ORA-600 [15160]"
Note 138430.1 "ORA-600 [15201]"
Note 138431.1 "ORA-600 [15203]"
Note 131186.1 "ORA-600 [15212]"
Note 137266.1 "ORA-600 [15419]"
Note 216277.1 "ORA-600 [15456]"
Note 138457.1 "ORA-600 [15709]"
Note 67490.1 "ORA-600 [15851]"
Note 76528.1 "ORA-600 [15868]"
Note 138499.1 "ORA-600 [16201]"
Note 106607.1 "ORA-600 [16365]"
Note 138523.1 "ORA-600 [16515]"
Note 138526.1 "ORA-600 [16607]"

ORA-600 Errors 17001 to 30000
Note 138537.1 "ORA-600 [17003]"
Note 138541.1 "ORA-600 [17012]"
Note 41472.1 "ORA-600 [17034]"
Note 138554.1 "ORA-600 [17059]"
Note 39616.1 "ORA-600 [17069]"
Note 29616.1 "ORA-600 [17090]"
Note 138565.1 "ORA-600 [17099]"
Note 47411.1 "ORA-600 [17112]"
Note 39453.1 "ORA-600 [17113]"
Note 34782.1 "ORA-600 [17114]"
Note 138576.1 "ORA-600 [17128]"
Note 138580.1 "ORA-600 [17147]"
Note 34781.1 "ORA-600 [17148]"
Note 138586.1 "ORA-600 [17172]"
Note 263110.1 "ORA-600 [17175]"
Note 34779.1 "ORA-600 [17182]"
Note 45725.1 "ORA-600 [17271]"
Note 138597.1 "ORA-600 [17274]"
Note 134139.1 "ORA-600 [17280]"
Note 39361.1 "ORA-600 [17281]"
Note 138602.1 "ORA-600 [17285]"
Note 138621.1 "ORA-600 [17585]"
Note 138640.1 "ORA-600 [18209]"
Note 216278.1 "ORA-600 [18261]"
Note 138678.1 "ORA-600 [20084]"
Note 100073.1 "ORA-600 [25012]"


ORA-600 Errors kc
Note 138981.1 "ORA-600 [kcbgcur_2]"
Note 70097.1 "ORA-600 [kcbgcur_3]"
Note 114058.1 "ORA-600 [kcbgcur_9]"
Note 138990.1 "ORA-600 [kcbgtcr_4]"
Note 138991.1 "ORA-600 [kcbgtcr_5]"
Note 261264.1 "ORA-600 [kcbgtcr]"
Note 248874.1 "ORA-600 [kcbgtcr_6]"
Note 233612.1 "ORA-600 [kcbgtcr_12]"
Note 204512.1 "ORA-600 [kcbnew_3]"
Note 216104.1 "ORA-600 [kcbrbo1]"
Note 139011.1 "ORA-600 [kcbzpb_1]"
Note 139012.1 "ORA-600 [kcbzpb_2]"
Note 229467.1 "ORA-600 [kcbzwb_4]"
Note 139013.1 "ORA-600 [kccsbck_first]"
Note 216108.1 "ORA-600 [kcllcu_0]"
Note 263225.1 "ORA-600 [kclwcrs_15]"
Note 76434.1 "ORA-600 [kcoapl_blkchk]"
Note 248718.1 "ORA-600 [kcratr1_lostwrt]"


ORA-600 Errors kd to kw
Note 139037.1 "ORA-600 [kdddgb2]"
Note 139042.1 "ORA-600 [kdisle:nrows]"
Note 139051.1 "ORA-600 [kghalo2]"
Note 139052.1 "ORA-600 [kghasp1]"
Note 139066.1 "ORA-600 [kghxhdr1]"
Note 229809.1 "ORA-600 [kgliep_1]"
Note 66501.1 "ORA-600 [kkrqtab2]"
Note 139095.1 "ORA-600 [kkslgop1]"
Note 139116.1 "ORA-600 [kohdtf048]"
Note 264061.1 "ORA-600 [kqludp2]"
Note 139162.1 "ORA-600 [kssrmp1]"
Note 247822.1 "ORA-600 [ksmals]"
Note 139153.1 "ORA-600 [ksmguard2]"
Note 233864.1 "ORA-600 [kteuproptime-2]"
Note 139180.1 "ORA-600 [ktsgsp5]"
Note 139193.1 "ORA-600 [kttmupd_segment-]"
Note 228480.1 "ORA-600 [kwqitnmptme:read]"
Note 228480.1 "ORA-600 [kwqitnmptme:ready]"
Note 228364.1 "ORA-600 [kwqitnmptme:wait]"


ORA-600 Errors q to z
Note 248095.1 "ORA-600 [qctcte1]"
Note 216273.1 "ORA-600 [qctstc2o1]"
Note 209363.1 "ORA-600 [qerrmofbu1]"
Note 237598.1 "ORA-600 [qertqtableallocate2]"
Note 226887.1 "ORA-600 [qkagby4]"
Note 222876.1 "ORA-600 [qmxiunppacked2]"
Note 244365.1 "ORA-600 [rworupo.1]"
Note 139263.1 "ORA-600 [srsget_1]"
Note 260951.1 "ORA-600 [ttcgcshnd]"
Note 216452.1 "ORA-600 [ttcgcshnd-1]"
Note 216453.1 "ORA-600 [ttcgcshnd-2]"


Happy Troubleshooting !!!

SSL in 11i -- Where all we can have certificates

Yup one off the major problem for Apps DBA comes, when we have all bounch of security in our environment. SSL is one off them. So you have to take care of Apache ceritificate and moreover all issues with certificate or SSL handshake etc etc. But where to look for ceritificates:

Application Tier:
Look at httpd.conf file and search for ssl.crt or passkey or ssl.key, you will get the location where these files are stored. Make sure you change your ceritificate in the location mentioned in httpd.conf file.
ssl.crt > contains ca.crt, rootca.crt and server.crt files
ssl.key> contains server.key
passkey> Contains the certificate password

so for Apache certificate if you have above mentioned files it will work.

Now comes Client machine:
We have certificate here too:
C:\Program Files\Oracle\JInitiator 1.3.1.18\lib\security look at certdb file. Your ca.crt certificate must match with one of certificate in this file.

And look at ceritificate in your browser too:
IE > Internet Options > contents > Ceritficates> Click on tab Trusted Root Certification Authorities> Specially look at Class 2,3 certificate expiry date.

If all the above this are ok, you should not face any issue with Apache and SSL.

Happy Troubleshooting !!!

Troubleshooting if you have SSO enabled 11i

You might ask what and how does it matter if you have SSO enable or not. And how does it help in case we disable SSO. Yes it does !!!
I would suggest just try this. I have seen many occasion in my Production env giving errors for SSL handshake, each and every pointer in google or metalink is saying something is wrong with root certificate or you didn't imported your root certificate well. I tried it many times, but error remains same.
Now what, I have disabled the SSO and tried login and amazingly it was working fine. But now how to resolve the issue. While looking at certificate I reaslized one off the certificate in client end, means my System has some issue. I have reinstalled that from server and re-enabled my SSO .... vola it worked !!!!!!!
I know you might feel its imaginery situation, but it always works.

Happy Troubleshooting !!!

Enabling Debug for Discoverer 4i

One of the other way to find out issues in discoverer connection specially for from Oracle Apps. Just enable log and check the log file for errors:

Put following lines in discwb.sh file and restart services

AFLOG_ENABLED=Y
AFLOG_FILENAME=/Complete_Path/6iserver/discwb4/disc.log
AFLOG_LEVEL=STATEMENT
AFLOG_MODULE="fnd.src.security.afscp fnd.src.osd.afenv"
export AFLOG_ENABLED AFLOG_FILENAME AFLOG_LEVEL AFLOG_MODULE

Happy Trobleshooting !!!

Orphan Profiles in Oracle Apps

You must be wondering what's that. Yup it does exists sometime in our clone instances and it takes us to somewhere else. Debugging and troubleshooting takes long time with no results. Check the following if you have any rows results out of following query that means you have someone which you don't want in your profiles:

col profile_option_name format a40
col "Orphan ID" format a10
select
v.level_value "Server Orphan",
v.level_value2 "ServResp Orphan",
decode(v.level_id,
10005, 'Server',
10007, 'ServResp',
'Other') LEVEL_SET,
p.Profile_option_name
from
fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
where
p.profile_option_name = n.profile_option_name
and p.profile_option_id = v.profile_option_id (+)
and
( /* check Server level */
(
v.level_id=10005
and v.level_value > 0
and v.level_value
not in
(
select
f.node_id
from
fnd_nodes f
)
)
or /* check ServResp level */
(
v.level_id=10007
and (v.level_value2 is not null)
and (v.level_value2 > 0)
and v.level_value2
not in
(
select
f.node_id
from
fnd_nodes f
)
)
);

Now what, if you get some rows: Do this

alter trigger APPS.FND_PROFILE_OPTION_VALUES_AD disable;

delete from fnd_profile_option_values
where
level_id = 10005
and level_value in (Value of Node);

commit;

alter trigger APPS.FND_PROFILE_OPTION_VALUES_AD enable;

Happy Troubleshooting !!!

Apache Debug

You might be knowing it, but I would like to summarize the steps again to take Debug for Apache, believe me set this and you can easily resolve the errors:

1. Shut the HTTP Server (Apache/iAS) down. - You can grep for the 'httpd' process to verify it is down
2. Rename (or delete) the following files so we get a fresh copy of them:
/Apache/Jserv/logs/jserv.log
/Apache/Jserv/logs/mod_jserv.log
/Apache/Jserv/logs/jvm (the whole directory if it exists)/Apache/Apache/logs/access_log*
/Apache/Apache/logs/error_log
/Apache/Apache/logs/error_log_pls (if it exists)

Now we will turn on debugging in the log files:

3. Modify the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file. Search for the parameter:
ApJServLogLevel notice
Change the 'notice' to 'debug'

4. Modify the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties file. Search for the following section:

log=false
Change this to be log=true
and Changelog.channel=false
To be: log.channel=true

5. Modify the $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf file. Search for:
LogLevel
Set the LogLevel to 'debug' from its current value.

6. Modify the $IAS_ORACLE_HOME/Apache/Apache/conf/httpd_pls.conf file (if it exists). Search for the following parameter
LogLevelSet the LogLevel to 'debug' from its current value.

7. In the zone.properties...- servlet.framework.initArgs=debuglevel=1

8. Start the HTTP Server

Happy Troubleshooting !!!

Finding Huge files

Yes its a big tasks for DBA's, when you do df -k in solaris and see your mount points are reaching 100%. It difficult to find what to delete for what to compress. I usually try some commands mentioned below :

This command will list top 10 directory from parent directory
du -ks * sort -rn head -10

This command will list out all files greater than mentioned size.
find . -size +10240

Keep Troubleshooting !!!

Old Oracle Apps 11i Looks

Hey you might be wondering what I am going to talk now. Yup its about oracle Apps front end look and feel. Some of the users still using same old grey and blue looks and DBA wonder if he missed something. No not at all, just check these two profiles and you are done.

Change the following profiles at user level first-
"Java Look and Feel " to oracle and
"Java Color Scheme" to blaf

If it works for you then change it Site level to have others enjoy the same.!!!

Keep Troubleshooting !!!

Script for Checking Java Mailer Status

---------------------------------Script Start-----------------------------------
#!/bin/ksh
#Script to monitor WF Mailer Status
. .env
cd /tmp
sqlplus /nolog << EOF
connect apps/password
set heading off
set pages 0
set feedback off
spool monitor_mailer.lst
select COMPONENT_STATUS from FND_SVC_COMPONENTS
where COMPONENT_ID='10006';
spool off
EOF
MONITOR=`grep -v "SQL>" monitor_mailer.lstsed s/\ //g`

if [ $MONITOR != "RUNNING" ] ; then
echo $MONITOR mailx -s "WFMAILER: Check Workflow Mailer STATUS" sudubey@gmail.com
fi
/bin/rm -f /tmp/monitor_mailer.lst
-----------------------Script End -------------------------------------------------

Put this in crontab for every 10 min or so. It will send a status whenever its not running. No more OAM checking !!!!

Note: This has been tested in Sun Solaris

Keep Troubleshooting !!!

Apps Password changed from sqlplus

Many of you might have done this mistake, or no option left like in my client usually happen in every six month, it force you to change apps password via sqlplus. Now what application login will stop working, isn't it. Yes !!

Do this :
1. Change to old apps password via sqlplus;

SQL> Alter user apps identified by oldappspasswd;

2. Run autoconfig with this password in all tiers.

3. Restore customizations if any.

4. Start all services and you all set to login to your apps frontend login.

Hope this helps you all.

Keep Troubleshooting !!!