Thursday, December 25, 2008

Christmas 2008

I want to wish everybody a merry X'mas and a very good 2009 !!!!

Tuesday, December 09, 2008

Oracle Application Manager

We have a lot off forms users in out eBS environment. Because of this there are also a lot of forms sessions running for a long time. I use the Oracle Application Manager to check the current forms sessions and kill them when running for more then 10 hours....

In OAM the forms sessions screen looks like this..

This print is taken from a test environment, but in the production environment you will see more forms sessions. Select a session and press the 'Sessions Details' button. On the next screen you will see the logon date and time, the session id, status and much more. Also you can kill the forms session.

Monday, November 24, 2008

Ucertify Prepkits

And again I am able to offer a 10% discount on any PrepKit from Ucertify.
Just go to the site and use the following discount code 'BASK10'. Good luck with your certification...

Wednesday, November 19, 2008

Oracle Modernization Solutions

'A Practical guide to planning and implementing SOA Integration and Re-architecting to an Oracle platform'

Last week I recieved the book 'Oracle Modernization Solutions' from Packt Publishing. I was asked to review this book. The book is written by Tom Laszewski and Jason Williamson. The book describes different ways to modernize your business IT. The book is a technical dive on two approaches for Legacy Modernization, namely SOA enablement and re-architecture. They focus on these two methods because SOA is a modernization option for staying on a mainframe and re-architecture for moving off the mainframe.

According to the book there are several business objectives for a modernization project :
- Agility (enable innovation within the business)
- Value (expose and extend business value locked in the lagacy system)
- Reduced Cost
- Dwindling Technology Resources (there seems to be a decline in experienced IT personnel)

I just started, but already the book is taking most of my free time :) It's good stuff and very interesting. Although I have not finished the book yet, I already recommend this book to other Oracle technical specialists (like dba's or architects)

Monday, November 03, 2008


This morning I arrived at work, as a freelance Oracle eBS Dba. Did I notice anything different ? Not yet, but it sure feels good !

Thursday, October 30, 2008

Which Oracle books should be published ?

Packt, an online book shop will be publishing more than a dozen Oracle titles in 2009. In order to better understand what information the Oracle community wants, they are looking at which topic areas and tools users would like to see covered. Therfore they created a short survey which can be found here
So, check it out and let them now which books to publish.

Tuesday, October 14, 2008

Oracle E-Business Suite Technology Stack Certifications

Another great experiment from Steven Chan. A single page with all the certifications for eBS !! Normally I visit Metalink to check if a certain combination is certified, but it takes a few clicks before I see the result.
Now all the eBS certifications are bundeled in one site. Great !
Check the site yourself...

Thursday, September 25, 2008

Oracle's Database Machine..

Because my former Amis colleques are again present at the OOW, I check the Amis blog every morning for Oracle news. This morning I noticed the following entry about a new oracle database Machine (hardware) called the Exadata Storage Server. So, after introducing their own OS (Oracle Enterprise Linux), they also created their own hardware to run the Oracle database.
Check the Amis blog for more detailed info...

Wednesday, September 24, 2008

Wednesday, September 10, 2008

Cloning eBS on Oracle Enterprise Linux 5

When cloning an eBS environment on Oracle Enterprise LInux 5, the following error is shown on both the db and app Tier.

Checking for make... found - /usr/bin/make
Checking for ld... found - /usr/bin/ld
Checking for cc... found - /usr/bin/cc
Checking for ar... found - /usr/bin/ar
Checking for ksh...
Unable to find 'kshell' in path
Unable to locate all utilities with system path.

Looking in the clone script showed me the error came from the script in the $COMMON_TOP/clone/bin directory for appTier and the $ORACLE_HOME/appsutil/clone/bin directory for the dbTier. In the script it's checking for ksh using the following syntax...

# Verify for ksh if its a linux only platform

if test $UNAME = "Linux"; then
printf "Checking for ksh...\t";
VERSN=`/bin/ksh -c 'echo $KSH_VERSION' grep "PD KSH";`;
if test $EC != "0" ; then
printf "\nUnable to find 'kshell' in path\n";
printf "found - $VERSN\n";

When manually checking for the ksh version on Linux 5...
[root@ebs ~]# /bin/ksh -c 'echo $KSH_VERSION'

But when doing the same thing on Linux 4...
[root@ebs ~]# /bin/ksh -c 'echo $KSH_VERSION'
@(#)PD KSH v5.2.14 99/07/13.2

So, it seems the check does not work anymore on Linux 5 ???
For now, I changed both scripts so the check didn't take place. I also logged an TAR, a new script should be delivered....

The new script is delivered in patch 6718351 '11I RAPIDCLONE CONSOLIDATED FIXES JUL/2008'


I decided to leave my current company and start my own. I have been thinking about contracting for a year now, and finally took the step. From 1-12-2008 I am working as a contractor Oracle eBS Dba. I already registered my company, called e-BSolutions

I looking forward to this new career move and will continue blogging....

Tuesday, September 02, 2008

Google Chrome (Beta)

Tonight I downloaded the new Google browser called Chrome. It is  a beta version , but I am going to try it anyway.
Will eBS run in this new browser ?

Monday, August 18, 2008

Meet the experts on the OCP blog !

Mohan Dutt, a.k.a. OCP Advisor, is presenting a new weekly feature on his OCP blog ( It is called 'Meet the Experts' and its' a Q/A with certain Oracle Certified professionals. I am proud to say that I was also asked to be part of this new feature. So, I delivered my certification experience and it will soon be on the OCP blog. So keep checking the OCP blog for the Q/A sessions !!

Friday, August 15, 2008

How to get Discoverer4i working on Oracle Linux5

This morning I tested the sollution on another EBS environment running on Oracle Linux 5. I also upgraded the database for this environment from 9i to, so it's also a test for Discoverer4i on 10gr2.
I tried starting the disco processes using the default start script from Oracle in the $COMMON_TOP/admin/script/ORACLE_SID. When the script tried to start the locator, I received the same error again...

oebs/oracle/ora/8.0.6/jre1183o/lib/i686/green_threads/ symbol errno, version GLIBC_2.0 not defined in file with link time reference (
Unable to initialize threads: cannot find class java/lang/Thread
Could not create Java VM

Setting the LD_ASSUME_KERNEL didn't help. After setting this parameter on Linux 5, nothing else is working anymore. I solved this issue by performing the following steps :

1. adjust the in the $ORACLE_806_HOME/discwb4 directory.
In this file, change the VBROKER_JAVAVM parameter, it was pointing to the jre in the 8.0.6 home, to ...

VBROKER_JAVAVM="/usr/java/jdk1.5.0_06/jre/bin/java - Dorg.omg.CORBA.ORBClass=com.visigenic.vbroker.orb.ORB -Dorg.omg.CORBA.ORBSingletonClass=com.visigenic.vbroker.orb.ORB"

After setting this parameter to it's new value, also edit the start,stop and status scripts.
Edit the start/stop script in the $8.0.6_HOME/discwb4/util directory and also the script in the $COMMON_TOP/admin/scripts
In these script change the check for 'jre' to 'java' ...

for example in the (8.0.6_home/discwb4/util) change
findprocess "JRE" ${lpid:-0} into findprocess "java" ${lpid:-0}

After these changes, the discoverer4i is working on Oracle Linux 5 and with a database !!

Thursday, August 14, 2008

Issue solved....

I solved the issue with Discoverer on Linux 5.
I will post the workaround tomorrow, because I have to test it on another enviroment also. To get Discoverer working I had to edit some config files, change it back later on. Patch the disco libraries, undo the patch and so on. To get a good image of the sollution I want to test it on another Linux 5 EBS environment.
The service request is still open, no answer from Oracle yet. Maybe because Disco 4 is desupported....

Wednesday, August 13, 2008

Discoverer on Oracle Linux 5

After I had the EBS running on Oracle Linux 5, I noticed another problem.
When trying to start the disco proces using in the $COMMON_TOP/admin/scripts directory I noticed the following error :

/oebs/pubtar21/pubtar21ora/8.0.6/jre1183o/lib/i686/green_threads/ symbol errno, version GLIBC_2.0 not defined in file with link time reference ( to initialize threads: cannot find class java/lang/ThreadCould not create Java VM

In my last post about Oracle Linux 5, I mentioned that the LD_ASSUME_KERNEL must be unset. In earlier versions this parameter had to be set if you wanted to install or work with EBS 11i. I searched Metalink for a sollution for my Disco problem. The answer was found on Metalink....
Before starting the Disco processes, set the LD_ASSUME_KERNEL parameter !!!!
But when the parameter is set, nothing else is working anymore. So, here is another problem with Linux 5. I already logged an service request. To be continued.

Thursday, August 07, 2008

Cloning 11i EBS on Oracle Linux 5

We have upgrade our Linux OS to Oracle Linux 5. We have 11i running on release 5, and today I tried to clone an EBS environment. At the beginning of the db_Tier the clone script runsinto an error.

Enter the APPS password [APPS]:
Checking for make... found - /usr/bin/make
Checking for ld... found - /usr/bin/ld
Checking for cc... found - /usr/bin/cc
Checking for ar... found - /usr/bin/ar
Checking for ksh...
Unable to find 'kshell' in path Unable to locate all utilities with system path.

Strange error, because ksh is located in de /usr/bin and this is in the $PATH

Oracle appl: /oebs/oracle/home/oracle> which ksh

I searched Metalink and Google, and found thsi problem also occurs when installing 11.5.10 on Oracle Linux 5.
I checked the script and found the problem :

## Verify for ksh if its a linux only platform#
if test $UNAME = "Linux"; then

printf "Checking for ksh...\t";
VERSN=`/bin/ksh -c 'echo $KSH_VERSION' grep "PD KSH";`;
if test $EC != "0" ; then
printf "\nUnable to find 'kshell' in path\n";
printf "found - $VERSN\n";

It's checking for ' PD KSH' and that's not installed when installing Oracle Linux 5 !!!!!!
So, it looks like an error in the script. I searched Metalink again, but could not find anything in the documents (316806.1)
I removed the ksh check from the script to see if it would run this time.
And it did......
Another problem is the script. This sh script is called by the .env file in your ORACLE_HOME and it produces library errors. A patch has to be installed, patch 6365595 from Metalink solves this issue. The patch replaces the shell script in the $AD_TOP/bin directory. But not in your 9.2.0 ORACLE_HOME ???? You have to copy the manually to your $ORACLE_HOME/appsutil/bin (9.2.0) directory...

The same error also occurs when running the app_Tier clone. So dont forget to edit the same script in the $COMMON_TOP/clone/bin

Tuesday, July 29, 2008

Installing new NLS...

I was asked to install an new NLS language on our excisting EBS environment. I had never done this before, usally the NLS is installed during a fresh install of EBS. This time I had to install the German language next to our US end NL languages.

Installing a new language is one thing, I downloaded the German langugae cd from e-delivery. This is a patch that can be installed, so no problem so far. I also downloaded the online help patch from Metalink and started patching.

But how to get the new language one the same pacthlevel as our other languages ? Should you look into every patch ever applied ?? I searched Metalink for a answer and found the following note 252422.1. According to this note Oracle provides you a synchronization patch for your NLS languages. All you have to do is run a perl script $AD_TOP/bin/
This script genrates an manifest $APPL_TOP/admin/$TWO_TASK/out/adgennls.txt you should upload to Oracle (see the note for prereqs etc..)
I created the manifest for our test enviroment and it looks like this :

# Release: 11i# Active languages: D, NL, US# Manifest generated by 115.7
abm admin/import/US abmactwk.dis 115.1 D:115.1:0 NL:115.1:0

abm admin/import/US abmcowk.dis 115.1 D:115.1:0 NL:115.1:0
abm admin/import/US abmdepwk.dis 115.1 D:115.1:0 NL:115.1:0
abm admin/import/US abmexpwk.dis 115.1 D:115.1:0 NL:115.1:0
abm admin/import/US abmmatwk.dis 115.1 D:115.1:0 NL:115.1:0
abm patch/115/discover/US abmactaccdaseflo.eex 115.7 D:115.7:0 NL:115.7:0
abm patch/115/discover/US abmactaccdavaflo.eex 115.6 D:115.6:0 NL:115.6:0
abm patch/115/discover/US abmactaccflo.eex 115.5 D:115.5:0 NL:115.5:0
abm patch/115/discover/US abmactbao.eex 115.5 D:115.5:0 NL:115.5:0
abm patch/115/discover/US abmactdrvflo.eex 115.5 D:115.5:0 NL:115.5:0
abm patch/115/discover/US abmactdrvsrcflo.eex 115.5 D:115.5:0 NL:115.5:0

The NL and D files are compared to the US files. If different your NLS is patched by the patch deliverd by Oracle.
So far, I have recieved a Dutch synchronization patch.

Wednesday, July 23, 2008

Agent upload fails...ret = -2

For some time now, I had some trouble with a database instance in OEM. The agent refused to upload his data to the OMS, giving me the Failed to upload file Bxxxxxx.xml, ret = -2 error.
In the log file, the following error occurs :

Thread-4595721 ERROR upload: Failed to upload file B0012907.xml, ret = -22008-07-23
Thread-4595721 WARN upload: FxferSend: received http error in header from repository: http://oemrepository/em/upload/

I bounced the agent, the OMS...even patched the OMS, but nothing solved this problem. Until I noticed the same problem in a forum. I tried the following on the target machine..

oemagent@oebs04:(state)> rm *
oemagent@oebs04:(collection)> rm *
oemagent@oebs04:(upload)> rm *
oemagent@oebs04:(emd)> rm lastupld.xml
oemagent@oebs04:(emd)> rm agntstmp.txt
oemagent@oebs04:(emd)> rm blackouts.xml
oemagent@oebs04:(emd)> cd ..
oemagent@oebs04:(sysman)> cd ../bin
oemagent@oebs04:(bin)> ./emctl clearstate agent
Oracle Enterprise Manager 10g Release
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully
oemagent@oebs04:(bin)> ./emctl start agent
Oracle Enterprise Manager 10g Release
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
Starting agent ........ started.
oemagent@oebs04:(bin)> ./emctl reload agent
Oracle Enterprise Manager 10g Release
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
EMD reload completed successfully
oemagent@oebs04:(bin)> ./emctl upload agent
Oracle Enterprise Manager 10g Release
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
EMD upload completed successfully
oemagent@oebs04:(bin)> ./emctl status agent
Oracle Enterprise Manager 10g Release
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
Agent Version :
OMS Version :
Protocol Version :
Agent Home : /oebs/oemagent/10.1.0
Agent binaries : /oebs/oemagent/10.1.0
Agent Process ID : 14591
Parent Process ID : 14588
Agent URL :
Started at : 2008-07-23 08:52:23
Started by user : oemagent
Last Reload : 2008-07-23 08:52:38
Last successful upload : 2008-07-23 08:52:52
Total Megabytes of XML files uploaded so far : 2.75
Number of XML files pending upload : 1
Size of XML files pending upload(MB) : 0.02
Available disk space on upload filesystem : 47.02%
Agent is Running and Ready

And this solved the problem !

Tuesday, July 22, 2008

OEM problem solved...

The problem mentioned before has been solved. Like most of us suspected, there was a synonym missing.

2 from all_synonyms
3 where synonym_name like 'MGMT_VIEW_UTIL';

no rows selected

SQL> create public synonym MGMT_VIEW_UTIL FOR MGMT_VIEW_UTIL;

Synonym created.

After creating the synonym, the reports look fine. I believe this porblem was caused by the export/import from the sysman schema.

Friday, July 18, 2008

Timezone in OEM ?

I am facing a strange problem. Three days ago I tried to run a report in OEM, a database Availability History report. I have to run this report every month for a customer, but this time the output showed an error !

The report showed 'Error rendering element. Exception: ORA-00904: "MGMT_VIEW_UTIL"."ADJUST_TZ": invalid identifier'

Checking Metalink didn't help me, it seems the error has something to do with a timezone ? It's true that the OSM and the target agent have different timezones, both Europe but different counties. But this has always been the case, so why suddenly an error ?? Could it have something to so with the recent export/import of the sysman schema ?

I tried to change the timezone of the target agent by adjusting the in the $AGENT_HOME. That did not solve the problem, even worse...the agent did not want to start anymore. I canhed the parameter back to the old timezone, hoping this would solve the problem. Reloaded the agent, bounced the oms and database. This time the agent started, but after a few seconds, when trying to upload data, the agent was killed.
Checking the emagent.trc file for errors showed me the following :

2008-07-15 16:59:48 Thread-2824600480
WARN upload: FxferSend: received fatal error in header from repository: Updating timezone region of a target is not supported. Use mgmt_target.set_agent_tzrgn() API to modify the timezone region.(target name = type = oracle_emd)(new tzrgn = Europe/Amsterdam)(old tzrgn = Europe/Warsaw)ORA-06512: at "SYSMAN.CHECK_DUPLICATE_TARGETS", line 92ORA-04088: error during execution of trigger 'SYSMAN.CHECK_DUPLICATE_TARGETS'

It seems the timezone change was still in proces, even after changing the again.
The following steps solved the agent problem...

oemagent@oebs20:(bin)> ./emctl resetTZ agentOracle Enterprise Manager 10g Release (c) 1996, 2006 Oracle Corporation. All rights reserved.Updating /oebs/oemagent/10.1.0/sysman/config/ zone set to Europe/Warsaw.
To complete this process, you must connect to the Grid Control repository as user sysman, and execute: SQL>exec mgmt_target.set_agent_tzrgn('','Europe/Warsaw')

The agent did start after this, but the report still showed the same error !!!

Wednesday, July 16, 2008

CPU July 2008...

This morning I received an email from Oracle......The CPU july 2008 was released yesterday.

Tuesday, July 15, 2008


After being gone for two weeks on a holiday in Spain, I was glad to find out I was able to continu the CPU April 2008 patches (first the ATG RUP5 patches had to be applied). So, right now I am patching different UAT environments. I also checked if the CPU 072008 was already announced so I could add the july pacthes, but I did not see any announcement on Metalink. Maybe at the time I am patching the production environments...

Friday, June 20, 2008

ORA-00600 solved....

The ORA-00600 from my previous post is solved. Also the notifications are being send again.
The 'ORA-00600 [kwqidrdq: loop], [0], [0], [0], [0], [], [], []' error was shown in the EMD_NOTIFICATION.QUEUE_READY.
Normally you receive this error wwhen records are deleted manually from queue tables, which is unsupported (also see note 1070715.6).
The problem was solved by executing the following statements in sql :

[oemprd01@amssys07 udump]$ sqlplus /nolog
SQL*Plus: Release - Production on Thu Jun 19 08:43:53 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn / as sysdba

SQL> show parameter aq


------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1

SQL> alter system set aq_tm_processes=0;
System altered.

SQL> conn sysman
Enter password:
SQL> delete from AQ$_mgmt_notify_qtable_I i 2
where not exists (select t.msgid from mgmt_notify_qtable t where i.msgid = t.msgid);
1 row deleted.
SQL> delete from AQ$_mgmt_notify_qtable_H h 2

where not exists (select t.msgid from mgmt_notify_qtable t where h.msgid = t.msgid);
1 row deleted.
SQL> delete from AQ$_mgmt_notify_qtable_T ti 2

where not exists (select t.msgid from mgmt_notify_qtable t where ti.msgid = t.msgid);
0 rows deleted.
SQL> commit;
Commit complete.

SQL> conn / as sysdba
SQL> alter system set aq_tm_processes=1;
System altered.

After this the ORA-00600 error was gone, trace files were no longer generated in the udump directory and OEM notifications were being send again.
Only thing that isn't clear for me....Did the export/import of the sysman schema cause this problem ?
I think it did, but what could I have done to prevent it. Because I followed all the steps mentioned by Metalink.
Next time, check the queue status before exporting sysman.
select owner,name,queue_table,queue_type,enqueue_enabled,dequeue_enabled
from dba_queues;
Then queues can be controlled by

Thursday, June 19, 2008

OEM after import sysman schema....

After the export/import of the sysman schema, we recoverd almost 23G of disk space. But yesterday the /oem partition again was 100% full ????
Analyzing the different diectories showed an udump directory with 23G of trace files !!!
So, something is wrong with OEM. I also noticed no notifications were being send to me. The trace files showed me an ORA-00600 [kwqidrdq: loop], [0], [0], [0], [0], [], [], [] error. Could this have something to do with the export/import ??
I check the repository for invalid sysman objects. No invalid objects. I bounced the database and after bouncing the database some emails were send to me with OEM notifications, but still a lot off notifications were waiting to be send.
Using the emdiag tool...

[oemprd01@amssys07 bin]$ ./repvfy verify notifications -level 9
Please enter the SYSMAN password:

EMDIAG: 2008.0321 Repository: 19-Jun-2008 07:47:08
100. Notification backlog: 2
103. Stuck notification waiting to be delivered: 1090

I decided to tell Oracle about this and ask them if this could have anything to do with the wxport/import.
Still no answer, and my udump is filling up every day.

Tuesday, June 17, 2008

Resizing OEM database part II

So, today I actually resized the OEM database. I resized the database from almost 40gb to 17 gb. In my recent post I already mentioned the emd_maintenance.partition_maintenance I used to cleanup old records in the mgmt_metrics_raw table (mgmt_tablespace). Problem was, I could not resize the datafile because of an ORA-03297: file contains used data beyond requested RESIZE value error. Two options remained, one export of the sysman schema and drop the excisting repository and import again. Second option, export tablespace mgmt_tablespace and drop the tablespace, recreatre it and import.
Because Oracle did not test the second option, I decided to go for that one.
The tablespace export went well, I used the expdp with the TABLESPACES=MGMT_TABLESPACE option. After the export, I tried to drop the tablespace. Because of materialized views I did not succeed.
I decided not to try any futher and also made an sysman schema export. This time using the SCHEMAS option instead of the tablespaces...
After succesfully exported the sysman schema, I dropped the repository using the RepManger in $OMS_HOME/sysman/admin/emdrep/bin directory

After dropping the repository I had to bounce the database to free up the disk space on linux. Even when I removed the big datafile (27gb), linux still showed a 100% full.
After reclaiming the disk space on linux, I returned to the Oracle documentation again. Next step, Import the sysman export ? No need for a recreate of the repository ? Or maybe only the rep users, tablespaces, dba_roles ?? In the $OMS_HOME/sysman/admin/emdrep/sql/core/latest/admin I found some scripts, for example admin_pre_import.sql and admin_create_tablespaces.sql. I decided to run those sql's before the import. The documentation continues after the import with the post import steps, but the pre import is just as important. Because if your import is not succesfull, you don't even have to perform any post import steps :)

To summerize this post, I will show the steps I have taken...

1. export sysman schema (expdp SCHEMAS=SYSMAN...)
2. drop the repository using RepManager
3. Create repository tablespaces (mgmt_tablespace and mgmt_emc_depot_ts)
4. Create repository users (sysman, mgmt_user and mgmt_view (see admin_pre_import.sql))
5. import the sysman export (impdp)
6. compile any invalid objects (admin_recompile_invalid.sql)
7. run admin_create_synonyms.sql (same directory)
8. run admin_post_import.sql
9. exec DBMS_AQADM.START_QUEUE( queue_name=> 'MGMT_TASK_Q');
11. exec DBMS_AQADM.START_QUEUE( queue_name=> 'MGMT_PAF_REQUEST_Q');
12. exec DBMS_AQADM.START_QUEUE( queue_name=> 'MGMT_LOADER_Q');
13. Check sysman context using select * from dba_context where SCHEMA='SYSMAN';
14. exec emd_maintenance.analyze_emd_schema('SYSMAN');
15. run admin_submit_dbms_jobs.sql

If all these steps went oke, startup the OMS and you are back in business !

Friday, June 13, 2008

Resizing OEM database 10g

We noticed our Grid control ( was not cleaning up his metrics. Because of this we are running out of disk space on the oem volume. I noticed the mgmt_tablespace was 27 gb. The table mgmt_metrics_raw had more then 410 million records. I also found out that almost 4 million records were older than sysdate -9. Oem should automaticly clean up these records after zeven days, so something has gone wrong...
I decided to run the emd_maintenance.partition_maintenance (after applying patches mentioned in note 456101.1)
Now the mgmt_tablespace is only 3 gb, so I was able to clean up 24 gb. Next step is to resize the tablespace, so I can reuse the 24 gb. But resizing the datafile didn't work. Oracle showed me the : 'ORA-03297: file contains used data beyond requested RESIZE value' error.

I think there are two ways of reclaiming the free space in this tablespace. Completly export the sysman scheme, drop repository and import it again (tested by Oracle), or (and I would like to do this one) export the mgmt_tablespace using expdp TABLESPACE=mgmt_tablespace, drop/create the tablespace end import the data again. This option is faster and eassier, but not tested by Oracle yet.

Now I wonder if anaybody has already solved an issue like this ?

Friday, June 06, 2008

Pocket guides....

Sometimes when surfing the internet you find something interesting. I don't know exactly how, but I arrived at the following site with a lot of handy guides for the dba or EBS dba.
Check it out !

Wednesday, May 21, 2008

Argument list too long....

This morning all the Oracle databases showed a 'down' status in OEM.
Going through my emails, I noticed the that the targets (agents monitoring the databases) were facing some trouble uploading their data. I noticed the oem host was out of disk space...

Usually this problem is solved when the *.trc files under $ORACLE_HOME/admin/SID/udump or $ORACLE_HOME/admin/SID/bdump are deleted, so that's what I tried this time. But the the rm command didn't work anymore, and als the find command gave the same error.....Argument list too long
Using google I found the following unix commando that did work !!! I adjusted it a little bit, so the last seven days will remain..

find . -name '*.trc' -ctime +7 -exec rm "{}" \;

After starting the database again, the targets showed the 'up' status in no time...

Thursday, May 15, 2008


Yesterday I was testing some recovery scenarios with a customer. We decided to restore a backup of a database from a month ago, and recover it to yesterday.
Because the backup was one month old, we had to apply a lot off archived log files. First I just recovered using the suggested archives by entering. It took me more then 20 minutes to recover and open the database.

SQL> recover database until time '2008-05-14:12:00:00';
ORA-00279: change 1274237264 generated at 04/14/2008 23:40:14 needed for thread1
ORA-00289: suggestion :/opt/oracle/product/10.2/db_1/dbs/arch1_100_646855611.dbf
ORA-00280: change 1274237264 for thread 1 is in sequence #100
Specify log: {=suggested filename AUTO CANCEL}

I decided to test again but thios time with the 'set autorecovery on'. Now, the recovery does not wait for your enter, but automaticly applies the next archived log file.
This time, recovery and open database took me 8 minutes....

SQL> set autorecovery on
SQL> recover database until time '2008-05-14:12:00:00';
ORA-00279: change 1274237264 generated at 04/14/2008 23:40:14 needed for thread1
ORA-00289: suggestion :/opt/oracle/product/10.2/db_1/dbs/arch1_100_646855611.dbf
ORA-00280: change 1274237264 for thread 1 is in sequence #100

Wednesday, May 14, 2008


After writing a review for the Prepkit 10g OCP, uCertify posted some info about me on their own blog.

Also they told me that anybody reading my blog can get a discount when buying any prepkit from uCertify. The discount will be 10%, all you have to do is use the following code "BASK10".
So, check it out....

Monday, April 28, 2008

New Metalink ?

Surfing my favorite EBS blogs, I saw something nice on the Online Apps DBA blog.
It seems Oracle had developed a new Metalink. Looks a lot better then the old one, check it out for yourself !

Monday, April 21, 2008

CPU April 2008 patch

Today I started the Cpu patch April, 2008.
I started with the RDBMS patch, followed by the HTTP patch.
No problems so far, unfortunatly for me the EBS 11i patches I have to apply are not cummulative. I have to apply EBS 11i CPU patches from October 2005 till now...
When applying so many EBS patches, something must be going wrong :)
I will keep you posted.

Thursday, April 17, 2008

Create pfile from memory...

During the post 11g upgrade steps, I discovered a new feature in 11g.
In the 11g database, you can now create a pfile/spfile from memory !

I was used to create a pfile from the current spfile in 10g, and offcourse create a spfile from the adjusted pfile...but now it's also possible to creaet a pfile from memory. The syntax is almost the same..

[oracle@ebs2 log]$ sqlplus '/ as sysdba'
SQL*Plus: Release - Production on Wed Apr 16 17:06:02 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='/ebs/proddb/11.1.0/dbs/init_prod.ora' from memory;

File created.

Wednesday, April 16, 2008

Post 11g upgrade steps..

After the database upgrade, you will have to perform some post upgrade steps like compiling invalid objects etc...

These are the steps I have taken :

1. verify upgrade by running utlu111s.sql

[oracle@ebs2 admin]$ sqlplus /nolog
SQL*Plus: Release - Production on Mon Apr 14 20:30:58 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 1071333376 bytes

Fixed Size 1304704 bytes

Variable Size 411043712 bytes

Database Buffers 645922816 bytes

Redo Buffers 13062144 bytes

Database mounted.

Database opened.

SQL> @utlu111s.sql

Oracle Database 11.1 Post-Upgrade Status Tool 04-14-2008 20:37:33.

Component Status Version HH:MM:SS.

Oracle Server. VALID 01:29:42

JServer JAVA Virtual Machine. VALID 00:00:00

Oracle Real Application Clusters. INVALID 00:00:04

OLAP Analytic Workspace. VALID 00:00:00

OLAP Catalog. VALID 00:00:00

Oracle OLAP API. VALID 00:00:00

Oracle XDK. VALID 00:00:00

Oracle Text. VALID 00:00:00

Oracle XML Database. VALID 00:00:00

Oracle Database Java Packages. VALID 00:00:00

Oracle Multimedia. VALID 00:00:00

Spatial. VALID 00:00:00

Gathering Statistics. 00:52:16

2. Run catuppst.sql

SQL> @catuppst.sql

3. compile invalid objects

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);



SQL> @utlrp.sql

During compiling the invalid objects, I also copied the tns_admin directory and the appsutil directory to the new 11g $ORACLE_HOME

Nex step is to get EBS started.

Tuesday, April 15, 2008

Database upgrade...

Last weekend I started the database upgrade from 10gr2 to 11gr1. As I mentioned before, I decided to manually upgrade the database. Before upgrading I already recieved an error.

[oracle@ebs2 admin]$ sqlplus /nolog
SQL*Plus: Release - Production on Thu Apr 10 08:19:14 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn / as sysdba

Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1071333376 bytes

Fixed Size 1304704 bytes
Variable Size 411043712 bytes
Database Buffers 645922816 bytes
Redo Buffers 13062144 bytes
Database mounted.
Database opened.
SQL> set echo on
SQL> spool upgrade.log
SQL> @catupgrd.sql
Upgrade error :

2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);

*ERROR at line 1:
ORA-01722: invalid number

Have to upgrade the timezone to 4.

Current timezone :
SQL> select * from v$timezone_file;

------------ ----------
timezlrg.dat 2

To upgrade my timezone to version 4, I applied the following patches :
1. 5601428
2. 5746835

After upgrading the timezone I started teh upgrade again, and this time the catupgrd.sql went fine...
Today I will finish the database upgrade following metalink note 429825.1, Complete checklist for Manual Upgrade to 11gR1.

Wednesday, April 09, 2008

Installing 11g database software

Next step is to install the 11g database software. Be sure to install the 11g software in a new ORACLE_HOME. Also, only install the software ! Do not create a new database and do not choose to upgrade the database during the install.

After installing the software, create nls/data/9idata directory..

[oracle@ebs2 old]$ perl cr9idata.plCreating directory /ebs/proddb/11.1.0/nls/data/9idata ...Copying files to /ebs/proddb/11.1.0/nls/data/9idata...Copy finished.Please reset environment variable ORA_NLS10 to /ebs/proddb/11.1.0/nls/data/9idata!

Now apply some additional RDBMS patches. Apply the following patches using Opatch :




Before running the first patch, be sure to apply patch 4898608, OPatch Without this patch the opatch napply -skip_subset -skip_duplicate will fail...

After downgrading the excisting opatch utility, you can start the patches..

[oracle@ebs2 6778860]$ opatch napply -skip_subset -skip_duplicateInvoking OPatch
Oracle Interim Patch Installer version (c) 2007, Oracle Corporation. All rights reserved.
Setting N-Apply implicit patch base-directory to /ebs/patches/11g/6778860UTIL session
Oracle Home : /ebs/proddb/11.1.0Central Inventory : /etc/oraInventory from : /etc/oraInst.locOPatch version : version : location : /ebs/proddb/11.1.0/ouiLog file location : /ebs/proddb/11.1.0/cfgtoollogs/opatch/opatch2008-04-09_13-59-41PM.log
Invoking utility "napply"Checking conflict among patches...Checking if Oracle Home has components required by patches...Checking skip_duplicateChecking skip_subsetChecking conflicts against Oracle Home...OPatch continues with these patches: 6318357 6353873 6412375 6417271 6431470 6454237 6530141 6617137 6633314 6633347 6735167 6744214
Do you want to proceed? [yn]

When the 11g ORACLE_HOME is patched, the database (10gr2) must be upgraded to 11g. I choose the manual upgrade instead of using DBUA....

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 :



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.....

Tuesday, April 08, 2008

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...

Tuesday, April 01, 2008

Notifications not send with 10g Grid Control

We are working with 10g Grid Control ( 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: 31-Mar-2008 14:24:44
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..)


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: 01-Apr-2008 10:00:07

And nothing is left behind this time....

Friday, March 28, 2008

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.

Wednesday, March 26, 2008

Friday, March 14, 2008

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

Wednesday, March 12, 2008

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 !!

Thursday, March 06, 2008


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 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

Tuesday, March 04, 2008

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 ( 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 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 from the installed jre...
ln -s /usr/java/jre1.5.0_12/plugin/i386/ns7/ ./

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.


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

Friday, February 29, 2008

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....

Thursday, February 21, 2008

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

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...

Friday, February 15, 2008

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 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 (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 (c) 1996, 2007 Oracle Corporation. All rights reserved.
Agent Version :
OMS Version :
Protocol Version :
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

Tuesday, February 05, 2008

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 !

Friday, February 01, 2008

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 Grid install.
Next step was the upgrade of the oms and agent. I also decided to upgrade the database itself, which is default (even after the 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 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 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 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 ?

Thursday, January 24, 2008

plsql developer

When trying to compile a package using plsql developer in a 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...

Tuesday, January 22, 2008

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 :


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


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..


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

Friday, January 18, 2008

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 !!

Friday, January 11, 2008


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.

Thursday, January 10, 2008

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 - 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;


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;

------- ------------------ -------------------- ----------
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..

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}
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;


The database is open and ready to use.