oracle数据库培训 系统体系结构

1. oracle数据库培训系统体系结构

2. Oracle 体系架构篇 1

3. 目标 • 基本组件预览 • 内存结构和核心后台进程 • 用户连接数据库方式 • 监听 • 数据库服务器组成 • 实例的组成

4. 数据库的物理结构 • 数据库的逻辑结构 • 表空间 • 段 • 区 • 数据块 • 角色 • 权限

5. Oracle Database 9i:“i” 表示Internet • Oracle从9I开始强调互联网的应用

6. Overview of Primary Components(9i) Instance Userprocess SGA Shared pool Databasebuffer cache Redo logbuffer cache Library cache Serverprocess Largepool Javapool 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

7. 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

8. 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) • There are three optional memory structures that can be configured within the SGA: • Large pool • Java pool • stream pool

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

10. Oracle Server 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

11. Oracle Instance An 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

12. Oracle Database An Oracle database: • Is a collection of data that is treated as a unit • Consists of three file types Oracle Database Data files Control files Redo log files Archived log files Parameter file Password file

13. Oracle server Serverprocess Connection established Session created Userprocess Database user Establishing a Connection and Creating a Session Connecting to an Oracle instance consists of establishing a user connection and creating a session.

14. User process Serverprocess A user process is a program that requests interaction with the Oracle server. It must first establish a connection. It does not interact directly with the Oracle server. Userprocess Connection established Database user

15. Oracle server Serverprocess Connection established Session created Userprocess Database user Server process A server process is a program that directly interacts with the Oracle server. It fulfills calls generated and returns results. Can be dedicated or shared server.

16. Client Spawned server process Server 5 4 3 2 1 listener Bequeath Session

17. Background process 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 RECO Optional background processes ARCn LMON Snnn QMNn LMDn CJQ0 Pnnn LCKn Dnnn

18. Instance DBWn writes when: Checkpoint Dirty buffers threshold reached No free buffers Timeout RAC ping request Tablespace offline Tablespace read only Table DROP or TRUNCATE Tablespace BEGIN BACKUP Data files Control files Redo log files SGA Database buffercache DBWn Database Writer (DBWn) Database

19. LGWR writes: At commit When one-third full When there is 1 MB of redo Every 3 seconds Before DBWn writes Log Writer (LGWR) Instance SGA Redo log buffer LGWR DBWn Data files Control files Redo log files Database

20. Responsibilities: Instance recovery: Rolls forward changes in the redo logs Opens the database for user access Rolls back uncommitted transactions Coalesces free space ever 3 sec Deallocates temporary segments System Monitor (SMON) Instance Instance SGA SGA SMON SMON Data files Control files Redo log files Database

21. Instance Cleans up after failed processes by: Rolling back the transaction Releasing locks Releasing other resources Restarts dead dispatchers SGA PMON Process Monitor (PMON) PGA area

22. Instance SGA Redo Log Buffer LGWR DWW0 Checkpoint (CKPT) Instance SGA DBWn LGWR CKPT Responsible for: Signalling DBWn at checkpoints Updating datafile headers with checkpoint information Updating control files with checkpoint information Data files Control files Redo log files

23. Archiver (ARCn) Optional background process Automatically archives online redo logs when ARCHIVELOG mode is set Preserves the record of all changes made to the database ARCn Data files Control files Redo log files Archived Redo log files

24. Segment Data file Segment Extent Blocks Logical database Structure Tablespace 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.

25. Space Management in Tablespaces • Locally managed tablespaces: • Free extents recorded in bitmap • Each bit corresponds to a block or group of blocks • Bit value indicates free or used • Dictionary-managed tablespaces: • Default method • Free extents recorded in data dictionary tables

26. Roles Users C A B Roles HR_MGR HR_CLERK Privileges SELECTON JOBS INSERT ON JOBS CREATE TABLE CREATE SESSION UPDATEON JOBS

27. Predefined Roles Role Name Description CONNECT, These roles are providedRESOURCE, DBA for backward compatibility EXP_FULL_DATABASE Privileges to export the database IMP_FULL_DATABASE Privileges to import the database DELETE_CATALOG_ROLEDELETE privileges on data dictionary tables EXECUTE_CATALOG_ROLEEXECUTE privilege on data dictionary packages SELECT_CATALOG_ROLE SELECT privilege on data dictionary tables

28. Managing Privileges Two types of Oracle user privileges: • System: Enables users to perform particular actions in the database • Object: Enables users to access and manipulate a specific object

29. System Privileges • There are over 100 distinct system privileges • The ANY keyword in the privileges signifies that users have the privilege in any schema • The GRANT command adds a privilege to a user or a group of users • The REVOKE command deletes the privileges

30. System Privileges: Examples Category Examples INDEXCREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX TABLE CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE SESSION CREATE SESSION ALTER SESSION RESTRICTED SESSION TABLESPACE CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE

31. Object Privileges Object priv. Table View Sequence Procedure ALTER Ö Ö Ö DELETE Ö Ö EXECUTE Ö INDEX Ö Ö INSERT Ö Ö REFERENCES Ö SELECT Ö Ö Ö UPDATE Ö Ö