Oracle Data dictionary cache database buffer cache redo log caches add any others
Oracle Memory Cache/Architecture: Oracle uses memory to store different pieces of information, like Program Code, Connected session, if it’s not currently active with any or different user, Information needed when program executive (Locking Information), and last Cached Data on peripheral memory.
A basic memory structure that associated with oracle database, System Global Area SGA (Shared with all servers & his background processes, Program Global Area PGA Private for each process and his background processes.
Memory Structure/Shared SGA: Shared System Global Area is a group of shared memory structure containing data and constrain information for each (every DB) database system, this space of memory and information are shared between different & multiple user.
Shared Pool: Shared Pool Cache is a piece of memory in the SGA that Oracle uses in order to store SQL (Structure Query Language) area statements and Data Dictionary (DD), DD Cache is a collection of database table & views containing reference information about the database, they also help database administrators to track the location of various data also in the similar way in PL/SQL statements and objects, the total size of the shared pool is defined by the initialization parameter shared_pool_size, default value parameter 8MB on 32bit platforms and 64MB on 64-bit platforms.
Oracle DB Buffer Cache: They store all the copies of DB locks containing rows from various tables in a database, they also read all the data file, if any change occurs then they are written data file, if all users connected to the database server access the database buffer cache, if any Oracle user access these data they are not in cache they called ‘Cache Miss’, on the other hand they called ‘Hit cache’.
Oracle DB Redo Log Buffer: Redo Log Buffer is a circular buffer that’s holding the information about any change occur made to the database, they also recovered and re-construct the database file, like (Insert, Delete, Update, Create, Alter and last Drop). Redo Log is copied by Oracle DB processes from user’s memory space to the Redo Log Buffer in SGA. The Redo entries continuous take up logs in sequential style.
Data Dictionary Cache: Data Dictionary Cache is a collection of database table & views containing reference information about the database, they also help database administrators to track the location of various data, DD take the data into two format one is DD cache and other is row cache because it holds data as rows. All Oracle user processes share these two-cache formats for access data for DD information.
SQL leisure/crypt/dbwr/Pomona/Simon what are all these and ad other’s if any,
Processes: A processes is thread of control in an operating system that can run a series of steps, a process normally has its own private memory area in which it runs. They are basically two types of processes, one is user process run the application (tool code), and other is server processes or background processes.
User processes are a combination of two main types, one is called Connections and other are Sessions processes.
Connection Processes: Connection processes are communication pathway between a user process and an Oracle instance like (Network, means different computer runs the DB application and Oracle, and communication through a network).
Session Processes: Session processes are a specific connection of a user to an Oracle instance through a user process session is basically the time period/frame between the user connection and disconnection the certain DB application. For example user connects to any DB they must have a valid user name or password for entering in DB application, and then a session is established for that specific user, the session is basically the time frame between connection & disconnects/exits the database application.
Oracle Instances: Consists of one or more control file, data files & redo file those all file names and record are saved in his control file.
Database Instances: Maximize performances and accommodate many users, a multi-process database system uses some processes called Background Processes. An instance is a combination of the background processes and memory area required to access to Oracle DB. The database application system required only one instance for one database; any database is mounted by his specific instances. Instances have four basic types that are given below.
PMON (Process Monitor), SMON (System Monitor), DBWR (Database Writer), LGWR (Log Writer).
PMON (Process Monitor): Cleans-up when user processes failed, PMON is responsible for cleaning the DB buffer cache & freeing database resources that the time user process was using, like to delete/remove the process ID from the list of processes. PNOM is also time-to-time checks the status of dispatcher and server processes on the network.
SMON (System Monitor): Cleans-up after an Oracle instance, they also perform recovery process, if necessary, SMON is also responsible for cleaning the temporary segments that are no longer in use within DD managed table-spaces. SMON read offline error and checks regularly to see whether it is needed. Other processes can call SMON if they need help.
DBWR (Database Writer): Write modified block from the DB Buffer cache to the data – file, the DBWR processes are responsible for writing modified (dirty) buffers in the DB buffer cache to the disk. When a buffer in the DB buffer cache is modified, it is marked dirty. A cold buffer is a buffer that has been recently used according to the least recently used algorithm.
LGWR (Log Writer): Write records of transactions from Redo-log buffer to Redo-log buffer file, they also record all the change in the database, LGWR writes and copied all redo entries in the buffer. Redo Log buffer is a circular buffer, LGWR normally very fast to write & copied data to ensure that buffer space is available for new entries. LGWR continues checks & update logs error into the LGWR trace file system. If any file or group file is damaged, or they are available for his backup. If anyone used in database application issue a commit statement, LGWR very fast to take record in the red log buffer & writes into the disk, this function is called fast commit mechanism. Oracle returns a success code to the committing transaction; through the data buffers have not yet been written to disk.