Oracle Databasae Architecture
----------------------------------------
Introduction
------------------
An oracle databse is a collection of data treated as a unit,The purpose of database is to store and retrieve related information, a database server is the key to solving the problems of information management,in general , aserver reliably manages large amount of data in a multiuser environmemnt so that many users can concurrently access the same data, all this is accomplished while delivering high performance.A database server also prevents unauthorized acess and provides efficient solution for failure recovery
Oracle database is the first database designed for enterprise grid computing, the most flexible and cost efective way to manage information and applications. Enterprise grid computing create large pools of industry standard ,modular storage and servers,with this architecture,each new system can be rapidly provisoned from the pool of components there is no need for peak workloads,because capacity can be added or reallocated from the resources pools as needed Defines the components of database software database is the to store data in terms of files database can be used in these sectors
Banking
Insurance
Oil
Retails
Real estate
Airlines
Payment gateways
Govt Sectors
private setors
The Database has a logical structures and physical structures.Because the physical and logical structures are separate,the physical storage of data can be managed without affecting the acess to logical storage structures,Oracle is an RDBMS (Relational database management systems),The oracle RDBMS stores data logically in the form of tablespaces and physically in the form of data files,the oracle database architecture can be described in terms of logical and physical structures,The advantage of separating the logical and physical structure is that the physical storage structure can be changed without affecting the logical structures.
Physical structure: The physical layer of the database consists of three types of files
One or more datafiles
two or more redolog files
one or more controlfiles
DataFiles(.dbf files): Data files stores the information contained in the database.one can have as few as one datafiles or as many as hundreds of data files.The information for a single table can span many datafiles or many tables can share a set of datafiles,the number of data files that can be configured is limited by the oracle parameter db_files, to stores permanent data
Redo Log Files(.log): Oracle maintains logs of all the transaction against the database,These transactions are recoreded in files called online redo log files(Redo logs) The main purpose of the redo log files is to hold information as recovery in the event of system failure,redo log stores a log of all changes made to the database the redolog files must perform well and be protected against hardware failures (through software or hardware fault tolerance).if redolog information is lost,one cannot recover the system when a transcation occurs in the database,it is entered in the redo log buffers,while the data blocks affected by the transactions are not immediately written to disk,in an oracle database there are at atleast three or more redolog files,oracle writes to redolog files in a cyclical order i.e after the first log file is filled ,it writes to the second log file,untill that one is filled .when all the redo log files have been filled,it returns to the first log file and begin overwrite its content with new transaction data.note if the database is running in ARCHIVELOG mode,the database will make a copy of the online redolog files before overwriting them
Control Files (.ctl): control files record control information about all of the files with in the database,control files contain information used to start an instance, such as the location of the datafiles and redologfiles ,oracle needs this information to start the database instance.control files must be protected,oracle provides a mechanism for storing multiple copies of control files,These multiple copies are stored on as separate disks to minimize the potential damage due to disk failure,the names of the database conntrol files are specified via the CONTROL_FILES initilization parameter. The control file is small binary file the contents of the control files are database name,time stamp creation of database,location of datafiles and redo log files,
checkpoint information of every record (dml)with scn number (system change number)
The parameter file defines the characterstics of an oracle instance.for example ,it contains parameters that size some of the memory structures in the SGA
The password file authenticates user privileged to start up and shutdown an oracle instance
The archived redo log files are offline copies of the redolog files that may be necessary to recover from media failures
sql> select name,value from v$ parameter;
sql> select name,bytes,1024/1024,autoextensible from dba_data_files;
Instance(service )startup ,shutdown
---------------------------------------
A database instance(also save as server) is set of memory structures and background process that access a set of database files,constitutes of memory structure and background process every database has one or more instancess,database running in one server has one instance,if multiple servers (RAC) will have mltiple instances
Instance = memory+Bg process
eg: host1 prod(dbname-sid)prod(instance)
sid: system identifier is the instance name
Memory Structure is divided into SGA and PGA
SGA: The SGA is also called the shared global area it is used to store database information that is shared by database process,it contains data and control information for the oracle server and is allocated in the virtual memory of the computer where oracle resides, oracle uses an area of shared memory called the shared global area(SGA) The SGA is a shared memory region that contains data and control information for one oracle instance oracle allocates the SGA when an instance starts and de-allocates it when the instance shutdowns,every instance has the sga ,the entire SGA should be large as possible to increase the system performance and to reduce disk i/o.
From 11g ,oracle introduced Automatic memory management (AMM) using two parameter control instance memory with
SGA is set with parameter SGA_MAX_SIZE
sql> show parameter sga_max_size (from 11g is et to zero -amm is enabled)
Memory_max_target
memory_target
RAM(64g)> memeory_max_target(60g)>=memory_target(35g-dynamic)>=sga+pga
with in the size of memory_target,oracle server distributes across sga and pga
If memory_target is set to non zero value with in the size of memory_max_target
If memory _target>0 (AMM is enabled -->without restart dynamic)
memory_max_target>0 (static--need restart)
memory_target=0(AMM is disabled), then dba ha s manually allocate memory for sga and pga
The information is stored in the SGA is divided into three memory structures
1)Database buffer cache
2)Redo Log buffer
3)Shared Pool
1)Database buffers cache: The database buffer stores the most recently used blocks of data.The set of database buffers in an instance is the database buffer cache,the buffer cache contains modified as well as unmodified blocks,because the most recently and most frequently used data is kept in memory.it improves the performance of system by reducing the i/o operations
2)Redo Log Buffer: The redo log buffer stores redo entries .these are log of changes made to the database in database buffer cache.The redo entries stored in the redo log buffers are written to an online redo log,an online redo log is a set of two or more files that record all the changes made to oracle data files and control files.
Data buffer cache: holds modified as well as unmodified blocsks most recently used (MRU) willbe kept and Least recently used (LRU) willbe written into files
oracle uses MRU and LRU algorithm
sql> show parameter db_cache_size
0 - amm is enabled
Log Buffer cache: Holds log of changes made against to database for recovery purpose in the event of system failure every 1/3 or 3 secs fills will write in online redolog files
sql> show parameter log_buffer
1m /6m max value -recommended
3)Shared Pool : The shared pool is used to store the most recently execeuted sql and plsql statements and the most recently used data definition
it consits of two key performance related memory structures
Library cache: Library cache stores information about the most recently used sql and pl/sql statements,enables the sharing of commonly used statements,it is
managed by the least recently used (LRU) algorithm
Data dictionary cache: The data dictionary cache is a collection of the most recently used definitions in the database,holds dictionary inforamtion of tables,indexes,users,permissions
sql>show parameter shared_pool_size
0 (If AMM is enabled) also we can give fixed value with in the size of memory_target
Program Global Area:
----------------------------
PGA is a memory buffer that contains data and control inforamtion for a server process,A server process is a process that services a client reqeuests.A PGA is created by oracle when a server process is started.theinformation in a PGA depends on the oracle configuration,The PGA area is non shared area of memory created by oracle when a server process is started ,the basic difference between SGA and PGA is PGA is cannot shared between multiple processes in the sense that is used only for requirements of a particular process whereas the SGA is used for the whole instance and it is shared.
Back ground process
---------------------------
Few background process writes or read into file and memory, the trace files are only created whenthere is any problem,some of the background process are
PMON: PMON stand Process monitor and this is database backround process cleans up failed user process also relases dead locks, cleaning up the cache and freeing resorces that the process was using (its effect can be seen when a proces holding a lock is killed
$ ps -ef |grep pmon
SMON: SMON stands for system monitor and this is database background process performs instance receovery at the start of the database,SMON also cleans up temporary segments that are no longer in use and recovers dead transcations skippedduring crash and instance recovcery because of file read or offline errors .it coalesces combines contiguos free extents into large free extents
$ ps - ef |grep smon
system crash--->redologfiles---->smon--.startup---->recovery changes--->datafiles
LGWR:LGWR stands for Log writer background process manages the writing of the contents of the redo log buffer to the online redolog files,LGWR writes the log entries in the batches form,the redo log buffers entries always contain the most upto date status of the database note LGWR is the only one process that writes to the online redo log files and the only one that directly reads the redo log buffer during to the database normal database operation, writes from redolog buffer to online redo log files every 1/3 rd or 3 sec fills
$ ps -ef | grep lgwr
DBWR:DBWR stand for database writer background process is responsible for managing the contents of the datablock buffer cache and dictionary cache,DBWR performs batch writes of changed block,DBWR doest not need to write blocks blocks when a transcation commits,can have upto 20 process with parameter db_writer_process,DBWriter writes from buffer cache to data files in batch form not on every commit
$ ps - ef | grep dbwr
CKPT: CKPT stands for check point transaction all modified information in database buffer in the sga is written to the datfiles by a database write process (DBWR) This event indicates checkpoint,the checkpoint processis responsible for signalling DBWR at checkpoints and updating all the datafiles and controlfiles of the database, onec the DBWR writes in to the datafiles then signal to CKPT a checkpint process occurs,then the ckpt process update in a datafile header and control file with the
SCN number (system change nmber for every record)
$ ps - ef | grep ckpt
number of instances running on a server
using ps - ef |grep smon
ps -ef |grep pmon
ARCHIVER(ARCH): The archiver process reads the redo log files once oracle has filled them and writes a copy of the used redo log files to the specified archive log destinations actually ,for most databases,the ARCH has no effect on the overall system performance
MMAN: MMAN dynamically adjust the sizes of the SGA components ,it is a new process added to oracle 10 g as part of automatic shared memory management
FMON: The database communicates with the mapping libararies provided by the storage vendors through an external non oracle database process that is spawned by a background process called FMON,FMON is responsible for managing and mapping information
LMON:LMON stands for Lock manager process lock monitor is an oracle background process created when you start a database instance in RAC mode the LMON process manages global locks and resources,it is also called as global enqueue service monitor,it monitor all instances in the cluster primarily for dictionary cache locks and dead locks on dead locks sensitive equeues and resources,LMON also provides cluster group services
MMON: the oracle 10 g background process to collect statastics for the automatic workload repository (AWR)
MMNL:This process performs frequent and lightweight manageability relatedtasks such as session history capture and metrics computation
WMON: The Wake up monitorprocess
RVWR: Recovery Writer oracle starts the recovery writer (RVWR)process to write the flashback data from the flashback buffer to the flash back logs
CTWR:(change tracking writer) oracle tracks the physcical location of database changes in a new file called the change tracking file.oracle backup utility the recovery manager RMAN uses the change tracking file to determine which data blocks to read during an incremental backup,making the incremental backups faster by avoiding reading entire data files.
DMON:Dataguard
DIAG: the diagnosibility process DIAG runs oracle bug commands and triggers diagnostic dumps as part of the new ADR(automatic diagnostic repository )feature which is replacement and major enhancement for the much reviled RDA
SMCO: The space management cordinator and slaves perform space allocation and reclamation
THANKS FOR VIEWING MYBLOG FOR MORE UPDATES FOLLOW ME
