Oracle DBA by Teshome Asfaw

Dropping a table during SELECT in oracle and SQL server

2010-05-07T06:42:00.000-07:00

Uwe Hesse in his article Dropping a table during SELECT has described how dropping a table during select statement works in oracle database server. As per his post, you are able to drop while selecting from the table. In contrary to this in SQL server dropping a table while a select is running doesn't work. The drop statement is blocked by the select statement. Once the select statement is completed, the drop statement also completes.

Mission Critical: Database Design

2010-03-22T03:40:00.000-07:00

A very good documentation on designing and implementing mission critical database. I found it quiet interesting though now far from what I normally do when designing a mission critical database. You can find the full document @ simple-talk.com

SOA governance

2010-03-17T10:40:00.000-07:00

Adaption of SOA is more likely to succeed if you embark a good but simple SOA governance. The following white paper explains the approches and best practices towards acheiving good SOA governance. Read
SOA governcance

List of oracle Table Names and Rows

2009-12-16T02:58:00.000-08:00

I was looking for ways of displaying table names and number of rows it hold. It took me a lot of time to figure out how this can be done. After nearly completing my script, I found a better script than the one I created and want to share it with you.

You can find the full script @List of oracle Table Names and Rows

Export oracle METADATA_ONLY

2009-12-01T08:12:00.000-08:00

steps
1. create dump directory (CREATE DIRECTORY metadata_export AS '/opt/app/oracle';
2. make sure the directory has got read and write permssion
3. create shell script as follows
4. schedule the script to run

#!/bin/sh
. envfile.env
$ORACLE_HOME/bin/expdp \"/ as sysdba\" SCHEMAS=shcema1, schema2, schema3 content='METADATA_ONLY' directory=met
adata_export NOLOGFILE=Y dumpfile=metadata_export:metadata.dmp compression=metadata_only

Invoking oracle ESB API through http protocol

2009-11-24T07:33:00.000-08:00

If you want to see the service status of esb in one xml file you can invoke oracle esb API through http protocol. For instance you can use http://hostname:port/esb/esbConfiguration/executeCommand?action=ExploreServices to list all your esb services and statuses in one xml document

This is documented very well @ One of soa blog

Optimizing Oracle on VMware

2009-11-16T08:10:00.000-08:00

I have been looking into the issues of running RDMS on vmware and found the following articles to be useful.

optmising oracle on vmware

Deployment of Oracle Databases on VMware Infrastructure

virtual machines versus bare metal

RAC on virtual machines

nice -n 19 rm -rf directory

2009-10-19T02:54:00.000-07:00

nice -n 19 rm -rf directory

this command will allow you to run a rm if your directory has got many files. It will run with lowest priority.

Integrating Oracle BIEE and Oracle Analytic Workspaces

2009-10-02T08:09:00.000-07:00

Very nice paper on how integrate oracle OBIEE and Oracle Analytic WorkspacesIntegrating Oracle BIEE and Oracle Analytic Workspaces

Oracle BPEL Process Manager Performance Tuning

2009-09-30T08:06:00.000-07:00

A good starting point on how to tune Oracle BPEL Process Manager Performance Tuning
Oracle BPEL Process Manager Performance Tuning

crs commands

2009-09-22T08:43:00.000-07:00

Oracle RAC Administration - Part 2: CRS commands. Nice article on RAC admin.
crs commands

Creating database link

2009-09-15T08:31:00.001-07:00

I always tend to get this one wrong. It is simple. The following can be used to create a database link

CREATE DATABASE LINK name_of_link CONNECT TO username IDENTIFIED BY userpassowrd USING 'database_sid'

Gettinng started with Oracle SOA 11 G

2009-08-29T21:28:00.000-07:00

Getting started with oracle SOA 11G. Five things you need to get started with Oracle SOA Suite 11G

1. Oracle WebLogic server 11G R1
2. Oracle SOA suite 11g R1
3. Oracle Repository Creation Utility 11g R1
4. Oracle JDeveloper 11g R1
5. Documentation (oracle documentation)
and probably when Getting Started With Oracle SOA Suite 11g R1 – A Hands-On Tutorial books come out it may be helpful. Not sure what the content will be but it seems that it is offering a hands-on tutorial which may or may not be good

Oracle SOA 11G

2009-08-29T21:14:00.000-07:00

I came accross this article on the launch of Oracle SOA suite 11G. This article is written by Alex Neihaus who probably happens to work for ActiveVOS who is a primary compititor of oracle SOA suite. I am a bit surprised for somebody who has not worked with the product to come up with this. For full details of this person verdict on Oracle SOA you can find it @On the software runway, Oracle 11g can’t quite pull it off

What sid is blocking what

2009-08-14T06:02:00.000-07:00

To find out what sid is blocking which sid you can use this query.

select b1.sid, ' IS BLOCKING ', b2.sid
from v$lock b1, v$lock b2
where b1.block =1 and b2.request > 0
and b1.id1=b2.id1
and b1.id2=b2.id2

Schedule Email Notification using Oracle BPEL

2009-08-05T09:46:00.000-07:00

Very good step by step of how to schedule email notification using oracle BPEL can be found Schedule Email Notification using Oracle BPEL

ESB CONTROL TAKES LONG

2009-07-24T09:15:00.000-07:00

Recently I have experienced that esb control taking to long to display services. Oracle has confirmed this as a bug when you have got a lot of BPEL process and you can get around this by adding a LazyLoad parameter to esb_config.ini file.

This has been reported by oracle as a bug 7720420

Adding the esb.console.services.lazyLoad.Allowed=true property to $OARCLE_HOME/integration/esb/config/esb_config.ini and restarting the services fixed the issue.

Oracle fusion middleware components and architecture

2009-07-10T08:49:00.000-07:00

Oracle fusion middleware architecture overview

Oracle fusion middleware components

Singleton adapters issues in HA

2009-07-10T02:04:00.000-07:00
Recently I have faced with the issue of Singleton (non-concurrent) adapters in HA (clustered environment). Two instances were created for a single file which means that the file will be processed twice.

Singleton adapters such as ftp and file can not run in active-active configuration. For this type of adapters you must make sure that there is only one adapter instance active at runtime independent of the number of clusters you have. This effectively means you will only have one non-concurrent adapter at runtime. If this active adapter fails, one of the passive adapter becomes active.

There are a lot resources in metalink that you will be able to use. I have listed some of them below.
How to Enable a Singleton Adapter in an ESB Cluster Environment
How to Enable a Singleton Adapter in BPEL Cluster Environment
How to Setup FileAdapter in Cluster for High Availability
and more can be found on metalink.

Oracle Fusion Middleware 11gR1 Software Downloads

2009-07-08T07:33:00.000-07:00
All Oracle Fusion Middleware 11gR1 Software Downloads can be found at Oracle Fusion Middleware 11gR1

Delete single or multiple esb instance

2009-07-07T09:25:00.000-07:00
From time to time you may need to delete stale esb instances. I had difficulty in finding a script that does it. But actually you can use oracle provided purge scripts and modify to be used only for single or multiple instance deletion. See scripts below it will delete single esb instance

DELETE FROM ESB_ACTIVITY
WHERE ID IN
(SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));

DELETE ESB_TRACKING_FIELD_VALUE
WHERE ACTIVITY_ID IN
(SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));

--

DELETE FROM ESB_FAULTED_INSTANCE
WHERE ACTIVITY_ID IN
(SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));

--

DELETE FROM ESB_TRANSACTION_STATUS
where Flow_id = 'instance Id'

--

DELETE FROM ESB_INSTANCE_RELATION_XML
WHERE FLOW_ID IN
(SELECT FLOW_ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));

--

DELETE FROM ESB_RELATION_XML A
WHERE NOT EXISTS
(SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML);

--

DELETE ESB_SERVICE_RELATION
WHERE RELATION_XML_ID IN
(SELECT ID
FROM ESB_RELATION_XML
WHERE NOT EXISTS
(SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML
WHERE ID = RELATION_XML_ID));
commit;

Compare Weblogic and Oracle Application Server

2009-07-07T07:53:00.000-07:00
Few not long list but will help in understanding SOA on application server and weblogic. To find more click this blog Compare Weblogic and Oracle Application Server

FEATURE MATRIX

2009-07-07T02:15:00.000-07:00
A useful comparison of feature matrix of oracle weblogic server standard edition 11g, oracle weblogic server enterprise edition 11g and oracle weblogic suite 11g can be found at FEATURE MATRIX.
On one of the line Oracle Application Server: for customers running existing environments on this application server, complete access and use rights to oracle weblogic suite 11g. Does this mean it is possible to upgrade to weblogic 11g from apps server 11 g ? again wait and see.

Understand hidden parms for corruption recovery

2009-07-06T06:15:00.000-07:00
Burleson explains that many Oracle DBA staff are not aware of special hidden parameters that allow you to open a corrupt database

These parameters allow you to ignore corrupt data blocks when your database is corrupted. These should only be used in emergencies. The following are direct copy from his post.

_allow_resetlogs_corruption - This parameter may be the only way to start a db backed-up open without setting backup on tablespaces, it will result in an unsupported system.

_corrupted_rollback_segments - The only way to start up with corrupted public rollback segments. This undocumented parameter can be used without fear of invalidating support.

_allow_read_only_corruption - This parameter allows the database to be opened even if it has corruption. This should only be used to export as much data from a corrupted database as is possible before re-creating a database. A database that has been opened in this manner should not be used in a normal manner, as it will not be supported.

_corrupt_blocks_on_stuck_recovery – This parameter can sometimes be useful for getting a corrupted database started. However, it probably won't be supported if done without Oracle's blessing. Immediately export the tables needed and rebuild the database if used.

Oracle Fusion Middleware 11g conference

2009-07-03T15:56:00.000-07:00
The launch of oracle fusion middleware 11G was held on july 2nd 2009 in Hilton London Paddington.

Keynote :
Charles Philips, President, Oralce
Thomas Kurian, Senior vice president, Oracle Development.
The main components discussed were
- Oracle SOA suite 11G
- Oracle WebCenter suite 11G
- Oracle WebLogic Suite 11G
- Oracle Identity management
- Oracle Fusion middleware for various sectors.

The conference was exciting and there were no much marketing. My expectation was that as it always been the morning session normally have a lot of marketing than substance but in this occasion it was all about the product.

Some of eye catching stuff that I have come accross are:
Weblogic server --> this has brought a lot of question to my mind. Are those who are on Application server 10 will be able to upgrade with no extra cost.It was vague and will still remain like that until the time come when oracle decides. It seems that oracle have put a lot of money and time on getting this product right. You wouldn't know what the issues will be until you actually use it but from what I have seen it is a wonderful product.

Oracle JRocket real time :

Oracle claims that JRockit Real Time is the industry’s fastest real-time solution for standard Java and the only one with average microsecond response performance. It guarantees a five nines maximum garbage collection latency on the order of one millisecond. JRockit uses deterministic garbage collections . Oracle datasheet also claims that JRocket can help you in predicting latency and extreme performance, zero coding when you swap JVM and advanced monitoring tools.

Oracle WebLogic Real Time is also the version of Oracle JRockit Real Time offered exclusively with Oracle WebLogic Suite. JRockit seems to run on unix like (Liunx) platforms and windows (both 32 and 64 bits)

New Jdeveloper :

Looks very promising. It reminds me of when Microsoft launched .Net framework. Oracle have revamped this products with many added features. To just mention few ability to use any source control, collaboration with your colleagues , assigning tasks, drag and drop capability is highly improved. There are more to it than mentioned her.

SOA suite 11 : it will run on weblogic server 11 and it looks like it is going to stay that way.

Application grid is also mentioned which again could improve application performance.

Last but not least the lunch was fantastic. For those who stayed behind there was also drinks (cool !!!!!!!!!)

ESB, BPEL using Oracle SOA Suite : articles

2009-06-26T01:09:00.000-07:00
ESB, BPEL using Oracle SOA Suite : articles is published by Apps fusion and it has 4 series. The articles are mainly based on oracle soa suite 10.1.3.3. I found it very useful both for beginner and advanced users.
Getting Comfortable with Oracle SOA Suite : part I
Database Adapters : part II
File And FTP Adapters
BPEL Process Activities

v$views

2009-06-25T06:03:00.001-07:00
A good collection of v$views and brief description of what they do can be found at René Nyffenegger's collection of things @Oracle's V$ Views.

Is port in use

2009-06-23T10:03:00.000-07:00
Linux command to check if the port is in use
netstat -nape | grep

Concurrent program and time

2009-06-23T05:18:00.000-07:00
I use the following sql statement to check concurrent program and time that it took to run. It will give you an overview of how long a program takes and results can be exported to excel for further analysis.

select
u.user_name username,
user_concurrent_queue_name QName,
cp2.user_concurrent_program_name programName,
--cp.concurrent_program_name short,
to_char(cr.actual_start_date,'DD-Mon-YYYY HH24:MI:SS') started,
to_char(cr.actual_completion_date,'DD-Mon-YYYY HH24:MI:SS') Completed,
round((nvl(cr.actual_completion_date,sysdate)-cr.actual_start_date)*1440,1)
ttl_rtime,
request_id ,
decode(status_code,'E','Err','T','Term','G','Warn','C','Normal','R','Normal','W','Pending',status_code)
scode
from fnd_application a,
fnd_concurrent_programs_tl cp2,
fnd_concurrent_programs cp,
fnd_user u,
fnd_concurrent_requests cr,
fnd_concurrent_queues_tl cq,
fnd_concurrent_processes cpr
where a.application_id = cp.application_id
and cp.concurrent_program_id = cr.concurrent_program_id
and cp2.concurrent_program_id = cr.concurrent_program_id
and u.user_id = cr.requested_by
and cq.concurrent_queue_id = cpr.concurrent_queue_id
and cr.controlling_manager = cpr.concurrent_process_id
--and status_code in ('E','G')
--and cp.concurrent_program_name <> 'FNDRSSUB'
--and cr.actual_completion_date>sysdate - 1
--AND TO_NUMBER(TO_CHAR(actual_start_date, 'HH24')) between 4 and 6
--AND to_char(actual_start_date, 'DD-MON-YY') = '23-JUN-09'
AND round((nvl(cr.actual_completion_date,sysdate)-cr.actual_start_date)*1440,1) > 10
order by cr.actual_start_date DESC ;

Global replace command

2009-06-17T08:04:00.000-07:00
From time to time I replace some oc4j-ra.xml files during soa cloning and changes. I found :%s/nametofind/nametoreplace/g command very useful.

Date and time difference in oracle

2009-06-12T03:42:00.000-07:00
This is the topic I never mastered but in searching for date and time difference function I have across this which I wanted to share with you.

How does one get the time difference between two date columns?

It is much easier to do the same thing in SQL Server as compared to oracle though.

Oracle Configuration Best Practices

2009-03-11T08:17:00.000-07:00
A good reference for Oracle Configuration Best Practices. It covers
Configuration Best Practices for the Database, Configuration Best Practices for Real Application Clusters, Configuration Best Practices for Data Guard, Configuration Best Practices for MAA , Recommendations for Backup and Recovery and Recommendations for Fast Application Failover. If you want to read click more

SOA magazine

2009-03-09T15:12:00.000-07:00
More on SOA. Some good technical articles. Nice to bookmark this site SOA magazine

SOA Best Practices : BPEL cookbook

2009-03-09T14:54:00.000-07:00
I find this a very good place to start looking at oracle soa best practices. SOA best practices : BPEL cookbook. It will discuss all aspects of soa starting from Service-Oriented Integration, Building Modern Applications and SOA Techniques.The area that interested me more is Managing a BPEL Production Environment
Learn how to automate common admin tasks in a BPEL production environment using BPEL Process Manager's API and Dehydration Store by Stany Blanvalet, BPEL and J2EE consultant

BPEL process on wait activity

2009-03-09T14:37:00.000-07:00
From time to time BPEL process on wait activity is more than what you expected it to be. It wasn't easy to get an answer for this. The wait activity sometimes is beyond your belief. There isn't good recommendation or what have caused this to happen anywhere. But, I have seen a post by expert consulting block BPEL process wait activity which is worth trying out.

Query to monitor BPEL processes.

2009-02-27T04:33:00.000-08:00
The following query can be used to monitor BPEL processes.

select count(1), RTRIM(process_id),
case state when 0 then 'intiated'
when 1 then 'open and running'
when 2 then 'open and suspended'
when 3 then 'open and faulted'
when 4 then 'closed and pending'
when 5 then 'closed and completed'
when 6 then 'closed and faulted'
when 7 then 'closed and canceled'
when 8 then 'closed and aborted'
when 9 then 'closed and stale'

else 'unknown' end as status
from ORABPEL.CUBE_INSTANCE
where trunc(creation_date) >= :B1 and state <> 5
group by process_id, state

CUBE_INSTANCE state column

2009-02-27T03:52:00.000-08:00
Recently, I have started using soa and want to get stat on all processes with different state. I have started looking at ways of getting this data from dehydration store but there seems to be no much info on the columns of CUBE_INSTANCE. I was interested in the process name and state.

I came across the following values for state and want to share with you.
0 --> initiated
1 --> open and running
2 --> open and suspended
3 --> open and faulted
4 --> closed and pending
5 --> closed and completed
6 --> closed and faulted
7 --> closed and canceled
8 --> closed and aborted
9 --> closed and stale

Instances created in ESB Console

2009-02-27T03:05:00.000-08:00
Instances created in ESB Console not showing up.

I have seen a situation where an instances created is not showing in esb console. At the first instance I thought something was wrong with esb. After further investigation, nothing broke as all the BPEL processes which is invoked by esb are all completed with out error.

By default the open_cursor parameter is set to 300. If you have got many processes, this can be exceeded and that was the problem and was able to fix by increasing this database parameter.

Long running Operations in Oracle

2009-02-18T04:43:00.000-08:00
From time to time you want to monitor long running queries in oracle. I have recently given a task of purging dehydration database and wanted to find out how far much of the work has been done. I came across dynamic performance view v$session_longops. For me a simple query something like :javascript:void(0)

select round( SOFAR/TOTALWORK * 100,2) ||'% completed' from v$session_longops where sid = 487

produced the result I needed. But there is much more you can do with this view. TIME_REMAINING is very interesting column as it will give you an estimated time remaining for the query to be completed.

Oracle SOA Suite High Availability

2009-02-04T09:58:00.000-08:00
If you are using Oracle SOA for business critial integaration, oracle have posted various resources on Oracle SOA suite high availability.

To view resources click Oracle SOA high availability

concurrent manager

2008-12-12T07:31:00.000-08:00
What are the status_code in table FND_CONCURRENT_REQUESTS ?

STATUS : X =Terminated ,
I = Inactive(on Hold ),
E = Completed ( Error )
G = Completed ( Warning ),
D = Cancelled,
C = Completed,
R = Running
T = Pending

Start of the beginning

2008-11-09T16:48:00.000-08:00
I tried to force myself into the world of oracle and promised to give you my experience and the difficulties of moving from SQL Server world to Oracle. Now, I have been working with oracle technology stack for the last 2 months and I have got something to tell. In the coming new months , I will try to start from the basics and will probably go though what it is like in Oracle world as compared to SQL Server. Which technology is best on what area ? How both RDMS fulfil various ANSI standards.

Oracle DBA versus SQL DBA

2007-11-23T15:38:00.002-08:00
Most database professionals tend to focus on one relational database management system. My believe was that if you know the theory behind relational theory you would think it would be easier to learn other systems overnight. This theory may be true if you are a database designer/developer but if you are a core DBA there is a lot of things that you need to learn. I am in early stages (two days to be precise) start looking into oracle database. It seems to me a lot of up hill to begin with. I have worked with SQL server for the last 8 years.

In the months to come I will try to write some pros and cons of moving to Oracle DB after spending 8 good years working with SQL server.

How difficult it is

2007-09-09T15:41:00.000-07:00
Changing from SQL server DBA to be Oracle DBA. This blog is all about what issues you face if you were a proficient SQL Server DBA but want to be an Oracle DBA. I will try to include all the good things and the difficult once while going through the process. I hope it will give an insight into what you would expect.