Oracle Database

by E-Computer Concepts September 22, 2019 at 7:53 am

A database is a centralized repository of organizational data. The oracle Database Server allows you to create, manage and retrieve data in the database. A database administrator who is responsible for administering a database should have a complete and thorough understanding of the architecture of the database.

An analogy could be a service technician trying to fix a car that is having a problem. The technician wouldn’t know where to start unless he knows very clearly what the various components if the car are, what they do, how they function and how they interact with each other. This is absolutely essential before he can start to find a solution to the problem. The same goes for a database.

If a user comes to an administrator with a certain problem, th DBA can’t come up with the right solution unless he knows everything he possibly can about the database.

Oracle Architecture

The Oracle server consists of physical files and memory components. The Oracle Database product is made up three main components namely:

  • The Oracle Server – This is the oracle database management system that is able to store, manage and manipulate data. It consists of all the files, structures, processes that form Oracle Database. The Oracle server is made up of an Oracle instance and an Oracle database.
  • The Oracle Instance – Consists of the memory components of Oracle and various background processes.
  • The Oracle Database – This is the centralized repository where the data is stored. It has a physical structure that is visible to the Operating system made up of operating files and a logical structure that is recognized only the Oracle Server.

Oracle Instance

The Oracle Instance is made up of a number of memory related components and background processes. The instance is created in memory every time the database is started. The instance is associated to only a single database at any time. The memory components of the Oracle Database are also known as the System Global Area (SGA).

The Instance and its components are configured using a file known as the Parameter file. In Oracle there are two types of initialization files namely

  • The ‘Server Parameter File’ (SPFILE)
  • The ‘Parameter File’ (PFILE)

The parameter file hold parameters that can set the sizes of the various buffers and pools of the SGA. You can also specify the name of the instance, the name of the database and other size-related parameters needed by the Oracle Instance.

Memory Components of the SGA

The Oracle Instance is made up of the System Global Area (SGA) and the background processes. The SGA is made up of the

  • Redo Log Buffer
  • The Database Buffer Cache
  • The Shared Pool
  • The Java Pool
  • The Large Pool
  • The Streams Pool

Redo Log Buffer

A circular buffer that stores all changes made in the database. It contains are transferred periodically from memory to the online redo log files on disk by the Log Writer (LGWR) background process. The contents of the redo log buffer are essential for instance recovery purposes. The size of the redo log buffer is determined by the LOG_BUFFER initialization parameter.

Database Buffer Cache

An area in memory that holds all the blocks read in from disk for query or modification. Blocks that need to be modified, are modified in memory and are written back to back periodically. Modified blocks that have not yet been written to disk are known as dirty blocks. This buffer is managed in a manner that free blocks are always made available for new blocks being read into memory. The contents of the database buffer cache are written to datafiles on disk by the Database Writer (DBWR) background process.

The size of the default database buffer cache is sized by the DB_CACHE_SIZE initialization parameter. The blocks that are part of the Database Buffer Cache are sized by the DB_BLOCK_SIZE initialization parameter. This is the default buffer cache.

Shared Pool

The contents of this memory area are shared by multiple users and hence the name shared pool. Two specific caches form the shared pool. The sizes of the two caches are not set individually and are automatically determined by the Oracle Server. However it is possible to set the overall size of the SHARED_POOL_SIZE initialization parameter.

Data Dictionary Cache

Sometimes referred to as the Row Cache. The cache consists of blocks that holds data dictionary information read in from the datafiles. Reads that are made to the data dictionary information in the datafiles. Reads that are made to the data dictionary information in the datafiles are also called recursive reads. Typical information stored in this cache is user account information, table, index and other object definitions, privileges and other relevant information that is frequently accessed. This cache is also managed using the Latest Recently Used (LRU) Algorithm, which is on a fisrt-in first -out basis.

Larger Pool

This is an optional pool. The size of this memory area is determined by the LARGE_POOL_SIZE initialization parameter. It is used for handling large I/O requests of server processes. Its main functions are to provide memory for session memory (UGA) for the shared server environment, parallel execution message buffers.

Java Pool

This memory area is used by all session-specific Java code and data within the Java Virtual Machine (JVM). Its size is determined by the JAVA_POOL_SIZE initialization parameter.

Stream Pool

This memory area is used by the Oracle Streams Product for its functioning.

Automatic Shared Memory Management

Automatic Shared Memory Management has been introduced in Oracle 9i. using this feature all you need to specify is the total amount of memory that will be used by the SGA components. The parameter that needs to be configured for automatic shared memory management is the SGA_TARGET initialization parameter.

Background Processes

The background processes of the Oracle instance are responsible for performing asynchronous I/O functions between the Oracle instance and the physical files of the Oracle database that exist on disk. There are 5 mandatory background processes in Oracle.

There are the Database Writer (DBWR), the Log Writer (LGWR). the Checkpoint process (CKPT), the System Monitor (SMON) and the Process Monitor (PMON). Other background processes can be started based on certain additional functionality required in the database.

Program Global Area (PGA)

This is memory area that is associated with a server process. It contains data and control information held by a single server process or background process. It is created when a user process is created and is released when the user process is terminated. This memory area is not shared by server processes. The PGA is used to process SQL statements and to hold logon and other session information. The contents of the PGA include:

Private SQL Area

Private SQL area contains bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. The private SQL area is in turn made up the persistent area that contains bind information and a Runtime area that is created as soon as an execute request is initiated. In a dedicated server environment the Private SQL area is created in the Program Global Area (PGA) of their server process. In a shared server environment, it is located in the System Global Area. The number of private SQL areas that a user process can allocate is limited to the OPEN_CURSORS initialization parameter that takes a default value of 50.

Session Maemory

This is a memory area that holds a session’s variables and session-related information. In a shared server environment, this memory area is located in the SGA and is shared by server processes.

SQL Work Area

This memory area is used for operations such as Sorting, Hash-joins, Bitmap Merge and Bitmap Create operations.

Physical Files of the Database

Recalling an earlier discussion, the Oracle Server is made up of the Oracle Database and Oracle Instance. The Oracle database has a physical and a logical structure. The physical structure of the database. All the data of the database is stored in the physical files.

There are three types of files namely:

Data Files – These files contain the data of the database. All objects created in the database by users have their data physical stored in the datafiles. Datafiles contain the data dictionary and user created data.

Control Files – This is a very important file that is required for the Oracle database to function. If any one of the control files is unavailable the database is shutdown. Hence it recommended that multiple copies of the control files are maintained in the database on separate disks. The control file keeps a record of the names, size and locations different physical files of the database.

Redo Log Files – These files contain a record of all the changes made in the database. These changes are referred to as redo entries. Redo entries can be used to recover the database in the event of an instance failure.

Add Comment