Orcl0(OracleArchitecturalComponents)

1、1实验安排实验安排地点：信息楼地点：信息楼603网络工程网络工程 第第2-5，7-16周，周， 周四周四1-2节节 第第11，14周，周， 周四周四3-4节节软件工程软件工程 第第2-5，7-15周，周， 周四周四7-8节节 第第10，13，16周，周四周，周四3-4节节兴湘兴湘 第第2-5，7-16周，周， 周三周三3-4节节 第第12，15周，周， 周四周四3-4节节Copyright Oracle Corporation, All rights reserved.Oracle Architecture and Management3OracleOracle的发展变迁的发展变迁1977年L

2、arry Ellison、Bob Miner和Ed Oates共同创建了软件开发实验室1979年，Oracle的第一个产品被发行。 1985年, Oracle公司推出了版本5，引入了Client/Server计算 1988年，Oracle 6版本，可以运行在多种平台和操作系统上1998年，Oracle 8i，世界上第一个全面支持Internet的数据库 2001年6月，在Oracle Open World大会中，Oracle发布了Oracle 9i2003年9月， Oracle 10g发布，g(grid)2007年11月，Oracle 11g正式发布 ， Oracle 11gr22013年6月

3、，Oracle 12c正式上市，c(cloud), 64位。4Oracle Architectural Components5Overview of Primary ComponentsInstanceSGARedo logbuffer cacheShared poolData Dict.cacheLibrarycacheDBWRSMONPMONCKPTLGWROthersUserprocessServerprocessPGAControl filesData files Archived log filesParameterfilePasswordfileRedo log filesData

4、baseDatabasebuffer cache6Oracle ServerAn Oracle server:Is a COracle Server7Oracle InstanceAn Oracle instance:Is a means to access an Oracle databaseAlways opens one and only one databaseConsists of memory and process structuresBackground structuresMemory structuresSGARedo logbuffer cacheDatabase buf

5、fer cacheShared poolDBWRSMONPMONCKPTLGWROthersData DictionarycacheLibrarycacheInstance8Establishing a Connection and Creating a SessionConnecting to an Oracle instance consists ofestablishing a user connection and creating asession.Connection establishedSession createdDatabase userUserprocessOracle

6、serverServerprocess9Oracle DatabaseAn Oracle database:Is a collection of data that is treated as a unitConsists of three file typesControl filesData files Archived log filesParameterfilePasswordfileRedo log filesOracle Database10Physical StructureThe physical structure of an Oracle database isdeterm

7、ined by the operating system files thatprovide the actual physical storage for databaseinformation.Control filesData filesRedo log filesControlfilesData files(includes data dictionary) HeaderOnlineredo logfiles)11Memory StructureOracles memory structure consists of two memoryareas known as: System G

8、lobal Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle InstanceProgram Global Area (PGA): Allocated when the server process is started12System Global Area (SGA)The SGA consists of several memory structures:Shared poolDatabase buffer cacheRedo log buffer Other st

9、ructures (e.g. lock and latch management, statistical data)There are two optional memory structures that can be configured within the SGA:Large poolJava pool13System Global Area (SGA)SGA is dynamic and sized using SGA_MAX_SIZE.SGA memory allocated and tracked in granules by SGA componentsContiguous

10、virtual memory allocationSize based on SGA_MAX_SIZE14Shared PoolThe shared pool is used to store the most recentlyexecuted SQL statements and the most recently useddata definitions.It consists of two key performance-related memory structures:Library cacheData dictionary cacheSized by the parameter S

11、HARED_POOL_SIZE.Shared poolData dictionarycacheLibrarycacheALTER SYSTEM SET SHARED_POOL_SIZE = 64M;15Library CacheThe library cache stores information about the mostrecently used SQL and PL/SQL statements. Thelibrary cache:Enables the sharing of commonly used statementsIs managed by a least recently

12、 used (LRU) algorithmConsists of two structures:Shared SQL areaShared PL/SQL areaHas its size determined by the shared pool sizing16Data Dictionary CacheThe data dictionary cache is a collection of the mostrecently used definitions in the database.It includes information about database files, tables

13、, indexes, columns, users, privileges, and other database objects.During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.Caching the data dictionary information into memory improves response time on queries.Size is determin

14、ed by the shared pool sizing.17Database Buffer CacheThe database buffer cache stores copies of datablocks that have been retrieved from the data files.It enables great performance gains when you obtain and update data.It is managed through a least recently used (LRU) algorithm.DB_BLOCK_SIZE determin

15、es the primary block size.Database buffercache18Redo Log Buffer CacheThe redo log buffer cache records all changesmade to the database data blocks. Its primary purpose is recovery.Changes recorded within are called redo entries.Redo entries contain information to reconstruct or redo changes.Size is

16、defined by LOG_BUFFER.Redo logbuffer cache19UserprocessProgram Global Area (PGA)The PGA is memory reserved for each user process that connects to an Oracle database.StackspaceSession informationsort area, cursor informationSGAShared SQL areasSGASessioninformation PGA Dedicated server Shared server S

17、erverprocessShared SQL areasStackspacesort area, cursor information20Process StructureAn Oracle process is a program that depending on its type can request information, execute a series of steps, or perform a specific task.Oracle takes advantage of various types of processes:User process: Started at

18、 the time a database user requests connection to the Oracle serverServer process: Connects to the Oracle Instance and is started when a user establishes a session.Background process: Available when an Oracle instance is started21User ProcessA user process is a program that requests interactionwith t

19、he Oracle server. It must first establish a connection. It does not interact directly with the Oracle server.Database userServerprocessUserprocessConnection established22Server ProcessA server process is a program that directly interactswith the Oracle server. It fulfills calls generated and returns

20、 results.Can be dedicated or shared server.Connection establishedSession createdDatabase userUserprocessOracle serverServerprocess23Background ProcessesThe relationship between the physical and memory structures is maintained and enforced by Oracles background processes.Mandatory background processe

21、sDBWnPMONCKPTLGWRSMONRECOOptional background processes ARCnLMONSnnn QMNnLMDn CJQ0Pnnn LCKnDnnn24InstanceSGADatabase buffercacheDBWnControl filesData files Redo log filesDatabase Writer (DBWn)DBWn writes when:CheckpointDirty buffers threshold reached No free buffersTimeoutRAC ping requestTablespace o

22、fflineTablespace read onlyTable DROP or TRUNCATETablespace BEGIN BACKUPDatabase25InstanceSGADBWnRedo logbufferLog Writer (LGWR)LGWR writes:At commit When one-third fullWhen there is 1 MB of redoEvery 3 secondsBefore DBWn writesControl filesData files Redo log filesLGWRDatabase26InstanceSGASMONSystem

23、 Monitor (SMON)Responsibilities:Instance recovery:Rolls forward changes in the redo logsOpens the database for user accessRolls back uncommitted transactionsCoalesces free space ever 3 secDeallocates temporary segmentsControl filesData files Redo log filesInstanceSGASMONDatabase27InstanceSGAPMONProc

24、ess Monitor (PMON)Cleans up after failed processes by:Rolling back the transactionReleasing locksReleasing other resourcesRestarts dead dispatchersPGA area28InstanceSGADWW0Redo LogBufferLGWRInstanceSGADBWnLGWRCKPTControl filesData files Redo log filesCheckpoint (CKPT)Responsible for:Signalling DBWn

25、at checkpointsUpdating datafile headers with checkpoint informationUpdating control files with checkpoint information29Archiver (ARCn)Optional background processAutomatically archives online redo logs when ARCHIVELOG mode is setPreserves the record of all changes made to the databaseControl filesDat

26、a files Redo log filesArchived Redo log filesARCn30Logical StructureThe 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.TablespaceData fileSegmen

27、tBlocksExtentSegment31Processing a SQL StatementConnect to an instance using:The user processThe server processThe Oracle server components that are used depend on the type of SQL statement:Queries return rows.DML statements log changes.Commit ensures transaction recovery.Some Oracle server componen

28、ts do not participate in SQL statement processing.32Processing a SELECT StatementSearch Shared PoolValidate statementValidate data sourceAcquire locksCheck privilegesParse statementExecute statementFetch values from cursor33Processing a DML StatementPrase statementExecute StatementRead data from dat

29、a and undo segment into buffer cacheLock data and undo segmentRecords the before image to the undo block and updates the data blockAny changed blocks in the buffer cache are marked as dirty buffersGenerate redo information34Managing an Oracle Instance35Starting Up a DatabaseNOMOUNTOPENMOUNTNOMOUNTSH

30、UTDOWNInstance startedSTARTUPSHUTDOWN36Starting Up a DatabaseMOUNTOPENMOUNTNOMOUNTSHUTDOWNControl file opened for this instanceInstance startedSTARTUPSHUTDOWN37Starting Up a DatabaseOPENOPENMOUNTNOMOUNTSHUTDOWNAll files opened as described by the control file for this instanceControl file opened for

31、 this instanceInstance startedSTARTUPSHUTDOWN38$ sqlplus as sysdba SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started. Total System Global Area 285212672 bytesFixed Size 1218472 bytesVariable Size 250177624 bytesDatabase Buffers 335

32、54432 bytesRedo Buffers 262144 bytesDatabase mounted.Database opened.SQL39STARTUP CommandStart up the instance and open the database:STARTUP FORCE RESTRICT PFILE=filenameOPEN RECOVERdatabase|MOUNT|NOMOUNTOPENenables users to access the databaseMOUNTmounts the database for certain DBA activities but

33、does not provide user access to the databaseNOMOUNTcreates the SGA and starts up the background processes but does not provide access to the databasePFILE=parfile enables a nondefault parameter file to be used to configure the instanceFORCEaborts the running instance before performing a normal start

34、upRESTRICT enables only users with RESTRICTED SESSION privilege to access the databaseRECOVERbegins media recovery when the database starts40The ALTER DATABASE CommandChange the state of the database from NOMOUNT to MOUNT:ALTER DATABASE db01 MOUNT;Open the database as a read-only database:ALTER DATA

35、BASE db01 OPEN READ ONLY;41Opening a Database in Restricted ModeUse the STARTUP command to restrict access to a database:Use the ALTER SYSTEM command to place an instance in restricted mode:STARTUP RESTRICTALTER SYSTEM ENABLE RESTRICTED SESSION;42Opening a Database in Read-Only ModeA databases can be opened as a read-only database.A read-only database can be used to:Execute queriesExecute disk sorts using locally managed tablespacesTake data files offline and online, not tablespacesPerform recovery of offline