Oracle 11g / 12c Architecture - Explained in Detail!

What is Oracle Sever ?

Oracle Server is a combination of Instance and database .

What is an Oracle instance ?

Instance is a gateway in-order to access the database.

Without instance we can’t access database and its objects .

Oracle Instance

An Oracle Instance consists of two different sets of components

• Background Processes
• Memory Structure. 

In Oracle 11g every Database has it's own instance. 

Oracle 12c is a Multitenant Architecture 

Exactly one root

The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. The root container is named CDB$ROOT.

• Exactly one seed PDB
  The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.
• Zero or more user-created PDBs
  A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.

The following figure shows a CDB with four containers: the root, seed, and two PDBs. Each PDB has its own dedicated application. A different PDB administrator manages each PDB. A common user exists across a CDB with a single identity. In this example, common user SYS can manage the root and every PDB. At the physical level, this CDB has a database instance and database files, just as a non-CDB does.

Instance is a combination of SGA (Memory components)  and Background process.

what is SGA ?

SGA stands for Shared Global Area or System global Area.

System Global Area is also known as Memory Area.

With in a single db ,we will have single instance.

with in a single instance, we will have one SGA.

In one database we can create n  no.of users but all the user will connect to one memory structure ,Thats why we can call it as Shared Global Area.

Whenever we system started ,some portion of RAM will be allocated to Kernel,when the Instance started some portion of RAM will be allocated to SGA.

SGA takes memory from RAM.

.An SGA comprises of Buffer cache, Redolog buffers and Shared pool area.

Automatic Shared Memory Management(ASSM) :

DBA can simply specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter.

The Oracle DB will automatically distribute this memory among various sub components to ensure most effective memory utilization.

When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration.

The DB automatically distributes available memory among various components as required, allowing system to maximize use of all available SGA memory.

SGA contains two types components

1.Mandatory Components

2.Optional Components

Mandatory Components

The Mandatory Components of SGA is

1.Database buffer cache

2.Shared pool

3. Redolog Buffer

Database buffer cache

DBBC contains recently used blocks.

DBBC have three types buffers

1.Free buffers :  it contains empty blocks

2.Pinned Buffer : It contains currently having latest transactions

3.Dirty Buffers : It contains all modified blocks but still available DBBC is known as Dirty Buffers

Shared Pool :

These are divided into two types

1.Library Cache(LC) : LC contains all the SQL ID’s ,Hash Value and Execution plans.

2.Data Dictionary Cache(DDC) : DDC contains all the metadata information.

Redo log Buffers :

RLB contains all the latest transaction information.

Optional Components

The Optional Components of SGA is

1.Streams Pool

2.Large Pool

3.Java pool

4.Result Cache etc..,

Background Processes

What is a process ?

A program under execution is known as Processs

What is Background process ?

A process which is running at the background is know as Background process.

Background processes are  PMON, SMON, RECO, DBW0, LGWR, CKPT, D000 and others.

These processes perform input/output and monitor other Oracle processes to provide good performance and database reliability.

Theses background process are divided into types They are :

1.Mandatory Background Process

2.Optional Background Process

Mandatory Background Process

The Mandatory Background Process are

1.SMON(System Monitor)

2.PMON(Process Monitor)

3.LGWR(Log Writer)

4.DBWR(Database Writer)

5.CKPT(Check pointer)

6.RECO(recover-er)

System Monitor

Process Name: SMON

Max Processes: 1

The system monitor performs recovery when a failed instance starts up again. In a Real Application Clusters database, the SMON process of one instance can perform instance recovery for other instances that have failed.

SMON also cleans up temporary segments that are no longer in use.

These transactions are eventually recovered by SMON when the tablespace or file is brought back online.

It also coalesces contiguous free extents in dictionary-managed tablespaces that have PCTINCREASE set to a non-zero value.

SMON checks the SCN in all datafile headers when the database is started.

Everything is OK if these entire SCNs match the SCN found in the controlfile. If the SCNs don’t match, the database is in an inconsistent state.

Process Monitor

Process Name: PMON

Max Processes: 1

This process monitor performs process recovery when a user process fails. It will rollback uncommitted transactions.

PMON is also responsible for cleaning up the database buffer cache and freeing resources that were allocated to a process.

PMON also registers information about the instance and dispatcher processes with network listener.

PMON also checks on the dispatcher processes and server processes and restarts them if they have failed.

Log Writer

Process Name: LGWR

Max Processes: 1

The log writer process writes data from the redo log buffers to the redo log files on disk. The writer is activated under the following conditions:

When a transaction is committed, a System Change Number (SCN) is generated and tagged to it. Log writer puts a commit record in the redo log buffer and writes it to disk immediately along with the transactions redo entries.

Changes to actual data blocks are deferred until a convenient time (Fast-Commit Mechanism).

Logwriter will invoke in three states.

1.Every 3 seconds.

2.When the redo log buffer is 1/3 full.

3.Commit

When DBWn signals the writing of redo records to disk. All redo records associated with changes in the block buffers must be written to disk first (The write-ahead protocol).

While writing dirty buffers, if the DBWn process finds that some redo information has not been written, it signals the LGWR to write the information and waits until the control is returned.

Log writer will write synchronously to the redo log groups in a circular fashion. If any damage is identified with a redo log file, the log writer will log an error in the LGWR trace file and the system Alert Log. Sometimes, when additional redo log buffer space is required, the LGWR will even write uncommitted redo log entries to release the held buffers. LGWR can also use group commits (multiple committed transaction’s redo entries taken together) to write to redo logs when a database is undergoing heavy write operations.

Database Writer

Process Name: DBWR

Max Processes: 100

The database writer process (DBW) writes the contents of database buffers to data files.

DBW processes write modified buffers in the database buffer cache to disk.

Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes—DBW1 through DBW9, DBWa through DBWz, and BW36 through BW99— to improve write performance if your system modifies data heavily. These additional DBW processes are not useful on uniprocessor systems.

The DBW 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 DBW to write.

DBW writes dirty buffers to disk asynchronously if possible while performing other processing.

DBW periodically writes buffers to advance the checkpoint, which is the position in the redo thread from which instance recovery begins.

The log position of the checkpoint is determined by the oldest dirty buffer in the buffer cache.

In many cases the blocks that DBW writes are scattered throughout the disk. Thus, the writes tend to be slower than the sequential writes performed by LGWR. DBW performs multiblock writes when possible to improve efficiency.

The number of blocks written in a multiblock write varies by operating system.

When a checkpoint is issued. Please see checkpoint process below.

When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers.

1.Every 3 seconds time-out.

2.When a log switch occurs

3.When Dirty-Blocks are becoming LRU Blocks

4.When Dirty-Blocks reaches to threshold value.

5.When Database shuts down.

Checkpoint Process

Process Name: CKPT

Max processes: 1

Checkpoint process signals the synchronization of all database files with the checkpoint information. It ensures data consistency and faster database recovery in case of a crash. CKPT ensures that all database changes present in the buffer cache at that point are written to the data files, the actual writing is done by the Database Writer process.

The datafile headers and the control files are updated with the latest SCN (when the checkpoint occurred) this is done by the log writer process. The CKPT process is invoked under the following conditions:

When a log switch is done.

When the time specified by the initialization parameter LOG_CHECKPOINT_TIMEOUT exists between the incremental checkpoint and the tail of the log; this is in seconds.

When the number of blocks specified by the initialization parameter LOG_CHECKPOINT_INTERVAL exists between the incremental checkpoint and the tail of the log; these are OS blocks.

The number of buffers specified by the initialization parameter FAST_START_IO_TARGET required to perform roll-forward is reached.

Oracle 9i onwards, the time specified by the initialization parameter FAST_START_MTTR_TARGET is reached; this is in seconds and specifies the time required for

a crash recovery. The parameter FAST_START_MTTR_TARGET replaces LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET, but these parameters can still be used.

When the ALTER SYSTEM SWITCH LOGFILE command is issued.

When the ALTER SYSTEM CHECKPOINT command is issued.

Incremental Checkpoints initiate the writing of recovery information to datafile headers and control files. Database writer is not signaled to perform buffer cache flushing activity here.

Recoverer

Process Name: RECO

Max processes: 1

The Recoverer process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

Listener Registration Process

Process Name: LREG

The Listener Registration Process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net Listener. When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running, then LREG passes it relevant parameters. If it is not running, then LREG periodically attempts to contact it.

NOTE: In releases before Oracle Database 12c, PMON performed the listener registration.

Optional Background Process

1.ARCH

2.RVWR

3.LTWR

4.LAS

5.LB

6.ASMB

7.RBAL etc…,

Extended Information about Oracle Architecture 

An Oracle Database consists of a database and at least one instance.

An instance, or database instance, is the combination of memory and processes that are a part of a running installation and a database is a set of files that store data.

The following picture illustrates the Oracle Database server architecture.

Sometimes, a database instance is referred to as an entire running database. However, it is important to understand the distinctions between the two.

First, you can start a database instance without having it accessing any database files. This is how you create a database, starting an instance first and creating the database from within the instance.

Second, an instance can access only one database at a time. When you start an instance, the next step is to mount that instance to a database. And an instance can mount only one database at a single point in time.

Third, multiple database instances can access the same database. In a clustering environment, many instances on several servers access a central database to enable high availability and scalability.

Finally, a database can exist without an instance. However, it would be unusable because it is just a set of files.

Oracle Database

One of the essential tasks of the Oracle Database is to store data. The following section briefly describes the physical and logical storage structure of an Oracle Database.

Physical storage structures

The physical storage structures are simply files that store data. When you execute a CREATE DATABASEstatement to create a new database, Oracle creates the following files:

• Data files: data files contain real data, e.g., sales order and customer data. The data of logical database structures such as tables and indexes are physically stored in the data files.
• Control files: every database has a control file that contains metadata. The metadata describes the physical structure of the database including the database name and the locations of data files.
• Online redo log files: every database has an online redo log that consists of two or more online redo log files. An online redo log is made up of redo entries that record all changes made to the data.

Besides these files, an Oracle database includes other important files such as parameter files, network files, backup files, and archived redo log files for backup and recovery.

Logical Storage Structures

Oracle Database uses a logical storage structure for fine-grained control of disk space usage. The following are logical storage structures in an Oracle Database:

• Data blocks: a data block corresponds to a number of bytes on the disk. Oracle stores data in data blocks. Data blocks are also referred to as logical blocks, Oracle blocks or pages.
• Extents: An extent is a specific number of logically contiguous data blocks used to store the particular type of information.
• Segments: a segment is a set of extents allocated for storing database objects, e.g., a table or an index.
• Tablespaces: a database is divided into logical storage units called tablespaces. A tablespace is a logical container for a segment. Each tablespace consists of at least one data file.

The following picture illustrates segments, extents and data blocks within a tablespace:

And the next figure shows the relationship between logical and physical storage structures:

Database Instance

A Database Instance is an interface between client applications (users) and the database. An Oracle instance consists of three main parts: System Global Area (SGA), Program Global Area (PGA), and background processes.

The SGA is a shared memory structure allocated when the instance started up and released when it is shut down. The SGA is a group of shared memory structures that contain data and control information for one database instance.

Different from the SGA, which is available to all processes, PGA is a private memory area allocated to each session when the session started and released when the session ends.

Major Oracle Database’s background processes

The following are the major background processes of an Oracle instance:

• PMON is the process monitor that regulates all other processes. PMON cleans up abnormally connected database connections and automatically registers a database instance with the listener process. PMON is a process that must be alive in an Oracle database.
• SMON is the system monitor process that performs system-level clean-up operation. It has two primary responsibilities including automatically instance recovery in the event of a failed instance, e.g., power failure and cleaning up of temporary files.
• DBWn is the database writer. Oracle performs every operation in memory instead of the disk because processing in memory is faster and more efficient than on disk. The DBWn process reads data from disk and writes it back to the disk. An Oracle instance has many database writers DBW0, DBW1, DBW2, and so on.
• CKPT is the checkpoint process. In Oracle, data that is on disk is called block and the data which in memory is called buffer. When a block is written to the buffer and changed, the buffer becomes dirty, and it needs to be written down to the disk. The CKPT process updates the control and data file headers with checkpoint information and signals writing of dirty buffers to disk. Note that Oracle 12c allows both full and incremental checkpoints.

• LGWR is the log writer process which is the key to the recoverability architecture. Every change occurs in the database is written out to a file called redo log for recovery purposes. And these changes are written and logged by LGWR process. The LGWR process first writes the changes to memory and then disk as redo logs which then can be used for recovery.
• ARCn is the archiver process that copies the content of redo logs to archive redo log files. The archiver process can have multiple processes such as ARC0, ARC1, and ARC3, which allow the archiver to write to various destinations such as D: drive, E drive or other storage.
• MMON is the manageability monitor process that gathers performance metrics.
• MMAN is the memory manager that automatically manages memory in an Oracle database.
• LREG is the listener registration process that registers information on the database instance and dispatcher processes with the Oracle Net Listener.