1 / 46

Oracle Database Administration

Oracle Database Administration. Session 2 System Level. The Database. A database can be viewed as the collection of data from a business An application presents this data to the users in a manageable way. The application can insert, delete or update the data.

jett
Télécharger la présentation

Oracle Database Administration

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. Oracle Database Administration Session 2 System Level

  2. The Database • A database can be viewed as the collection of data from a business • An application presents this data to the users in a manageable way. • The application can insert, delete or update the data. • The database structures hold and manipulate this data

  3. The Database • The Data is stored in Relational Tables, defined by Columns • Data is stored as Rows in the table • These tables can then be related to each other using relationships • The database enforces these relationships

  4. The Instance • A database instance (server) is a set of memory structures and background processes • The memory structures together makeup the System Global Area( SGA)

  5. The Instance • This set of memory structures and background processes are used to provide access to the data • There can be multiple instances,or sets of memory structures,for one database eg. Oracle Real Application Clusters (RAC) • A parameter file, the init.ora, is used to configure the instance • The parameters held in this file, determine the size and composition of the instance

  6. The Instance • This file is read during the instance startup • When some changes are made to the init.ora, they will not take effect until the next time the database is started. • Pfile – Parameter File • The parameter file is used to set the name of the instance • This is the SID (system identification) name • Spfile – Server Parameter File

  7. Database Structures • Three Types • Internal to the Database • Memory Based (SGA and Processes) • External to the Database

  8. Internal Database Structures • Tables • Tables are the storage mechanism for data inside the oracle database. There are made up of columns. • Each column has a datatype and a length • Indexes • It is a partial copy of the data in a table • They are used to quickly find data in the database • An index is created when a primary key is created.

  9. Internal Database Structures • Views • A method of looking at “some” of the data in a table or in groups of tables • Views are masks over a table or group of tables • Views have no indexes • They can be used to enforce security

  10. Internal Database Structures • Procedures • Blocks of PL/SQL statements, called by applications • They do not return a value to the calling program • Functions • Like a procedure, but can return a value to the calling program.

  11. Internal Database Structures • Packages • These hold procedures and functions in logical groups • A package can have PUBLIC and PRIVATE elements • Public elements would be for a USERS usage • Private may be called by other procedures in the package

  12. Internal Database Structures • Triggers • Procedures that execute when a specific event occurs • Used to augment referential integrity, enforce additional integrity • Statement triggers • Row triggers

  13. Internal Database Structures • Sequences • Sequential lists of unique numbers • Used as primary Keys • Synonyms • Synonyms are pointers to tables, views, procedures functions. • To fully qualify an object we need the machine hostname, the instance name, the objects owner and the object’s name • Synonyms help to simplify the interface for the user

  14. Internal Database Structures • Users • Users are not physical structures in database. • All objects are owned by a user • All users are associated to a specific default tablespace • Users can be ‘granted’ access to the objects of other users and ‘granted’ the privilege to execute a specific function or procedure

  15. Internal Database Structures • Schemas • They are related to users. • A set of tables and other database objects are created as a schema and are owned by a user • Database Links • Database Links are used to reference data outside the database • Database links can be public or private • Rollback Segments (Undo Segments)

  16. Memory Structures • System Global Area (SGA) • Part of the System Memory • Unix level processes

  17. System Global Area (SGA) • The Data Buffer Cache is the cache area where data blocks are read into from the data segments, such as tables, indexes, etc. • Its size is controlled by the db_cache_size parameter in the init.ora file. • This space is managed by a least recently used (LRU) algorithm

  18. System Global Area • The Data Buffer Cache is very important in database tuning • If data is not in this area, it must be read from the datafile, so we have disk I/O.

  19. System Global Area • The Shared Pool stores the data dictionary cache and the library cache • Data Dictionary Cache stores data from the data dictionary tables • These tables store information about the database objects • It is managed by aLeast Recently Used (LRU) algorithm

  20. System Global Area • The Library Cache holds information about statements that have run against the database • It allows the sharing of commonly used SQL statements • It is also managed by an LRU algorithm • Their sizes are set by the shared_pool_size parameter

  21. System Global Area • Automatic SGA sizing • Automatic PGA Sizing

  22. System Global Area • The Redo log buffer holds redo data on a transaction, before it gets written to the redo log • Its size(in bytes) is controlled by the log_buffer parameter

  23. SGA

  24. Process Architecture • A process is a mechanism in an operating system that can run a series of steps. • A process has its own private memory area • An Oracle database server has • User processes • Oracle processes

  25. Background Processes • PMON cleans up failed user processes. It wake up periodically to check if it is needed. • SMON checks to see if a database needs recovery, on startup. It also coalesces free space in tablespaces.

  26. Background Processes • DBWR manages the data block buffer cache and the dictionary cache. It handles the batch writes of changed blocks from the SGA to the datafiles. There can be multiple DBWR processes.

  27. Background Processes • LGWR manages the writing of the contents of the redo log buffer, to the online redo log files. It writes the log entries in batches. If the redo logs are mirrored sets, then both are written to simultaneously. There can be multiple LGWR processes

  28. Background Processes • ARCH performs the archiving of the redo log files. LGWR writes to the redo log files in a round robin fashion. When all are full, it over-writes the first one. However, if the database is in archive mode, Oracle takes a copy of this file and stores it on disk.

  29. The Physical World • The computer system • The operating system • Memory Modules • Storage Disks • Processors • Daemons • printers • Networks • Files

  30. System • Information on a computer is stored in bytes • Bytes are grouped into Blocks • The Operating System usually has 512 bytes in a block • 1K = 1024 bytes • Oracle data is also stored in bytes • Oracle data blocks are in 2k,4k,8k,16, or 32k bytes

  31. System • Oracle data blocks are multiples of the Operating System blocks • The computer memory uses the same block sizes as the disk storage

  32. System • The O/S software is Solaris 9 or 10 • It is the layer between the computer hardware and the Oracle Software • It manages the computer system • writes to disk • reads from disk • controls printing • controls network connections

  33. The OFA Standard

  34. Oracle Directory Structure

  35. Files • Datafiles contain the actual data stored in a database • The Parameter file contain the initialization parameters used to create the memory area used to manage the database • Control files map the physical files of the database to the logical tablespaces and online redo logs. It helps ensure the database remains consistent

  36. Files • Redo files contain enough information to allow Oracle to reconstruct or back out a transaction, if the database should shutdown before these changes have been written to the disk • INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations generate redo information • Alert and Trace files contain information on the health of the database and provides warnings when problems occur

  37. Files • Backup files contain copies of the database files and can be used to recover the database. • The standard convention for file extensions or endings to file names are • data files .dbf • control files .ctl • redo log files .dbf (some use .rdo) • parameter file .ora

  38. Datafiles and Tablespaces

  39. Oracle’s “Logical” World • A tablespace can belong to only one database • There must be at least two tablespaces, SYSTEM and SYSAUX to create a database • Other include USERS, UNDO, TOOLS, etc.

  40. Undo segments • Undo segments hold the before image of the data in a transaction • As a program begins to change the data in the database, Oracle changes the physical blocks that contain that information. • Before changing the data block buffers in the SGA or writing to disk, Oracle takes a copy of this data in an undo segment

  41. Tablespace Examples • System holds all objects owned by the sys user • SYSAUX is an auxiliary tablespace to the SYSTEM tablespace • Rollback (RBS) (undo) is used to store the rollback segments • Temp (Temporary) is used for sort functions.

  42. Tablespace Examples • Users is the default space for user accounts • Tools should be the default space for the system user, after database creation. • Data holds the ‘real’ application data • Index holds the index data for the application

  43. Reading • 10g and 11g Concepts Guide • Part 2 • Chapter 3 Tablespaces,datafiles and control files • Chapter 5 Schema Objects • Chapter 7 The Data Dictionary • The two installation guides • A quick review of each. Look them over to identify an overall process

More Related