DBM 420

1
DBM 420

• Enterprise Data Management Systems
• Workshop One
• Richard Perry

2
Database Overview

• What is a
• Database?
• DBMS?
• Four types of Databases
• Hierarchical
• Network
• Object Oriented
• Relational

3
What Does a Database Administrator Do?

• Advise on Product Selection and Implementation
• Perform Physical and Logical Database Design
• Perform Software Installations and Upgrades
• Perform Problem Troubleshooting and Root Cause
  Failure Analysis

4
DBA (cont.)

• Perform Emergency and Normal Database Maintenance
• Patches
• Database Reorganizations
• Perform Database Tuning
• Project Database Growth
• Develop, Implement, and Execute Backup and
  Recovery Process

5
DBA (cont.)

• Develop, Implement, and Execute Disaster Recovery
  Plans
• Perform Data Migrations
• Advise Database Analysts and Programmers
• Learn and Understand Emerging Database
  Technologies and the Impact on the Organization

6
From This Point Forward All Discussion Is Oracle
7
Oracle 8i Architecture Components

• What is the relationship between an Oracle
• Server
• Instance
• Database in an Oracle 8i environment?

8
(No Transcript)
9
Oracle Server

• Object-Relational DBMS
• Consists of an Oracle Database and an Oracle
  Server Instance
• Runs on a host system

10
Oracle Instance

• Consists of Background Processes
• User - Executes application program code
• Oracle - Server processes that perform work for
  the user processes
• and Memory Buffers (SGA)
• Parallel Server option runs multiple instances
  that share a single physical database.

11
Oracle Database (Logical)

• Consists of
• Tablespaces
• USERS
• SYSTEM
• Schema Objects
• Tables
• Views
• Stored Procedures
• Synonyms
• Indexes
• Clusters
• Database links

• Data Blocks
• Extents
• Segments
• data
• index
• rollback
• temporary

12
Oracle Database (Physical)

• Consists of
• Datafiles
• Contain logical database structures
• Redo Log Files
• Records all changes made to data
• Multiplexed
• Used during database recovery

• Control Files
• Specifies physical structure of the database
• Database Name
• Names and locations of
• datafiles
• redo log files
• Timestamp of database creation

13
Oracle SQL

• SQL is the programming language that defines and
  manipulates the database.
• Data Manipulation Language (DML) commands
• Data Definition Language (DDL) commands

14
Oracle DML

• Perform CRUD on data
• Create
• Read
• SELECT
• Most Common
• Update
• Delete

15
Oracle DDL

• Sets up the data structures (schema)
• Create
• Update
• Delete
• Most often used by Database Administrator

16
Oracle Administration Tools

• Oracle 8i Administrative Utilities
• SQLPLUS
• DBA Studio
• SQLPlus Worksheet
• Import, Export, SQLLoader
• Oracle Enterprise Manager (OEM)
• Management Packs
• Wizards

17
Management of Oracle Instance

• Authentication
• Privileges
• Parameter Files
• How an Instance Processes Through Startup and
  Shutdown

• Dynamic Performance Views
• Dynamic Parameters
• Trace and Alert Files

18
Oracle Security Features

• Prevents unauthorized database access
• Prevents unauthorized access to schema objects
• Controls disk usage
• Controls system resource use
• Audits user actions

• System Security
• user name
• password
• resource limits
• Data Security
• Which users have access to specific schema
  objects
• Audit actions on schema objects

19
Oracle Privileges

• System Privileges
• System wide actions
• Usually available only to database administrators
  and developers
• Schema Object Privileges
• Allows users to perform specific actions on
  specific objects

• Roles
• Named groups of related privileges granted to
  users.

20
Oracle Start Up and Shut Down

• Start the instance of an Oracle server
• Mount the database
• Open the database

• Close the database
• Unmount the database
• Shut down the instance of the Oracle server

21
Oracle Parameter File

• Database Name
• Memory Allocation
• Names of Control Files
• Various Limits
• System Parameters

22
Oracle Dynamic Performance Views

• Set of virtual tables that record database
  activity
• Not true tables
• Names begin with V
• Not generally accessed by users
• Database administrators can modify some dynamic
  parameters

23
Trace and Alert Files

• Each server and background process has an
  associated trace file.
• Information about internal errors are written to
  the trace file.
• Alert file is a chronological log of all messages
  and errors.
• Internal Errors
• Block Corruption
• Deadlock

24
Wrap-Up

• Questions?
• Project Discussion
• Mission Critical Application description
• Next Weeks Assignment
• 63311- Manage an Instance
• 63312 - Create a Database
• Paper - Oracle Data Dictionary