Oracle® Database
2 Day DBA
10g Release 1 (10.1)
Part No. B10742-03
June 2004
Oracle Database 2 Day DBA, 10g Release 1 (10.1)
Part No. B10742-03
Copyright © 2004, Oracle. All rights reserved.
Contributing Authors: Colin McGregor, Ruth Baylis, Sushil Kumar, Antonio Romero, David Austin, and
Michele Cyran
Contributors: Daniela Hansell, Donna Keesling, Vasudha Krishnaswamy, Peter Laquerre, Tony Morales,
Venkat Maddali, Mughees Minhas, JP Polk, Mark Townsend, and Wanli Yang
The Programs (which include both the software and documentation) contain proprietary information; they
are provided under a license agreement containing restrictions on use and disclosure and are also protected
by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly,
or decompilation of the Programs, except to the extent required to obtain interoperability with other
independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems in
the documentation, please report them to us in writing. This document is not warranted to be error-free.
Except as may be expressly permitted in your license agreement for these Programs, no part of these
Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any
purpose.
If the Programs are delivered to the United States Government or anyone licensing or using the Programs on
behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data
delivered to U.S. Government customers are "commercial computer software" or "commercial technical
data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental
regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including
documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable
Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19,
Commercial Computer Software Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway,

Tools for Administering the Database 1-2
2 Installing Oracle and Building the Database
Overview of Installation 2-1
Checking Prerequisites 2-1
Installation Choices 2-2
Basic Installation Choices 2-2
Advanced Installation Choices 2-3
Installing Oracle Software and Database 2-4
Using DBCA to Create and Configure a Database 2-7
Starting DBCA 2-7
Using DBCA to Create a Database 2-8
Database Templates 2-8
Database Identification 2-8
Management Options 2-8
Database Credentials 2-9
Storage Options 2-9
Database File Locations 2-9
Recovery Configuration 2-9
Database Content 2-10
iv
Initialization Parameters 2-10
Database Storage 2-12
Creation Options 2-12
Using DBCA to Delete a Database 2-12
Using DBCA to Configure Database Options 2-12
Managing Templates with DBCA 2-12
Advantages of Using Templates 2-12
Types of Templates 2-13
DBCA Templates Provided by Oracle 2-13
Creating Templates Using DBCA 2-14

Oracle Net Listener Configuration 4-1
Client Configuration 4-2
Connection Requests 4-2
Naming Methods 4-2
Local Naming 4-3
v
Directory Naming 4-3
Easy Connect Naming 4-3
External Naming 4-3
Tools for Network Configuration 4-3
Oracle Net Configuration Assistant 4-3
Enterprise Manager 4-3
Oracle Net Manager 4-4
Viewing Listener Configuration 4-4
Starting and Shutting Down the Listener 4-5
Configuring Oracle Networking on Client Machines 4-5
Networking: Oracle by Example Series 4-6
5 Managing the Oracle Instance
Overview of an Instance and Instance Management 5-1
Instance Memory Structure 5-2
The System Global Area (SGA) 5-2
Program Global Area (PGA) 5-3
Oracle Background Processes 5-3
Accessing the Database 5-4
How the Oracle Instance and Database are Started 5-4
Server and Client Processes 5-5
Network Connections 5-5
About Initialization Parameters 5-6
Shutting Down and Restarting the Instance and Database 5-6
Starting and Shutting Down the Database Instance on Windows 5-7

About Undo 6-12
Undo Tablespace Size and Retention Time 6-12
Automatic Undo Management 6-13
Undo Management with Enterprise Manager 6-13
Using Oracle Recommendations for Managing Undo 6-14
Extending the Undo Tablespace 6-14
Using the Undo Advisor 6-14
Setting New Threshold Undo Retention Time 6-15
Making Changes to the Database Storage Structure 6-15
Storage: Oracle by Example Series 6-16
7 Administering Users and Security
Overview of Users and Security 7-1
Database Administrative Accounts 7-3
SYS 7-3
SYSTEM 7-4
Administrative Privileges 7-4
Administering Roles 7-4
Viewing Roles 7-5
Creating a New Role 7-6
Modifying Roles 7-6
Dropping Roles 7-7
Administering Profiles 7-7
Administering Database Users 7-7
Creating Users 7-8
Using a Shortcut to Create Users 7-10
Editing Users 7-10
Unlocking Accounts and Resetting Passwords 7-10
Granting Roles 7-11
Revoking Roles 7-11
Dropping Users 7-12

Managing Views 8-16
View Creation Example 8-17
Managing Database Resident Program Units 8-17
Managing Packages 8-18
Managing Package Bodies 8-19
Managing Standalone Subprograms 8-19
Managing Triggers 8-19
Other Schema Objects 8-20
Loading Data Into Tables 8-21
Managing Access to Schema Objects 8-23
Some Schemas of Interest 8-24
SYS Schema 8-24
SYSTEM Schema 8-24
Sample Schemas 8-24
Schemas: Oracle by Example Series 8-25
9 Performing Backup and Recovery
Overview of Oracle Backup and Recovery 9-1
Oracle Backup, Restore and Recovery Concepts 9-2
Consistent and Inconsistent Backups 9-2
Media Recovery 9-3
The RMAN Repository 9-3
The Flash Recovery Area 9-3
Configuring Your Database for Basic Backup and Recovery 9-4
viii
Planning Space Usage and Location for the Flash Recovery Area 9-4
Retention Policy and the Flash Recovery Area 9-4
Sizing the Flash Recovery Area 9-5
Credentials for Performing Oracle Enterprise Manager Backup and Recovery 9-6
Configuring the Flash Recovery Area and ARCHIVELOG Mode 9-6
Configuring Backup Settings 9-7

Proactive Database Monitoring 10-1
Alerts 10-1
Performance Self-Diagnostics: Automatic Database Diagnostics Monitor 10-2
Monitoring General Database State and Workload 10-2
Managing Alerts 10-3
Viewing Metrics and Thresholds 10-3
Setting Metric Thresholds 10-5
Setting up Notification 10-6
Responding to Alerts 10-7
Clearing Alerts 10-7
ix
Diagnosing Performance Problems 10-7
Viewing Performance Analysis 10-8
Responding to Performance Findings 10-8
Modifying Default ADDM Behavior 10-8
Running ADDM Manually 10-9
Using Advisors to Optimize Database Performance 10-9
About Performance Advisors 10-10
Using the SQL Tuning Advisor 10-11
Using the SQL Access Advisor 10-12
Using the Memory Advisor 10-12
Monitoring and Tuning: Oracle by Example Series 10-13
Index
x
xi
Send Us Your Comments
Oracle Database 2 Day DBA, 10g Release 1 (10.1)
Part No. B10742-03
Oracle welcomes your comments and suggestions on the quality and usefulness of this
publication. Your input is an important part of the information used for revision.

Oracle Database 2 Day DBA is for anyone who wants to perform common day-to-day
administrative tasks with the Oracle Database. Prior knowledge or experience with
managing databases is not required. The only requirement is a basic knowledge of
computers.
In particular this book is targeted toward the following groups of Oracle users:
■ Developers wanting to acquire part-time DBA skills
■ Anyone managing departmental servers
■ Database administrators managing an Oracle database for small or medium
business (SMB)
This book is equally useful for enterprise DBAs. It provides recommended best
practices and describes efficient ways of performing administrative tasks with Oracle
Enterprise Manager as the primary interface.
Documentation Accessibility
Our goal is to make Oracle products, services, and supporting documentation
accessible, with good usability, to the disabled community. To that end, our
documentation includes features that make information available to users of assistive
technology. This documentation is available in HTML format, and contains markup to
facilitate access by the disabled community. Standards will continue to evolve over
time, and Oracle is actively engaged with other market-leading technology vendors to
address technical obstacles so that our documentation can be accessible to all of our
customers. For additional information, visit the Oracle Accessibility Program Web site
at
/>xiv
Accessibility of Code Examples in Documentation
JAWS, a Windows screen reader, may not always correctly read the code examples in
this document. The conventions for writing code require that closing braces should
appear on an otherwise empty line; however, JAWS may not always read a line of text
that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or

xv
Related Documentation
For more information, see these Oracle resources:
■ Oracle Database Administrator's Guide
■ Oracle Enterprise Manager Advanced Configuration
■ Oracle Database Concepts
■ Oracle Enterprise Manager Concepts
■ Oracle Net Services Administrator's Guide
■ Oracle Database SQL Reference
■ Oracle Database Reference
■ Oracle Database Backup and Recovery Basics
■ Oracle Database Backup and Recovery Advanced User's Guide
■ Oracle Database Installation Guide for Windows
■ Oracle Database 10g Installation Guide, Release 1 (10.1) Beta for UNIX Systems:
AIX-Based Systems, HP-UX, HP Tru64 UNIX, Linux, and the Solaris Operating System
(SPARC)
Many of the examples in this book use the sample schemas, which are installed by
default when you select the Basic Installation option with an Oracle Database
installation. Refer to Oracle Database Sample Schemas for information on how these
schemas were created and how you can use them yourself.
Printed documentation is available for sale in the Oracle Store at
/>To download free release notes, installation documentation, white papers, or other
collateral, please visit the Oracle Technology Network (OTN). You must register online
before using OTN; registration is free and can be done at
/>If you already have a username and password for OTN, then you can go directly to the
documentation section of the OTN Web site at
/>Conventions
This section describes the conventions used in the text and code examples of this
documentation set. It describes:
■ Conventions in Text

elements include parameters, privileges,
datatypes, RMAN keywords, SQL
keywords, SQL*Plus or utility commands,
packages and methods, as well as
system-supplied column names, database
objects and structures, usernames, and
roles.
You can specify this clause only for a NUMBER
column.
You can back up the database by using the
BACKUP command.
Query the TABLE_NAME column in the USER_
TABLES data dictionary view.
Use the DBMS_STATS.GENERATE_STATS
procedure.
lowercase
monospace
(fixed-width)
font
Lowercase monospace typeface indicates
executable programs, filenames, directory
names, and sample user-supplied
elements. Such elements include computer
and database names, net service names
and connect identifiers, user-supplied
database objects and structures, column
names, packages and classes, usernames
and roles, program units, and parameter
values.
Note: Some programmatic elements use a

{ENABLE | DISABLE}
|
A vertical bar represents a choice of two
options.
{ENABLE | DISABLE}
[COMPRESS | NOCOMPRESS]
xvii
Conventions for Windows Operating Systems
The following table describes conventions for Windows operating systems and
provides examples of their use.

Ellipsis points mean repetition in syntax
descriptions.
In addition, ellipsis points can mean an
omission in code examples or text.
CREATE TABLE AS subquery;
SELECT col1, col2, , coln FROM
employees;
Other symbols You must use symbols other than brackets
([ ]), braces ({ }), vertical bars (|), and
ellipsis points ( ) exactly as shown.
acctbal NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
Italics
Italicized text indicates placeholders or
variables for which you must supply
particular values.
CONNECT SYSTEM/system_password
DB_NAME = database_name
UPPERCASE

File and directory
names
File and directory names are not case
sensitive. The following special characters
are not allowed: left angle bracket (<), right
angle bracket (>), colon (:), double
quotation marks ("), slash (/), pipe (|), and
dash (-). The special character backslash (\)
is treated as an element separator, even
when it appears in quotes. If the filename
begins with \\, then Windows assumes it
uses the Universal Naming Convention.
c:\winnt"\"system32 is the same as
C:\WINNT\SYSTEM32
C:\> Represents the Windows command
prompt of the current hard disk drive. The
escape character in a command prompt is
the caret (^). Your prompt reflects the
subdirectory in which you are working.
Referred to as the command prompt in this
manual.
C:\oracle\oradata>
Convention Meaning Example
xviii
Special characters The backslash (\) special character is
sometimes required as an escape character
for the double quotation mark (") special
character at the Windows command
prompt. Parentheses and the single
quotation mark (') do not require an escape

computer with no other Oracle software
installed, then the default setting for the
first Oracle home directory is
C:\oracle\product\10.1.0\db_n,
where n is the latest Oracle home number.
The Oracle home directory is located
directly under ORACLE_BASE.
All directory path examples in this guide
follow OFA conventions.
Refer to Oracle Database Installation Guide
for Windows for additional information
about OFA compliances and for
information about installing Oracle
products in non-OFA compliant
directories.
Go to the ORACLE_BASE\ORACLE_
HOME\rdbms\admin directory.
Convention Meaning Example
Introduction 1-1
1
Introduction
As an Oracle Database Administrator (DBA), you are responsible for the overall
operation of the Oracle database and maintaining its health.
This chapter contains the following topics:
■ About this Book
■ The Oracle Database
■ Common Oracle DBA Tasks
■ Tools for Administering the Database
About this Book
Oracle 2 Day DBA is a database administration quick start guide that teaches you how

Database 10g: 2 Day DBA. Contact Oracle University for more information.
The Oracle Database
Oracle is a relational database. In a relational database, all data is stored in
two-dimensional tables that are composed of rows and columns. The Oracle Database
enables you to store data, update it, and efficiently retrieve it.
Oracle provides software to create and manage the Oracle database. The database
consists of physical and logical structures in which system, user, and control
information is stored. The software that manages the database is called the Oracle
database server. Collectively, the software that runs oracle and the physical database
are called the Oracle database system.
You will learn more about the operation of the database server and the structure of the
Oracle database where they are relevant to the performance of specific database
management tasks.
Common Oracle DBA Tasks
As an Oracle DBA, you can expect to be involved in the following tasks:
■ Installing Oracle software
■ Creating Oracle databases
■ Performing upgrades of the database and software to new release levels
■ Starting up and shutting down the database
■ Managing the database’s storage structures
■ Managing users and security
■ Managing schema objects, such as tables, indexes, and views
■ Making database backups and performing recovery when necessary
■ Proactively monitoring the database’s health and taking preventive or corrective
action as required
■ Monitoring and tuning performance
In a small to midsize database environment, you might be the sole person performing
these tasks. In large, enterprise environments, the job is often divided among several
DBAs, each with their own specialty, such as database security or database tuning.
Tools for Administering the Database

Installing Oracle and Building the Database
This chapter describes how to install Oracle software and a starter database with the
Oracle Universal Installer (OUI). It also describes upgrading an older database to the
current release.
This chapter contains the following topics:
■ Overview of Installation
■ Installing Oracle Software and Database
■ Using DBCA to Create and Configure a Database
■ Upgrading a Database
■ Installation: Oracle by Example Series
Overview of Installation
To install your Oracle software you use the Oracle Universal Installer (OUI). The
Oracle Universal Installer is a graphical user interface (GUI) tool that enables you to
view the Oracle software that is already installed on your machine, install new Oracle
software, and deinstall Oracle software that you no longer intend to use. Online Help
is available to guide you through the installation process.
Checking Prerequisites
Before the installation process, Oracle performs a number of automated prerequisite
checks to ensure that your machine fulfills the basic hardware and software
requirements for an Oracle installation. If your machine does not meet any of the
requirements, an error message is displayed. The requirements may vary somewhat
depending upon the type of machine and operation system you are running, but some
examples of prerequisites include:
■ A minimum of 512 MB of memory
■ Sufficient paging space
■ Appropriate operating system service packs or patches are installed.
■ Appropriate file system format is being used.
The installer automatically sets any operating system environment variables that the
Oracle database server requires for its operation.
See Also: Your Oracle operating system-specific documentation if

installation that would not normally be installed or prevent certain
components from being installed.
■ Do you want to create a starter database in addition to installing the Oracle
software? To create the database, the installer automatically launches the Database
Configuration Assistant at the end of the installation.
If you want a preconfigured database installed, you can select one of the following
database types, optimized according to usage:
■ General Purpose
■ Transaction Processing
■ Data Warehouse
If you want to create an custom database where you configure your own database
structure, you can select an advanced install.
■ What are your Database Configuration Options? These options include your
Global Database Name, and Oracle System Identifier (SID).
Best Practice: Oracle recommends that you install a
preconfigured database, which is faster and easier to do. You can
customize the database later.
Overview of Installation
Installing Oracle and Building the Database 2-3
The Global Database Name is the full name of the database which uniquely
identifies it from any other database. The global database name is of the form
database_name.database_domain as in sales.us.acme.com. The database name
portion sales is a simple name you call your database. The database domain
portion us.acme.com specifies the database domain in which the database is
located. Together database name and domain make up the Global Database Name.
During a Windows basic installation, Oracle automatically installs the sample
schemas. Many of the examples in the documentation are based on the sample
schemas. Installing sample schemas is an option only during Windows advanced
installations and installations on Linux and UNIX. See "Advanced Installation
Choices" on page 2-3.

number of disks, this option simplifies database administration and
maximizes performance. Disk mirroring and striping are done automatically.
Automatic Storage Management requires a separate instance to configure and
manage disks groups. You are guided through the creation and launching of
an Automatic Storage Management instance and its components by the
database installation process. For more information, see Oracle Database
Administrator's Guide.