1 / 43

International Technology University Lecture 1 Overview of Oracle Database

International Technology University Lecture 1 Overview of Oracle Database. Part 1. Introduction to Database System. Introduction to Database History of RDBMS Entity-Relationship Modeling Database Language. Introduction to Database. File-Based Approach

nijole
Download Presentation

International Technology University Lecture 1 Overview of Oracle Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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. Redo Log Buffer Shared SQL Area Database Buffer Cache Server Data File Raw Device USER 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 4,000,000 KByte KByte KByte KByte KByte 2,100 1,200,000 CKPT LGWR DBW0 ARCH Archive Log Mode(50M)

  22. Shared SQL Area PL/SQL Procedures and Package Control Structures for examples; Locks Library Cache handles and so on ... 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 Dictionary Cache Library Cache Control Structures for example: Character Set Conversion Memory Network Security Attributes and so on .. Reusable Runtime Memory

  23. SGA Database Buffer Cache Shared Pool Shared SQL Area 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. PMON SMON RECO LCK0 SNPn Pnnn SGA Database Buffer Cache Redo Log Buffer Offline Storage Device DBWR LGWR ARCH Dnnn User Process CKPT Control Files Users Redo Log Files Data Files 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. Cont’d • 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. Cont’d • CKPT (Check Point) - is enabled by setting the parameter CHECKPOINT_PROCESS=TRUE - If enabled, take over LGWR’s 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. Segment 96K Extent 24K Extent 72K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K 2K Database Blocks Physical Storage Architecture • Relationship among Segments, Extents, and Blocks

  33. Database USER Tablespace System Tablespace DATA1.ORA DATA2.ORA DATA3.ORA Logical Storage Architecture • Relationship between tablespaces and data files

  34. Tablespace(one or more data files) Table Table INDEX INDEX INDEX INDEX INDEX INDEX INDEX Table INDEX INDEX INDEX Database Files (Physical structures associated with only one tablespace) Objects (stored in tablespace may span several data files) Cont’d • Objects stored in tablespaces

  35. Header Table Dictionary Row Dictionary Free Space Row Data 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. SQL*Net • What is SQL*Net? - Oracle’s 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 - Oracle’s 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. SQL*Net Configuration • LISTENER.ORA LISTENER= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=brinfoa01) (PORT=1521) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=BRBINFO1) (ORACLE_HOME=/oracle7/oracle7) (ENVS='EPC_DISABLED=TRUE') ) ) STARTUP_WAIT_TIME_LISTENER=0 CONNECT_TIMEOUT_LISTENER=0 LOG_DIRECTORY_LISTENER=/oracle7/oracle7/network/log LOG_FILE_LISTENER=listener TRACE_LEVEL_LISTENER=OFF • TNSNAME.ORA info = (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=brinfoa01) (PORT=1521) ) ) (CONNECT_DATA= (SID=BRBINFO1) ) ) • 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

More Related