Oracle Database 10g:
Administration Workshop I
StudentGuide
D17090GC10
Edition 1.0
March 2004
D39126
®
Copyright © 2004, Oracle. All rights reserved.
This documentation contains proprietary information of Oracle Corporation. It is
provided under a license agreement containing restrictions on use and disclosure and
is also protected by copyright law. Reverse engineering of the software is prohibited.
If this documentation is delivered to a U.S. Government Agency of the Department of
Defense, then it is delivered with Restricted Rights and the following legend is
applicable:
Restricted Rights Legend
Use, duplication or disclosure by the Government is subject to restrictions for
commercial computer software and shall be deemed to be Restricted Rights software
under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013,
Rights in Technical Data and Computer Software (October 1988).
This material or any portion of it may not be copied in any formor by any means
without the express prior written permission of Oracle Corporation. Any other copying
is a violation of copyright law and may result in civil and/or criminal penalties.
If this documentation is delivered to a U.S. Government Agency not within the
Department of Defense, then it is delivered with “Restricted Rights,” as defined in
FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987).
The information in this document is subject to change without notice. If you find any
problems in the documentation, please report them in writing to Education Products,
Oracle Corporation, 500 Oracle Parkway, Redwood Shores, CA 94065. Oracle
Corporation does not warrant that this document is error-free.
Oracle and all references to Oracle Products are trademarks or registered trademarks

Objectives 1-2
Course Objectives 1-3
Oracle Products 1-4
Relational Database Systems 1-5
How the Data Is Organized 1-6
Integrity Constraints 1-7
Structured Query Language 1-8
Tasks of an Oracle Database Administrator 1-9
Summary 1-10
2 Installing Oracle Database 10g Software
Objectives 2-2
System Requirements 2-3
Optimal Flexible Architecture (OFA) 2-4
Using Optimal Flexible Architecture 2-5
Setting Environment Variables 2-7
PreinstallationChecks 2-9
Oracle Universal Installer 2-10
Inventory and UNIX Group Name 2-11
orainstRoot.sh 2-12
File Locations 2-14
Install Type 2-15
Prerequisite Checks 2-16
Starter Database 2-17
Configuration and Management 2-18
File Storage and Backup Recovery 2-19
Passwords and Summary 2-20
Installation 2-21
Configuration Assistants 2-22
Summary 2-24
Practice 2: Installing the Oracle Software 2-25

Objectives 4-2
What Is SQL? 4-3
Using SQL 4-4
Enterprise Manager: Seeing the SQL 4-5
What Is SQL*Plus? 4-6
What Is iSQL*Plus? 4-7
Using iSQL*Plus 4-9
Describing Data 4-10
Querying Data 4-11
Sorting the Data 4-12
Joining Tables 4-13
Manipulating Data 4-15
Defining Data 4-16
Overview of Transactions 4-17
Transaction Control Statements 4-18
Locking Data 4-19
Other Statement Categories 4-20
What Is PL/SQL? 4-21
Example PL/SQL Block 4-22
Uses of PL/SQL 4-23
What Is Java? 4-24
Oracle and Java 4-25
ii
What Is OCI? 4-26
Other APIs 4-27
Summary 4-29
Practice 4: Using SQL 4-30
5 Controlling the Database
Objectives 5-2
Starting and Stopping iSQL*Plus 5-3

iii
7 Administering Users
Objectives 7-2
Database User Accounts 7-3
Creating a User 7-4
Profiles and Users 7-5
Authenticating Users 7-7
Default and TemporaryTablespacesand Locking 7-9
Database Users and Schemas 7-10
Checklist for Creating Users 7-11
Privileges 7-12
System Privileges 7-13
Object Privileges 7-15
Assigning Quota to Users 7-16
Assigning Roles to Users 7-17
Roles 7-18
Benefits of Roles 7-19
Predefined Roles 7-20
Secure Roles 7-21
Summary 7-22
Practice 7: Administering Users 7-23
8 Managing Schema Objects
Objectives 8-2
What Is a Schema? 8-3
Schemas 8-4
Accessing Schema Objects 8-6
Naming Database Objects 8-7
Schema Object Namespaces 8-9
Specifying Data Types in Tables 8-10
Other Data Types 8-11

Comparing Direct and Conventional Path Loads 9-25
Loading Data with SQL*Loader 9-27
Summary 9-29
Practice 9: Using Data Pump Export and Import 9-30
10 PL/SQL
Objectives 10-2
PL/SQL 10-3
Administering PL/SQL Objects 10-4
PL/SQL Objects 10-5
Functions 10-6
Procedures 10-7
Packages 10-8
Package Body 10-9
Built-In Packages 10-11
Triggers 10-12
PL/SQL Configuration Options 10-14
Summary 10-15
Practice Overview 10-16
v
11 Oracle Database Security
Objectives 11-2
Database Security 11-3
Apply the Principle of Least Privilege 11-5
Protect the Data Dictionary 11-6
Revoke Unnecessary Privileges from PUBLIC 11-7
Restrict the Operating System Directories Accessible by the User11-9
Limit Users with Administrative Privileges 11-10
Disable Remote Operating System Authentication 11-11
Manage Default User Accounts 11-12
Implement Standard Password Security Features 11-13

12 Oracle Net Services
Objectives 12-2
Oracle Net Services 12-3
Oracle Net Listener 12-4
Monitoring the Listener 12-5
Creating a Listener 12-6
Listening Addresses 12-7
Configuring Optional Parameters 12-8
Static Database Registration 12-9
Listener Control Utility 12-10
Listener Control Utility Syntax 12-11
Monitoring with Listener Control 12-13
Oracle Net Connections 12-15
Names Resolution 12-16
Easy Connect 12-17
Local Naming 12-18
Directory Naming 12-19
Configuring Service Aliases 12-20
Advanced Connection Options 12-21
Oracle Net Manager 12-23
Choosing Naming Methods 12-24
Configuring Service Aliases with Net Manager 12-25
Advanced Connection Options Using Oracle Net Manager 12-26
Testing Oracle Net Connectivity 12-27
Summary 12-28
Practice 12 Overview: Oracle Net Services 12-29
13 Oracle Shared Servers
Objectives 13-2
Establishing a Connection 13-3
Dedicated Server Process 13-4

Schedule Optimizer Statistics Collection 14-14
Dynamic Performance Views 14-15
Viewing Metric Information 14-16
Reacting to Performance Issues 14-17
Reacting to Performance Issues 14-18
Summary 14-19
Practice 14: Monitoring Performance 14-20
15Proactive Maintenance
Objectives 15-2
Server Generated Alerts 15-3
Thresholds 15-4
Setting Thresholds 15-5
Baseline Measurements 15-6
Using Baselines 15-7
Tuning and Diagnostic Advisors 15-8
SQL Tuning and Access Advisors 15-10
Memory Advisors 15-11
Segment Advisor 15-12
Automatic Workload Repository (AWR) 15-13
Managing the AWR 15-14
Automatic Database Diagnostic Monitor (ADDM) 15-15
ADDM Findings 15-16
ADDM Recommendations 15-17
Summary 15-18
Practice 15: Proactive Maintenance 15-19
viii
16Undo Management
Objectives 16-2
Undo Data 16-3
Transactions and Undo Data 16-5

User Errors 18-8
Instance Failure 18-10
Instance Recovery 18-11
Phases of Instance Recovery 18-12
Tuning Instance Recovery 18-13
ix
Using the MTTR Advisor 18-14
Media Failure 18-15
Configuring for Recoverability 18-16
Control Files 18-17
Redo Log Files 18-18
Multiplexing the Redo Log 18-19
Archived Log Files 18-20
Archive Log File Naming and Destinations 18-21
ARCHIVELOG Mode 18-23
Summary 18-24
Practice 18: Backup and Recovery Concepts 18-25
19Database Backups
Objectives 19-2
Terminology 19-3
Recovery Manager (RMAN) 19-5
Configuring Backup Settings 19-6
Scheduling Backups: Strategy 19-8
Scheduling Backups: Options 19-9
Scheduling Backups: Settings 19-10
Scheduling Backups: Schedule 19-11
Scheduling Backups: Review 19-12
Backup Control File to Trace 19-13
Manage Backups 19-15
Flash Recovery Area 19-16

Copyright © 2004, Oracle. All rights reserved.
Introduction
Oracle Database 10g: Administration Workshop I 1-2
1-2
Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Explain the course objectives
• Identify the Oracle product line
• Describe the basic concepts of a relational
database
• List the core database administrator tasks
Oracle Database 10g: Administration Workshop I 1-3
1-3
Copyright © 2004, Oracle. All rights reserved.
Course Objectives
In this course, you will gain hands-on experience with:
• Installing, creating, and administering an Oracle
Database 10g Enterprise Edition database
• Configuring the database for an application
• Implementing a backup and recovery strategy
• Employing basic monitoring procedures
Course Objectives
In this course you will install the Oracle Database 10g Enterprise Edition software, create
a new database, and learn how to administer the database.
You will also configure the database to support an application, performing tasks such as
creating users, defining storage structures, and setting up security. This course uses a
fictional application. However you will be performing all the core tasks necessary for a
real application.

expertise you need to keep your Oracle project on track and running smoothly.
Oracle Database 10g: Administration Workshop I 1-5
1-5
Copyright © 2004, Oracle. All rights reserved.
Relational Database Systems
Table
Field or cellRow Column
Relational Database Systems
Dr. E. F.Coddpublished , “A Relational Model of Data for Large Shared Data Banks,” in
June, 1970, in the Association of Computer Machinery (ACM) journal, Communications
of the ACM.Codd'smodel is now accepted as the definitive model for relational database
management systems (RDBMS). The language, called structured English query language
(SEQUEL), was developed by IBM Corporation, Inc., to useCodd'smodel. SEQUEL later
became SQL (still pronounced “sequel”). In 1979, Relational Software, Inc. (now Oracle
Corporation) introduced the first commercially available implementation of SQL. Today,
SQL is accepted as the standard RDBMS language.
The basic element of a relational database system is a two-dimensional table. A table
consists of zero or more rows of data. Each row has one or more columns. A single column
of a single row is referred to as a field or cell of data.
Each row of data is a collection of data items relating to a given subject. For example, you
might have a table to hold information about employees in your company. Each row
represents one employee in the company, and the columns may be things such as, first and
last name, e-mail, phone number, hire date, and so on. Each column has a nameand a data
type associated with it. The data type defines what kind of datais allowed in the column,
for example numbers or characters.
Oracle Database 10g: Administration Workshop I 1-6
1-6
Copyright © 2004, Oracle. All rights reserved.
How the Data Is Organized
EMPLOYEES

not in each application that accesses the table.
Oracle Database 10g supports the following types of constraints:
• NOT NULL: Disallows nulls (empty entries) in a table’s column
• UNIQUE: Disallows duplicate values in a column or set of columns
• PRIMARY KEY: Disallows duplicate values and nulls in a column or set of columns
• FOREIGN KEY: Requires each value in a column or set of columns to match a value
in a related table’s UNIQUE or PRIMARY KEY. FOREIGN KEY integrity
constraints also define referential integrity actions that dictate what Oracle should do
with dependent data if the data it references is altered.
• CHECK: Disallows values that do not satisfy the logical expression ofthe constraint.
Oracle Database 10g: Administration Workshop I 1-8
1-8
Copyright © 2004, Oracle. All rights reserved.
Structured Query Language
• Structured query language (SQL): A standard
interactive and programming language for getting
information from and updating information in a
database
• SQL provides statements for a variety of tasks,
including:
– Querying data
– Inserting, updating, and deleting rows in a table
– Creating, replacing, altering, and dropping objects
– Controlling access to the database and its objects
– Guaranteeing database consistency and integrity
Structured Query Language
Structured Query Language (SQL) is the set of statements with which all programs and
users access data in an Oracle database. Application programs and Oracle tools often allow
users access to the database without using SQL directly, but these applications in turn must
use SQL when executing the user’s request.

performance of the database.
As you proceed through the lessons in this course, you will learn how to perform each of
these tasks. You can also refer to the Oracle Database Administrator’s Guide for
additional information about each of the tasks outlined in the slide.
Oracle Database 10g: Administration Workshop I 1-10
1-10
Copyright © 2004, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Explain the course objectives
• Describe the Oracle product line
• Identify the basic concepts of a relational
database
• List the core Database Administrator tasks
Copyright © 2004, Oracle. All rights reserved.
Installing Oracle Database 10g Software