Exploring the Oracle Database Architecture

What are Daemon/ Application Processes

- Networking listeners - Grid Infrastructure daemons

what are database processes

- Server process: Connects to the Oracle instance and is started when a user establishes a session - Background processes: Are started when an Oracle instance is started

What is a Segment

A segment is a set of extents allocated for a certain logical structure such as: data segments, index segments, undo segments, temporary segments, etc.

what are Online redo log files

Allow for instance recovery of the database. If the database server crashes and does not lose any data files, the instance can recover the database with the information in these files.

what is a password file

Allows users using the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, and SYSASM roles to connect remotely to the instance and perform administrative tasks

What is an Extent

An extent is a specific number of contiguous Oracle data blocks (obtained in a single allocation) that are used to store a specific type of information. Oracle data blocks in an extent are logically contiguous but can be physically spread out on disk because of RAID striping and file system implementations.

Describe ARCn

Archiver Processes • Copy redo log files to a designated storage device after a log switch has occurred • Can collect transaction redo data and transmit that data to standby destinations If you anticipate a heavy workload for archiving (such as during bulk loading of data), you can increase the maximum number of Archiver processes. There can also be multiple archive log destinations. It is recommended that there be at least one Archiver process for each destination. The default is to have four Archiver processes.

what are backup files

Are used for database recovery. You typically restore a backup file when a media failure or user error has damaged or deleted the original file.

What is ASM?

Automatic Storage Management A portable and high-performance cluster file system It manages Oracle database files Manages application files with ASM Cluster File System Spreads data across disks to balance load Mirrors data in case of failures Solves storage management challenges

Describe the CKPT

Checkpoint Process Records checkpoint information in - Control file - Each data file header • Signals DBWn to write blocks to disk CKPT Checkpoint process A checkpoint is a data structure that defines a system change number (SCN) in the redo thread of a database. They are a crucial element of recovery. Oracle Database must update the control file and the headers of all data files to record the details of the checkpoint. This is done by the CKPT process.

define connection

Connection: Communication between a user process and an instance connection is a communication pathway between a user process and an Oracle Database instance

what are Archived redo log files

Contain an ongoing history of the data changes (redo) that are generated by the instance. Using these files and a backup of the database, you can recover a lost data file. That is, archive logs enable the recovery of restored data files.

what are data files

Contain the user or application data of the database, as well as metadata and the data dictionary

Describe DBWn

Database Writer Process The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. DBWn writes non- frequently used buffers to the data files from the tail of the LRU list so that processes can replace buffers when they need them. DBWn also writes from the tail of the checkpoint queue to keep the checkpoint advancing.

what are control files

Each database has one unique control file that contains data about the database itself (that is, physical database structure information). Multiple copies may be maintained to protect against total loss. It can also contain metadata related to backups. The control file is critical to the database. Without the control file, the database cannot be opened.

what is the relationship between databases, tablespaces, and datafiles

Each database is comprised of two or more tablespaces. Tablespaces can have multiple data files

what are trace files

Each server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, whereas other information is for Oracle Support Services.

Describe the UGA

Found in the PGA The UGA is composed of the following: • Cursor area for storing runtime information on cursors • User session data storage area for control information about a session • SQL working areas for processing SQL statements consisting of: - A sort area for functions that order data such as ORDER BY and GROUP BY - A hash area for performing hash joins of tables - A create bitmap area used in bitmap index creation common to data warehouses - A bitmap merge area used for resolving bitmap index plan execution In a shared server environment, multiple client users share the server process. In this model, the UGA is moved into the SGA (shared pool or large pool if configured) leaving the PGA with only stack space.

Describe the Java Pool

Found in the SGA Java pool memory is used to store all session-specific Java code and data in the JVM.

Describe the Large pool

Found in the SGA Provides large memory allocations for: • Session memory for the shared server and the Oracle XA interface (used where transactions interact with multiple databases) • I/O server processes • Oracle Database backup and restore operations • Parallel Query operations • Advanced Queuing memory table storage

Describe the Streams Pool

Found in the SGA Streams pool memory is used exclusively by Oracle Streams to: • Store buffered queue messages • Provide memory for Oracle Streams processes Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as needed when Oracle Streams is used.

what is a parameter file

Is used to define how the instance is configured when it starts up

Describe the LREG

Listener Registration Process Registers information about the database instance and dispatcher processes with the Oracle Net Listener LREG provides the listener with the following information: • Names of the database services • Name of the database instance associated with the services and its current and maximum load • Service handlers (dispatchers and dedicated servers) available for the instance, including their type, protocol addresses, and current and maximum load

Describe the LGWR

Log Writer Process Writes the redo log buffer to a redo log file on disk - When a user process commits a transaction - When an online redo log switch occurs - When the redo log buffer is one-third full or contains 1 MB of buffered data - Before a DBWn process writes modified buffers to disk - When three seconds have passed since the last write

is the large pool managed by LRU?

NO

From smallest to largest tell me the Logical database structures

Oracle data block extent segment tablespace database

two basic memory structures associated with an instance

PGA and SGA

Describe the PMON

Process Monitor Process Performs process recovery when a user process fails - Cleans up the database buffer cache - Frees resources that are used by the user process • Monitors sessions for idle session timeout cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes. PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle Database has terminated intentionally). Like SMON, PMON checks regularly to see whether it is needed; it can be called if another process detects the need for it.

describe the PGA

Program Global Area (PGA) is a memory region that contains data and control information for each server process. An Oracle server process services a client's requests. Each server process has its own private PGA that is allocated when the server process is started. Access to the PGA is exclusive to that server process, and the PGA is read and written only by the Oracle code acting on its behalf. The PGA is divided into two major areas: stack space and the user global area (UGA).

Describe the RECO

Recoverer Process • Used with the distributed database configuration • Automatically connects to other databases involved in in- doubt distributed transactions • Automatically resolves all in-doubt transactions • Removes any rows that correspond to in-doubt transactions If the RECO process fails to connect with a remote server, RECO automatically tries to connect again after a timed interval. However, RECO waits an increasing amount of time (growing exponentially) before it attempts another connection.

What are the mandatory Tablespaces?

SYSTEM and SYSAUX created upon creation of database • The SYSTEM tablespace is used for core functionality (for example, data dictionary tables). • The auxiliary SYSAUX tablespace is used for additional database components. • The SYSTEM and SYSAUX tablespaces should not be used for application data.

define Session

Session: Specific connection of a user to an instance through a user process A session represents the state of a current user login to the database instance

What does the PGA contain?

Stack Space and User Global Area(UGA)

Describe the SMON

System Monitor Process • Performs recovery at instance startup • Cleans up unused temporary segments SMON checks regularly to see whether the process is needed. Other processes can call SMON if they detect a need for it.

When does the DBWn actually write to disk?

The DBWn process writes dirty buffers to disk under the following conditions: • When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write. DBWn writes dirty buffers to disk asynchronously while performing other processing. • DBWn writes buffers to advance the checkpoint, which is the position in the redo thread (log) from which instance recovery begins. This log position is determined by the oldest dirty buffer in the buffer cache. In all cases, DBWn performs batched (multiblock) writes to improve efficiency. The number of blocks written in a multiblock write varies by operating system.

what are alert log files

These are special trace entries. The alert log of a database is a chronological log of messages and errors. Oracle recommends that you review the alert log periodically.

What is a dirty buffer?

When a buffer in the database buffer cache is modified, it is marked dirty and is added to the head of the checkpoint queue that is kept in system change number (SCN) order. This order therefore matches the order of redo that is written to the redo logs for these changed buffers.

Describe the Redo Log Buffer

circular buffer in the SGA Holds information about changes made to the database Contains redo entries that have the information to redo changes made by operations such as DML and DDL

What are the three files that comprise an Oracle database?

control files, data files, online redo log files

What is the nK Buffer Cache

found in the Database Buffer Cache in the SGA. Additional buffer caches can be configured to hold blocks of a size that is different from the default block size.

What is the Keep Pool?

found in the Database Buffer Cache in the SGA. The keep buffer pool is designed to retain buffers in memory longer than the LRU would normally retain them.

What is the Recycle Pool

found in the Database Buffer Cache in the SGA. The recycle buffer pool is designed to flush buffers from memory faster than the LRU normally would.

The Database Buffer Cache contains what?

found in the SGA. Holds copies of data blocks that are read from data files Contains keep pool, recycle pool, nK buffer cache

describe the Sever Results Cache

found in the shared pool in SGA. contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure. The server result cache contains result sets, not data blocks. Saves memory on repeat SQL results

describe the Shared SQL area

found in the shared pool in SGA. all SQL statements run through the Shared SQL area. Oracle recognizes when two users use same SQL. Saves memory on repeat SQL statements.

describe the Reserved Pool

found in the shared pool in the SGA can use to allocate large contiguous chunks of memory.

what is LRU algorithm

least recently used (LRU) helps to ensure that the most recently used blocks tend to stay in memory to minimize disk access

3 major structures in Oracle Database server

memory structures, process structures, storage structures

how many databases associated with an instance?

one and only one database

What are the additional files used during the operation of the database

parameter file, password file, backup files, archived redo log files, trace files, alert log file

The SGA contains what data structures

shared pool, database buffer cache, redo log buffer, large pool, java pool, streams pool, fixed SGA

What is a user process

the application or tool that connects to the Oracle database

describe the data dictionary

the data dictionary is found in the Shared pool in the SGA. It is both in the data dictionary cache (row cache: holds data as rows instead of buffers) and in the library cache. It is a collection of database tables and views containing reference info about the database, its structures, and users

What is a data block?

the finest level of granularity. Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical space on the disk

Shared pool contains what?

the shared pool contains the library cache and Shared SQL area, the data dictionary cache, the server results cache

What are the three major Process groups in an oracle database system

user processes, database processes, Daemon/Application Processes

Name 8 background processes

• Database Writer process (DBWn) • Log Writer process (LGWR) • Checkpoint process (CKPT) • System monitor process (SMON) • Process monitor process (PMON) • Recoverer process (RECO) • Listener registration process (LREG) • Manageability monitor process (MMON) • Manageability monitor lite process (MMNL) • Job queue coordinator (CJQ0) • Job slave processes (Jnnn) • Archiver processes (ARCn) • Queue monitor processes (QMNn)