ORACLE DATABASE ARCHITECTURE

ORACLE DATABASE  ARCHITECTURE:

·        Oracle database is a combination of oracle instance and datafiles on the file system.

·     Oracle instance is the combination of SGA and background process.with the help of instance only    we can acess the database objects.without database instance have no meaning.

What is Database?

Database is the collection of oraganized data.every running oracle database is assosciated with oracle instance.database consists of 2 phases.

                        1)logical phase--->objects-->tables

                        2)physical phaseàfiles-->filesystem-->os--->devicedrivers(harddisk)

                                                                        1)controlfiles

                                                                        2)redolog files

                                                                        3)datafiles

                                                                        4)archive redo log files(optional)

·        The purpose of physical phase is for storing

·        The purpose of logical phase is to access the database.

SGA: [System global area or shared global area]

When instance startsup memory structure called SGA is allocated.The total memory allocated to the oracle database can be broadly categorized into SGA and PGA[program global area].

SGA contains following data structure:

 1)DBBC[database buffer cache]

2)shared pool.

3)RLBC[redolog buffer cache]

4)Java Pool

5)large pool

6)streams pool

SGA can be categorized into 2 types:

1)Fixed SGA:- is a component of the SGA that varies in size from platform to platform and release to release. The size of the fixed SGA is something over which we have no control and it is generally very small.we cannot alter the size of fixed SGA.

2)Variable SGA:- Variable SGA contains 4 main components as listed above, those are “Database Buffer Cache”, “Redo Log Buffer”, “Shared Pool” and “Large Pool”. We call it variable SGA because we can alter the size of each of these components manually using ALTER SYSTEM command. The size of each of the components of variable SGA is determined by INIT.ORA parameters. Following are the INIT.ORA parameter for each of the component.

Database Buffer Cache – db_block_buffers

Redo Log Buffer – log_buffer

Shared Pool – shared_pool_size

Large Pool – Large_pool_size

Database Buffer Cache – This is used to hold the data into the memory. When ever a user access the data, it gets fetched into database buffer cache and it will be managed according to LRU (Least recently used) algorithm. Advantages – If a user is requesting data, which gets fetched into the buffer cache, then next time if he ask for same data with in a short period of time, the data will be read from buffer cache and Oracle process does not have to fetch data again from disk. Reading data from buffer cache is a faster operation. Another advantage is that if a user is modifying the data, it can be modified in the buffer cache which is a faster operation then modifying the data directly on the disk.

Redo Log Buffer – This memory block hold the data which is going to be written to redo log file. Why do we need this data? To rollback the changes if the need be. But instead of writing the data directly to the redo log files, it is first written to log buffer which improves performance and then with the occurrence of certain event it will be written to redo log file.

Shared Pool – This contains 2 memory section, 1) Library Cache 2) Dictionary Cache. Library cache hold the parsed SQL statement and execution plans and parsed PLSQL codes. Dictionary cache hold the information about user privileges, tables and column definitions, passwords etc. These 2 memory components are included in the size of shared pool.

Large Pool – If defined then used for heavy operations such as bulk copy during backup or during restore operation.

The total size of SGA is determined by a parameter SGA_MAX_SIZE. Below is the simple calculation of memory sizes.

SQL> show sga

Total System Global Area  577574308 bytes

Fixed Size                   452004 bytes

Variable Size             402653184 bytes

Database Buffers          163840000 bytes

Redo Buffers               10629120 bytes

This will show fixed and variable size SGA. Fixed size SGA, as I said is not in our control. However we can verify the size of variable SGA and other memory values shown above.

Database Buffers          163840000 bytes

SQL> show parameters db_block_buffer

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_block_buffers                     integer     20000

This value is in terms of blocks. we can find the size of a block using DB_BLOCK_SIZE parameter

SQL> show parameters db_block_size

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_block_size                        integer     8192

So Database Buffers = db_block_buffers X db_block_size = 20000 X 8192 = 163840000 bytes

Also Variable size = “Shared Pool Size” + “Large Pool Size” + “Java Pool size” (some times defined)

SQL> SELECT pool, sum(bytes) from v$sgastat group by pool;

POOL        SUM(BYTES)

java pool     50331648

shared pool  352321536

PGA:-[program global area]   sga is allocated when oracle instance startsup

And PGA is allocated when server process startsup.

BACKGROUND PROCESSES:   will starts when oracle instance startsup.

1)SMON:[system monitor]

The system monitor process (SMON) 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.

2)PMON:[process monitor]

The process monitor (PMON) 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.

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.

3)LGWR[log writer]

The log writer process (LGWR) is responsible for redo log buffer management.writing the redo log buffer to a redo log file on disk. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote.

4)DBWN[database writer]

The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) 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) to improve write performance if your system modifies data heavily.

5)CKPT[check point]

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; DBWn always performs that work.

6)RECO[recoverer]

The RECO process is present only if the instance permits distributed transactions and if the DISTRIBUTED_TRANSACTIONS parameter is greater than zero. If this initialization parameter is zero, RECO is not created during instance startup.

7)ARCN[archiver process]

The archiver process (ARCn) copies online redo log files to a designated storage device after a log switch has occurred. ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.

An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The ALERT file keeps a record of when LGWR starts a new ARCn process.

1)startup and shutdown modes?

STARTUP MODES:

         

1)nomount--->instance creation-->oracle is going to read the p-file[parameter file].from the parameter file it will find how much is the memory allocation for SGA and it will allocate those memory structure and background process will be created.instartup nomount we can modify the controlfile.

2)mount----->instance will read the controlfile and locates the datafiles and redologfiles.in mount we can rename datafiles,enable or disable archive redolog,full database recovery.

 3)open------->it will open the datafiles and redologfiles.if the database is in mount state.we can open by alter database open;

SHUTDOWN MODES:

1)normal shutdown---->during normal shutdown before the oracle database is shutdown oracle will wait for all the active users to disconnect their sessions.As the parameter name suggests use this option to shutdown the database under normal conditions.

            

2)shutdown immediate---->during shutdown immediate before the oracle database is shutdown oracle will rollback active transaction and disconnect all active users.use this option  when there is a problem with your database and you dont have enough time to request users to log off.

           

3)shut abort---->during shut abort before the the oracle database is shutdown all user sessions will be terminated immediately.uncommited transactions will not be rolledback.use this                 option only during emergency situations when the shutdown and shut immmediate does not work.

           

2)what happens in background when we enter select statement?

user process cannot directly interact with the instance.so user process will send to server process.server process makes use of data dictionary cache.the data dictionary cache will check whether the user has the proper privileges are not.If the user has the proper privileges the pl/sql statements by the user will send to library cache.the parsing of sql and pl/sql statements are done in shared pool.parsing is of two types

            1)hard parsing--->if the statement is executing for the first time.

            2)soft parsing--->if the statement is not there it will fetch from dbf.

parsing consists of 3 steps

            1)syntax check-->it will check whether the statement by the user is correct or not it will check.

            2)schemantic check-->it will check whether the user has proper privilges to access the table or not.

            3)hash value generation-->hash value is created on the basis of adding all the ASCII values of the statement.it is stored in cursor.and then there is an execution plan that also made             for each sql statement which is also created by optimizer.optimizer generates n number of execution plan among them it will take the best execution plan which consumes less system             resources.

this execution plan will be stored in library cache and goes TO dbbc if it is not there it will fetch from userdata1,dbf and sends to userprocess.

TABLESPACES and DATAFILES:

Tablespaces:- An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database's data. You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.

Datafiles:- Each tablespace in an Oracle database consists of one or more files called datafiles.datafiles contains actual business data.The extensions for datafiles is .dbf. The size of datafile will be in MB’S or GB’S depends up on the company.

Controlfiles:-

It contains the crucial information of the database.i.e

1)database name.

2)timestamp of db creation.

3)names and locations of assosciated datafiles and redologfile.

4)datafile offline ranges.

5)the log history.

6)archive log information.

7)backup set and backup piece information.

8)backup datafile and redologfile information.

9)datafile copy information.

10)the current log scn.[system change number-A SCN is a logical,internal time stamp used by oracle db.every transaction has a scn.for example if a transaction updates a row then database records the SCN at which this update occurred.]

Redologfiles:-

it contains the latest transactions for recovery.

Restore--->getting yesterdays data.

Recovery--->getting todays data.

The extension for redologfile is .log.

The size of redologs is in mb’s.The redologfiles are used to recover the database in the event of application program failures,instance failures and other minor failures.