1 / 59

Database Architecture Overview

Database Architecture Overview. Oracle 11g Server Architecture. Oracle Instance Oracle Database Database Files Server Processes Memory Structures Transactions Data Dictionary Schema And Users. Oracle Server Architecture. An Oracle server:

oksana
Download Presentation

Database Architecture Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Architecture Overview

  2. Oracle 11g Server Architecture • Oracle Instance • Oracle Database • Database Files • Server Processes • Memory Structures • Transactions • Data Dictionary • Schema And Users

  3. Oracle Server Architecture An Oracle server: • Is a database management system that provides an open, comprehensive, integrated approach to information management • Consists of an Oracle instance and an Oracle database Oracle Server

  4. Overview of Primary Components Instance Userprocess SGA Shared pool Library cache Redo logbuffer cache Databasebuffer cache Serverprocess Data Dict.cache PGA PMON SMON DBWR LGWR CKPT Others Data files Control files Redo log files Parameter file Archived log files Password file Database

  5. Oracle 11g Server Architecture • Oracle Instance • Oracle Database • Database Files • Server Processes • Memory Structures • Data Dictionary • Schema And Users

  6. Oracle Instance • Is a means to access an Oracle database • Always opens one and only one database • Consists of memory and process structures Instance SGA Shared pool Memory structures Library cache Database buffer cache Redo logbuffer cache Data Dictionarycache Background structures PMON SMON DBWR LGWR CKPT Others

  7. Oracle 11g Server Architecture • Oracle Instance • Oracle Database • Database Files • Server Processes • Memory Structures • Data Dictionary • Schema And Users

  8. Header Data files (includes data dictionary) Control files Online redo log files) Physical Structure The physical structure of an Oracle database is determined by the operating system files that provide the actual physical storage for database information. • Control files • Data files • Redo log files

  9. Oracle 11g Server Architecture • Oracle Instance • Oracle Database • Database Files • Server Processes • Memory Structures • Data Dictionary • Schema And Users

  10. Database Files • Parameter Files • Control Files • Data Files • Redo Log Files • Undo Data Files

  11. Oracle Database Data files Control files Redo log files Archived log files Parameter file Password file Oracle Database

  12. Database Files • Parameter Files • Control Files • Data Files • Redo Log Files • Undo Data Files

  13. Parameter Files • Entries are specific to the instance being accessed • There are two kinds of parameters: • Explicit: Having an entry in the file • Implicit: No entry within the file, but assuming the Oracle default values • Multiple files can be used for a single database to optimize performance in different situations.

  14. SPFILE spfileSID.ora • Binary file with the ability to make changes persistent across shutdown and startup • Maintained by the Oracle server • Records parameter value changes made with the ALTER SYSTEM command • Can specify whether the change being made is temporary or persistent • Values can be deleted or reset to allow an instance to revert to the default value • Can be dumped to text file for manual editing

  15. Database Files • Parameter Files • Control Files • Data Files • Redo Log Files • Undo Data Files

  16. Control File The control file is a binary file that defines the current state of the physical database.. • Loss of the control file requires recovery • Is read at MOUNT stage • Is required to operate • Is linked to a single database • Should be multiplexed • Maintains integrity of database • Sized initially by CREATE DATABASE Database Control files

  17. Database Files • Parameter Files • Control Files • Data Files • Redo Log Files • Undo Data Files

  18. Database Files • Parameter Files • Control Files • Data Files • Tablespaces • Oracle Managed Files • Data Block • Data Types • Tables • Indexes • Redo Log Files • Undo Data Files

  19. Data file Segment Segment Extent Blocks Logical Structure The logical structure of the Oracle architecture dictates how the physical space of a database is to be used. A hierarchy exists in this structure that consists of tablespaces, segments, extents, and blocks. Tablespace

  20. SYSTEM and Non-SYSTEM Tablespaces • SYSTEM tablespace: • Created with the database • Contains the data dictionary • Contains the SYSTEM undo segment • Non-SYSTEM tablespaces: • Separate segments • Ease space administration • Control amount of space allocated to a user

  21. Database Files • Parameter Files • Control Files • Data Files • Tablespaces • Oracle Managed Files • Data Block • Data Types • Tables • Indexes • Redo Log Files • Undo Data Files

  22. Oracle Managed Files • Oracle Managed Files (OMF) simplify file administration by eliminating the need to directly manage the files in an Oracle database • This feature has two major thrusts: • Allows database objects to be created without specifying the underlying operating system files • Automatically removes obsolete data files and online redo logs

  23. Database Files • Parameter Files • Control Files • Data Files • Tablespaces • Oracle Managed Files • Data Block • Data Types • Tables • Indexes • Redo Log Files • Undo Data Files

  24. Database Block • Minimum unit of I/O • Consists of one or more OS blocks • Set at tablespace creation • DB_BLOCK_SIZE is the default block size

  25. Database Files • Parameter Files • Control Files • Data Files • Tablespaces • Oracle Managed Files • Data Block • Data Types • Tables • Indexes • Redo Log Files • Undo Data Files

  26. Oracle data types Data type User-defined Built-in Scalar Collection Relationship CHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N) NUMBER(P,S) DATERAW(N)BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID VARRAYTABLE REF

  27. System-Provided Datatypes Built-In Datatype Native Datatypes Extended Datatypes Support Datatypes CHARACTER Text ANSI NUMBER DB2 Image DATE SQL/DS Video LOB Audio PL/SQL Datatypes RAW ROWID BOOLEAN Spatial BINARY_INTEGER XML Time Series

  28. Database Files • Parameter Files • Control Files • Data Files • Tablespaces • Oracle Managed Files • Data Block • Data Types • Tables • Indexes • Redo Log Files • Undo Data Files

  29. Index-organizedtable Cluster Tables in Oracle Partitionedtable Regular table

  30. Database Files • Parameter Files • Control Files • Data Files • Tablespaces • Oracle Managed Files • Data Block • Data Types • Tables • Indexes • Redo Log Files • Undo Data Files

  31. Classification of Indexes • Logical • Single column or concatenated • Unique or nonunique • Function Based • Domain Based • Physical • Partitioned or nonpartitioned • B-tree • Normal or reverse key • Bitmap • Bitmap Join Index

  32. Database Files • Parameter Files • Control Files • Data Files • Redo Log Files • Undo Data Files

  33. Using Redo Log Files Redo log files record all changes made to data and provide a recovery mechanism from a system or media failure. • Redo log files are organized into groups. • An Oracle database requires at least two groups. • Each redo log within a group is called a member. Database Redo log files

  34. Structure of Redo Log Files Group 1 Group 2 Group 3 Disk 1 Member Member Member Disk 2 Member Member Member

  35. How Redo Logs Work • Redo logs are used in a cyclic fashion. • When a redo log file is full, LGWR will move to the next log group. • This is called a log switch • Checkpoint operation also occurs • Information is written to the control file

  36. Database Files • Parameter Files • Control Files • Data Files • Redo Log Files • Undo Data Files

  37. Undo Data Overview • Before Oracle makes a change to a database block it is copied to the Undo area. • Used for Rollback and Read Consistency.

  38. Oracle 11g Server Architecture • Oracle Instance • Oracle Database • Database Files • Server Processes • Memory Structures • Transactions • Data Dictionary • Schema And Users

  39. Process Structure An Oracle process is a program that depending on its type can request information, execute a series of steps, or perform a specific task. On some OS (MS Windows…) these are actually implemented as process threads. Oracle takes advantage of various types of processes: • User process: Started at the time a database user requests connection to the Oracle server • Server process: Connects to the Oracle Instance and is started when a user establishes a session. • Background process: Available when an Oracle instance is started

  40. Background Processes The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes. • Mandatory background processes DBWn PMON CKPT LGWR SMON and others • Optional background processes ARCn LMON Snnn QMNn LMDn RECO CJQ0 Pnnn and others LCKn Dnnn

  41. Oracle 11g Server Architecture • Oracle Instance • Oracle Database • Database Files • Server Processes • Memory Structures • Transactions • Data Dictionary • Schema And Users

  42. Memory Structure Oracle’s memory structure consists of two memory areas known as: • System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance • Program Global Area (PGA): Allocated when the server process is started

  43. System Global Area (SGA) • The SGA consists of several memory structures: • Shared pool • Database buffer cache • Redo log buffer • Other structures (e.g. lock and latch management, statistical data)

  44. System Global Area (SGA) • SGA is dynamic and sized using MEMORY_TARGET without shutting down the instance • SGA memory allocated and tracked in granules by SGA components • Contiguous virtual memory allocation • Size based on MEMORY_TARGET

  45. PGA Dedicated server Shared server Serverprocess Session information sort area, cursor information sort area, cursor information Stack space Stack space Userprocess SGA SGA Session information Shared SQL areas Shared SQL areas Program Global Area (PGA) The PGA is memory reserved for each user process that connects to an Oracle database. Managed as part of the MEMORY_TARGET

  46. Oracle 11g Server Architecture • Oracle Instance • Oracle Database • Database Files • Server Processes • Memory Structures • Transactions • Data Dictionary • Schema And Users

  47. Transactions • Oracle has an implicit transaction model. • A transaction starts immediately after the last one completes • Can be made READ-ONLY via ALTER TRANSACTION command • COMMIT or ROLLBACK ends transaction

  48. Oracle’s Multi-Version ConcurrencyRead Consistent Row Locking With Oracle … report • Updates don’t lockout reports and reports don’t lockout updates • Reports see only committed data via Multi-Versioning • Queries yield maximum throughput with correct results - no waiting and no dirty reads! • Row locks never escalate - the most scaleable solution available Budget Table update Undo Tablespace Before Image accurate report

  49. Multi Version Read Consistency High Throughput Concurrency

More Related