1. Oracle 12C Database Architecture

Oracle database architecture divided into 3 parts:

i)   Process Structure
ii)  Memory Structure
iii) Storage Structure

Database = Storage Structure
Instance = Memory Structure + Process Structure
Oracle Server = Instance + Database

Instance: After oracle server software installation we create a database on server. When we start this database oracle allocates a memory called SGA (System Global Area) and starts some oracle processes. This combination of the SGA and oracle processes called an oracle instance.

1.Instance Memory Structure: Oracle database include diffrent memory structures as following:

• System Global Area(SGA): Oracle allot memory for the SGA when the instance is started and de-allocates it when the instance is shut down. All the users connect with database share the information maintained in this area. The SGA have 3 mandatory components and 4 optional components.
• Program Global Area(PGA): A memory stored for each oracle process.
• User Global Area(UGA): The UGA is memory associated with a user session.

1.1.User Global Area:

For Dedicated sessions, the UGA is a part of PGA that controls user sessions space for sorting and hash joins. For Shared Session,  the UGA is inside the SGA Large Pool. 
Contains Session Variables and OLAP Pool. For PL/SQL packages in memory, the UGA contains the package state, a set of values stored in all the package variables at a specific time. The package changes its state when a package subprogram changes the variables. An OLAP session opens automatically whenever a user queries a dimensional object such as a square, rectangle.

1.2.Program Global Area:
PGA is process specific not shared by other processes, never allocates in the SGA.
Every server process has a PGA memory area. Contains session-dependent variables required by a dedicated or shared server process. Contents of PGA are Private SQL area and SQL work area and Session Memory.
                

 SQL Work Area: 
Private allocation of PGA memory used for memory-intensive operations. Ex: A sort operator uses the sort area to sort a bundle of rows. A hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes. If the data does not fit into a work area, then Oracle Database divides the input data into smaller pieces. In this way, the database processes few data pieces in memory while writing the rest to temporary disk storage for processing later.
        
Private SQL Area: 
Holds information about a parsed SQL statement and session-related information for processing. Divided in The run-time area(contains query execution stat information) and The Persistent area(contains bind variable values). This area is manages by client process.

Session Memory:
Hold a session's variables (logon information) and other information related to the session.


1.3.System Global Area:

• Database Buffer Cache:
• Caches data blocks(data/rows of interest) from database. 
• Hold blocks that have been accessed recently.
• It is shared among all the users connection to database.
• Least recently used(LRU) algorithm for contents in the database buffer cache.

       There are different types of buffers:

  1.Dirty buffers contain buffer blocks that has changed and need to be written to the data files.
  2.Free buffers/Default cache:Free to be overwritten.When DBWn(Database read & writer) reads data from the disk, free buffers hold this data.The DB_CACHE_SIZE parameter specifies the size of database buffer cache DEFAULT pool.
  3.Pinned buffers/Keep Cache(Optional):If you want certain data not to be aged from memory , configure the KEEP pool and use the ALTER TABLE statement to specify which tables should use the KEEP pool.Also uses the LRU algorithm.DB_KEEP_CACHE_SIZE configure KEEP pool .
4.Recycle Cache(Optional):If you do not want to age out good data from the default cache for temporary data, you may specify such tables to have the RECYCLE pool instead of the default.It removes the buffers from the memory as soon as they are not needed. DB_RECYCLE_CACHE_SIZE parameters configure the RECYCLE pool.


Redo Log Buffer:

• Holds information about the changes made to the data, known as redo entries or changed vectors.
• Used to redo the changes in case of failure.
• LOG_BUFFER determines the size of the redo log buffer cache.
• Size of the log buffer is static, can't change after instance startup.
• Background process LGWR writes the redo log information to the online redo log files.

Shared Pool:

Holds information such as SQL, PL/SQL procedures and packages, locks, character-set information, security attributes etc. Shared pool contains THE LIBRARY CACHE and the DATA DICTIONARY CACHE.

THE LIBRARY Cache:
Contains the shared SQL areas, private SQL areas, PL/SQL programs, and control structures (locks and library cache handles). The shared SQL area stores recently executed SQL statements their parse tree and execution plans.Information in the shared SQL area used for all users.The private SQL area contains values for the bind variables (persistent area) and runtime buffers (runtime area). Oracle creates the runtime area as the first step of an execute request and frees the runtime area after the statement has been executed/all rows have been fetched or the query has been canceled.
A PL/SQL program unit is executed, the code is moved to the shared PL/SQL area, and the individual SQL commands within the program unit are moved to the shared SQL area.
The Control Structure is maintained internally by Oracle. Various locks, latches, and other control structures reside here, and any server process that requires this information can access it.

The Data Dictionary Cache:
Known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). Holds the most recently used object definitions: descriptions of tables, indexes, users, and other metadata definitions. Stores object definitions so that statements can be parsed quickly.


Result Cache:

Hold the SQL queries and PL/SQL function results to use next time for same query/function.

Reserved Pool:
Used to allocate large chunks of memory. Size is determined by the SHARED_POOL_RESERVED_SIZE initialization parameter.

Large Pool:
Optional area, that can configure to provide large memory allocations for specific database operations such as an RMAN backup or restore, shared server processes, Parallel execution servers. Large pool does not follows an LRU algorithem to age out objects. Parameter LARGE_POOL_SIZE determines the size of the large pool.

Java Pool:
Optional area to provide memory for Java operations, used for the heap space needed to instantiate the Java objects. Parameter JAVA_POOL_SIZE determines the size of the Java pool. Java code is not cached in the Java pool, cached in the shared pool, in the same way that PL/SQL code is cached.

Streams Pool:
Used by Oracle Streams.The mechanism used by Streams is to extract change vectors from the redo log and to reconstruct statements that were executed from these—or statements that would have the same net effect. These statements are executed at the remote database. The processes that extract changes from redo and the processes that apply the changes need memory: this memory is the Streams pool.

Fixed SGA:

• An internal housekeeping area.
• Contains information about the state of the database and the instance, which the background processes need to access.
• Information communicated between processes, such as information about locks.
• The size of the fixed SGA is set by Oracle Database and cannot be altered manually. The fixed SGA size can change from release to release.

2.Instance Background Processes:

Processes are launched when the instance is started and run until it is terminated. Few are must processes,others available if certain options are enable. To check the running background process in oracle database Session wise or Instance wise     SQL> select program from v$session order by program;
SQL> select program from v$process order by program;
To check the running process of oracle in linux                                                                      [oracle@prod ~]$ ps -ef|grep oracle|wc -l                          //Shows the number of processes// 
[oracle@prod ~]$ ps -ef|grep oracle


2.1.SMON:

• Mounts a database by locating and valodating the database controlfile.
• Opens a database by locating and validating all the datafiles and online log files.
• Performs instance or crash recovery at database startup by using the online redo log files.
• Clean temporary segments in the tablespaces that are no longer used and for coalescing the contiguous free space in the dictionary-managed tablespaces.
• Recovers dead transactions were skipped during instance recovery because of file-read or offline errors when tablespace or datafile is brought back online.

2.2.PMON:

Cleans failed user processes and frees up all the resources used by the failed process.
Rolled back the active transaction of abnormally terminated sessions.

2.3.Database Writer (DBWn):

• In Oracle 11g,we have upto 20 database writer(DBW0-9,DBWa-j).
• In Oracle 12C,we have upto 100 database writer(DBW0-9,DBWa-j & 37-100 are BW36-BW99).
• Write the contents of the dirty buffers to the datafiles OR from datafiles to Database Buffer cache.
• Oracle starts one database writer process by default when the instance starts.
• Four conditions that will cause DBWn to write: no free buffers, too many dirty buffers, a three-second timeout, when there is a checkpoint occurs(all dirty buffers written to disk).

2.4.Log Writer (LGWR):

• Server processes write every transaction synchronously that make changes in blocks of Database Buffer cache to redo log buffers.
• Writes the redo log buffer to the online redo log files.
• There are few circumstances that will cause LGWR to flush the log buffer: if a session issues a COMMIT; if the log buffer is one-third full; Three seconds since the last LGWR write; When redo log buffer contains 1MB worth of redo information ; Whenever a database checkpoint occurs .

2.5.CheckPoint (CKPT):

• Oracle identifies the commit transaction with a unique number called the system change number (SCN).
• A checkpoint is when the DBWn process writes all the dirty buffers to the datafiles.
• When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. The CKPT process updates only the control file with the checkpoint position, not the data files. DBWn process writes the actual data blocks to the data files.

  Types of CheckPoints:

     
    Full checkpoints: A full checkpoint writes all dirty buffers from all instances. Both
        controlfile and datafile headers are updated during this checkpoint. This occurs during:

        Alter system checkpoint global                                 //Generate a CheckPoint
        Alter database begin backup                                     // Begin database backup
        Alter database close                                                    // Close database
        Shutdown Immediate/Transactional                     // Perform clean/complete shutdown

Thread checkpoints:The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint.Controlfile and datafile headers are updated during this checkpoint.Occures in the following situations:

• Consistent database shutdown
• Alter system checkpoint local
• Online redo log switch

Tablespace and Datafile Checkpoint:Writes block images to the database for all dirty buffers  for all files of a tablespace from all instances. Controlfile and datafile headers are updated during this checkpoint. Occurs in following situations

        Alter tablespace … offline
        Alter tablespace … begin backup
        Alter tablespace … read on
        Alter database datafile resize ( while shrinking a data file)

Log Switch Checkpoint: 

• Occurs Writes the contents of "some" dirty buffers to the database.
• Controlfile and datafile headers are updated with checkpoint_change#.

Instance Recovery Checkpoint: Writes recovered block back to datafiles. Trigger as soon as SMON is done with instance recovery.

RBR Checkpoint: It's called a Reuse Block Range checkpoint, usually appears post index rebuild operations.

Multiple Object Checkpoint: Triggered whenever a single operation causes checkpoints on multiple objects i.e. dropping partitioned table or index.

Parallel Query Checkpoint: Writes block images to the database for all dirty buffer  belonging to objects accessed by the query from all instances. It's mandatory to maintain consistency. Occurs in following situations:
                Parallel Query
                Parallel Query component of PDML or PDDL.

Incremental checkpoints: Occurs during redo log switch. In incremental checkpoint CKPT  updates only control file, not the header file of data files.

Object Checkpoint: Writes block images to the database for all dirty buffers belonging to an object from all instances. Occurs in following situations:
                Drop table
                Drop table … purge
                Truncate table
                Drop Index

2.6.MMON, the Manageability Monitor:
MMON regularly (by default, every hour) captures statistics(activity and performance accumulated by instance in the SGA, and their current values canbe interrogated by issuing SQL queries) from the SGA and writes them to the data dictionary, where they can be stored indefinitely (though by default, they are kept for only eight days).
Gathers a set of statistics (known as a snapshot), also launches the Automatic Database Diagnostic Monitor, the ADDM(analyses database activity using current and previous snapshots).


2.7.MMNL, the Manageability Monitor Light:
A process that assists the MMON. If memory buffers used to accumulate statistical information fill before MMON is due to flush them, MMNL will take responsibility for flushing the data.


2.8.MMAN, the Memory Manager:

• Responsible for automatic management of memory allocations.
• MMAN will observe the demand for PGA memory and SGA memory, and allocate memory to them as needed, while keeping the total allocated memory within a limit set by the DBA.

2.9.The Archiver ARCn:

• Runs only when the database is in ARCHIVELOG mode and automatic archiving is enabled.
• Can be 30 archive processes from ARC0-ARC29.
• Make archive offline files of online redo log files to provide to preserve a complete history of all changes applied to the data so that they can be used for recovery.

2.10.Listener Registration Process (LREG):

Registers information about the database instance and dispatcher processes with the Oracle Net Listener. During instance startup, LREG select 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.

Storage Structure

The 3 file types that must be present in Oracle Database are The Control File, Online Redo log file, Data Files. Other required files are Parameter file, Password File, Archive Redo Log Files, Log and Trace File.

Controlfile:

• Required to mount a database during instance startup process.
• Contains the information about database, location and sizes of the data files, redo log files, Tablespace, Redo log, Archived log, RMAN backup and Checkpoint.
• You can save upto 8 multiplexed copies of Controlfile on different location.
• CKPT background process automatically updates Controlfile and synchronize the all copies of Controlfile.
• You can check the conrolfile location in parameter file or use CONTROLFILE view...         SQL> show parameter controlfiles;                                                                                         SQL> select name from v$controlfile;
• To check the types of information contained by controlfile use.....                                         SQL> select name from v$controlfile_record_section;

Online Redo Log files:

• Called redo thread for database instance.
• Every instance have a single redo thread. So in single instance database there is only 1 redo thread. And in RAC environment No. of Redo Thread=No. of instance .
• Contains undo data for permanent objects and changes made to database.
• LGWR process writes the redo log buffer in Online Redo Log files.
• To check the online redo group and member use logfile view.....                                          SQL> select group#,member, from v$logfile order by group#;
• To check the currently active group number                                                                          SQL> select group#,member,status from v$log order by group#;
• LGWR writes in currently active group and after group 1 is filled LGWR switch to group 2nd, meanwhile if you have archive log enabled then ARCn process writes the group 1 data in archive log. LGWR will not overwrite the filled group till ARCn process completed its job. 
• Online Redo log files have different statuses available:                                                    UNUSED  - New and never been written.                                                                    CURRENT - The currently active redo log.                                                                    ACTIVE  - Log is active but is not the current log. It is needed for crash recovery.        CLEARING- Status during ALTER DATABASE CLEAR LOGFILE. Log is cleared,status changes to UNUSED. INACTIVE -Log is no longer needed for instance recovery.                      CLEARING_CURRENT - Current log is being cleared of a closed thread. The log file may be in this status if there is an I/O error writing the new log information.

Data files:

• Physical storage area stores the actual data of database and Tabelspace is a logical storage area.
• DBWn background process writes the changes in data files and read data to write in Database Buffer Cache.
• Every Tabelspace is associated with at-least single data files.
• To see the tablespace associated with related data files...                                                      SQL> SELECT tablespace_name, file_name FROM dba_data_files ORDER BY tablespace_name;
• Every database must have 4 tablespace: SYSTEM(data dictionary tables and PL/SQL code), SYSAUX, TEMP, UNDO.

Instance Parameter file:

• Required to start the instance from shutdown to nomount.
• Contains database name and default parameters like control file, db_recovery_file_dest info.
• We have SPFILE(binary form) and PFILE(human readable). If SPFILE is not found then oracle looks for PFILE.
• We can make vice-versa from both.                                                                                       SQL> create pfile from spfile;     //Loc: $ORACLE_BASE/product/version-name/dbhome_1/dbs/                                                                                                              SQL> create spfile from pfile scope = both;                                                                        Scope= both,  Make change for current instance in memory and in spfile also for next reboot. Scope= memory, Make change for currently running instance only.                                    Scope= spfile, Make changes in SPFILE but will effect after next startup.

The Password file:

• Contains few username and Password outside the data dictionary.
• OS based authentication username and passwords store in this file.

Archive Redo Log files:

• When online redo log files one group is full then ARCn copies this data to an archive redo log file.
• Required for database recovery.

Alert Log and Trace files:

• A continuous stream of messages regarding some critical operations affecting the instance and the database.
• Really important such as startup and shutdown, changes to the physical structures of the database, parameters that control the instance are logged. 
• Trace files are generated by background processes when they detect error conditions, and sometimes to report specific events.