listing the active oracle tasks/processes

[Bram Vermeulen]

greatings,

every day we try to bring down our database to do a filesystem backup, however the last couple of days the shutdown script won't finish because we suspect there's something occupying the oracle database at that time
I've tried reschedueling the shutdown to a few hours later but still no luck..
if I reboot the database server and then do a backup that night it works, but the next night again no shutdown
so my question is:
is there a way or command or query to list the active processes occupying the database through a schedueled script or something?


thanks in advance,
bram

[Shrikant Lavhate]

Hi Bram,

Simple way is to grep through all active processes to know active database processes. you can achecivbe this by using

#ps -ef|grep -i oracle

You can have a script which get the PID from above command and kill those processes to run backup smoothly.

Using crontab entry you can schedule this shell script execution just before you start backup.

[Steven E. Protter]

Shalom,

ps -fu oracle

Also, the default dbshut script does a database shutdown, not a shutdown immediate.

The default will not permit the database to shut down if there are active connections. If you modify dbshut and use shutdown immediate the database will shut down.

If dbshut is already fixed check the alert logs.

SEP

[Bram Vermeulen]

hello, thanks for the answers

I probably should've explained the system set up a bit further.
The oracle (oracle9i) server itself is running on a win2k3 server, but the application server is HP-UX.
So we're 90% sure that it's a process on the unix server that's holding up the database. The two commandos I tried didn't give any results so I'm guessing it was supposed to be executed on a HP-UX server functioning as database server?
The shutdown (connecting through sqlplus and issuing a shutdown) is executed as a pre-backup script from data protector.

Sorry for not providing enough information.

[Bram Vermeulen]

Also it's not the point to automatically shut down these processes because some processes shouldn't be killed, this would just mean finding out why these processes aren't being finished rather than killing them.

[Bram Vermeulen]

Perhaps the script or query or command shouldn't be executed on the HP-UX but on the Win2k3 server (which would mean this thread is in the wrong place I guess) because there's too many programs with different names using the database on HP-UX?
I was kind of hoping that there'd be a way to see it through which connections are made to the IP of the database server or something.
However if anybody knows a way to see it from the win2k3 server through a command ran there it'd be great aswell.

[Hein van den Heuvel]

There is a lot in here.

While shutting down a DB to make a 'cold' backup is certainly an option, it is not a particulary friedly one. There are better (oracle) tools. Check out Rman!

You may also want to shutdown the oracle more harshly: 'shutdown immediate'

Certainly there is little point asking your (client) box about oracle processes as originally thought. You need to know about the server, and there Oracle is mutlythreaded, so just one process does it all and thus a simple process scan can not check for activity.

However, oracel has all the tools to just ask it who is talking to it.
Check out tables v$session and v$process.

For a sample query, to be executed from any (client) system that can talk to the server below.

Groetjes,
Hein van den Heuvel

column id format 999;
column Local format 99999999;
column Remote format 99999999;

column Node format a9;
column Local_Program format a20;
column Remote_Program format a24;
set pages 9999;
set lines 132;

select pid "Id", spid "Local", process "Remote",
s.machine "Node", SUBSTR(p.program,1,20) "Local Program",
SUBSTR(s.program,1,24) "Remote Program"
from v$process p, v$session s
where addr = paddr
order by machine, spid
/

[Bram Vermeulen]

Hein, thanks alot! That's exactly what I wanted.
However there's still something not quite clear, this is my output when I run the script during the day:


Id Local Remote Node Local Program Remote Program
---- ------------ ------------ --------- -------------------- ------------------
2 1804 1804 BORA01 ORACLE.EXE ORACLE.EXE
4 1884 1884 BORA01 ORACLE.EXE ORACLE.EXE
7 2516 2516 BORA01 ORACLE.EXE ORACLE.EXE
3 5020 5020 BORA01 ORACLE.EXE ORACLE.EXE
6 5388 5388 BORA01 ORACLE.EXE ORACLE.EXE
8 6004 6004 BORA01 ORACLE.EXE ORACLE.EXE
5 6028 6028 BORA01 ORACLE.EXE ORACLE.EXE
9 980 980 BORA01 ORACLE.EXE ORACLE.EXE
12 1016 944:1932 WVB\BORA0 ORACLE.EXE sqlplus.exe
1


What are those Local and Remote process numbers? Where can I find out what the numbers mean?

Either way thanks alot! You've already been extremely helpful.

[Eric Antunes]

Hi,

v$session.process stands for client's pid, v$process.spid stands for server's pid and v$process.pid stands for oracle's pid.

Best Regards,

Eric Antunes

[Hein van den Heuvel · Accepted solution]

>> What are those Local and Remote process numbers? Where can I find out what the numbers mean?


As Eric indicates, they are process-id, much like on hpux. However, for a windows server they are actually Thread ID.
You can 'see' them with tools like pviewer from the windows resource kit, or my current favourite: The sysinternals process explorer (google!).

You will also find reference to then in the alert log and .trc files in the bdump (background dump) directory on the server.

For example, below you'll find a logwriter process trace for a freshly restarted Oracle XE instance on my workstation. The simple 'taskmanger' give PID 200, but I can add column 'threads' which indicates 23 for process 200. The log writer was thread 1364
(you may want to make those colums in the script wider, they server a particular purpose for a downstream consumer for me)

hth,
Hein.

Dump file c:\oraclexe\app\oracle\admin\xe\bdump\xe_lgwr_1364.trc
Thu Jan 24 07:14:17 2008
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1343M/2046M, Ph+PgF:2718M/3429M, VA:1682M/2047M
Instance name: xe

Redo thread mounted by this instance: 1

Oracle process number: 6

Windows thread id: 1364, image: ORACLE.EXE (LGWR)

[Bram Vermeulen]

Thanks all for the help, thanks to the script we saw there was a schedueled task of another (old) server occupying the database. This server was disconnected from the network but needed to be reconnected a few days ago.