Introduction to Oracle9i Database Administration

1
Introduction to Oracle9i Database Administration

• Chapter 11

2
Lesson A Objectives

• Become familiar with database administration
  tasks
• Understand the process for installing an Oracle9i
  database, configuring client applications, and
  removing Oracle9i applications
• Learn how to use Oracle9i Enterprise Manager to
  perform database administration tasks
• Understand Oracle9i data storage structures
• Become familiar with Oracle9i database files

3
Overview of Database Administration

• DBA tasks include
• Install and upgrade the DBMS software on the
  server
• Optimize database performance by configuring how
  the database uses storage space in the servers
  main memory and file system
• Create and maintain user accounts to control
  database access
• Monitor data storage space and allocate
  additional storage space as needed
• Start and shut down the database to perform
  database maintenance tasks

4
Overview of Database Administration

• Perform backup and recovery operations
• Install and upgrade developer client utilities
  (such as Forms Builder and SQLPlus) on developer
  client workstations
• Deploy finished database applications to users
• Assist developers in designing and creating
  database tables
• Assist developers in designing and creating form
  and report components and integrated database
  applications
• Assist in testing and debugging new applications
• Assist in training developers and users

5
Installing an Oracle9i Database

• The three Oracle9i DBMS levels are
• Enterprise Edition - the most powerful DBMS, and
  is appropriate for installations that require a
  large number of transactions performed by
  multiple simultaneous users
• Standard Edition - sufficient for high volume
  multiple-user installations
• Personal Edition - provides a single-user DBMS
  for developing database applications.

6
Installing an Oracle9i Database

• Universal Installer utility
• Allows you to view the Oracle Corporation
  products that are installed on a workstation
• Guides you through the process of installing new
  products
• Allows you to uninstall Oracle9i applications

7
Installing an Oracle9i Database

• Universal Installer displays the following pages
  for the Oracle9i database installation
• File Locations page specifies the location of
  the application source files and the location and
  name of the Oracle Home to which you wish to
  install the application
• Available Products page displays a menu showing
  the different products available for installation
• Installation Types page displays installation
  options for the selected product

8
Installing an Oracle9i Database

• Database Configuration page specifies the
  properties of the preconfigured database that
  Universal Installer creates
• Database Identification page specifies the
  database name and instance name values that
  uniquely identify the database
• Database File Location page specifies the drive
  letter and folder path in which the database
  stores the files that contain the actual database
  data values

9
Installing An Oracle9i Database

• Database Character Set page specifies the
  character set or sets that the database uses to
  store character data
• Configuration Tools page configures properties
  of the predefined database and configures the
  database network properties
• End of Installation page signals the end of the
  installation process, and provides messages and
  other details about the installation process

10
Configuring Client Applications to Connect to an
Oracle9i Database

• To connect to an Oracle9i database, you type a
  connect string in the Host String field on the
  Log On dialog box in SQLPlus, or in the Database
  field in the Connect dialog box in Forms Builder
• The connect string provides Oracle Net the
  information it needs to create a connection
  between the client application and a specific
  database instance

11
Configuring Client Applications to Connect to an
Oracle9i Database

• To configure connect strings for client
  applications that connect to databases that are
  not on the same workstation as the database
  server, you use one of the following approaches
• Local naming
• Oracle Internet Directory

12
Configuring Client Applications to Connect to an
Oracle9i Database

• Local Naming - The local naming approach stores
  connect string and database connection
  information in a file named tnsnames.ora on the
  client workstation
• Oracle Net uses a connect descriptor to route a
  client data request to a specific Oracle9i DBMS.
  It specifies
• Network communication protocol
• IP address of the database server
• Database instance name

13
Configuring Client Applications to Connect to an
Oracle9i Database

• Oracle Internet Directory stores connect
  descriptor and service name information on a
  directory server called an Oracle Internet
  Directory (OID) server
• A directory server specifies the location of
  servers on a network
• An OID server uses the Lightweight Directory
  Access Protocol (LDAP) which is a standard
  protocol for configuring a directory server
• LDAP specifies that the directory server contains
  entries for each server process in a specific
  format

14
Configuring Client Applications to Connect to an
Oracle9i Database

• To use an Oracle Internet Directory server
• a client database application requests to connect
  to an Oracle9i database by contacting the OID
  server and specifying the LDAP server entry for
  the desired database, as shown in the next slide

15
Removing an Oracle9i Database

• You can use Universal Installer to remove
  Oracle9i applications from a server or
  workstation
• Universal Installer does not remove all elements
  from the registry
• To completely remove an Oracle9i database or
  client application from a workstation, you must
  perform the following operations
• Manually edit the system registry to remove all
  Oracle9i-related entries
• Restart the workstation
• Manually delete all Oracle9i-related files and
  folders

16
Using OEM to Perform DBA Tasks

• OEM Oracle Enterprise Manager a utility that
  allows DBAs to perform database administration
  support tasks
• Universal Installer installs OEM on the server or
  workstation when you install any Oracle9i
  database
• OEM has a three tier architecture as shown in the
  next slide

17
Using OEM to Perform DBA Tasks
Oracle Enterprise Manager Architecture
18
Using OEM to Perform DBA Tasks

• Console where the DBA performs administration
  tasks
• Oracle Management Server an Oracle9i database
  server that supports database administration
  tasks in an organization. The OMS interacts with
  the repository
• Repository contains information for remotely
  administering different databases
• The OMS then interacts with different database
  servers in Tier 3

19
Using OEM to Perform DBA Tasks

• Through OEM you access different database objects
  to perform various database administration tasks
• Database objects include instance, schema,
  security, storage, distributed, warehouse,
  workspace, XML database

20
Using OEM to Perform DBA Tasks

• Instance - provides information about the
  database instance, allows the DBA to edit
  instance information, and allows the DBA to start
  up and shut down the instance
• Schema - allows the DBA to examine and manipulate
  database objects (such as tables, sequences,
  indexes, and views) in all user schemas
• Security - allows the DBA to create and manage
  user accounts
• Storage - provides information about existing
  database storage structures and allows the DBA to
  create and configure storage structures

21
Using OEM to Perform DBA Tasks

• Distributed - allows the DBA to support
  applications and databases that are distributed
  across multiple database servers
• Warehouse - provides applications that allow DBAs
  and database developers to analyze database
  contents and look for long-term trends
• Workspace - allows DBAs and other users to view
  and maintain different versions of the same data
• XML Database - allows DBAs and database
  developers to create an XML database based on
  Oracle9i database tables

22
Managing Oracle9i Data Storage

• Data structure provides a framework to organize
  data that a computer stores
• Tables, lists, and arrays are examples of data
  structures
• Oracle9i data structures have a hierarchical
  relationship

23
Managing Oracle9i Data Storage

• Database Instance stores data in one or more
  tablespaces
• Tablespace - stores related database objects
• Segments - stores an individual database object,
  such as a table or an index
• Extent - a contiguous unit of storage space
  within a segment
• Data Block - smallest storage unit that the
  database can address. Extents consist of data
  blocks

24
Managing Oracle9i Data Storage

• Each Data Block consists of header, free space
  and row data
• Header - contains information about the data
  block contents, and is made up of three separate
  subsections the block header, the table
  directory, and the row directory
• Free space - is empty space that the block
  retains in case users update the data within the
  data block, and the updated data occupies more
  storage space than the original data
• Row Data stores actual data values

25
Managing Oracle9i Data Storage
Data Block Components
26
The Oracle9i Database File Architecture

• An Oracle9i database is made up of several
  different files that reside in the file system of
  the database server workstation. These files
  include
• Parameter files - initialize the database
  specifications and point to the locations of the
  database control file
• Control files - contain information about the
  database tablespaces, datafiles, redo log files,
  and the current state of the database

27
The Oracle9i Database File Architecture

• Datafiles contain the actual data values
• Redo log files - contain rollback information for
  uncommitted transactions
• An Oracle9i database stores rollback information
  in redo log files in a data structure called a
  rollback segment
• A rollback segment is made up of data blocks
  configured in a circular fashion
• As users perform action queries, the DBMS adds
  new rollback information to the rollback segment
  data blocks

28
The Oracle9i Database File Architecture
Rollback Segment
29
Lesson B Objectives

• Create and manage user accounts
• Understand an Oracle9i database instances memory
  areas and background processes
• Learn how to start and shut down the database
• Understand Oracle9i database backup and recovery

30
Creating and Managing User Accounts

• When you create a new user account you must
  specify the following
• Username, Password, default tablespace and
  temporary tablespace
• System privileges the user has in the database
• Users tablespace quota on the database server

31
Creating and Managing User Accounts

• System Privilege - allows a user to perform a
  specific task with the Oracle9i database, such as
  connecting to the database or creating a new
  table
• Object Privilege - allows a user to perform a
  specific action on a database object, such as
  selecting data from a table or retrieving a value
  from a sequence

32
Creating and Managing User Accounts

• Tablespace Quota - A users tablespace quota
  specifies
• Amount of disk space that the users database
  objects can occupy in his or her default
  tablespace
• A role is a database object that represents a
  collection of system privileges that you can
  assign to multiple users

33
Components of an Oracle9i Database Instance

• In an Oracle9i client/server database, a client
  process that runs on the client workstation
  connects to a database instance that runs on the
  database server
• The link between the client process and the
  database instance is called a connection, and it
  creates a user session
• A database instance consists of a set of
  processes and associated memory structures that
  manipulate data in a databases tablespaces

34
Components of an Oracle9i Database Instance

• An Oracle9i database instance creates two memory
  areas in the database servers main memory
• System Global Area
• Program Global Area
• The System Global Area (SGA) is a memory area
  that all database connections use. The purpose of
  the SGA is to share information among all
  database processes. The SGA is made up of five
  primary memory areas shared pool, database
  buffer cache, large pool, redo log buffer, Java
  Pool

35
Components of an Oracle9i Database Instance
Primary Memory Areas Within the System Global Area
36
Components of an Oracle9i Database Instance

• The Program Global Area (PGA) is a memory area
  that stores information for a specific user
  connection. The PGA contains two separate memory
  areas session information and stack space
• The session information area contains information
  about the user session, such as the username,
  time the session started, date of the last
  activity, and global variable values
• The stack space contains the values of the
  variables that the user declares in PL/SQL
  programs and other programs

37
Components of an Oracle9i Database Instance
Memory Areas Within the Program Global
Area
38
Components of an Oracle9i Database Instance

• An Oracle9i database instance contains a set of
  background processes to service user requests
• The Oracle9i background processes include DBWn,
  LGWR, SMON, PMON, and CKPT
• DBWn - writes changed data from the database
  buffer cache to the datafiles
• LGWR - writes redo information from the redo log
  buffer to the redo log files

39
Components of an Oracle9i Database Instance

• SMON - has three primary functions
• Recover lost data after a system hardware or
  software failure
• Deallocate temporary memory areas that the
  database uses for sort operations
• Manage server disk space by periodically
  coalescing free space to make larger continuous
  extents for new data
• PMON - monitors and manages individual user
  sessions
• CKPT - responsible for initiating checkpoints. A
  checkpoint signals the DBWn and LGWR processes to
  write the buffer contents to the datafiles and
  redo log files

40
Starting and Shutting Down the Database

• Database administrators must shut down a database
  periodically to perform maintenance tasks such
  as
• Backing up the database
• Adding new datafiles
• Modifying the properties of the parameter file
• Recovering from a database crash
• After the DBA performs the maintenance activity,
  he or she restarts the database to make it
  available for new user connections

41
Starting and Shutting Down the Database

• When a DBA shuts down an Oracle9i database
  instance, the DBA has four different options for
  how the shutdown process handles existing user
  connections
• Normal
• Transactional
• Immediate
• Abort

42
Starting and Shutting Down the Database

• Normal - instance does not accept any new
  connections, but allows current users to finish
  their transactions and log off normally
• Transactional - instance does not accept any new
  connections, and allows users to finish their
  current transaction
• Immediate - instance does not accept any new user
  connections, and immediately terminates current
  user connections
• Abort - immediately disconnects all users, stops
  the instances processes, and reallocates all
  server memory

43
Starting and Shutting Down the Database

• When a DBA starts an Oracle9i database instance,
  he or she can start it in one of three modes
• Unrestricted allows all users to create
  connections
• Restricted creates connections only for users
  who have the RESTRICTED SESSION system privilege
• Read-only allows users to read database
  contents, but does not allow users to perform
  action queries that modify database contents

44
Starting and Shutting Down the Database

• When a DBA shuts down a database instance using
  the Normal, Transactional, or Immediate shutdown
  option, the shutdown process performs the
  following tasks
• Writes the contents of the data buffer cache to
  the datafiles
• Writes the contents of the redo log buffer to the
  redo log files
• Closes all files
• Stop all background processes
• Deallocates the SGA in the servers main memory
• NOTE When a DBA restarts a database instance,
  the startup process performs these tasks in the
  reverse order

45
Database Backup and Recovery

• Important database administration support tasks
  include
• Backup creating a copy of the database files
• Recovery restoring the database to a working
  state after a hardware or software malfunction
• Backup operations on an Oracle9i database can be
  classified as
• Offline (cold) backups
• Online (hot) backups

46
Database Backup and Recovery

• Offline Backup (cold backup) requires shutting
  down the database, then copying all of the
  database files to an alternate location
• Online Backup (hot backup) involves backing up
  critical database files while the instance is
  running, as well as creating an ongoing archive
  of database changes so the DBA can restore the
  database to its state at any point in time

47
Summary

• Database administration involves installing,
  configuring, maintaining, and troubleshooting a
  database
• Oracle9i database exists at three levels
  Enterprise Edition, Standard Edition, and
  Personal Edition
• Universal Installer pages guide you through the
  installation process
• You can use local naming to configure client
  applications to connect to an Oracle9i database
• OEM allows DBAs to perform database
  administration support tasks

48
Summary

• A database instance stores data in one or more
  tablespaces
• Parameter file specifies configuration
  information about an Oracle9i database instance
• Control files store information about the
  database structure and state
• Redo log files record information to undo action
  query changes through user rollback operations
• An Oracle9i database stores the rollback
  information in a rollback segment

49
Summary

• When creating a new user account, you must
  specify general information about the user
  account, system privileges, and the users
  tablespace quota
• System privileges allow users to perform specific
  tasks with the Oracle9i database
• A role represents a collection of system
  privileges that you can assign to multiple users

50
Summary

• An Oracle9i database instance creates the System
  Global Area (SGA) and the Program Global Area
  (PGA)
• DBA can start an Oracle9i database instance in
  unrestricted, restricted or read-only mode
• Backup involves creating a copy of the database
  files, and recovery involves restoring the
  database to a working state after a hardware or
  software malfunction