Oracle Overview English

1 about

proper understanding of how base components are working is necessary to do a good sysadmin job.
so i took the time to write this outline of oracle core components.
this documentation is focused on oracle standard edition.

2 Oracle core components

Oracle consists of a database and an instance.

3 database

A database includes all the physical data files, control files, and redo log files that will hold your data and Oracle’s metadata information.

3.1 Data files

3.1.1 Data tables

holds user data.

3.1.2 Indexes

similar to book indexes, contain table location information for faster lookups.

3.1.3 Rollback segments

holds before-image copies of data being changed. Maintains read consistency

3.2 Redo log files

Contains a record of all changes made to data residing in data files. Should be multiplexed.

• Used to record changes made to data.
• Server processes started on a user’s behalf will make changes on data in memory.
• A record of those changes are immediately recorded in the redo log files by LGWR.
• Should database crash before DBWR flushes changed data from memory to disk, on startup SMON will simply replay the redo log file to bring the database to a consistent state.
• Due to critical nature of redo log files, should be multiplexed and, optionally, OS mirrored.

3.3 Control file

• Contains all information necessary for an instance to access a database, during startup and normal operation.
• Metadata contained within it is important during database recovery, as it can identify files needed to bring a database to a stable condition.
• Due to its critical nature, should be multiplexed by Oracle and, optionally, mirrored by the OS.

3.4 Oracle Caching

• Whenever a user accesses a data table, an Oracle server process created on his behalf reads the data from the data file into the SGA.
• When another user attempts to access that same data, his server process will read from the copy in memory.
• If another user attempts to modify it, his server process will make changes to the copy in memory. DBWR will write these changes to disk in its due time.

4 Instance

An instance is a combination of the pool of physical memory (RAM) allocated to Oracle, referred to as the System Global Area (SGA), and the background processes that Oracle spawns to use this memory pool.

4.1 Oracle Processes

4.1.1 DBWR

process responsible for writing modified data that resides in the SGA to the data files on disk.

4.1.2 LGWR

records changes applied to data in the redo log files.

4.1.3 PMON

performs cleanup of failed or killed user and server processes.

4.1.4 SMON

performs instance recovery should that database shutdown improperly.

4.1.5 CKPT

takes account of whenever DBWR writes data in memory to disk.

4.2 SGA

Area where Oracle attempts to cache database data for faster access (RAM I/O is about 1000x faster than disk I/O). Oracle uses Shared Memory of the underlying operating system it to implement its System Global Area (SGA), in which table data, RDBMS metadata, and other Oracle objects are cached.

4.2.1 Shared Memory and the SGA

• The larger the SGA, the better, as frequent I/O in RAM is significantly faster than the same from disk.
• SGA size must be balanced with memory requirements of the OS and other running applications.
  • Too much results in excessive paging.
  • Too little results in high disk I/O.
• Unix variants offer unique shared memory features to enhance the performance of Oracle.

4.2.2 Physical Memory and the SGA

• Due to the memory needs of Oracle’s server, background processes, and its SGA, a large amount of RAM is highly desirable.
  • Entire SGA must fit in RAM.
• When drafting specifications for server hardware of a Unix system that will host Oracle, be sure to invest in plenty of RAM.

5 OS depending components

5.1 File System

5.1.1 Raw

Raw disk refers to a disk slice containing no file system.

• Raw disks were commonly chosen for performance due to the fact that disk I/O to such devices didn’t have to go through file system code and buffers to access the disk.
• Historically, file systems added too much overhead for Oracle’s I/O characteristics - File-system buffer

5.1.2 Cooked

Cooked disk refers to a disk that is formatted with a file system.

• Advances in file system technology have narrowed that gap significantly in I/O performance.
  • Asynchronous I/O
  • Direct I/O
  • Configurable file-system buffers
  • Veritas File-systems Quick I/O
  • extent-based file systems (xfs, jfs2, vxfs, etc.)
• File-systems provide more flexibility.
  • ‘cp’, ‘mv’, ‘cpio’, ‘tar’, ‘dump/restore’, etc.

5.2 RAID

5.2.1 RAID 0: Striping

• Just pure disk striping.
• Great for Oracle disk I/O performance.
• However, offers no additional protection.

5.2.2 RAID 1: Mirroring

• Offers data redundancy.
• Great for data reliability which is essential for Oracle, and great for read performance.
• However, adds some write overhead, and is an expensive solution.

5.2.3 RAID 5: Data & Parity Striping

• Data & Parity striped across all disks.
• Inexpensive alternative to RAID 0, and improves read performance.
• Degrades write performance due to parity calculations for each write. DBAs like to request this!!!!

5.2.4 RAID 10: Striping and Mirroring

• Disks that are both striped and mirrored.
• Best of both worlds (reliability and performance).
• Expensive solution, for reasons stated for RAID 1.