What Happens when you login to Oracle Database:

I log in to oracle using Scott? What happens , how oracle serves my request?

Ans:How the Oracle Database processes operate—by decoupling the user process from the server process. If you don't remember it completely,When a user connects to an Oracle database, say with SQL*Plus, Oracle creates a new process to service this user's program.
This new process is called the Oracle server process, which differs from the user's process (sqlplus, sqlplus.exe, TOAD.exe, or whatever it else it may be). This server process interacts with the memory structures such as the System Global Area (SGA) and reads from the data files; if the data is not found in the data block buffers in the SGA, and so on. Under no circumstances is the user's process (sqlplus) allowed to directly interact with the Oracle database. As there are two processes (the user process and the server process) working in tandem to get the work done, this is sometimes known as two-task architecture.If a user process does something potentially disruptive, such as violating the memory management in the host machine, the Oracle database itself is not affected and the damage is limited to the user's process.
Note: that the above applies to Oracle connections in a dedicated server environment. In a multi-threaded server environment, this model is a little different in the sense that a single server process can service more than one user process. It's still two-task, but instead of a 1:1 relation between the server and user processes, it's 1:many.

What are Host based Connections and What are Network-based Connections?

Ans:Host Based Connection:

Suppose the user logs into the database using SQL*Plus.
oracle@myserver::/scripts [practicedb] $ sqlplus bnfdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 17:44:56 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options

USER is "BNFDBA"

Instance
----------
PracticeDB

PracticeDB > select sid from v$mystat where rownum=1;

SID
----------
3917

PracticeDB > select spid from v$session s, v$process p
2 where s.sid = (select sid from v$mystat where rownum <2) addr =" s.paddr;"> !ps -eafgrep 10442

bnfdba 17151 17149 1 18:32:58 pts/7 0:00 grep 10442
bnfdba 10442 10212 0 18:27:07 ? 0:00 oraclepracticedb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

This, of course, assumes that no other SQL*Plus sessions have been running on the server. 
The first one you've seen before (the process for the SQL*Plus session). The second one—process ID 10442—is the server process that Oracle creates for the user. Note the Parent Process ID of the process; it's 10212, which is the process ID of the SQL*Plus session. 
The process name is "oraclepracticedb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))", which tells you several things. First, the presence of the clause 
LOCAL=YES indicates that this process started due to another process that is running locally, on the same server as the database itself. It also shows PROTOCOL=beq, which means that the connection was made through a bequeath connection. You can also find the information about the server process from the dynamic views.

select spid
from v$session s,v$process p
where s.sid = (select sid from v$mystat where rownum <2) addr =" s.paddr;" practicedb =" (DESCRIPTION" address_list =" (ADDRESS" protocol =" TCP)(HOST" port =" 1521))" connect_data =" (SERVER" service_name =" PRACTICEDB)" onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:oracle@myserver::/scripts" target="_blank">oracle@myserver::/scripts [practicedb] $ sqlplusbnfdba@practicedb.bnfd.com

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 17:44:56 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options

USER is "BNFDBA"

Instance
----------
PracticeDB

PracticeDB > select sid from v$mystat where rownum=1;

SID
----------
3939

After this, if you search for this process:

It shows:

PracticeDB > @vusersid
Enter value for sid: 3939
Press return to continue
Fri Oct 19 page 1 
User Session Statistics

CLIENT SERVER OS ORACLE 
PID PID SID SERIAL USERNAME USERNAME MODULE
---------- ------- ----------- ----- --------- -------- ----- - --------------------------------26917 27292 3939 34941 oracle BNFDBA SQL*Plus

This, of course, assumes that no other SQL*Plus sessions have been running on the server.
Note the process id (26917). Now if you search that process ID Searching for this on the server:

PracticeDB > !ps -eafgrep 26917 
oracle 383 26917 1 17:47:22 pts/7 0:00 grep 26917
oracle 26917 29489 1 17:44:55 pts/7 0:00 sqlplus bnfdba@practicedb.bnfd.com <---This is the Client PID 29489 PracticeDB > !ps -eafgrep 27292

oracle 27292 1 0 17:45:03 ? 0:00 oraclepracticedb (LOCAL=NO) <-----This is the Server PID 27292 oracle 286 26917 1 17:47:11 pts/7 0:00 grep 27292 The parent process is "1". But why isn't it 29489?

Ans:To understand the answer, you have to understand how different Oracle server processes are created. In the first case, when the user did not use a TNS connect string, theconnection was routed directly to the database without going to the listener first. The database created a user process and then handed the control of the process to the 
process owner, a process known as bequeathing—hence the term bequeath process, which showed up in the process name. In the second case, when the user was still on the same server but connected through the listener, the listener created the process for the user—which is known as forking. Similarly, if the user process were running on a different machine (such as a laptop), the connection would have to be made to the listener and the listener would have
created the process. The process was created by a remote server, hence the process name contains the clause LOCAL=NO. Even if the SQL*Plus session was running on the same server, the fact that it was a non-bequeath connection made it a non-LOCAL one.

Note: Depending on the OS, you may not see the parent ID of the server process the same way you see the SQL*Plus session in bequeath connections. In some cases, even through the connection is bequeath, the parent ID will show as "1". Therefore, don't rely on the parent ID to determine what type of server process it is; use the process name instead.