CHAPTER 12
Architectural Overview of Oracle Database 12c
Exam Objectives
• 062.2.1 List the Architectural Components of an Oracle Database
• 062.1.2 Explain the Memory Structures
• 062.1.3 Describe the Process Structures
• 062.1.4 Explain the Relationship Between Logical and Physical Storage Structures
An Oracle server consists of two entities: the instance and the database. The instance consists of memory structures and processes, whereas the database consists of files on disk. Within the Oracle server there is complete abstraction of logical storage from physical storage. The logical structures that programmers see (such as tables) are not directly related to the physical structures (datafiles) that system administrators see. The relationship between the two is maintained by structures within the controlfile and the data dictionary.
List the Architectural Components of an Oracle Database
For the most part in this book, you will be dealing with the simplest database environment: one instance on one computer, opening a database stored on local disks. The more complex distributed architectures, involving multiple instances and multiple databases, are beyond the scope of the Oracle Certified Professional (OCP) examination (although not the Oracle Certified Master [OCM] qualification), although you may realistically expect to see high-level summary questions on distributed architecture.
Single-Instance Database Architecture
The instance consists of memory structures and processes. Its existence is transient, in your random access memory (RAM) and on your central processing units (CPUs). When you shut down the running instance, all trace of its existence goes away at the same time. The database consists of physical files on disk. Whether the instance is running or stopped, these files remain. Thus, the lifetime of the instance is only as long as it exists in memory; it can be started and stopped. By contrast, the database, once created, persists indefinitely—that is, until you deliberately delete the files that are associated with the database.
The processes that make up the instance are known as background processes because they are present and running at all times while the instance is active. These processes are for the most part completely self-administering, although in some cases it is possible for the database administrator (DBA) to influence the number of them and their operation. The memory structures, which are implemented in shared memory segments provided by the operating system, are known as the System Global Area (SGA). The SGA is allocated at instance startup and released on shutdown. Within certain limits, the SGA in the 12c instance and the components within it can be resized while the instance is running, either automatically or in response to the DBA’s instructions.
User sessions consist of a user process running locally to the user machine connecting to a server process running locally to the instance on the server machine. The technique for launching the server processes, which are started on demand for each session, is covered in Chapter 13. The connection between user process and server process is usually across a local area network (LAN) and uses Oracle’s proprietary Oracle Net protocol layered on top of an industry-standard protocol (usually Transmission Control Protocol [TCP]). The user-process/server-process split implements the client-server architecture; user processes generate SQL, and server processes execute SQL. The server processes are sometimes referred to as foreground processes, in contrast with the background processes that make up the instance. Associated with each server process is an area of nonshareable memory, called the Program Global Area (PGA). This is private to the session, unlike the SGA, which is available to all the foreground and background processes. Note that background processes also have a PGA. The size of any one session’s PGA will vary according to the memory needs of the session at any one time; the DBA can define an upper limit for the total of all the PGAs, and Oracle manages the allocation of this to sessions dynamically.
TIP You will sometimes hear the term shadow process. Be cautious of using this term. Some people use it to refer to foreground processes; others use it to refer to background processes. Never mind which is correct—just make sure you know what they are talking about.
Memory management in 12c can be totally automatic. The DBA need do nothing more than specify an overall memory allocation for both the SGA and the PGA and then let Oracle manage this memory as it thinks best. Alternatively, the DBA can control memory allocations. As an in-between technique, the DBA can define certain limits on what the automatic management can do.
EXAM TIP SGA memory is shared across all background and foreground processes; PGA memory can be accessed only by the foreground process of the session to which it has been allocated. Both SGA and PGA memory can be automatically managed.
The physical structures that make up an Oracle database are the datafiles, the online redo log files, and the controlfile. Within the physical structures of the database, which the system administrators see, are the logical structures that the end users see. The Oracle architecture guarantees abstraction of the logical from the physical; there is no way a programmer can determine where, physically, a bit of data is located. Programmers address only logical structures, such as tables. Similarly, it is impossible for system administrators to know what bits of data are in any physical structure. All they can see is the operating system files, not what is within them. It is only you, the database administrator, who is permitted (and required) to see both sides of the story.
The abstraction of logical storage from physical storage is part of the relational database management (RDBMS) standard. If it were possible for a programmer to determine the physical location of a row, then successful execution of the code would be totally dependent on the one environment for which it was written. Changing the platform, moving the datafiles, and even renaming a file would break the application. It is, in fact, possible to determine where a table (and even one row within a table) actually is, but not through standard SQL. The language does not permit it. Tools are supplied for the database administrator’s use for doing this, should it ever be necessary.
Data is stored in datafiles. There is no practical limit to the number or size of datafiles, and the abstraction of logical storage from physical storage means that datafiles can be moved or resized and more datafiles can be added without the application developers being aware of this. The relationship between physical and logical structures is maintained and documented in the data dictionary, which contains metadata describing the whole database. By querying certain views in the data dictionary, the DBA can determine precisely where every part of each table resides.
The data dictionary is a set of tables stored within the database. There is a recursive problem here: The instance needs to be aware of the physical and logical structures of the database, but the information describing this is itself within the database. The solution to this problem lies in the staged startup process, which is detailed in Chapter 13.
A requirement of the RDBMS standard is that the database must not lose data. This means it must be backed up; furthermore, any changes made to data between backups must be captured in a manner such that they can be applied to a restored backup. This is the forward recovery process. Oracle implements the capture of changes through the redo log, which is a sequential record of all change vectors applied to data. A change vector is the alteration made by a Data Manipulation Language (DML) statement (such as INSERT, UPDATE, DELETE, or MERGE, discussed in Chapter 6). Whenever a user session makes any changes, the data in the data block is changed, and the change vector is written out sideways to the redo log, in a form that makes it repeatable. Then, in the event of damage to a datafile, a backup of the file can be restored, and Oracle will extract the relevant change vectors from the redo log and apply them to the data blocks within the file. This ensures that work will never be lost—unless the damage to the database is so extensive as to lose not only one or more datafiles but also either their backups or the redo log.
The controlfile stores the details of the physical structures of the database and is the starting point for the link to the logical structures. When an instance opens a database, it does so by first reading the controlfile. Within the controlfile is information the instance can then use to connect to the rest of the database and the data dictionary within it.
The architecture of a single-instance database, represented graphically in Figure 12-1, can be summarized as consisting of four interacting components:
• A user interacts with a user process.
• A user process interacts with a server process.
• A server process interacts with an instance.
• An instance interacts with a database.
Figure 12-1 The indirect connection between a user and a database
The user process is the user interface software. It could be a simple tool, such as SQL*Plus, or something more complicated, such as Microsoft Access plus the ODBC driver, something written in C, or a Java process running on an application server. Whatever it is, it is the tool with which the user interacts on the client side. It is absolutely impossible for any client-side process to have any contact with the database. The client-server split is between the user process (which generates SQL) and the server process (which executes it).
Distributed Systems Architectures
In the single-instance environment, one instance opens one database. In a distributed environment, there are various possibilities for grouping instances and databases.
• Real Application Clusters (RAC) Multiple instances open one database.
• Streams or GoldenGate Multiple Oracle servers propagate transactions between each other.
• Data Guard A primary database updates one or more standby databases to keep them all synchronized.
Combinations of these options can deliver a distributed system that can achieve the goal of 100 percent uptime and zero data loss, with limitless scalability and performance.
Exercise 12-1: Identify the Components of the Database Server In this exercise you will run queries to determine whether the database is a self-contained system or is part of a larger distributed environment, and you will also identify the major components.
Connect to the database using SQL*Plus as user SYSTEM and then follow these steps:
1. Determine whether the instance is part of a RAC database.
This will return NO for a single-instance database.
2. Determine whether the database is protected against data loss by a Data Guard standby database.
This will return UNPROTECTED if the database is indeed unprotected.
3. Determine whether Streams has been configured in the database.
This will return no rows if Streams has never been configured.
4. Identify the physical structures of the database.
Can you deduce any file and directory naming standards?
5. Identify the memory and process structures.
For a Linux environment, from an operating system prompt use the ipcs command to display shared memory segments and the ps command to show the oracle processes.
Figure 12-2 shows some sample output. ipcs shows two blocks of shared memory owned by user oracle that make up the SGA, and ps shows the background processes of an instance called orcl. Your output will vary depending on how your database instance was named.
Figure 12-2 SGA and processes, as seen in Linux
For a Windows environment, launch Windows Task Manager. Search for an image named oracle.exe and adjust the column display to show the number of threads within the process and the committed memory. The threads are the background processes, and the committed memory is the total of SGA and PGA for the instance.
Explain the Memory Structures
An Oracle instance consists of a block of shared memory (the SGA) and a number of background processes. At a minimum, the SGA will contain three data structures:
• The database buffer cache default pool
• The log buffer
• The shared pool
It may, optionally, also contain the following:
• A large pool
• A Java pool
• Additional buffer cache pools
User sessions also need memory on the server side. This is the nonshareable PGA. Each session will have its own private PGA.
EXAM TIP Which SGA structures are required, and which are optional? The database buffer cache, log buffer, and shared pool are required; the large pool, Java pool, and streams pool are optional.
The Database Buffer Cache
The database buffer cache is Oracle’s work area for executing SQL. When updating data, users’ sessions don’t directly do so on disk. The data blocks containing the data of interest are first copied into the database buffer cache. Changes (such inserting new rows and deleting or modifying existing rows) are applied to these copies of the data blocks in the database buffer cache. The blocks will remain in the cache for some time afterward, until the buffer they are occupying is needed for caching another block.
When querying data, it also goes via the cache. The session works out which blocks contain the rows of interest and copies them into the database buffer cache; the projected columns of relevant rows are then transferred into the session’s PGA for further processing. Again, the blocks remain in the database buffer cache for some time afterward.
Take note of the term block. Datafiles are formatted into fixed-sized blocks. Table rows and other data objects such as index keys are stored in these blocks. The database buffer cache is formatted into memory buffers, each sized to hold one block. Unlike blocks, rows are of variable length; the length of a row will depend on the number of columns defined for the table, whether the columns actually have anything in them, and, if so, what. Depending on the size of the blocks (which is chosen by the DBA) and the size of the rows (which is dependent on the table design and usage), there may be several rows per block, or possibly a row may stretch over several blocks. The structure of a data block will be described in the section “The Datafiles” later in this chapter.
Ideally, all the blocks containing data that is frequently accessed will be in the database buffer cache, thus minimizing the need for disk input/output (I/O). As a typical use of the database buffer cache, consider an end user retrieving an employee record and updating it with these statements:
The user process will have prompted the user for the employee number and constructed the SELECT statement. The SELECT retrieves some details to be sent to the user process, where they will be formatted for display. To execute this statement, the session’s server process will read the data block containing the relevant row from a datafile into a buffer. The user process will then initiate a screen dialog to prompt for some change to be made and verified; then the UPDATE statement and the COMMIT statement will be constructed and sent to the server process for execution. Provided that an excessive period of time has not elapsed, the block with the row will still be available in the cache when the UPDATE statement is executed. In this example, the buffer cache hit ratio will be 50 percent: two accesses of a block in the cache but only one read of the block from disk. A well-tuned database buffer cache can result in a cache hit ratio well over 90 percent.
A buffer storing a block whose image in the cache is not the same as the image on disk is often referred to as a dirty buffer. A buffer will be “clean” when a block is first copied into it. At that point, the block image in the buffer is the same as the block image on disk. The buffer will become dirty when the block in it is updated. Eventually, dirty buffers must be written back to the datafiles, at which point the buffer will be clean again. Even after being written to disk, the block remains in memory; it is possible that the buffer will not be overwritten with another block for some time.
Note that there is no correlation between the frequency of updates to a buffer (or the number of COMMITs) and when it gets written back to the datafiles.
EXAM TIP Changed blocks may be written to datafiles before or after COMMIT. In the course of normal running, it is not unusual for uncommitted data to be written to the datafiles or for committed changes not to have been written.
The size of the database buffer cache is critical for performance. The cache should be sized adequately for caching all the frequently accessed blocks (whether clean or dirty) but not so large that it caches blocks that are rarely needed. An undersized cache will result in excessive disk activity, as frequently accessed blocks are continually read from disk, used, overwritten by other blocks, and then read from disk again. An oversized cache is not so bad (so long as it is not so large that the operating system has to swap pages of virtual memory in and out of real memory) but can cause problems; for example, the startup of an instance is slower if it involves formatting a massive database buffer cache.
EXAM TIP The size of the database buffer cache can be adjusted dynamically, and it can be automatically managed.
The database buffer cache is allocated at instance startup time and can be resized up or down at any time. This resizing can be either manual or automatic, according to the workload, if the automatic mechanism has been enabled.
The Log Buffer
The log buffer is a small, short-term staging area for change vectors before they are written to the redo log on disk. A change vector is a modification applied to something; executing DML statements generates change vectors applied to data. The redo log is the database’s guarantee that data will never be lost. Whenever a data block is changed, the change vectors applied to the block are written out to the redo log, from where they can be extracted and applied to datafile backups if it is ever necessary to restore a datafile. Thus, the datafile can be brought up to date.
Redo is not written directly to the redo log files by session server processes. If it were, the sessions would have to wait for disk I/O operations to complete whenever they executed a DML statement. Instead, sessions write redo to the log buffer in memory. This is much faster than writing to disk. The log buffer (which will contain change vectors from many sessions, interleaved with each other) is then written out to the redo log files. One write of the log buffer to disk may therefore be a batch of many change vectors from many transactions. Even so, the change vectors in the log buffer are written to disk in very nearly real time—and when a session issues a COMMIT statement, the log buffer write really does happen in real time. The writes are done by the log writer background process, the LGWR.
EXAM TIP What does LGWR do when you COMMIT? It flushes the entire log buffer to disk. What does the DBWn do on COMMIT? Nothing.
The log buffer is small (in comparison with other memory structures) because it is a short-term storage area. Change vectors are inserted into it and are streamed to disk in near real time. There is no need for it to be more than a few megabytes at the most, and indeed making it much bigger than the default value can be seriously bad for performance. The default is determined by the Oracle server and is based on the number of CPUs on the server node. The default is usually correct.
Understanding COMMIT processing is vital. When a COMMIT statement is issued, part of the commit processing involves writing the contents of the log buffer to the redo log files on disk. This write occurs in real time, and while it is in progress, the session that issued the COMMIT will hang. The guarantee that a committed transaction will never be lost is based on that the commit-complete message is not returned to the session until the data blocks in the cache have been changed (which means that the transaction has been completed) and the change vectors have been written to the redo log on disk (and therefore the transaction could be recovered, if necessary).
The log buffer is allocated at instance startup, and it can never be resized subsequently without restarting the instance. It is a circular buffer. As server processes write change vectors to it, the current write address moves around. The log writer process writes the vectors out in batches, and as it does so, the space they occupied becomes available and can be overwritten by more change vectors. It is possible that at times of peak activity, change vectors will be generated faster than the log writer process can write them out. If this happens, all DML activity will cease (for a few milliseconds) while the log writer clears the buffer.
The process of flushing the log buffer to disk is one of the ultimate bottlenecks in the Oracle architecture. You cannot do DML faster than the LGWR can flush the change vectors to the online redo log files.
EXAM TIP The size of the log buffer is static, fixed at instance startup. It cannot be automatically managed.
The Shared Pool
The shared pool is the most complex of the SGA structures. It is divided into hundreds of substructures, all of which are managed internally by the Oracle server. This discussion of architecture will mention only three of the shared pool components briefly:
• The library cache
• The data dictionary cache
• The SQL query and PL/SQL function result cache
Some other structures will be described in later chapters. All the structures within the shared pool are automatically managed. Their size will vary according to the pattern of activity against the instance, within the overall size of the shared pool. The shared pool itself can be resized dynamically, either in response to the DBA’s instructions or through being managed automatically.
The Library Cache
The library cache is a memory area for storing recently executed code in its parsed form. The library cache caches all code: SQL, PL/SQL, and Java. Parsing is the conversion of code written by programmers into something executable, and it is a process that Oracle does on demand. Because parsed code is cached in the shared pool so that it can be reused without reparsing, performance can be greatly improved. Parsing SQL code takes time. Consider this simple SQL statement:
Before this statement can be executed, the Oracle server has to work out what it means and how to execute it. To begin with, what is employees? Is it a table, a synonym, or a view? Does it even exist? Then there is the asterisk (*). What are the columns that make up the EMPLOYEES table (if it is a table)? Does the user have permission to see the table? Answers to these questions and many others have to be found by querying the data dictionary.
TIP The algorithm used to find SQL in the library cache is based on the American Standard Code for Information Interchange (ASCII) values of the characters that make up the statement. The slightest difference (even something as trivial as SELECT instead of select) means that the statement will not match and will therefore be parsed again. Code that is not reusable is bad for performance.
Having worked out what the statement actually means, the server has to decide how best to execute it. Is there an index on the last_name column? If so, would it be quicker to use the index to locate the row or to scan the whole table? More queries against the data dictionary are needed. It is quite possible for a simple one-line query against a user table to generate dozens of queries against the data dictionary and for the parsing of a statement to take many times longer than eventually executing it. The purpose of the library cache of the shared pool is to store statements in their parsed form, ready for execution. The first time a statement is issued, it has to be parsed before execution; the second time, it can be executed immediately. In a well-designed application, it is possible that statements may be parsed once and executed millions of times. This saves a huge amount of time.
The Data Dictionary Cache
The data dictionary cache is sometimes referred to as the row cache. Whichever term you prefer, it stores recently used object definitions, including descriptions of tables, indexes, users, and other metadata definitions. Keeping such definitions in memory in the SGA, where they are immediately accessible to all sessions rather than each session having to read them repeatedly from the data dictionary on disk, enhances parsing performance. The cached object definitions can be used to parse many different statements.
The data dictionary cache stores object definitions so that when statements do have to be parsed, they can be parsed fast—without having to query the data dictionary. Consider what happens if these statements are issued consecutively:
Both statements must be parsed because they are different statements, but parsing the first SELECT statement will have loaded the definition of the EMPLOYEES table and its columns into the data dictionary cache, so parsing the second statement will be faster than it would otherwise have been because no data dictionary access will be needed.
TIP Shared pool tuning is usually oriented toward making sure that the library cache is the right size. This is because the algorithms Oracle uses to allocate memory in the SGA are designed to favor the dictionary cache, so if the library cache is correct, the dictionary cache will already be correct.
The SQL Query and PL/SQL Function Result Cache
In many applications, the same query is executed many times, by either the same session or many different sessions. Creating a result cache lets the Oracle server store the results of such queries in memory. The next time the query is issued, rather than running the query, the server can retrieve the cached result. The result cache mechanism is intelligent enough to track whether the tables against which the query was run have been updated. If this has happened, the query results will be invalidated, and the next time the query is issued, it will be rerun. There is therefore no danger of ever receiving an out-of-date cached result.
The PL/SQL result cache uses a similar mechanism. When a PL/SQL function is executed, its return value can be cached, ready for the next time the function is executed. If the parameters passed to the function or the tables that the function queries are different, the function will be reevaluated, but otherwise the cached value will be returned.
By default, use of the SQL query and PL/SQL function result cache is disabled, but if enabled programmatically, it can often dramatically improve performance. The cache is within the shared pool; unlike the other memory areas described previously, it does afford DBAs some control in that they can specify a maximum size.
Sizing the Shared Pool
Sizing the shared pool is critical for performance. It should be large enough to cache all the frequently executed code and frequently needed object definitions (in the library cache and the data dictionary cache) but not so large that it caches statements that have been executed only once. An undersized shared pool cripples performance because server sessions have repeatedly had to grab space in it for parsing statements, which are then overwritten by other statements and therefore have to be parsed again when they are reexecuted. An oversized shared pool can impact badly on performance because it takes too long to search it. If the shared pool is less than the optimal size, performance will degrade. However, there is a minimum size below which statements will fail.
Memory in the shared pool is allocated according to a least recently used (LRU) algorithm. When the Oracle server needs space in the shared pool, it will overwrite the object that has not been used for the longest time. If the object is later needed again, it will have to be reloaded—possibly overwriting another object.
The shared pool is allocated at instance startup time. Prior to release 9i of the database, it was not possible to resize the shared pool subsequently without restarting the database instance, but from 9i onward it can be resized up or down at any time. This resizing can be either manual or (from release 10g onward) automatic, according to workload, if the automatic mechanism has been enabled.
The Large Pool
The large pool is an optional area that, if created, will be used automatically by various processes that would otherwise take memory from the shared pool. One major use of the large pool is by shared server processes, described in Chapter 14 in the discussion of the shared (or multithreaded) server architecture. Parallel execution servers will also use the large pool, if there is one. In the absence of a large pool, these processes will use memory from the shared pool. This can cause bad contention for the shared pool. If shared servers or parallel servers are being used, a large pool should always be created. Some I/O processes may also make use of the large pool, such as the processes used by the Recovery Manager when it is backing up to a tape device.
The Java Pool
The Java pool is required only if your application is going to run Java stored procedures within the database. It is used for the heap space needed to instantiate the Java objects. However, a number of Oracle options are written in Java, so the Java pool is considered standard nowadays. Note that Java code is not cached in the Java pool; it is cached in the shared pool, in the same way that PL/SQL code is cached.
The Streams Pool
Oracle Streams uses the streams pool, which is an advanced tool that is beyond the scope of the OCP examinations or this book, but for completeness a short description follows.
The mechanism used by Streams involves extracting change vectors from the redo log and from these reconstructing the statements that were executed—or statements that would have the same effect. These statements are executed at the remote database. The processes that extract changes from redo and the processes that apply the changes need memory; this memory is the streams pool. From database release 10g onward, it is possible to create and resize the streams pool after instance startup; this creation and sizing can be completely automatic. With earlier releases, it had to be defined at startup and was a fixed size.
EXAM TIP The size of the shared, large, Java, and streams pools is dynamic and can be automatically managed.
Exercise 12-2: Investigate the Memory Structures of the Instance In this exercise, you will run queries to determine the current sizing of various memory structures that make up the instance. Here are the steps:
1. Connect to the database as user SYSTEM.
2. Use the SHOW SGA command to display summarized information.
3. Show the current, maximum, and minimum sizes of the SGA components that can be dynamically resized, like so:
Figure 12-3 shows the results from a demonstration database. Note that the buffer cache is 150MB, consisting of a 134MB default pool plus a 16MB shared I/O pool (this last component is a section of the cache used for LOBs), and that no components have been resized since startup.
Figure 12-3 Example of SGA configuration
4. Determine how much memory has been, and is currently, allocated to PGAs.
Describe the Background Processes
The instance background processes are the processes that are launched when the instance is started and run until it is terminated. Five background processes have a long history with Oracle and are the first five described in the sections that follow: System Monitor (SMON), Process Monitor (PMON), Database Writer (DBWn), Log Writer (LGWR), and Checkpoint Process (CKPT). A number of others have been introduced with the more recent releases; notable among these are Manageability Monitor (MMON) and Memory Manager (MMAN). There are also some that are not essential but will exist in most instances. These include the Archiver (ARCn) and Recoverer (RECO) processes. Others will exist only if certain options have been enabled. This last group includes the processes required for RAC and Streams. Additionally, some processes exist that are not properly documented (or are not documented at all). The processes described here are those that every OCP candidate will be expected to know.
First, though, a platform variation must be cleared up before discussing processes. On Linux and Unix, all the Oracle processes are separate operating system processes, each with a unique process number. On Windows, there is one operating system process (called ORACLE.EXE) for the whole instance, and the Oracle processes run as separate threads within this one process.
TIP In release 12c, it is in fact possible to run Oracle in a multithreaded model (rather than the default multiprocess model) as it runs on Windows. This is enabled with the THREADED_EXECUTION instance parameter, which is beyond the scope of the OCP exam.
SMON, the System Monitor
SMON initially has the task of mounting and opening a database. The steps involved in this are described in detail in Chapter 13. In brief, SMON mounts a database by locating and validating the database controlfile. It then opens a database by locating and validating all the datafiles and online log files. Once the database is opened and in use, SMON is responsible for various housekeeping tasks, such as collating free space in datafiles.
PMON, the Process Monitor
A user session is a user process that is connected to a server process. The server process is launched when the session is created and destroyed when the session ends. An orderly exit from a session involves the user logging off. When this occurs, any work they were doing will be completed in an orderly fashion, and their server process will be terminated. If the session is terminated in a disorderly manner (perhaps because the user’s PC is rebooted), the session will be left in a state that must be cleared up. PMON monitors all the server processes and detects any problems with the sessions. If a session has terminated abnormally, PMON will destroy the server process, return its PGA memory to the operating system’s free memory pool, and roll back any incomplete transaction that may have been in progress.
EXAM TIP If a session terminates abnormally, what will happen to an active transaction? It will be rolled back by the PMON background process.
DBWn, the Database Writer
Always remember that sessions do not as a general rule write to disk. They write data (or changes to existing data) to buffers in the database buffer cache. It is the database writer that subsequently writes the buffers to disk. It is possible for an instance to have several database writers (up to a maximum of 100) that will be called DBW0 to DBW9 and then DBWa to DBWz; that’s why DBWn refers to “the” database writer. Writers beyond 36 are named BW36 to BW99. The default number is one database writer per eight CPUs, rounded up.
TIP How many database writers do you need? The default number may well be correct. Adding more may help performance, but usually you should look at tuning memory first. As a rule, before you optimize disk I/O, ask why there is any need for disk I/O.
DBWn writes dirty buffers from the database buffer cache to the datafiles, but it does not write the buffers as they become dirty. On the contrary—it writes as few buffers as it can get away with. The general idea is that disk I/O is bad for performance, so don’t do it unless it really is needed. If a block in a buffer has been written to by a session, there is a reasonable possibility that it will be written to again—by that session or a different one. Why write the buffer to disk if it may well be dirtied again in the near future? The algorithm DBWn uses to select dirty buffers for writing to disk (which will clean them) will select only buffers that have not been recently used. So if a buffer is very busy because sessions are repeatedly reading or writing to it, DBWn will not write it to disk for some time. There could be hundreds or thousands of writes to a buffer before DBWn cleans it. It could be that in a buffer cache of a million buffers, a hundred thousand of them are dirty, but DBWn might write only a few hundred of them to disk at a time. These will be the few hundred that no session has been interested in for some time.
EXAM TIP What will cause DBWn to write? The answer is no free buffers, too many dirty buffers, a three-second timeout, or a checkpoint.
DBWn writes according to a very lazy algorithm: as little as possible and as rarely as possible. Four circumstances will cause DBWn to write: no free buffers, too many dirty buffers, a three-second timeout, and when there is a checkpoint.
The first circumstance is when there are no free buffers. If a server process needs to copy a block into the database buffer cache, it must find a free buffer, which is a buffer that is neither dirty (updated and not yet written back to disk) nor pinned (a pinned buffer is one that is being used by another session at that moment). A dirty buffer must not be overwritten because if it were, the changed data would be lost, and a pinned buffer cannot be overwritten because the operating system’s memory protection mechanisms will not permit this. If a server process takes “too long” (as determined by Oracle internally) to find a free buffer, it signals DBWn to write some dirty buffers to disk. Once this is done, they will be clean—and thus free and available for use.
The second circumstance is when there are too many dirty buffers (“too many” being another internal threshold). No one server process may have had a problem finding a free buffer, but overall, there could be a large number of dirty buffers, which will cause DBWn to write some of them to disk.
The third circumstance is the three-second timeout. Every three seconds, DBWn will clean a few buffers. In practice, this event may not be significant in a production system because the two previously described circumstances will be forcing the writes, but the timeout does mean that even if the system is idle, the database buffer cache will eventually be cleaned.
Fourth, there may be a checkpoint requested. The three reasons already given will cause DBWn to write a limited number of dirty buffers to the datafiles. When a checkpoint occurs, all dirty buffers are written.
Writing buffers for the first three reasons mentioned is referred to as an incremental checkpoint or as advancing the incremental checkpoint position. This is all that should happen in the course of normal running and is optimized such that buffers will be made available as needed without impacting performance by stressing the I/O system.
The only moment when a full checkpoint is absolutely necessary is when the database is closed and the instance is shut down (a full description of this sequence is given in Chapter 13). A checkpoint writes all dirty buffers to disk; this synchronizes the buffer cache with the datafiles and synchronizes the instance with the database. During normal running, the datafiles are always out of date. They may be missing changes (committed and uncommitted). This does not matter because the copies of blocks in the buffer cache are up to date, and it is these that the sessions work on. But on shutdown, it is necessary to write everything to disk. Automatic checkpoints occur only on shutdown, but a checkpoint can be forced at any time with this statement:
The checkpoint described so far is a full checkpoint. Partial checkpoints that force DBWn to write all the dirty buffers containing blocks from just one or more datafiles, rather than the whole database, occur more frequently, for example, when a datafile or tablespace is taken offline, when a tablespace is put into backup mode, or when a tablespace is made read-only. These are less drastic than full checkpoints and occur automatically whenever the relevant event happens.
EXAM TIP Is there a full checkpoint on log switch? No, there hasn’t been since release 8i. Many DBAs, including some who should know better, have never learned this change in behavior that happened many years ago.
To conclude, DBWn writes on a very lazy algorithm—as little as possible and as rarely as possible—except when a full checkpoint occurs. Then, all dirty buffers are written to disk as fast as possible.
LGWR, the Log Writer
LGWR writes the contents of the log buffer to the online log files on disk. A write of the log buffer to the online redo log files is often referred to as flushing the log buffer.
When a session makes any change (by executing INSERT, UPDATE, or DELETE commands) to blocks in the database buffer cache, before it applies the change to the block it writes out the change vector that it is about to apply to the log buffer. So that no work is lost, these change vectors must be written to disk with only minimal delay. To this end, the LGWR streams the contents of the log buffer to the online redo log files on disk in near real time. And when a session issues a COMMIT, the LGWR writes in real time; the session hangs while LGWR writes the buffer to disk. Only then is the transaction recorded as committed and therefore nonreversible.
Three circumstances will cause LGWR to flush the log buffer: if a session issues a COMMIT, if the log buffer is one-third full, and if DBWn is about to write dirty buffers.
The first circumstance is the write-on-commit. To process a COMMIT, the server process inserts a commit record into the log buffer. It will then hang while LGWR flushes the log buffer to disk. Only when this write has completed is a commit-complete message returned to the session, and the server process can then continue working. This is the guarantee that transactions will never be lost. Every change vector for a committed transaction will be available in the redo log on disk and can therefore be applied to datafile backups. Therefore, if the database is ever damaged, it can be restored from backup, and all work done since the backup was made can be redone.
Second, when the log buffer is one-third full, LGWR will flush it to disk. This is about performance. If the log buffer is small (as it usually should be), this one-third-full trigger will force LGWR to write the buffer to disk in near real time, even if no one is committing transactions. The log buffer for many applications will be optimally sized at only a few megabytes. The application will generate enough redo to fill one-third of this in a fraction of a second, so LGWR will be forced to stream the change vectors to disk continuously, in near real time. Then, when a session does COMMIT, there will be hardly anything to write, so the COMMIT will complete almost instantaneously.
Third, when DBWn needs to write dirty buffers from the database buffer cache to the datafiles, it will signal LGWR to flush the log buffer to the online redo log files. This is to ensure that it will always be possible to reverse an uncommitted transaction. The mechanism of transaction rollback is fully explained in Chapter 6. For now, you just need to know that it is perfectly possible for DBWn to write an uncommitted transaction to the datafiles. This is fine, so long as the undo data needed to reverse the transaction is guaranteed to be available. Generating undo data also generates change vectors, and because these will be in the redo log files before the datafiles are updated, the undo data needed to roll back a transaction (should this be necessary) can be reconstructed if needed.
Note that it can be said that there is a three-second timeout that causes LGWR to write. In fact, the timeout is on DBWn. However, because LGWR will always write just before DBWn, in effect there is a three-second timeout on LGWR as well.
EXAM TIP When will LGWR flush the log buffer to disk? It happens on COMMIT, when the buffer is one-third full and just before DBWn writes.
CKPT, the Checkpoint Process
The CKPT keeps track of where in the redo stream the incremental checkpoint position is and, if necessary, instructs DBWn to write out some dirty buffers in order to push the checkpoint position forward. The current checkpoint position is the point in the redo stream at which recovery must begin in the event of an instance crash. CKPT continually updates the controlfile with the current checkpoint position.
MMON, the Manageability Monitor
MMON is the enabling process for many of the self-monitoring and self-tuning capabilities of the database. The database instance gathers a vast number of statistics about activity and performance. These statistics are accumulated in the SGA, and their current values can be interrogated by issuing SQL queries against various V$ views. For performance tuning and also for trend analysis and historical reporting, it is necessary to save these statistics to long-term storage. MMON regularly (by default, every hour) captures statistics from the SGA and writes them to the data dictionary, where they can be stored indefinitely (though, by default, they are kept for only eight days).
Every time MMON gathers a set of statistics (known as a snapshot), it also launches the Automatic Database Diagnostic Monitor (ADDM). The ADDM is a tool that analyzes database activity using an expert system developed over many years by many DBAs. It studies two snapshots (by default, the current and previous snapshots) and makes observations and recommendations regarding performance during the period covered. Chapter 18 describes the use of ADDM (and other tools) for performance tuning. As well as gathering snapshots, MMON continuously monitors the database and the instance to check whether any alerts should be raised.
EXAM TIP By default, MMON gathers a snapshot and launches the ADDM every hour.
MMNL, the Manageability Monitor Light
MMNL is a process that assists the MMON. There are times when MMON’s scheduled activity is not enough. For example, MMON flushes statistical information accumulated in the SGA to the database according to a schedule (by default, every hour). If the memory buffers used to accumulate this information fill before MMON is due to flush them, MMNL will take responsibility for flushing the data.
MMAN, the Memory Manager
MMAN can completely automate memory management. All the DBA needs to do is set an overall target for memory usage, and MMAN will observe the demand for PGA memory and SGA memory and then allocate memory to sessions and to SGA structures, as needed, while keeping the total allocated memory within a limit set by the DBA.
TIP The automation of memory management is one of the major technical advances of the later releases, automating a large part of the DBA’s job and giving huge benefits in performance and resource utilization. MMAN does it better than you can.
LREG, the Listener Registration Process
A database instance will attempt to register itself with a database listener. This is to allow users to connect via the listener. In an advanced environment such as a clustered database with several instances offering many services, LREG will also update the listener with information regarding workload and performance. This allows the listener to direct sessions intelligently to appropriate instances. In earlier releases, this function was performed by the PMON process, but in release 12c a dedicated process (namely, LREG) has been added to do this.
ARCn, the Archiver
This is an optional process as far as the database is concerned, but it is usually a required process for the business. Without one or more ARCn processes (there can be up to 30, named ARC0, ARC1, and so on), it is possible to lose data. The process and purpose of launching ARCn to create archive log files is described in detail in Chapter 22. For now, only a summary is needed.
All change vectors applied to data blocks are written out to the log buffer (by the sessions making the changes) and then to the online redo log files (by the LGWR). The online redo log files are of fixed size and number. Once they have been filled, LGWR will overwrite them with more redo data. The time that must elapse before this happens is dependent on the size and number of the online log files and the amount of DML activity (and therefore the amount of redo generated) against the database. This means that the online redo log stores change vectors only for recent activity. To preserve a complete history of all changes applied to the data, the online log files must be copied as they are filled and before they are reused. The ARCn is responsible for doing this. Provided that these copies, known as archive redo log files, are available, it will always be possible to recover from any damage to the database by restoring datafile backups and applying change vectors to them extracted from all the archive log files generated since the backups were made.
EXAM TIP LGWR writes the online log files; ARCn reads them. During normal running, no other processes touch them at all.
TIP The progress of the ARCn processes and the state of the destinations to which they are writing must be monitored. If archiving fails, the database will eventually hang. This monitoring can be done through the alert system.
RECO, the Recoverer Process
A distributed transaction is a transaction that involves updates to two or more databases. Distributed transactions are designed by programmers and operate through database links. Consider this example:
The first update applies to a row in the local database; the second applies to a row in a remote database identified by the database link DEV. The COMMIT command instructs both databases to commit the transaction, which consists of both statements. Distributed transactions require a two-phase commit. The commit in each database must be coordinated; if one were to fail and the other were to succeed, the data overall would be in an inconsistent state. A two-phase commit prepares each database by instructing their LGWRs to flush the log buffer to disk (the first phase), and once this is confirmed, the transaction is flagged as committed everywhere (the second phase). If anything goes wrong anywhere between the two phases, RECO takes action to cancel the commit and roll back the work in all databases.
Some Other Background Processes
It is unlikely that processes other than those already described will be examined, but for completeness this section describes the remaining processes usually present in an instance. Figure 12-4 shows a query that lists all the processes running in an instance on a Linux system. Many more processes may exist, depending on certain options being enabled, but those shown in the figure will be present in most instances.
Figure 12-4 The background processes typically present in a single instance
Exercise 12-3: Investigate the Processes Running in Your Instance In this exercise, you will run queries to see what background processes are running on your instance. You may use either SQL Developer or SQL*Plus. Here are the steps to follow:
1. Connect to the database as user SYSTEM.
2. Determine what processes are running and how many of each.
These queries will give similar results. Each process must have a session (even the background processes), and each session must have a process. The processes that can occur multiple times will have a numeric suffix, except for the processes supporting user sessions, which will all have the same name.
3. Investigate how many processes could be running.
The v$bgprocess view has one row for every possible process. Processes that are actually running have an address, which is a join column to the v$process view.
4. Observe the launching of server processes as sessions are made by counting the number of server processes (on Linux or any Unix platform) or the number of Oracle threads (on Windows). The technique is different on the two platforms. On Linux/Unix, the Oracle processes are separate operating system processes, but on Windows they are threads within one operating system process.
On Linux, run this command from an operating system prompt:
This will count the number of processes running that have the string LOCAL in their name. This will include all the session server processes.
Launch a SQL*Plus session and then rerun the preceding command. Use the host command to launch an operating shell from within the SQL*Plus session. You will see that the number of processes has increased. Exit the session, and you will see that the number has dropped again. Figure 12-5 demonstrates this.
Figure 12-5 Counting session server processes
Observe in the figure how the number of processes changes from 3 to 4 and back again; the difference is the launching and terminating of the server process supporting the SQL*Plus session.
On Windows, launch Task Manager. Configure it to show the number of threads within each process by following these steps: In the View menu, select the Select Columns option and select the Thread Count check box. Look for the ORACLE.EXE process, and note the number of threads. In Figure 12-6, this is currently at 33.
Figure 12-6 Displaying the thread count within the Oracle executable image
Launch a new session against the instance, and you will see the thread count increment. Exit the session, and it will decrement.
Explain the Relationship Between Logical and Physical Storage Structures
The Oracle database provides complete abstraction of the logical storage from the physical. The logical data storage is in segments. There are various segment types; a typical segment is a table. The segments are stored physically in datafiles. The abstraction of the logical storage from the physical storage is accomplished through tablespaces. The relationships between the logical and physical structures, as well as their definitions, are maintained in the data dictionary.
You can find a full treatment of database storage, both logical and physical, in Chapter 15.
The Physical Database Structures
Three file types make up an Oracle database, plus a few others that exist externally to the database and are, strictly speaking, optional. The required files are the controlfile, the online redo log files, and the datafiles. The external files that will usually be present (there are others, needed for advanced options) are the initialization parameter file, the password file, the archive redo log files, and the log and trace files.
EXAM TIP What three files types must be present in a database? The controlfile, the online redo log files, and any number of datafiles.
The Controlfile
First a point of terminology: Some DBAs will say that a database can have multiple controlfiles, whereas others will say that it has one controlfile, of which there may be multiple copies. This book will follow the latter terminology. The Oracle documentation is inconsistent.
The controlfile is small but vital. It contains pointers to the rest of the database, including the locations of the online redo log files, the datafiles, and more recently the archive log files (if the database is in archive log mode). It also stores information required to maintain database integrity such as various critical sequence numbers and timestamps, for example. If the Recovery Manager tool is being used for backups, the details of these backups will also be stored in the controlfile. The controlfile will usually be no more than a few megabytes in size, but you can’t survive without it.
Every database has one controlfile, but a good DBA will always create multiple copies of the controlfile so that if one copy is damaged, the database will survive. If all copies of the controlfile are lost, it is possible (though perhaps awkward) to recover, but you should never find yourself in that situation. You don’t have to worry about keeping multiplexed copies of the controlfile synchronized—Oracle will take care of that. Its maintenance is automatic; your only control is how many copies to have and where to put them.
If you get the number of copies or their location wrong at database creation time, you can add or remove copies later or move them around. However, you should bear in mind that any such operations will require downtime, so it is a good idea to get it right at the beginning. There is no right or wrong answer when determining how many copies to have. The minimum is one; the maximum possible is eight. All organizations should have a DBA standards handbook that states something like “All production databases will have three copies of the controlfile, on three separate devices” (three being a number picked for illustration purposes only but a number with which many organizations are happy). If no such guidelines are in place, someone should write them (and perhaps the “someone” should be you). There is no rule that says two copies are too few or seven copies are too many; there are only corporate standards, and the DBA’s job is to ensure that the databases conform to these standards.
Damage to any controlfile copy will cause the database instance to terminate immediately. There is no way to avoid this. Oracle Corporation does not permit operating a database with less than the number of controlfiles requested.
The Online Redo Log Files
The redo log stores a continuous chain in chronological order of every change vector applied to the database. This will be the bare minimum of information required to reconstruct, or redo, all the work that has been done. If a datafile (or the whole database) is damaged or destroyed, these change vectors can be applied to datafile backups to redo the work, bringing them forward in time until the moment that the damage occurred. The redo log consists of two file types: the online redo log files (which are required) and the archive log files (which are optional).
Every database has at least two online redo log files, but like with the controlfile, a good DBA creates multiple copies of each online redo log file. The online redo log consists of groups of online redo log files, each file being known as a member. An Oracle database requires at least two groups of at least one member each to function. You may create more than two groups for performance reasons and more than one member per group for security (as the old joke goes, “This isn’t just data security; it is job security”). The requirement for a minimum of two groups is so that one group can be accepting the current changes while the other group is being backed up (or archived, to use the correct term).
EXAM TIP Every database must have at least two online redo log file groups to function. Each group should have at least two members for safety.
One of the groups is the current group. Changes are written to the current online redo log file group by LGWR. As user sessions update data in the database buffer cache, they also write out the minimal change vectors to the redo log buffer. LGWR continually flushes this buffer to the files that make up the current online redo log file group. Log files are fixed size; therefore, eventually the files making up the current group will fill. LGWR will then perform what is called a log switch, which makes the second group current and starts writing to that. If your database is configured appropriately, the ARCn process(es) will then archive (in effect, back up) the log file members making up the first group. When the second group fills, LGWR will switch back to the first group, making it current and overwriting it; ARCn will then archive the second group. Thus, the online redo log file groups (and therefore the members making them up) are used in a circular fashion, and each log switch will generate an archive redo log file.
As with the controlfile, if you have multiple members per group (and you should!), you don’t have to worry about keeping them synchronized. LGWR will ensure that it writes to all of them, in parallel, thus keeping them identical. If you lose one member of a group, as long as you have a surviving member, the database will continue to function.
The size and number of your log file groups are a matter of tuning. In general, you will choose a size appropriate to the amount of activity you anticipate. The minimum size is 50MB, but some active databases will need to raise this to several gigabytes if they are not to fill every few minutes. A busy database can generate megabytes of redo a second, whereas a largely static database may generate only a few megabytes an hour. The number of members per group will be dependent on what level of fault tolerance is deemed appropriate and is a matter to be documented in corporate standards. However, you don’t have to worry about this at database creation time. You can move your online redo log files around, add or drop them, and create ones of different sizes as you please at any time later. Such operations are performed “online” and don’t require downtime; they are therefore transparent to the end users.
The Datafiles
The third required file type making up a database is the datafile. At a minimum, you must have three datafiles (all to be created at database creation time), one each for the SYSTEM tablespace (which stores the data dictionary), the SYSAUX tablespace (which stores data that is auxiliary to the data dictionary), and the UNDO tablespace (which stores the undo segments required to protect transactions). You will have many more than that when your database goes live and will often create a few more to begin with.
Datafiles are the repository for data. Their size and numbers are effectively unlimited. A small database might have just half a dozen datafiles of only a few hundred megabytes each. A larger database could have thousands of datafiles, whose size is limited only by the capabilities of the host operating system and hardware.
The datafiles are the physical structures visible to the system administrators. Logically, they are the repository for the segments containing user data that the programmers see and also for the segments that make up the data dictionary. A segment is a storage structure for data; typical segments are tables and indexes. Datafiles can be renamed, resized, moved, added, or dropped at any time in the lifetime of the database, but remember that some operations on some datafiles may require downtime.
At the operating system level, a datafile consists of a number of operating system blocks. Internally, datafiles are formatted into Oracle blocks. These blocks are consecutively numbered within each datafile. The block size is fixed when the datafile is created, and in most circumstances it will be the same throughout the entire database. The block size is a matter for tuning and can range (with limits, depending on the platform) from 2KB up to 32KB. There is no necessary relationship between the Oracle block size and the operating system block size.
Figure 12-7 shows the Oracle storage model in the form of an entity-relationship diagram. The left column shows the logical structures next to the physical structures. For completeness, the diagram also shows the ASM entities in the two rightmost columns, which are covered in Chapter 3. These are an alternative to the file system storage discussed here.
Figure 12-7 The Oracle storage model
TIP Many DBAs like to match the operating system block size to the Oracle block size. For performance reasons, the operating system blocks should never be larger than the Oracle blocks, but there is no reason not to have them smaller. For instance, having a 1KB operating block size and an 8KB Oracle block size is perfectly acceptable.
Within a block is a header section, a data area, and possibly some free space. The header section contains information such as the row directory, which lists the location within the data area of the rows in the block (if the block is being used for a table segment) and also row locking information if there is a transaction (or several concurrent transactions) working on the rows in the block. The data area contains the data itself, such as rows if it is part of a table segment, or index keys if the block is part of an index segment.
EXAM TIP Server processes read from the datafiles; DBWn writes to datafiles.
Other Database Files
These files exist outside the database. They are necessary for practical purposes, but are not, strictly speaking, part of the database.
• Instance parameter file When an Oracle instance is started, the SGA structures build in memory, and the background processes start according to settings in the parameter file. This is the only file that needs to exist to start an instance. There are several hundred parameters, but only one is required: the DB_NAME parameter. All others have defaults. Therefore, the parameter file can be quite small, but it must exist.
• Password file Users establish sessions by presenting a username and password. The Oracle server authenticates these against user definitions stored in the data dictionary. The data dictionary is a set of tables in the database; it is therefore inaccessible if the database is not open. There are occasions when a user needs to be authenticated before the data dictionary is available, such as when they need to start the database or indeed to create it. An external password file is one means of doing this. It contains a small number of (typically less than half a dozen) usernames and passwords that exist outside the data dictionary that can be used to connect to an instance before the data dictionary is available.
• Archive redo log files When an online redo log file fills, the ARCn process copies it out of the database to an archive redo log file. Once this is done, the archive log is no longer part of the database. It is, however, essential if it’s ever necessary to restore a datafile backup, and Oracle does provide facilities for managing the archive redo log files.
• Alert log and trace files The alert log is a continuous stream of messages regarding certain critical operations affecting the instance and the database. Not everything is logged; only events that are considered to be really important (such as startup and shutdown), changes to the physical structures of the database, and changes to the parameters that control the instance. Trace files are generated by background processes when they detect error conditions and sometimes to report certain actions.
The Logical Database Structures
The physical structures that make up a database are visible as operating system files to your system administrators. Your users see logical structures such as tables. Oracle uses the term segment to describe any structure that contains data. A typical segment is a table, containing rows of data, but there are more than a dozen possible segment types in an Oracle database. Of particular interest (for examination purposes) are table segments, index segments, and undo segments, all of which are investigated in detail later. For now, you don’t need to know any more than that tables contain rows of information, that indexes are a mechanism for giving fast access to any particular row, and that undo segments are data structures used for storing the information that might be needed to reverse, or roll back, any transactions you do not want to make permanent.
So, system administrators see physical datafiles, and programmers see logical segments. Oracle abstracts the logical storage from the physical storage by means of the tablespace, which is logically a collection of one or more segments and physically a collection of one or more datafiles. Put in terms of relational analysis, there is a many-to-many relationship between segments and datafiles. One table may be cut across many datafiles, and one datafile may contain bits of many tables. By inserting the tablespace entity between the segments and the files, Oracle resolves this many-to-many relationship.
EXAM TIP The SYSAUX tablespace must be created at database creation time. If you do not specify it, one will be created by default.
A segment will consist of a number of blocks. Datafiles are formatted into blocks, and these blocks are assigned to segments as the segments grow. Because managing space one block at a time would be a time-consuming process, blocks are grouped into extents. An extent is a series of Oracle blocks that are consecutively numbered within a datafile, and segments grow by having new extents added to them. These extents need not be adjacent to each other or even in the same datafile; they can come from any datafile that is part of the tablespace within which the segment resides.
Figure 12-7, shown earlier, shows the Oracle data storage hierarchy, with the separation of logical from physical storage. The figure shows the relationships between the storage structures. Logically, a tablespace can contain many segments, each consisting of many extents. Physically, a datafile consists of many operating system blocks assigned by whatever file system the operating system is using. The two parts of the model are connected by the relationships showing that one tablespace can consist of multiple datafiles and at the lowest level that one Oracle block will consist of multiple operating system blocks.
The Data Dictionary
The data dictionary is metadata, which is data about data. It describes the database, both physically and logically, and its contents. User definitions, security information, integrity constraints, and performance monitoring information are all part of the data dictionary. It is stored as a set of segments in the SYSTEM and SYSAUX tablespaces.
In many ways, the segments that make up the data dictionary are segments like any other—just tables and indexes. The critical difference is that the data dictionary tables are generated at database creation time, and you are not allowed to access them directly. There is nothing to stop an inquisitive DBA from investigating the data dictionary directly, but if you do any updates to it, you may cause irreparable damage to your database—and certainly Oracle Corporation will not support you. Creating a data dictionary is part of the database creation process. It is maintained subsequently by Data Definition Language (DDL) commands. When you issue the CREATE TABLE command, you are in fact inserting rows into data dictionary tables, as you are with commands such as CREATE USER and GRANT.
For querying the dictionary, Oracle provides a set of views. The views come in four forms, prefixed CDB_, DBA_, ALL_, or USER_. Any view prefixed USER_ will be populated with rows describing objects owned by the user querying the view. Therefore, no two people will see the same contents. If user SCOTT queries USER_TABLES, he will see information about his tables; if you query USER_TABLES, you will see information about your tables. Any view prefixed ALL_ will be populated with rows describing objects to which you have access. Therefore, ALL_TABLES will contain rows describing your own tables, plus rows describing tables belonging to anyone else you have been given permission to see. Any view prefixed DBA_ will have rows for every object in the database, so DBA_TABLES will have one row for every table in the database, no matter who created it. The CDB views are identical to the DBA views, unless you are working in a multitenant database. These views are created as part of the database creation process, along with a large number of PL/SQL packages that are provided by Oracle to assist database administrators in managing the database and programmers in developing applications. PL/SQL code is also stored in the data dictionary.
EXAM TIP Which view will show you all the tables in the database? The view is DBA_TABLES, not ALL_TABLES.
The relationship between tablespaces and datafiles is maintained in the database controlfile. This lists all the datafiles, stating which tablespace they are part of. Without the controlfile, there is no way an instance can locate the datafiles and then identify those that make up the SYSTEM tablespace. Only when the SYSTEM tablespace has been opened is it possible for the instance to access the data dictionary, at which point it becomes possible to open the database.
Exercise 12-4: Investigate the Storage Structures in Your Database In this exercise, you will create a table segment and then work out where it is physically. Follow these steps:
1. Connect to the database as user SYSTEM.
2. Create a table without nominating a tablespace—it will be created in your default tablespace, with one extent.
3. Identify the tablespace in which the table resides, the size of the extent, the file number the extent is in, and at which block of the file the extent starts.
4. Identify the file by name (substitute the file_id from the previous query when prompted).
5. Work out precisely where in the file the extent is in terms of how many bytes into the file it begins. This requires finding out the tablespace’s block size. Enter the block_id and tablespace_name returned by the query in step 3 when prompted.
Figure 12-8 shows these steps, executed from SQL*Plus.
The figure shows that the table exists in one extent (extent 0) that is 64KB in size. This extent is in the file /u01/app/oracle/oradata/orcl/system01.dbf and begins about 826MB into the file.
Figure 12-8 Determining the physical location of a logical object
Two-Minute Drill
List the Architectural Components of an Oracle Database
• An Oracle server is an instance connected to a database.
• An instance is a block of shared memory and a set of background processes.
• A database is a set of files on disk.
• A user session is a user process connected to a server process.
Explain the Memory Structures
• The instance shared memory is the System Global Area (the SGA).
• A session’s private memory is its Program Global Area (the PGA).
• The SGA consists of a number of substructures, some of which are required (the database buffer cache, the log buffer, and the shared pool) and some of which are optional (the large pool, the Java pool, and the streams pool).
• The SGA structures can be dynamically resized and automatically managed, with the exception of the log buffer.
Describe the Background Processes
• Session server processes are launched on demand when users connect.
• Some background processes are launched at instance startup and persist until shutdown; others start/stop as needed.
• Server processes read from the database; background processes write to the database.
• Some background processes will always be present (in particular SMON, PMON, DBWn, LGWR, CKPT, and MMON); others will run depending on what options have been enabled.
Explain the Relationship Between Logical and Physical Storage Structures
• There are three required file types in a database: the controlfile, the online redo log files, and the datafiles.
• The controlfile stores integrity information and pointers to the rest of the database.
• The online redo logs store recent change vectors applied to the database.
• The datafiles store the data.
• External files include the parameter file, the password file, the archive redo logs, and the log and trace files.
• Logical data storage (segments) is abstracted from physical data storage (datafiles) by tablespaces.
• A tablespace can consist of multiple datafiles.
• Segments consist of multiple extents, which consist of multiple Oracle blocks, which consist of multiple operating system blocks.
• A segment can have extents in several datafiles.
Self Test
1. What statements regarding instance memory and session memory are correct? (Choose all correct answers.)
A. SGA memory is private memory segments; PGA memory is shared memory segments.
B. Sessions can write to the PGA, not the SGA.
C. The SGA is written to by all sessions; a PGA is written by one session.
D. The PGA is allocated at instance startup.
E. The SGA is allocated at instance startup.
2. How do sessions communicate with the database? (Choose the best answer.)
A. Server processes use Oracle Net to connect to the instance.
B. Background processes use Oracle Net to connect to the database.
C. User processes read from the database and write to the instance.
D. Server processes execute SQL received from user processes.
3. What memory structures are a required part of the SGA? (Choose all correct answers.)
A. The database buffer cache
B. The Java pool
C. The large pool
D. The log buffer
E. The Program Global Area
F. The shared pool
G. The streams pool
4. Which SGA memory structures cannot be resized dynamically after instance startup? (Choose all correct answers.)
A. The database buffer cache.
B. The Java pool.
C. The large pool.
D. The log buffer.
E. The shared pool.
F. The streams pool.
G. All SGA structures can be resized dynamically after instance startup.
5. Which SGA memory structures cannot be resized automatically after instance startup? (Choose all correct answers.)
A. The database buffer cache.
B. The Java pool.
C. The large pool.
D. The log buffer.
E. The shared pool.
F. The streams pool.
G. All SGA structures can be resized automatically after instance startup.
6. When a session changes data, where does the change get written? (Choose the best answer.)
A. It gets written to the data block in the cache and the redo log buffer.
B. It gets written to the data block on disk and the current online redo log file.
C. The session writes to the database buffer cache, and the log writer writes to the current online redo log file.
D. Nothing is written until the change is committed.
7. Which of these background processes is optional? (Choose the best answer.)
A. ARCn, the archive process
B. CKPT, the checkpoint process
C. DBWn, the database writer
D. LGWR, the log writer
E. MMON, the manageability monitor
8. What happens when a user issues a COMMIT? (Choose the best answer.)
A. The CKPT process signals a checkpoint.
B. The DBWn process writes the transaction’s changed buffers to the datafiles.
C. The LGWR flushes the log buffer to the online redo log.
D. The ARCn process writes the change vectors to the archive redo log.
9. An Oracle instance can have only one of some processes but several of others. Which of these processes can occur several times? (Choose all correct answers.)
A. The archive process
B. The checkpoint process
C. The database writer process
D. The log writer process
E. The session server process
10. One segment can be spread across many datafiles. How? (Choose the best answer.)
A. By allocating extents with blocks in multiple datafiles
B. By spreading the segment across multiple tablespaces
C. By assigning multiple datafiles to a tablespace
D. By using an Oracle block size that is larger than the operating system block size
11. Which statement is correct regarding the online redo log? (Choose the best answer.)
A. There must be at least one log file group, with at least one member.
B. There must be at least one log file group, with at least two members.
C. There must be at least two log file groups, with at least one member each.
D. There must be at least two log file groups, with at least two members each.
12. Where is the current redo byte address, also known as the incremental checkpoint position, recorded? (Choose the best answer.)
A. In the controlfile
B. In the current online log file group
C. In the header of each datafile
D. In the System Global Area
Self Test Answers
1. 
C, E. The SGA is shared memory, updated by all sessions; PGAs are private to each session. The SGA is allocated at startup time (but it can be modified later).
A, B, and D are incorrect. A is incorrect because it reverses the situation. It is the SGA that exists in shared memory, not the PGA. B is incorrect because sessions write both to their own PGA and to the SGA. D is incorrect because (unlike the SGA) the PGA is allocated only on demand.
2. D. This is the client-server split. User processes generate SQL, and server processes execute SQL. A, B, and C are incorrect. A and B are incorrect because they get the use of Oracle Net incorrect. Oracle Net is the protocol between a user process and a server process. C is incorrect because it describes what server processes do, not what user processes do.
3. A, D, F. Every instance must have a database buffer cache, a log buffer, and a shared pool. B, C, E, and G are incorrect. B, C, and G are incorrect because the Java pool, the large pool, and the streams pool are needed only for certain options. E is incorrect because the PGA is not part of the SGA at all.
4. D. The log buffer is fixed in size at startup time. A, B, C, E, F, and G are incorrect. A, B, C, E, and F are incorrect because these are the SGA’s resizable components. G is incorrect because the log buffer is static.
5. D. The log buffer cannot be resized manually, never mind automatically. A, B, C, E, F, and G are incorrect. A, B, C, E, and F are incorrect because these SGA components can all be automatically managed. G is incorrect because the log buffer is static.
6. A. The session updates the copy of the block in memory and writes out the change vector to the log buffer. B, C, and D are incorrect. B is incorrect because although this will happen, it does not happen when the change is made. C is incorrect because it confuses the session making changes in memory with LGWR propagating changes to disk. D is incorrect because all changes to data occur in memory as they are made—the COMMIT is not relevant.
7. A. Archiving is not compulsory (although it is usually a good idea). B, C, D, and E are incorrect. CKPT, DBWn, LGWR, and MMON are all necessary processes.
8. C. On COMMIT, the log writer flushes the log buffer to disk. No other background processes need do anything. A, B, and D are incorrect. A is incorrect because full checkpoints occur only on request or on orderly shutdown; partial checkpoints are automatic as needed. B is incorrect because the algorithm DBWn uses to select buffers to write to the datafiles is not related to COMMIT processing but to how busy the buffer is. D is incorrect because ARCn copies only filled online redo logs; it doesn’t copy change vectors in real time.
9. A, C, E. Both A and C are correct because the DBA can choose to configure multiple archive and database writer processes. E is correct because one server process will be launched for every concurrent session. B and D are incorrect. An instance can have only one log writer process and only one checkpoint process.
10. C. If a tablespace has several datafiles, segments can have extents in all of them. A, B, and D are incorrect. A is incorrect because one extent consists of consecutive blocks in any one datafile. B is incorrect because one segment can exist in only one tablespace (although one tablespace can contain many segments). D is incorrect because although this can certainly be done, one block can exist in only one datafile.
11. C. Two groups of one member is the minimum required for the database to function. A, B, and D are incorrect. A and B are incorrect because at least two groups are always required. D is incorrect because although it is certainly advisable to multiplex the members, it is not a technical requirement.
12. A. The checkpoint process writes the redo byte address (RBA) to the controlfile. B, C, and D are incorrect. The online logs, the datafiles, and the SGA have no knowledge of where the current RBA is.