Thursday, March 22, 2007

Oracle9i DBA -- Database: Fundamentals I (1)

Oracle Architectural Components

  • OSDBA groups allows its members to connect to 9i using the SYSDBA privileg0065
  • Tablespace -> Segment -> Extent -> DB blocks
  • The value of ORACLE_HOME are represented using ‘?’ symbol.
  • The SGA_MAX_SIZE is used to specify the max size of the SGA.
  • The share pool consists of the most recently executed SQL statements and the most recently used data definitions. The Database Buffer Cache contains copies of the database files, the redo log buffer records all changes made to the database blocks and the Program Global Area contains the data and control information for single server process or single background process.
  • The data dictionary cache is also referred to as the dictionary cache or row cache.
  • The database buffer cache stores copies of data blocks that have been retrieved from the data files. Consists of independent sub-caches:

    1. DB_CACHE_SIZE : Sizes the default buffer cache size only, it always exists and cannot be set to zero. Size of the buffer cache in Oracle SGA.

    2. DB_KEEP_CACHE_SIZE : Sizes the keep buffer cache, which is used to retain blocks in memory that are likely to be reused.

    3. DB_RECYCLE_CACHE_SIZE : Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little change of being reused.

  • DB_BLOCK_BUFFERS : Number of blocks cached in the SGA
  • The size of each buffer in the buffer cache is equal to the size of an Oracle block, and it is specified by the DB_BLOCK_SIZE parameter. The database buffer cache consists of independent sub-caches for buffer pools and for multiple block sizes. The parameter DB_BLOCK_SIZE determines the primary block size, which is used for the SYSTEM tablespace.
  • Diagnostic files contain information about significant events encountered while the instance is operational.

    1. alertSID.log file

    2. Background trace files : Vital information when background processes, such as SMON, PMON, DBWn, and others fail.

    3. User trace files : Vital information for fatal user errors or user forced traced files.

  • The minimum SGA configuration is three granules (one granule for fixed SGA (includes redo buffers; one granule for buffer cache; one granule for shared pool).

Read more!

No comments: