Oracle 10g Basics

Here is a short overview for Oracle 10g Basics including Memory Structure, Background Processes and Storage Structures.

Memory Structure

System Global Area (SGA) (sga_max_size and sga_target)

This is a shared memory area containing data and control information for the instance. Users can share data to avoid repeated access from physical disk.

SGA Components

Buffer Cache (db_cache_size)

If you select/modify any data it stores them from disk in the buffer cache, hence all user processes share this buffer cache

Shared Pool (shared_pool_size)

This pool chaches information that can be shared among users:
SQL statements can be reused (use bind variable)
Information from datadictionary (table/index description, user account data and privileges)
Stored procedures

Redo Log Buffer (log_buffer)

This buffer caches redo information before it can be written to the physical redo log files stored on disk

Large Pool (large_pool_size)

This buffers large I/O requests for various server processes

Java Pool (java_pool_size)

This is used for all session specific Java code and data within the JVM

Streams Pool (streams_pool_size)

This is used by Oracle Streams product

Program Global Area (PGA) (pga_aggregate_target)

This is a nonshared memory area. Each Oracle server process has its own PGA. It’s used to process SQL statements and to hold logon and other session information. The amount of the PGA depends on the instance configuration

Background Processes

The background processes depend on the features that are being used in the database. Here the most common background processes:

Database Writer (DBWn) (db_writer_processes max. 20)

Writes modified blocks from the database buffer cache to the files on the disk.

Log Writer (LGWR)

Writes redo log entries sequentially to the redologfiles generated in redo log buffer of the SGA. Force it with:

alter system switch logfile;

Checkpoint (CKPT)

At a specific time. all modified database buffers in the SGA are written to the datafiles by a DBWn. This event signal is called a checkpoint. The checkpoint process signals the DBWn to update all datafiles and control files of the database. To force it use:

alter system checkpoint;

System Monitor (SMON)

This performs a crash recovery when a faild instance starts up.

Process Monitor (PMON)

This perfomrs a recovery when a user process fails. E.g. if a session gets killed. It cleans up the cache and frees recources that the filed process was using.

Achiver (ARCn) (log_archive_dest_n max. 10)

This copy the redo log files to archival storage when the log files are full or a log switch occurs. The database must be in archive log mode to run archive process. (log_archive_start)

Storage Structures

Conrol Files (control_files)

A control file tracks the physical components of the database. It is also the repository for RMAN. It is essential to the functioning of the database. Because its importance, Oracle recommend three copies of the controlfile of course on different disks.

Online Redo Log Files

The redo log records all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. The switch of the redo log should be between 15 and 20 minutes, so you must size the redo logs to size which correlates with this times.

Archive Log Files (if log_archive_start=true)

This is the archive of the redo log files. So the redo log files are written to an other location with an other filename (log_archive_format)

Datafiles

Datafiles are the operating system files that hold data within the database. The files have a special format that can only be readen/written by Oracle.

Components of datafiles:

Segments and Extents

There exists data segments for tables and index segments for indexes. An extent is a contiguous set of data blocks within a segment. Oracle allocates an extent of a specific size for a segment, but if that extent fills, the more extens can be allocated.

Data Blocks

These is the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. If you create a database you must set the db_block_size parameter. You cannot change this parameter anymore.

Tablespaces

A tablespace is a logical structur consisting of one or more datafiles or temfiles.

Oracle recommends locally-managed tablespaces over dictionary-managed tablespaces. With locally-managed tablespaces offers a better performance and they keep the space allocation information within the tablespace not in the data-dictionary.

Type of tablespaces

Permanent Tablespaces

This type stores permanent data, such as system data, user data and application data.

Undo Tablespace

A database running in automatic undo management mode transparently creates and manages undo data in the undo tablespace. Oracle uses undo data to provide read-consistency and to enable features such as Flashback query. You can only have one active undo tablespace at a time.

Temporary Tablespace

Temporary tablespaces are used for storing temporary data, as would be created when SQL statements perform sorts (order by)

Default Tablespaces

After creating a database there are some predefined tablespaces

EXAMPLE

This one contains sample schemas for documentation and educational uses.

SYSAUX

This is an auxiliary tablespace to the SYSTEM tablespace.

Using SYSUAX reduces the load on the SYSTEM tablespace and reduces maintenance because there are fewer tablespaces to monitor and maintain. Every Oracle Database 10g or higher level database must have a SYSAUX tablespace. Components that uses SYSAUX as their default tablespace are: Automatic Workload Repository, Oracle Streams, Oracle Text, Enterprise Manager Repository etc…

SYSTEM

Oracle uses this tablespace to managa the database. It contains the data dictionary, which is a central set of tables and view used as a read-only reference for a particular database. It contains also tables and views under the SYS schema that contain administrative information about the database.

TEMP

This one would be used for query sorting. A temporary tablespace should be defined as the default temp tablespace.

UNDOTBS1

This is used to store undo information. Every database must have an undo tablespace.

USERS

This is uesd to store permanent user objects and data. This sould be the default tablespace for new users.

Source: Oracle 2 Day DBA

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s