Questions about Oracle related processes as shown by the ps command

[Frank Fitch_1]

Hi All,

I am not knowledgeable about Oracle whatsoever.

I have an HP-UX 11.11 system running Oracle with a database having a SID named "ora".

When I run a ps command on the system, it shows a 122 "oracleora" processes. I assume the the "oracleora" name is a concatenation of the text string "oracle" with the SID name, "ora" appended to it.

Of the 122 oracleora processes, 28 can be categorized as (LOCAL=YES) and have a parent PID of an in-house developed application that utilizes Oracle.

The remaining 94 oracleora processes show (LOCAL=NO) and all have PPIDS of 1, which is the init process.

I have attached a text file containing the actual "ps" output from the system.

My questions are:

1. What does LOCAL=(YES|NO) mean?
2. Why do the LOCAL=NO processes have a PPID of 1 (init)?
3. Is an "oracleora" process with a parent of init normal behavior or is this an indication of these processes are orphans?

Thanks in advance......
Frank

[Jeff_Traigle]

1. LOCAL=YES means the process was spawned on the local machine. LOCAL=NO means it was spawned by a client using Net8 (or whatever Oracle is calling their SQL*Net software these days and what version of Oracle RDBMS you're using).

2 and 3. The PPID of 1 is normal. Oracle probably exec()'s the process.

[Steven E. Protter]

Shalom Frank,

How Oracle starts depends on how its configured. This has to be donemanually in older oracle releases.

There should be a file called /sbin/init.d/oracle

In it is the start stop instructions or the oracle server software.

The ps output is a little hard to look at, perhaps next time filter it.

ps ... | grep oracle

The PPID of 1 does imply that the init process spawned the actual process. Since the proceses for the database are owned by oracle I feel the startup is not problematic.

I noticed you have a local httpd server running which installs with the OS, along with the administrative httpd(web) server that comes with Oracle. If you are not using those, you might improve system performance marginaly not not auto starting those daemons.

SEP

[Hein van den Heuvel]

Just a little more info for you:

This 'beq' on the LOCAL=YES signifies a local communication protocol. Some of the alterntives are IPC for local, and TPC for remote connections.
As you can observe, those local 'slaves' are created by other local processes, often with a PID just one or 2 lower than those 'background' slave processes.

The remote tasks are created by "The Listener".
In your case:
oracle 2535 1 1 Oct 17 ? 11:52:57 /users/oracle/product/9.2.0/bin/tnslsnr LISTENER -inherit

To learn more about the connections as they come and go: su - oracle; lsnrctl status
This will point to a log which you can tail -f

For the real details you'll have to ask Oracle itself nicely. You don't want to learn that here, but ask the folks around. you. You'll need an interface program like SQLPLUS and you'll want to query internal tables like V$SESSIONS and V$PROCESS (sp?)

hth,
Hein.

[Indira Aramandla · Accepted solution]

Hi Frank,

When you see a number of oracle process on the server, some of then are the oracle background processes which manage the Oracle RDBMS. These process must always be running for the server to be available; others are optional on all platforms; and some are optional and specific to certain platforms.

For example A = Must always be running. The following are the background process.
DBWR (A) - the database writer
LGWR (A) - the log writer
PMON (A) - the process monitor
SMON (A) - the system monitor

On UNIX, a separate operating system process is created to run each of the background functions listed above.

The default Net driver on Oracle on UNIX has two main purposes. One is to 'spawn' subprocesses and pass / receive message(s) to / from the spawned process. The other is to provide a default local SQL*Net connection layer that does NOT require any listener process.

Once connected a process listing (Using the ps command) will show
two processes - 'sqlplus' and 'oracle'. Eg:

PID PPID PROCESS
6598 6367 sqlplus scott/tiger
6600 1 oracleA (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Here are answeres to your questions.

1. What does LOCAL=(YES|NO) mean?

You see processes on server as follows:

oracleSID(DESCRIPTION=(LOCAL=YES) or as
oracleSID(DESCRIPTION=(LOCAL=NO)

The LOCAL=YES is more likely to be the User's shadow process connected to the Database.
While The LOCAL=NO will be deamons or processes like the Intelligent Agent connected to the Database.

When you connect as sqlplus username/password without specifying a TNS connect string (i.e @prod), you would get oracle (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=tcp.....

If you did specify a TNS connect string, then the processes you would see would like like this:
oracle (DESCRIPTION=(LOCAL=NO)(ADDRESS=(PROTOCOL=tcp......

because you would be connecting through sqlnet and the connection has to be routed through a listener.

To summarise, every dedicated connection to the database will result in the creation of an oracle process. Whether or not it is local=YES or local=NO depends on how you are connecting (SQLNET or not).

2. Why do the LOCAL=NO processes have a PPID of 1 (init)?
3. Is an "oracleora" process with a parent of init normal behavior or is this an indication of these processes are orphans?

The bequeath driver spawns a subprocess which then executes a program. In the case of a standard database connection the program executed is the Oracle executable.

On some UNIX platforms the bequeath driver performs a 'double fork' such that it spawns a process which then spawns a further process and the 'middle' process drops out. The Oracle shadow process becomes 'orphaned' and hence its parent becomes 'init'.
This is to avoid potential problems with signal handling (SIGCHLD) and 'wait' code in the client process. Process listings of local SQL*Net connections thus show '1' for the 'parent id' of such an Oracle shadow process. This is quite normal and does NOT indicate a rogue process as was the case under SQL*Net V1.


I hope this helps


Indira A

[Yogeeraj_1]

hi Frank,

below an extract from metalink note:158281.1
Subject: Description OF LOCAL=YES on TNS Connections


The LOCAL=YES is more likely to be the User's shadow process connected to the Database.

While

The LOCAL=NO will be deamons or processes like the Intelligent Agent connected to the Database.

When you connect as sqlplus username/password
without specifying a TNS connect string (i.e @prod), you would get oracle (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=tcp....

If you did specify a TNS connect string, then the processes you would see would like like this:
oracle (DESCRIPTION=(LOCAL=NO)(ADDRESS=(PROTOCOL=tcp....

because you would be connecting through sqlnet and the connection has to be routed through a listener.

To summarise, every dedicated connection to the database will result in the creation of an oracle process.Whether or not it is local=YES or
local=NO depends on how you are connecting (SQLNET or not).



hope this clarifies everything.

kind regards
yogeeraj

[Frank Fitch_1]

Thanks all for your help and your quick responses !!!! Frank

[Frank Fitch_1]

.