Oracle 1
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Oracle 1 as PDF for free.
More details
- Words: 4,195
- Pages: 52
Oracle Arthitecture
Last Reviewed By : Bhagavn/Sourajit Seth,Bangalore Last updated on :23/08/2005
People. Processes. Technology. Results.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
Contents •
Introduction to Oracle Server
•
Overview of Database Structure
•
Oracle Instance
•
Logical Database Structure
•
Physical Database Structure
•
Data Dictionary
•
Oracle Server Architecture – Memory Structures – Processes – Program Interface
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
2
Oracle Server •
RDBMS
•
Open, comprehensive and integrated approach to information management via. – Independence of physical and logical data storage – Variable and easy access to all data – Flexibility in database design – Reduced data storage and redundancy
•
Consists of a database and an instance
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
3
Data Modeling • A Data model is a conceptual representation of data structures(tables) required for a database and is very powerful in expressing and communicating the business requirements. • A data model visually represents the nature of data, business rules governing the data, and how it will be organized in the database. A data model is comprised of two parts logical design and physical design.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
4
Data Modeling types • Logical Data modeling • This is the actual implementation and extension of a conceptual data model. A Logical data model is the version of a data model that represents the business requirements(entire or part) of an organization and is developed before the physical data model. • Logical data model includes all required entities, attributes, key groups, and relationships that represent business information and define business rules.
• Physical Data modeling • Physical data model includes all required tables, columns, relationships, database properties for the physical implementation of databases. Database performance, indexing strategy, physical storage and denormalization are important parameters of a physical model. • The transformations from logical model to physical model include imposing database rules, implementation of referential integrity, super types and sub types etc.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
5
Entity Relationship Model • the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects. – It maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. – It is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user. – In addition, the model can be used as a design plan by the database developer to implement a data model in a specific database management software. • Entities are the principal data object about which information is to be collected. • A Relationship represents an association between two or more entities. • Relationship types: One-to-One, One-to-Many, Many-to-Many
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
6
Normalization • In relational database design, the process of organizing data to minimize redundancy. • Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability. This improvement is balanced against an increase in complexity and potential performance losses from the joining of the normalized tables at query-time. • Well normalized data makes programming (relatively) easy, and works very well in multi-platform, enterprise wide environments. Nonnormalized data leads to heartbreak.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
7
Normalization Types • First Normal form: • No repeating groups. Grouping similar type data into separate tables and giving each table a primary key. • Second Normal Form: • Each column must depend on the *entire* primary key. Move attributes that do not depend on entire key to a new table. • Third Normal Form: • Each column must depend on *directly* on the primary key. Separate items that doesn't depend solely on the primary key. – Arrange the data so that the columns in each table, other than the primary key, are dependent only on the whole primary key. Table is said to be in third normal form if all of the columns are dependent only on the primary key. – "the key, the whole key, and nothing but the key"
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
8
Overview of Database Structure •
Physical Structure
•
Logical Structure
Physical Structure – Operating System files that constitute the database. – Files are physical storage of the database information. – 3 types of files: Data files Redo log files Control files
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
9
Overview of Database Structure
Logical Structure Dictate how the physical space of the database is used. Comprises: – Table spaces : Logical area of storage – Schema Objects: Logical structures that directly refer to the dB data. Schema objects and their relationships form the relational design of the database.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
10
Oracle Instance
•
When a database is started, a system global area (SGA)is allocated and Oracle background processes are started. SGA is a memory area used for dB information shared by dB users.
•
An Oracle instance is this combination of background processes and memory buffers.
•
Oracle instance has 2 types of processes:
www.perotsystems.com
– User Process: Executes the application program code – Oracle Process: Server Process: Perform work for user processes Background Process: Perform maintenance work for the Oracle Server.
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
11
Logical Database Structure - Tablespaces
Database System Tablespace
USERS Tablespace
Data1.ORA (1MB) Data2.ORA (1MB)
www.perotsystems.com
Data3.ORA (4MB)
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
12
Logical Database Structure - Schemas & Schema Objects •
Schema is a collection of objects
•
Schema objects are logical structures that directly refer to the database’s data
•
No relationship between a tablespace and a schema
Schema Objects – Tables : Basic unit of data storage. Data is stored in the form of rows and columns. – Views : Custom tailored presentation of data in one or more tables.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
13
Logical Database Structure Schemas & Schema Objects Schema Objects – Sequences: Generate a serial list of unique numbers for numeric columns of a database’s table. – Program Unit: Stored procedures, functions and packages. – Indexes: Provides a faster access path to a table data. – Clusters: Groups of one or more tables physically stored together because they share common columns, thus improving the disk access time. – Database links: Describes a path from one database to another. Used in distributed databases. – Data Blocks: A data block corresponds to a specific number of bytes of physical database space on the disk.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
14
Logical Database Structure - Schemas & Schema Objects Schema Objects – Extents: A specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information – Segments: A set of extents allocated for a certain logical structure. 4 types are: Data Segment: Each table has a data segment Index Segment: Each index has a index segment Rollback Segment: Stores ‘undo’ information Temporary Segment: Created when a SQL statement needs a temporary work area. Oracle allocates segments dynamically when existing extents of a segment become full. This dynamic allocation causes non-contiguous extents.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
15
Physical Database Structure
Data files – One or more physical files in the disk – Contain database data – Can be associated with only one database – Have certain characteristics that allow them to automatically extend when the database runs out of space – One or more data files form a tablespace The data in a data file is read during normal database operation and stored in the memory cache of Oracle. Modified / new data is pooled in memory and written to appropriate data files all at once.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
16
Physical Database Structure
Redo Log files – Two or more physical files in the disk – Set of redo log files are called the database’s ‘redo log’ – Records all changes made to data – Protects database against failures – Process of applying a redo log during a recovery operation is called ‘rolling forward’
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
17
Physical Database Structure
Control Files – One physical file in the disk – Contains entries that specify the physical structure of the database viz. Database name, names and locations of data files and redo log files, time stamp of database creation etc. – Can be associated with only one database When an instance of an Oracle database is started, the control file is used to identify the database and the redo log files that have to be opened. If the physical makeup of the database is altered, this control file is automatically modified by Oracle to reflect the change.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
18
Data Dictionary •
Contains data about data
•
Stores information about – logical and physical structure of the database – Valid users of the database – integrity constraints defined for tables in the database – space allocation for a schema object and its utilization
•
Created when the database is created
•
Automatically updated by Oracle in response to specific actions
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
19
Oracle Server Architecture
The architectural features discussed in this session is to provide an understanding of Oracle’s capabilities to support: – many users concurrently accessing a single database – high performance required by concurrent multi-user, multiapplication database Topics covered here are: – Memory Structures – Processes – Program Interface
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
20
Oracle Server Architecture
Figure 1
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
21
Memory Structures
Oracle creates and uses memory structures to complete several jobs. Ex: Memory is used to – store program code being executed. – Store data that is shared among users. Basic Memory Structures associated with Oracle:
– System Global Area (SGA) – Program Global Area (PGA)
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
22
Memory Structures -SGA •
Shared memory region that contains data and control information for one Oracle instance.
•
Oracle allocates the SGA when an instance starts and de-allocates it when an instance shuts down.
•
Optimal performance requires that the SGA be as large as possible, to store as much data as possible, thus minimizing disk I/O.
•
Types of structures (fixed sizes) within the SGA: – Database Buffer Cache – Redo log Buffer – Shared Pool – Cursors
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
23
Memory Structures -SGA
Database Buffer Cache – Stores most recently used blocks of database data – Set of database buffers is an instance of the database buffer cache – Can contain modified data that has not been permanently written to the database Since most recently used data is kept in memory, less disk I/O is necessary and performance is increased. Redo log Buffer – Stores redo entries - a log of changes made to the database – Redo entries are written to an online redo log file Redo log file is used for database recovery.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
24
Memory Structures -SGA Shared Pool – Stores shared memory constructs like shared SQL areas – Shared SQL area is used to process every unique SQL statement submitted to the database and contains information such as the execution plan for the SQL statement These are used by multiple applications that issue the same statement, thus leaving more shared memory for other users Cursors – Handle for the memory associated with a specific statement
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
25
Memory Structures - PGA •
Memory buffer that contains data and control information of a single process (server or background)
•
Created when a process is started
•
Also called ‘Process Global Area’
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
26
Processes
•
A process is a ‘thread of control’ or mechanism in an operating system that can execute a series of steps.
•
Also called ‘job’ or ‘task’
•
Oracle has two general types of processes: – User (Client) processes – Oracle processes
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
27
Processes - User
•
Created when a user runs an application program.
•
Maintained to execute the software code of an application program
•
User process manages communication with the server process using program interfaces.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
28
Processes - Oracle •
Called by other processes to perform functions on behalf of the invoking process
•
Types of Oracle processes: – Server Processes – Background Processes Database Writer (DBWR) Checkpoint (CKPT) System Monitor (SMON) Archiver (ARCH) Dispatcher (Dnnn)
www.perotsystems.com
Log Writer (LGWR) Process Monitor (PMON) Recoverer (RECO) Lock (LCKn)
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
29
Processes - Oracle Server Server Processes – Created by Oracle to handle requests from connected user processes – Communicates with user process and interacts with Oracle to carry out requests of the user. – The number of server processes viz.. a viz. the user processes can be configured based on a dedicated server / multithreaded server configuration
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
30
Processes - Oracle Background Background Processes – Oracle creates a set of background processes for each instance – Background processes consolidate common functions that are handled by Oracle programs – Perform asynchronous I/O and monitor other Oracle processes – Each Oracle instance may use several background processes
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
31
Processes - Oracle Background Database Writer (DBWR) Writes modified blocks from the dB buffer cache to the data files (disk). Log Writer (LGWR) Writes redo log entries to the redo log files (disk) Checkpoint (CKPT) Responsible for signalling DBWR to do its job at specific times, called the check point. Also updates all data files and control files to indicate the most recent check point. CKPT is optional. If not present LGWR will assume its role.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
32
Processes - Oracle Background
System Monitor (SMON) Performs instance recovery at instance startup. It cleans up temporary segments that are not in use and recovers dead transactions skipped during a dB crash. Process Monitor (PMON) Performs process recovery when a user process fails. It cleans up the cache and frees resources that the process was using. It checks the Dnnn and server processes to restart them if they have failed.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
33
Processes - Oracle Background Archiver (ARCH) Copies the online redo log files to archive storage when they get full. Recoverer (RECO) Resolves the distributed transactions that are pending due to n/w or system failure. Applicable in a distributed database scenario.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
34
Processes - Oracle Background Dispatcher (Dnnn) Routes requests from connected user processes to shared server processes. Also returns response back to user processes. Applicable in a multi-threaded server configuration. Atleast one dispatcher process is created for every communication protocol in use (D000,….., Dnnn) Lock (LCKn) Used to lock inter-instances in a Oracle Parallel Server scenario.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
35
Program Interface •
A software layer between a database application and Oracle
•
A mechanism by which a user process communicates with a server process
•
Provides a method of standard communication between any client tool or application and Oracle – Formats information requests – Converts, translates and passes data – Traps and returns errors
Ex: OCI or SQLLIB, TCP/IP
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
36
Oracle Architecture
Session 2
People. Processes. Technology. Results.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
Contents
•
Data Access
•
Data Concurrency & Consistency
•
Database Security
•
Database Backup & Recovery
•
Distributed Processing
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
38
Data Access Oracle meets the general requirements of a DBMS to: – Adhere to industry accepted standards for a data access language – Control and preserve data consistency while manipulating data – Provide for defining and enforcing rules to maintain integrity of information – Provide high performance
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
39
Data Access •
SQL – Simple yet powerful database access language – Compliant with ANSI/ISO standard SQL data la
•
Transactions – A logical unit of work that comprises one or more SQL statements, executed by a single user. Transactions are committed - making permanent changes to the database Transactions are rolled back - retracting any of the changes made by SQL statements Save points - Large transactions are divided into smaller parts using intermediate markers
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
40
Data Access •
Transactions – Guarantee consistent changes to data when SQL statements within a transaction are grouped logically. – Data in the referenced tables are in a consistent state before and after the transaction
•
PL/SQL – Procedural language extension to SQL - Combines ease & flexibility of SQL with the procedural functionality of a structured programming language – Defined and stored in compiled form, centrally in a database Stored Procedures Functions Packages Database triggers
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
41
Data Access •
www.perotsystems.com
Data Integrity – Guarantees that data adheres to certain business rules as determined by the application developer or the database administrator – Achieved using Integrity Constraints NOT NULL UNIQUE KEYS Database Triggers
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
42
Data Concurrency & Consistency Oracle has software mechanisms to fulfill the following: – Data must be read and modified in a consistent fashion – Data concurrency of a multi-user system must be maximized – Provide high performance
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
43
Data Concurrency & Consistency •
www.perotsystems.com
Concurrency – Simultaneous access of the same data in the database, by many users causing data integrity issues. – Concurrency is controlled using Read Consistency Rollback segments Read-only transactions Locking Automatic Locking Manual Locking
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
44
Database Security Oracle has security features that control how a database is accessed and used: – Prevent unauthorized database access – Prevent unauthorized access to schema objects – Control disk usage – control system resource usage – audit user actions
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
45
Database Security Database security can be classified into: – System Security valid user name and password amount of disk space available to the objects of a user resource limits for a user – Data Security Users, their access to various schema objects and specific actions allowed on the schema objects Actions to be audited for each schema object
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
46
Database Security Security Mechanisms – Database users & Schemas actions tablespace quotas system resource limits – privileges system privileges object privileges granting privileges – storage settings and quotas Default tablespace Temporary tablespace Tablespace quotas
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
47
Database Security Security Mechanisms – roles – profiles & resource limits number of concurrent sessions CPU processing time amount of logical I/O amount of idle time for a user session amount of connect time for a user session – auditing statement privilege object
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
48
Database Backup & Recovery One or more database files can be physically damaged as a result of disk failure. Hence backup of the database is important. Types of backup: – Full backup Done when the database is closed and unavailable for use – Partial backup Done to backup a part of the database
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
49
Database Backup & Recovery In every database system, the possibility of a system or hardware failure always exists. Hence recovery is important. Types of failures: – User Error – Statement and process failure – Instance failure – media failure Structures used for Recovery: – The Redo Log – Control files – Rollback Segments
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
50
Database Backup & Recovery Recovery Steps – Rolling Forward – Rolling Back
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
51
Distributed Processing Distributed processing uses more than one processor to divide the processing for a set of related jobs. This improves the performance and capabilities of the system as a whole. Oracle uses its Client/Server architecture to achieve this.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
52
Last Reviewed By : Bhagavn/Sourajit Seth,Bangalore Last updated on :23/08/2005
People. Processes. Technology. Results.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
Contents •
Introduction to Oracle Server
•
Overview of Database Structure
•
Oracle Instance
•
Logical Database Structure
•
Physical Database Structure
•
Data Dictionary
•
Oracle Server Architecture – Memory Structures – Processes – Program Interface
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
2
Oracle Server •
RDBMS
•
Open, comprehensive and integrated approach to information management via. – Independence of physical and logical data storage – Variable and easy access to all data – Flexibility in database design – Reduced data storage and redundancy
•
Consists of a database and an instance
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
3
Data Modeling • A Data model is a conceptual representation of data structures(tables) required for a database and is very powerful in expressing and communicating the business requirements. • A data model visually represents the nature of data, business rules governing the data, and how it will be organized in the database. A data model is comprised of two parts logical design and physical design.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
4
Data Modeling types • Logical Data modeling • This is the actual implementation and extension of a conceptual data model. A Logical data model is the version of a data model that represents the business requirements(entire or part) of an organization and is developed before the physical data model. • Logical data model includes all required entities, attributes, key groups, and relationships that represent business information and define business rules.
• Physical Data modeling • Physical data model includes all required tables, columns, relationships, database properties for the physical implementation of databases. Database performance, indexing strategy, physical storage and denormalization are important parameters of a physical model. • The transformations from logical model to physical model include imposing database rules, implementation of referential integrity, super types and sub types etc.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
5
Entity Relationship Model • the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects. – It maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. – It is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user. – In addition, the model can be used as a design plan by the database developer to implement a data model in a specific database management software. • Entities are the principal data object about which information is to be collected. • A Relationship represents an association between two or more entities. • Relationship types: One-to-One, One-to-Many, Many-to-Many
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
6
Normalization • In relational database design, the process of organizing data to minimize redundancy. • Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability. This improvement is balanced against an increase in complexity and potential performance losses from the joining of the normalized tables at query-time. • Well normalized data makes programming (relatively) easy, and works very well in multi-platform, enterprise wide environments. Nonnormalized data leads to heartbreak.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
7
Normalization Types • First Normal form: • No repeating groups. Grouping similar type data into separate tables and giving each table a primary key. • Second Normal Form: • Each column must depend on the *entire* primary key. Move attributes that do not depend on entire key to a new table. • Third Normal Form: • Each column must depend on *directly* on the primary key. Separate items that doesn't depend solely on the primary key. – Arrange the data so that the columns in each table, other than the primary key, are dependent only on the whole primary key. Table is said to be in third normal form if all of the columns are dependent only on the primary key. – "the key, the whole key, and nothing but the key"
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
8
Overview of Database Structure •
Physical Structure
•
Logical Structure
Physical Structure – Operating System files that constitute the database. – Files are physical storage of the database information. – 3 types of files: Data files Redo log files Control files
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
9
Overview of Database Structure
Logical Structure Dictate how the physical space of the database is used. Comprises: – Table spaces : Logical area of storage – Schema Objects: Logical structures that directly refer to the dB data. Schema objects and their relationships form the relational design of the database.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
10
Oracle Instance
•
When a database is started, a system global area (SGA)is allocated and Oracle background processes are started. SGA is a memory area used for dB information shared by dB users.
•
An Oracle instance is this combination of background processes and memory buffers.
•
Oracle instance has 2 types of processes:
www.perotsystems.com
– User Process: Executes the application program code – Oracle Process: Server Process: Perform work for user processes Background Process: Perform maintenance work for the Oracle Server.
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
11
Logical Database Structure - Tablespaces
Database System Tablespace
USERS Tablespace
Data1.ORA (1MB) Data2.ORA (1MB)
www.perotsystems.com
Data3.ORA (4MB)
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
12
Logical Database Structure - Schemas & Schema Objects •
Schema is a collection of objects
•
Schema objects are logical structures that directly refer to the database’s data
•
No relationship between a tablespace and a schema
Schema Objects – Tables : Basic unit of data storage. Data is stored in the form of rows and columns. – Views : Custom tailored presentation of data in one or more tables.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
13
Logical Database Structure Schemas & Schema Objects Schema Objects – Sequences: Generate a serial list of unique numbers for numeric columns of a database’s table. – Program Unit: Stored procedures, functions and packages. – Indexes: Provides a faster access path to a table data. – Clusters: Groups of one or more tables physically stored together because they share common columns, thus improving the disk access time. – Database links: Describes a path from one database to another. Used in distributed databases. – Data Blocks: A data block corresponds to a specific number of bytes of physical database space on the disk.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
14
Logical Database Structure - Schemas & Schema Objects Schema Objects – Extents: A specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information – Segments: A set of extents allocated for a certain logical structure. 4 types are: Data Segment: Each table has a data segment Index Segment: Each index has a index segment Rollback Segment: Stores ‘undo’ information Temporary Segment: Created when a SQL statement needs a temporary work area. Oracle allocates segments dynamically when existing extents of a segment become full. This dynamic allocation causes non-contiguous extents.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
15
Physical Database Structure
Data files – One or more physical files in the disk – Contain database data – Can be associated with only one database – Have certain characteristics that allow them to automatically extend when the database runs out of space – One or more data files form a tablespace The data in a data file is read during normal database operation and stored in the memory cache of Oracle. Modified / new data is pooled in memory and written to appropriate data files all at once.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
16
Physical Database Structure
Redo Log files – Two or more physical files in the disk – Set of redo log files are called the database’s ‘redo log’ – Records all changes made to data – Protects database against failures – Process of applying a redo log during a recovery operation is called ‘rolling forward’
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
17
Physical Database Structure
Control Files – One physical file in the disk – Contains entries that specify the physical structure of the database viz. Database name, names and locations of data files and redo log files, time stamp of database creation etc. – Can be associated with only one database When an instance of an Oracle database is started, the control file is used to identify the database and the redo log files that have to be opened. If the physical makeup of the database is altered, this control file is automatically modified by Oracle to reflect the change.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
18
Data Dictionary •
Contains data about data
•
Stores information about – logical and physical structure of the database – Valid users of the database – integrity constraints defined for tables in the database – space allocation for a schema object and its utilization
•
Created when the database is created
•
Automatically updated by Oracle in response to specific actions
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
19
Oracle Server Architecture
The architectural features discussed in this session is to provide an understanding of Oracle’s capabilities to support: – many users concurrently accessing a single database – high performance required by concurrent multi-user, multiapplication database Topics covered here are: – Memory Structures – Processes – Program Interface
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
20
Oracle Server Architecture
Figure 1
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
21
Memory Structures
Oracle creates and uses memory structures to complete several jobs. Ex: Memory is used to – store program code being executed. – Store data that is shared among users. Basic Memory Structures associated with Oracle:
– System Global Area (SGA) – Program Global Area (PGA)
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
22
Memory Structures -SGA •
Shared memory region that contains data and control information for one Oracle instance.
•
Oracle allocates the SGA when an instance starts and de-allocates it when an instance shuts down.
•
Optimal performance requires that the SGA be as large as possible, to store as much data as possible, thus minimizing disk I/O.
•
Types of structures (fixed sizes) within the SGA: – Database Buffer Cache – Redo log Buffer – Shared Pool – Cursors
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
23
Memory Structures -SGA
Database Buffer Cache – Stores most recently used blocks of database data – Set of database buffers is an instance of the database buffer cache – Can contain modified data that has not been permanently written to the database Since most recently used data is kept in memory, less disk I/O is necessary and performance is increased. Redo log Buffer – Stores redo entries - a log of changes made to the database – Redo entries are written to an online redo log file Redo log file is used for database recovery.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
24
Memory Structures -SGA Shared Pool – Stores shared memory constructs like shared SQL areas – Shared SQL area is used to process every unique SQL statement submitted to the database and contains information such as the execution plan for the SQL statement These are used by multiple applications that issue the same statement, thus leaving more shared memory for other users Cursors – Handle for the memory associated with a specific statement
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
25
Memory Structures - PGA •
Memory buffer that contains data and control information of a single process (server or background)
•
Created when a process is started
•
Also called ‘Process Global Area’
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
26
Processes
•
A process is a ‘thread of control’ or mechanism in an operating system that can execute a series of steps.
•
Also called ‘job’ or ‘task’
•
Oracle has two general types of processes: – User (Client) processes – Oracle processes
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
27
Processes - User
•
Created when a user runs an application program.
•
Maintained to execute the software code of an application program
•
User process manages communication with the server process using program interfaces.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
28
Processes - Oracle •
Called by other processes to perform functions on behalf of the invoking process
•
Types of Oracle processes: – Server Processes – Background Processes Database Writer (DBWR) Checkpoint (CKPT) System Monitor (SMON) Archiver (ARCH) Dispatcher (Dnnn)
www.perotsystems.com
Log Writer (LGWR) Process Monitor (PMON) Recoverer (RECO) Lock (LCKn)
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
29
Processes - Oracle Server Server Processes – Created by Oracle to handle requests from connected user processes – Communicates with user process and interacts with Oracle to carry out requests of the user. – The number of server processes viz.. a viz. the user processes can be configured based on a dedicated server / multithreaded server configuration
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
30
Processes - Oracle Background Background Processes – Oracle creates a set of background processes for each instance – Background processes consolidate common functions that are handled by Oracle programs – Perform asynchronous I/O and monitor other Oracle processes – Each Oracle instance may use several background processes
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
31
Processes - Oracle Background Database Writer (DBWR) Writes modified blocks from the dB buffer cache to the data files (disk). Log Writer (LGWR) Writes redo log entries to the redo log files (disk) Checkpoint (CKPT) Responsible for signalling DBWR to do its job at specific times, called the check point. Also updates all data files and control files to indicate the most recent check point. CKPT is optional. If not present LGWR will assume its role.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
32
Processes - Oracle Background
System Monitor (SMON) Performs instance recovery at instance startup. It cleans up temporary segments that are not in use and recovers dead transactions skipped during a dB crash. Process Monitor (PMON) Performs process recovery when a user process fails. It cleans up the cache and frees resources that the process was using. It checks the Dnnn and server processes to restart them if they have failed.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
33
Processes - Oracle Background Archiver (ARCH) Copies the online redo log files to archive storage when they get full. Recoverer (RECO) Resolves the distributed transactions that are pending due to n/w or system failure. Applicable in a distributed database scenario.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
34
Processes - Oracle Background Dispatcher (Dnnn) Routes requests from connected user processes to shared server processes. Also returns response back to user processes. Applicable in a multi-threaded server configuration. Atleast one dispatcher process is created for every communication protocol in use (D000,….., Dnnn) Lock (LCKn) Used to lock inter-instances in a Oracle Parallel Server scenario.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
35
Program Interface •
A software layer between a database application and Oracle
•
A mechanism by which a user process communicates with a server process
•
Provides a method of standard communication between any client tool or application and Oracle – Formats information requests – Converts, translates and passes data – Traps and returns errors
Ex: OCI or SQLLIB, TCP/IP
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
36
Oracle Architecture
Session 2
People. Processes. Technology. Results.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
Contents
•
Data Access
•
Data Concurrency & Consistency
•
Database Security
•
Database Backup & Recovery
•
Distributed Processing
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
38
Data Access Oracle meets the general requirements of a DBMS to: – Adhere to industry accepted standards for a data access language – Control and preserve data consistency while manipulating data – Provide for defining and enforcing rules to maintain integrity of information – Provide high performance
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
39
Data Access •
SQL – Simple yet powerful database access language – Compliant with ANSI/ISO standard SQL data la
•
Transactions – A logical unit of work that comprises one or more SQL statements, executed by a single user. Transactions are committed - making permanent changes to the database Transactions are rolled back - retracting any of the changes made by SQL statements Save points - Large transactions are divided into smaller parts using intermediate markers
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
40
Data Access •
Transactions – Guarantee consistent changes to data when SQL statements within a transaction are grouped logically. – Data in the referenced tables are in a consistent state before and after the transaction
•
PL/SQL – Procedural language extension to SQL - Combines ease & flexibility of SQL with the procedural functionality of a structured programming language – Defined and stored in compiled form, centrally in a database Stored Procedures Functions Packages Database triggers
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
41
Data Access •
www.perotsystems.com
Data Integrity – Guarantees that data adheres to certain business rules as determined by the application developer or the database administrator – Achieved using Integrity Constraints NOT NULL UNIQUE KEYS Database Triggers
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
42
Data Concurrency & Consistency Oracle has software mechanisms to fulfill the following: – Data must be read and modified in a consistent fashion – Data concurrency of a multi-user system must be maximized – Provide high performance
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
43
Data Concurrency & Consistency •
www.perotsystems.com
Concurrency – Simultaneous access of the same data in the database, by many users causing data integrity issues. – Concurrency is controlled using Read Consistency Rollback segments Read-only transactions Locking Automatic Locking Manual Locking
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
44
Database Security Oracle has security features that control how a database is accessed and used: – Prevent unauthorized database access – Prevent unauthorized access to schema objects – Control disk usage – control system resource usage – audit user actions
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
45
Database Security Database security can be classified into: – System Security valid user name and password amount of disk space available to the objects of a user resource limits for a user – Data Security Users, their access to various schema objects and specific actions allowed on the schema objects Actions to be audited for each schema object
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
46
Database Security Security Mechanisms – Database users & Schemas actions tablespace quotas system resource limits – privileges system privileges object privileges granting privileges – storage settings and quotas Default tablespace Temporary tablespace Tablespace quotas
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
47
Database Security Security Mechanisms – roles – profiles & resource limits number of concurrent sessions CPU processing time amount of logical I/O amount of idle time for a user session amount of connect time for a user session – auditing statement privilege object
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
48
Database Backup & Recovery One or more database files can be physically damaged as a result of disk failure. Hence backup of the database is important. Types of backup: – Full backup Done when the database is closed and unavailable for use – Partial backup Done to backup a part of the database
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
49
Database Backup & Recovery In every database system, the possibility of a system or hardware failure always exists. Hence recovery is important. Types of failures: – User Error – Statement and process failure – Instance failure – media failure Structures used for Recovery: – The Redo Log – Control files – Rollback Segments
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
50
Database Backup & Recovery Recovery Steps – Rolling Forward – Rolling Back
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
51
Distributed Processing Distributed processing uses more than one processor to divide the processing for a set of related jobs. This improves the performance and capabilities of the system as a whole. Oracle uses its Client/Server architecture to achieve this.
www.perotsystems.com
Proprietary and confidential. © 2005 Perot Systems. All rights reserved. All registered trademarks are the property of their respective owners.
V1.0
52
