Transcript ADVANCED SQL AND PL/SQL TOPICS

Introduction to Oracle9i Database
Administration
Chapter 11
A Guide to Oracle9i
1
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
A Guide to Oracle9i
2
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 server’s 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
A Guide to Oracle9i
3
Overview of Database Administration
• Perform backup and recovery operations
• Install and upgrade developer client utilities (such as
Forms Builder and SQL*Plus) 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
A Guide to Oracle9i
4
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 multipleuser installations
• Personal Edition - provides a single-user DBMS for
developing database applications.
A Guide to Oracle9i
5
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
A Guide to Oracle9i
6
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
A Guide to Oracle9i
7
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
A Guide to Oracle9i
8
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
A Guide to Oracle9i
9
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 SQL*Plus, 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
A Guide to Oracle9i
10
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
A Guide to Oracle9i
11
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
A Guide to Oracle9i
12
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
A Guide to Oracle9i
13
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
A Guide to Oracle9i
14
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
A Guide to Oracle9i
15
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
A Guide to Oracle9i
16
Using OEM to Perform DBA Tasks
Oracle Enterprise Manager Architecture
A Guide to Oracle9i
17
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
A Guide to Oracle9i
18
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
A Guide to Oracle9i
19
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
A Guide to Oracle9i
20
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
A Guide to Oracle9i
21
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
A Guide to Oracle9i
22
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
A Guide to Oracle9i
23
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
A Guide to Oracle9i
24
Managing Oracle9i Data Storage
Data Block Components
A Guide to Oracle9i
25
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
A Guide to Oracle9i
26
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
A Guide to Oracle9i
27
The Oracle9i Database File
Architecture
Rollback Segment
A Guide to Oracle9i
28
Lesson B Objectives
• Create and manage user accounts
• Understand an Oracle9i database instance’s memory
areas and background processes
• Learn how to start and shut down the database
• Understand Oracle9i database backup and recovery
A Guide to Oracle9i
29
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
– User’s tablespace quota on the database server
A Guide to Oracle9i
30
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
A Guide to Oracle9i
31
Creating and Managing User
Accounts
• Tablespace Quota - A user’s tablespace quota
specifies
– Amount of disk space that the user’s 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
A Guide to Oracle9i
32
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 database’s tablespaces
A Guide to Oracle9i
33
Components of an Oracle9i
Database Instance
• An Oracle9i database instance creates two memory
areas in the database server’s 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
A Guide to Oracle9i
34
Components of an Oracle9i
Database Instance
Primary Memory Areas Within the System Global Area
A Guide to Oracle9i
35
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
A Guide to Oracle9i
36
Components of an Oracle9i
Database Instance
Memory Areas Within the Program Global Area
A Guide to Oracle9i
37
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
A Guide to Oracle9i
38
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
A Guide to Oracle9i
39
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
A Guide to Oracle9i
40
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
A Guide to Oracle9i
41
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
instance’s processes, and reallocates all server
memory
A Guide to Oracle9i
42
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
A Guide to Oracle9i
43
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 server’s main memory
• NOTE: When a DBA restarts a database instance,
the startup process performs these tasks in the
reverse order
A Guide to Oracle9i
44
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
A Guide to Oracle9i
45
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
A Guide to Oracle9i
46
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
A Guide to Oracle9i
47
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
A Guide to Oracle9i
48
Summary
• When creating a new user account, you must specify
general information about the user account, system
privileges, and the user’s 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
A Guide to Oracle9i
49
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
A Guide to Oracle9i
50