Introduction to Oracle Database
Overview
If you are going to be an effective DBA there is no escaping knowing the Oracle database fundamentals. If you are new to Oracle perhaps scan it then revisit it until it is in your DNA.
This is for you veteran DBAs too! Why? Because Oracle keeps changing. Many Oracle fundamentals stay the same. Many do not. You can be more effective managing an Oracle database based on how it actually works rather than how you believe it does.
In establishing the foundation here I have been able to keep the presentation of most Oracle tasks found in the other sections as concise as possible.
Database Storage
An Oracle database is made up of a set of files containing data and metadata. Information is stored persistently in these files.
Common file types include the following:
- .arc - Archived Redo Logs
- .bkp - RMAN Backupset Files
- .ctl - Control Files
- .dat - Instance or Service Files
- .dbf - Datafiles
- .exp - Export Files
- .ora - Configuration Files
- .rdo - Redo Logs
Control Files
Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes: The database name. Names and locations of associated datafiles and redo log files. The timestamp of the database creation. The current log sequence number. Checkpoint information. If used, RMAN backup information in the circular reuse area. The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. You should create two or more copies of the control file during database creation.
Tablespaces
A database is divided into one or more logical storage units called tablespaces. Every Oracle database must contain a tablespace named SYSTEM. The SYSTEM tablespace is always online when the database is open. The SYSTEM tablespace contains the data dictionary tables for the entire database. The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. The SYSAUX tablespace should always be created during the creation of an Oracle 10g database. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace.
Undo Tablespaces are special tablespaces used solely for storing undo information. Each undo tablespace is composed of a set of undo files and is locally managed.
Designating one or more Temporary tablespaces exclusively for sort operations effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space.
There are basically two types of tablespaces employed to hold user data: SmallFile and BigFile. As the names imply, SmallFile tablespaces are designed for small to medium databases while BigFile tablespaces are architected for large databases. Oracle recognized that managing large databases can be unwieldy because of the number of files and complexity of partition schemas. BigFile tablespaces provide great performance and scalability without the complexity of managing hundreds or even thousands of datafiles for large databases.
Temporary Tablespace Groups groups enable users to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused when one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.
A tablespace group name can appear where a tablespace name would appear when assigning a default temporary tablespace for the database or assigning a temporary tablespace for a user.
Datafiles
Oracle creates a datafile for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header. When a datafile is first created, the allocated disk space is formatted but does not contain any user data. Oracle reserves the space to hold the data for future segments of the associated tablespace. As the data grows in a tablespace, Oracle uses the free space in the associated datafiles to allocate extents for the segment. The data associated with schema objects in a tablespace is physically stored in one or more of the datafiles that constitute the tablespace.
A SmallFile tablespace in an Oracle database can consist of one or more physical datafiles. A BigFile tablespace, however, only consists of one datafile. In either case, a datafile can only be associated with one tablespace and only one database.
Redo Log Groups and Archive Logs
The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log(s) to protect the database in case of an instance failure.
Each database instance has its own redo log groups. In typical configurations, only one database instance accesses an Oracle Database, so only one redo log thread is present. In an Oracle Real Application Clusters (RAC) environment, however, two or more instances concurrently access a single database and each instance has its own thread of redo.
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.
Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.
The redo log of a database consists of two or more redo log files. The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived (if the database is in ARCHIVELOG mode).
LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again.
Filled redo log files are available to LGWR for reuse depending on whether archiving is enabled.
- If archiving is disabled (the database is in NOARCHIVELOG mode), a filled redo log file is available after the changes recorded in it have been written to the datafiles.
- If archiving is enabled (the database is in ARCHIVELOG mode), a filled redo log file is available to LGWR after the changes recorded in it have been written to the datafiles and the file has been archived.
Database Instance
To enable interaction with the data and metadata files, Oracle uses background processes. It must also allocate memory to be used during database operations. The background processes and memory allocated by Oracle together make up an Oracle database instance. An instance must be started to read and write information to the database.
The background processes and memory allocated by Oracle together make up an Oracle database instance. Managing an instance requires configuring parameters that affect the basic operation of the database instance. These parameters are called initialization parameters. The Oracle database server reads these parameters at database startup and monitors them while the database is running. They are stored in memory, where many of them can be changed dynamically.
An Oracle database can be made up of one or more instances. A database with multiples instances is referred to as a Real Application Cluster (RAC).
Instance Parameter File
The parameter file resides on the machine that Oracle is running on. There is one per instance. There are two types of parameter files. The type of parameter file you use will dictate how dynamic changes occur across database shutdown and startup.
- Server Parameter File (SPFILE). The server parameter file is the preferred form of initialization parameter file. It is a binary file that can be written to, and read by, the database server. It cannot be edited manually.
- Text Initialization Parameter File (PFILE). This type of initialization parameter file can be read by the database server, but it is not written to by the server. With a PFILE you can use a text editor to set initialization parameters.
Instance Memory
The size of the instance memory pools affects the performance of the Oracle database server and is controlled by initialization parameters. The most common memory pools are:
Buffer Cache - Before any data stored in the database can be queried or modified, it must be read from disk and stored in memory in the buffer cache. All user processes connected to the database share access to the buffer cache.
Java Pool - Used for all session-specific Java code and data within the Java Virtual Machine (JVM).
Large Pool - Used to buffer large I/O requests for various server processes.
Redo Log Buffer - Caches redo information used for instance recovery until it can be written to the physical redo log files stored on disk.
Shared Pool - The shared pool caches information that can be shared among users (SQL statements, data dictionary, stored procedures etc.).
Streams Pool - Used by the Oracle Streams product.
The System Global Area (SGA) is a shared memory area that contains data and control information for the instance. Multiple users can share data within this memory area and avoid repeated, time-consuming access from physical disk. For optimal performance, the SGA should be large enough to avoid frequent disk reads and writes.
A Program Global Area (PGA) is a memory area used by a single Oracle server process. A server process is a process that services a client's requests. Each server process has its own nonshared PGA when the process is started. The amount of PGA memory used, and its content, depends on the instance configuration. The PGA is used to process SQL statements and to hold logon and other session information.
Processes
The following are the most common Oracle processes:
CKPT - The checkpoint process is responsible for updating file headers in the database datafiles. A checkpoint occurs when Oracle moves new or updated blocks (called dirty blocks) from the RAM buffer cache to the database datafiles. A checkpoint keeps the database buffer cache and the database datafiles synchronized. This synchronization is part of the mechanism that Oracle uses to ensure that your database can always be recovered.
DBWn - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files. Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks. The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.
LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC, each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.
PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important.
SMON - System Monitor process recovers after instance failure and monitors temporary segments and extents. SMON in a non-failed instance can also perform failed instance recovery for other failed RAC instance.
ARCn – The optional Archive process writes filled redo logs to the archive log location(s). In RAC, the various ARCn processes can be utilized to ensure that copies of the archived redo logs for each instance are available to the other instances in the RAC setup should they be needed for recovery.
OracleNet
The most common method used by Oracle to communicate over a network is the Transparent Network Substrate (TNS). It is also referred to as OracleNet. This is a foundation technology that works with any standard network transport protocol.
The Listener
When a database instance starts, a Listener process establishes a network pathway to Oracle. When a user process makes a connection request the listener establishes the initial connection.
The listener also establishes a network pathway between databases. When multiple databases or instances run on one computer, as in Real Application Clusters (RAC), service names enable instances to register automatically with other listeners on the same computer. A service name can identify multiple instances, and an instance can belong to multiple services. Clients connecting to a service do not have to specify which instance they require.
The listener configuration parameters are stored in the listener.ora file on the database server. Service Information Registration Service registration relies on the PMON process (an instance background process) to register instance information with a listener, as well as the current state and load of the instance. The registered information enables the listener to forward client connection requests to the appropriate service handler. Service registration does not require configuration in the listener.ora file.
On startup, each instance registers with the listeners of other instances belonging to the same services. During database operations, the instances of each service pass information about CPU use and current connection counts to all of the listeners in the same services. This enables dynamic load balancing and connection failover.
Database Schema Objects
A schema is a collection of database objects and is owned by a database user. Schema objects are logical structures created to contain or reference data. Schema objects include structures such as tables, indexes, sequences and views.
Tables
Tables are the basic unit of data storage in an Oracle database. Data in a table is stored in rows and columns. You define a table with a table name (work_order) and set of columns. You give each column a column name (such as work_order_id, date_opened, date_closed, title, location), a datatype (such as VARCHAR2, DATE or NUMBER), and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is simply a collection of column information corresponding to a single record.
You can specify rules for each column of a table. These rules are called constraints. One example is a NOT NULL constraint. This constraint forces the column to contain a value in every row.
After you create a table SQL statements can be used to SELECT, INSERT, UPDATE and DELETE your data. Commands that can change data are known as DML (Data Manipulation Language).
You can choose from the following table types: Standard Table - A standard table is an ordinary table whose data is stored as an unordered collection (heap). Index Organized Table (IOT) - An Index Organized table stores the primary key column values of an index-organized table row and the nonkey column values as well. Clustered Table - Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together. External Table - Create a table to view data from a file. You can access data in external sources as if it were in a table in the database. External tables are read-only; therefore, no DML operations are possible, and no indexes can be created on them.
Indexes
Indexes are structures associated with tables. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Just as the index in a book helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. Indexes are the primary method to improve performance on tables.
You can have multiple indexes for a table as long as the combination of columns differs for each index. One practice you want to avoid at all costs is to create an index on all or too many columns. This will actually hinder performance because of the overhead of updating all the indexes associated with the table.
Oracle provides several indexing types, which provide complementary performance functionality:
- B-tree indexes
- B-tree cluster indexes
- Hash cluster indexes
- Reverse key indexes
- Bitmap indexes
- Bitmap join indexes
Oracle also provides support for function-based indexes and domain indexes specific to an application.
The absence or presence of an index does not require a change to SQL statements. An index simply speeds access to the data. Given a data value that has been indexed, the index will point directly to the location of the rows containing that value.
Indexes are logically and physically independent of the data in the corresponding tables. You can create or drop an index at any time without affecting the base tables or other indexes (if using the ONLINE option). If you drop an index, all applications will continue to work but will be slower. Indexes require their own storage space. Oracle automatically maintains indexes to reflect changes to data (via INSERTs, UPDATES, DELETEs etc.).
Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values.
Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. A Primary Key (PK) however, is a combination of both unique and also not null, so the field (or combination of field values) the PK is created on will be both unique and also a not null.
Views
A view is a logical representation of one or more tables. A view derives its data from the tables with which it is associated. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables. Views can be subsets or supersets of the data that resides within other tables and views.
Synonyms
A synonym is an alias for another schema object such as a table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.
Sequences
Sequences are schema objects that can generate unique numbers. The sequence generator generates sequential numbers. A common usage of sequences is to create unique values for primary keys.
Directory Objects
Directory Objects are logical structures that represent a physical directory on the server's file system. They contain the location of a specific operating system directory.
Database Links
A database link connection allows local users to access data on a remote database.
OFA
Oracle Optimal Flexible Architecture (OFA) helps you to organize database software and configure databases to allow multiple databases, of different versions.
OFA rules also provide optimal performance by isolating fragmentation and minimizing contention. By default, Oracle Universal Installer (OUI) places Oracle Database components in directory locations and with permissions in compliance with OFA rules. Oracle recommends that you configure all Oracle components on the installation media in accordance with OFA guidelines.
Oracle Database Names
Oracle has myriad names for how it references a database. This page should make it clearer.