Oracle mix

Just before the new year, I found this site...

https://mix.oracle.com/

We all know Linkedin or Plaxo. Now Oracle has it's own network site (beta..)
Check it out and login using your Oracle sso account (otn account)

Oracle EBS R12 Applications DBA Beta Exam

Today I received an email from Oracle in which they announced the Oracle EBS R12 Applications DBA Beta Exam. This exam is open to register and this beta period runs from January 03 – April 04, 2008
The exam is called :
Oracle EBS R12: Install, Patch and Maintain Applications [1Z0-238]
This new exam is required as part of earning the new Oracle EBS R12 Applications Database Administrator Certified Professional certification.

I am not sure if I am going to participate in this beta exam....

Enterprise Manager

We had a problem using OEM 10g. During the day a database proces (ora_j001) was consuming 100% cpu. Because of this OEM coud not be reached, oms down. Looking for a solution, I ran into the job_queue_processes database parameter. This parameter was set to 10 in our database. I searched the net and could only find out that the value of this parameter should be > 0, if you want to use OEM. Prefered value is 10 (our original value...), but it looks like a value of 10 leads to the 100 % cpu problem.
I changed the job_queue_processes parameter to 2, also changed the aq_tm_processes parameter to 1. The OEM database seems to be more stable, but I don't know if everthing still works in OEM....
Has anyone got any experience with this ??

Colors in vi

So after turning off the colors in the 'ls' command on linux, I also had to deal with the colors using vi...
When reading a logfile for example, the file is presented in 5 or 6 different colors..
To turn off these colors edit the following file (as root)...

/usr/share/vim/vim60/macros/vimrc

In this file you will see the following lines:

" Switch syntax highlighting on, when the terminal has colors
" Also switch on highlighting the last used search pattern.
if &t_Co > 2 has("gui_running")
syntax on
set hlsearch
endif

You can disable coloring by placing " in front of these lines..

" Switch syntax highlighting on, when the terminal has colors
" Also switch on highlighting the last used search pattern.
"if &t_Co > 2 has("gui_running")
" syntax on
" set hlsearch
"endif

Now all colors are gone, and I believe reading is much easier ....

Colors in linux terminal

This week I started my new job with Smartapps. The first thing I usually do is check out the excisting EBS environments. I also did the same starting here...When starting a putty session on one of the EBS nodes, I noticed the different colors when using the ' ls' or 'll' commando. I find it very annoying, so I decided with my new colleagues to remove this.
I found this was caused by the use of aliasses.

alias cp='cp -i'
alias l.='ls -d .[a-zA-Z]* --color=tty'
alias ll='ls -l --color=tty'
alias ls='ls --color=tty'
alias mv='mv -i'
alias rm='rm -i'
alias vi='vim'

So, all I had to do was unalias the ' ls' and the 'll' commando (and make a new alias for ' ll' )

Upgrade R12

a few months ago, I had an EBS R12 poll on my blog. The question was if anyone already upgrade their EBS environemnt from 11i to R12. The results of the poll showed me that nobody had upgraded or was going to...
For those who are planning to upgrade their 11i environment, Steven Chan has writen an article abouot this on his blog.
Check it out !! http://blogs.oracle.com/schan/2007/11/29#a2268

Last day

Today is my last day at the customer site. I am leaving Amis after almost two years. Last Friday I had diner with my customer colleagues. Almost everyone of them is external. Because I had a great time working with them I decided to place some pictures of Friday night on this blog. In the following order you see : René Kuss, Siwa Soekhoe, Robbert Wolters and Ali Akgurbuz. Missing on the photos is Arian Stijf, who could not be there....

Thanks guys !!!

Trace Analyzer

Today I installed to Trace Analyzer (note 224270.1 from Metalink) to see if
this tool could make my trace files easier to read.
You can download the tool from Metalink in zip format (trca.zip)

Install the tool...
unzip the trca.zip file
in the trca directory start a sqlplus session as sysdba...
[oracle@vamisux32 trca]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.6.0 - Production on Sat Nov 10 17:02:08 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @install/tacusr.sql (askes for pwd and default tablespaces)
SQL> @tactab.sql
... Creating TRCA$_TRACE_ID Sequence
... Creating TRCA$... tables
NOTE:
TACTAB complete. Please check tactab.lis for any errors.
SQL> @tacpkg.sql
Taking a snapshot of DBA_EXTENTS (may take several minutes)
NOTE:
TACPKG complete. Please check tacpkg.lis for any errors.

Now the Trace Analyzer is installed in the EBS database.

I decided to create a trace file in EBS when querying journals in General ledger.

So, the trace file is created in the udump direcory. Lets see how it looks like

without analyzer....

*** TRACE DUMP CONTINUED FROM FILE ***
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /ebs/oatestdb/9.2.0
System name: Linux
Node name: vamisux32.amis.local
Release: 2.6.9-42.0.0.0.1.ELsmp
Version: #1 SMP Sun Oct 15 14:02:40 PDT 2006
Machine: i686
Instance name: oatest
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 12460, image: oracle@vamisux32.amis.local (TNS V1-V3)
*** 2007-11-10 17:52:00.876
*** SESSION ID:(85.37029) 2007-11-10 17:52:00.874
APPNAME mod='FNDSCSGN' mh=375781535 act='General Ledger Super User' ah=3773307719
=====================
PARSING IN CURSOR #65 len=70 dep=1 uid=47 oct=42 lid=47 tim=1166712422731428 hv=2110961874 ad='6746d1e4'
alter session set events='10046 trace name context forever, level 12'
END OF STMT
EXEC #65:c=2000,e=45848,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1166712422729192
WAIT #0: nam='SQL*Net message to client' ela= 9 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 1217 p1=1952673792 p2=1 p3=0
RPC CALL:FUNCTION APPS.FND_TRACE.GET_TRACE_FILENAME() RETURN VARCHAR2;
RPC BINDS:
bind 0: dty=1 bfp=0af28a20 flg=0a avl=00 mxl=32767 val=""

You see, the trace file looks like any other trace file. Difficult to read and also not easy to understand. When using the analyzer...

[oracle@vamisux32 trca]$ sqlplus apps/*****
SQL*Plus: Release 9.2.0.6.0 - Production on Sat Nov 10 18:01:52 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> start run/trcanlzr.sql oatest_ora_12460_SYSADMIN.trc
Value passed: oatest_ora_12460_SYSADMIN.trc
~~~~~~~~~~~~
...generating trcanlzr report into UDUMP server directory
Trace Analyzer Report "trcanlzr_12460_1.html" has been created in /ebs/oatestdb/9.2.0/admin/oatest_vamisux32/udump
...trcanlzr report has been generated into UDUMP server directory
...copying trcanlzr report into local SQL*Plus client directory
...trcanlzr report was copied from UDUMP into local SQL*Plus directory

When opening the html file, you see the difference....

Oracle's own virtual machine...

Oracle announces a new Oracle Virtual Machine..
Instead in vmware (for example), you can now use the Oracle VM.
Read all about it in this article...

http://www.oracle.com/corporate/press/2007_nov/ovm-ga-111107.html?msgid=5803830

EBS DBa homepage

Some time ago I created my own EBS Dba home page. This homepage is a collection off all the links I once visited because of my work as an EBS Dba. So, maybe there are some use full links for you also...and if you are missing a link..let me know and I will place it on the homepage.

url EBS Dba homepage http://oappsdba.jouwpagina.nl/

Missing responsibilities after cloning EBS

From my great colleague Siwa Soekhoe I received the following tip.

After we had cloned an EBS environment (11.5.9), it seemed some application users were missing some or all responsibilities they had in the original environment.

When checking the EBS, you could see the responsibilities were granted to the specific application users. No end date, nothing strange in the application.

So, why don't they see their responsibilities at login ??

It seems the problem was that the start_date and end_date in WF_LOCAL_ROLES did not match the information in the FND_RESPONSIBILITY table for roles/responsibilities assigned.

This issue is being solved by running the concurrent program FNDWFDSURV "Workflow Directory Services User/Role Validation" on the target environment.

Custom sql scripts in OAM

I noticed Oracle Applications Manager has some very handy sql script you can use when monitoring your EBS environment.

When starting up OAM, navigate to Site Map ----> Monitoring ----> SQL Extensions
The following scripts are available...











There is also a way to add your own personal sql scripts to this page. When you follow the steps mentioned in Metalink note 216816.1, you will find your own scripts on this page.

So, here is what you should do.

1. create a custom directory in the $APPL_TOP/admin directory


2. now create a directory called 'sql' in this custom directory

/appl/oatestappl/admin/custom/sql

[oracle@vamisux32 sql]$ ls

oa_info.sql patchlevel_ebs.sql


3. Now place your custom sql scripts in this directory


4. Bounce Apache to see the scripts in OAM


I have tested it with two custom scripts, and after bouncing Apache,
the two scripts are visible under 'Custom Extensions'...














You can run the custom scripts by clicking the 'run report' button.

Back again...

After two weeks laying down on my back because of a back injury, I started at work today.
I did not post anything the last two weeks because I could not sit down to do it :)

Hope things will be better from now on...

Maintenance wizard

Yesterday I noticed an announcement on Metalink of the 'Maintenance wizard' for the EBS R12 upgrade.
I had no idea what the Maintenance wizard looked like, or what it was supposed to do.
So, I decided to install the latest full version (2.05) of this wizard.

The wizard can be installed by downloading patch 6398371 from Metalink.
According to note 329476.1, this version only runs with a 10g database and a 10g iAS...
Here is what I did..

1. Install 10.2.0 database software
2. Install 10.1.3 iAS (choose 'Web Server and Process Management' option..)
3. create 10g database using dbca
4. create new tablespace NEWEOF
5. define new $EOF_HOME as $ORACLE_HOME/eof
6. unzip the patch in the $EOF_HOME and run the setup.sh from the config directory

So what does the wizard really do ??

According to the user's guide at least the following :


Maintenance Wizard:
• Presents a consolidated, personalized set of instructions in a step-by-step format
• Enables validation of important activities to prevent downstream issues
• Maintains log and status information for all tasks
• Enables Project Administrators to assign different groups of upgrade tasks to different users
• Downloads, merges, and installs many required patches automatically
• Provides access for Web-based operating system (OS) commands to check the upgrade processes, logs, and OS status during patch upgrades.
• Provides project management utilities to record the time taken for each task and its completion status
• Enables maintenance of multiple customer systems


I checked the wizard out, and post my experience with it very soon..

Survey added

I have added a survey to see how the readers of this blog feel about certain subjects concerning Oracle EBS..

First guest post....

So, yesterday I wrote my first guest post for the EBS OCP/OCE certification blog.
Check the blog for more interesting posts....

http://ebizocp.blogspot.com/

10g OCA certification

After waiting a few weeks, I finally received the OCA certificate....

guest posts...

A colleague blogger of mine, Mohan Dutt, asked me to write some posts on his own blog.
His blog (http://ebizocp.blogspot.com/) is about Oracle EBS OCP and OCE. Very interesting stuff. I accepted his invitation and agreed to write some posts for his blog.
I am going to share my OCE and OCP experience with his blog readers also....

So, be sure to visit his blog also.

Upgrade jinitiator

a simple tip I ran into at work.
We were working on a jinitiator upgrade (1.3.1.25 to 1.3.1.28), and the upgrade must be tested on both test and production environment.

How can we be sure only a few people will test the new jinitiator, while the rest is still working with the old jinitiator ??

The answer is easy. By using a profile option at user level.
The profile is : 'ICX: Forms Launcher ' , and will have the following value... http://host.domain:port/dev60cgi/f60cgi
When you want a few users to work with another version than the default jinitiator (for example 1.3.1.28), adjust the url like this :
http://host.domain:port/dev60cgi/f60cgi?config=jini13128 at user level.
This way only that user is working with the new jinitiator....

Also be sure to adjust the appsweb.cfg and add the second new jinitiator...

MO: Operating Unit not set....

This morning I was asked to look at a EBS login problem.

The customer received the following error when logging in :


oracle.apps.fnd.framework.OAException: Application: FND, Message Name: SQL_PLSQL_ERROR. Tokens: ROUTINE = AppsConnectionManager.appsInitialize(int,int,int,int,Connection):-1,-1,-1,0,oracle.jdbc.driver.OracleConnection@1d12d84; REASON = java.sql.SQLException: ORA-20001: Oracle error -20001: ORA-20001: FND-ORG_ID PROFILE CANNOT READ: N, OPTION, MO: Operating Unit has been detected in FND_GLOBAL.INITIALIZE.ORA-06512: at "APPS.APP_EXCEPTION", line 70ORA-06512: at "APPS.FND_GLOBAL", line 64ORA-06512: at "APPS.FND_GLOBAL", line 1028ORA-06512: at "APPS.FND_GLOBAL", line 541ORA-06512: at line 1; ERRNO = 20001; (Could not lookup message because there is no database connection) at oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java:988) at oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java:866) at oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java:4572) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:687) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418) at _oa__html._OA._jspService(_OA.java:88) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456) at org.apache.jserv.JServConnection.run(JServConnection.java:294) at java.lang.Thread.run(Thread.java:534)

So it seems the profile option 'MO:Operating Unit' is not set, or is set with a wrong value.
I decided to query the fnd_profile_option_values table and check how the profile was set. The query did not produce any rows, so the profile option is empty ??

Anyway, a colleague of mine showed me a sql script to restore the old value of the profile option.

DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('ORG_ID',, 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;

The script did put a value for the MO:Operating Unit in the fnd_profile_option_values table. After bouncing Apache, the problem was solved.