Thursday, October 23, 2008

'Applications SSO Auto Link User' profile and bootstrapping again

Introduction 

After integrating Oracle E-Business Suite with SSO, setting the system profile 'Applications SSO Auto Link User' = 'Enabled' lets users login to Oracle using only their SSO username/pwd (Windows NT username/pwd) if the 3rd party LDAP directory is MS Active Directory) WITHOUT the need to enter their Oracle E-Business local username/pwd again in the next page. This profile 'automatically links' the Windows NT username and the existing Oracle username internally (provided there is a user_name in FND_USER that EXACTLY matches the Windows NT username) and directly shows the Oracle E-Business responsibilities navigation pane. It does it by updating FND_USER.USER_GUID to a value that matches with the corresponding OID's ORCLGUID value of the logged in user.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Problem

Due to some circumstances, one may have to bulkdelete all the OID users and reload them from AD to OID using the bootstrap process. Because it is a new bootstrap process, a new ORCLGUID may get generated in the OID that will make it out of sync with the target FND_USER.USER_GUID value. With such a scenario, Oracle will prompt again for the E-Business local username/pwd despite the system profile 'Applications SSO Auto Link User' having an 'Enabled' value. One can verify the mismatch after the reload by running the following command.

ldapsearch -v -h oid_host_name -p 389 -D "cn=orcladmin" -w ***** -b "" -s sub "sn=RAMINENI*" uid orclguid orclactivestartdate orclactiveenddate orclisenabled
 
ldap_open( oid_host_name, 389 )
filter pattern: sn=RAMINENI*
returning: uid orclguid orclactivestartdate orclactiveenddate orclisenabled filter is: (sn=RAMINENI*)
cn=c123456,ou=consultants,ou=users,IdentityRealm
orclguid=844AB8C88EE56B74E040810A0CBC6249
1 matches

This orclguid shown above may NOT match the FND_USER.USER_GUID for the same user because the former is a newly generated one because of the reload process. In this case, Oracle prompts for the Oracle E-Business username/pwd after entering the Windows NT username/pwd in the login page even though the system profile 'Applications SSO Auto Link User' is set to 'Enabled', often confusing the  user.

 

   

 

 

 

 

Solution

The process to 'auto link' SSO username and the FND_USER user_name after a reload of data from the 3rd party LDAP directory to OID is to update FND_USER.USER_GUID value to NULL.

update fnd_user set user_guid = NULL where user_guid is NOT NULL. 
 
A better alternative is to run the $FND_TOP/patch/115/sql/fndssouu.sql USER_NAME
script (Apart from setting the FND_USER.USER_GUID value to NULL, this script also flushes the workflow entity cache for the user)

Friday, October 17, 2008

Different methods of provisioning users from Oracle LDAP to E-Business Suite

Introduction

While working on the Oracle E-Business Suite login integration project with MS Active Directory through Oracle Internet Directory (OID), I have come across several ways to provision (add) new users in the E-Business Suite. Without Single Sign On, the only way to add new users is through the FND_USER_PKG or by using the Security => User => Define of Oracle E-Business 11i. Depending on the setup in OID, I have identified five different ways to add new users in 11i after integration with MS Active Directory

Assumptions
  1. Oracle Identity Management 10.1.4.2 and Oracle Applications E-Business Suite 11.5.10.2
  2. Uni-directional synchronization from Active Directory to Oracle Internet Directory
  3. Uni-directional provisioning from Oracle Internet Directory to the E-Business Suite

Options

On-Demand User Creation

On-Demand User Creation allows any enterprise user who has access to the SSO login page of the E-Business Suite to get an account created automatically. The user has to enter his/her Windows username/pwd on the SSO login page. A click on the 'OK' button will create a user in the FND_USER table of the E-Business Suite with a default 'Preferences SSWA' responsibility and will let the user access Oracle immediately.

Procedure

Set the profile 'Applications SSO Auto Link User' to 'Create a new user and link to OID user'

Advantages
  • Account Creation in E-Business Suite is self-service and automatic.

Disadvantages

  • Lets any user in the enterprise to have an Oracle account. Anyone who has the login webpage address can get an account created in 11i.

ldifwrite on OID and LDAPUserImport on 11i

Procedure

  1. Use the ldifwrite command in the OID server to create a dump file containing the user's LDAP attributes and other information.
  2. Copy the ldif file to one of the 11i middle-tiers
  3. Run the LDAPUserImport java command to import the user into FND_USER table.

Advantages

  • Control on user creation. Only genuine and approved users will be allowed to have an account in Oracle.

Diadvantages

  • Manual process. However, it can be automated as well.

provsubtool on OID and Workflow Subscription Event on 11i

Procedure

  1. Run the provsubtool command in OID to add new users to an account subscription list
  2. On the 11i side, a workflow subscription event oracle.apps.fnd.subscription.add will be triggered at an appropriate time to add these users in the list to the FND_USER table in 11i

Security => User => Define form in 11i

The good old way of using Security => User => Define can still be continued for provisioning users in 11i after an integration with an LDAP directory.
Procedure
  1. Verify the user exists in OID using an ldapsearch command or by using the oidadmin tool.
  2. Disable Applications 'SSO LDAP Synchronization profile' option
  3. Create the user using the Security => User => Define form

Unconditional Provisioning

By enabling the 'Applications SSO Enable OID Identity Add Event' system profile, the provisioning profile will add every user account to E-Business Suite that is synchronized from MS Active Directory to OID. Exercise caution before enabling this system profile because not every employee or consultant in an organization will need an Oracle account. This can lead to a proliferation of users in the FND_USER table.

Conclusion

Sufficient thought has to be exercised before enabling the Unconditional Provisioning or the On-Demand user creation as described above. Use one of the other three options for maximum control on user provisioning.

Tuesday, September 16, 2008

Oracle E-Business Suite Login Integration with Corporate LDAP

Introduction

I am currently working on a new project to integrate Oracle E-Business Suite Login Information with the corporate LDAP Directory ( In our case it is the Microsoft Active Directory). This will accomplish Single Sign On (SSO) functionality for Oracle Applications. Instead of storing user login credentials in a local oracle database table, users can directly login to Oracle Applications website using their Windows NT credentials. SSO allows users login to their enterprise assets using only a single username/password across the enterprise. This eliminates the need for the user to register multiple times for multiple IT Applications, greatly increasing simplicity. In our organization, the enterprise user credential repository is stored in the corporate LDAP directory (MS Active Directory). Oracle E-Business Suite uses OID (Oracle's LDAP) Implementation to integrate with MS Active Directory (Microsoft's LDAP Implementation). However, external users and vendors who use Applications such as iSupplier and Procurement will continue to get authenticated using the local oracle database table, because of the fact that they are not enterprise users.

Architecture

All the details are available in the Proof-of-Concept architecture diagram attached above.Click on it for the full-sized image. Currently i am still working on the Proof-of-Concept setup before finalizing the final architecture. The final architecture will have advanced features such as High Availability through Real Application Clusters for the OID database, OID LDAP Replication, Oracle AS Cluster for SSO login servers etc.

Monday, September 1, 2008

Oracle DBA Activities

1) What is a typical day at your job?

I start my day checking any system alerts such as database performance problems, backup failures etc. We are using Oracle's Enterprise Manager which is a web-based software that sends email alerts to us automatically whenever it detects a problem based on certain criteria. I spend most of the day working on current projects such as database upgrades, migrations, new installations etc. I also help application developers and end-users whenever they have a database related question or problem.
2) What does it take to be a successful Oracle DBA?
Most of today's E-Business and IT applications are entirely web-based and hence the underlying databases have to be highly available 24*7. Responsibility, proactive attitude and emergency preparedness are some of the key characteristics that can make a successful Oracle DBA. IT application developers and the end-user communities rely heavily on the database administrator for their day-to-day database issues, questions and projects. An Oracle DBA should be polite and must treat every one in the organization with courtesy and respect.
3) Has your job description evolved over time?
Yes indeed ! The definition of an Oracle DBA has a much broader scope today. I started with just "database work" in my first job. Today my responsibilities include Oracle systems design and architecture, including Oracle E-Business Suite administration, Oracle Application Server setup and administration, setting up of Continuity of Business systems (Disaster Recovery preparedness), setup and administration of Oracle Fusion Middleware components such as Oracle Portal Server, Identity Management etc. I am also expected to work on hardware specifications and requirements for upgrading existing Oracle installations or setting new ones. Whereas the traditional "Oracle DBA" designation has remained the same, it has a much wider scope and responsibility today.
4) How do you keep up with new features and changes & advancements in database technology?
Every major Oracle database release comes with a lot of exciting new features which can be leveraged for simplicity, automation or better database management.
a)I am an avid reader of the bi-monthly Oracle Magazine. The subscription is free and it is available online as well. The magazine covers the latest in Oracle, contains a lot of expert articles with a practical outlook to tackle business problems.

b)I have also subscribed to rss feeds in http://otn.oracle.com/ so that i get updated whenever there is a new knowledge based article. This a popular site for the Oracle community and most of the technology articles are posted by Oracle ACEs and Oracle ACE Directors who are proven and recognized individuals by Oracle Corporation.

c)I also recommend aspiring DBAs to register in the Official Oracle Forum , thanks to the many experts who generously contribute to this discussion board, virtually any of your database related questions can get answered here.
5.What is the best feature you like about oracle DB, what needs improvement compared to other databases in the market?
My favorite Oracle database feature is Real Application Clusters (RAC). Using RAC technology, Oracle databases can be setup for high availability and virtually unlimited scalability. I did not get a chance to fully evaluate other databases in the market vis-a-vis the Oracle database. Oracle is the recognized Industry leader as per various results published by market research companies such as IDC and Gartner.
6.Has any of the following major macro trends affected you personally, whats your opinion?
a.Outsourcing & Offshoring

No. Oracle DBA is one of the few jobs that had a lesser impact by Outsourcing. A DBA is critical to the success of an IT department requiring a lot of technical understanding, emotional maturity, ability to handle pressure and crisis and one that comes with a lot of responsibility. Infact, all the Dice Reports this year show Oracle database as one of the top technology skills in the market in the USA.

b.Virtualization

Remote Service and Tele-commuting are only for low profile work such as after-hours support etc. Most of the managers prefer Oracle DBAs to work onsite and with direct supervision.

c.Moving from client-server to web-based

The Oracle DBA is usually less impacted by Client-server to Web-based migrations. Oracle databases can work with both client-server systems and web-based systems.
7.Your advice to people who are evaluating Oracle DB administration as a career.

The IT industry is facing a shortage of quality Oracle DBAs. Oracle database administration is a good career option with long-term benefits. I have been working as an Oracle database administrator since more than 6 years and the experience is very rewarding. It has also given me the confidence to architect and build large scale IT systems. I was able to positively impact the experience of the end-user community and positively contribute to various IT departments.

I recommend all aspiring Oracle DBAs to get trained in a class atmosphere. Oracle University offers a lot of online and classroom courses in many countries across the world.

Wednesday, August 20, 2008

SSO Login failure: Is it a problem with the username or the password ?

We are working on a proof of concept instance to evaluate integrating E-Business Suite login information with the corporate LDAP directory (In our case, it is the MS Active Directory). Users are currently getting authenticated using the local fnd_user table in the E-Business Suite database. The project aims to use the corporate user repository (MS Active Directory) for authenticating the E-Business Suite users, enabling single sign on (SSO) for all Oracle E-Business suite of Applications in the enterprise.

I had a tough time testing login failures after the SSO integration with E-Business Suite. The login page does not reveal if it is an invalid User Name or an invalid Password. All i get is "Error: Authentication failed. Please try again".

I turned to the $ORACLE_HOME/sso/log/ssoServer.log for help. The ssoServer.log provides additional error information only for invalid User Names. In the below error message, the stack trace reveals the problem is with the username sramineni@mycompany.com , meaning this username does not exist in the Oracle Internet Directory (and MS Active Directory).

Wed Aug 20 23:47:41 CDT 2008 [ERROR] AJPRequestHandler-ApplicationServerThread-7 Could not get attributes for user, sramineni@mycompany.com oracle.ldap.util.NoSuchUserException: User does not exist - SIMPLE NAME = sramineni@mycompany.com at oracle.ldap.util.Subscriber.getUser_NICKNAME(Subscriber.java:1160)

Now, if i enter a valid User Name,but invalid password, Oracle fails to update ssoServer.log with any error/stack trace. Oracle keeps you guessing on this aspect.

Monday, August 18, 2008

Database and archive log backup in one RUN command

Introduction


We went live with 10.2.0.4 Oracle CRS this weekend, completing the 5 month 9i to 10g database upgrade project. I also verified the first RMAN backup job after the upgrade and it completed successfully. A known issue with the backup job in 9i no longer exists after the 10g upgrade, acting as an additional bonus of the upgrade !

Problem


We take full database backups always without any incremental backups. Though the below run block contains two backup commands, one for the database backup and the other for the archive log backup, the latter never used to get picked up in 9i. In this older version, Oracle always skipped the second backup command in the below run block without reporting any error messages or warnings.

connect rcvcat 'rmanprod/*****@catdb'
connect target '/'


run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
backup
format 'prod_bk_%s_%p_%t'
(database include current controlfile);
# backup all archive logs
backup
skip inaccessible
format 'prod_al_%s_%p_%t'
(archivelog all
delete input);
release channel t1;
release channel t2;
}
allocate channel for maintenance type 'SBT_TAPE';
crosscheck backup of database;
delete expired backup;
release channel;


Solution

Since we have a different backup job to backup only archive logs, we did not pursue this problem with Oracle Support. I am not sure if this is a known issue in 9i version, but pleasantly surprised to find this problem go away in 10g. The first backup job that we run after the 10g upgrade successfully processed both the database backup command and archive log backup command in the same RUN block. The 9i mystery seems to have gone away in 10g. I am interested to know if some of you observed the same issue with 9i before ?

Sunday, July 13, 2008

APP-FND-01508: Failed to Connect

Introduction

The other day i spent almost two hours to know the source of a APP-FND-01508: Failed to Connect error occurring in all forms associated with some custom responsibilities in the Applications 11i E-Business Suite. The problem started after changing the password of a custom database user 'amarchive' that is used to connect to open the forms from the applications' responsibility navigation pane.


Problem

We use Informia Archive software to archive historical financial data from the E-Business Suite. For reporting purposes, we have created a couple of custom responsibilities in the E-Business Suite so that users can run concurrent requests. All forms associated with these custom responsibilities started displaying the APP-FND-01508: Failed to Connect error. An event trace for the ORA-01017 error (invalid username/password) showed a custom database user 'amarchive' in the trace file whenever the above connect error was displayed. Upon further investigation, i found that the password of this custom database user was changed with the 'alter user amarchive identified by newpass' command. In normal circumstances, such a command would seem the right way to change the password of a custom database schema user. However, it was not the case in our database. The custom user was registered as an Oracle Applications module and hence it has an entry in the fnd_oracle_userid view. An alter user command only updates the password in the dba_users view and hence the password in fnd_oracle_userid does not get changed. A successful forms connection process needs both fnd_oracle_userid and dba_users view contain the same password. This can be accomplished only by using the FNDCPASS command.
FNDCPASS apps/**** 0 Y system/***** ORACLE AMARCHIVE newpass

Solution

I used the FNDCPASS command to change the password again (used the same password specified in the alter user command) and this command synchronized both fnd_oracle_userid and dba_users views, enabling successful forms navigation again.

Conclusion
Many Oracle E-Business Suite 11i databases have custom database schema users. These schema users have to be verified whether they are registered as application modules. If yes, then the users have entries in the fnd_oracle_userid view also. The only way to change password for registered application modules is through the FNDCPASS command.