Persons you wana to know

Founders of Google

Persons Behind ORKUT

Linus Torvalds of Linux Operating System

Different Types Of Databases

Operational database
These databases store detailed data needed to support the operations of the entire organization. They are also called subject-area databases (SADB), transaction databases, and production databases. These are all examples:
Customer databases
Personal databases
Inventory databases
Analytical database
These databases stores data and information extracted from selected operational and external databases. They consist of summarized data and information most needed by an organizations manager and other end user. They may also be called multidimensional database, Management database, and Information database.
Data warehouse
A data warehouse stores data from current and previous years that has been extracted from the various operational databases of an organization. It is the central source of data that has been screened, edited, standardized and integrated so that it can be used by managers and other end user professionals throughout an organization
Distributed database
These are databases of local work groups and departments at regional offices, branch offices, manufacturing plants and other work sites. These databases can include segments of both common operational and common user databases, as well as data generated and used only at a user’s own site.
End-user database
These databases consist of a variety of data files developed by end-users at their workstations. Examples of these are collection of documents in spreadsheets, word processing and even downloaded files.
External database
These databases where access to external, privately owned online databases or data banks is available for a fee to end users and organizations from commercial services. Access to a wealth of information from external database is available for a fee from commercial online services and with or without charge from many sources in the internet.
Hypermedia databases on the web
These are set of interconnected multimedia pages at a web-site. It consists of home page and other hyperlinked pages of multimedia or mixed media such as text, graphic, photographic images, video clips, audio etc.
Navigational database
Navigational databases are characterized by the fact that objects in it are found primarily by following references from other objects. Traditionally navigational interfaces are procedural, though one could characterize some modern systems like XPath as being simultaneously navigational and declarative.
In-memory databases
In-memory databases are database management systems that primarily rely on main memory for computer data storage. It is contrasted with database management systems which employ a disk storage mechanism. Main memory databases are faster than disk-optimized databases since the internal optimization algorithms are simpler and execute fewer CPU instructions. Accessing data in memory provides faster and more predictable performance than disk. In applications where response time is critical, such as telecommunications network equipment that operates 9-11 emergency systems, main memory databases are often used.
Document-oriented databases
Document-oriented databases are computer programs designed for document-oriented applications. These systems may be implemented as a layer above a relational database or an object database. As opposed to relational databases, document-based databases do not store data in tables with uniform sized fields for each record. Instead, each record is stored as a document that has certain characteristics. Any number of fields of any length can be added to a document. Fields can also contain multiple pieces of data.
Real-time databases
A real-time database is a processing system designed to handle workloads whose state is constantly changing. This differs from traditional databases containing persistent data, mostly unaffected by time. For example, a stock market changes very rapidly and is dynamic. Real-time processing means that a transaction is processed fast enough for the result to come back and be acted on right away. Real-time databases are useful for accounting, banking, law, medical records, multi-media, process control, reservation systems, and scientific data analysis. As computers increase in power and can store more data, they are integrating themselves into our society and are employed in many applications.

Physical Structure

Physical Database Structures
The Oracle database consists of several types of operating system files, which it uses to manage itself. Although you call these files physical files to distinguish them from the logical entities they contain, understand that from an operating system point of view, even these files are not really physical, but rather logical components of the physical disk storage supported by the operating system.
Oracle Data Files
Data files are the way Oracle allocates space to the tablespaces that make up the logical database. The data files are the way Oracle maps the logical tablespaces to the physical disks. Each tablespace consists of one or more data files, which in turn belong to an operating system file system. Therefore, the total space allocated to all the data files in an Oracle database will give you the total size of that database.
When you create your database, your system administrator assigns you a certain amount of disk space based on your initial database sizing estimates. All you have at this point are the mount points of the various disks you are assigned (e.g., /prod01, /prod02, /prod03, and so on). You then need to create your directory structure on the various files. After you install your software and create the Oracle administrative directories, you can use the remaining file system space for storing database objects such as tables and indexes. The Oracle data files make up the largest part of the physical storage of your database. However, other files are also part of the Oracle physical storage, as you'll see in the following sections.
The Control File
Control files are key files that the Oracle DBMS maintains about the state of the database. They are a bit like a database within a database, and they include, for example, the names and locations of the data files and the all-important System Change Number (SCN), which indicates the most recent version of committed changes in the database. Control files are key to the functioning of the database, and recovery is difficult without access to an up-to-date control file. Oracle creates the control files during the initial database creation process.
Due to its obvious importance, Oracle recommends that you keep multiple copies of the control file. If you lose all your control files, you won't be able to bring up your database without re-creating the control file using special commands. You specify the name, location, and the number of copies of the control file in the initialization file (init.ora). Oracle highly recommends that you provide for more than one control file in the initialization file. Here's a brief list of the key information captured by the Oracle control file:
Checkpoint progress records
Redo thread records
Log file records
Data file records
Tablespace records
Log file history records
Archived log records
Backup set and data file copy records
Control files are vital during the time the Oracle instance is operating and during database recovery. During the normal operation of the database, the control file is consulted periodically for necessary information on data files and other things. Note that the Oracle server process continually updates the control file during the operation of the database. During recovery, it's the control file that has the data file information necessary to bring the database up to a stable mode.
The Redo Log Files
The Oracle redo log files are vital during the recovery of a database. The redo log files record all the changes made to the database. In case you need to restore your database from a backup, you can recover the latest change made to the database from the redo log files.
Redo log files consist of redo records, which are groups of change vectors, each referring to a distinct atomic change made to a data block in the Oracle database. Note that a single transaction may have more than one redo record. If your database comes down without warning, the redo log helps you determine if all transactions were committed before the crash or if some were still incomplete. Initially, the contents of the log will be in the redo log buffer (a memory area), but they will be transferred to disk very quickly.
Oracle redo log files contain the following information regarding database changes made by transactions:
Indicators to let you know when the transaction started
Name of the transaction
Name of the data object (e.g., an application table) that was being updated
The "before image" of the transaction (i.e., the data as it was before the changes were made)
The "after image" of the transaction (i.e., the data after the transaction changed the data)
Commit indicators that let you know if and when the transaction completed

Logical Structure

The Logical Structures
Oracle9i uses a set of logical database storage structures to use disk space, whether the database uses operating system files or "raw" database files. These logical structures, which primarily include tablespaces, segments, extents, and blocks, control the allocation and use of the "physical" space allocated to the Oracle database. Note that Oracle database objects such as tables, indexes, views, sequences, and others are also logical entities. These logical objects make up the relational design of the database, which I discuss in detail later on.
You can look at the logical composition of Oracle databases from either a top-down viewpoint or a bottom-up viewpoint. Let's use the bottom-up approach by first looking at the smallest logical components of an Oracle database and progressively move up to the largest entities. Before you begin learning about the logical components, remember that Oracle database objects such as tables, indexes, and packaged SQL code are actually logical entities. Taken together, a set of related database logical objects is called a schema. Dividing a database's objects among various schemas promotes ease of management and a higher level of security.
The Oracle data block is the foundation of the database storage hierarchy. The Oracle data block is the basis of all database storage in an Oracle database. Two or more contiguous Oracle data blocks form an extent. A set of extents that you allocate to a table or an index (or some other object) is termed a segment. A tablespace is a set of one or more data files, and it usually consists of related segments. The following sections explore each of these logical database structures in detail.
Data Blocks
The smallest logical component of an Oracle database is a data block. Data blocks are defined in terms of operating system bytes, and they are the smallest unit of space allocation in an Oracle database. For example, you can size an Oracle data block in units of 2KB, 4KB, 8KB, 16KB, or 32KB (or ever larger chunks). It is common to refer to the data blocks as Oracle blocks.
The storage disks on which the Oracle blocks reside are themselves divided into disk blocks, which are areas of contiguous storage containing a certain number of bytes—for example, 4096 or 32768 bytes (4KB or 32KB, because each kilobyte has 1024 bytes). Note that if the Oracle block size is smaller than the operating system file system buffer size, you may be wasting the capacity of the operating system to read and write larger chunks of data for each I/O. Ideally, the Oracle block size should be a multiple of the operating system block size. On an HP-UX system, for example, if you set your Oracle block size to a multiple of the operating system block size, you gain 5 percent in performance.
Multiple Oracle Block Sizes
The initialization parameter db_size determines the size of the Oracle data block in the database. Unlike previous Oracle database versions, Oracle9i lets you specify up to four additional nonstandard block sizes in addition to the standard block size. Thus, you can have a total of five different block sizes in an Oracle9i database. For example, you can have 2KB, 4KB, 8KB, 16KB, and 32KB block sizes all within the same database. If you choose to configure multiple Oracle block sizes, you must also configure corresponding subcaches in the buffer cache of the System Global Area (SGA, Oracle's memory allocation), as you'll see shortly. Multiple data block sizes aren't always necessary, and you'll do just fine with one standard Oracle block size.
Extents
When you combine several contiguous data blocks, you get an extent. Remember that you can allocate an extent only if you can find enough contiguous data blocks. Your choice of tablespace type will determine how the Oracle database allocates the extents. The traditional dictionary-managed tablespaces allow you to specify both the beginning allocation of space and further increments as needed.
All database objects are allocated an initial amount of space, called the initial extent, when they are created. When you create an object, you specify the size of the next and subsequent extents as well as the maximum number of extents for that object, in the object creation statement. Once allocated to a table or index, the extents remain allocated to the particular object, unless you drop the object from the database, at which time the space will revert to the pool of allocatable free space in the database.
The locally managed tablespaces (explained later in this chapter) use the simpler method of allocating a uniform extent size, which is chosen automatically by the database. Therefore, you don't have to worry about setting the actual sizes for future allocation of extents to any particular tablespace.
Segments
A set of extents forms the next higher unit of data storage, the segment. In an Oracle database you can store different kinds of objects, such as tables and indexes, as you'll see in the next few chapters. Oracle calls all the space allocated to any particular database object a segment. That is, if you have a table called customer, you simply refer to the space you allocate to it as the "customer segment."
Each object in the database has its own segment. For example, the customer table is associated with the customer segment. When you create a table called customer, Oracle will create a new segment named customer also and allocate a certain number of extents to it based on the table creation specifications for the customer table. When you create an index, it will have its own segment named after the index name.
You can have several types of segments, with the most common being the data segment and the index segment. Any space you allocate to these two types of segments will remain intact even if you truncate (remove all rows) a table, for example, as long as the object is part of the database. However, as you'll see later on, there's something called a temporary tablespace in Oracle, and Oracle deallocates the temporary segments as soon the transactions or sessions that are using the segments are completed.
Tablespaces
A tablespace is defined as the combination of a set of related segments. For example, all the data segments belonging to the out-of-town sales team can be grouped into a tablespace called out_of_town_sales. You can have other segments belonging to other types of data in the same tablespace, but usually you try to keep related information together in the same tablespace. Note that the tablespace is a purely logical construct, and it is the primary logical structure of an Oracle database. All tablespaces need not be of the same size within a database. For example, it is quite common to have tablespaces that are 100GB in size coexisting in the same database with tablespaces as small as 1GB. The size of the tablespace depends on the current and expected size of the objects included in the various segments of the tablespace.
As you have already seen, Oracle9i lets you have multiple block sizes, in addition to the default block size. Because tablespaces ultimately consist of data blocks, this means that you can have tablespaces with different block sizes in the same database. This is a great new feature, and it gives you the opportunity to pick the right block size for a tablespace based on the data structure of the tables within that tablespace. This customization of the block size for tablespaces provides several benefits:
· Optimal disk I/O: Remember that the Oracle server has to read the table data from mechanical disks into the buffer cache area for processing. One of your primary goals as a DBA is to optimize the expensive I/O involved in the reads and writes to disk. If you have tables that have very long rows, you're better off with a larger block size. Each read, for example, will fetch more data than with a smaller block size, and you'll need fewer read operations to get the same amount of data. Tables with large object (LOB) data will also benefit from a very high block size. Similarly, tables with small row lengths can have a small block size as the building block for the tablespace. If you have large indexes in your database, you need to have a large block size for their tablespace, so each read will fetch a larger number of index pointers for the data.
· Optimal caching of data: The Oracle9i feature of separate pools for the various block sizes leads to a better use of the buffer cache area.
· Easier to transport tablespaces: If you have tablespaces with multiple block sizes, it's easier to use the "transport tablespaces" feature.
Each Oracle tablespace consists of one or more operating system (or raw) files called data files, and a data file can only belong to one tablespace. An Oracle database can have any number of tablespaces in it. You could manage a database with ten or several hundred tablespaces, all sized differently. However, for every Oracle database, you'll need a minimum of two tablespaces: the System tablespace and the temporary tablespace. Later on, you can add and drop tablespaces as you wish, but you can't drop the System tablespace. At database creation time, the only tablespace you must have is the System tablespace, which contains Oracle's data dictionary. However, users need a temporary location to perform certain activities such as sorting, and if you don't provide a default temporary tablespace for them, they end up doing the temporary sorting in the System tablespace.
When you consider the importance of the System tablespace, which contains the data dictionary tables along with other important information, it quickly becomes obvious why you must have a temporary tablespace. Oracle9i allows you to create this temporary tablespace at database creation time, and all users will automatically have this tablespace as their default temporary tablespace. In addition, if you choose the Oracle-recommended Automatic Undo Management over the traditional manual rollback segment management mode, you'll also need to create the undo tablespace at database creation time. Thus, although only the System tablespace is absolutely required by Oracle, your database should have at least the System, temporary, and undo tablespaces when you initially create it.

9i Certification

Oracle Cerified Associate

1Z0-007 Introduction to Oracle9i SQL®
1Z0-047 Oracle Database SQL Expert
1Z0-051 Oracle Database 11g: SQL Fundamentals I
+
Database: Fundamentals I1Z0-031

Oracle Certified Professional

Oracle Cerified Associate
+
Oracle9i Database: Fundamentals II1Z0-032
+
Database: Performance Tuning1Z0-033
+
the Hands On Course Requirement Form

11g Certification

Oracle Certified Associate

1Z0-007Introduction to Oracle9i SQL®
or 1Z0-047Oracle Database SQL Expert
or1Z0-051Oracle Database 11g: SQL Fundamentals
+
Oracle Database 11g: Administration I1Z0-052

Oracle Certified Professional

Oracle Certified Associate
+
Oracle Database 11g: Administration II1Z0-053
+
Submit the Hands On Course Requirement Form

10 Certification

Oracle Certified Associate

1Z0-007 Introduction to Oracle9i SQL®
or
1Z0-047 Oracle Database SQL Expert
or
1Z0-051 Oracle Database 11g: SQL Fundamentals I
+
Oracle Database 10g: Administration I 1Z0-042

Oracle Cerified Prefessional

Oracle Certified Associate
+
Oracle Database 10g: Administration II1Z0-043
+
Submit the Hands On Course Requirement Form

Data Pump

Data Pump is the new high-speed infrastructure for data and metadata movement in Oracle Database 10g. The Data Pump commands are similar to the traditional export and import commands, but they are separate products. Data Pump provides a remarkable performance improvement over the original export and import utilities. It also provides faster data load and unload capability to existing tables. Using Data Pump, platform-independent flat files can be moved between multiple servers. You can use the new network mode to transfer data using database links.
Export and import operations in Data Pump can detach from a long-running job and reattach to it later with out affecting the job. You can also remap data during export and import processes. The names of data files, schema names, and tablespaces from the source can be altered to different names on the target system. It also supports fine-grained object selection using the EXCLUDE, INCLUDE, and CONTENT parameters.
Data Pump Export (dpexp) is the utility for unloading data and metadata from the source database to a set of operating system files (dump file sets). Data Pump Import (dpimp) is used to load data and metadata stored in these export dump file sets to a target database.
The advantages of using Data Pump utilities are as follows.
You can detach from a long-running job and reattach to it later without affecting the job. The DBA can monitor jobs from multiple locations, stop the jobs, and restart them later from where they were left.
Data Pump supports fine-grained object selection using the EXCLUDE, INCLUDE, and CONTENT parameters. This will help in exporting and importing a subset of data from a large database to development databases or from data warehouses to datamarts, and so on.
You can control the number of threads working for the Data Pump job and control the speed (only in the Enterprise version of the database).
You can remap data during the export and import processes.
In Oracle Database 10g Release 2, a default DATA_PUMP_DIR directory object and additional DBMS_DATAPUMP API calls have been added, along with provisions for compression of metadata in dump files, and the capability to control the dump file size with the FILESIZE parameter.

Oracle 10g Processes

Background Processes Revisited

An Oracle database instance can have many background processes, but all of them are not always needed. When the database instance is started, these background processes are automatically created. The important background processes are given next, with brief explanations of each:
Database Writer process (DBWn). The database writer process (DBWn) is responsible for writing modified (dirty) buffers in the database buffer cache to disk. Although one process (DBW0) is sufficient for most systems, you can have additional processes up to a maximum of 20 processes (DBW1 through DBW9 and DBWa through DBWj) to improve the write performance on heavy online transaction processing (OLTP) systems. By moving the data in dirty buffers to disk, DBWn improves the performance of finding free buffers for new transactions, while retaining the recently used buffers in the memory.
Log Writer process (LGWR). The log writer process (LGWR) manages the redo log buffers by writing the redo log buffer to a redo log file on disk in a circular fashion. After the LGWR moves the redo entries from the redo log buffer to a redo log file, server processes can overwrite new entries in to the redo log buffer. It writes fast enough to disk to have space available in the buffer for new entries.
Checkpoint process (CKPT). Checkpoint is the database event to synchronize the modified data blocks in memory with the data files on disk. It helps to establish data consistency and allows faster database recovery. When a checkpoint occurs, Oracle updates the headers of all data files using the CKPT process and records the details of the checkpoint. The dirtied blocks are written to disk by the DBWn process.
System Monitor process (SMON). SMON coalesces the contiguous free extents within dictionary managed tablespaces, cleans up the unused temporary segments, and does the database recovery at instance startup (as needed). During the instance recovery, SMON also recovers any skipped transactions. SMON checks periodically to see if the instance or other processes need its service.
Process Monitor process (PMON). When a user process fails, the process monitor (PMON) does process recovery by cleaning up the database buffer cache and releasing the resources held by that user process. It also periodically checks the status of dispatcher and server processes, and restarts the stopped process. PMON conveys the status of instance and dispatcher processes to the network listener and is activated (like SMON) whenever its service is needed.
Job Queue processes (Jnnn). Job queue processes run user jobs for batch processing like a scheduler service. When a start date and a time interval are assigned, the job queue processes run the job at the next occurrence of the interval. These processes are dynamically managed, allowing the job queue clients to demand more job queue processes (J000J999) when needed. The job queue processes are spawned as required by the coordinator process (CJQ0 or CJQnn) for completion of scheduled jobs.
Archiver processes (ARCn). The archiver processes (ARCn) copy the redo log files to an assigned destination after the log switch. These processes are present only in databases in ARCHIVELOG mode. An Oracle instance can have up to 10 ARCn processes (ARC0ARC9). Whenever the current number of ARCn processes becomes insufficient for the workload, the LGWR process invokes additional ARCn processes, which are recorded in the alert file. The number of archiver processes is set with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES and changed with the ALTER SYSTEM command.
Queue Monitor processes (QMNn). The queue monitor process is an optional background process that monitors the message queues for Oracle Advanced Queuing in Streams.
Memory Monitor process (MMON). MMON is the acronym for Memory Monitor, a new process introduced in Oracle Database 10g associated with the Automatic Workload Repository (AWR). AWR gets the necessary statistics for automatic problem detection and tuning with the help of MMON. MMON writes out the required statistics for AWR to disk on a regularly scheduled basis.
Memory Monitor Light process (MMNL). Memory Monitor Light (MMNL) is another new process in Oracle Database 10g; it assists the AWR with writing the full statistics buffers to disk on an as-needed basis.

Automatic Storage Management

Automatic Storage Management

Oracle Database 10g introduces Automatic Storage Management (ASM), a service that provides management of disk drives. ASM can be used on a variety of configurations, including Oracle9i Database RAC installations. ASM is an alternative to the use of raw or cooked file systems and is part of Oracle's overall desire to make management of the Oracle Database 10g easier, overall.

ASM Features

ASM offers a number of features, including:

Simplified daily administration

The performance of raw disk I/O for all ASM files

Compatibility with any type of disk configuration, be it just a bunch of disks (JBOD) or complex Storage Area Network (SAN)

Use of a specific file-naming convention to name files, enforcing an enterprise-wide file-naming convention

Prevention of the accidental deletion of files, since there is no file system interface and ASM is solely responsible for file management

Load balancing of data across all ASM managed disk drives, which helps improve performance by removing disk hot spots

Dynamic load balancing of disks as usage patterns change and when additional disks are added or removed

Ability to mirror data on different disks to provide fault tolerance

Flashback Feature

The term flashback was first introduced with Oracle 9i in the form of the new technology known as flashback query. When enabled, flashback query provided a new alternative to correcting user errors, recovering lost or corrupted data, or even performing historical analysis without all the hassle of point-in-time recovery.

The new flashback technology was a wonderful addition to Oracle 9i, but it still had several dependencies and limitations. First, flashback forced you to use Automatic Undo Management (AUM) and to set the UNDO_RETENTION parameter. Second, you needed to make sure your flashback users had the proper privileges on the DBMS_FLASHBACK package. After these prerequisite steps were completed, users were able to use the flashback technology based on the SCN (system change number) or by time. However it's important to note that regardless of whether users used the time or SCN method to enable flashback, Oracle would only find the flashback copy to the nearest five-minute internal. Furthermore, when using the flashback time method in a database that had been running continuously, you could never flashback more than five days, irrespective of your UNDO_RETENTION setting. Also, flashback query did not support DDL operations that modified any columns or even any drop or truncate commands on any tables.

Oracle 9i offers an unsupported method for extending the five-day limit of UNDO_RETENTION. Basically, the SCN and timestamp are stored in SYS.SMON_SCN_TIME, which has only 1,440 rows by default (5 daysx24 hoursx12 months = 1,440). At each five-minute interval, the current SCN is inserted into SMON_SCN_TIME, while the oldest SCN is removed, keeping the record count at 1,440 at all times. To extend the five-day limit, all you need to do is insert new rows to the SMON_SCN_TIME table. Oracle 10g extends this limit to eight days by extending the default record count of SMON_SCN_TIME to 2,304 (8 daysx24 hoursx12 months = 2,304).

Oracle 10g has evolved the flashback technology to cover every spectrum of the database. Following is a short summary of the new flashback technology that is available with Oracle 10g:

• Flashback Database. Now you can set your entire database to a previous point in time by undoing all modifications that occurred since that time without the need to restore from a backup.

• Flashback Table. This feature allows you to recover an entire table to a point in time without having to restore from a backup.

• Flashback Drop. This feature allows you to restore any accidentally dropped tables.

• Flashback Versions Query. This feature allows you to retrieve a history of changes for a specified set of rows within a table.

• Flashback Transaction Query. This feature allows users to review and/or recover modifications made to the database at the transaction level.

Oracle Database Proccesses

Oracle Processes
Oracle server processes running on the operating system perform all the database operations, such as inserting and deleting data. These Oracle processes, together with the memory structures allocated to Oracle by the operating system, form the working Oracle instance. There is a set of mandatory Oracle processes that need to be up and running for the database to function at all. In addition, other processes exist that are necessary only if you are using certain specialized features of the Oracle databases (e.g., replicated databases). This section's focus is on the set of mandatory processes that every Oracle instance uses to perform database activities.
Processes are essentially a connection or thread to the operating system that performs a task or job. The Oracle processes you'll encounter in this section are continuous, in the sense that they come up when the instance starts, and they stay up for the duration of the instance's life. Thus, they act like Oracle's "hooks" into the operating system resources. Note that what I call a process on a UNIX system is analogous to a thread on a Windows system.
Oracle instances need two different types of processes to perform various types of database activities. This distinction is made for efficiency purposes and the need to keep client processes separate from the database server's tasks. The first set of processes is called user processes. These processes are responsible for running the application that connects the user to the database instance. The second and more important set of processes is called the Oracle processes. These processes perform the Oracle server's tasks, and you can divide the Oracle processes into two major categories: the server processes and the background processes. Together, these processes perform all the actual work of the database, from managing connections to writing to the logs and data files to monitoring the user processes.
Interaction Between the User and Oracle Processes
User processes execute program code of application programs and that of Oracle tools such as SQL*Plus. The user processes communicate with the server processes through the user interface. These processes request that the Oracle server processes perform work on their behalf. Oracle responds by using its server processes to service the user processes' requests.
When you connect to an Oracle database, you have to use software such as SQL Net, which is Oracle's proprietary networking tool. Regardless of how you connect and establish a session with the database instance, Oracle has to manage these connections. The Oracle server creates server processes to manage the user connections. It's the job of the server processes to monitor the connection, accept requests for data, and so forth from the database, and hand the results back from the database itself. All selects, for example, involve reading data from the database, and it's the server processes that bring the output of the select statement back to the users.
You'll examine the two types of Oracle processes, the server processes and the background processes, in detail in the following sections.
The Server Process
The server process is the process that services the user process. The server process is responsible for all interaction between the user and the database. When the user submits requests to select data, for example, the server process checks the syntax of the code and executes the SQL code. It will then read the data from the data files into the memory blocks. If another user intends to read the same data, the new user's server process will read it not from disk again, but from Oracle's memory, where it usually remains for a while. Finally, the server process is also responsible for returning the requested data to the user.
The most common configuration for the server process is where you assign each user a dedicated server process. However, Oracle provides for a more sophisticated means of servicing several users through the same server process called the shared server architecture, which enables you to service a large number of users efficiently. A shared server process enables a large number of simultaneous connections using fewer server processes, thereby conserving critical system resources such as memory.
The Background Processes
The background processes are the real workhorses of the Oracle instance. These processes enable large numbers of users to use the information that is stored in the database files. By being continuously hooked into the operating system, these processes relieve Oracle software from having to constantly start numerous, separate processes for each task that needs to be done on the operating system server.
BACKGROUND PROCESS
PROCESS FUNCTION
Database writer
Writes modified data from buffer cache to disk (data files)
Log writer
Writes redo log buffer contents to online redo log files
Checkpoint
Signals database to flush its contents to disk
Process monitor
Cleans up after finished and failed processes
System monitor
Performs crash recovery and coalesces extents
Archiver
Archives filled online redo log files
Recoverer
Used only in distributed databases
Dispatcher
Used only in shared server configurations
Coordination job
Coordinates job queues to expedite job processes queue process
I discuss in detail the main Oracle background processes in the following sections.
The Database Writer
The database writer (DBW) process is responsible for writing data from the memory areas known as database buffers to the actual data files on disk. It is the database writer process's job to monitor the usage of the database buffer cache and help manage it. For example, if the free space in the database buffer area is getting low, the database writer process has to make room there by writing some of the data in the buffers to the disk files. The database writer process uses an algorithm called least recently used (LRU), which basically writes data to the data files on disk based on how long it has been since someone asked for it. If the data has been sitting in the buffers for a very long time, chances are it is "dated" and the database writer process clears that portion of the database buffers by writing the data to disk.

The Process Monitor
The process monitor (PMON) process cleans up after failed user processes and the server processes. Essentially, when processes die, the PMON process ensures that the database frees up the resources that the dead processes were using. For example, when a user process dies while holding certain table locks, the PMON process would release those locks, so other users could use them without any interference from the dead process. In addition, the PMON process restarts failed server processes. The PMON process sleeps most of the time, waking up to see if it is needed. Other processes will also wake up the PMON process if necessary.
In Oracle9i, the PMON process automatically performs dynamic service registration. When you create a new database instance, the PMON process registers the instance information with the listener. (The listener is the entity that manages requests for database connections. This dynamic service registration eliminates the need to register the new service information in the listener.ora file, which is the configuration file for the listener.
The System Monitor
The system monitor (SMON) process, as its name indicates, performs system monitoring tasks for the Oracle instance. The SMON process performs crash recovery upon the restarting of an instance that crashed. The SMON process determines if the database is consistent following a restart after an unexpected shutdown. This process is also responsible for coalescing free extents if you happen to use dictionary managed tablespaces. Coalescing free extents will enable you to assign larger contiguous free areas on disk to your database objects. In addition, the SMON process cleans up unnecessary temporary segments. Like the PMON process, the SMON process sleeps most of the time, waking up to see if it is needed. Other processes will also wake up the SMON process if they detect a need for it.
The Archiver
The archiver (ARCH) process is used when the system is being operated in an archive log mode—that is, the changes logged to the redo log files are being saved and not being overwritten by new changes. The archiver process will archive the redo log files to a specified location, assuming you chose the automatic archiving option in the init.ora file. If a huge number of changes are being made to your database, and consequently your logs are filling up at a very rapid pace, you can use multiple archiver processes up to a maximum of ten. The parameter log_archive_maximum_processes in the initialization file will determine how many archiver processes Oracle will invoke. If the log writer process is writing logs faster than the default single archiver process can archive them, it will be necessary to enable more than one archiver process.
Note that besides the processes discussed here, there are other Oracle background processes that perform specialized tasks that may be running in your system. For example, if you use Oracle Real Application Clusters (ORAC), you'll see a background process called the lock (LCKn) process, which is responsible for performing interinstance locking. If you use the Oracle Advanced Replication Option, you'll notice the background process called recoverer (RECO), which recovers terminated transactions in a distributed database environment.

Oracle 10g New Features

SYSAUX Tablespace
The SYSAUX tablespace is an auxiliary tablespace that provides storage for all non sys-related tables and indexes that would have been placed in the SYSTEM tablespace. SYSAUX is required for all Oracle Database 10g installations and should be created along with new installs or upgrades. Many database components use SYSAUX as their default tablespace to store data. The SYSAUX tablespace also reduces the number of tablespaces created by default in the seed database and user-defined database. For a RAC implementation with raw devices, a raw device is created for every tablespace, which complicates raw-device management. By consolidating these tablespaces into the SYSAUX tablespace, you can reduce the number of raw devices. The SYSAUX and SYSTEM tablespaces share the same security attributes.
When the CREATE DATABASE command is executed, the SYSAUX tablespace and its occupants are created. When a database is upgraded to 10g, the CREATE TABLESPACE command is explicitly used. CREATE TABLESPACE SYSAUX is called only in a database-migration mode.
Rename Tablespace Option
Oracle Database 10g has implemented the provision to rename tablespaces. To accomplish this in older versions, you had to create a new tablespace, copy the contents from the old tablespace to the new tablespace, and drop the old tablespace. The Rename Tablespace feature enables simplified processing of tablespace migrations within a database and ease of transporting a tablespace between two databases. The rename tablespace feature applies only to database versions 10.0.1 and higher. An offline tablespace, a tablespace with offline data files, and tablespaces owned by SYSTEM or SYSAUX cannot be renamed using this option. As an example, the syntax to rename a tablespace from REPORTS to REPORTS_HISTORY is as follows.
SQL> ALTER TABLESPACE REPORTS RENAME TO REPORTS_HISTORY;
Automatic Storage Management
Automatic Storage Management (ASM) is a new database feature for efficient management of storage with round-the-clock availability. It helps prevent the DBA from managing thousands of database files across multiple database instances by using disk groups. These disk groups are comprised of disks and resident files on the disks. ASM does not eliminate any existing database functionalities with file systems or raw devices, or Oracle Managed Files (OMFs). ASM also supports RAC configurations.
In Oracle Database 10g Release 2, the ASM command-line interface (ASMCMD) has been improved to access files and directories within ASM disk groups. Other enhancements include uploading and extracting of files into an ASM-managed storage pool, a WAIT option on disk rebalance operations, and the facility to perform batch operations on multiple disks.
Now it's time to take a deeper look into the new storage structures in Oracle Database 10gtemporary tablespace groups and BigFile tablespaces. These will help you to better utilize the database space and to manage space more easily.
Temporary Tablespace Group
A temporary tablespace is used by the database for storing temporary data, which is not accounted for in recovery operations. A temporary tablespace group (TTG) is a group of temporary tablespaces. A TTG contains at least one temporary tablespace, with a different name from the tablespace. Multiple temporary tablespaces can be specified at the database level and used in different sessions at the same time. Similarly, if TTG is used, a database user with multiple sessions can have different temporary tablespaces for the sessions. More than one default temporary tablespace can be assigned to a database. A single database operation can use multiple temporary tablespaces in sorting operations, thereby speeding up the process. This prevents large tablespace operations from running out of space.
In Oracle Database 10g, each database user will have a permanent tablespace for storing permanent data and a temporary tablespace for storing temporary data. In previous versions of Oracle, if a user was created without specifying a default tablespace, SYSTEM tablespace would have become the default tablespace. For Oracle Database 10g, a default permanent tablespace can be defined to be used for all new users without a specific permanent tablespace. By creating a default permanent tablespace, nonsystem user objects can be prevented from being created in the SYSTEM tablespace. Consider the many benefits of adding users in a large database environment by a simple command and not having to worry about them placing their objects in the wrong tablespaces!
BigFile Tablespace
We have gotten past the days of tablespaces in the range of a few megabytes. These days, database tables hold a lot of data and are always hungry for storage. To address this craving, Oracle has come up with the Bigfile tablespace concept. A BigFile tablespace (BFT) is a tablespace containing a single, very large data file. With the new addressing scheme in 10g, four billion blocks are permitted in a single data file and file sizes can be from 8TB to 128TB, depending on the block size. To differentiate a regular tablespace from a BFT, a regular tablespace is called a small file tablespace. Oracle Database 10g can be a mixture of small file and BigFile tablespaces.
BFTs are supported only for locally managed tablespaces with ASM segments and locally managed undo and temporary tablespaces. When BFTs are used with Oracle Managed Files, data files become completely transparent to the DBA and no reference is needed for them. BFT makes a tablespace logically equivalent to data files (allowing tablespace operations) of earlier releases. BFTs should be used only with logical volume manager or ASM supporting dynamically extensible logical volumes and with systems that support striping to prevent negative consequences on RMAN backup parallelization and parallel query execution. BFT should not be used when there is limited free disk space available.
Prior to Oracle Database 10g, K and M were used to specify data file sizes. Because the newer version introduces larger file sizes up to 128TB using BFTs, the sizes can be specified using G and T for gigabytes and terabytes, respectively. Almost all the data warehouse implementations with older versions of Oracle database utilize data files sized from 16GB to 32GB. With the advent of the BigFile tablespaces, however, DBAs can build larger data warehouses without getting intimidated by the sheer number of smaller data files
Oracle block size Maximum file size
2KB 8TB
4KB 16TB
8KB 32TB
16KB 64TB
32KB 128TB
For using BFT, the underlying operating system should support Large Files. In other words the file system should have Large File Support (LFS).
Cross-Platform Transportable Tablespaces
In Oracle 8i database, the transportable tablespace feature enabled a tablespace to be moved across different Oracle databases using the same operating system. Oracle Database 10g has significantly improved this functionality to permit the movement of data across different platforms. This will help transportable tablespaces to move data from one environment to another on selected heterogeneous platforms (operating systems). Using cross-platform transportable tablespaces, a database can be migrated from one platform to another by rebuilding the database catalog and transporting the user tablespaces. By default, the converted files are placed in the flash recovery area (also new to Oracle Database 10g), which is discussed later in this chapter. A list of fully supported platforms can be found in v$transportable_platform.
A new data dictionary view, v$transportable_platform, lists all supported platforms, along with the platform ID and endian format information. The v$database dictionary view has two new columns (PLATFORM_ID, PLATFORM_NAME) to support it.
The table v$TRansportable_platform has three fields: PLATFORM_ID (number), PLATFORM_NAME (varchar2(101)), and ENDIAN_FORMAT (varchar2(14)). Endianness is the pattern (big endian or little endian) for byte ordering of data files in native types. In big-endian format, the most significant byte comes first; in little-endian format, the least significant byte comes first.

Performance Management Using AWR
Automatic Workload Repository (AWR) is the most important feature among the new Oracle Database 10g manageability infrastructure components. AWR provides the background services to collect, maintain, and utilize the statistics for problem detection and self-tuning. The AWR collects system-performance data at frequent intervals and stores them as historical system workload information for analysis. These metrics are stored in the memory for performance reasons. These statistics are regularly written from memory to disk by a new background process called Memory Monitor (MMON). This data will later be used for analysis of performance problems that occurred in a certain time period and to do trend analysis. Oracle does all this without any DBA intervention. Automatic Database Diagnostic Monitor (ADDM), which is discussed in the next section, analyzes the information collected by the AWR for database-performance problems.
By default, the data-collection interval is 60 minutes, and this data is stored for seven days, after which it is purged. This interval and data-retention period can be altered.
This captured data can be used for system-level and user-level analysis. This data is optimized to minimize any database overhead. In a nutshell, AWR is the basis for all self-management functionalities of the database. It helps the database with the historical perspective on its usage, enabling it to make accurate decisions quickly.
The AWR infrastructure has two major components:
In-memory statistics collection. The in-memory statistics collection facility is used by 10g components to collect statistics and store them in memory. These statistics can be read using v$performance views. The memory version of the statistics is written to disk regularly by a new background process called Memory Monitor or Memory Manageability Monitor (MMON). We will review MMON in background processes as well.
AWR snapshots. The AWR snapshots form the persistent portion of the Oracle Database 10g manageability infrastructure. They can be viewed through data dictionary views. These statistics retained in persistent storage will be safe even in database instance crashes and provide historical data for baseline comparisons.
AWR collects many statistics, such as time model statistics (time spent by the various activities), object statistics (access and usage statistics of database segments), some session and system statistics in v$sesstat and v$sysstat, some optimizer statistics for self-learning and tuning, and Active Session History (ASH). We will review this in greater detail in later chapters.
Automatic Database Diagnostic Monitor (ADDM)
Automatic Database Diagnostic Monitor (ADDM) is the best resource for database tuning. Introduced in 10g, ADDM provides proactive and reactive monitoring instead of the tedious tuning process found in earlier Oracle versions. Proactive monitoring is done by ADDM and Server Generated Alerts (SGAs). Reactive monitoring is done by the DBA, who does manual tuning through Oracle Enterprise Manager or SQL scripts.
Statistical information captured from SGAs is stored inside the workload repository in the form of snapshots every 60 minutes. These detailed snapshots (similar to STATSPACK snapshots) are then written to disk. The ADDM initiates the MMON process to automatically run on every database instance and proactively find problems.
Whenever a snapshot is taken, the ADDM triggers an analysis of the period corresponding to the last two snapshots. Thus it proactively monitors the instances and detects problems before they become severe. The analysis results stored in the workload repository are accessible through the Oracle Enterprise Manager. In Oracle Database 10g, the new wait and time model statistics help the ADDM to identify the top performance issues and concentrate its analysis on those problems. In Oracle Database 10g Release 2, ADDM spans more server components like Streams, RMAN, and RAC.
DROP DATABASE Command
Oracle Database 10g has introduced a means to drop the entire database with a single command: DROP DATABASE. The DROP DATABASE command deletes all database files, online log files, control files, and the server parameter (spfile) file. The archive logs and backups, however, have to be deleted manually.

Shutting Down Database

You may need to shut down a database for a number of reasons, for example, for some types of backup, for upgrades of software, and so on. You have several options for shutting down a running database. The option you choose has several implications for the time it takes to shut down the database and any potential need of database instance recovery upon a consequent starting up of the database. The following sections cover the four available shutdown command options for the Oracle9i database.

Shutdown Normal

When you issue the shutdown normal command to shut the database down, Oracle will wait for all users to disconnect from the database before shutting the database down. That is, if a user goes on vacation for a week after logging into a database and you subsequently issue a shutdown normal command, the database will have to keep running until the user returns. The normal mode is Oracle's default mode for shutting down the database. The command is issued as follows:

Sql> shutdown normal
OR
Sql> shutdown

The shutdown normal command involves the following:

• No new user connections can be made to the database.

• Oracle waits for all users to exit their sessions.

• No instance recovery is needed when you restart the database because Oracle will write all redo log buffers and data block buffers to disk before shutting down. Thus, the database will be consistent when it's shut down in this way.

• Oracle closes the data files and terminates the background processes. Oracle's SGA is deallocated.

Shutdown Transactional

If you don't want to wait for a long time for a user to log off, you can use the shutdown transactional command. Oracle will wait for all active transactions to complete before disconnecting all users from the database, and then it will shut down the database.

Sql> shutdown transactional

The shutdown transactional command involves the following:

• No new user connections are permitted.

• Existing users can't start a new transaction and will be disconnected.

• If a user has a transaction in progress, Oracle will wait until the transaction is completed before disconnecting the user.

• After all existing transactions are completed, Oracle shuts down the instance and deallocates memory. Oracle writes all redo log buffers and data block buffers to disk.

• No instance recovery is needed because the database is consistent.

Shutdown Immediate

Sometimes, a user may be running a very long transaction when you decide to shut down the database. Both of the previously discussed shutdown modes are worthless to you under such circumstances. Under the shutdown immediate mode, Oracle will neither wait indefinitely for users to log off nor wait for any transaction to complete. It simply rolls back all active transactions, disconnects all connected users, and shuts the database down. Here is the command:

Sql> shutdown immediate

The shutdown immediate operation involves the following:

• No new user connections are allowed.

• Oracle immediately disconnects all users.

• Oracle terminates all currently executing transactions.

• For all transactions terminated midway, Oracle will perform a rollback so the database ends up consistent. This rollback process is why the shutdown immediate operation is not always immediate. This is because Oracle is busy rolling back the transactions it just terminated. However, if there are no active transactions, the shutdown immediate command will shut down the database very quickly. Oracle terminates the background processes and deallocates memory.

• No instance recovery is needed upon starting up the database because it is consistent when shut down.

Shutdown Abort

The shutdown abort command is a very abrupt shutting down of the database. Currently running transactions are neither allowed to complete nor rolled back. The user connections are just disconnected.

Sql> shutdown abort

The shutdown abort command involves the following:

• No new connections are permitted.

• Existing sessions are terminated, regardless of whether they have an active transaction or not.

• Oracle doesn't roll back the terminated transactions.

• Oracle doesn't write the redo log buffers and data buffers to disk.

• Oracle terminates the background processes, deallocates memory immediately, and shuts down.

• Upon a restart, Oracle will perform an automatic instance recovery, because the database isn't guaranteed to be consistent when shut down.

When you shut down the database using the shutdown abort command, upon recovery the database has to perform instance recovery to make the database transactionally consistent because there may be uncommitted transactions that need to be rolled back. The critical thing to remember about the shutdown abort command is this: The database may be shut down in an inconsistent mode. That's the reason Oracle recommends that you always shut down the database in a consistent mode by using the shutdown or shutdown immediate command and not the shutdown abort command before backing it up. In most cases, you aren't required to explicitly use a recover command, because the database will perform the instance recovery on its own.

Startup Database

You can start up and shut down your Oracle database through different interfaces.

Starting the Database

When you issue the startup command, Oracle will look for the initialization parameters in the default location, $ORACLE_HOME/dbs (UNIX). There, Oracle will look for the relevant files in the following order:

SPFILE$ORACLE_SID.ora

SPFILE.ora

Init$ORACLE_SID.ora

You can start the database in several modes. Let's take a quick look at the different options you have while starting up a database.

The Startup Nomount Command

You can start up the database with just the instance running by using the startup nomount command. The control files aren't read and the data files aren't opened when you open a database under this mode. The Oracle background processes are started up and the SGA is allocated to Oracle by the operating system. In fact, the instance is running by itself, rather like the engine of a tractor trailer being started with no trailer attached to the cab (you can't do much with either

SQL> connect / as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 156147688 bytes Fixed Size 438248 bytes Variable Size 146800640 bytes Database Buffers 8388608 bytes Redo Buffers 520192 bytes

What good does it do to have the database instance running without opening the data files for access? Well, sometimes during certain maintenance operations and during recovery times, you can't have the database open for public access. That's when this "partial open" of the database is necessary. During database creation and when you have to re-create control files, you use the nomount start-up option.

The Startup Mount Command

The next step in the database start-up process, after the instance is started, is the mounting of the database. This step reads the control file and mounts (i.e., connects) the data files to the database instance. You can do this in two ways. You can either use the alter database command to mount an already started instance, or you can use the startup mount command in the beginning, as shown in .

Sql> alter database mount;

Database altered.

Sql>

OR,

SQL> startup mount

ORACLE instance started.

Total System Global Area 156147688 bytes

Fixed Size 438248 bytes

Variable Size 146800640 bytes

Database Buffers 8388608 bytes

Redo Buffers 520192 bytes

Database mounted.

SQL>

During the mount stage, Oracle will associate the instance with the database. Oracle will open and read the control files, and get the names and locations of the data files and the redo log files. You usually need to start up a database in the mount mode when you're doing activities such as performing a full database recovery, changing the archive logging mode of the database, or renaming data files. Note that all three operations mentioned here require Oracle to access the data files, but can't accommodate any user operations in these files. Hence the opening of the database in the mount mode, with access to the general users still cut off.

The Startup Open Command

The last stage of the start-up process is the database open stage. The database is open for all users, not just the DBA. Prior to this stage, the general users can't connect to the database at all. You can bring the database into the open mode by issuing the alter database command as follows:

Sql> alter database open;

Database altered.

When the database is started in the open mode, all valid users can connect to the database and perform database operations. To open the database, the Oracle server will first open all the data files and the online redo log files and verify that the database is consistent. If the database isn't consistent—for example, if the SCNs in the control files don't match some of the SCNs in the data file headers—the background process will automatically perform an instance recovery before opening the database. If media recovery rather than instance recovery is needed, Oracle will signal that a database recovery is called for and won't open the database until you perform the recovery.

SQL> startup

ORACLE instance started.

Total System Global Area 156147688 bytes

Fixed Size 438248 bytes

Variable Size 146800640 bytes

Database Buffers 8388608 bytes

Redo Buffers 520192 bytes

Database mounted.

Database opened.

SQL>

Create the Database

You can create a new database either manually (using scripts) or by using the Oracle Database Configuration Assistant (DBCA). DBCA is configured to appear immediately after the installation of the Oracle9i software to assist you in creating a database. You can also invoke DBCA later on to help you create a database.

DBCA has several benefits, including the provision of templates for creating DSS, OLTP, or hybrid databases. You can run the tool in an interactive or "silent" mode. The biggest benefit to using DBCA is that for DBAs with little experience, it lets Oracle set all the configuration parameters and start up a new database quickly without errors. Finally, DBCA also automatically creates all its file systems based on the highly utilitarian Oracle Flexible Architecture (OFA) standard.

DBCA is an excellent tool that will help you create a new database quickly without your having to type in any database creation commands or use any scripts. The tool helps you create both small and large databases very easily, and it even allows you to register a new database automatically with Oracle Internet Directory (OID). However, I recommend strongly that you use the manual approach initially, so you can get a good idea of what initialization parameters to pick and how the database is created step by step. Once you gain sufficient confidence, of course, DBCA is without a doubt the best choice for creating an Oracle database of any size and complexity.

Whether you create a database manually or let Oracle create one for you at software installation, a configuration file called the init.ora file or its newer equivalent, the SPFILE, holds all the database configuration details. After the initial creation of the database, you can always change the behavior of the database by changing the init.ora file parameters. You can also change the behavior of the database for brief periods or during some sessions by using the alter system and alter session commands to temporarily modify some parameter values.

You need to perform certain steps before you can create a database. Among other things, you need to make sure you have the necessary software and the memory and storage resources to successfully create the database. The next few sections run down the brief list of preliminary steps.

Installing the Software

Before you can create a database, you must first install the Oracle9i software. If you currently have other Oracle9i databases running on your system, then of course you are already set and can proceed to the creation of the database itself.

Creating the File System for the Database

Planning your file systems is an important task you need to complete before you get down to creating the database. The location of the various files such as the redo log files and archive log files has to be carefully thought out beforehand. Similarly, the placement of the table and index data has serious implications for performance down the road. Two issues you need to focus on with regard to your file system are its size and location. Let's look at both of these issues in some detail.

Sizing the File System

It is a good idea to systematically figure out how big your database is going to be in terms of the total space required. Your overall space estimate should include estimates for the following:

• Space for the tables: Table data is the biggest component of the physical database. You need to first estimate the size of all the tables by getting information regarding the columns included in the tables. You also need row estimates for all the major tables. You don't need accurate numbers here; roughly accurate figures should suffice.

• Space for the indexes: There are formulas you can use to figure out the space required by the indexes in your database. First, though, you must know the indexes needed by your application. You also need to know the type of indexes you're going to create, as this has a major bearing on the physical size of the indexes.

• Space for the undo tablespace: The space that needs to be allocated to the undo tablespace depends on the size of your database and the nature of your transactions. If you anticipate a lot of large transactions or you need to plan for large batch jobs, you will require a fairly large undo tablespace.

• Space for the temporary tablespace: The temporary tablespace size also depends on the nature of your application and the transaction pattern. If the queries involve a lot of sorting operations, you're better off with a larger temporary tablespace in general. Note that you'll be creating the temporary tablespace with the create temporary tablespace command. This temporary tablespace will be designated during the creation of the database as the default temporary tablespace for the users in the database.

Choosing the Location for the Files

If you've been following the OFA guidelines you learned about in , you'll place the various files of the database such as the system, redo log, and archive log files so you can benefit from the OFA guidelines. The following list summarizes the benefits of using the OFA guidelines for file placement in your database. OFA-based files will

• Make it easy for you to locate and identify the various files such as the database files, control files, and redo log files

• Make it easy to administer multiple Oracle databases and multiple Oracle software versions

• Improve database performance by minimizing contention between competing types of files

Tip

In addition to laying out the files in the OFA format, you need to put the data and index files on different drives for performance reasons. If you're going to have several data files, it's a good idea to stripe them across several spindles. This will improve the I/O performance in your database. which discusses instance tuning, explains striping and other disk-related issues in detail.

Sizing the Redo Log Files

Redo log files are critical for the functioning of a database, and they're key components when you're trying to recover the database without any loss in committed data. Here are some other points about redo log files:

• Oracle recommends a minimum of two redo log groups (each group can have one or more members). Redo log files need to be multiplexed—that is, you should have more than a single redo log file in each group, because they're a critical part of the database and they're a single point of failure in the database.

• The size of the redo log file will depend on how fast your database is writing to the log. If you have a lot of DML operations in your database and the redo logs seem to be filling up very fast, you may want to increase the size of the log file. You can't increase the size of an existing redo log file, though—what I mean here is that you can create larger files and drop the smaller redo log files. The redo log files are written in a circular fashion, and your goal should be to size the log files such that no more than two to three redo log files are filled up every hour. The fundamental conflict here is between performance and recovery time. A very large redo log file will be efficient because there won't be many log switches and associated checkpoints, all of which impose a performance overhead on the database. However, when you need to perform recovery, larger redo logs take more time to recover from because you have more data to recover due to infrequent checkpointing.

If you have followed the OFA guidelines while installing your software, you should be in good shape regarding the way your files are physically laid out.

Ensuring Enough Memory Is Allocated

If you don't have enough memory on the system to satisfy the requirements of your database, your database instance will fail to start. Even if it does start, there will be a severe penalty to be paid by the system in the form of memory paging and swapping, which will slow your database down. Memory cost is such a small part of enterprise computing costs these days that you're better off getting a large amount of memory for the server on which you plan to install the Oracle database.

Getting Necessary Authorizations

You will need authorizations to be granted by the UNIX/Linux or Windows system administrator for you to be able to create file systems on the server. Your Oracle username should be included in the DBA group by the system administrator if you are working on a UNIX or a Linux server. If you are working on a Windows server, the system administrator should give you the appropriate administrative privileges as specified in the Oracle installation manual for Windows.

Setting the Operating System Environment Variables

Before you proceed to create the database, you must set all the necessary operating system environment variables. In Windows systems there is less need to set any specific variables, but in UNIX and Linux environments, you must set the following environment variables:

• ORACLE_SID: This is your database's name. For this chapter's purposes, you should set this variable to remorse.

• ORACLE_BASE: This is the directory at the top of the Oracle software. For this chapter's purposes, this is :/u01/app/oracle.

• ORACLE_HOME: This is the directory in which you installed the Oracle software. Oracle recommends you use the following format for this variable: $ORACLE_BASE/product/release. For this chapter's purposes, this is /u01/app/oracle/product/9.2.0.1.0.

• PATH: This is the directory in which Oracle's executable files are located. Oracle's executables are always located in the $ORACLE_HOME/bin directory. You can add the Oracle's executable files location to the existing PATH value in the following way:

  Export PATH=$PATH:$ORACLE_HOME/bin
  
  

• LD_LIBRARY_PATH: this variable points out where the Oracle libraries are located. The usual location is the $ORACLE_HOME/lib directory.

Creating the Initialization File

Every Oracle instance needs resources such as memory for the various components of the SGA. In addition, you must sometimes specify or limit how much of the system resources the instance can use. Oracle uses database parameter files, which list the names of the parameters and the values for each. An initialization parameter file, known as the initdb_name.ora, was traditionally the only type of file in which you could store these initialization parameter values. By default, this file is located in the $ORACLE_HOME/dbs directory, and again it's up to you to store it in a place that's helpful to you. When you store the configuration file in any location other than the default location, you must specify the complete location when you start the instance. If the initialization filename and the location follow the default conventions, you don't have to provide the name or location of the configuration file at start-up time.

Note

The initialization files are used not only to create the database itself initially, but also to tune its performance later on by modifying parameter values. You can change some of these parameters dynamically while the database is running, but to change the others you'll have to restart your database.

The initialization file includes parameters that will help tune the instance. It also contains parameters that set limits on certain database resources and parameters that specify the name and location of some important files. The variables that affect performance are called variable parameters by Oracle, and these are the variables DBAs are mostly interested in. Once the initialization file is ready, you can start the instance by invoking the file. However, you can dynamically modify several important configuration parameters while the instance is running. These modifications won't be permanent; as soon as you shut down the database, the changes are gone and you're back to the values hard-coded in the init.ora file. If you want to make the dynamic changes permanent so the database will come up with these new values upon a restart, you should use a server parameter file, also known as the SPFILE. The SPFILE is also an initialization file, but you can't make changes to it directly because it's a binary file, not a text file. Using the SPFILE to manage your instance provides several benefits, as you'll see in the section "The Server Parameter File (SPFILE)" later in the chapter.

In the sections that follow, I group the initialization parameters into sets of related parameters to make it easier to understand the configuration of a new database. My parameter groupings are purely arbitrary and are mainly for exposition purposes. Oracle provides a template to make it easy for you to create your own customized file. This file is located in the $ORACLE_HOME/dbs directory in UNIX systems and in the $ORACLE_HOME/database directory in Windows-based systems. You can copy this init.ora template and name it initdb_name.ora, and you can then edit it per your own site's requirements. Don't be nervous about trying to make "correct" estimates for the various configuration parameters. Most of the configuration parameters are easily modifiable throughout the life of the database. Just make sure you're careful about the handful of parameters that you can't change without redoing the entire database from scratch.

The interesting thing about the init.ora file is that it contains the configuration parameters for memory and some I/O parameters, but not the database filenames or the tablespaces the data files belong to. The control file holds all that information. The initialization file, though, has the locations of files such as the control files, the redo log files, and the dump directories for error messages. The initialization file also specifies the mode chosen for the undo management, the optimizer mode, and the archiving mode for the redo logs.

Note

All the parameters in the initialization file are optional. That is, if you don't have any parameters configured in your init.ora file, Oracle will apply default values for all the parameters and your database will be successfully started. For example, I can start a brand-new instance called "remorse" very quickly by using this short init.ora file: db_name = REMORSE control_files = (/u01/app/oracle/control1,/u01/app/oracle/control2)

As you can imagine, this means you won't have any control over the behavior of the configurable parameters. You should leave parameters out of the init.ora file only after you ascertain that their default values are OK for your database. In general, it's a good idea to use approximate sizes for the important configuration parameters you know well and use a trial-and-error method to decide whether to use newer or never-before-used parameters.

Oracle9i is famous for being a highly configurable database, but that benefit also carries with it the need for DBAs to expend the necessary energy to learn how these large numbers of parameters work. Most important, you should learn how the parameters may interact with one another at times, thereby producing a result that is at variance with your initial plans. To give you an elementary example, an increase in the SGA size may increase database performance up to a point. After that, any increase in SGA might actually slow the database down, because the operating system may be induced to swap the higher SGA in and out of real memory. Beware of configuration changes, and always think through the implications of "slight" changes in the parameter file.

Changing the Initialization Parameter Values

You can change the value of any initialization parameter by simply editing the init.ora file. However, for the changes to actually take effect, you have to bounce the database, or stop and start it again. As you can imagine, this is not always possible, especially if you are managing a production database. However, you can change several of the parameters "on the fly," and these are called dynamic parameters for that reason. The parameters you can change only by restarting the database after changing the init.ora file are called static parameters.

You have three ways to change the value of dynamic parameters. You can use the alter session, alter system, or alter system … deferred command option to change the parameter values.

Using the Alter Session Command

The alter session command enables you to change the dynamic parameter values for the duration of the session that issues the command. Obviously, you are going to use the alter session command only to change a parameter's value temporarily. Here is the general syntax for the command:

Alter session set parameter_name=value;

Using the Alter System Command

The alter system command changes the parameter's value for all sessions. However, these changes will be in force only for the duration of the instance; when the database is restarted, these changes will go away unless you modify the init.ora file accordingly or you use the SPFILE. Here is the syntax for this command:

Alter system set parameter_name=value;

Using the Alter System … Deferred Command

The alter system … deferred command will make the new values for a parameter effective for all sessions, but not immediately. Only new sessions started after the command is issued are affected. All currently open sessions will continue to use the old parameter values.

Alter system set parameter_name deferred;

The alter system … deferred command works only for the following parameters: backup_tape_io_slaves, transaction_auditing, sort_area_retained_size, object_cache_optimal_size, sort_area_size, and object_cache_max_size_percent. Because of the very small number of parameters to whom the "deferred" status applies, you can, for all practical purposes, consider alter system a command that applies immediately to all sessions.

Important Oracle9i Initialization Parameters

The following sections present some of the important Oracle initialization parameters you need to be familiar with. For the sake of clarity, I've assigned the parameters to various groups.

Although this list looks long and formidable, it isn't really a complete list of initialization parameters that you can configure for the Oracle9i database—it's a list of only the most commonly used parameters. Oracle9i has over 250 initialization parameters that DBAs can configure. Don't be disheartened, though. The basic list of parameters that you need to start your new database could be fairly small and easy to understand. Later on, as you study various topics such as backup and recovery, performance tuning, networking, and so on, you'll have a chance to really understand how to use the more esoteric initialization parameters.

Database Name and Other General Parameters

Most important among the name parameters, of course, is the parameter that sets the name of the database. Let's look at this set of parameters in detail.

Db_Name

The db_name parameter sets the name of the database. This parameter can't be changed after the database is created. You can have a db_name parameter of up to eight characters.

For the purposes of this chapter, you'll name your database remorse, and this will be the db_name parameter's value. Note that this parameter is optional; Oracle can get the name of the database from the create database statement if this parameter is omitted.

Default: false
Type: Static.

Db_Domain

The db_domain parameter gives a fully qualified name for the database. You'll use the default .world name, so your db_domain will be remorse.world.

Default: false
Type: Static.

Instance_Name

The instance_name parameter will have the same value as the db_name parameter, which is remorse for your database.

Default: false
Type: Static.

Service_Name

The service_name parameter provides a name for the database service, and it can be anything you want it to be. Usually, it is a combination of the database name and your database domain.

Default: DB_NAME.DB_DOMAIN
Type: Dynamic, can be changed with the 'alter system' command.

Compatible

Suppose you upgrade to the Oracle9i version, but your application developers haven't made any changes to the Oracle8i application. You need to set your compatibility parameter equal to 8i, so the untested features of the new version you're using won't hurt your application. Later on, after the application has been suitably upgraded, you can reset the compatible initialization parameter to Oracle9i.

Default: false
Type: Static.

Dispatchers

The dispatchers parameter configures the dispatcher process if you choose to run your database in the shared server mode.

Default: None
Type: Dynamic. 'Alter system' command can be used to reconfigure the dispatchers.

Nls_Date_Format

The nls_date_format parameter specifies the default date format Oracle will use. Oracle uses this date format when using the to-char or to-date function in SQL. There is a default value, which is derived from the nls_territory parameter. For example, if the nls_territory format is America, the nls_date_format parameter is automatically set to the DD-MON-YY format.

Default: Depends on the nls_territory variable and the operating system.
Type: Dynamic. Can be altered by using the 'alter session' command.
 File-Related Parameters

You can specify several file-related parameters in your init.ora file. Oracle requires you to specify several destination locations for trace files and error messages. The bdump, udump, and cdump files are used by the database to store the alert logs, background trace files, and core dump files. In addition, you need to specify the utl_file_directory parameter for using the UTL_FILE package. The following sections cover the key file-related parameters.

Control_Files

Control files are key files that hold information regarding the data file names and locations, and a lot of other important information. The database needs only one control file, but because this is such an important file, you always save multiple copies of it. The way to multiplex the control file is to simply specify multiple locations (two or three, although you can go up to the maximum Oracle allows) for the control_files parameter. The minimum number of control files is one. Oracle recommends at least two control files per instance, but three seems to be the number most commonly used by DBAs.

Default: false
Type: Static.

Db_Files

The db_files parameter simply specifies the maximum number of files allowed to be created in the database. This is just a number, and you don't list all the data files for your database here. In fact, the specification of the files and the tablespaces comes during the creation of the database itself. The larger the size of the database, the larger this number should be. For a large warehouse, you can set the value of the db_files parameter to 1000 or greater.

Default: 200
Type: Static

Core_Dump_Dest

The core_dump_dest parameter specifies the location where you want the core (error) messages dumped to.

Default: Depends on the operating system. You can use any valid directory.
Type: Dynamic, can be changed with the 'alter system' command.

User_Dump_Dest

This is the directory where you want Oracle to save error messages from various processes such as PMON and the database writer.

Default: Depends on the operating system. You can use any valid directory.
Type: Dynamic, can be changed with the 'alter system' command.

Background_Dump_Dest

This parameter specifies the Oracle alert log location and the locations of some other trace file for the instance.

Default:  Depends on the operating system. You can use any valid directory.
Type: Dynamic, can be changed with the 'alter system' command.

Utl_File_Directory

You can use the utl_file_directory parameter to specify the directory (or directories) Oracle will use to process I/O when you use the Oracle UTL_FILE package to read from or write to the operating system files.

Default: None. You can't use the utl_file package
to do any I/O under this scenario.
Type: Static. You can set the utl_file_dir to any OS
directory you want. If you just specify *, instead of any
specific directory name, the utl_file package will read and
write to and from all the OS directories, and Oracle
recommends against this practice.

Oracle Managed Files Parameters

The test database that you're going to create doesn't use the Oracle Managed Files (OMF) feature, so the parameter will remain blank. If you were to use the OMF feature, however, this is the parameter that you'll need to include to enable your database to use the OMF files. You'll usually need to use two parameters, both of which specify the format of the OMF files when you decide to use the feature.

Db_Create_File_Dest

The db_create_file_dest parameter denotes the directory where Oracle will create data files and temporary files when you don't specify an explicit location for them. The directory must exist already with the right read/write permissions for Oracle.

Default: false
Type: Dynamic, can be changed using either the 'alter system' or
the 'alter session' command.

Db_Create_Online_Log_Dest_n

This parameter specifies where you want the OMF online redo log files to be created by default. To multiplex the online redo log files, specify more than one value for the parameter. You can have a maximum of five separate directory locations.

Default: false
Type: Dynamic, can be changed using either the 'alter system'
or the 'alter session' command.

Process and Session Parameters

Several initialization parameters relate to the number of processes and the number of sessions that your database can handle. The following sections explore the important process and session parameters.

Processes

The value of the processes parameter will set the upper limit for the number of operating system processes that can connect to your database concurrently. Both the sessions and transactions parameters derive their default values from this parameter.

Default: 6 (may vary depending on the operating system)
Type: Static.

Db_Writer_Processes

The db_writer_processes parameter specifies the initial number of database writer processes for your instance. Instances with very heavy data modification may opt for more than the default single process. You can have up to 20 processes per instance.

Default: 1
Type: Static.

Sessions

The sessions parameter sets the maximum number of sessions that can connect to the database simultaneously. Actually, this parameter is redundant, because the processes parameter will by default determine the maximum number of sessions also.

Default: (1.1 * Processes) + 5
Type: Static.

Open_Cursors

The open_cursors parameter sets the limit on the number of cursors a single session can have.

Default: 50
Type: Static

Memory Configuration Parameters

The memory configuration parameters determine the memory allocated to key components of the SGA. There are no hard-and-fast rules regarding the right size for these parameters. You allocate an approximate amount to start with, and based on the performance statistics, you fine-tune the allocations after the database starts operating.

Note

Oracle's guidelines regarding the ideal settings for the various components of memory, such as the db_cache_size and shared pool, are often vague and not really helpful to a beginner. For example, Oracle states that the db_cache_size should be 20 percent to 80 percent of the available memory for a data warehouse database. The shared pool recommendation for the same database is 5 percent to 10 percent. Well, the wide ranges make the db_cache_size recommendations useless. If your total memory is 2GB, you're supposed to allocate 100MB to 200MB of memory for the shared pool. If your total memory allocation is 32GB, your allocation for the shared pool would be between 1.6GB and 3.2GB, according to the "standard" recommendations. The best thing to do is use a trial-and-error method to see if the various memory settings are appropriate for your database.

The buffer cache and the shared pool are the two main components of Oracle's instance memory, with the other important components being the PGA and the large pool. The buffer cache is the area of Oracle's memory where it keeps the data blocks read in from the disks. The data blocks may be modified here before being written back to disk again. A big enough buffer cache will improve performance by avoiding too many disk accesses, which are much slower than accessing data in memory.

You can set up the buffer cache for your database in units of the standard block size you chose for the database (using the db_block_size parameter), or you can use nonstandard block sized buffer caches. If you want to base your buffer cache on the standard block size, you use the db_cache_size parameter to size your standard block-based cache. You have to make an educated guess as to the right size of the buffer cache parameter. For larger databases, allocate larger buffer caches. Let's say you want to allocate about 500MB of memory on your system to the buffer cache parameter. The following sections cover the standard block size buffer cache-related initialization parameters.

Db_Cache_Size

This parameter sets the size of the default standard block-sized cache. For example, you can use a number like 1024MB.

Default: 48 MB
Type: Dynamic, can be modified with the 'alter system' command.

The normal behavior of the buffer pool is to treat all the objects placed in it equally. That is, any object will remain there as long as free memory is available in the buffer cache. Objects are removed or "aged out" only when there is no free space. When this happens, the least recently used (LRU) algorithm is used to remove the oldest unused objects sitting in memory to make space for new objects. The use of two specialized buffer tools, the keep pool and the recycle pool, allows you to specify at object creation time how you want the buffer pool to treat certain objects.

For example, if you know that certain objects don't really need to be kept in memory for long, you can have them assigned to a recycle pool, which removes the objects that aren't needed anymore as soon as they're used. Similarly, the keep pool always retains an object in memory if it's created with the keep option. The following sections cover the two relevant parameters for configuring multiple buffer pools.

Db_Keep_Cache_Size

The db_keep_cache_size parameter specifies the size of the keep pool. If you store objects in the keep pool of the buffer cache, Oracle will ensure that they will never age out of the pool.

Example: db_keep_cache_size = 5
Default: 0 Megabytes. By default, this is not configured.
Type: Dynamic. Can be changed by using the 'alter system' command.

Db_Recycle_Cache_Size

The db_recycle_cache_size parameter specifies the size of the recycle pool in the buffer cache. Oracle removes objects from this pool as soon as the objects are used.

Example: db_cycle
Default: 0 Megabytes. By default, this is not configured.
Type: Dynamic. Can be changed by using the 'alter system' command.

Db_nK_Cache_Size

If you prefer to use nonstandard-sized buffer caches, for each of the nonstandard-sized buffer cache you need to specify the db_nk_cache_size parameter, as in the following example: db_4k_cache_size=2048MB or db_8k_cache_size=4096MB. The range of values for this parameter is 2K, 4K, 8K, 16K, and 32K.

Default: 0 Megabytes.
Type: Dynamic. You can change this parameter's value with the
'alter system' command.

Shared_Pool_Size

The shared pool is a critical part of Oracle's memory, and the shared_pool_size parameter sets the total size of the SGA that is devoted to the shared pool. The shared pool consists of the data dictionary cache and the library cache. The data dictionary cache stores the recently used data dictionary information, so you don't have to constantly hit the disk to access the data dictionary.

Remember that the data dictionary is one of the most frequently consulted parts of any Oracle database. Before any query can execute, the data dictionary is consulted to verify the objects involved, user privileges, and a bunch of other important things. There is no way to separately manipulate the sizes of the two components of the shared pool. If you want to increase the size of either component of the shared pool or both of them at once, you do it through increasing the value of the shared_pool_size parameter. Oracle recommends 5 percent to 10 percent of the total memory for the shared pool for a data warehouse and a larger proportion for OLTP databases.

Default: 16 Megabytes for non-64 Bit Operating Systems, 64 Megabytes for 64 Bit.
Type: Dynamic. The 'alter system' command can be used to
change it to OS-dependent maximum size.

Shared_Pool_Reserved_Size

This parameter sets the amount of space to be reserved in the shared pool for holding large queries or packages.

Default: Five percent of shared_pool_size.
Type: Static. You can increase this to half of the total shared_pool size.

Pga_Aggregate_Target

Users need areas in memory to perform certain memory-intensive operations, such as sorting, hash joining, bitmap merging, and so on. The pga_aggregate_target parameter is the total amount of memory allocated to the instance so it can be assigned to users as "work areas" to perform the previously mentioned memory-heavy jobs. contains a detailed discussion of the PGA and how to size it. By setting the pga_aggregate_target parameter, you let Oracle manage the runtime memory management for SQL execution. The sum of the total PGA memory allocated to all sessions in this instance cannot exceed the value of this parameter.

Default: 0
Type: Dynamic. The 'alter system' command can be used to modify this parameter.

Tip

You can adjust the pga_aggregate_target parameter dynamically using the alter system command. The target value should range between 10MB and 4096GB. Oracle recommends that the pga_aggregate_target parameter should be between 20 percent and 80 percent of the available memory.

Log_Buffer

The log_buffer parameter indicates the size of the redo log buffer. As you recall, the redo log buffer holds the redo records, which are used to recover a database. The log writer writes the contents of this buffer to the redo log files on disk. The log buffer's size is usually set to a small amount, under about a megabyte or so. The more changes the redo buffers have to process using redo records, the more active the redo logs will be. Instead of adjusting the log_buffer parameter to a very large size, you may want to use the nologging option to reduce redo operations.

Default: Maximum of 512 Kilobytes or 128 Kilobytes * Number of CPUS,
whichever is greater.
Type: Static.

Large_Pool

The shared pool can normally take care of the memory needs of shared servers as well as Oracle backup and restore operations and a few other operations. But sometimes this may place a heavy burden on the shared pool, causing a lot of fragmentation in it and also the premature aging-out recycling of important objects from the shared pool due to lack of space.

To avoid these problems, Oracle enables you to use a parameter called large_pool, which is used for the previously mentioned specialized operations, thus freeing up the shared pool mostly for caching SQL queries and the data dictionary. If the parallel_automatic_tuning parameter is set, the large pool is also used for parallel-execution message buffers. The amount of memory for the large pool in this case depends on the number of parallel threads per CPU and the number of CPUs.

Default: Zero if the pool is not required for parallel
execution and DBWR_IO_SLAVES is not set.
Type: Static.
Range: 600K to 2 Gigabytes

Java_Pool_Size

Use this parameter only if your database is using Java stored procedures. Other-wise, you can leave it out of the init.ora file.

Default: 20000 Bytes
Type: Static.
Range: 1Megabyte to 1Gigabyte

Sga_Maximum_Size

You can also set a maximum limit for the memory that can be used by all the components of the SGA with the sga_maximum_size parameter. This is an optional parameter, because omitting it just means that the SGA's maximum size will default to the sum of the memory parameters in the SGA.

Default: false
Type: Static.

Lock_Sga

Setting the value of the lock_sga parameter to true will lock your entire SGA into the host physical memory. This works only on some operating systems, and you should set this parameter to true only after verification. As mentioned in Chapter 5, Oracle doesn't recommend using this parameter under most circumstances.

Default: Depends on the values of the component variables.
Type: Static.

Db_Cache_Advice

Once you start the instance with an approximate memory sizes, you can have Oracle itself advise you on the best levels for the buffer size based on the cache miss rates for various hypothetical cache sizes. The database will simulate the use of a wide range of buffer cache sizes and store the information. To enable Oracle to do the analysis regarding the ideal value for the database buffer cache size, you must set the db_cache_advice parameter to true in the init.ora file.

Default: Off
Type: Dynamic. 'Alter system' command can be used to switch to off/on.

Archive Log Parameters

Oracle gives you the option of archiving your filled redo logs. When you configure your database to archive its redo logs, the database is said to be in an archivelog mode. You should always archivelog your production databases unless there are exceptional reasons for not doing so. If you decide to archive the redo logs, you have to specify that in the initialization file by specifying the three parameters described in the following sections.

Log_Archive_Dest_n

This parameter enables you to specify the location (multiple) of the archived logs. You should set this parameter only if you are running the database in archivelog mode. You can do this when you create the database in the next section by specifying the archivelog keyword in your create database statement. But when you first create the database, there is no need for archiving to be turned on; you will thus not have a need to specify this parameter.

Default: None
Type: Dynamic. You can use the 'alter session' or the 'alter
system' command to make changes.

Log_Archive_Start

This parameter enables the automatic archiving of the logs. The alternative is to manually archive them, which may not be practical in a busy production system, as you'll see in Chapter 15.

Default: false
Type: Static.

Log_Archive_Format

This parameter specifies the default filename format for the archived redo log files.

Default: Operating system dependent.
Type: Static.

Undo Space Parameters

The main parameters to be configured here are the undo_management mode and the undo_tablespace parameter. The undo management mode will be set to auto in your case, because the remorse database will be configured to use the Automatic Undo Management (AUM) option. The undo_tablespace will be set to UNDOTBSP_01.

Undo_Tablespace

This parameter determines the default tablespace for undo records. If you don't specify one, the database will use the system rollback segment, and this should be avoided. If you don't specify a value for this parameter when you create the database, and you have chosen AUM, Oracle will create a default undo tablespace with the name UNDOTBS. This default tablespace will have a single 10MB data file that will be automatically extended without any maximum limit.

Default: The first undo_tablespace available
Type: Dynamic. You can use the 'alter system' command to
change the default undo tablespace.

Undo_Management

If the mode is set to auto, then the undo tablespace is used for storing the undo records and Oracle will automatically manage the undo segments.

Default: Manual (You need to use rollback segments)
Type: Static. You can use the AUTO value if you want the undo
space management to be automated using the undo tablespace.

Undo_Retention

This parameter specifies the amount of redo information to be saved in the undo tablespace before it can be overwritten. The value for this parameter depends on the size of the undo tablespace and the nature of the queries in your database. If the queries aren't huge, they don't need to have large snapshots of data, and you could get by with a low undo_retention interval. Similarly, if there is plenty of free space available in the undo tablespace, transactions won't be overwritten, which will cause the failure of queries (the "snapshot too old" problem). If you plan on using the Flashback Query feature extensively, you have to figure out how far back in time your Flashback Queries will go and specify the undo_retention parameter accordingly.

Default: 900 (seconds)
Type: Dynamic. You can use the 'alter system' command to
increase the value to a practically unlimited time period.

Rollback Segment Parameters

These parameters need to be set if you're choosing manual management of undo space. It will then list all the rollback segments that have been configured for the database.

You can switch from an AUM mode to the traditional undo management mode by using the alter session statement, as shown here:

Alter session set undo_management_mode=manual;

This parameter sets the standard database block size (for example, 4096, a 4KB block size). You can pick anywhere from 2KB to 32KB (2, 4, 8, 16, and 32) as your db_block_size value. You always should make the db_block_size parameter a multiple of your operating system block size, which you can ascertain from your UNIX or Windows system administrator.

You have to carefully evaluate your application's needs before you pick the correct database block size. Whenever you need to read data from or write data to an Oracle database object, you do so in terms of data blocks.

If you're supporting data warehouse applications, it makes sense to have a very large db_block_size—say, something between 8KB and 32KB. This will improve database performance when it's reading in huge chunks of data from disk. However, if you're dealing with a typical OLTP application where most of your reads and writes consist of relatively short transactions, a large db_block _size would be overkill and could actually lead to inefficiency in input and output operations. Most OLTP transactions read or write a very small number of rows per transaction and conduct numerous transactions with random access I/O (index scans), so you need to have a smaller block size, somewhere between 2KB and 8KB. A large block size for most OLTP applications is going to hurt performance, as the database has to read large amounts of data into memory even when it really needs very small bits of information. A small db_block_size for an OLTP database would reduce slowdowns due to buffer_busy_waits, of which you'll learn a lot more in . Large data warehouses perform more fill table scans and thus perform more sequential data access than random access I/Os.

Default: 2048, range is 2048-32768.
Type: Static.

Db_File_Multiblock_Read_Count

This parameter specifies the maximum number of blocks Oracle will read during a full table scan. The larger the value, the more efficient your full table scans will be, because Oracle will retrieve multiple blocks of data in a single read. The general principle is that data warehouse operations need high multiblock read counts because of the heavy amount of data processing involved. If you are using a 16KB block size for your database and the multiblock read count parameter is set to 16 also, Oracle will read 256KB in a single I/O. Depending on the platform, Oracle supports I/Os up to 1MB. Note that when you stripe your disks, the stripe size should be a multiple of the I/O size for optimum performance. If you are using an OLTP application, a multiblock read count such as 8 or 16 would be ideal. Large data warehouses could go much higher than this.

Default: 8
Type: Dynamic - modifiable with either an 'alter system' or
an 'alter session' command.