Applied Apps patches for 11g upgrade...

Yesterday I started the 11g upgrade off my own EBS database (10gr2).
Before doing anything with 11g, I first had to patch the EBS environment.
The following EBS patches have been applied :

1. Patch 3460000, 11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2

2. Patch 6328363, 11GR1 APPS INTEROPERABILITY PATCH

3. Patchset 5903765, 11i.ATG_PF.H.RUP6

After succesfully (allthough Gabor provided me some errors he experienced....thnx again for that ;), I did not ran into any error...) installing these patches, I am now ready to install the 11g database software.

More to come.....

11g database upgrade EBS 11.5.10

Today I started the database upgrade to 11g on my own EBS 11i environment.

Because I did not patch my environment since the 10gr2 upgrade, the first patch to apply on my EBS environment is patch 3460000 'ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2'

After this patch I have to install the 11g Release 1 interoperability patch for 11.5.10....

At this moment the CU2 is still running. To be continued...

Notifications not send with 10g Grid Control

We are working with 10g Grid Control (10.2.0.3) for a few months now and we noticed a problem with the notifications from OEM. It seemed OEM was holding back a lot of notifications. I could not find any error in OEM, and because some notifications did reach us by email, the email setup was oke.
According to note 285093.1 I installed the EMDiagkit to examine the problem.
Running the following statement showed me the problem..

[oemprd01@amssys07 bin]$ ./repvfy verify notifications -level 9

Please enter the SYSMAN password:

---------------------------------------------------------------------------
EMDIAG: 2008.0321 Repository: 10.2.0.3.0 31-Mar-2008 14:24:44
---------------------------------------------------------------------------
verifyNOTIFICATIONS
100. Notification backlog: 3
103. Stuck notification waiting to be delivered: 7352
104. Notification delivery delay: 2

So, more than 7000 notifications are stuck in OEM ?
I decided to log a SR on Metalink, because we were missing some critical notifications concerning our production databases...
After loggin the SR, I decided to check note 356774.1 again. I already noticed the DBMS_JOB's were running. But just to be sure I executed the following sql statements :

SQL> exec emd_maintenance.remove_em_dbms_jobs (to remove broken jobs..)

and

SQL> exec emd_maintenance.submit_em_dbms_jobs (submit all jobs again)

After these statements I recieved a few 1000 emails with old notifications !
The problem now seems to be solved, we are recieving a lot more emails than we used to.
Checking if some notifications are still hold back by OEM..

[oemprd01@amssys07 bin]$ ./repvfy verify notifications -level 9
Please enter the SYSMAN password:
---------------------------------------------------------------------------
EMDIAG: 2008.0321 Repository: 10.2.0.3.0 01-Apr-2008 10:00:07
---------------------------------------------------------------------------
verifyNOTIFICATIONS

And nothing is left behind this time....

uCertify prepkit O1Z0-043

I was aked to evaluate the uCertify prep kit oz10-034. Because I am studying for the o1z0-043 exeam anyway, I thought it would be usefull to test the prep kit.
For the OCA exam, I only used the OCA study quide from Sybex. For the OCP exam, I also used the Sybex study guide, but also th euCertify prepkit.

I must say, the prepkit looks fine to me. It's a user friendly tool, with a lot of options to choose from.



Above you see the welcome screen with all options. So what can you do with the prepkit ?
- you can choose from 7 different tests (this is great, because making exams is the best way to get used to the actual exam..)
- there are study tips
- you can read related articles
- you can use the studynotes, which are all the important subjects from the book
- offcourse the prepkit saves your test results, so you can check your test history
- you can create a readiness report, check if you are already prepared for the real exam
- you can update the prepkit, so it's never out of date !

I think the tool is great for preparing for your Oracle OCP exam, and I am sure the prepkit will help me prepare.

Oracle 11g certified with EBS11i

Today 11g certification for EBS 11i was announced on the Steven Chan blog.
See http://blogs.oracle.com/schan/2008/03/26#a2532 for more info..

Startup/shutdown Oracle EBS when linux is booted

For a demo, I installed Oracle EBS on a laptop with Oracle Linux version 4.
Because the consultants take thelaptop with them during the demo, and I am not present at that demo, I wanted the Oracle EBS to start when the laptop was turned on and also stopped when the laptop was turned off...

Starting Oracle EBS at laptop startup was not that kind of a problem. I just created a script in the /etc/rc.d/init.d directory called 'oracleebs'. Just a simple start script for EBS, just be sure to put this line in the script...

# chkconfig: 2345 99 10

This lets the chkconfig tool know in which runlevel the script must be started (2345) and the script should be started after all ohther scripts (99), the stop must be the 10th script to be run (10)

After creating the script, add your script using the chkconfig tool

chkconfig --add oraclebs

to check if the script was added.....

[root@ebsdemo init.d]# chkconfig --list

ipmi 0:off 1:off 2:off 3:off 4:off 5:off 6:off

sysstat 0:off 1:on 2:on 3:on 4:on 5:on 6:off

readahead 0:off 1:off 2:off 3:off 4:off 5:on 6:off

apmd 0:off 1:off 2:on 3:on 4:on 5:on 6:off

arptables_jf 0:off 1:off 2:on 3:on 4:on 5:on 6:off

oracleebs 0:off 1:off 2:on 3:on 4:on 5:on 6:off

I tested the startup and everything went fine. But when shutting down the laptop, the script was not executed. Offcourse the Oracle EBS was stopped, but I wanted it to be stopped the right way. I noticed the K* scripts in the runlevels 0,1 and 6. So why isn't the stop script executed ??

I found out, a simple touch was missing :)

The shutdown is checking if the proces that has to be stopped is actually running. If the proces is running, there must be an entry in the /var/lock/subsys...
So, when a touch /var/lock/subsys/oracleebs is added to the startup part of the script, the shutdown will also execute the stop part of the script. This time the Oracle EBS was stopped nicely.

Oh, booting linux is hanging on ' starting killall' .....
Don't forget to remove the created lock after the EBS is stopped. At the end of the script add
rm -f /var/lock/subsys/oracleebs
Now, booting linux runs smoothly

Oracle White papers

On the Oracle Insight blog I found this interesting site : the Oracle White papers site
A nice site with a lot of white papers concerning Oracle..
You have to register yourself before accessing the white papers.
Enjoy !!

uCertify

Last week I received an email from uCertify. This company creates preparation kits for different certification programs, like Oracle OCP Dba....
They noticed my blog and asked me if I was willing to review one of their preparation kits and share my experience with this prep kit. Because I am studying for my OCP 10g, I agreed and yesterday I received the registration key for the 1Z0-043 PrepKit. During the download I noticed what in the prepkit, and I must say..it looks good !

Full version includes:
282 challenging questions with full explanation
199 Study Notes and Articles
5 Full length interactive tests
Detailed reports and analysis tools
No adware, malware or spyware
One year free upgrades
24x7 Support
100% money back guarantee

I will continu my study and use the prep kit also. I will share my experience with the prep kit in future posts...
For more info check the site http://www.ucertify.com/

Accessing Oracle EBS from linux client...

We cloned an Oracle EBS enviroment to a laptop for demo purposes. We installed Oracle Enterprise Linux 4, and cloned an excisting EBS environment.
After I got the EBS running, I tried to access the application from the linux laptop. But there was one problem, Jinitiator only works with windows. So, I was able to download the jini.exe, but could not execute it. Another way to work with the Oracle EBS (11.5.10.2) is using the sun java plugin.
I found out no plugins were installed in Firefox yet, so I had to download the sun java plugin.
I decided to download version 1.5.0.12. After installing the downloaded file, the plugin has to be noticed by Firefox. You can do this by creating a link in the FIREFOX_HOME/plugins directory.
Create a link to the libjavaplugin_oji.so from the installed jre...
ln -s /usr/java/jre1.5.0_12/plugin/i386/ns7/libjavaplugin_oji.so ./libjavaplugin_oij.so

After the link is created, the java plugin is recognized in Firefox.
Next step is to let EBS now, you can also use the sun java plgin instead of the default jinitiator. This can be done by adjusting the $OA_HTML/bin/appsweb_sid.cfg and change the following lines.

sun_plugin_mimetype=application/x-java-applet;jpi-version=1.5.0_12
sun_plugin_version=1.5.0_12

After bouncing the webserver you can use the sun java plugin, but also the default jinitiator.

GAPP performance approach

Yesterday evening I visited an Amis query about performance. Speaker this time was Gerwin Hendriksem from Amis, a real performance freak. He designed his own approach for solving performance problems, and it was a good session. Gerwin used Oracle data mining to spot performance issues. He gathered system data for more then a year and using data mining to analyze and predict the bottlenecks. He succesfully used this approach with at least two customers.
Next week he will be giving the same presentation in the VS at the Hotsos symposium in Dallas. He also gave his approach a name, the GAPP approach.
So, be ware of te GAPP....

Changing IP and hostname in Application Server 10g

Because we moved our new OEM10gr2 server to the datacenter, the IP address and the hostname changed. When added to the datacenter I had to start all the services again, but first I had to change the opmn configuration. Instead of changing all the config files by hand, I noticed a shell script in the $OMS_HOME/chgip/scripts, called chgiphost.sh

This script changes all the config files and replaces the ip and hostname. It will ask you for the source and target ip/hostname...

Saved me some time...

Discovering windows host in linux oms...

Yesterday I decided to add a windows host to my Oracle 10gr2 Grid. The 10gr2 Grid control is running on Oracle Enterprise Linux release 4.5
I downloaded the windows agent 10.2.0.3 and installed this agent on the windows machine. After installing I checked my OEM site to see if the target was already discovered....
I did not see the windows host, so I decided to check the windows agent log files. The following error was found :

Thread-7244 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://oemserver.local:1159/em/upload: retStatus=-12008-02-14 12:45:17 Thread-928 ERROR upload: Error in uploadXMLFiles. Trying again in 900.00 seconds or earlier.2008-02-14 12:45:32 Thread-2924 ERROR upload: Error in uploadXMLFiles

It seems the agent cannot connect to the oms, but why ? I tried uploading the data manually...

D:\oracle\agent\10203\agent10g\BIN>emctl upload
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

Checking Metalink brought me the sollution. It had to unsecure the agent...

To unlock the oms you do
OMS_HOME/bin/emctl secure oms unlock

Then restart the agent
AGENT_HOME/bin/emctl start agent

and unsecure it using:
AGENT_HOME/bin/emctl unsecure agent

then issue a clearstate for the agent:
AGENT_HOME/bin/emctl clearstate agent

then attempt the upload
AGENT_HOME/bin/emctl upload agent

And thsi time the upload was succesfull. Checking the status of the agent today showed me the following...

D:\oracle\agent\10203\agent10g\BIN>emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.3.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.2.0
Agent Home : D:\oracle\agent\10203\agent10g
Agent binaries : D:\oracle\agent\10203\agent10g
Agent Process ID : 6828
Agent URL : http://agenthost.local:3872/emd/main/
Repository URL : http://oemhost.local:4889/em/upload/
Started at : 2008-02-14 14:36:10
Started by user : SYSTEM
Last Reload : 2008-02-14 14:36:10
Last successful upload : 2008-02-15 10:39:37
Total Megabytes of XML files uploaded so far : 20.17
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 95.40%
Last successful heartbeat to OMS : 2008-02-15 10:41:37
---------------------------------------------------------------
Agent is Running and Ready

Concurrent Manager recovery

This morning I faced an issue with the concurrent managers in a production environment.

After bouncing the database because of a memory fault, I decided also to startup the concurrent managers again.
But after starting them, using the start script in the $COMMON_TOP/admin/scripts, the application showed me no concurrent managers ??
Checking the logfile of the internal manager for errors...



APP-FND-01564: ORACLE error 1000 in afpsmrsc
Cause: afpsmrsc failed due to ORA-01000: maximum open cursors exceeded.
The SQL statement being executed at the time of the error was: &SQLSTMT and was executed from the file &ERRFILE.



The parameter open_cursors seemed high enough, so I looked for another reason. I also checked the v$open_cursor, which showed me a lot of open cursors. I already bounced the database, so that did not solve anything.


I decided to use the 'Concurrent Manager recovery' in Oracle Application Manager.



This recovery proces showed me concurrent managers with an active status in the database, but no active process on the OS or a active connection to the database. Maybe, that's the reason for the open cursor error ?
After cleaning them using this recovery tool, and going throuhg all the steps, I started the concurrent managers again. And this time, they started !

Grid control 10g

The last two days I have been busy installing and upgrading Grid control 10g.
Because of problems wth an older version of OEM 10g , we decided to install a new version (10gr2).
On a new dell server I installed Oracle Enterprise Linux release 4 (update 6). After installing linux, I started the 10.2.0.1 Grid install.
Next step was the 10.2.0.3 upgrade of the oms and agent. I also decided to upgrade the database itself, which is default 10.1.0.4 (even after the 10.2.0.3 upgrade...)
Before upgrading the database, I tested the Grid control. Everything worked fine. Stopped and started the oms/agents a few time to check stability. No problems so far.
I started the upgrade, which means I first had to install the 10.2.0.1 Oracle software in a new $ORACLE_HOME. After this fresh install, upgrade the new ORACLE_HOME to 10.2.03. When the software is patched to the desired level, use the dbua from your new home to upgrade the excisting 10.1.0.4 database. At this point, some problems occured.
At first the dbua could not perform any OEM configuration ?? Checking the logfile only told me to try this manually...so I did. Oke, the configuration ended without errors, so lets try to start things up.
I started up the oms, no problems. Next, I started the agent....
Seems the agent would not start, the http server could not be started ? Does thsi has anything to do with my database upgrade ??? I can't see how ?
The logfiles showed me there was a port conflict. Suddenly the agent http server wants to use the same port as the oms http server ? Before the upgrade the could work together without a problem, but suddenly they are fighting over the same 6102 port....
The problem was fixed easy, I just changed the local port in the /oem/oemprd01/oms10g/opmn/conf/opmn.xml file.
After this change, the startup succeeded.
But I still don't understand why this port conflict arrived after the database upgrade ?

plsql developer

When trying to compile a package using plsql developer in a 9.2.0.1 Oracle database, the developer ran into an ORA-00600 error. The error is generated in plsql developer and looks like this...

This problem can easily be fixed when bouncing the database, but the problem was that this production database could not be stopped. I had to find another way to solve this.

Could it be they are using a wrong version of plsql developer ? I couldn't find out if they were using a wrong version, so upgrading plsql developer seems useless....

I found another work around for this problem.

Maybe it has something to do with the "debug information" mode. To verify this, go to Tools > Preferences > Debugger tab page, disable the "Add debug information when compiling" option, and retry the compilation.

The developers tried to compile the package again and it worked...

Using logminer

A customer wanted to know how a certain record in their EBS database was updated.
A colleque of mine decided to use logminer to find out what happened to that specific record.
Using logminer, all changes in our archived logfiles are written to the v$logmnr_contents and you can query this view using sql statements.

So, how does it work ? We choose the online catalog variant..(see Meatlink note 291686.1 for more options)

First, try to find out which logfiles you should examine. In our case, when was the record updated. We figured it had to be between 10:00 am untill 04:00 pm. So we had to use like 33 archived logfiles ! Teh change had to be in one of them.
Once you have decided which logfiles to use, you must 'add' them using the following syntax :

SQL> execute DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '/XXX01.LOG',OPTIONS =>DBMS_LOGMNR.NEW);

The first logfile must be added with the LOGMNR.NEW option. If you have more logfiles, the follwoing ones must have the LOGMNR.addfile option. Otherwise, only the last logfile is used :)

When all logfiles are added, you can start the logminer

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => RBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Now, you can query the v$logmnr_contents view. We decided to spool the output, make it a little bit eassier to search.

SQL> select username, operation, SQL_REDO,SQL_UNDO FROM V$LOGMNR_CONTENTS where sql_redo like '%updated_table%';

When finished..

SQL> EXECUTE DBMS_LOGMNR.end_LOGMNR

Now the searching begins. We finally succeeded, but searching is not easy.

vmware converter

For a colleague ( an Oracle EBS functional consultant), I had to install 2 EBS environments on a hard disk (500gb). Using vmware, he would be able to play around, or use the the environments for a demo. I already created both environments using vmware workstation, but decided it would be better to use vmware server. Because vmware server cannot work with the already created workstation environments, I thought starting over waas the only option. Just to check if someone else already faced the same problem, I decided to google for 'vmware conversion' and I found something about Vmware Converter. It seems vmware already created a tool for this kind of conversions !! You can download the tool from the vmware site. I converted the first workstation environment and it worked. I started the new vmware server environment and everything worked like before. Great tool !!

Tagged.....

I have been tagged.....three times already :). Gareth Roberts , OCP Advisor and Atul Kumar tagged me.
Following the rules of this new tagging feature among Oracle bloggers, I shuold now blog 8 things about myself and tag other Oracle bloggers. Look here for the rules.

So 8 things about myself you don't know already....Publish Post

1. My name is actually Bastiaan
2. I have a seasons ticket for Ajax for 18 years now...
3. My gaming alias is Djewie
4. I am part of a gaming (COD4) clan called Bullet Proof Underwear
5. I have studied economics, but never finished it
6. I love to get together for a drink with friends
7. My youngest son has been sick for three years now (..he is three years old :(..)
8. I love running to clear my head

Now, who am I going to tag ?

1. Marco Gralike
2. Rene Kuss
3. Arian Stijf
4. Bandari Huang
5. Johan Louwers
6. Richard Byrom
7. Madan Mohan
8. Navdeep Saini

Maybe some of them are already tagged by now, but we will see.

Recover database

Monday evening I was told a company needed some help recovering an important database.
They were not able to get the database back online. The next morning, after the 'oke' from my manager, I visited the company. It seemed the just moved to a new building, stopped all servers and started them again in the new building. The Oracle Dba who used to work over there was gone, so nowbody really knew what to do or how i should work.
The database that could not be started was a online clone, using snapshots, from a production database. The production database could not be stopped, so they used the online snapshot technology to clone the database. So, what was the problem. Using a online backup for your clone means recovery, so I first checked the status of the database. It seemed the status was ' mounted', so the control creation succeeded ?
Next I tried a to open the database, but this command resulted in the following error..


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/ORA/ONTW/DATA/system01.dbf'

More recovery ??
Because I did not know what happened exactly, I decided to clone again. This way I could see with my own eyes what was going wrong.
During the copy fase of cloning, I also decided to create a new backup of the controlfile. The script used for the new controlfile creation was already a year old. On the production database I created a new backup using the ' alter database backup control file to trace' statement. I was going to use this one instead of the old one. Maybe they had removed or added some datafiles...
After the copy was ready I could start the actual clone.

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jan 9 09:24:30 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1060074720 bytes
Fixed Size 738528 bytes
Variable Size 922746880 bytes
Database Buffers 134217728 bytes
Redo Buffers 2371584 bytes
SQL> @control_ontw.sql
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select status, checkpoint_change#,
2 to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as
3 checkpoint_time,
4 count(*)
5 from v$datafile_header
6 group by status, checkpoint_change#, checkpoint_time
7 order by status, checkpoint_change#, checkpoint_time;

STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
------- ------------------ -------------------- ----------
ONLINE 1.2965E+10 09-JAN-2008 08:45:49 43


In the create controlfile I used the resetlogs, which is needed when renaming the database, and the query showed me the datafiles were in sync.
So, now lets try to recover..

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 12964929028 generated at 01/09/2008 08:45:49 needed for
thread 1
ORA-00289: suggestion : /ORA/ONTW/ARCH/arch60558.dbf
ORA-00280: change 12964929028 for thread 1 is in sequence #60558


Specify log: {=suggested filename AUTO CANCEL}

Strange, the archive logfile suggested is not copied. And on the production server, the file isn't there neither ??? So, the recover process is asking me for a future archive log ?? This seems strange, the data that's missing must be in the online log files.
Searching the net showed me the solution. Just enter the path to your online log files !!
The first logfile didn't fix the problem, but the second one did..

Specify log: {=suggested filename AUTO CANCEL}
/ORA/ONTW/DATA/redo02a.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

The database is open and ready to use.

Cloning error

Yesterday I started cloning an EBS environment. After running the preclone, I started copying the enviroment on our netapp filer.
After the environment was copied, I started the actual clone.
But when adcfgclone is trying to create my new controlfile, the following error is reported in my alert.log..

Fri Jan 4 10:30:01 2008
scumnt: failed to lock /oebs/pubtst22/pubtst22db/9.2.0/dbs/lkPUBTST22 exclusive
Fri Jan 4 10:30:01 2008
ORA-09968: scumnt: unable to lock file
Linux Error: 37: No locks available
Additional information: 300
Fri Jan 4 10:30:01 2008
ORA-1503 signalled during: CREATE CONTROLFILE REUSE SET DATABASE "PUBTST22"

It seems linux cannot lock the file that's needed to mount the database...
I searched Metalink and google, but could not find anything that could cause my problem.
Finanlly we decided to umount the complete mount point. But all directories were still visible ???
We umounted again, adn this time the directories were gone. It seemed the mount point was mounted twice ?
After mounting againg, I started the clone again. This time no lock problems.....

Fri Jan 4 11:04:30 2008
Successful mount of redo thread 1, with mount id 2212425917
Fri Jan 4 11:04:30 2008
Completed: CREATE CONTROLFILE REUSE SET DATABASE "PUBTST22"