ARCHITECTURE OF ORACLE DATABASE STEP 1: When a user clicks on a software or terminal a user process is allocated. STEP 2 : When user enters a user id and password it requests for a server process and a server process is allocated for that user. STEP 3: Now the server process will go in the data dictionary cache/Row Cache and will check for the authentication, if any entry present it will authenticate the user, if not then it will check in the system data file of physical storage and will give the authentication, if user id and password exist . STEP 4: Now when a user fires select statement query it goes through the parsing stages. The parsing stages are: ➢ Syntax check ➢ Symantec check ➢ Preparation of execution plan STEP 5: In syntax check it will check for the position of keywords, semicolon, structure of query. These checks will be performed by the use process. STEP 6: Now the query will go for Symantec check where two things will be checked viz. Accessibility and availability. In availability server process will check whether the table is present or not and in accessibility server process will check whether the user in is having sufficient privileges on that table or not STEP 7: Now this query will hit the sql area of Library cache which is a part of shared pool. In sql area a unique sql_id will be given to the query and after that the query will be copied in the sql_text of sql area. STEP 8: The sql text which is copied is given a #value which is a unique number STEP 9: Case1:- If query is fired for the first time then the execution plan will be made, the execution plan will be made based on three parameters i.e. CPU consumption, memory and I/O. Number of execution plans will be made and out of that the best plan will be chosen and an unique #value will be given. A parse code will be generated for the plan chosen and this code will be an unique code, which will be stored in the p.code of library cache. Case 2:- If query is fired for the second time the, the server process will firstly check the data dictionary cache whether the p.code is present or not, if present then it will use that plan for execution, if not present then it will check in system data file and will use it for execution STEP 10: Based on the execution plan the server process will bring the data from the respective data files block by block in the data base buffer cache which is the part of SGA. This is nothing but fetching of data. STEP 11: Now server process will display the data in the database buffer cache on the terminal and will also keep a record of the same in result cache area of library cache if it is enabled STEP 1: When a user clicks on a software or terminal a user process is allocated STEP 2: When user enters a user id and password it requests for a server process and a server process is allocated for that user. STEP 3: Now the server process will go in the data dictionary cache/Row Cache and will check for the authentication, if any entry present it will authenticate the user, if not then it will check in the system data file of physical storage and will give the authentication, if user id and password exist STEP 4: Now when a user fires select statement query it goes through the parsing stages. The parsing stages are: ➢ Syntax check ➢ Symantec check ➢ Preparation of execution plan STEP 5: In syntax check it will check for the position of keywords, semicolon, structure of query. These checks will be performed by the use process. STEP 6: Now the query will go for Symantec check where two things will be checked viz. Accessibility and availability. In availability server process will check whether the table is present or not and in accessibility server process will check whether the user in is having sufficient privileges on that table or not. STEP 7: Now this query will hit the sql area of Library cache which is a part of shared pool. In sql area a unique sql_id will be given to the query and after that the query will be copied in the sql_text of sql area. STEP 8: The sql text which is copied is given a #value which is a unique number STEP 9: Case1:- If query is fired for the first time then the execution plan will be made, the execution plan will be made based on three parameters i.e. CPU consumption, memory and I/O. Number of execution plans will be made and out of that the best plan will be chosen and an unique #value will be given. A parse code will be generated for the plan chosen and this code will be an unique code, which will be stored in the p.code of library cache. Case 2:- If query is fired for the second time the, the server process will firstly check the data dictionary cache whether the p.code is present or not, if present then it will use that plan for execution, if not present then it will check in system data file and will use it for execution STEP 10:- In insert statement after the preparation of execution plan, the data comes in the database buffer cache. The old image of the dirty buffer is stored in the IN MEMORY UNDO and a copy of the dirty buffer is sent to the Redo Buffers(Redo Vectors). When the data comes in Redo vectors the header of the block is updated with UBA(Undo buffer address) ,RBA(Redo Buffer address), DBA (Data block address), SCN NO(System Change Number) any many as such unique addreses and also a information is updated whether the data is commited or uncommited . STEP 11:- Redo vectors then write the data in online redo log groups using LGWR background process if any of the following five conditions is fulfiled. The conditions are: • Every 3 sec • Every 1MB full • Every 1/3 full • Every commit • Every graceful shutdown STEP 12:- When the memeber of a online redo log group gets full at that time a checkpoint ocurrs will gives a signal to the CKPTr process . And at the same time the archives are generated by the ARCn background process. STEP 13:- The CKPTr process will update the headers with SCN no. in data file and control file. Also the CKPTr process will give a signal to the DBWRn and LGWR process. DBWRn will write the data of dirty buffer in the respective data files and LGWR will write the data from redo vectors to the onlie redo log groups Oracle Server:- Oracle server is a database management system that manages a large amount of data in multiuser environment so that many users can concurrently access the same data at the same time. Components of Oracle Server:- • It consists of two components 1. Oracle Instance 2. Oracle Database • It is a means to access an Oracle database. • Always opens one and only one database. • An instance is created during the NOMOUNT stage of the database startup after the parameter file has been read. • It consist of two components: 1. System Global Area (SGA) 2. Background Processes • When instance is started, SGA is allocated and Background processes are started SHARED POOL:- • Library cache – SQL Area – PL/SQL Area • Data dictionary cache • Result cache SHARED POOL:- • Library cache – SQL Area • SQL Text • SQL Id • # value • E.P • Plan # value • P_code SHARED POOL:- • Data dictionary cache – Data dictionary view of System tablespace – Availability – Accessibility • Result cache – Snapshot of result DBBC (Database Buffer Cache) • db_keep_cache_size • db_recycle_cache_size • db_nk_cache_size n=2, 4, 8, 16, 32 – Free Buffer – Pinned Buffer – Dirty Buffer – Clean Buffer • In Memory Undo • In Memory Flash Area RLBC (Redo Log Buffer Cache) • Redo Vector – DML or DDL Statement – Timestamp of transaction – Row Id – New Image – SCN – Committed or Uncommited Large Pool • Managed load of shared pool • Backup devices info is kept Java Pool • To run Java interface tools Stream Pool • Share data between databases Background Processes • PMON • SMON • DBWR • LGWR • CKPT • ARCn PMON (Process Monitor): • The process monitor database process. •The Process Monitor (PMON) performs process recovery when a user process fails • PMON is responsible for cleaning up the cache and freeing resources that the 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. SMON(System Monitor) • SMON is an Oracle background process created when you start a database instance. • The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents. • SMON wakes up every 5 minutes to perform housekeeping activities. • SMON must always be running for an instance. If not, the instance will terminate DBWRn (Database Writer Process): • DBWR (DataBase WRiter) is an Oracle background process created when you start a database instance. • The DBWR writes modified data (dirty buffers) from the SGA into 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. • There can be multiple database writer processes named DBWn. LGWR (Log Writer Process) • 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 CKPTr (Checkpoint Process): • CKPT (Oracle Checkpoint Process) is an Oracle background process that timestamps all datafiles and control files to indicate that a checkpoint has occurred. • The “DBWR checkpoints” statistic (v$sysstat) indicates the number of checkpoint requests completed ARCn (Archive process): • ARCn is an oracle background process responsible for copying the entirely filled online redo log file to the archive log • Once these files have been copied, they can be overwritten • The n in ARCn represents the sequence number of the archiver process. • A single oracle instance can have 10 (ARC0 to ARC0) archiver processes associated with it. •ARCn process is however, only activated when the database is running in ARCHIVELOG mode and the LOG_ARCHIVE_START initialization parameter is set to true