Oracle Database Discussions

PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally).

PMON also registers information about the instance and dispatcher processes with the network listener.

You must distinguish between the client process -- which is the user process, for example, SQLPLUS.EXE on a Windows client or sqlplus from another Linux/Unix host or even the database server itself -- and the server process which is attached to the SGA.

PMON monitors server processes , not client processes.
That is why we sometimes have the "runaway process" scenario where the client has abnormally disconnected but the server process is still "active". PMON and the server process do not "know" that the client has disappeared.

SQLNet based Dead Connection Detection may be used by some sites to handle such situations (not with 100% success always).

Hemant K Chitale
http://hemantoracledba.blogspot.com

Thanks for reply Hemant sir

But my doubt is how does oracle comes to know if my system restarts while i was logged into sql plus.

Will some process detect it immediately or after some time?? if so what is the mechanism involved.


Thanks

Depends what you mean by "my system".

If you are logged into system using sqlplus to access "remote" DB, then you should realize if/when your system restarts.
If you are logged into system using sqlplus to access "remote" DB & if remote DB system restarts, sqlplus will throw error next time you issue command to DB.

What i mean was i am using sql plus installed on my PC and is connected to remote server. Now i restart my PC(without closing sql session). Then how will the things go forward . How will pmon detect it?

First, some terminology:
Background server processes: PMON, SMON, DBWR, LGWR, etc. These are the core instance processes. They must all be up and running to have an operating instance.

User server processes: These are dedicated or shared server processes that facilitate communication with client programs. If one of these dies, a user loses connection to the database, but the instance doesn't crash.

PMON checks for existence of the other background processes (DBWR, SMON, LGWR, etc) every 3 seconds. PMON checks for users server processes every 60 seconds. If it finds a dead background process, then it will crash the instance. If it finds a dead server process, it will go about cleaning in progress transaction, clearing locks and latches, etc.

That all happens on the database server side of the world.

Now, on the client side, say, SQL*Plus running on your local PC, the story is a bit different. SQL*Plus connects to a listener, listener spawns a dedicated server process (or hands off to a dispatcher in the shared server scenario) and the SQL*Plus process connects to the server process to request work to be done on behalf of the user. While the SQL*Plus program is idle, and not asking the server to do any work, the server process sits idle, waiting on a 'SQL*Net message from client' wait, which essentially means the server process is blocking on the network socket, waiting for the client program to make a request. If, in the meanwhile, the SQL*Plus client program goes away, there's no way for the server process to know that, nor is there any way for PMON to know that. So, yes, it's possible that the server process will be "orphaned". It will be sitting there, waiting for input from a client program that it has no idea has gone away. Now, in general, this should not be a big problem. If however, you work in an environment where this type of thing can happen frequently, you could consider enabling dead connection detection (DCD). However, DCD can put a load on the database server, so, should not normally be enabled unless it's really needed.

Hope that helps clarify things a bit,

-Mark

You'll need to launch sqlplus after restart & connect to a new session to the DB.
As previously stated, PMON does not care in the least about the now "orphan" client sessions.
Without manual intervention or DCD, the old session continues to remain within the DB; without any subsequent changes to the DB by this old session.
If previously DML had occurred prior to client restart, the changes are neither COMMITed nor ROLLBACKed.
Eventually the DML above will be ROLLBACKed; possibly as late as next instance restart.

As has already been pointed out, if your client abnormally disconnects, the server process does not know of it.

You should use Dead Connection Detection to handle such cases.
See SQLNET.EXPIRE_TIME : http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref474


Hemant K Chitale
http://hemantoracledba.blogspot.com

Ok suppose i disconnected my sql session abnormally while some large updates were being performed.
So according to you and the post after yours (by sb92075) the tables will be locked for unlimited time or till instance restarts is'nt it?

PMON waits for an idle session timeout. If it comes up, it won't wait for the instance to get restarted but would start clearing the processes and its related resources. If you would crash the instance, its the SMON who would take care of the cleaning.

If you would end your session abruptly, PMON would start clearing it right away when the session timeout would hit it. You would not need wait for the server to get restarted.

HTH
Aman....

Thanks Aman. Is there any parameter which needs to be set after which session timeout occurs.

Hi,

Refer to Hemanth reply,he provided the parameter for your reference.

- Pavan Kumar N
ORACLE 9i/10g - OCP
http://www.oracleinternals.blogspot.com

"idle session timeout" is not the same as "dead connection detection".

The former is configured as a limit in the profile configured for the database account (you can have the same limit or different profiles with different limits for different accounts).

The latter is configured as SQLNet parameter which applies to all SQLNet (i.e. remote) connections to the database.


Hemant K Chitale