ORACLE Architecture and Administration

1
International Technology University Lecture
1 Overview of Oracle Database
2
Part 1. Introduction to Database System

• Introduction to Database
• History of RDBMS
• Entity-Relationship Modeling
• Database Language

3
Introduction to Database

• File-Based Approach
• Each program defines and manages its own data
• Limitation
• Separation and isolation of data
• Duplication of data
• Data dependence
• Incompatibility of files
• Fixed queries/proliferation of application
  program
• Database Approach
• A shared collection of logically related data,
  designed to meet the information needs of an
  organization

4
Introduction to Database

• Database Management System(DBMS)
• A software system that enables users to define,
  create and maintain the database and provides
  controlled access to database
• DDL
• DML procedural, non-procedural
• Control security, integrity, concurrency
  control,
• recovery control, user-accessible catalog
• Components of the DBMS Environment
• Hardware - Software - Data - Procedures - People

5
Introduction to Database

• Advantages of DBMS
• - Control of data redundancy - Economy of scale
• - Data consistency - Balance of conflicting
  requirements
• - More information from the same amount of data
• - Sharing of data - Improved data accessibility
  and responsiveness
• - Improved data integrity - Increased
  productivity
• - Improved security - Improved maintenance
  through data independence
• - Enforcement of standards - Increased
  concurrency
• - Improved backup and recovery services
• Disadvantages of DBMS
• - Complexity, Size, Cost of DBMSs, Additional H/W
  costs
• - Cost of conversion, Performance, Higher impact
  of a failure

6
Introduction to Database

• Three-Level Database Architecture
• External Level
• The users view of the database
• Conceptual Level
• The community view of the database
• Internal Level
• The physical representation of the database on
  the computer

7
Introduction to Database

• Functions of a DBMS
• 1. Data storage, retrieval, and update
• 2. A user-accessible catalog
• 3. Transaction support
• 4. Concurrency control services
• 5. Recovery services
• 6. Authorization services
• 7. Support for data communication
• 8. Integrity services
• 9. Services to promote data independence
• 10. Utility services

8
Introduction to Database

• Components of a DBMS

Programmers
Users
DBA
Application Programs
Queries
Database Schema
DBMS
DML preprocessor
Query processor
DDL compiler
Program object code
Database manager
Dictionary manager
Access methods
File manager
Database and system catalog
System buffers
9
Introduction to Database

• Components of Database Manager

Authorization control
Integrity checker
Command processor
Query optimizer
Transaction manager
Scheduler
Data Manager
Buffer manager
Recovery manager
10
History of RDBMS

• History of DBMS
• 1960s - Apollo moon-landing project, GUAM
• mid 1960s - IMS by IBM (hierarchical DBMS)
• mid 1960s - IDS by GE (network DBMS)
• 1965 - CODASYL(Conference on Data SYStems
  Language)
• 1967 -DBTG(Data Base Task Group)
• 1970 - E.F.Codd of the IBM Research Lab.
• Late 1970s - System R project at IBM
• 1980s - commercial relational DBMS(DB2, Oracle,
  Informix..)
• Now - OODBMS, ORDBMS

11
RDBMS Terminology

• Terminology
• Relation a relation is a table with columns and
  rows
• Attribute an attribute is a named column of a
  relation
• Domain a domain is the set of allowable values
  for one or more attributes
• Tuple a tuple is a row of a relation
• Degree the degree of a relation is the number
  of attributes it contains
• Cardinality the cardinality of a relation is
  the number of tuples it contains
• Relational database a collection of normalized
  relation

12
Properties of Relations

• Properties of Relations
• The relation has a name that is distinct from all
  other relation names
• Each cell of the relation contains exactly on
  atomic value
• Each attribute has a distinct name
• The values of an attribute are all from the same
  domain
• The order of attributes has no significance
• Each tuple is distinct there are no duplicate
  tuples
• The order of tuples has no significance,
  theoretically

13
Properties of Relations

• When is a DBMS Relational?
• Foundational rules
• Rule 0 Foundational rule Rule 12
  Non-subversion rule
• Structural rules
• Rule 1 Information representation Rule 6 View
  updateing
• Integrity rules
• Rule 3 Systematic treatment of null values Rule
  10 Integrity independence
• Data manipulation rules
• Rule2 Guaranteed access Rule 4 Dynamic online
  catalog based on the relational
  model
• Rule5 Comprehensive data sublanguage Rule7
  High-level insert, update, delete
• Data independence rules
• Rule8 Physical data independence Rule 9
  Logical data independence
• Rule11 Distribution independence

14
Entity-Relationship Modeling

• Concepts of the E-R Modeling
• Entity Types
• An object or concept that is identified by the
  enterprise as having an independent existence
• Attributes
• A property of an entity or a relationship type
• Relationship Types
• A meaningful association among entity types

15
Normalization

• Normalization
• A technique for producing a set of relations with
  desirable properties, given the data requirements
  of an enterprise
• UNF is a table that contains one or more
  repeating groups
• 1NF is a relation in which the intersection of
  each row and column contains one and only one
  value
• 2NF is a relation that is in 1NF and every
  non-primary-key attribute is fully functionally
  dependent on the primary key.
• 3NF is a relation that is in 1NF, 2NF in which no
  non-primary-key attribute is transitively
  dependent on the primary key
• BCNF is a relation in which every determinant is
  a candidate key
• 4NF is a relation that is in BCNF and contains no
  trivial multi-valued dependency
• 5NF is a relation that contains no join dependency

16
Database Views

• Conceptual Database Design
• The process of constructing a model of the
  information used in an enterprise, independent of
  all physical considerations
• Logical Database Design
• The process of constructing a model of the
  information used in an enterprise based on a
  specific data model, but independent of a
  particular DBMS and other physical
  considerations.
• Physical Database Design
• The process of producing a description of the
  implementation of the database on secondary
  storage it describes the storage structures and
  access methods used to archieve efficient access
  to the data

17
Database Language

• SQL
• 1974 - SEQUEL by D.Chamberlin (IBM)
• 1975 - SQUARE by Boyce (System R project)
• 1976 - SEQUEL/2 (SQL) by Chamberlin and Boyce)
• late 1970 - SQL(Oracle), QUEL(Ingres)
• 1982 - Relational Database Language(RDL) ANSI
• 1987 - ISO standard
• 1989 - Integrity Enhancement Feature (ISO)
• 1992 - SQL2(SQL92) ISO

18
Database Language

• DML
• SELECT
• INSERT
• UPDATE
• DELETE
• DDL
• CREATE(DROP) SCHEMA
• CREATE(ALTER, DROP) DOMAIN
• CREATE(ALTER, DROP) TABLE
• CREATE(DROP) VIEW
• CREATE(DROP) INDEX

19
Structured Query Language - SQL

• SQL
• View
• Integrity Enhancement Feature
• Primary key
• Unique
• Foreign key
• Access Control
• Embedded SQL
• Host Language Variables
• Application Programming Interface
• Dynamic SQL

20
Part 2. Understanding Oracle Database

• Overview of oracle Database Architecture
• Memory Structure
• Process Structure
• Storage Structure
• New Features

21
Overview of Oracle Architecture
D000
SMON
RECO
S000
PMON
P000


Total SGA Size
1700

Mbyte


Fixed Size
SGA

70
Kbyte

Variavle
Size

490
MByte
TL-812
KByte
KByte
KByte
KByte
4,000,000
KByte
2,100
1,200,000
LGWR
CKPT
DBW0
ARCH
Archive Log Mode(50M)
22
Memory Structure Shared Pool
Shared Pool

• Shared Pool Contents
• - Text of the SQL or PL/SQL statement
• - Parsed form of the SQL or PL/SQL statement
• - Execution plan for the SQL or PL/SQL
• statements
• - Data dictionary cache containing rows of data
• dictionary information
• Library Cache
• - shared SQL area
• - private SQL area
• - PL/SQL procedures and package
• - control structures lock and library cache
  handles
• Dictionary Cache
• - names of all tables and views in the database
• - names and datatypes of columns in database
  tables
• - privileges of all Oracle users
• SHARED_POOL_SIZE

Library Cache
Dictionary Cache
Control Structures for example Character
Set Conversion Memory Network Security
Attributes and so on ..
Reusable Runtime Memory
23
Memory Structure Database Buffer Cache

• Database Buffer Cache holds copies of data blocks
  read from disk
• All users concurrently connected to the system
  share access to the buffer cache
• Dirty List
• LRU List
• Size DB_BLOCK_SIZE DB_BLOCK_BUFFERS

24
Memory Structure Redo Log Buffer

• Circular buffer containing information about
  changes made to the database
• save it redo entry
• Redo Entries is used when Database Recovery
• DBWR write contents of Redo Log Buffer to Online
  Redo Log
• LOG_BUFFER

change vector 1
redo record
change vector 1
change vector 1
25
Oracle Processes
Dedicated Server Process
Snnn
26
Background Process

• DBWR (Database Writer)
• - write all dirty buffers to data files
• - Use a LRU algorithm to keep most recently
  used blocks in memory
• - Defers write for I/O optimization
• dirty list reaches a threshold length
• A process scans a specified number of buffer in
  the LRU without finding free buffer
• A time-out occurs
• DBWR checkpoint occurs
• LGWR (Log Writer)
• - writes redo log entries to disk
• Commit occurs
• The redo log buffers pool becomes one-third full
• DBWR completes cleaning the buffer blocks at a
  checkpoint
• LGWR time-out
• - A commit confirmation is not issued until
  the text has been recorded in the redo
• log file

27
Contd

• PMON (Process Monitor)
• - Cleans up abnormally terminated connection
• - Rolls back uncommitted transactions
• - Releases locks held by a terminated process
• - Frees SGA resources allocated to the failed
  processes
• - Database maintenance
• SMON (System Monitor)
• - Performs automatic instance recovery
• - Reclaims space used by temporary segments
  no longer in use
• - Merges contiguous area of free space in the
  datafile

28
Contd

• CKPT (Check Point)
• - is enabled by setting the parameter
  CHECKPOINT_PROCESSTRUE
• - If enabled, take over LGWRs task of
  updating files at a checkpoint
• - Updates header of data files and control
  files at the end of checkpoint
• - More frequent checkpoint reduce recovery
  time from instance failure
• - CKPT improve the performance of database
  with many database files
• ARCH (Archiver)
• - Copies redo log files to tape or disk for
  media failure
• - Operates only when a log switch occurs
• - Is optional and is only needed when in
  ARCHIVELOG mode
• - May write to a tape drive or to a disk
• LCKn (Lock), Dnnn (Dispatcher), Snnn (Server),
• RECO (Recover), Pnnn(Parallel), SNPn(Job
  Queue),
• QMNn(Queue Monitor),

29
Server/User Process

• User Processes
• - A user process is used when a user runs an
  application program
• - Runs the tool/application and is considered
  the client
• - Passes SQL to the server process and
  receives the results
• Server Processes
• - A server process must place the data in the
  database buffer cache
• - Parse and execute SQL statements
• - Read data blocks from disk into the shred
  database buffers of the SGA
• - Return the results of SQL statements to the
  user process
• Parse check syntax, security access, object
  resolution, optimization
• Execute applies the parse tree to the data,
  perform a physical read and change
• Fetch Passes data to the user (only SELECT)

30
Oracle Files

• Data file
• Redo Log Files
• Control Files
• Parameter File
• Archive File
• Log File (alert.log, sqlnet.log,
  listener.log...)
• Trace File

31
Storage Architecture

• Physical storage structures
• Data files
• Segments
• Extents
• Blocks
• Logical storage structures
• Tablespaces
• Tables / Clusters / Indexes
• Rows
• Columns

32
Physical Storage Architecture

• Relationship among Segments, Extents, and Blocks

33
Logical Storage Architecture

• Relationship between tablespaces and data files

34
Contd

• Objects stored in tablespaces

35
Block
General Block Information (Block add, Segment
type) 85 100 bytes Table info in
Cluster Row info in Block (2 byte per
row) using when New Row Insert
or Update (pctfree, pctused) Table or Index
Data
36
PCTFREE / PCTUSED
20 Free space
PCTFREE
PCTUSED
61 Free space
PCTUSED 40
PCTFREE 20

• Insert new row until 80
• 20 use when Update

• Can insert new row when below 60
• When Usage is below 40 (61 Free
• space), block is listed in FREELIST

37
Extent

• A set of contiguous database blocks within a data
  file.
• Extent are allocated when.
• - The segment is created (INITIAL EXTENT)
• - The segments grows (NEXT EXTENT)
• - The table is altered to allocate extents.
• Extent are de-allocated when the
• - The segment is dropped and truncated.
• - The segment is larger than optimal and
  contains free extents
• (for rollback segments only)
• Each segment is created with at least on extend(
  initial extent )
• ( Rollback segment 2)
• ALTER TABLE table_name DEALLOCATE UNUSED

38
Segment

• a set of one or more extents that contains all
  the data for a specific type of logical storage
  structure within a tablespace
• Data Segment
• - A collection of extents that holds all of
  the data for a table or a cluster
• Index Segment
• - A collection of extents that holds all of the
  index data for search optimization on large
  tables and clusters
• Rollback Segment
• - A collection of extents that holds rollback
  data for rollback, read-consistency, or recovery
• Temporary segment
• - A collection of extents that holds data
  belonging to temporary tables created during a
  sort operation
• Bootstrap segment
• - An extent that contains dictionary definitions
  for dictionary tables to be loaded when the
  database is opened.

39
Oracle Client/Server Architecture
NETWORK
Server b
Client Application
Server/Server
Client/Server
Server A

• Benefit of Client/Server Component
• - Database S/W work on Server
• - Minimize network resource
• - concurrency, consistency, transparency

- Only Server upgrade to increase size
- Minimize Client H/W spec - concurrency,
consistency, transparency
40
SQLNet

• What is SQLNet?
• - Oracles Client/Server middleware product
• - transparent connection from client tool to
  DB ( from on DB to another )
• - works across multiple network protocol and
  operation system
• What is TNS?
• - Transparent Network Substrate
• - Oracles Network applications to access
  the underlying network protocols transparently
• - TNS-based application, Oracle Protocol
  Adapters, Network software like TCP/IP
• Configuration File
• - TNSNAME.ORA ( Client )
• - TNSNAV.ORA ( Client )
• - SQLNET.ORA ( Client, Server )
• - LISTENER.ORA ( Server )

41
SQLNet Configuration

• TNSNAME.ORA
• info
• (DESCRIPTION
• (ADDRESS_LIST
• (ADDRESS
• (PROTOCOLtcp)
• (HOSTbrinfoa01)
• (PORT1521)
• )
• )
• (CONNECT_DATA
• (SIDBRBINFO1)
• )
• )

• LISTENER.ORA
• LISTENER
• (ADDRESS_LIST
• (ADDRESS
• (PROTOCOLtcp)
• (HOSTbrinfoa01)
• (PORT1521)
• )
• )
• SID_LIST_LISTENER
• (SID_LIST
• (SID_DESC
• (SID_NAMEBRBINFO1)
• (ORACLE_HOME/oracle7/oracle7)
• (ENVS'EPC_DISABLEDTRUE')
• )
• )
• STARTUP_WAIT_TIME_LISTENER0
• CONNECT_TIMEOUT_LISTENER0

• SQLNET.ORA
• SQLNET.EXPIRE_TIME 0
• SQLNET.AUTHENTICATION_SERVICES(none, beq)

42
Networking Challenge

• Networking Challenge
• Support large mission-critical client/server, and
  provide migration path towards distributed object
  architecture

• Focus
• 1. Scalability Connection Pooling,
  Multiplexing(Connection Manager)
• 2. Manageability Configuration-free
  installation option,
• Centralized client administration, Automated
  client configuration
• 3. Security Oracle Security Server

43
ODBC / oo4o / JDBC

• ODBC (Open Database Connectivity )
• - Provide a way for client program (eg VB,
  Excel, Access) to access database
• - is a standardized API, developed according
  to the specification of the SQL
• Access Group, than allows one to connect to
  SQL database
• oo4o (Oracle Object for OLE)
• - a middleware product manufactured by Oracle
  that allows native access to Oracle7
• databases from client applications via the
  Microsoft OLE standard
• - OLE 2.0 Automation Server, Oracle Data
  Control, Two C Class Library
• JDBC (Java Database Connectivity )
• - a set of classes and interfaces written in
  Java to allow other Java programs to send
• SQL statements to a relational database
  management system
• - JDBC Thin for Java applets, JDBC OCI for
  Java application