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.
- 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.