Oracle Architecture

  1. System Global Area
    • SGA is an area in memory that stores information shared by all database processes and by all users of the database
    • This information includes both organizational data and conrol information used by the Oracle Server
    • The SGA is allocated in memory and virtual memory
    • The size of the SGA can be established by a DBAby assigning a value to the parameter SGA_MAX_SIZE in the parameter file(optional parameter)
  2. memory structures
    SGA and PGA (Program Global Area)

    • Shared Pool
    • Library Cache
    • Data Dictionary Cache
    • Database Buffer Cache
    • Redo Log Buffer

    • optional memory structures:
    • Large Pool
    • Java Pool
    • Streams Pool
  3. Database Buffer Cache
    fairly large memory object that stores the actual data blocks that are retrieved from datafiles by system queries and other data manipulation language commands
  4. Keep buffer pool
    • "Brain" of the software,
    • pool retains blocks in memory that are likely to be reused throughout daily processing
  5. Recycle buffer pool
    This pool is used to store table data that is unlikely to be reused throughout daily processing - thus the data will be quickly recycled
  6. Large Pool
    • an optional memory structure that primarily relieves the memory burden placed on the Shared Pool. The large pool is used for the following tasks if allocated:
    • Allocating space for session memory requirements from the User Global Area where a Shared Server is in use
    • Transactions that interact with more than one database
    • Backup and restore operations byt he Recovery Manager(RMAN) process
    • Parallel excution message buffers for parallel server operations
  7. Java Pool
    • optional memory object, but required if the database has Oracle Java installed and in use for Oracle JVM
    • Used for memory allocation to parse Java commands
  8. streams pool
    • new to Oracle 10g
    • This pool stores data and control structures to support the Oracle Streams feature of Oracle Enterprise Edition
    • manages sharing of data and events in a distributed environment
  9. Redo Log Buffer
    stores images of all changes made to database blocks. Changes include: INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP
  10. Data Dictionary Cache
    • contains the information on the system and the pieces thereof that are part of the software's functioning
    • memory structure that chaches data dictionary information that has been recently used, such as user account information, datafile names, table descriptions, user privileges and other information
    • If too small will lead to more disk queries, which slows down performance
  11. Library Cache
    memory is allocated whenever an SQL statement is parsed or a program unit is called. this enables storage of the most recently used SQL and PL/SQL statements

    • Has two subcomponents:
    • Shared SQL
    • Shared PL/SQL Procedures and Packages
  12. Shared SQL
    This stores/shares the execution plan and parse tree for SQL statements. If a system user executes an identical statement, then the statement does not have to be parsed again in order to execute the statement
  13. PL/SQL Procedures & Packages
    • the procedures, processes, and rules for data manipulation are stored here
    • This stores/shares the most recently used PL/SQL statements such as functions, packages, and triggers
  14. Control Structures
    underpining of the software
  15. Fixed SGA
    the reserved space for processing, each system would need a slightly different amount
  16. LGWR
    log writer - writes the contents from the Redo Log buffer to the Redo Log File that is in use. the writes are sequential, and records database modifications in real time rather than after they are committed to.
  17. DBWn
    • Database writer - creates the data files
    • writes modified blocks from the database buffer cache to the datafiles. It improves system performance by caching writes of database blocks
    • It writes when: checkpoints occurs, dirty buffers reach threshold, there are no free buffers, timeout occurs, RAC ping request is made, Tablespacc is OFFLINE, Tablespace READ ONLY, Table DROP or TRUNCATE, Tablespace BEGIN BACKUP
  18. SMON
    • responsible for instance recoery by applying entries in the online redo log files to the ddatafiles
    • It rolls forward changes in redo logs
    • rolls back uncommitted transactions
    • coalesces free space
    • deallocates temporary segments
  19. Data files
    the physical location on the hard drive where data is stored
  20. Redo Log Files
    resides in hard drive, storage space for all data logged by LGWR
  21. ARCn
    • archiver process - takes log files and stores them safely
    • it is optional, but is almost always used for production systems. The process must be used to recover from loss of a physical disk drive for systems athat are 'busy' with lots of transactions being completed
    • it automatically archives online redo logs when ARCHIVELOG mode is set
    • preserves the record of all changes made to the database
  22. Archive Redo Log Files
    reside on hard drive, storage location of all redo log giles after they have been archived
  23. CKPT
    • Checkpoint process, maintains data accuracy by copying datafiles to the control files, unless it finds an issue in data consistency which it will notify you of
    • Responsible for signaling DBWn at checkpoints
    • updating datafile headers with checkpoint information
    • updating control files with checkpoint information
  24. Control Files
    completed database files, considered the resource material, holds checked copies of datafiles and archived redo log files
  25. Server Process
    Establishes the Connection to an Oracle Instance when a User Process requests connection - makes the connection for the user process
  26. User Process
    Starts when a database user requests to connect to an Oracle Server
  27. Background Processes
    • Maintains and enforces relationships between physical and memory structures
    • Mandatory background processes:
    • DBWn
    • LGWR
    • PMON
    • SMON
    • CKPT

    • Optional background processes:
    • ARCn
    • CJQ0
    • Dnnn
    • LCKn
    • LMDn
    • LMON
    • Pnnn
    • QMNn
    • REC0
    • Snnn
  28. CJQ0
    coordinator job que - coordinator of job queue processes for an instance. It monitors the JOB$ table and starts job queue processes(Jnnn) as needed to execute jobs
  29. Dnnn
    Dispatcher number, for example, D000 would be the first dispatcher process - Dispatchers are optional background processes, present only whenthe shared server coniguration is used
  30. RECO
    recoverer - the recoverer process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions
  31. Oracle Server
    • Oracle Instance (or multiple instances)
    • Oracle Database
    • Server needs to manage concurrent access to the same data
  32. Oracle Instance
    • background processes
    • memory structures- SGA allocated
    • Provides access to one and only one Oracle database
  33. Oracle Database
    • collection of files
    • database files that store the database information that an organization needds to operate (.dbf)
    • the redo log files (.log)
    • archived redo log files
    • parameter file, password file, alert and trace log files are also contained in the database
  34. parameter file
    file that is used to specify parameters for configuring an Oracle instance when it starts up
  35. password file
    optional file to authenticate special users of the database - privildged users, which includes database aministrators
  36. Alert and Trace Log files
    store information about errors and actions taken that affect the configuration of the database
  37. Dedicated Server
    one to one correspondence betwen the user and server processes
  38. Shared Server
    more than one user process shares a server process
  39. Physical Structure
    • contains three types of files
    • Control files
    • Datafiles
    • Redo log files
  40. types of parameter file
    init.ora file (PFILE) - a static parameter file. It contains parameters that specify how the database instance is to start up

    spfile.ora is a dynamic prameter file. It also stores parameters to speify how to startup a database; but can be modified while the database is running
  41. PGA
    • Program Global Area - this is allocated when a Server Process starts up
    • Memory is reservedd for each user process connecting to an Oracle database
    • Alocated when a process is created
    • deallocated when the process if terminated
    • used by only one process
    • Allocated outside outside of the Oracle Instance. It stores data and control information for a single server process or a single background process - it is not a shared part of memory - only one PGA to each process
  42. Shared Pool
    memory structure that is shared by all system users. It consists of both fixed andd variable structures, stores the most recently excuted SQL statements and used data definitions.
  43. LRU
    least recently used algorithm - the process by which data is aged out of memory. Every time a unique query is created, all parsed requests are moved down the list by one. Once the list is full, each unique query is added to the top of the list, and the least recently used query is lost.
  44. write list
    part of the database buffer cache that holds dirty buffers
  45. dirty buffers
    buffers that hold data that has been modified, but the blocks have not been written back to disk
  46. LRU list
    part of the database buffer cache that holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list
  47. free buffers
    buffers that do not contain any useful data and are available for use within the database buffer cache
  48. pinned buffer
    buffers in the database buffer cache that are currently being accessed and are therefore unabled to be used at the time
  49. MRU
    most recently used- the top of the list on the LRU list
  50. Private SQL area
    data for binding variables and runtime memory allocations
  51. Dedicated Server Environment
    the Private SQL Area is located in the Program Global Area
  52. Shared Server environment
    The Private SQL Area is located in the System Global Area
  53. Session Memory
    meomory that holds session variables and other session informtion
  54. SQL Work Area
    Memory allocated for sort, hash-join, bitmap merge, and bitmap create types of operations
  55. Software Code Area
    store Oracle executable files running as part of the Oracle instance
Card Set
Oracle Architecture
vocabulary words describing the parts of an oracle RDBMS