Introduction of Week 2

Module 1DBMS Strategy and ORACLE Architecture

Database Administration Techniques and Practices1ITEC 450Fall 2012Organizations DBMS StrategyIts typical to run two or more DBMS products in large and medium-sized organizationThe organization should have the vision to consolidate and minimize the numberUsing an existing DBMS whenever it is possibleOnce a DBMS is installed, removal can be difficult: incompatibilities, conversion, special featuresOrganizations DBMS strategy is a join effort from groups of DBA, Architect, App Dev, and Business2ITEC 450Fall 2012Choosing a DBMS: Selection DriversA commercial off-the-shelf application package: ERM, PeopleSoft, CRMThe latest and greatest technology: Web, Java, .NETDBMS performance: TPC benchmarksOperating systems: Unix, Windows, LinuxHardware platforms: HP, Sun, IBM, DellTotal cost of ownership: license, people, development, and maintenance support3ITEC 450Fall 2012Levels of DBMS ArchitectureEnterprise and Departmental DBMSScalability and availability, high performanceLarge database, concurrent users, multiprocessorsExamples: Oracle, DB2, SQL ServerPersonal DBMS: Access, Visual dBase, Personal OracleMobile DBMS: a special version of DBMS for remote users who are not on the network all the time.4ITEC 450Fall 2012DBMS Architecture ComponentsDisk: logical and physical database structuresCPU: Operating system processesMemory: shared and private memory structuresNetwork: user access and process interactionsClustering: multiple computing systems working together5ITEC 450Fall 2012

Section Important NotesThis section is Oracle specific.Terms can be very different among different DBMSs (e.g. Oracle vs. SQL)Database (different)Instances (different)Schemas (common)Segments (Oracle specific)

6ITEC 450Fall 2012Oracle Database and InstanceAn Oracle Database server consists of an Oracle Database and one or more Oracle Database instances.Database: all Oracle related filesData files holding tables and indexesSystem files System, Undo, TempPhysical and logical structuresInstance: the combination of processes and memoryProcesses backgroundMemory allocated to OracleRunning components Instance is up

7ITEC 450Fall 2012Oracle Database and Instance8ITEC 450Fall 2012

Physical Database StructureMain types of files data files, control files, redo log files, archived redo log files.Data files database data, such as tables and indexesControl files Oracle structure information, such as database name, data file names, time stamp of databaseRedo log files database change logs for recoveryArchived redo log files offline copies of online redo log filesParameter files SPFILE, init.oraAdministration files alert.log, trace files, the password files9ITEC 450Fall 2012Physical Database Structure10ITEC 450Fall 2012

Logical Structure DetailsRead Chapter 2 Oracle Database ConceptData block - the standard block size is determined by the init parameter DB_BLOCK_SIZE.Extent - When you create a database object, you allocate it an initial extent. When the object grows more than the initial extent, Oracle will automatically allocate next extent.Segment - Oracle calls all the space allocated to a particular database object a segment, such as table or index.Tablespace - A tablespace contains one or more physical files; a data file belongs to one and only one tablespace.11ITEC 450Fall 2012LOGICAL & Physical Database Structure12ITEC 450Fall 2012

Online Redo Log Files and Archive Log FilesRedo log files record changes to database dataOnline redo log files are open and available whenever the database is up and runningThey capture details of DB transactions and information about changes to DB including:CheckpointsChangesData Manipulation Language (DML)Data Definition Language (DDL)Datafile changesA database should have at least three redo log groups containing at least one file each13ITEC 450Fall 2012Online Redo Log Files and Archive Log Files14ITEC 450Fall 2012

PURPOSE OF REDO LOG FILESPurpose: aid in database recoveryRedo log files keep list of DB changesIf DB loses changes, recovery process restores themRedo log files receive the change information before the data files are updatedIn minor failures (e.g., short power outage), redo log files are automatically checked during DB startup, and data is restored, from redo log files into data filesIn major failures (e.g., loss of an entire disk), data would not be saved from the online redo logs aloneYou need a full DB backup and archived redo log files that begin after the date of the backup

15ITEC 450Fall 2012STRUCTURE OF REDO LOG FILESRedo log files store info as a result of DB activityInformation is recorded in the redo log buffer in SGAContents of redo log buffer are written by LGWR process, to online redo log file, when:A transaction issues a COMMIT commandRedo log buffer is one-third fullEvery 3 secondsA checkpoint occursThe redo log file contains sets of redo recordsA redo record (or redo entry) is made up of a related group of change vectors that record a description of the changes to a single block in the DBA single transaction may generate many redo entries

16ITEC 450Fall 2012REDO LOG MANAGEMENT17ITEC 450Fall 2012

Oracle Database Structure18ITEC 450Fall 2012

Overview of Oracle InstanceWhen a database is started, Oracle starts many background processes and acquires memory structures.The combination of the background processes and memory buffers is called an Oracle instance.

19ITEC 450Fall 2012Oracle ProcessesRead Chapter 9 Oracle Database ConceptA process is a connection or thread to the operating system.Oracle processes server processes and background processes.The background processes are the core of the Oracle instance. When you start an instance, these processes are created; when you shutdown an instance, these processes are terminated.Database writerLog writerProcess monitorSystem monitorUser processes connecting users to the database instance20ITEC 450Fall 2012Oracle ProcessesDatabase Writer (DBW0): Database writer process uses a least-recently-used (LRU) algorithm to identify dirty buffers and write them to disk. Dirty buffers are buffers that contain data that has been modifiedLog Writer (LGWR): The log writer process is responsible for writing the redo entries from the redo log buffer to the disk files.Check Point (CKPT): The check point process performs check point on an on-going basis. Check point flushes all the buffers from the buffer cache to the disk and also all the current redo log sequence number and time stamps are written to the all of the data files.Process Monitor (PMON): The process monitor monitors all server processes. When ever a user connection fails, PMON is responsible foe cleaning up the buffer cache and freeing up system resources used by the failed process.System Monitor (SMON): The system monitor is involved in instance recovery when the database is started.

21ITEC 450Fall 2012Oracle Memory StructuresRead Chapter 8 Oracle Database ConceptThe memory structures enable Oracle to share executable code, and produces high performance.Oracle uses two basic memory structures: shared and process-specificSystem global area (SGA) total memory shared by all server processes including background processesProgram global area (PGA) memory used for private (application, program) processes22ITEC 450Fall 2012System Global AreaThe SGA main components.Database buffer cache recently used blocks of dataShared pool library cache, data dictionary cacheRedo log buffer a log of database changesOther Memory areas:Large pool optional large memory segments for shared SQL and better performanceJava pool memory for java code and data within the JVMStreams pool memory for using StreamsAutomatic shared memory management: total amount of SGA memory for the database23ITEC 450Fall 2012DATABASE BUFFER CACHEUsed to hold data blocks read from datafiles by server processesContains dirty or modified blocks and clean or unused or unchanged bocksDirty and clean blocks are managed in lists called the dirty list and the LRUFree space is created by DBWR writing out dirty blocks or aging out blocks from the LRUSize is managed by the parameter DB_BLOCK_BUFFERS24ITEC 450Fall 2012REDO LOG BUFFERA circular buffer that contains redo entriesRedo entries reflect changes made to the databaseRedo entries take up contiguous, sequential space in the bufferData stored in the redo log buffer is periodically written to the online redo log filesSize is managed by the parameter LOG_BUFFERDefault is 4 times the maximum data block size for the operating system

25ITEC 450Fall 2012SHARED POOLConsists of multiple smaller memory areasLibrary cacheShared SQL areaContains parsed SQL and execution plans for statements already run against the databaseProcedure and package storageDictionary cacheNames of all tables and views in the databaseNames and datatypes of columns in the database tablesPrivileges of all usersManaged via an LRU algorithmSize determined by the parameter SHARED_POOL_SIZE

26ITEC 450Fall 2012PROGRAM Global AreaProgram Global Area (PGA)Effectively used in session connection memoryBroken into private chunks for each server process

It is a non shared memory created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.

27ITEC 450Fall 2012SGA & BACKGROUND PROCESSES28ITEC 450Fall 2012

BACKGROUND PROCESSES (Continued)29ITEC 450Fall 2012

DATABASEInstall the software components, create DB files to store your data, and start a set of background processes that allocate memory and handle database activitiesOracle defines a DB as the collection of operating system files that store your dataDatabase server: combination of DB software, a DB (the files), and DB instance (the SGA and the background processes)Single-instance server (typical configuration)Multiple-instance serverClustered servers30ITEC 450Fall 2012SINGLE INSTANCE SERVER31ITEC 450Fall 2012

MULTI INSTANCE SERVER32ITEC 450Fall 2012

CLUSTERED SERVER33ITEC 450Fall 2012

ORACLE SOFTWARE OPTIONSEnterprise Edition: includes all major components Enables multiple users to connect concurrentlyOptimized for high data volume is commonSupports multiple DB instances and replicationStandard Edition: provides basic support for multi-user database applications on a smaller scale than that of the Enterprise EditionCannot be upgraded with database featuresPersonal Edition: single-user access to DB instanceTwo primary uses: programming and deployment

34ITEC 450Fall 2012ORACLE SOFTWARE SOME ADDITONAL OPTIONSOptional features requiring additional license fees:Oracle PartitioningOracle Cluster wareOracle SpatialOracle Data MiningOracle Database Extensions for .NETOracle Advanced SecurityAnd Other..

Note: Some of the slides are from Oracle 10g Database Administrator: Implementation and Administration by Gavin Powell and Carol McCullough-Dieter 35ITEC 450Fall 2012