Database architecture sounds bit complex no it is not let me explain:
Qs 1: What is database; any database?
Ans: A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content.
A database management system is a suite of software applications that together make it possible for people or businesses to store, modify, and extract information from a database.
Qs2: What are the three major pieces of any database? (Oracle, DB2 or any other database)
Ans:
1. STORAGE, it is made of
a. Datafiles.
b. Control files.
c. Online redo logs files.
d. Archive redo logo files.
2. MEMORY, it is made of SGA.
3. PROCESSES, it is made of background processes
a. DBWR
b. LGWR
c. SMON
d. PMON
e. CKPT
f. ARCH
g. RECO
h. MMAN
Now begin with storage-datafiles.
Datafiles contains Data – mapped to tables, Index stored in datafiles, Rollback segments(called in Oracle version 6, 7, 8 & 8i and in Oracle 9i, 10g, 11g it is called undo segments) also datafiles have Temporary segments-whenever oracle is not able to do sorting in PGA it writes in temporary segments.
So Oracle is as easy as DIRT
Very important view for a DBA, it is DBA_DATA_FILES that has information about datafiles which are physical it also shows you how these datafiles mapped to tablespaces which are logical.
There are couple of dynamic performance views those are:
a. V$DBFILE
b. V$DATAFILE
Now move on to another type of files which contains structure of your database those are CONTROL FILES earlier we have two control files, Oracle 10g and 11g Oracle Corporation recommends that we should have 3 control files (each on separate disk drives and controllers)
All 3 control file look exactly alike.
Inside control files we have information like:
a. Database name.
b. When the database was created.
c. Entire path name of all your datafiles.
d. Checkpoint information for each of datafile.
V$CONTROLFILE - dynamic performance view, having structure of your database.
SPFILE: very important parameter in your database, close to some 290-300 parameters.
An SPFILE is a Server Parameter File.
The SPFILE file is read when an instance is started up.
Up to Oracle 8i, the initialization parameters were stored (only) in the init.ora file. If a parameter has been dynamically changed (with alter system), the changements was lost after the next startup of the database unless it was also changed in the init.ora. SPFILEs ease this as changements made with alter system can be stored and be used for the next startup of the database.
In order to permanently store such a changement, use the alter system ... scope spfile syntax.
Either, the database uses the pfile or the spfile to determine its initialization parameters. In order to find out if the database uses an spfile, use the following
show parameter spfile
If it returns null, no spfile is used, otherwise it returns the path and the file name of the spfile.
Very important parameter in SPFILE are-
a. control_files – it will list full path name of 3 control files discussed above.
b. db_cache_size it determines size of database buffer cache.
c. db_block_size each block is of 8k, it will be good if Oracle block size is multiple of O/S block size.
d. shared_pool_size that defines size of shared pool.
e. java_pool_size
Another important file is password file, there is a utility-orapwd to create and make changes in this file, it completes with second piece of storage of database.
ONLINE REDO LOGS
Online redo logs groups stores:
a. All DML (inserts, update, deletes – having two commands for each DML: means UNDO/REDO and vice versa)
b. DDL (create, alter, drop)
c. Commits.
Recording changes going in your database, Oracle Corporation recommends that you multiply your groups-members of course in different disk drives and controllers, to have duplicate group-members.
Oracle performs a checkpoint to do a log switch to write to next available group also you can force to do a log switch when your group get filled, when all group get filled, it overwrite contents of first group. If we are running database in archive redo log mode if a group got filled then its contents are written in ARCHIVE REDO LOG and log switch will never take place if it contents are not fully archived by default when we create a database it is in no archive log mode.
ONLINE REDO logs are used for recovery.
Second piece of database – MEMORY
SGA – (shared global area)
a. Database buffer cache (earlier we call it as database buffer pool), have 4 major type of blocks, LRU algorithm (Least Recently Used algorithm), oracle keeps popular block in memory for quicker access as it is faster to read from memory then going and reading from disk.
b. Shared pool purpose of shared pool is to reduce parsing.
i. Library cache
a. inside library cache we have actual text means SQL statement or PL/SQL statement.
b. Compile version of SQL statement or PL/SQL statement referred as META CODE.
c. Execution plan.
There are couple of dynamic performance views those look into shared pool of SGA:
a. V$SQLAREA
b. V$SQL
c.
ii. Dictionary cache contains names of objects touched recently; LRU algorithm applied to shared pool also.
iii. JAVA pool - java objects get stored here.
iv. Large pool (optional) used for RMAN utility – to backup/restore oracle database, need to configure large pool.
v. redo log buffer - The redo log buffer is a RAM area (defined by the initialization parameter log_buffer) that works to save changes to data, in case something fails and Oracle has to put it back into its original state (a “rollback”). When Oracle SQL updates a table (a process called Data Manipulation Language, or DML), redo images are created and stored in the redo log buffer. Since RAM is faster than disk, this makes the storage of redo very fast, LGWR writes redo information from the log buffer to the redo log files.
Third piece of database – PROCESSES (background process)
1. DBWR (DataBase WRiter) is an Oracle background process created when you start a database instance. The DBWR writes data from the SGA to the Oracle database files. When the SGA data buffer cache fills the DBWR process selects buffers using an LRU algorithm and writes them to disk.
We can have more than one databasewriter, it performs a deferred write.
2. LGWR (LoG WRiter) is an Oracle background process created when you start a database instance. The LGWR writes the redo log buffers to the on-line redo log files. If the on-line redo log files are mirrored, all the members of the group will be written out simultaneously, LGWR is also responsible for performing checkpoints.
3. SMON system monitor process performs recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.
With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.
4. PMON process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes, like deadlock detected oracle 60 error.
PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally).
PMON also registers information about the instance and dispatcher processes with the network listener.Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.
5. CKPT - When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWR always performs that work, till oracle 7 it was optional no more optional now.
A checkpoint performs the following three operations:
a. Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk, It's the DBWR that writes all modified databaseblocks back to the datafiles.
b. The latest SCN is written (updated) into the datafile header.
c. The latest SCN is also written to the controlfiles.
6. ARCH (Oracle's ARCHiver Process) is an Oracle background process created when you start an instance in ARCHIVE LOG MODE. The ARCH process will archive on-line redo log files to an archive destination, typically a directory on the database server.
7. RECO (Oracle RECOverer Process) is an Oracle background process created when you start an instance with DISTRIBUTED_TRANSACTIONS= in the initialization parameter file. The RECO process will try to resolve in-doubt transactions across Oracle distributed databases.
8. MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.
Oracle instance comprises of SGA and background processes, when you combine oracle database with instance it is called ORACLE SERVER, now we have oracle database with instance associated with it we call it as oracle server – this concept is very important to understand.
Can we have multiple instances associated with a database? and the answer is yes we call this RAC (Real application cluster) I think we have largest RAC in Japan with 59 nodes…
We have user process we never connect directly to database – we go through dedicated server process also we have PGA and inside PGA we do our sorting, stacks space
PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.
Not all RAM in Oracle is shared memory. When you start a user process, that process has a private RAM area, used for sorting SQL results and managing special joins called “hash” joins. This private RAM is known as the Program Global Area (PGA). Each individual PGA memory area is allocated each time a new user connects to the database.
SCN - System Change Number
The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database. Every time a user commits a transaction, Oracle records a new SCN. You can obtain SCNs in a number of ways, for example, from the alert log.
Oracle uses SCNs in control files, datafile headers, and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file, while the high SCN records the highest SCN in the log file.
The SCN number is incremented every 3 seconds. This number is very useful while recovering the database or instance. All the datafile headers will have the same scn number when the instance is shutdown normally. You can get the current scn number from dbms_flashback.get_system_change_number or if you are using 10g your can query v$database
That finishes our understanding of Oracle database architecture, also try to learn more on checkpoint CKPT mostly asked in Oracle interviews :)
it is simple, do let me know if you have any improvement point or suggestions.
Comments
Post a Comment