Oracle is designed to be a very portable database—it is available on every platform of relevance, from Windows to UNIX to mainframes. However, the physical architecture of Oracle looks different on different operating systems. For example, on a UNIX operating system, you'll see Oracle implemented as many different operating system processes, virtually a process per major function. On UNIX, this is the correct implementation, as it works on a multiprocess foundation. On Windows, however, this architecture would be inappropriate and would not work very well (it would be slow and nonscalable). On the Windows platform, Oracle is implemented as a single process with multiple threads. On IBM mainframe systems, running OS/390 and z/OS, the Oracle operating system–specific architecture exploits multiple OS/390 address spaces, all operating as a single Oracle instance. Up to 255 address spaces can be configured for a single database instance. Moreover, Oracle works together with OS/390 Workload Manager (WLM) to establish the execution priority of specific Oracle workloads relative to each other and relative to all other work in the OS/390 system. Even though the physical mechanisms used to implement Oracle from platform to platform vary, the architecture is sufficiently generalized that you can get a good understanding of how Oracle works on all platforms.
In this chapter, I present a broad picture of this architecture. We'll take a look at the Oracle server and define some terms such as "database" and "instance" (terms that always seem to cause confusion). We'll take a look at what happens when you "connect" to Oracle and, at a high level, how the server manages memory. In the subsequent three chapters, we'll look in detail at the three major components of the Oracle architecture:
Chapter 3 covers files. Here we'll look at the five general categories of files that make up the database: parameter, data, temp, control, and redo log files. We'll also cover other types of files, including trace, alert, dump (DMP), data pump, and simple flat files. We'll look at the new file area (Oracle 10g and above) called Flashback Recovery, and we'll also discuss the impact that Automatic Storage Management (ASM) has on file storage.
Chapter 4 covers the Oracle memory structures referred to as the System Global Area (SGA), Process Global Area (PGA), and User Global Area (UGA). We'll examine the relationships between these structures, and we'll also discuss the shared pool, large pool, Java pool, and various other SGA components.
Chapter 5 covers Oracle's physical processes or threads. We'll look at the three different types of processes that will be running on the database: server processes, background processes, and slave processes.
It was hard to decide which of these components to cover first. The processes use the SGA, so discussing the SGA before the processes might not make sense. On the other hand, when discussing the processes and what they do, I'll need to make references to the SGA. These two components are closely tied: the files are acted on by the processes and won't make sense without first understanding what the processes do.
What I'll do, then, is define some terms and give a general overview of what Oracle looks like (if you were to draw it on a whiteboard). You'll then be ready to get into some of the details.
There are two terms that, when used in an Oracle context, seem to cause a great deal of confusion: "database and "instance." In Oracle terminology, the definitions of these terms are as follows:
Database: A collection of physical operating system files or disks. When using Oracle Automatic Storage Management (ASM) or RAW partitions, the database may not appear as individual, separate files in the operating system, but the definition remains the same.
Instance: A set of Oracle background processes or threads and a shared memory area, which is memory that is shared across those threads or processes running on a single computer. This is the place for volatile, nonpersistent stuff, some of which gets flushed to disk. A database instance can exist without any disk storage whatsoever. It might not be the most useful thing in the world, but thinking about it that way definitely helps draw the line between the instance and the database.
The two terms are sometimes used interchangeably, but they embrace very different concepts. The relationship between them is that a database may be mounted and opened by many instances. An instance may mount and open just a single database at any point in time. In fact, it is true to say that an instance will mount and open at most a single database in its entire lifetime! We'll look at an example of that in a moment.
Confused even more? Some further explanation should help clear up these concepts. An instance is simply a set of operating system processes, or a single process with many threads, and some memory. These processes can operate on a database, which is just a collection of files (data files, temporary files, redo log files, and control files). At any time, an instance will have only one set of files (one database) associated with it. In most cases, the opposite is true as well: a database will have only one instance working on it. However, in the special case of Oracle Real Application Clusters (RAC), an Oracle option that allows it to function on many computers in a clustered environment, we may have many instances simultaneously mounting and opening this one database, which resides on a set of shared physical disk. This gives us access to this single database from many different computers at the same time. Oracle RAC provides for extremely highly available systems and has the potential to architect extremely scalable solutions.
Let's start by taking a look at a simple example. Say we've just installed Oracle 11g version 11.2.0.1 on our Linux-based computer. We did a software-only installation. No starter databases, nothing—just the software.
The pwd command shows the current working directory, dbs (on Windows, this would be the database directory) and the ls –l command shows the directory is empty. There is no init.ora file and no SPFILES (stored parameter files; these will be discussed in detail in Chapter 3).
[ora11gr2@dellpe dbs]$ pwd /home/ora11gr2/dbs [ora11gr2@dellpe dbs]$ ls -l total 0
Using the ps (process status) command, we can see all processes being run by the user ora11gr2 (the Oracle software owner in this case). There are no Oracle database processes whatsoever at this point.
[ora11gr2@dellpe dbs]$ ps -aef | grep ora11gr2 ora11gr2 4447 4446 0 13:15 pts/1 00:00:00 -bash ora11gr2 4498 4447 0 13:17 pts/1 00:00:00 ps -aef ora11gr2 4499 4447 0 13:17 pts/1 00:00:00 grep ora11gr2
We then enter the ipcs command, a UNIX command that is used to show interprocess communication devices, such as shared memory, semaphores, and the like. Currently there are none in use on this system at all.
[ora11gr2@dellpe dbs]$ ipcs -a
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
------ Semaphore Arrays --------
key semid owner perms nsems
------ Message Queues --------
key msqid owner perms used-bytes messagesWe then start up SQL*Plus (Oracle's command-line interface) and connect as sysdba (the account that is allowed to do virtually anything in the database). Initially, assuming you haven't yet set the environment variable ORACLE_SID, you'll see:
[ora11gr2@dellpe dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 11 14:07:14 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-12162: TNS:net service name is incorrectly specified Enter user-name:
This error occurs because the database software has no idea what to try to connect to. When you connect, the Oracle software will look for a TNS connect string (a network connection). If, as in our example, the connect string is not supplied, the Oracle software will look at the environment for a variable named ORACLE_SID (on Windows, it would look also in the registry for the ORACLE_SID variable). The ORACLE_SID is the Oracle "site identifier;" it is sort of a key to gain access to an instance. If we set our ORACLE_SID:
[ora11gr2@dellpe dbs]$ export ORACLE_SID=ora11g
the connection is successful and SQL*Plus reports we are connected to an idle instance:
[ora11gr2@dellpe dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 11 13:48:01 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance. SQL>
Our "instance" right now consists solely of the Oracle server process shown in bold in the following output. There is no shared memory allocated yet and no other processes.
SQL> !ps -aef | grep ora11gr2 ora11gr2 4447 4446 0 13:15 pts/1 00:00:00 -bash ora11gr2 4668 4667 0 13:48 pts/2 00:00:00 sqlplus as sysdbaora11gr2 4669 4668 0 13:48 ? 00:00:00 oracleora11g(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))ora11gr2 4678 4668 0 13:48 pts/2 00:00:00 /bin/bash -c ps -aef | grep ora11gr2 ora11gr2 4679 4678 0 13:48 pts/2 00:00:00 ps -aef ora11gr2 4680 4678 0 13:48 pts/2 00:00:00 grep ora11gr2 SQL> !ipcs -a ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status ------ Semaphore Arrays -------- key semid owner perms nsems ------ Message Queues -------- key msqid owner perms used-bytes messages SQL>
Note
On Windows, Oracle executes as a single process with threads; you won't see separate processes as on Linux. Moreover, the Windows threads will not have the same names as the processes just shown. I am using Linux specifically here so we can differentiate the individual processes and "see" them clearly.
One interesting thing to note from this ps output is the process named oracle ora11g. No matter how hard you look on your system, you will not find an executable by that name. The Oracle binary that is executing is really the binary file $ORACLE_HOME/bin/oracle.
Note
It is assumed that the environment variable (on UNIX) or registry setting (on Windows) named ORACLE_HOME has been set and represents the fully qualified path to where the Oracle software is installed.
The Oracle developers simply rename the process as it is loaded into memory. The name of the single Oracle process that is running right now (our dedicated server process; more on what a dedicated server process is later) is oracle$ORACLE_SID. That naming convention makes it very easy to see what processes are associated with which instances and so on. So, let's try to start the instance now:
SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/home/ora11gr2/dbs/initora11gr2.ora' SQL>
Notice the error about a missing file named initora11gr2.ora. That file, referred to colloquially as an init.ora file, or more properly as a parameter file, is the sole file that must exist to start up an instance—we need either a parameter file (a simple flat file that I'll describe in more detail shortly) or a stored parameter file.
We'll create the parameter file now and put into it the minimal information we need to actually start a database instance. (Normally, we'd specify many more parameters, such as the database block size, control file locations, and so on). By default, this file is located in the $ORACLE_HOME/dbs directory and has the name init${ORACLE_SID}.ora:
[ora11gr2@dellpe ~]$ cd $ORACLE_HOME/dbs [ora11gr2@dellpe dbs]$ echo db_name=ora11g > initora11g.ora [ora11gr2@dellpe dbs]$ cat initora11g.ora db_name=ora11g
and then, once we get back into SQL*Plus:
SQL> startup nomount ORACLE instance started. Total System Global Area 150667264 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 6725632 bytes SQL>
We used the nomount option to the startup command since we don't actually have a database to mount yet (the SQL*Plus documentation includes all of the startup and shutdown options).
Note
On Windows, prior to running the startup command, you'll need to execute a service creation statement using the oradim.exe utility
Now we have what I'd call an instance. The background processes needed to actually run a database are all there, including process monitor (pmon), log writer (lgwr), and so on (these processes are covered in detail in Chapter 5). Let's take a look:
SQL> !ps -aef | grep ora11gr2 ora11gr2 4447 4446 0 13:15 pts/1 00:00:00 -bash ora11gr2 4900 4899 0 14:15 pts/2 00:00:00 /home/ora11gr2/bin/sqlplus ora11gr2 4904 1 0 14:16 ? 00:00:00 ora_pmon_ora11g ora11gr2 4906 1 0 14:16 ? 00:00:00 ora_vktm_ora11g ora11gr2 4910 1 0 14:16 ? 00:00:00 ora_gen0_ora11g ora11gr2 4912 1 0 14:16 ? 00:00:00 ora_diag_ora11g ora11gr2 4914 1 0 14:16 ? 00:00:00 ora_dbrm_ora11g
ora11gr2 4916 1 0 14:16 ? 00:00:00 ora_psp0_ora11g ora11gr2 4918 1 0 14:16 ? 00:00:00 ora_dia0_ora11g ora11gr2 4920 1 0 14:16 ? 00:00:00 ora_mman_ora11g ora11gr2 4922 1 0 14:16 ? 00:00:00 ora_dbw0_ora11g ora11gr2 4924 1 0 14:16 ? 00:00:00 ora_lgwr_ora11g ora11gr2 4926 1 0 14:16 ? 00:00:00 ora_ckpt_ora11g ora11gr2 4928 1 0 14:16 ? 00:00:00 ora_smon_ora11g ora11gr2 4930 1 0 14:16 ? 00:00:00 ora_reco_ora11g ora11gr2 4932 1 0 14:16 ? 00:00:00 ora_mmon_ora11g ora11gr2 4934 1 0 14:16 ? 00:00:00 ora_mmnl_ora11g ora11gr2 4935 4900 0 14:16 ? 00:00:00 oracleora11g(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) ora11gr2 4953 4900 0 14:18 pts/2 00:00:00 /bin/bash -c ps -aef | grep ora11gr2 ora11gr2 4954 4953 0 14:18 pts/2 00:00:00 ps -aef ora11gr2 4955 4953 0 14:18 pts/2 00:00:00 grep ora11gr2
Additionally, ipcs, for the first time, reports the use of shared memory and semaphores—two important interprocess communication devices on UNIX:
SQL> !ipcs -a ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x873d6bdc 753667 ora11gr2 660 153092096 16 ------ Semaphore Arrays -------- key semid owner perms nsems 0x420a82a0 1015808 ora11gr2 660 104 ------ Message Queues -------- key msqid owner perms used-bytes messages SQL>
Note we have no "database" yet. We have the name of a database (in the parameter file we created), but no actual database. If we try to "mount" this database, it would fail because, quite simply, the database does not yet exist. Let's create it. I've been told that creating an Oracle database involves quite a few steps, but let's see:
SQL> create database; Database created.
That is actually all there is to creating a database. In the real world, however, we'd use a slightly more complicated form of the CREATE DATABASE command because we would want to tell Oracle where to put the log files, data files, control files, and so on. But we do now have a fully operational database. We still need to run the $ORACLE_HOME/rdbms/admin/catalog.sql script and other catalog scripts to build the rest of the data dictionary we use every day (the views we use such as ALL_OBJECTS are not yet present in this database), but we have an actual database here. We can use a simple query against some Oracle V$ views, specifically V$DATAFILE, V$LOGFILE, and V$CONTROLFILE, to list the files that make up this database:
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /home/ora11gr2/dbs/dbs1ora11g.dbf /home/ora11gr2/dbs/dbx1ora11g.dbf /home/ora11gr2/dbs/dbu1ora11g.dbf SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /home/ora11gr2/dbs/log1ora11g.dbf /home/ora11gr2/dbs/log2ora11g.dbf SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /home/ora11gr2/dbs/cntrlora11g.dbf SQL>
Oracle used defaults to put everything together and created a database as a set of persistent files. If we close this database and try to open it again, we'll discover that we can't:
SQL> alter database close; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-1619c6: database has been previously opened and closed
An instance can mount and open at most one database in its life. Remember, the instance consists simply of the processes and shared memory. This is still up and running. All we did was close the database, that is, the physical files. We must discard this instance (shutdown) and create a new one (startup) in order to open this or any other database.
To recap,
An instance is a set of background processes and shared memory.
A database is a collection of data stored on disk.
An instance can mount and open only a single database, ever.
A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.
As noted earlier, in most cases there's a one-to-one relationship between an instance and a database. This is probably why the confusion surrounding the terms arises. In most peoples' experience, a database is an instance, and an instance is a database.
In many test environments, however, this is not the case. On my disk, I might have five separate databases. On the test machine, at any point in time there is only one instance of Oracle running, but the database it is accessing may be different from day to day or hour to hour, depending on my needs. By simply having many different parameter files, I can mount and open any one of these databases. Here, I have one instance at a time but many databases, only one of which is accessible at any time.
So now when people talk about an instance, you'll know they mean the processes and memory of Oracle. When they mention the database, they are talking about the physical files that hold the data. A database may be accessible from many instances, but an instance will provide access to exactly one database at a time.
You're probably ready now for an abstract picture of what an Oracle instance and database look like, so take a look at Figure 2-1.
Figure 2-1 shows an Oracle instance and database in their simplest form. Oracle has a large chunk of memory called the SGA that it uses, for example, to do the following:
Maintain many internal data structures that all processes need access to.
Cache data from disk; buffer redo data before writing it to disk.
Hold parsed SQL plans.
And so on.
Oracle has a set of processes that are "attached" to this SGA, and the mechanism by which they attach differs by operating system. In a UNIX environment, the processes will physically attach to a large shared memory segment, a chunk of memory allocated in the OS that may be accessed by many processes concurrently (generally using shmget() and shmat()).
Under Windows, these processes simply use the C call, malloc() to allocate the memory, since they are really threads in one big process and hence share the same virtual memory space.
Oracle will also have a set of files that the database processes or threads read and write (and Oracle processes are the only ones allowed to read or write these files). These files hold all of our table data, indexes, temporary space, redo logs, and so on.
If you were to start up Oracle on a UNIX-based system and execute a ps command, you'd see that many physical processes are running, with various names. You saw an example of that earlier when you observed the pmon, smon, and other processes. I cover these processes in Chapter 5, so just be aware for now that they are commonly referred to as the Oracle background processes. They are persistent processes that make up the instance, and you'll see them from the time you start the instance until you shut it down.
It is interesting to note that these are processes, not individual programs. There is only one Oracle binary executable on UNIX; it has many "personalities," depending on what it was told to do when it starts up. The same binary executable that was run to start ora_pmon_ora11g was also used to start the process ora_ckpt_ora11g. There is only one binary executable program, named simply oracle. It is just executed many times with different names.
On Windows, using the pstat tool (part of the Windows XP Resource Kit; search for "pstat download" using your favorite search engine if you don't have it), we'll find only one process, oracle.exe. Again, on Windows there is only one binary executable (oracle.exe). Within this process, we'll find many threads representing the Oracle background processes.
Using pstat (or any of a number of tools, such as tasklist, which comes with many Windows versions), we can see these processes:
C:WINDOWS> pstat
Pstat version 0.3: memory: 523760 kb uptime: 0 1:37:54.375
PageFile: ??C:pagefile.sys
Current Size: 678912 kb Total Used: 228316 kb Peak Used 605488 kb
Memory: 523760K Avail: 224492K TotalWs: 276932K InRam Kernel: 872K P:20540K
Commit: 418468K/ 372204K Limit:1169048K Peak:1187396K Pool N:10620K P:24588K
User Time Kernel Time Ws Faults Commit Pri Hnd Thd Pid Name
56860 2348193 File Cache
0:00:00.000 1:02:23.109 28 0 0 0 0 1 0 Idle Process
0:00:00.000 0:01:50.812 32 4385 28 8 694 52 4 System
0:00:00.015 0:00:00.109 60 224 172 11 19 3 332 smss.exe
0:00:33.234 0:00:32.046 2144 33467 1980 13 396 14 556 csrss.exe
0:00:00.343 0:00:01.750 3684 6811 7792 13 578 20 580 winlogon.exe
0:00:00.078 0:00:01.734 1948 3022 1680 9 275 16 624 services.exe
0:00:00.218 0:00:03.515 1896 5958 3932 9 363 25 636 lsass.exe
0:00:00.015 0:00:00.078 80 804 592 8 25 1 812 vmacthlp.exe
0:00:00.093 0:00:00.359 1416 2765 3016 8 195 17 828 svchost.exe
0:00:00.062 0:00:00.453 1340 3566 1764 8 244 10 896 svchost.exe
0:00:00.828 0:01:16.593 9632 36387 11708 8 1206 59 1024 svchost.exe
0:00:00.046 0:00:00.640 1020 2315 1300 8 81 6 1100 svchost.exe
0:00:00.015 0:00:00.234 736 2330 1492 8 165 11 1272 svchost.exe
0:00:00.015 0:00:00.218 128 1959 3788 8 117 10 1440 spoolsv.exe
0:00:01.312 0:00:19.828 13636 35525 14732 8 575 19 1952 explorer.exe
0:00:00.250 0:00:00.937 956 1705 856 8 29 1 228 VMwareTray.exe
0:00:00.812 0:00:04.562 1044 4619 3800 8 165 4 240 VMwareUser.exe
0:00:00.015 0:00:00.156 88 1049 1192 8 88 4 396 svchost.exe
0:00:00.109 0:00:04.640 744 1229 2432 8 81 3 460 cvpnd.exe
0:00:02.015 0:00:12.078 1476 17578 1904 13 139 3 600 VMwareService.exe
0:00:00.031 0:00:00.093 124 1004 1172 8 105 6 192 alg.exe
0:00:00.062 0:00:00.937 2648 13977 22656 8 101 3 720 TNSLSNR.EXE
0:04:00.359 0:02:57.734164844 2009785 279168 8 550 29 1928 oracle.exe
0:00:00.093 0:00:00.437 6736 2316 2720 8 141 6 1224 msiexec.exe
0:00:00.015 0:00:00.031 2668 701 1992 8 34 1 804 cmd.exe
0:00:00.015 0:00:00.000 964 235 336 8 11 1 2856 pstat.exeHere we can see there are 29 threads (Thd in the display) contained in the single Oracle process. These threads represent what were processes on UNIX—they are the pmon, arch, lgwr, and so on. They each represent a separate bit of the Oracle process. Paging down through the pstat report, we can see more details about each thread:
pid:788 pri: 8 Hnd: 550 Pf:2009785 Ws: 164844K oracle.exe tid pri Ctx Swtch StrtAddr User Time Kernel Time State 498 9 651 7C810705 0:00:00.000 0:00:00.203 Wait:Executive 164 8 91 7C8106F9 0:00:00.000 0:00:00.000 Wait:UserRequest ... a68 8 42 7C8106F9 0:00:00.000 0:00:00.031 Wait:UserRequest
We can't see the thread "names" like we could on UNIX (ora_pmon_ora11g and so on), but we can see the thread IDs (Tid), priorities (Pri), and other operating system accounting information about them.
In this section, we'll take a look at the mechanics behind the two most common ways to have requests serviced by an Oracle server: dedicated server and shared server connections. We'll see what happens on the client and the server in order to establish connections, so we can log in and actually do work in the database. Lastly, we'll take a brief look at how to establish TCP/IP connections; TCP/IP is the primary networking protocol used to connect over the network to Oracle. And we'll look at how the listener process on our server, which is responsible for establishing the physical connection to the server, works differently in the cases of dedicated and shared server connections.
Figure 2-1 and the pstat output presented a picture of what Oracle looks like immediately after starting. If we were now to log into this database using a dedicated server, we would see a new thread get created just to service us:
C:Documents and Settings kyte>sqlplus tkyte/tkyte
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Dec 11 18:05:32 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> host pstat
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
Pstat version 0.3: memory: 523760 kb uptime: 0 1:40:36.687
PageFile: ??C:pagefile.sys
Current Size: 678912 kb Total Used: 227744 kb Peak Used 605488 kb
Memory: 523760K Avail: 194928K TotalWs: 315172K InRam Kernel: 876K P:20616KCommit: 447888K/ 401420K Limit:1169048K Peak:1187396K Pool N:10636K P:24628K
User Time Kernel Time Ws Faults Commit Pri Hnd Thd Pid Name
...
0:04:00.515 0:02:58.546166948 2020411 279216 8 549 30 1928 oracle.exe
...
SQL>Now you can see there are 30 threads instead of 29, the extra thread being our dedicated server process (more information on what exactly a dedicated server process is shortly). When we log out, the extra thread will go away. On UNIX, we would see another process get added to the list of Oracle processes running, and that would be our dedicated server.
[tkyte@dellpe ~]$ ps -aef | grep oracle$ORACLE_SID tkyte 26935 19699 0 16:05 pts/5 00:00:00 grep oracleora11gr2 [tkyte@dellpe ~]$ sqlplus / SQL*Plus: Release 11.2.0.1.0 Production on Mon May 10 16:05:22 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ops$tkyte%ORA11GR2> !ps -aef | grep oracle$ORACLE_SIDora11gr2 26938 26937 1 16:05 ? 00:00:00 oracleora11gr2(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))tkyte 26947 26945 0 16:05 pts/2 00:00:00 grep oracleora11gr2
This brings us to the next iteration of our diagram. If we were to connect to Oracle in its most commonly used configuration, we would see something like Figure 2-2.
As noted, typically Oracle will create a new process for me when I log in. This is commonly referred to as the dedicated server configuration, since a server process will be dedicated to me for the life of my session. For each session, a new dedicated server will appear in a one-to-one mapping. This dedicated server process is not (by definition) part of the instance. My client process (whatever program is trying to connect to the database) will be in direct communication with this dedicated server over some networking conduit, such as a TCP/IP socket. It is this server process that will receive my SQL and execute it for me. It will read data files if necessary, and it will look in the database's cache for my data. It will perform my update statements. It will run my PL/SQL code. Its only goal is to respond to the SQL calls I submit to it.
Oracle can also accept connections in a manner called shared server, in which you wouldn't see an additional thread created or a new UNIX process appear for each user connection.
Note
In Version 7.x and 8.x of Oracle, shared server was known as Multi-Threaded Server or MTS. That legacy name is not in use anymore.
In shared server, Oracle uses a pool of shared processes for a large community of users. Shared servers are simply a connection pooling mechanism. Instead of having 10,000 dedicated servers (that's a lot of processes or threads) for 10,000 database sessions, shared server lets us have a small percentage of this number of processes or threads, which would be (as the name implies) shared by all sessions. This allows Oracle to connect many more users to the instance than would otherwise be possible. Our machine might crumble under the load of managing 10,000 processes, but managing 100 or 1,000 processes is doable. In shared server mode, the shared processes are generally started up with the database and appear in the ps list.
A big difference between shared and dedicated server connections is that the client process connected to the database never talks directly to a shared server, as it would to a dedicated server. It can't talk to a shared server because that process is, in fact, shared. In order to share these processes, we need another mechanism through which to "talk." Oracle employs a process (or set of processes) called a dispatcher for this purpose. The client process will talk to a dispatcher process over the network. The dispatcher process will put the client's request into the request queue in the SGA (one of the many things the SGA is used for). The first shared server that is not busy will pick up this request and process it (e.g., the request could be UPDATE T SET X = X+5 WHERE Y = 2). Upon completion of this command, the shared server will place the response in the invoking dispatcher's response queue. The dispatcher process monitors this queue and, upon seeing a result, will transmit it to the client. Conceptually, the flow of a shared server request looks like Figure 2-3.
As shown in Figure 2-3, the client connection will send a request to the dispatcher. The dispatcher will first place this request onto the request queue in the SGA (1). The first available shared server will dequeue this request (2) and process it. When the shared server completes, the response (return codes, data, and so on) is placed into the response queue (3), subsequently picked up by the dispatcher (4), and transmitted back to the client.
As far as the developer is concerned, there is conceptually no difference between a shared server connection and a dedicated server connection. Architecturally they are quite different, but that's not apparent to an application.
Now that you understand what dedicated server and shared server connections are, you may have the following questions:
How do I get connected in the first place?
What would start this dedicated server?
How might I get in touch with a dispatcher?
The answers depend on your specific platform, but the sections that follow outline the process in general terms.
We'll investigate the most common networking case: a network-based connection request over TCP/IP. In this case, the client is situated on one machine and the server resides on another, with the two connected on a TCP/IP network. It all starts with the client. The client makes a request using the Oracle client software (a set of provided application program interfaces, or APIs) to connect to a database. For example, the client issues the following:
[tkyte@dellpe ~]$ sqlplus scott/tiger@orcl SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 11 16:00:31 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options scott%ORA11GR2>
Note
The string orcl used above is unique to my configuration. I have a tnsnames.ora entry (more on that below) named orcl. It is a TNS connect string that points to an existing, installed configured Oracle Database 11g Release 2 instance on my network. You will be using your own TNS connect strings, unique to your installation.
Here, the client is the program SQL*Plus, scott/tiger is the username and password, and orcl is a TNS service name. TNS stands for Transparent Network Substrate and is "foundation" software built into the Oracle client that handles remote connections, allowing for peer-to-peer communication. The TNS connection string tells the Oracle software how to connect to the remote database. Generally, the client software running on your machine will read a file called tnsnames.ora. This is a plain-text configuration file commonly found in the $ORACLE_HOME/network/admin directory ($ORACLE_HOME represents the full path to your Oracle installation directory). It will have entries that look like this:
[tkyte@dellpe ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = somehost.somewhere.com)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[tkyte@dellpe ~]$This configuration information allows the Oracle client software to map the TNS connection string we used, orcl, into something useful—namely, a hostname, a port on that host on which a listener process will accept connections, the service name of the database on the host to which we wish to connect, and so on. A service name represents groups of applications with common attributes, service level thresholds, and priorities. The number of instances offering the service is transparent to the application, and each database instance may register with the listener as willing to provide many services. So, services are mapped to physical database instances and allow the DBA to associate certain thresholds and priorities with them.
This string, orcl, could have been resolved in other ways. For example, it could have been resolved using Oracle Internet Directory (OID), which is a distributed Lightweight Directory Access Protocol (LDAP) server, similar in purpose to DNS for hostname resolution. However, use of the tnsnames.ora file is common in most small to medium installations where the number of copies of such a configuration file is manageable.
Now that the client software knows where to connect to, it will open a TCP/IP socket connection to the server with the hostname somehost.somewhere.com on port 1521. If the DBA for our server has installed and configured Oracle Net, and has the listener listening on port 1521 for connection requests, this connection may be accepted. In a network environment, we will be running a process called the TNS listener on our server. This listener process is what will get us physically connected to our database. When it receives the inbound connection request, it inspects the request and, using its own configuration files, either rejects the request (because there is no such service, for example, or perhaps our IP address has been disallowed connections to this host) or accepts it and goes about getting us connected.
If we are making a dedicated server connection, the listener process will create a dedicated server for us. On UNIX, this is achieved via fork() and exec() system calls (the only way to create a new process after initialization in UNIX is via fork()). The new dedicated server process inherits the connection established by the listener, and we are now physically connected to the database. On Windows, the listener process requests the database process to create a new thread for a connection. Once this thread is created, the client is "redirected" to it, and we are physically connected. Diagrammatically in UNIX, it would look as shown in Figure 2-4.
However, the listener will behave differently if we are making a shared server connection request. This listener process knows the dispatcher(s) we have running in the instance. As connection requests are received, the listener will choose a dispatcher process from the pool of available dispatchers. The listener will either send back to the client the connection information describing how the client can connect to the dispatcher process or, if possible, hand off the connection to the dispatcher process (this is OS- and database version–dependent, but the net effect is the same). When the listener sends back the connection information, it is done because the listener is running on a well-known hostname and port on that host, but the dispatchers also accept connections on randomly assigned ports on that server. The listener is made aware of these random port assignments by the dispatcher and will pick a dispatcher for us. The client then disconnects from the listener and connects directly to the dispatcher. We now have a physical connection to the database. Figure 2-5 illustrates this process.
This completes our overview of the Oracle architecture. In this chapter, we defined the terms "instance" and "database" and saw how to connect to the database through either a dedicated server connection or a shared server connection. Figure 2-6 sums up the material covered in the chapter and shows the interaction between a client using a shared server connection and a client using a dedicated server connection. It also shows that an Oracle instance may use both connection types simultaneously. (In fact, an Oracle database always supports dedicated server connections—even when configured for shared server.)
Now you're ready to take a more in-depth look at the files that comprise the database and the processes behind the server—what they do and how they interact with each other. You're also ready to look inside the SGA to see what it contains and what its purpose is. You'll start in the next chapter by looking at the types of files Oracle uses to manage the data and the role of each file type.