Patching Oracle e-Business Suite

this is my fourth week at my new assignment and already I am busy applying all kinds of patches. Because they have a real big environment here, it's important to know what options to provide adpatch with.
The options I mostly work with are :

- hotpatch, I don't want to put the database in maintenance mode every time
- noautoconfig, because of the multi node installation over here don't run autoconfig

But there are many more options to use. Check the following post for more options..
http://appsdbablog.com/blog/patching/

optimize parameters

At the customer I working right now, they asked me to look at a query that did run in a test EBS database, but wouldn't run in a developer EBS database.
When running the query in the dev database, the session seems to hang and no records are returned.
So, first I looked at the explain plan from the query running in the test database. It looked like this..

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 339 K 103359
NESTED LOOPS 339 K 8 M 103359
TABLE ACCESS BY INDEX ROWID CSI.CSI_T_EXTEND_ATTRIBS 339 K 6 M 103358
INDEX RANGE SCAN CSI.CSI_T_EXTEND_ATTRIBS_N02 339 K 4066
INDEX UNIQUE SCAN CSI.CSI_T_TXN_LINE_DETAILS_U01 1 6

Then I tried running the query in the dev database and the explain plan looked like..

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 339 K 56396
HASH JOIN 339 K 8 M 56396
TABLE ACCESS BY INDEX ROWID CSI.CSI_T_EXTEND_ATTRIBS 339 K 6 M 7
INDEX RANGE SCAN APPS.XXX_CSI_T_EXTEND_ATTRIBS_N1 339 K 2
TABLE ACCESS FULL CSI.CSI_T_TXN_LINE_DETAILS 11 M 64 M 51170

It seems in the dev database the query is doing a full table scan, instead of indexed scan in the test database. Also in the test database a NESTED LOOP is used, while the dev database uses a HASH JOIN ??
I focussed on the last finding and searched for a reason why the query should use HASH JOIN instead of a NESTED LOOP.
I found out the following two init.ora parameters were different in the dev database, the optimizer_index_caching parameter and the optimizer_index_cost_adj parameter. So, I tried running the query after I changed my session settings..

SQL> alter session set optimizer_index_caching=x;

SQL> alter session set optimizer_index_cost_adj=x;

And this time the query returned the desired rows ! Next step is to edited the init.ora so the whole dev environment can use these settings...

Changed Oracle EBS Dba OCP program

It seems Oracle has changed their OCP program for EBS Dba's
At the end of 2006 we were able to participate in the beta program of EBS OCP for dba's. I completed two exams, the Oracle 11i Install Patch and Maintain Applications beta exam and the Oracle 11i System Administration beta exam.
Now Oracle has changed the OCP program and added two new exams to the list. They also deleted two exams, the Oracle 11i System Administration and Implement Oracle Workflow 11i were removed out of the OCP program and they added Applications DBA Fundamentals I and II. Also, when you are already an OCP 9i or 10g, you only have to pass the 1Z0-233 Oracle 11i Install Patch and Maintain Applications exam to become a certified EBS Dba professional.

Also, the results of the beta exams are supposed to be known in April...

See also http://www.oracle.com/global/us/education/certification/appsdba.html

Lost root password

For a colleague I had to install Oracle Infrastructure on Suse Linux. The installation went very smoothly, but at the end I was facing a big problem. As you know, at the end of any Oracle installation you are asked to run a script called root.sh as root. So, you must have access to the root password for that host. Normally that is not a problem, but this time I did not know the password. When asking other colleague's, I found out that the password was once changed, and it seems nobody knew what the new password was. So, who to reset or change the root password when you don't know the old one ?
Another colleague of mine showed me some Google results, and we tried to change a unknown root password. This is what we did..

- We restarted the operating system (Suse..) and interrupted the start up so we could use a command line
- we typed in : init=/bin/bash and resumed startup
- on the following command prompt : mount -o remount,rw /
- at this point you can enter passwd and change the root password without knowing the old one !