ORACLE DATABASE ARCHITECTURE

                                          ORACLE DATABASE ARCHITECTURE

Oracle database 11g architecture:-

Basically there are two main component of oracle database-instance and database itself.

An instance is consist of some memory structure and background processes. Whereas database as disk resource.

In this section following topic are related to the database architecture.

·         Processes architecture.

·         Oracle memory structure.

·         Oracle background process.

·         Overview physically and logically database architecture.

·         Overview of parameter file, server parameter file and password file.

·         Startup and shutdown.

1.  Processes architecture:-

A process is a thread of control or a mechanism in an operating system that can run a series of step.

Some operating system use term job or task.

A process generally has its own private memory area in which it runs.

In oracle database server has two general types of process.

·         User process

·         Oracle process

User process:-

A database user who need to request information from the database must first make connection with the oracle server, the connection is request using a database interface tool, such as sql*plus, and beginning the user process.

The user process does not interact with directly with oracle server.

Rather it generate call through the user program interface which create a session and start a server process.

Oracle process:-

Oracle create server process to handle request from connected user process.

A server communicate with the user process and interacts with oracle to carry out request from the associated user process.

Oracle can be configured to vary the number of user process for each server process.

In dedicated server configuration, a server process handle request for a single user process.

A share server configuration lets many user process share a small number of server processes, minimizing the number of server processes and maximizing the user of available system resource.

2. Oracle memory structures:-

Focus first on the memory components of the oracle instance. This set of memory components represent a living version of oracle that is available only when the instance is running.

·         System global area (SGA)

·         Program global area (PGA)

·         User global area (UGA)

System global area:-

This is a large share, memory segment that virtually all oracle process will access at one point or another.

SGA is used to store database information that is shared by database processes.

It contains data and control information of the oracle server and is allocated in the virtual memory if the computer where oracle resides.

The SGA store different component of memory usage that are designed to execute process to obtain data for user queries as quickly as possible while also maximizing the number of current user than access the oracle instance.

The SGA consists of three different item:-

·         The buffer cache

·         The shared pull

·         The redo log buffer

·         The large pull

·         The java pull

The buffer cache:-

The buffer cache consist of buffer that are the size of database block.

They are designed to store data block recently used by user sql statement in order to improve performance for subsequent select and data changed.

There are three place to store cache block from individual segments in SGA.

·         Default pool (Hot cache):-The location where all segments blocked are normally cached.

·         Keep pool (Warm cache):-An alternate buffer pool where by convention you assign that are accessed by fairly frequently.

·         Recycle pool:-An alternate buffer pool where by convention you assign large segment that you access very randomly and which would therefore cause excessive buffer flushing of many block from many segment.

Share pool:-

The share pool is where oracle cache many bit of program data.

When you parse a query the parse representation is cache here.

Before you go through the job of parsing an entry query, oracle search the share pool to see if the work has already is done.

Share pool manages memory on an LRU basis similar to buffer cache which is perfect for caching and reusing data.

Redo log buffer:-

The redo log buffer allow user process to write their redo log entries to a memory area in order to speed processing on the tracking of database change.

One fact that is important to remember about redo log and user process is that makes changes to the database must write an entry to the redo log in order to allow oracle to recover the change.

Since a memory to memory transfer is much faster than a memory to disk transfer, use of the redo log buffer can speed of database operation.

The data will not reside in the redo log buffer for very long.

§  Every three second.

§  Whenever someone committed.

§  When LGWR is asked switch log file.

§  When the redo log buffer get one-third full or contains 1MB of cache redo log data.

The large pool:-

The large pool is an optional area of memory in SGA.

It is used to relive the burden place on the shared pool.

It is also used for I/O process.

The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

Java pool:-

The java pool is used to services parsing of the java command.

It size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Program global area (PGA):-

A PGA is a memory region that control data and control information for a server processes.

PGA is the memory reserved for each user processes connecting to an oracle database and is allocated when a processes is created and deallocated when a process is terminated.

It is non-shared memory created by oracle database when a server process is started access to the PGA is exclusive to the server processes.

There is one PGA for each server process. Background processes also allocate their own PGA.

The total memory used by all individual PGA is known as the total instance PGA memory, and the collection of individual PGA is referred to as total instance of PGA. 

Contents of PGA:-

§  Private sql area

§  Session memory

§  Sql work area

Private Sql area:-

Contains data such as bind information and run-time memory structures.

Its contains persistent area which contains bind information and is freed only when the cursor is closed and run time area which is created as the first step of an executed request.

The number of private sql areas that can be allocated to a user process depends on the OPEN_CURSORS initialization parameter.

Session memory:-

Consists of memory allocated to hold a sessions variable and other info related session.

Sql work area:-

Used for memory intensive operations such as: sort, hash-join, bitmap merge, bitmap create.

User global area (UGA):-

This is memory associated with your session.

It located either in the SGA or the PGA depending whether you are connect to the database using a shared server (SGA) or a dedicated server (PGA).

Background processes:-

As is here there are both mandatory, optional and slave background process that are started whenever an oracle instance startup. These background processes serve all system users.

Mandatory background processes:-

§  process monitor process (PMON)

§  System monitor process (SMON)

§  Database writer process (DBWn)

§  Log writer process (LGWR)

§  Check point process (CKPT)

Optional processes:-

§  Archive process (ARCn)

§  Coordinator job queue (CJQO)

§  Dispatcher (number “nnn”) (Dnnn)

This query will display all background processes running to server a database:

Select pname from v$process where pname is not null order by pname;

PMON (process monitor):-

The process monitor (PMON) monitors the other background process and perform process recovery when a server or dispatcher process terminate abnormally.

PMON is responsible for cleaning up the database buffer cache and freeing resource that the client process was using.

e.g:- PMON resets the status of the active transaction table, releases locks are no longer required and remove the process ID from the list of active processes.

PMON also register information about the instance and dispatcher processes with the oracle net listener.

When an instance start PMON poll the listener to determine whether it is running.

If listener is running then PMON passed it relevant parameter. If it is not running then PMON periodically attempts to contact it.

SMON (system monitor):-

The system monitor (SMON) does system-level cleanup duties.

It is responsible for instance recovery by applying entries in the online redo log file to the data file. Other processes can call SMON when it is needed.

It is also performs other activities as outlined in the figure show below.

If an oracle instance fails, all information in memory not written to disk is lost. SMON is responsible for recovering the instance when the database is startup again. It does the following

Roll forward to recover data that was recorded in a redo log file, but that had not yet been recorded to a data file by DBWn. SMON reads the redo log file and applies the changes to the data blocks. This recovers all transactions that where committed because these were written to the redo log file prior to system failure.

Open the database to allow system users to login.

Rollback uncommitted transactions.

SMON also does limited space management. It is combine adjacent area of free space in the databases data file for table space that are dictionary managed.

It also deallocated temporary segment to create free space in the data file.

DBWn (Database writer):-

The database writers writes modified block from the database buffer cache to the data files.

One database writer process (DBWO) is sufficient for most systems.

The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.

The purpose of DBWn it to improve system performance by caching writes of database blocks from the databases buffer cache back to data files.

Block that have been modified and that need to be written back to disk are termed “dirty block”.

The DBWn also ensure that there are enough free buffers in the database buffer cache to server processes that may be reading data from data files into the database buffer cache.

Performance improve because by dealing writing changed database blocks back to disk a server process may find the data that is needed to meet a user process request already residing in memory.

DBWn writes to data files when one of these events occurs that is illustrated in the figure below.

LGWR (log writer):-

The log writer (LGWR) writes contents from the redo log buffer to the redo log file that is in use.

These are sequential writes since the redo log files record database modification based on the actual time that the modification takes place.

LGWR actually writes before the DBWn writes and only confirm that a commit operation has succeeded when the redo log buffer contents are successfully written to disk.

LGWR can also call the DBWn to write contents of the database buffer cache to disk.

The LGWR writes according to the events illustrated in the figure show below.

CKPT (checkpoint):-

The checkpoint (CPT) process writes information to update the database control files and header of the files.

A checkpoint identifies a point in time with regard to the redo log files where instance recovery is to begin should it be necessary.

It can tell DBWn to write block to disk.

A checkpoint is taken at a minimum once every three second.

Think of a checkpoint record as a starting point recovery. DBWn will have completed writing all buffer from the database buffer cache to disk prior to the checkpoint thus those record will not require recovery. This does the following:

Ensures modified data block in memory are regular written to disk- checkpoint can be call the DBWn process in order to ensure this and does so when writing a checkpoint record.

Reduce instance recovery time by minimizing the amount of work needed for recovery since only redo log file entries processed since the last checkpoint require recovery.

If a redo log file fills up and a switch is made to a new redo log file, the CKPT process also writes checkpoint information into the headers of the data files.

Checkpoint information written to control files includes the system change number (the SCN is a number store in control files and in the header of the database files that are used to ensure that all files in the system are synchronized). Location of which redo log file is to be used for recovery, and other information.

CHPK does not write data block or redo log block to disk- it can be call DBWn and LGWR as necessary.

MMON (manageability monitor and process) and MMNL (Manageability monitor lite process):-

The manageability monitor process (MMNO) perform tasks related to the automatic workload repository (AWR) – a repository of statistical data in the SYSAUX table pace.

The manageability monitor lite process (MMNL) writes statics from the active session history (ASH) buffer in the SGA to disk.

MMNL writes to disk when the ASH buffer is full.

 Optional background processes:-

·         ARCn

·         CJQO

·         Dnnn

·         FEDA

·         ARCn

·         ARCHIVELOG MODE

·         IN ARCHIVELOG MODE

ARCn (archiver):-

One or more archive processes copy the online redo log files to archival storage when they are full or a log switch occurs.

CJQO (coordinator job queue):-

This is the coordinator of job queue processes for an instance.

It monitor the JOB table and start job queue processes (JNNNN) as needed to execute job the JNNN processes execute job requests created by the DBMS_JOBS package.

Dnnn (Dispatcher number “nnn”):-

DOOO would be the first dispatcher process – dispatcher are optional background processes, present only when the shared server configuration is used.

FBDA (flashback data archiver process):-

This archives historical row of tracked tables into flashback data archive.

When a transaction containing DML on a tracked table commit this process store the pre-image of the rows into the flashback data archive. It also keeps metadata on the current rows.

FBDA automatically you will most often use ARCn, when you automatically archive redo log file information.

ARCn (Archiver):-

When the archiver (ARCn) is on optional background process, we cover it in more details because it is almost always used for production system storing mission critical information.

It perform task listed below

When a redo log files fill up oracle switch to the next redo log files.

The DBA creates several of these and the details of creating them are covered in a later modules.

If all redo log files fill up then oracle switches to the first one and uses them in round robin fashion by overwriting ones that have already been used.

Overwritten redo log files have information that once overwritten is lost forever.

ARCHIVELOG MODE:- 

If ARCn is in what is termed ARCHIVELOG mode, then as redo log files fill up, they are individually written to archive redo log files.

LGWR does not overwrite a redo log file until archiving has completed.

Committed data is not lost forever and can be recovered in the event of a disk failure.

Only the contents of the SGA will be lost if an instance fails.

IN ARCHIVELOG MODE:-

The redo log files are overwritten and not archived.

Recovery can only be made to the last full backup are lost, and you can see that this could cost the firm a lot of $$$.

When running in ARCHIVELOG MODE, the DBA is responsible to ensure that the archive redo log files do not consume all available disk space. Usually after two complete backup are made any archive redo log file for prior backup are deleted.

OVERVIEW OF PHYSICALLY BACKUP AND LOGICALLY ARCHIECTURE:-

PHYSCIALLY STRUCTURE:-

The physically structure includes three types of files:-

·         Control files

·         Data files

·         Redo log files

Data files:-

Every oracle database has one or more physically data files. The data files contains all the database data.

The data of logical database structure, such as tables and indexes, is physically stored in the data files allocated for a database.

The characteristics of data files are:

A data file can be associated with only one database.

Data files have certain characteristics set to let them automatically extend when the database run out of space.

One or more data files from a logical unit of database storage called tablespace.

Control files:-   

Every database has a control files. A control files contains entries that specify the physical structure of the database. It contains the following information:-

Database name

Name and location of data files and redo log files.

Time stamp of database creation.

Oracle can multiplex the control files that is simultaneously maintain a number of identical control files copies, to protect against a failure involving the control files.

Every time an instance of an oracle database is started, it control files identifies the database and redo log that must be opened for database operation to proceed.

A control files is also used to database recovery.

REDOLOF FILES:-

Every database has a set of two or more redo log files. The set of redo log files is collectively known as the redo log file for the database. A redo log is made up of redo entries also called redo record.

The primary function of the redo log is to record all change made to data.

To protect against a failure involving the redo log itself. Oracle allow a multiplexed redo log so that two or more copies of the redo log can be maintained on different disk.

ARCHIVE LOG FILES:- 

You can enabled automatic archiving of the redo log. Oracle automatic archive files when the database is ARCHIVE LOG MODE.

OVERVIEW OF LOGICAL DATABASE ARCHIECTURE:-

·         Tablespace

·         Segment

·         Extend

·         Oracle data block

·         Table

Tablespace:-

A database is divided into logical storage unit called tablespace, which group related logical structure together.

Each database is logically divided into one or more tablespace. One or more data files explicitly create for each tablespace to physically store the data of all logically structure in tablespace.

Every oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. Oracle create a automatically when the database is created.

The system default is to create a smallfile tablespace, which is the traditional types of tablespace. The SYSTEM and SYSAUX tablespace are created as smallfile tablespace.

ONLINE AND OFFLINE TABLESPACE:- 

A tablespace can be online accessible or offline not accessible.

A tablespace is generally online, so that users can be access the information in the tablespace.

However, sometimes a tablespace is taken offline to make a portion of the database unavailable while allowing normal access to the reminder of the database.

ORACLE DATABLOCK:-

Oracle database data store in the data block.

One data block corresponds to a specific number of bytes of physical database space on disk.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

In addition you specified up to five other block size.

A database user and allocates free database space in oracle database block.

EXTEND:-

The next level of logical database space is an extent.

An extend is a specific number of contiguous data block, obtained in a single allocation.

It is used to store a specific types of information.

SEGMENTS:-

Above extend the level of database storage is segment.

A segment is a set of extends allocated for a certain logical structure.

OVERVIEW OF PARAMETER FILES, SERVER PARAMETER FILES AND PASSWORD FILES:-

PARAMETER FILE (PFILE – INITSID.ORA):-

Parameter files contains a list of configuration parameter for that instance and database. You can read and change this files.

The files contains all oracle parameter files to configure a database instance.

In this files you can reset and change the buffer cache size, shared pool sized, redo log buffer sized etc.

SERVER PARAMETER FILE (SPFILESID.ORA):-

This files is a binary format and you can’t read this files.

You should create the server parameter files (CREATE SPFILE FROM PFILE) and startup your database using the spfile file.

PAASWORD FILE:-

The oracle orapwd command line utility assists the DBA with granting SYSDBA and SYSOPER privileges to the other users.