Try to imagine, how Confucius, Buddha, Jesus, Mohammed or Homer would have reacted when they had been offered a [computer].
—Edsger Dijkstra, advocate of structured programming and winner of the 1972 Turing Award, in “Correctness Concerns and, Among Other Things, Why They Are Resented”
Just as an automobile engine has a lot of interconnected parts that must all work well together, and just as an automobile mechanic must understand the individual parts and how they relate to the whole, the Oracle Database engine has a lot of interconnected parts, and the database administrator must understand the individual parts and how they relate to the whole. This chapter summarizes the Oracle engine. It is impossible to document the workings of a complex piece of machinery in a short chapter—for a full treatment, refer to Oracle Database 12c Concepts, which, like all the reference manuals, is searchable and downloadable for free at the Oracle web site.
As an introduction, Figure 3-1 shows an interesting diagram of the inner workings of the Oracle Database engine. This diagram was produced by a software tool called Spotlight from Quest Software. The name of the database—pdb1—is seen in the upper-right corner of the diagram.
In Figure 3-1, the various components of the Oracle Database architecture are grouped as follows:
The other terms used in the diagram are explained in the following sections.
In Oracle terminology, the word database is used to collectively indicate the data files and supporting files on the storage disks attached to the host computer, and the word instance is used to describe the computer processes resident in computer memory and memory areas shared by these processes. The relationship between Oracle instances and databases is one-to-one (one instance per database), many-to-one (multiple instances per database), or one-to-many (Oracle Database 12c only). The many-to-one configuration is called Real Application Clusters (RAC)—the database lives on shared disks, and instances on multiple computers attach to the database. The one-to-many configuration is called the Oracle Multitenant option and—as the name implies—is an extra-cost optional feature of Oracle Database 12c.
The most concrete aspect of a database is the files on the storage disks connected to the database host. This section briefly discusses each category of file. Placement, sizing, and other configuration details are discussed in Chapters 5 and 6.
The location of the database software is called the Oracle home, and the path to that location is usually stored in the environment variable ORACLE_HOME. There are two species of database software: server software and client software. Server software is necessary to create and manage the database and is required only on the database host. Client software is necessary to utilize the database and is required on every user’s computer—the most common example is the SQL*Plus command-line tool.
The most important database configuration file is the one containing the settings used during database startup. It comes in two versions: a text version called a pfile and a binary version called an spfile. (You see an example in Chapter 6.) The pfile and spfile specify such details as the amount of computer memory that Oracle may use during operation. The pfile is traditionally referred to as the init.ora file.
Another important server-side configuration file is the listener.ora file. It controls the operation of the listener—an important process that comes into play when users start a database session. You see an example of this in Chapter 6. The tnsnames.ora file is a client-side configuration file that contains location information for databases. You see an example later in this chapter.
The biggest component of a database is usually the files where data is stored. You could create a database with just one data file if you wanted to prove a point, but most databases have dozens of data files.
Data files are logically grouped into tablespaces and are usually given descriptive names such as DATA, INDEX, UNDO, and TEMP that indicate their intended purpose. Use a tablespace only for the purpose indicated by its name—for example, the SYSTEM tablespace should be used to store only the data dictionary (tables containing information about the rest of the database). Except for the SYSTEM and SYSAUX tablespaces, which are always created and whose names are mandated by Oracle, the number and names of the other tablespaces are left to you.
Each Oracle table and index is assigned to a single tablespace, and their growth is therefore limited by the availability of space in that assigned tablespace. They share the space with all the other tables or indexes also assigned to the tablespace. Data files can grow automatically as required; but, for reasons associated with manageability, you should limit how big they can grow. You can also create large data files of fixed size, and you can create additional data files during the life of the database.
The names chosen for data files typically include the name of the tablespace and the extension dbf or ora —for example, SYSTEM01.dbf might be the name given to the first data file in the SYSTEM tablespace. But this is only a convention, not a requirement, and you may invent your own convention or choose not to have one.
The space within data files is organized into data blocks (sometimes called pages) of equal size: 2, 4, 8, 16, 32, or 64 kilobytes (KB). 8KB is a commonly used block size. Each block contains data from just one table. The size of data records should be considered when deciding what size block to use. Oracle allows data records to span data blocks, but it is more efficient to retrieve one data block instead of multiple blocks from different locations on the storage disks. All the data files in one tablespace use blocks of the same size, and the block size should be a factor in the decision to assign a table or an index to a particular tablespace. When a table needs more space, it grabs a contiguous range of data blocks called an extent; it is conventional to use uniformly sized extents for all tables in the same tablespace.
Each dedicated Oracle server process (a process that is dedicated to a single database connection) uses a private work area called a Program Global Area in computer memory to hold intermediate results: for example, data that must be sorted. The temporary files are used for intermediate storage when sufficient memory is unavailable.
Redo log files help Oracle ensure that the effects of a user’s transaction are durable even if there is a computer failure. Before any data in the data files is changed, the log-writer (LGWR) process stores a copy of the old data (undo information) and the new data (redo information) in the redo log file. In the event of a computer failure, the redo log files enable Oracle to undo the effects of incomplete transactions (uncommitted transactions) and redo committed transactions.
The sizes of the redo log files are decided by the database administrator. It is conventional but not required for all redo log files to be the same size. An Oracle database needs at least two redo log files. Oracle uses the redo log files in round-robin fashion; when one redo log file is filled, Oracle begins filling the next one, and so on.
Because the redo log files defend the database against computer failure, they must be well protected. It is typical to mirror each redo log file; a mirrored set of redo log files is referred to as a redo log group. It is also typical to put each member of a redo file group on a different storage disk. All the members of a redo file group have the same size; the log writer process stores the same information in all members of a redo file group. Oracle can therefore continue to operate as long as at least one member of each redo file group is undamaged.
When a redo file fills up, an Oracle component called the archiver makes one or more copies in locations specified by the database administrator. Multiple copies improve the chances that at least one will survive if the storage disks are damaged.
These copies make it possible to reconstruct data files if they are ever damaged. If a data file is damaged, the database administrator can first restore the most recent backup copy of the data file, and the information contained in the archived redo files can be systematically processed to reproduce the effects of all the transactions that modified the data file since the backup copy was created.
It is possible to make copies of redo files directly on computer tapes, but it is more common to make copies on dedicated disks and to periodically sweep them to the tapes.
Oracle uses the control file while starting the database. This file contains information about the rest of the database, such as the names and locations of the data files. It also contains information—such as the names and locations of the archived redo log files—needed for recovery of damaged data files. It is continuously updated during the operation of the database.
Because of the criticality of the control file, it is conventional to have multiple copies—that is, mirrors. Oracle keeps all copies in perfect synchronization.
Oracle records important events in various log files. Events such as startup and shutdown, important Data Definition Language (DDL) operations such as ALTER TABLESPACE ADD DATAFILE, and space shortages are some of the events recorded in the alert log. A record is written to the listener log every time a user establishes a connection to the database. A detailed trace file is produced every time certain severe errors occur; examples include the ORA-600 error, which usually indicates that an Oracle bug has been encountered. You see examples of event logs in Chapters 10 and 11.
You must protect the database from storage failures by creating backup copies of the data files. If not enough disk space is available, backup copies of the data files can be stored on magnetic tapes. If enough disk space is available, backup copies can be stored on the disks, although they should not be stored on the disks where data files are stored. The location of the backup copies is traditionally referred to as the flashback recovery area. The backups stored on disk are usually copied to tapes for added safety.
We return to the topic of database backups in Chapters 12 and 13.
The Oracle instance is the engine that processes requests for data from the database—it is composed of foreground processes, background processes, and a large shared memory area called the System Global Area (SGA).
The SGA is typically a very large memory area shared by all Oracle processes. It is divided into distinct areas such as the buffer cache, the shared pool, and the log buffer, whose sizes are specified in the database configuration file (pfile or spfile). The Oracle processes coordinate their access to these areas by using inter-process communication (IPC) mechanisms called latches.
Typically, the buffer cache is the largest portion of the SGA. For reasons of efficiency, copies of data blocks (block buffers) are cached in computer memory whenever possible. When a foreground process needs a data block, it first checks the buffer cache, hoping to find the block there. If a block is not found in the cache, query processing has to be delayed until the foreground process retrieves the block from the storage disks.
When the buffer cache fills up, the least recently used blocks are removed to make space for new requests. You can try various strategies to improve the efficiency of the cache. A special keep pool can be created in the buffer cache to store data blocks from frequently used data tables—for example, lookup tables. A recycle pool can be created to hold data blocks that are not likely to be reused. Other techniques include partitioning and clusters—more information is provided in Chapter 7.
The shared pool is another large component of the SGA and has many uses. The best-known use is caching query-execution plans for potential reuse by the user who first submitted the query or by any another user who submits the identical query—this is done in an area called the shared SQL area. Another well-known use is caching information from the data dictionary; this is done in an area within the shared pool called the dictionary cache.
The log buffer is a queue of undo entries and redo entries. The log writer copies undo and redo entries to the redo log file. Typically, the size of this area is only a few megabytes.
A dedicated server process is typically started whenever a user connects to the database—it performs all the work requested by the user. An alternative model called multithreaded server (MTS), in which all user connections are serviced by a small set of dispatchers and shared servers, is also available but is not very suitable for general-purpose use and is not widely used.
These processes perform activities such as checking whether the user has permission to access the data, generating a query-execution plan for the SQL query submitted by the user, and retrieving data blocks into the buffer cache and modifying them. Before changing the contents of block buffers, it gains exclusive control to them by using a latch. Before modifying a data block, the foreground process first makes a copy of the block in an undo segment in the UNDO tablespace; it also creates the undo and redo entries that the log writer will store in the redo log files.
Dedicated server processes are terminated when the corresponding database sessions are terminated.
Unlike dedicated foreground processes, background processes live from database startup until database shutdown. The following are some of the better-known categories of background processes:
The one-to-many configuration has always been the standard configuration used by other database products including IBM DB2, Microsoft SQL, MySQL, and PostgreSQL; but Oracle Database 12c is the first version of Oracle Database to support this configuration. The official name for this feature is the Oracle Multitenant option, and—as the name implies—it is an extra-cost optional feature of Oracle Database 12c; that is, both the traditional one-to-one-configuration and the new one-to-many configurations are supported in Oracle Database 12c. In the one-to-many configuration, multiple databases share an Oracle home directory, SGA, background processes such as PMON and SMON, an UNDO tablespace, online redo logs, and (optionally) a TEMP tablespace. Each such database is referred to as a pluggable database (PDB) to indicate that it “plugs” into a container database (CDB) and can be plugged or unplugged when necessary. For example, a PDB can be unplugged from one CDB and plugged into another CDB with more hardware resources or a CDB to which software patches and upgrades have been applied. In other words, PDBs are easily transportable.
The one-to-many architecture is compatible with the one-to-one architecture. Oracle Distinguished Product Manager Bryn Llewellyn calls this the PDB/non-CDB compatibility guarantee. Any database application designed on the one-to-one architecture will continue to work without change in the one-to-many architecture. Llewellyn asserts that all questions about backward compatibility can immediately be answered in the affirmative by virtue of this guarantee. For example:
The answer to the last question is particularly important because one of the defining characteristics of a database is that it is a logical unit of recovery.
Oracle Multitenant can only be licensed in conjunction with Enterprise Edition (Chapter 4 discusses the various editions of Oracle Database). However, a limited license is available for free. You can freely use Oracle Multitenant with any edition of Oracle Database and without paying any extra charges as long as you restrict yourself to a single PDB. You can reduce the downtime associated with database upgrades by creating an upgraded CDB and then painlessly unplugging a PDB from its current CDB and plugging it into the upgraded CDB. However, because of the PDB/non-CDB compatibility guarantee, this book does not need to spend much time on Oracle Multitenant, even though it is the signature feature of Oracle Database 12c.
When you need to scale a database to support more users and transactions, there are two solutions: scale up or scale out. Scale up means additional hardware resources are added to the computer server housing the database. Scale out means the database is spread over multiple computer servers. Oracle Real Application Clusters (RAC) is Oracle’s scale-out architecture. In this architecture, multiple Oracle instances, each housed in a separate computer server, all connect to a single set of database files on a network-attached storage (NAS) or storage area network (SAN) storage server. This approach is called the shared disk approach to contrast it with the shared nothing or federated approach used by other database products such as IBM DB2. In the shared nothing approach, each instance manages a part of the database such that, if one instance stops, that part of the database becomes inaccessible until the failed instance is restarted. In the shared disk approach, the failure of one instance impacts the availability of the database. Another distinguishing feature of the Oracle scheme is cache fusion, in which each instance can request data held in the data caches of other instances.
The many-to-one architecture is much harder to configure and administer than the one-to-many architecture. In a nod to the increased complexity of RAC, Oracle Corporation sells an engineered system—that is, a combined hardware and software package—called the Oracle Database Appliance (ODA) . The ODA is a two-node RAC cluster with sufficient power for all but the largest organizations. Here are some of the advantages of the ODA:
Oracle RAC is outside the scope of this introductory text, so it is not mentioned again.
Now let’s consider the life cycle of a database session, from initiation to termination. For simplicity, I am restricting the example to a traditional client-server connection and assuming the use of dedicated servers instead of shared servers. Here are the phases that each database session goes through:
pdb1 =
(description =
(address = (protocol = tcp)(host = 192.168.56.101)(PORT = 1521))
(connect_data =
(server = dedicated)
(service:name = pdb1)
)
)
The information in this chapter summarizes the contents of Oracle Database 12c Concepts. Here is a summary of the concepts the chapter touched on:
3.144.30.62