harvard university oracle database administration session 2 system level
Post on 21-Dec-2015
225 views
TRANSCRIPT
![Page 1: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/1.jpg)
Harvard University
Oracle Database Administration
Session 2
System Level
![Page 2: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/2.jpg)
Harvard University
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
![Page 3: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/3.jpg)
Harvard University
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
![Page 4: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/4.jpg)
Harvard University
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)
![Page 5: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/5.jpg)
Harvard University
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
![Page 6: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/6.jpg)
Harvard University
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
The Instance
![Page 7: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/7.jpg)
Harvard University
Database Structures
Three Types – Internal to the Database– Memory Based (SGA and Processes)– External to the Database
![Page 8: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/8.jpg)
Harvard University
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.
![Page 9: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/9.jpg)
Harvard University
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
![Page 10: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/10.jpg)
Harvard University
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.
Internal Database Structures
![Page 11: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/11.jpg)
Harvard University
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
Internal Database Structures
![Page 12: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/12.jpg)
Harvard University
Triggers– Procedures that execute when a specific event
occurs– Used to augment referential integrity, enforce
additional integrity– Statement triggers– Row triggers
Internal Database Structures
![Page 13: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/13.jpg)
Harvard University
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
Internal Database Structures
![Page 14: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/14.jpg)
Harvard University
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
![Page 15: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/15.jpg)
Harvard University
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)
![Page 16: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/16.jpg)
Harvard University
Memory Structures
– System Global Area (SGA) Part of the System Memory
– Unix level processes
![Page 17: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/17.jpg)
Harvard University
![Page 18: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/18.jpg)
Harvard University
PMON SMON
DBW0 LGWR
Library CacheDictionary
Cache
ARCH
DatafilesRedo Log Files Archived Redo
Log Files
Data BlockBuffers Redo Log BuffersShared Pool
1
23 4
![Page 19: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/19.jpg)
Harvard University
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
![Page 20: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/20.jpg)
Harvard University
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.
![Page 21: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/21.jpg)
Harvard University
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 a Least Recently Used (LRU) algorithm
![Page 22: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/22.jpg)
Harvard University
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
![Page 23: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/23.jpg)
Harvard University
System Global Area
Automatic SGA sizing
Automatic PGA Sizing
![Page 24: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/24.jpg)
Harvard University
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
![Page 25: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/25.jpg)
Harvard University
PMON SMON
DBW0 LGWR
Library CacheDictionary
Cache
ARCH
DatafilesRedo Log Files Archived Redo
Log Files
Data BlockBuffers Redo Log BuffersShared Pool
1
23 4
SGA
![Page 26: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/26.jpg)
Harvard University
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
![Page 27: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/27.jpg)
Harvard University
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.
![Page 28: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/28.jpg)
Harvard University
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.
Background Processes
![Page 29: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/29.jpg)
Harvard University
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
![Page 30: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/30.jpg)
Harvard University
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.
![Page 31: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/31.jpg)
Harvard University
Oracle InstanceMemory structures
DatabaseDatafiles, Control files, etc
PMON SMON DBW0 LGWR
![Page 32: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/32.jpg)
Harvard University
The Physical World
The computer system– The operating system– Memory Modules– Storage Disks– Processors– Daemons
printers Networks
– Files
![Page 33: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/33.jpg)
Harvard University
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
![Page 34: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/34.jpg)
Harvard University
System
Oracle data blocks are multiples of the Operating System blocks
The computer memory uses the same block sizes as the disk storage
![Page 35: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/35.jpg)
Harvard University
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
![Page 36: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/36.jpg)
Harvard University
The OFA Standard
/oracle($O racle_base)
/app
/u01
/(other applications)
/app
/u02
/($S ID)
/oradata
/u03
/($S ID)
/oradata
/u04 /u05
/
![Page 37: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/37.jpg)
Harvard University
Oracle Directory Structure
bdum p udum p pfile cdum p arch create
<SID_NAM E>
adm in
netw ork dbs bin
$ORACLE_HOME
product
/u01/app/oracle ($ORACLE_BASE)
![Page 38: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/38.jpg)
Harvard University
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
![Page 39: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/39.jpg)
Harvard University
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
![Page 40: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/40.jpg)
Harvard University
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
![Page 41: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/41.jpg)
Harvard University
Datafiles and Tablespaces
![Page 42: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/42.jpg)
Harvard University
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.
![Page 43: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/43.jpg)
Harvard University
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
![Page 44: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/44.jpg)
Harvard University
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.
![Page 45: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/45.jpg)
Harvard University
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
![Page 46: Harvard University Oracle Database Administration Session 2 System Level](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d585503460f94a37563/html5/thumbnails/46.jpg)
Harvard University
Reading
10g and 11g Concepts Guide– Part 2
Chapter 3 Tablespaces,datafiles and control files
Chapter 5 Schema ObjectsChapter 7 The Data Dictionary
The two installation guides– A quick review of each. Look them over to
identify an overall process