Oracle Database - Part One

The Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is an object-relational database management system produced and marketed by Oracle Corporation.

Physical and logical structures
An Oracle database system—identified by an alphanumeric system identifier or SID—comprises at least oneinstance of the application, along with data storage. An instance—identified persistently by an instantiation number (or activation id: SYS.V_$DATABASE.ACTIVATION#)—comprises a set of operating-system processes and memory-structures that interact with the storage. (Typical processes include PMON (the process monitor) and SMON (the system monitor).) Oracle documentation can refer to an active database instance as a "shared memory realm".

Users of Oracle databases refer to the server-side memory-structure as the SGA (System Global Area). The SGA typically holds cache information such as data-buffers, SQL commands, and user information. In addition to storage, the database consists of online redo logs (or logs), which hold transactional history. Processes can in turn archive the online redo logs into archive logs (offline redo logs), which provide the basis (if necessary) for data recovery and for the physical-standby forms of data replication using Oracle Data Guard.

If the Oracle database administrator has implemented Oracle RAC (Real Application Clusters), then multiple instances, usually on different servers, attach to a central storage array. This scenario offers advantages such as better performance, scalability and redundancy. However, support becomes more complex, and many sites do not use RAC. In version 10g, grid computing introduced shared resources where an instance can use (for example) CPU resources from another node (computer) in the grid.

The Oracle DBMS can store and execute stored procedures and functions within itself. PL/SQL (Oracle Corporation's proprietary procedural extension to SQL), or the object-oriented language Java can invoke such code objects and/or provide the programming structures for writing them.

The Oracle RDBMS stores data logically in the form of tablespaces and physically in the form of data files ("datafiles"). Tablespaces can contain various types of memory segments, such as Data Segments, Index Segments, etc. Segments in turn comprise one or more extents. Extents comprise groups of contiguous data blocks. Data blocks form the basic units of data storage. A DBA can impose maximum quotas on storage per user within each tablespace.

Newer versions of the database can also include a partitioning feature: this allows the partitioning of tables based on different set of keys. Specific partitions can then be easily added or dropped to help manage large data sets. Monitoring Oracle database management tracks its computer data storage with the help of information stored in the SYSTEM tablespace. The SYSTEM tablespace contains the data dictionary—and often (by default) indexes and clusters. A data dictionary consists of a special collection of tables that contains information about all user-objects in the database.

Since version 8i, the Oracle RDBMS also supports "locally managed" tablespaces that store space management information in bitmaps in their own headers rather than in the SYSTEM tablespace (as happens with the default "dictionary-managed" tablespaces). Version 10g and later introduced the SYSAUX tablespace, which contains some of the tables formerly stored in the SYSTEM tablespace, along with objects for other tools such as
OEM, which previously required its own tablespace.

Disk files
Disk files primarily represent one of the following structures:

• Data and index files: These files provide the physical storage of data, which can consist of the data-dictionary data (associated to the tablespace SYSTEM), user data, or index data. These files can be managed manually or managed by Oracle itself ("Oracle-managed files"). Note that a datafile has to belong to exactly one tablespace, whereas a tablespace can consist of multiple datafiles.

• Redo log files, consisting of all changes to the database, used to recover from an instance failure. Note that often a database will store these files multiple times, for extra security in case of disk failure. The identical redo log files are said to belong to the same group.

• Undo files: These special datafiles, which can only contain undo information, aid in recovery, rollbacks, and read-consistency.

• Archive log files: These files, copies of the redo log files, are usually stored at different locations. They are necessary (for example) when applying changes to a standby database, or when performing recovery after a media failure. It is possible to archive to multiple locations.

• Tempfiles: These special datafiles serve exclusively for temporary storage data (used for example for large sorts or for global temporary tables).

• Control file, necessary for database startup. "A binary file that records the physical structure of a database and contains the names and locations of redo log files, the time stamp of the database creation, the current log sequence number, checkpoint information, and so on. At the physical level, datafiles comprise one or more data blocks, where the block size can vary between data files. Data files can occupy pre-allocated space in the file system of a computer server, utilize raw disk directly, or exist within ASM logical volumes.

Please click here for Part Two