1 / 50

IT 21003 Database Administration

IT 21003 Database Administration. This course is a study of the duties and responsibilities of a database administrator. This course covers setting up, maintaining, and troubleshooting a distributed multi-user database. We will utilize Oracle9i for our relational database.

hagen
Download Presentation

IT 21003 Database Administration

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

E N D

Presentation Transcript

  1. IT 21003 Database Administration

  2. This course is a study of the duties and responsibilities of a database administrator. This course covers setting up, maintaining, and troubleshooting a distributed multi-user database. We will utilize Oracle9i for our relational database.

  3. COURSEOBJECTIVE/LEARNING OUTCOMES: Understand the basic concepts of an ORACLE Database Administrator COURSE TOPIC OUTLINE/PURPOSE: To develop the next step in building computer competencies for a career in ORACLE Database field.

  4. Database Humor

  5. Database Humor

  6. IT Humor

  7. IT Humor

  8. Student Humor

  9. One day a college professor was greeting his new college class. He stood up in front of the class and asked if anyone in the class was a moron, and if they were, they should stand. After a minute a young man stood up. The professor then asked thekid if he actually thought he was a moron. The kid replied, 'No, I just didn't want to see you standing there all by yourself' Student Humor

  10. Software Humor

  11. Database ConceptsReview

  12. Database Management Systems (DBMS) • Software that is designed to simplify and improve data management • Supports • Methods of database access • Manages user transactions • Establishes data security • Enforces integrity • Optimizes performance • Controls concurrent data access

  13. Relational Database Management Systems (RDBMS) • A DBMS that supports data organization based on the relational model • Structure of the database is tabular • Tables are related to one another through common columns • For example, subject_area and course are two tables that are related by subject_id • Oracle developed the first commercial RDBMS in 1979

  14. Structured Query Language (SQL) • SQL is the standard language for relational databases • Users issue SQL commands to access and manage data in a database • SQL is essentially a nonprocedural language • SQL functionality has evolved significantly over the years • 1979: First Oracle release • First commercial RDBMS

  15. Logical Data Model • Provides a level of abstraction from physical database design by representing data in terms of “logical” or business entities and the relationships between them • Represents business information and rules • Provides input to physical database design • Composed of three critical elements • Entities • Attributes • Relationships

  16. Student represents Sends for training Company Entity • An object of importance • The object may be a tangible item such as a student or an abstract concept such as attendance • Represented as a soft box • Example:

  17. Attribute • A fact that is a nondecomposable unit of information about an entity • Qualify, identify, classify, quantify, or express the state of an entity • Example: • Student id, last name, and first name are attributes of the student entity

  18. What is ORACLE? • ORACLE is a Relational Database Management System (DBMS) • Market share is 65-75% • 3 out of 4 Fortune 100 companies run their enterprise applications on Oracle • Number one DBMS in the world ORACLE 9i

  19. ORACLE Database Administrator • Duties: • Database Creation • Database Maintenance • Backup/Repair • Installing New Releases • Assessment of Database Needs • Reports to CTO, ITO ORACLE DBA

  20. ORACLE DBA JOBS • Over 5000 job openings currently listed at online job search engines • Highest demand in IT is for Oracle

  21. Data Types, Pseudocolumns, and Functions • NUMBER Data Type • Used to store fixed or floating-point numbers • Syntax: • NUMBER [(Precision, Scale)] • Guaranteed to default to 38 digits • Precision • Total number of significant digits • Optional, defaults to the maximum supported by the operating system • Scale • Number of digits to the right of the decimal point • Can range from –84 to 127 • Optional, defaults to zero

  22. Data Types, Pseudocolumns, and Functions • NUMBER Data Type Examples • NUMBER • Maximum digits allowed by the system before or after the decimal point • Number (2) • Two digits before the decimal lace and zero digits after • 99 • Number (3,2) • One digit before the decimal place and two digits after • 9.99

  23. Data Types, Pseudocolumns, and Functions • CHAR Data Types • CHAR is used to store fixed-length character data • Syntax: • CHAR [(length)] • Rarely used, for small codes • y/n is good use of this type • Length • Maximum 2,000 bytes • Optional, defaults to 1 • Values are padded with blanks to the maximum length • Example: • CHAR stores one character • CHAR (10) stores 10 characters for a value of any length • Storing “Mike” in this data type would result in MIKE and six blanks

  24. Data Types, Pseudocolumns, and Functions • VARCHAR2 Data Type • Used to Store variable-length character data • Syntax: • VARCHAR2 (length) • Length • Maximum 4,000 bytes • Mandatory that a length is specified • Values are not padded; exactly the length of the string is stored • Example: • VARCHAR2 (10) stores up to 10 characters based on the actual string • Storing “MIKE” in this data type would not store extra characters or blanks

  25. Data Types, Pseudocolumns, and Functions • DATE Data Type • Used to store date and time to the precision of seconds • Syntax: • DATE • Stores date values from Jan. 1, 4712 BC, to Dec. 31, 4712 AD • Stored internally as a number using seven bytes default format mask • Retrieved and updated based on a • Default format mask is DD-MON-YY • Changed by setting NLS_DATE_FORMAT parameter • TIME default is HH:MN:SE

  26. Data Types, Pseudocolumns, and Functions • LOB Data Type • There are three LOB or Large OBject Data Types • CLOB is used to store long variable-length character strings • BLOB is used to store binary large objects, such as bitmaps or audio or video images • BFILE is a database reference to an operating system file that is stored outside of Oracle • LOB data types can contain up to 4 GB of data • Handling LOBS is a complex subject

  27. SubQueries • Increase the functionality of SELECT statements by allowing interrogation of tables that are not used in the main SELECT statement • A SELECT statement that occurs inside a condition of another SELECT statement • Each row of the parent statement is compared with the result of the subquery • If the comparison fails, the row is rejected

  28. SubQueries • Subquery Syntax and Rules: • Same as the regular SELECT statement without the ORDER BY clause • A subquery can have its own subqueries • Normally used in the WHERE clause of the parent SELECT statement • Common comparison operators for a subquery • Relational operators (=, <>, >, >=, <, <=) • IN and NOT IN • EXISTS and NOT EXISTS

  29. Inserting, Updating, and Deleting Data • Adding Data • Data is added to a table using the INSERT statement • The INSERT statement can operate in two modes • Adding exactly one row • Using the VALUES clause of the INSERT statement • Copying zero or more rows • Using the subquery clause of the INSERT statement

  30. Building Databases • Schema • Collection of logical structures (objects) • Owned by a database user and has the same name as that user • Schema objects are created and manipulated with SQL statements.

  31. Building Databases • Object Ownership • Objects created by the user belong to that user’s schema • They have full control • Other users can access these objects only if the owner grants access • Even if access is granted, other users must prefix objects with owner’s name unless public synonyms are created

  32. Building Databases • Building Tables • Use the CREATE TABLE command • Simplified Syntax: • CREATE TABLE table_name (column_name datatype [default default_value] [NOT NULL],... (column_name datatype [default default_value] [NOT NULL] );

  33. Creating Objects • Object Types • Oracle uses the power of an RDBMS and extends it to include objects • A type in the database for use in other structures • Does not result in storage allocation • Syntax: • CREATE [OR REPLACE] TYPE object_type_name AS OBJECT (attribute_name datatype, attribute_name datatype) • Attributes are defined the same way as columns in tables • Must be unique within an object type • Normally, alphanumeric strings with a maximum of 30 characters

  34. Enforcing Integrity • Integrity Constraints • Declarative method of enforcing business rules in the database • Defined with the corresponding table using the CREATE TABLE or ALTER TABLE command • Validated when constraints are defined and on each INSERT, UPDATE and DELETE • Five Types: • NOT NULL • Primary Key • Unique Key • Foreign Key • Check

  35. Establishing Security • Database Authentication • Verification and management of database users and their passwords • Users and their passwords are stored in the data dictionary • To prevent unauthorized access, the password is validated during a connect • Once connected, a user’s ability to view, create, and modify data is controlled via other security features

  36. Establishing Security • Changing User Password • Users should change their passwords regularly to avoid unauthorized access • Note: DBAs can’t see passwords • Syntax: • ALTER USER user_name IDENTIFIED BY password; • The password must conform to Oracle’s naming restrictions • Maximum of 30 characters • Must start with a letter • Can include special characters such as $, #, and _ • Not case sensitive

  37. Establishing Security • Granting Privileges without Roles Users Privileges

  38. Establishing Security • Granting Privileges with Roles Users Roles Privileges

  39. Improving Performance • Index • A structure that is defined for a table to provide faster access to table data • Analogous to a book index • To find a particular row in a table, the database engine searches the index, which identifies the row with a pointer • A book usually contains one index; a table can contain any number of independent indexes • An indexed search is much faster than a table search because an index’s internal structure is designed to improve performance • Oracle maintains a balanced B-tree structure for indexes

  40. Improving Performance • Guidelines for Index Creation • Create primary and unique key constraints • Oracle will automatically generate all unique indexes • Review all the queries in the application • If all queries use existing unique indexes, then do not create any other indexes • If all queries do not us the unique indexes, determine whether they reference multiple columns • If so, create composite indexes that contain those columns • If there are still queries that do not use an index, determine whether those columns return fewer than 15% of the rows in a table • If so, create single-column indexes • Do Not Add Indexes on Small Tables

  41. Oracle DBA • The Role of the DBA • Responsibilities: • Install and Upgrade Oracle Products • Create Oracle Databases • Allocate and Plan Disk Storage • Set-Up Users • Modify Database Structures • Develop Backup and Recovery Strategies • Maintain Security • Monitor and Optimize Performance • Select Software and Handle Licensing Issues • Act as the Principle Oracle Support Contact

  42. Oracle Instances and Database Concepts • Oracle’s Memory and Process Architecture Redo Log Buffer Shared Pool Database Buffer Cache System Global Area Oracle Instance

  43. Oracle Instances and Database Concepts • Oracle’s Memory and Process Architecture Database Writer System Monitor Process Monitor Recoverer Log Writer Archiver CKPT Dedicated Server Process Background Processes Shadow Processes User Process Run on Client Machines

  44. Oracle Instances and Database Concepts • Background Processes: Summary

  45. Oracle Instances and Database Concepts • Oracle’s Memory and Process Architecture Database Offline Storage Files Redo Log File 2 Rollback Segment Redo Log File 1 Data Segment Control File(s) DATABASE

  46. Oracle Instances and Database Concepts • Oracle Database – The Big Picture • ORACLE ARCHITECTURE “INSTANCE” “DATABASE” also known as ORACLE_SID also known as DB_NAME Composed of Physical Disk Files Composed of Memory Structures Data Files Redo Log Files Control Files System Global Area Background Processes Other Memory Structures Other Storage Structures ORACLE_SID = DB_NAME

  47. Oracle Instances and Database Concepts • Oracle Instances vs. Oracle Databases • Oracle Instance • Memory structure used for database management • Independent of the database – in memory • Consists of the System Global Area (SGA) and the Oracle Background Processes • Can mount (attach to) only one database at a time • Uniquely defined as ORACLE_SID or ORA_SID (OpenVMS only) • Oracle Database • One or more Data Files • Two or more Online Redo Log Files • One or more Control Files • Should have at least two Control Files on separate disks • Provides the actual physical storage of the database data

  48. Oracle Instances and Database Concepts • Oracle Instances vs. Oracle Databases cont’d • A Database is mounted by one or more Instances • Data is accessible only when the database is open • Uniquely defined as DB_NAME in init.ora (startup parameter file)

  49. Oracle Instances and Database Concepts • Instance-Database Relationship Parallel Server Architecture, multiple Instances (Clustered Database) Exclusive Database Instance e.g. SID=ORA1 Instance 1 e.g. SID = ORA1 Instance 2 e.g. SID = ORA2 Shared Database e.g. DB_NAME = ORA1 Exclusive Database e.g. DB_NAME = ORA1

  50. END REVIEW

More Related