Web based School

This chapter describes the components of the Oracle database software that are present (in one form or another) on all machines on which the Oracle database can run. I describe the various components (including memory, process, hardware, and network components) and discuss the interaction between them. In addition, I discuss some of the internal objects (such as rollback segments) so you can have a greater understanding of the inner functioning and setup of the database.

Computer Architecture Fundamentals

In this section, I give a very brief overview of the architecture present in all computers; keep in the mind, however, that a stand-alone machine might not have any network connections.

Memory

Memory is a storage device in a computer chip into which instructions and data are entered and retrieved when needed for processing. It is thousands, if not hundreds of thousands, times faster to read information from memory rather than directly from disk; however, there must be an initial load of information into memory from disk. The larger the memory available for a machine, the quicker it will run.

As far as Oracle is concerned, the instructions are the Oracle programs, and the data is the data read from the Oracle database files.

Processes/Programs

A process or program is a set of instructions to a computer that are run on the machine's processor. These instructions, or programs, are stored on the file system of the machine. Before you can run a program, the instructions for it are read into the machine's memory from the file system.

For Oracle, the processes are the Oracle system (background) processes that look after the database or the user processes that perform work for the user accessing the database.

File Systems

Data and programs for the computer are stored on the machine's file system, which typically consists of one or more hard disks. Programs and data, when required, are loaded from the hard disk into the machine's memory before the program or data is used.

Oracle uses the file system for the sets of the files that make up the database and the software programs that enable you to access the database.

Network

A network is a system of connections between machines that enable one machine to communicate with another and share resources. As well as the physical wires and components, you need to establish a set of rules for communication; this is known as a protocol.

Oracle can support many different types of networks and protocols. If you require communication between machines running Oracle software, you must install the Oracle SQL*Net software on all machines requiring network access.

Global View of the Oracle Architecture

The Oracle architecture described in this section is the generic architecture that applies to all platforms on which Oracle runs. There might be differences in the architecture between different platforms, but the fundamentals are the same.

What Is a Database?

A database is a collection of related data that is used and retrieved together for one or more application systems. The physical location and implementation of the database is transparent to the application programs, and in fact, you could move and restructure the physical database without affecting the programs.

Figure 3.1 illustrates the concept of a database holding data for many different (possibly unrelated) applications.

Figure 3.1. Database with data for many applications.

Physically, in its simplest form, an Oracle database is nothing more than a set of files somewhere on disk. The physical location of these files is irrelevant to the function (although important for the performance) of the database. The files are binary files that you can only access using the Oracle kernel software. Querying data in the database files is typically done with one of the Oracle tools (such as SQL*Plus) using the Structured Query Language (SQL).

Logically, the database is divided into a set of Oracle user accounts (schemas), each of which is identified by a username and password unique to that database. Tables and other objects are owned by one of these Oracle users, and access to the data is only available by logging in to the database using an Oracle username and password. Without a valid username and password for the database, you are denied access to anything on the database. The Oracle username and password is different from the operating system username and password. For example, a database residing on a UNIX machine requires that I log in to the UNIX machine using my UNIX operating system username and password and then log in again to Oracle before I can use the database objects (the UNIX login would not be required for a client/server setup). This process of logging in, or connecting to, the database is required whether you're using an Oracle or non-Oracle tool.

The same table name can coexist in two separate Oracle user accounts; although the tables might have the same name, they are different tables. Sometimes, the same database (same set of physical database files) is used for holding different versions of tables (in separate Oracle accounts) for the developers, system testing, or user testing, or the same table name is used in different application systems.

Often, people refer to an Oracle user account as a database, but this is not strictly correct. You could use two Oracle user accounts to hold data for two entirely different application systems; you would have two logical databases implemented in the same physical database using two Oracle user accounts.

In addition to physical files, Oracle processes and memory structures must also be present before you can use the database.

Figure 3.2 shows the basic Oracle architecture that I discuss throughout this chapter.

Figure 3.2. The basic Oracle architecture.

Oracle Files

There are three major sets of files on disk that compose a database.

Database files
Control files
Redo logs

The most important of these are the database files where the actual data resides. The control files and the redo logs support the functioning of the architecture itself.

All three sets of files must be present, open, and available to Oracle for any data on the database to be useable. Without these files, you cannot access the database, and the database administrator might have to recover some or all of the database using a backup, if there is one! All the files are binary.

System and User Processes

For the database files to be useable, you must have the Oracle system processes and one or more user processes running on the machine. The Oracle system processes, also known as Oracle background processes, provide functions for the user processes—functions that would otherwise be done by the user processes themselves. There are many background processes that you can initiate, but as a minimum, only the PMON, SMON, DBWR, and LGWR (all described later in the chapter) must be up and running for the database to be useable. Other background processes support optional additions to the way the database runs.

In addition to the Oracle background processes, there is one user process per connection to the database in its simplest setup. The user must make a connection to the database before he can access any of the objects. If one user logs into Oracle using SQL*Plus, another user chooses Oracle Forms, and yet another user employs the Excel spreadsheet, then you have three user processes against the database—one for each connection.

Memory

Oracle uses the memory (either real or virtual) of the system to run the user processes and the system software itself and to cache data objects. There are two major memory areas used by Oracle: memory that is shared and used by all processes running against the database and memory that is local to each individual user process.

System Memory

Oracle database-wide system memory is known as the SGA, the system global area or shared global area. The data and control structures in the SGA are shareable, and all the Oracle background processes and user processes can use them.

The combination of the SGA and the Oracle background processes is known as an Oracle instance, a term that you'll encounter often with Oracle. Although there is typically one instance for each database, it is common to find many instances (running on different processors or even on different machines) all running against the same set of database files.

User Process Memory

For each connection to the database, Oracle allocates a PGA (process global area or program global area) in the machine's memory. Oracle also allocates a PGA for the background processes. This memory area contains data and control information for one process and is not shareable between processes.

**Network Software and SQL*Net**

A simple configuration for an Oracle database has the database files, memory structures, and Oracle background and user processes all running on the same machine without any networking involved. However, much more common is the configuration that implements the database on a server machine and the Oracle tools on a different machine (such as a PC with Microsoft Windows). For this type of client/server configuration, the machines are connected with some non-Oracle networking software that enables the two machines to communicate. Also, you might want two databases running on different machines to talk to each other—perhaps you're accessing tables from both databases in the same transaction or even in the same SQL statements. Again, the two machines need some non-Oracle networking software to communicate.

Whatever type of networking software and protocols you use to connect the machines (such as TCP/IP) for either the client/server or server-server setup mentioned previously, you must have the Oracle SQL*Net product to enable Oracle to interface with the networking protocol. SQL*Net supports most of the major networking protocols for both PC LANs (such as IPX/SPX) and the largest mainframes (such as SNA). Essentially, SQL*Net provides the software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running SQL*Plus) and the database server or from one database server to another.

You must install the SQL*Net software on both machines on top of the underlying networking software for both sides to talk to each other. SQL*Net software options enable a client machine supporting one networking protocol to communicate with another supporting a different protocol.

You do not need to change the application system software itself if the networking protocols or underlying networking software changes. You can make the changes transparently with the Database Administrator, installing a different version of SQL*Net for the new network protocol.

Figure 3.3 shows the role of SQL*Net in a client/server environment with two server database machines.

Figure 3.3. SQL*Net diagram in a client/server environment with two server databases.

Oracle Files

In this part, I discuss the different types of files that Oracle uses on the hard disk drive of any machine.

Database Files

The database files hold the actual data and are typically the largest in size (from a few megabytes to many gigabytes). The other files (control files and redo logs) support the rest of the architecture. Depending on their sizes, the tables (and other objects) for all the user accounts can obviously go in one database file—but that's not an ideal situation because it does not make the database structure very flexible for controlling access to storage for different Oracle users, putting the database on different disk drives, or backing up and restoring just part of the database.

You must have at least one database file (adequate for a small or testing database), but usually, you have many more than one. In terms of accessing and using the data in the tables and other objects, the number (or location) of the files is immaterial.

The database files are fixed in size and never grow bigger than the size at which they were created.

Control Files

Any database must have at least one control file, although you typically have more than one to guard against loss. The control file records the name of the database, the date and time it was created, the location of the database and redo logs, and the synchronization information to ensure that all three sets of files are always in step. Every time you add a new database or redo log file to the database, the information is recorded in the control files.

Redo Logs

Any database must have at least two redo logs. These are the journals for the database; the redo logs record all changes to the user objects or system objects. If any type of failure occurs, such as loss of one or more database files, you can use the changes recorded in the redo logs to bring the database to a consistent state without losing any committed transactions. In the case of non-data loss failure, such as a machine crash, Oracle can apply the information in the redo logs automatically without intervention from the database administrator (DBA). The SMON background process automatically reapplies the committed changes in the redo logs to the database files.

Like the other files used by Oracle, the redo log files are fixed in size and never grow dynamically from the size at which they were created.

Online Redo Logs

The online redo logs are the two or more redo log files that are always in use while the Oracle instance is up and running. Changes you make are recorded to each of the redo logs in turn. When one is full, the other is written to; when that becomes full, the first is overwritten, and the cycle continues.

Offline/Archived Redo Logs

The offline or archived redo logs are exact copies of the online redo logs that have been filled; it is optional whether you ask Oracle to create these. Oracle only creates them when the database is running in ARCHIVELOG mode. If the database is running in ARCHIVELOG mode, the ARCH background process wakes up and copies the online redo log to the offline destination (typically another disk drive) once it becomes full. While this copying is in progress, Oracle uses the other online redo log. If you have a complete set of offline redo logs since the database was last backed up, you have a complete record of changes that have been made. You could then use this record to reapply the changes to the backup copy of the database files if one or more online database files are lost.

Other Supporting Files

When you start an Oracle instance (in other words, when the Oracle background processes are initiated and the memory structures allocated), the instance parameter file determines the sizes and modes of the database. This parameter file is known as the INIT.ORA file (the actual name of the file has the Oracle instance identifier appended to the filename). This is an ordinary text file containing parameters for which you can override the default settings. The DBA is responsible for creating and modifying the contents of this parameter file.

On some Oracle platforms, a SGAPAD file is also created, which contains the starting memory address of the Oracle SGA.

System and User Processes

In this part, I discuss some of the Oracle system processes that must be running for the database to be useable, including the optional processes and the processes that are created for users connecting to the Oracle database.

Mandatory System Processes

The four Oracle system processes that must always be up and running for the database to be useable include DBWR (Database Writer), LGWR (Log Writer), SMON (System Monitor), and PMON (Process Monitor).

DBWR (Database Writer)

The database writer background process writes modified database blocks in the SGA to the database files. It reads only the blocks that have changed (for example, if the block contains a new record, a deleted record, or a changed record). These blocks are also called dirty blocks. The database writer writes out the least recently used blocks first. These blocks are not necessarily written to the database when the transaction commits; the only thing that always happens on a commit is that the changes are recorded and written to the online redo log files. The database blocks will be written out later when there are not enough buffers free in the SGA to read in a new block.

LGWR (Log Writer)

The log writer process writes the entries in the SGA's redo buffer for one or more transactions to the online redo log files. For example, when a transaction commits, the log writer must write out the entries in the redo log buffer to the redo log files on disk before the process receives a message indicating that the commit was successful. Once committed, the changes are safe on disk even though the modified database blocks are still in the SGA's database buffer area waiting to be written out by DBWR. The SMON can always reapply the changes from the redo logs if the memory's most up-to-date copy of the database blocks is lost.

SMON (System Monitor)

The system monitor process looks after the instance. If two transactions are both waiting for each other to release locks and neither of them can continue (known as a deadlock or deadly embrace), SMON detects the situation and one of the processes receives an error message indicating that a deadlock has occurred.

SMON also releases temporary segments that are no longer in use by the user processes which caused them to be created.

During idle periods, SMON compacts the free-space fragments in the database files, making it easier and simpler for Oracle to allocate storage for new database objects or for existing database objects to grow.

In addition, SMON automatically performs recovery when the Oracle instance is first started up (if none of the files have been lost). You won't see a message indicating that instance recovery is occurring, but the instance might take longer to come up.

PMON (Process Monitor)

The process monitor monitors the user processes. If any failure occurs with the user processes (for example, if the process is killed in the middle of a transaction), PMON automatically rolls back the work of the user process since the transaction started (anything since the last COMMIT or ROLLBACK). It releases any locks taken out and other system resources taken up by the failed process.

PMON also monitors the dispatcher and shared server processes, which are part of the multi-threaded server setup, and restarts them if they have died.

Optional System Processes

As well as the four mandatory system processes, there are a number of optional system processes that you can initiate.

ARCH (Archiver)

When the database is running in ARCHIVELOG mode and you've started the Archiver background process, it makes a copy of one of the online redo log files to the archive destination (the exact location is specified in an INIT.ORA parameter). In this way, you can have a complete history of changes made to the database files recorded in the offline and the online redo logs.

There is no point in keeping the Archiver background process running if the database is not running in ARCHIVELOG mode.

CKPT (Checkpoint Process)

A checkpoint occurs when one of the online redo log files fills; it will be overwritten when one of the other online redo logs fills. If the redo log file is overwritten, the changes recorded in that file are not available for reapplying in case of system failure. At a checkpoint, the modified database buffer blocks are written down to the relative safety of the database files on disk by the database writer background process. This means that you won't need the record of changes in the event of system failure with lost memory areas. After a checkpoint occurs, the redo log can be reused.

At a checkpoint, all the database file headers and redo log file headers are updated to record the fact that a checkpoint has occurred. The LGWR background process performs the updating task, which could be significant if there are a large number of database and redo log files. The entire database might have to wait for the checkpoint to complete before the redo logs can record further database changes. To reduce the time it takes for LGWR to update the database and redo log file headers, you can initiate the checkpoint process.

A checkpoint can occur at other times, such as when the entries in the redo log files reach a limit defined by the database administrator.

Whether or not the CKPT background process is initiated, checkpointing still occurs when one of the redo log files fills.

RECO (Recoverer)

You use the Recoverer background process when there is a failure in a distributed transaction (a transaction where two or more databases are updated), and one or more of the databases involved need to either commit or roll back their changes. If initiated, the Recoverer attempts to automatically commit or roll back the transaction on the local database at timed intervals in synchronization with the Recoverer processes on the other Oracle databases.

There is no point in keeping the Recoverer background process running if you're not using distributed transactions on the database.

LCK (Lock)

You use the lock background process in the parallel server setup of Oracle where more than one instance is running against the same set of database files. The LCK processes running on all instances will synchronize locking between the instances. If a user connects to one instance and locks a row, the row remains locked for a user attempting to make a change on another instance. Other users can always query the rows regardless of how the rows are locked by other users.

You can initiate up to ten LCK background processes to reduce the bottleneck of synchronizing locking, but one is usually more than enough.

You should not initiate the LCK background processes unless you're implementing a parallel server (multi-instance) setup of Oracle.

**SQL*Net Listener**

The SQL*Net listener is a process running on the machine that routes requests coming in from client machines through to the correct Oracle instance. It communicates with the underlying networking software to route requests to and from the database server and the client machine (whether that client machine is a machine running an Oracle tool or even another database server).

For example, the communications between a client machine running Oracle Forms on a PC with DOS and Windows and a database server on a UNIX machine with TCP/IP as the networking protocol would involve the following major steps:

The client machine sends the SQL statement execution request to the UNIX database server machine.
The non-Oracle TCP/IP listener process picks up the request and recognizes it as a request for Oracle.
The request is sent to the Oracle SQL*Net listener, which routes the request to the correct instance on the machine. (The machine might be running many instances for many different databases.)
A process on the instance executes the statement.
The results are then sent back up the communications link to the client machine.

The SQL*Net listener is not related to the instance itself but is system wide and will process requests for all instances running on the machine. You can initiate more than one SQL*Net listener, but this is uncommon.

User Processes

User processes logically consist of two halves: the Oracle server code, which translates and executes SQL statements and reads the database files and memory areas, and the tool-specific code, which is the executable code for the tool that is used. The server code is the same regardless of the tool that is executing the SQL statement; the same steps are involved. The server code is sometimes known as the Oracle kernel code.

You can configure the user processes in Oracle three different ways, all of which could coexist for the same instance. These three configurations are single task, dedicated server, or multi-threaded server.

Single Task

In the single-task configuration, the tool-specific code and database server code are both configured into one process running on the machine. Each connection to the database has one user process running on the machine. This is common on VAX VMS platforms without a client/server environment.

Dedicated Server Processes

In the dedicated server configuration (also known as two-task or running with shadow processes), the two parts of a user process are implemented as two separate processes running on the machine. They communicate with each other using the machine's interprocess communication mechanisms. Each connection to the database has two processes running on the machine. The Oracle kernel software in one process is sometimes called the shadow process.

This configuration is common for UNIX platforms because the operating system cannot (in some implementations of UNIX) protect the Oracle code and memory areas from the application code. It is also common for client/server configurations where the server code resides on the server machine and the tool-specific code runs on the client machine with communication over a network. The way the two component parts of one logical process communicate is fundamentally the same as if one process were implemented on the same machine—except that the two halves of the logical process happen to reside on two machines and communicate over the network using SQL*Net rather than the interprocess communication mechanisms of the operating system.

The dedicated server configuration can be wasteful because memory is allocated to the shadow process and the number of processes that must be serviced on the machine increases, even when the user is not making any database requests. The dedicated server (shadow process) will only process requests from one associated client process.

MTS—The Multi-Threaded Server

The multi-threaded server configuration enables one Oracle server process to perform work for many user processes. This overcomes the drawbacks of the dedicated server configuration. It reduces the number of processes running and the amount of memory used on the machine and can improve system performance. The multi-threaded server introduces two new types of system processes that support this part of the architecture.

Using one of the shared server processes that comes as part of the multi-threaded server configuration is not appropriate when a user process is making many database requests (such as an export backup of the database); for that process, you could use a dedicated server. A mixture of both configurations can coexist.

Dispatchers

One or more dispatcher processes retrieves requests for the client processes from the SQL*Net listener and routes the request to one of the shared server processes. The SQL*Net listener is required for the multi-threaded server configuration even if no networking is involved.

You must configure at least one dispatcher for each network protocol that is used to route requests to the instance. The number of dispatchers configured does not increase if the system load increases because the dispatchers are only providing the routing. The actual work is done by the shared servers.

The multi-threaded server requires SQL*Net Version 2 or later even if both the dispatcher and the user process are running on the same machine.

Shared Servers

The shared servers provide the same functionality as the dedicated server processes and contain the Oracle server code that performs the work for the client. They can service requests from many different user processes. The actual shared server used might differ from one call to another so that no user process can monopolize any one particular shared server process. Oracle uses an area in the SGA for messaging between the different processes involved.

The number of shared server processes is automatically increased (or decreased to an initial number defined by the database administrator) according to the system activity.

The number of shared servers is increased or decreased automatically, but the number of dispatchers is not.

Oracle Memory

In this part, I discuss how Oracle uses the machine's memory. Generally, the greater the real memory available to Oracle, the quicker the system runs.

System Global Area (SGA)

The system global area, sometimes known as the shared global area, is for data and control structures in memory that can be shared by all the Oracle background and user processes running on that instance. Each Oracle instance has its own SGA; in fact, the SGA and background processes is what defines an instance. The SGA memory area is allocated when the instance is started, and it's flushed and deallocated when the instance is shut down.

The contents of the SGA are divided into three main areas: the database buffer cache, the shared pool area, and the redo cache. The size of each of these areas is controlled by parameters in the INIT.ORA file. The bigger you can make the SGA and the more of it that can fit into the machine's real memory (as opposed to virtual memory), the quicker your instance will run. Figure 3.4 shows the Oracle SGA in memory.

Figure 3.4. The Oracle SGA in memory.

Database Buffer Cache

The database buffer cache of the SGA holds Oracle blocks that have been read in from the database files. When one process reads the blocks for a table into memory, all the processes for that instance can access those blocks.

If a process needs to access some data, Oracle checks to see if the block is already in this cache (thereby avoiding a disk read). If the Oracle block is not in the buffer, it must be read from the database files into the buffer cache. The buffer cache must have a free block available before the data block can be read from the database files.

The Oracle blocks in the database buffer cache in memory are arranged with the most recently used at one end and the least recently used at the other. This list is constantly changing as the database is used. If data must be read from the database files into memory, the blocks at the least recently used end are written back to the database files first (if they've been modified). The DBWR process is the only process that writes the blocks from the database buffer cache to the database files.

The more database blocks you can hold in real memory, the quicker your instance will run.

Redo Cache

The online redo log files record all the changes made to user objects and system objects. Before the changes are written out to the redo logs, Oracle stores them in the redo cache memory area. For example, the entries in the redo log cache are written down to the online redo logs when the cache becomes full or when a transaction issues a commit. The entries for more than one transaction can be included together in the same disk write to the redo log files.

The LGWR background process is the only process that writes out entries from this redo cache to the online redo log files.

Shared Pool Area

The shared pool area of the SGA has two main components: the SQL area and the dictionary cache. You can alter the size of these two components only by changing the size of the entire shared pool area.

SQL Area

A SQL statement sent for execution to the database server must be parsed before it can execute. The SQL area of the SGA contains the binding information, run-time buffers, parse tree, and execution plan for all the SQL statements sent to the database server. Because the shared pool area is a fixed size, you might not see the entire set of statements that have been executed since the instance first came up; Oracle might have flushed out some statements to make room for others.

If a user executes a SQL statement, that statement takes up memory in the SQL area. If another user executes exactly the same statement on the same objects, Oracle doesn't need to reparse the second statement because the parse tree and execution plan is already in the SQL area. This part of the architecture saves on reparsing overhead. The SQL area is also used to hold the parsed, compiled form of PL/SQL blocks, which can also be shared between user processes on the same instance.

Make SQL statements exactly the same in application code (using procedures) to avoid reparsing overhead.

Dictionary Cache

The dictionary cache in the shared pool area holds entries retrieved from the Oracle system tables, otherwise known as the Oracle data dictionary. The data dictionary is a set of tables located in the database files, and because Oracle accesses these files often, it sets aside a separate area of memory to avoid disk I/O.

The cache itself holds a subset of the data from the data dictionary. It is loaded with an initial set of entries when the instance is first started and then populated from the database data dictionary as further information is required. The cache holds information about all the users, the tables and other objects, the structure, security, storage, and so on.

The data dictionary cache grows to occupy a larger proportion of memory within the shared pool area as needed, but the size of the shared pool area remains fixed.

Process Global Area

The process global area, sometimes called the program global area or PGA, contains data and control structures for one user or server process. There is one PGA for each user process (connection) to the database.

The actual contents of the PGA depend on whether the multi-threaded server configuration is implemented, but it typically contains memory to hold the session's variables, arrays, some rows results, and other information. If you're using the multi-threaded server, some of the information that is usually held in the PGA is instead held in the common SGA.

The size of the PGA depends on the operating system used to run the Oracle instance, and once allocated, it remains the same. Memory used in the PGA does not increase according to the amount of processing performed in the user process. The database administrator can control the size of the PGA by modifying some of the parameters in the instance parameter file INIT.ORA; one parameter that DBAs often change is the SORT_AREA_SIZE.

The Oracle Programs

The Oracle server code mentioned previously is code that performs the same function regardless of which tool the front-end programs are using (such as SQL*Plus, Oracle Forms, Reports, Excel, and so on). On some platforms, the server code is loaded only once into the machine's memory, and all the processes using the instance can share it—even across instances (as long as you are running the same version of Oracle for both instances). This Kernel code, also known as reentrant code, saves memory because it requires that only one copy of the code be loaded into memory.

Storage

In this section, I discuss the organization of the database files themselves. You have already learned that the database files are binary, fixed-size files on disk. I do not discuss the control and redo log files in this section.

Tablespaces and Database Files

For management, security, and performance reasons, the database is logically divided into one or more tablespaces that each comprise one or more database files. A database file is always associated with only one tablespace.

A tablespace is a logical division of a database comprising one or more physical database files.

Every Oracle database has a tablespace named SYSTEM that has the very first file of the database allocated to it. The SYSTEM tablespace is the default location of all objects when a database is first created. The simplest database setup is one database file in the SYSTEM tablespace (simple, but not recommended).

Typically, you create many tablespaces to partition the different parts of the database. For example, you might have one tablespace for tables, another to hold indexes, and so on, and each of these tablespaces would have one or more database files associated to them.

When you create objects that use storage in the database (such as tables), you should specify the tablespace location of the object as part of the CREATE statement for the object. Only system tables should occupy storage in the SYSTEM tablespace. The system tables are tables such as tab$, col$, ind$, fet$, and other internal tables.

Objects such as synonyms and views do not take up storage within the database other than the storage in the data dictionary table for their definitions, along with the definitions for all other types of objects.

Tablespaces can be added, dropped, taken offline and online, and associated with additional database files. By adding another file to a tablespace, you increase the size of the tablespace and therefore the database itself.

You cannot drop the SYSTEM tablespace; this would destroy the database because the system tables are there. You also cannot take the SYSTEM tablespace offline.

Segments

A segment is a generic name given to any object that occupies storage in the database files. Some examples of segments are table segments (data segments), index segments, rollback segments, temporary segments, and the cache (bootstrap) segment. A segment uses a number of Oracle blocks that are in the same tablespace (although the blocks themselves can be in different files that make up the tablespace).

Extents

The storage for any object on the database is allocated in a number of blocks that must be contiguous in the database files. These contiguous blocks are known as extents. For example, when a table is first created using default settings, five Oracle blocks are allocated to the table for the very first extent (otherwise known as the initial extent). As rows are inserted and updated into the table, the five blocks fill with data. When the last block has filled and new rows are inserted, the database automatically allocates another set of blocks (five blocks) for the table, and the new rows are inserted into the new set of blocks. This allocating of additional storage (additional extents) continues until there is no more free space in the tablespace. The table starts with the one initial extent and is then allocated other secondary (or next) extents. The blocks for an extent must be contiguous within the database files.

Once an extent is allocated to a segment (table), those blocks cannot be used by another other database object, even if all the rows in the table are deleted. The table must be dropped or truncated to release the storage allocated to the table. The exception to this is rollback segments, which can dynamically release storage that was allocated to them.

Oracle Blocks

Oracle "formats" the database files into a number of Oracle blocks when they're first created—making it easier for the RDBMS software to manage the files and easier to read data into the memory areas.

These blocks are usually 1 KB (the default for PC-DOS systems), 2 KB (the default for most UNIX machines and VAX VMS), 4 KB (the default for IBM mainframes), or larger. For a 50MB database file, there would be 25,600 Oracle blocks assuming a block size of 2 KB (50 MB/2 KB).

The block size should be a multiple of the operating system block size. Regardless of the block size, not all of the block is available for holding data; Oracle takes up some space to manage the contents of the block. This block header has a minimum size, but it can grow.

These Oracle blocks are the smallest unit of storage. Increasing the Oracle block size can improve performance, but you should do this only when the database is first created.

When you first create a database, it uses some of the blocks within the first file, and the rest of the blocks are free. In the data dictionary, Oracle maintains a list of the free blocks for each data file in each tablespace.

Each Oracle block is numbered sequentially for each database file starting at 1. Two blocks can have the same block address if they are in different database files.

Do not modify the Oracle block size once you've created the database.

ROWID

The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.

The ROWID consists of the following three components, the combination of which uniquely identifies the physical storage location of the row.

Oracle database file number, which contains the block with the row
Oracle block address, which contains the row
The row within the block (because each block can hold many rows)

The ROWID is used internally in indexes as a quick means of retrieving rows with a particular key value. Application developers also use it in SQL statements as a quick way to access a row once they know the ROWID.

Free Space and Automatic Compaction

When a database file is first created or added to a tablespace, all the blocks within that file are empty blocks that have never been used. As time goes by, the blocks within a database file are used by a segment (table), or they remain free blocks. Oracle tracks the file's free blocks in a list in the data dictionary. As you create and drop tables, the free space becomes fragmented, with free space in different parts of the database file. When the free blocks are scattered in this way, Oracle has no way to automatically bring the free storage together.

When two fragments of free space are physically next to each other in the database file, the two smaller fragments can be compacted together into one larger fragment, which is recorded in the free space list. This compacting reduces the overhead when Oracle actually needs the free space (when a table wants to allocate another extent of a certain size, for example). The SMON background process performs this automatic compaction.

System Database Objects

In this part, I discuss some of the system objects that support the workings of the architecture and give information about the structure of the database.

The Data Dictionary

The first tables created on any database are the system tables, also known as the Oracle data dictionary. These tables are owned by the first Oracle user account that is created automatically— by the user SYS. The system tables record information about the structure of the database and the objects within it, and Oracle accesses them when it needs information about the database or every time it executes a DDL statement (Data Definition Language) or DML statement (Data Manipulation Language). These tables are never directly updated; however, updates to them occur in the background whenever a DDL statement is executed.

The core data dictionary tables hold normalized information that is cryptic to understand, so Oracle provides a set of views to make the information in the core system tables more meaningful. You can access the names of over 170 of the views in the data dictionary with the following command:

SELECT * FROM DICT;

Oracle requires the information in the data dictionary tables to parse any SQL statement. The information is cached in the data dictionary area of the shared pool in the SGA.

Because the very first tablespace created is the SYSTEM tablespace, the data dictionary tables use storage in the database files associated with the SYSTEM tablespaces.

Rollback Segments

Whenever you change data in Oracle, the change must be either committed or reversed. If a change is reversed or rolled back, the contents of the data block are restored back to the original state before the change. Rollback segments are system-type objects that support this reversing process. Whenever you make any kind of change to either application tables or system tables, a rollback segment automatically holds the previous version of the data that is being modified, so the old version of the data is available if a rollback is required.

If other users want to see the data while the change is pending, they always have access to the previous version from the rollback segment. They are provided with a read-consistent version of the data. Once the change is committed, the modified version of the data is available.

Rollback segments are always owned by the user SYS, and no Oracle user can access them for viewing.

Rollback segments use storage in the same way as other segments in terms of extents. With a rollback segment, however, you must initially allocate a minimum of two extents instead of only one.

The first rollback segment is created automatically when the database is first created and has a name of SYSTEM, and it uses storage in the first tablespace, which also has a name of SYSTEM.

It can get confusing that Oracle uses the same name for three different types of objects; the first tablespace is called SYSTEM, the first rollback segment is called SYSTEM, and one of the first Oracle accounts created is called SYSTEM. They are different types of objects, so do not confuse them.

Temporary Segments

Temporary segments use storage in the database files to provide a temporary work area for intermediate stages of SQL processing and for large sort operations.

Oracle creates temporary segments on the fly, and they are automatically deleted when the SMON background process no longer needs them. If only a small working area is required, Oracle does not create a temporary segment but instead uses a part of the PGA (the program global area) memory as a temporary work area.

The following operations might cause Oracle to create a temporary segment:

Creating an index
Using the ORDER BY, DISTINCT, or GROUP BY clauses in a SELECT statement
Using the set operators UNION, INTERSECT, MINUS
Creating joins between tables
Using some subqueries

The database administrator can control which tablespaces contain the temporary segments on a user-by-user basis.

Bootstrap/Cache Segment

A bootstrap or cache segment is a special type of object on the database that is used to perform an initial load of the data dictionary cache in the shared pool area of the SGA.

Oracle uses the cache segment only when the instance first starts and does not use it again until the instance restarts. Once the segment is used to perform the initial load of the data dictionary cache, the remainder of the cache in memory is steadily populated as statements are executed against the database.

Protecting Your Data

In this part, I discuss the idea of saving or reversing out changes made—committing and rolling back.

Transactions, Commit, and Rollback

Database changes are not saved until the user explicitly decides that the insert, update, and delete statements should be made permanent. Up until that point, the changes are in a pending status, and any failures, such as a machine crash, will reverse the changes.

A transaction is an atomic unit of work comprising one or more SQL statements; it begins when the user first connects to the database and ends when a COMMIT or ROLLBACK statement is issued. Upon a COMMIT or ROLLBACK, the next transaction automatically begins. All the statements within a transaction are either all saved (committed) or all reversed (rolled back).

Committing a transaction makes changes permanent in the entire transaction to the database, and once committed, the changes cannot then be reversed. Rolling back reverses all the inserts, updates, deletes in the transaction; again, once rolled back, those changes cannot then be committed. Internally, the process of committing means writing out the changes recorded in the SGA's redo log buffer cache to the online redo log files on disk. If this disk I/O succeeds, the application receives a message indicating a successful commit. (The text of the message changes from one tool to another.) The DBWR background process can write out the actual Oracle data blocks in the SGA's database buffer cache at a later time. If the system should crash, Oracle can automatically reapply the changes from the redo logs files even if the Oracle data blocks were not written back to the database files before the failure.

DDL (Data Definition Language) statements such as CREATE TABLE will automatically issue a COMMIT, even if the DDL statement itself fails.

Oracle also implements the idea of statement-level rollback. If a single statement fails during a transaction, the entire statement will fail. In other words, an INSERT statement for 1000 rows will insert either all 1000 rows or none at all; the entire statement works, or nothing happens. If a statement does fail within a transaction, the rest of the statements in the transaction are still in a pending state and must be committed or rolled back.

If a user process terminates abnormally (the process is killed, for example), the PMON background process automatically rolls back changes. Any changes that the process had committed up to the point of failure remain committed, and only those changes for the current transaction are rolled back.

All locks held by the transaction are automatically released when the transaction commits or rolls back or when the PMON background process rolls back the transaction. In addition, other system resources (such as rollback segments) are released for other transactions to use.

Savepoints enable you to set up markers within a transaction so that you have the option of rolling back just part of the work performed in the transaction. You can use savepoints in long and complex transactions to provide the reversing option for certain statements. However, this causes extra overhead on the system to perform the work for a statement and then reverse the changes; usually, changes in the logic can produce a more optimal solution. When Oracle performs a rollback to a savepoint, the rest of the statements in the transaction remain in a pending state and must be committed or rolled back. Oracle releases the locks taken by those statements that were rolled back.

Data Integrity

Data integrity is about enforcing data validation rules—such as checking that a percentage amount is between 0 and 100—to ensure that invalid data does not get into your tables. Historically, these rules were enforced by the application programs themselves (and the same rules were checked repeatedly in different programs). Oracle, however, enables you to define and store these rules against the database objects to which they relate so that you need to code them only once so they are enforced whenever any kind of change is made to the table, regardless of which tool issues the insert, update, or delete statement. This checking takes the form of integrity constraints and database triggers.

Integrity Constraints

Integrity constraints enforce business rules at the database level by defining a set of checks for the tables in your system. These checks are automatically enforced whenever you issue an insert, update, or delete statement against the table. If any of the constraints are violated, the insert, update, or delete statement is rolled back. The other statements within the transaction remain in a pending state and can be committed or rolled back according to application logic.

Because integrity constraints are checked at the database level, they are performed regardless of where the insert, update, delete statement originated—whether it was an Oracle or a non-Oracle tool. Defining checks using these constraints is also quicker than performing the same checks using SQL. In addition, the information provided by declaring constraints is used by the Oracle optimizer to make better decisions about how to run a statement against the table. The Oracle Forms product can also use constraints to automatically generate code in the front-end programs to provide an early warning to the user of any errors.

The types of integrity constraints that you can set up on a table are NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and indexes.

NOT NULL Constraints

You set the NOT NULL constraint against a column to specify that the column must always have a value on every row; it can never be null. By default, all the columns in a table are nullable. For example, using a NOT NULL constraint on an orders table, you can specify that there must always be an order amount.

PRIMARY KEY

The PRIMARY KEY constraint defines a column or a set of columns that you can use to uniquely identify a single row. No two rows in the table can have the same values for the primary key columns. In addition, the columns for a primary key constraint must always have a value—in other words, they are NOT NULL. If you add a constraint to a table after it has been created, any columns that make up the PRIMARY KEY constraint are modified to NOT NULL. Only one PRIMARY KEY constraint can exist for any table. For example, using a PRIMARY KEY constraint on an orders table, you can specify that a table cannot have two records with the same order number.

UNIQUE

The UNIQUE constraint defines a secondary key for the table. This is a column or set of columns that you can use as another way of uniquely identifying a row. No two rows can have the same values for the UNIQUE key column or columns. Although it is not possible for a table to have more than one primary key, a table can have more than one UNIQUE constraint.

The columns for a UNIQUE constraint do not have to be identified as NOT NULL (although they usually are). If the values for any of the columns that form the unique constraint are null, the constraint is not checked. For example, using a PRIMARY KEY and UNIQUE constraint on a customers table, you can specify that the customer number is a primary key and that the customer name is a unique key (which would mean that you could not have two customers with the same name on your table—a rare situation).

The UNIQUE constraint is not checked if the values in the column are null.

FOREIGN KEY

The FOREIGN KEY or referential integrity constraint enforces relationship integrity between tables. It dictates that a column or set of columns on the table match a PRIMARY KEY or UNIQUE constraint on a different table. For example, you could set up a FOREIGN KEY constraint on the orders table to specify that whenever an order record is inserted or updated, the customer number must also exist in the customers table. This ensures that you don't get orders for nonexistent customers.

You use FOREIGN KEY constraints to enforce parent/child relationships between tables. You can even use them to enforce self-referential constraints, usually in situations where a hierarchical structure is set up with all the rows held in the same table. If any of the columns of the foreign key are null, the constraint is not checked at all. Foreign key columns are usually declared as NOT NULL.

It is possible to specify that when the parent row is deleted, the delete should automatically cascade and delete the child rows—a dangerous situation. The user is informed only about the master rows that were removed, and he might not be aware of the additional rows that were deleted automatically in the background because he is not told that this cascading deletion has happened.

Only this automatic deletion of child rows is supported by specifying the ON DELETE CASCADE clause to the end of the foreign key creation statement. If you change the master table's key value, however, the child rows are not updated automatically to reflect the new key; you can implement this update cascade requirement using database triggers.

FOREIGN KEY constraints are not checked at all if any of the columns in the foreign key are null.

CHECK

A CHECK constraint specifies additional logic that must be true for the insert, update, or delete statement to work on the table. The additional logic returns a Boolean result, and in the check constraint, you ensure the values in the row being modified satisfy a set of validation checks that you specify. The syntax of a CHECK constraint is very similar to the syntax found in the WHERE clause of a SELECT statement; however, you cannot use subqueries or other columns that vary over time (such as SYSDATE). You can use database triggers to perform this additional processing that you cannot put into constraints. For example, using a CHECK constraint on the orders table, you can specify that the order amount must be greater than zero and the salesman's commission cannot be greater than 10 percent of the order total.

Indexes

PRIMARY KEY and UNIQUE constraints automatically create an index on the columns they're defined against if the constraint is enabled upon creation. If an index already exists on the columns that form the PRIMARY KEY or UNIQUE constraint, that index is used, and Oracle cannot create a new one. Oracle creates indexes when the constraint is enabled (which is the default when the constraint is first added to the table). Oracle drops the indexes from the table when the constraint is disabled. Enabling and disabling constraints can take significant time and system overhead due to the index creation and removal.

When you set up a FOREIGN KEY constraint, the columns are not indexed automatically. Because the foreign key columns are usually involved in joining tables together, you manually create indexes on those columns.

Disabling a PRIMARY KEY or UNIQUE constraint drops the index for the constraint.

Database Triggers

A database trigger is a PL/SQL block that you can define to automatically execute for insert, update, and delete statements against a table. You can define the trigger to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. For each of the twelve events, you can define many database triggers for the same event.

A database trigger can call database procedures that are also written in PL/SQL. Unlike database triggers, procedures on the database are stored in a compiled form. For this reason, you should put the longer code segments into a procedure and then call the procedure from the database trigger.

In addition to implementing complex business rules, checking, and defaulting, you can use database triggers to insert, update, and delete other tables. An example of this use is providing an auditing facility where an audit trail is automatically created in an audit table whenever a row is changed on a table. Without database triggers, this function would be implemented in the front-end programs that make the change to the database; however, someone bypassing the code in the front-end programs (using SQL*Plus, for example) would not go through the checks and processing defined.

Database triggers differ from constraints in that they enable you to embed SQL statements within them, whereas constraints do not.

If possible, use constraints for checking; they are quicker than using database triggers.

System-Level Privileges

Each Oracle user defined on the database can have one or more of over 80 system-level privileges. These privileges control on a very fine level the right to execute SQL commands. The database administrator assigns system privileges either directly to Oracle user accounts or to roles. The roles are then assigned to the Oracle user accounts.

For example, before I can create a trigger on a table (even if I own the table as an Oracle user), I must have a system privilege called CREATE TRIGGER either assigned to my Oracle user account or assigned to a role given to the user account.

The CREATE SESSION privilege is another frequently used system-level privilege. In order to make a connection to the database, an Oracle account must have the CREATE SESSION system-level privilege assigned to it. This gives the account the privilege to make connections to the database.

Object-Level Privileges

Object-level privileges provide the capability to perform a particular type of action (select, insert, update, delete, and so on) on a specific object. The owner of the object has full control over the object and can perform any action on it; he doesn't need to have object-level privileges assigned to him. In fact, the owner of the object is the Oracle user who grants object-level privileges to others.

For example, if the user who owns a table wants another user to select and insert rows from his table (but not update or delete), he grants the select and insert object-level privileges on that table to the other user.

You can assign object-level privileges either directly to users or to roles that are then assigned to one or more Oracle user accounts.

Users and Roles

A role is a type of object that you can use to simplify the administration of system and object-level privileges. Instead of assigning privileges directly to user accounts, you can assign the privileges to roles that are then assigned to users.

Roles are essentially groupings of system and object-level privileges. They make the administration of privileges much easier because you can configure the privileges for a particular type of user once and assign those privileges to a role. When a user needs that set of privileges, you can use a single role assignment command to set that user up. Without the use of roles, you'd need to issue many commands for each of the different privileges required.

In addition, you can set up different roles with the correct privileges even though you don't yet have Oracle user accounts that require those assignments. You can assign a role to another role, building hierarchies of roles. Also, you can protect a role with a password that the user must supply when he wants to enable the role.

As already discussed, a physical database could contain many Oracle user accounts that are protected by passwords. You must supply the username and password regardless of which tool you use to gain access to the database. Roles are not the same as Oracle users; you cannot connect to the database by supplying a role name and password.

Auditing

Oracle's auditing mechanism provides three types of audit trails. One audit trail tracks which system privileges are used. Statement auditing keeps track of which SQL statements are used without regard to specific objects. Object-level auditing audits access to specific objects. You can initiate these audit trails to track when the statements succeed, when they fail, or both, so that all accesses are audited. You can use auditing to keep track of anyone attempting to break into the system.

In addition, you can set up how all the different types of auditing record the entries. The audit trail can record one entry per operation regardless of how many attempts are made on the operation during the connection session. Alternatively, request one entry in the audit trail for every attempt (successful or not) on the operation during the session.

If it's set up and enabled, the audit trail keeps the audit information in a data dictionary table owned by the user SYS. This table indicates the operation being audited, the user performing the operation, and the date and time of the operation. Oracle provides a set of data dictionary views to make the information in the dictionary audit table more meaningful. Although the audit trail is implemented in a data dictionary table, it keeps the insertion of rows in the audit trail even if the user rolls back his transaction.

The database administrator can clear out or archive the audit trail periodically.

Backup and Recovery

In this part, I discuss some of the options that the architecture gives you when it comes to backing up and recovering your database. See Chapter 14, "Backup and Recovery," for more detail.

Backup and Recovery Options

This section outlines at a high level some of the options available for backing up and restoring your database. I discuss the types of failure that can occur and the actions to take. The major part of this section describes preventive action to guard against loss of your database files.

This section discusses in theory the available options. The backup and recovery options mentioned here, along with other available options, are discussed in greater detail in Chapter 14.

Different Types of Failure

The major types of failure that can occur are statement failure, user process failure, machine failure, distributed transaction failure, instance failure, and disk failure/file loss.

Statement Failure

In Oracle, a DML statement such as UPDATE operates on either all the rows satisfying its where clause or none at all.

Failure with a statement occurs for a myriad of reasons. For example, when you insert rows into a table, the table might require more storage; if the database software discovers that no more free storage is available, it returns an error message to the user. Oracle does not leave only half the rows updated. Even if the failure occurs halfway through the statement, the rows already modified are "unmodified." This is known as statement-level rollback.

Note that other DML statements in the transaction remain in a pending state ready for a commit or rollback.

User Process Failure

A user process failure occurs when the user process making the connection to the database terminates abnormally during execution. For example, the system administrator could have killed the user process. If this does occur, the Oracle background process PMON automatically rolls back any changes for the current transaction. All changes already committed by the user process are saved, but inserts, updates, and deletes since the last commit or rollback are reversed.

Also, the PMON background process releases any locks, rollback segments, and other system resources acquired by the user process when it was alive. No database administrator involvement is necessary. The database continues to function as usual, and the tables are accessible to other users. (A slight delay could occur before the locks are released.)

Machine Failure

When the machine on which the database server is running fails and shuts down (the power is turned off, for example), the Oracle instance stops running. As long as no database files are lost, the only action required of the database administrator is restarting the Oracle instance. When you do this, the SMON background process reads the online redo log files and reapplies any changes for committed transactions. Any changes that had not been committed are rolled back.

Remember that a COMMIT statement writes only the changes to the redo log files; it does not write the database blocks back to disk at the point at which the commit was issued. If the database blocks with committed changes were written to the database files before the machine failure, the SMON background process obviously does not need to reapply the changes for those blocks.

Instance Failure

Instance failure occurs when the machine is still up and running but the Oracle instance itself fails (perhaps one of the background processes was killed). This situation is very similar to machine failure in that the database administrator needs only to restart the instance; the SMON process reapplies any changes. When restarting the instance after this kind of failure, you will notice no difference from when the instance is started after a normal shutdown.

Distributed Transaction Failure

A distributed transaction is one that involves changes to more than one database. If a failure occurs during a distributed transaction, the RECO background process (if it's running) automatically synchronizes the rollbacks of the transaction's changes across all the databases involved. Again, no manual intervention is required in all but the most serious cases. The database administrators of the instances involved in the distributed transaction can manually force the commit or rollback of the changes on their databases, but I recommend that you leave the recovery to the RECO background process if possible. This might not be possible if the links between the databases are not available for the RECO processes on all the instances to communicate.

Disk Failure/File Loss

The only time you really need to concern yourself with recovery is when you lose one or more of the files making up the database—the database files themselves, the control file, and the redo logs. Some type of manual recovery is necessary.

If you lose one or more of the files (database, redo, control), you have available the options highlighted in the following sections. In every situation, you must work with a previous backup of the database.

Cold Backup

A cold backup is when you copy the three sets of files (database files, redo logs, and control file) when the instance is shut down. This is a straight file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy. (It is possible to back up the files without bringing the instance down; see the section titled "Hot Backup.")

If you only perform a cold backup, the only option available in the event of data file loss is restoring all the files from the latest backup. All work performed on the database since the last backup is lost.

Archiving

If you've set up the database to run in ARCHIVELOG mode (easily done by the DBA), the database changes recorded in the redo logs are archived to an archive destination whenever the redo logs fill. Using this option, you have a complete record of changes made to the database files in the offline and online redo log files.

If you lose one or more of the database files, you could restore them from the last backup and reapply the changes since the last backup from the online and offline redo log files. You must have some kind of backup of the files and the complete set of online and offline redo logs from which to reapply all the changes made to the database.

With the archiving option, you lose no changes in the database if the complete set of redo logs is available. All the changes committed before the file was lost are reapplied. It is also possible to perform recovery if the control or redo log files are lost.

Hot Backups

Some sites (such as worldwide airline reservations systems) cannot shut down the database while making a backup copy of the files. The cold backup is not an available option.

You can use a different means of backing up your database—the hot backup. Issue a SQL command to indicate to Oracle, on a tablespace-by-tablespace basis, that you want to back up the files of the tablespace. The users can continue to make full use of the files, including making changes to the data. Once you have indicated that you want to back up the tablespace files, you can use your operating system to copy those files to your backup destination.

The database must be running in ARCHIVELOG mode for the hot backup option.

If a data loss failure does occur, you can restore the lost database files using the hot backup and the online and offline redo logs created since the backup was done. The database is restored to the most consistent state without any loss of committed transactions.

Export and Import

Along with the RDBMS software, Oracle provides two utilities that you can use to back up and restore the database. These utilities are useful to database administrators for system-wide backups and recoveries and also application developers for temporary backups of their own data and object recovery into their own user accounts.

The Export utility dumps the definitions and data for the specified part of the database to an operating system binary file. The Import utility reads the file produced by an export, recreates the definitions of objects, and inserts the data.

For a full database import, you must have an existing template database already created.

If you use Export and Import as a means of backing up and recovering the database, you cannot recover all the changes made to the database since the export was performed. This is similar to the situation with the cold backup. The best you can do is recover the database to the time when the export was last performed.

On large, data-filled databases, the Export and Import utilities can take a long time to run; many hours is not unusual. However, the utilities do provide an option to selectively export and import different user accounts and even objects within an Oracle user account.

Multiplexing

In this part, I discuss the options available for Oracle to duplicate data to provide an extra level of protection in the event of data loss.

Control Files

To protect against control file loss, the Oracle RDBMS software can maintain more than one copy of the control file. You do this by making a consistent copy of the existing control file and modifying an INIT.ORA parameter. This does not significantly impact the performance of the database. If all copies of the control file are lost, you can manually recreate them using the CREATE CONTROLFILE command.

Redo Logs

Redo logs record all the changes made to data blocks on the database. If the database is running in ARCHIVELOG mode and only the offline redo logs are lost, you should shut down the database and make another backup of the three sets of files for the database.

If the online redo logs are lost, however, you could lose some work because some of the information required to reapply changes to the database files is in the online redo log files. To guard against this, you can multiplex (mirror) the online redo log files in the same way as the control files. When the RDBMS software writes changes to one redo log, the exact same information is written to an exact copy of the redo log.

Distributed Databases

A distributed database is one logical database that is implemented as two or more physical databases on either the same machine or separate machines thousands of miles away. The system's designers decide where the tables should physically reside.

Each physical database has its own instance and sets of files, and the machines on which the databases reside are connected over a network. The location of tables can be made transparent to the application using database links and synonyms.

Oracle enables a transaction and even a single statement to access tables on two or more distributed databases. This does not necessitate any more coding by the application developers.

A distributed transaction is a transaction that modifies tables on more than one database and then expects all the changes to be committed. If there is any kind of failure, all the changes on all the databases are rolled back. A distributed transaction can involve many Oracle databases and only one non-Oracle database. The Oracle two-phase commit mechanism controls the synchronization of commits across all databases and can automatically roll back changes on all the databases if any kind of failure should occur. The RECO background process synchronizes this operation.

In addition to the this functionality, Oracle also provides the capability to replicate tables from one database to others. This is called creating a snapshot of the table.

You create a snapshot with the CREATE SNAPSHOT command on the database where you want to have the copy of the data. The Oracle RDBMS software automatically sends down any changes made to the master copy of the table to each of the snapshot copies at user-defined intervals without any manual intervention.

The snapshot mechanism enables you to make updates to the snapshot copy of the table, in which case the changes are sent from the copy table back to the master table.

Chapter 53, "Networking Oracle," discusses distributed databases in greater detail.

National Language Support

Oracle's national language support (NLS) enables users to use the database in their own languages. It provides the following functions:

Support for different encoding schemes, so that data created with an encoding scheme on one machine can be processed and displayed on another. You define the character set to be used for storing data on the database as part of the CREATE DATABASE statement. For example, data created on a database with the 7-bit U.S. ASCII standard character set can be displayed on a machine connected to the same database using the Chinese GB2312-8 character set. Translation tables within the national language support provide this support.
Control over the language used for server error and informational messages, numbers, dates, currency formats, and the starting day of the week.
Support for linguistic sort to ensure the characters appear in the correct order next to each other in a sort.
Definition of a NLS language either for the database as a whole or at the session level. With changes to the session parameters but without any changes to the Oracle user account, you could run some sessions with English, others German, others French, and so on, if the same Oracle username is connected to the database with many different sessions.

You can add support for new languages using the NLS*WorkBench product, which essentially maintains translation tables for interpreting input from the user and for displaying output.

When it comes to delivering application systems in different languages, the most important part of the user interface is the different prompts, boilerplate, and messages from the application. Currently, the application developers themselves define how the boilerplate, prompts, and messages from the application system change from one language to another. Oracle is working on a translation product to make this task easier.

Following a SQL Statement Through the Architecture

In this section, I bring together major parts of the Oracle architecture and follow the steps a typical SQL statement might go through to be executed. I use a simple scenario with both the Oracle SQL*Plus tool and the Oracle database server machine on a UNIX box without any networking involved. Using a single task configuration, the Oracle instance has just started.

The following shows some of the steps involved in executing SQL statements.

The user executes the SQL*Plus tool and enters the Oracle username and password.
Oracle validates the username and password against the data dictionary and sends a response to the user process to indicate connection.
The user enters a SELECT statement.
Oracle must translate the SELECT before it executes it so the Oracle parser and optimizer is called. If any user has issued exactly the same statement before, the parsed version might be in the shared pool area in memory. Oracle uses the parsed version, so no extra parsing is done for this statement.
To translate the SELECT statement, Oracle must obtain the names of the objects, privileges, and other information from the data dictionary. The data dictionary cache area in the shared pool in the SGA does not have the information on the tables, so parsing of the SELECT statement is suspended while the information is read in.
Oracle runs a recursive SQL statement (a system-generated statement) to load information about the objects from the data dictionary tables in the database files into the data dictionary cache in memory.
Parsing of the original user SELECT statement resumes, and Oracle constructs an optimization plan to control the way the statement runs.
The statement accesses a table. Assume the Oracle blocks for the table are not in the database buffer cache in the SGA. The required Oracle blocks are read in from the database files and held in the cache area of the SGA.
Oracle runs the statement and returns the results to the user.
The user issues an UPDATE statement to modify some of the fields on the rows he's just selected. Because the data dictionary cache already has the information about the table in memory, no more recursive SQL is generated (assuming that the information has not been flushed out by another process requiring space in the cache area). Also, the Oracle blocks for the table are in the database buffer cache, so you won't do another disk I/O to read these blocks in.
Oracle locks the rows to be updated.
Before Oracle makes the UPDATE, information about the old state of the blocks is recorded in a rollback segment and the original version of the values is also recorded in the redo buffers cache.
Oracle updates the rows and records the new version of the values in the redo buffer cache in the SGA.
The user issues the COMMIT command to make the change permanent to the database.
Oracle records an entry indicating a commit in the redo buffer cache, and the old, new, and the commit entry are all flushed down to the online redo log (whichever one is the current one in use).
The rollback segment is released (but not overwritten) for other processes to use.
Oracle releases the locks on the table.
The user receives a commit successful message (the exact wording of this message varies from tool to tool).
If the user issues a rollback instead of a commit, the old versions of the values changed are restored back to the Oracle data blocks from the rollback segment. Oracle also writes to the redo buffer cache an entry indicating that a rollback was performed.

Summary

In this chapter, I covered the major parts of the Oracle architecture—the architecture used by all the tools and commands against the database. Even though familiarity with the intricate workings of the architecture is not necessary for you to use the database and tools, it provides valuable background information in helping to work out why the database is not functioning as it should. Knowledge of the architecture also lends more meaning to some of the more cryptic error and information messages.