Understanding Oracle Architecture – Creating a Database
This chapter introduced concepts such as database (data files, online redo log files, control files), instance (background processes and memory structures), parameter file, and listener. Now is a good time to present an Oracle architecture diagram that shows the various files and processes that constitute a database and instance. Some of the concepts depicted in Figure 2-6 have already been covered in detail, for example, database versus instance. Other aspects of Figure 2-6 will be covered in future chapters. However, it is appropriate to include a high-level diagram such as this to represent visually the concepts already discussed and created with the database creation to lay the foundation for understanding upcoming topics.
Figure 2–6. Oracle database architecture
There are several aspects to note about Figure 2-6. Communication with the database is initiated through a sqlplus user process. Typically, the user process connects to the database over the network. This requires that you configure and start a listener process. The listener process hands off incoming connection requests to an Oracle server process, which handles all subsequent communication with the client process. If a remote connection is initiated as a sys user, then a password file is required. A password file is also required for local sys connections that do not use OS authentication.
The instance consists of memory structures and background processes. When the instance starts, it reads the parameter file, which helps establish the size of the memory processes and other characteristics of the instance. When starting a database, the instance goes through three phases: nomount (instance started), mount (control files opened), and open (data files and online redo logs opened).
The number of background processes varies by database version. You can view the names and descriptions of the processes via this query:
This returns a long list of processes including multiple database and log writer processes. Here are a few of the major processes:
• DBWn: The database writer writes blocks from the database buffer cache to the data files.
• CKPT: The checkpoint process writes checkpoint information to the control files and data file headers.
• LGWR: The log writer writes redo information from the log buffer to the online redo logs.
• ARCn: The archiver copies the contents of online redo logs to archive redo log files.
• RVWR: The recovery writer maintains before images of blocks in the fast recovery area.
• MMON: The manageability monitor process gathers automatic workload repository statistics.
• MMNL: The manageability monitor lite process writes statistics from the active session history buffer to disk.
• SMON: The system monitor performs system-level cleanup operations, including instance recovery in the event of a failed instance, coalescing free space, and cleaning up temporary space.
• PMON: The process monitor cleans up abnormally terminated database connections and also automatically registers a database instance with the listener process.
• RECO: The recoverer process automatically resolves failed distributed transactions.
The structure of the System Global Area (SGA) varies by Oracle release. It is the major memory structures and can be automatically managed. You can view details for each component using this query:
The major SGA memory structures include the following:
• SGA: The SGA is the main read/write memory area and is composed of several buffers, such as the database buffer cache, redo log buffer, shared pool, large pool, Java pool, and steams pool.
• Buffer cache: The buffer cache stores copies of blocks read from data files.
• Log buffer: The log buffer stores changes to modified data blocks.
• Shared pool: The shared pool contains library cache information regarding recently executed SQL and PL/SQL code. The shared pool also houses the data dictionary cache, which contains structural information about the database, objects and users.
Finally, the program global area (PGA) is a memory area separate from the SGA. The PGA is a process-specific memory area that contains session-variable information. PGA is the work are used for sorting, grouping, and hashing.