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.

Saturday, June 14, 2008

RMAN DUPLICATE - Not everything is made using the CATALOG alone?

Introduction

We just wrapped up our 10g RMAN testing. To test recovery using our RMAN backup, we have used the RMAN DUPLICATE command to create a duplicate database using this backup. I have noticed something about the DUPLICATE command that i have missed so far: The DUPLICATE command creates the duplicate database's controlfile using information from BOTH the existing target database's controlfile and the backup controlfile, not just the latter alone.

Test Case

1) dev10g is an existing development database that has got upgraded to 10g version
2) dev10g is in noarchivelog mode (like most development databases do)
3) dev10g is converted to archivelog mode to take a RMAN backup including full database and archivelogs backup
4) dev10g archiving is disabled (noarchivelog mode again)
5) dup10g is the proposed new database that will be created using the RMAN backup of dev10g.
6) DUPLICATE command is run on the dup10g database's host.
7) DUPLICATE command fails after restoring the datafiles.

From the logfile.....

contents of Memory Script:
{
set until scn 8321617961105;
recover
clone database
noredo
,
delete archivelog
;
}


..
..
..


RMAN-00571: =======================================
RMAN-00569: ===== ERROR MESSAGE STACK FOLLOWS =====
RMAN-00571: =======================================
RMAN-03002: failure of Duplicate Db command at 06/09/2008 23:41:15
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database:
ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/dbfs/oracle/oradata/dev10g_sys01.dbf'

Problem

The target database dev10g was converted to noarchivelog mode in the 4th step above after the RMAN backup was taken. However, the DUPLICATE command *considers* there are no archivelog files to be applied to dup10g because the target database's controlfile shows the database is in noarchivelog mode. Though dev10g's archivelog files were backed up when the backup commands were run in the 3rd step above, DUPLICATE generates an internal script that forbids the archivelog apply because the target database's current controlfile shows its in noarchivelog mode.

Solution

Enable archiving in the target database when DUPLICATE is running, even though its a development database that does not require archiving. It can be disabled once the duplicate database is created successfully.

One may not encounter this problem when the target database is a production one, because production databases are usually in archivelog mode; however, one may encounter this problem when the target database is a development one when its archiving is disabled after an RMAN backup.

Thursday, June 12, 2008

RMAN debug

RMAN automates Oracle database's backup and recovery processes. If you would like to know what happens inside an RMAN command,(RMAN Internals), then try the debug option. Also, when you have a case opened on any RMAN issue, the debug logfile can help a support analyst quickly locate the source of your RMAN problem.

Its very easy to turn on the debug feature.

/home/oracle/backup_testing> rman log=output.log trace=debug_info.log
RMAN> debug on
RMAN> RMAN Command 1;
RMAN> RMAN Command 2;
....
....
....
RMAN> exit

The debug_info.log contains all the internal details. BTW, you will not find information about this feature in the Oracle Documentation.