hi all,
our Baan IV ERP is running on a hardware cluster environment.Our Oracle 9i db on a ibm p630 server, and Baan IV on the other p630 server.

we want to find out which Baan IV sessions is using the rollback segment(s) on Oracle 9i.We can find which users is using the rollback segment but couldnt find their Baan session names.

Thanks in advance.

You won't be able to see Baan session names on the Oracle side because the session is a program generating any number of SQL statements that get passed to Oracle through the Baan/Oracle driver. The only thing Oracle sees are the SQL statements associated with a user. The session controls the frequency of commits, so if you don't have the source code you won't know what that is. You can create a trace of the execution of SQL statements on both the Baan and Oracle side which would give you a better picture of what the session is doing.

Are you wanting to size rollbacks better?

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Now that you have the userid's you can at least see what processes are being used by those users at the unix level.

ps -ef |grep bshell |grep <userid> will show you their bshell.

bshcmd6.1 -p -s -w 10 <bshell_pid>

will show all the session that they are running.

You can probably surmise from this what session is taking up the rollback segment...

If you have multiple bshell's per user, you'll have to track down the PID of the oracle connection (via v$session) and then trace that back to the driver which will trace it back to the bshell, if you really need info on how to do that let us know.

Dave

yes,we are getting ora-1555 errors and we think our rollback segment sizes isnt enough for those long running sessions which is making lots of undo data.our aim is capturing the Baan IV sessions which is let the ora-1555 errors.

Regards.

You can also query view v$session_longops to view long running sessions - from this view you'll be able find the osuser and unix process id - hence subsequently the Baan user/session.

Doesn't log.oracleX tell you the session?

To combat 1555, i'd suggest you do a search on Baan's support site, there is a general solution on it that will help you understand it..

Also, what does your rollback segment setup look like? The first thing Baan support is going to look at is this..

I have mine set at 10 meg initial 10 meg next optimal 20 meg. I find that is a good setup for rollback's, if i get a 1555, Baan will usually provide a fix via the session.

Dave

One of the big advantages of Oracle 9 is that you don't need the use of rollback segments anymore but can use undo tablespaces.

Please RTFM to find the advantages of it. Hereby a couple:
- you don't need to bother about minimal, optimal, shrink and number of rollback segments.
- if you can query data of the past (undo_management and undo_retention)
- No error 1555 anymore (ok, if the tablespace is full, you get another eror)

Hope this helps,
Dick

dave_23,
i cant see the session name but the follwing log in the alert.log file of oracle.if i set anything on oracle side, can i see the sessions which causing ora-1555?how?

We use Dictionary space management (not automatic undotbs) and have 30 rollback segment with 10 meg initial 10 meg next optimal 20 meg as yours.we assigned 6GB rollback segment tablespace(RBS) the them.

Regards.

Hi beksalur,

Dick's right, with 9i you really should use undo tablespace. You can keep everything else dictionary managed if you'd like, but the undo tablespace with oracle 9i is specifically ment to address this!!

But as for your sessions, no the oracle alert log won't help in this case.

but I'm confused.. aren't you're users calling you complaining that they're getting this error? How about the log.bshell it doesn't show the username / session?

Dave

we can see the user but not the session which causes the ora-1555 err as you can see from the following log.bshell log.

2003-10-17[13:33:54]:E:bim5: ******* S T A R T of Error message *******
2003-10-17[13:33:54]:E:bim5: Log message called from /view/port.6.1c.07/vobs/tt/mir/mir/bdb_fun.c: #1031 keyword: DB error
2003-10-17[13:33:54]:E:bim5: Pid 2875444 Uid 262 Euid 262 Gid 125 Egid 125
2003-10-17[13:33:54]:E:bim5: user_type S language T user_name bim5 tty ote locale ISO88591/NULL
2003-10-17[13:33:54]:E:bim5: Errno 0 bdb_errno 2555 (bdb_errno 2555)
2003-10-17[13:33:54]:E:bim5: Log_mesg: Error 2555 (bdb_errno 2555) on SELECT
2003-10-17[13:33:54]:E:bim5: ******* E N D of Error message *******

If the enduser is not complaining, you probably don't have a problem ;) but probably the user will call you and can immediately tell you what session he was running. If it is a batch session, take a look at the job history. It will exactly tell you what session caused the problem.

Hope this helps,
Dick