Database Administration
Preface Profile xii Related Publications xiii Typographic Conventions xiv Curriculum Map Oracle8 Database Administrator Curriculum xviii Advanced DBA Curriculum xix Introduction Course Objectives I-3 Oracle8 Enterprise Edition I-4 Database Administrator Tasks I-5 Course Schedule I-6 Lesson 1: Oracle Architectural Components Objectives 1-3 Overview 1-4 Connecting to a Database 1-6 Running a Query 1-14 Running a DML Statement 1-19 COMMIT Processing 1-25 Summary 1-27 Lesson 2: Using Administration Tools Objectives 2-3 Overview 2-4 Using the Server Manager 2-5 Oracle Enterprise Manager 2-9 OEM Components 2-11 Using Oracle Enterprise Manager 2-19 Summary 2-24 Lesson 3: Managing an Oracle Instance Objectives 3-3 Overview 3-4 Validating Privileged Users 3-5 Creating a Parameter File 3-14 Stages in Startup and Shutdown 3-19 Starting Up 3-22 Shutting Down 3-25 Getting and Setting Parameter Values 3-30 Managing Sessions 3-36 Trace Files and the ALERT File 3-40 Summary 3-43 iv Oracle8: Database Administration C o n t.e n t.s Lesson 4: Creating a Database Objectives 4-3 Overview 4-4 Preparing the Operating System 4-5 Creating a Database 4-12 Lesson 5: Creating Data Dictionary Views and Standard Packages Objectives 5-3 Data Dictionary Structure 5-4 Constructing the Data Dictionary 5-11 Using Administrative Scripts 5-13 Administering Stored Procedures and Packages 5-15 Obtaining Information 5-23 Troubleshooting 5-26 Summary 5-27 Lesson 6: Maintaining the Control File Objectives 6-3 Using the Control File 6-4 The Contents of the Control File 6-5 Obtaining Information About the Control File 6-7 Multiplexing the Control File 6-9 Summary 6-10 Lesson 7: Maintaining Redo Log Files Objectives 7-3 Overview 7-4 Using Online Redo Files 7-5 LGWR, Log Switches, and Checkpoints 7-7 Archiving Redo Log Files 7-9 Obtaining Log and Archive Information 7-11 Controlling Log Switches and Checkpoints 7-17 Multiplexing and Maintaining Members and Groups 7-19 Relocating Online Redo Log Files 7-22 Dropping Online Redo Log Groups and Members 7-23 Clearing Online Redo Log Files 7-27 Planning Online Redo Logs 7-28 Troubleshooting 7-30 Summary 7-31 Oracle8: Database Administration v C o n t.e n t.s Lesson 8: Managing Tablespaces and Data Files Objectives 8-3 Overview 8-4 Logical Database Structure 8-5 Creating Tablespaces 8-8 Changing the Size of Tablespaces 8-16 Changing the Size of Data Files 8-18 Changing the Storage Settings 8-21 Taking Tablespaces Offline or Online 8-22 Moving Data Files 8-24 Read-Only Tablespaces 8-27 Dropping Tablespaces 8-30 Data Dictionary Information 8-32 Considerations in Creating Tablespaces 8-37 Lesson 9: Storage Structure and Relationships Objectives 9-3 Overview 9-4 Types of Segments 9-5 Using Block Space Utilization Parameters 9-16 Obtaining Information About Storage Structures 9-21 Planning the Location of Segments 9-25 Summary 9-27 Lesson 10: Managing Rollback Segments Objectives 10-3 Overview 10-4 Rollback Segments 10-5 Using Rollback Segments with Transactions 10-8 Planning Rollback Segments 10-13 Creating Rollback Segments 10-15 Maintaining Rollback Segments 10-21 Obtaining Rollback Segment Information 10-26 Troubleshooting Rollback Segment Problems 10-32 Summary 10-39 Lesson 11: Managing Temporary Segments Objectives 11-3 Overview 11-4 Types of Temporary Segments 11-6 Allocating Space for Temporary Segments 11-9 Obtaining Temporary Segment Information 11-11 Summary 11-14 vi Oracle8: Database Administration C o n t.e n t.s Lesson 12: Managing Tables Objectives 12-3 Overview 12-4 Oracle Data Types 12-7 Creating a Table 12-16 Controlling Space Used by Tables 12-24 Retrieving Table Information 12-42 Summary 12-47 Lesson 13: Managing Indexes Objectives 13-3 Types of Indexes 13-4 Creating Indexes 13-12 Reorganizing Indexes 13-20 Dropping Indexes 13-26 Obtaining Index Information 13-28 Summary 13-30 Lesson 14: Maintaining Data Integrity Objectives 14-3 Overview 14-4 Integrity Constraints and Triggers 14-6 Implementing Constraints and Triggers 14-16 Maintaining Constraints and Triggers 14-20 Getting Constraint and Trigger Information 14-31 Summary 14-36 Lesson 15: Using Clusters and Index-Organized Tables Objectives 15-3 Overview 15-4 Clusters 15-5 Creating Clusters 15-9 Maintaining Clusters 15-17 Retrieving Information About Clusters 15-21 Index-Organized Tables 15-24 Using Index-Organized Tables 15-26 Retrieving Information About Index-Organized Tables 15-31 Summary 15-32 Lesson 16: Loading and Reorganizing Data Objectives 16-3 Overview 16-4 Loading Data Using Direct-Load Insert 16-6 Loading Data Using SQL*Loader 16-9 Reorganizing Data Using Export and Import 16-29 Summary 16-48 Oracle8: Database Administration vii C o n t.e n t.s Lesson 17: Managing Users Objectives 17-3 Overview 17-4 Creating New Database Users 17-7 Altering and Dropping Database Users 17-14 Dropping Users 17-18 Monitoring Information About Users 17-19 Summary 17-21 Lesson 18: Managing Profiles Objectives 18-3 Overview 18-4 Controlling Usage of Resources 18-6 Altering and Dropping a Profile 18-14 Viewing Resource Limits 18-18 Administering Passwords 18-20 Viewing Password Information 18-27 Summary 18-29 Lesson 19: Managing Privileges Objectives 19-3 Overview 19-4 System Privileges 19-5 Granting System Privileges 19-8 Password File Authentication 19-10 Displaying System Privileges 19-13 Revoking System Privileges 19-17 Object Privileges 19-20 Granting Object Privileges 19-21 Displaying Object Privileges 19-23 Revoking Object Privileges 19-25 Summary 19-29 Lesson 20: Managing Roles Objectives 20-3 Overview 20-4 Creating and Modifying Roles 20-7 Assigning Roles 20-13 Controlling Availability of Roles 20-15 Displaying Role Information 20-27 Summary 20-28 viii Oracle8: Database Administration C o n t.e n t.s Lesson 21: Auditing Objectives 21-3 Overview 21-4 Using Database Auditing 21-7 Viewing Auditing Results 21-18 Auditing Guidelines 21-20 Summary 21-22 Lesson 22: Using National Language Support Objectives 22-3 Overview 22-4 Choosing a Database and a National Character Set 22-6 Specifying Language-Dependent Behavior 22-12 NLS Parameters and SQL-Functions 22-20 NLS Parameters in SQL-Functions 22-22 Importing and Loading Data Using NLS 22-26 Obtaining Information About NLS Settings 22-27 Summary 22-32 Appendix A: Practices Environment A-2 Exercise 1—Oracle Architectural Components A-3 Lab 2—Using Administration Tools A-5 Lab 3—Managing an Oracle Instance A-6 Lab 4—Creating a Database A-8 Lab 5—Creating Data Dictionary Views and Standard Packages A-9 Lab 6—Maintaining the Control File A-10 Lab 7—Maintaining Redo Log Files A-11 Lab 8—Managing Tablespaces and Data Files A-12 Lab 9—Storage Structure and Relationships A-13 Lab 10—Managing Rollback Segments A-14 Lab 11—Managing Temporary Segments A-15 Lab 12—Managing Tables A-16 Lab 13—Managing Indexes A-18 Lab 14—Maintaining Data Integrity A-20 Lab 15—Using Clusters and Index-Organized Tables A-22 Lab 16—Loading and Reorganizing Data A-23 Lab 17—Managing Users A-25 Lab 18—Managing Profiles A-26 Lab 19—Managing Privileges A-27 Lab 20—Managing Roles A-28 Lab 21—Auditing A-29 Lab 22—Using National Language Support A-30 Oracle8: Database Administration ix C o n t.e n t.s Appendix B: Hints Exercise 1—Oracle Architectural Components B-2 Lab 2—Using Administration Tools B-3 Lab 3—Managing an Oracle Instance B-4 Lab 4—Creating a Database B-7 Lab 5—Creating Data Dictionary Views and Standard Packages B-9 Lab 6—Maintaining the Control File B-10 Lab 7—Maintaining Redo Log Files B-11 Lab 8—Managing Tablespaces and Data Files B-13 Lab 9—Storage Structure and Relationships B-15 Lab 10—Managing Rollback Segments B-17 Lab 11—Managing Temporary Segments B-19 Lab 12—Managing Tables B-20 Lab 13—Managing Indexes B-22 Lab 14—Maintaining Data Integrity B-24 Lab 15—Using Clusters and Index-Organized Tables B-26 Lab 16—Loading and Reorganizing Data B-28 Lab 17—Managing Users B-30 Lab 18—Managing Profiles B-31 Lab 19—Managing Privileges B-33 Lab 20—Managing Roles B-34 Lab 21—Auditing B-35 Lab 22—Using National Language Support B-36 Appendix C: Server Manager Solutions Exercise 1—Oracle Architectural Components C-2 Lab 2—Using Administration Tools C-4 Lab 3—Managing an Oracle Instance C-8 Lab 4—Creating a Database C-16 Lab 5—Creating Data Dictionary Views and Standard Packages C-20 Lab 6—Maintaining the Control File C-25 Lab 7—Maintaining Redo Log Files C-29 Lab 8—Managing Tablespaces and Data Files C-35 Lab 9—Storage Structure and Relationships C-40 Lab 10—Managing Rollback Segments C-47 Lab 11—Managing Temporary Segments C-56 Lab 12—Managing Tables C-60 Lab 13—Managing Indexes C-70 Lab 14—Maintaining Data Integrity C-78 Lab 15—Using Clusters and Index-Organized Tables C-87 Lab 16—Loading and Reorganizing Data C-92 Lab 17—Managing Users C-102 Lab 18—Managing Profiles C-105 Lab 19—Managing Privileges C-112 Lab 20—Managing Roles C-117 x Oracle8: Database Administration C o n t.e n t.s Lab 21—Auditing C-120 Lab 22—Using National Language Support C-121 Appendix D: Oracle Enterprise Manager Solutions Exercise 1—Oracle Architectural Components D-3 Lab 2—Using Administration Tools D-5 Lab 3—Managing an Oracle Instance D-9 Lab 4—Creating a Database D-18 Lab 5—Creating Data Dictionary Views and Standard Packages D-21 Lab 6—Maintaining the Control File D-25 Lab 7—Maintaining Redo Log Files D-28 Lab 8—Managing Tablespaces and Data Files D-34 Lab 9—Storage Structure and Relationships D-38 Lab 10—Managing Rollback Segments D-46 Lab 11—Managing Temporary Segments D-55 Lab 12—Managing Tables D-59 Lab 13—Managing Indexes D-68 Lab 14—Maintaining Data Integrity D-76 Lab 15—Using Clusters and Index-Organized Tables D-85 Lab 16—Loading and Reorganizing Data D-90 Lab 17—Managing Users D-100 Lab 18—Managing Profiles D-103 Lab 19—Managing Privileges D-110 Lab 20—Managing Roles D-115 Lab 21—Auditing D-118 Lab 22—Using National Language Support D-119 Appendix E: Certification Test: Sample Questions Oracle Certified Professional (OCP) Program: Oracle Certified Database Administrator Track E-2 Oracle Database Administration: Sample Test E-3 Oracle Backup and Recovery: Sample Test E-5 Answers E-8 Registering for an OCP Test E-9
Bạn đang xem trước 20 trang tài liệu Database Administration, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Oracle8: Database Administration
....................................................................................................
Volume 1 • Instructor Guide
30020GC10
Production 1.0
March 1998
M06291
Copyright Oracle Corporation, 1998. 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 form or by any means
without the express prior written permission of the Worldwide Education Services
group 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 Right,” 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, Box 659806, Redwood
Shores, CA 94065. Oracle Corporation does not warrant that this document is
error-free.
SQL*Loader, SQL*Net, SQL*Plus, Net8, Oracle Call Interface, Oracle7,
Oracle8, Developer/2000, Developer/2000 Forms, Designer/2000, Oracle
Enterprise Manager, Oracle Parallel Server, Oracle Server Manager, PL/
SQL, Pro*C, Pro*C/C++, and Trusted Oracle are trademarks or registered
trademarks of Oracle Corporation.
All other products or company names are used for identification purposes only,
and may be trademarks of their respective owners.
Authors
Ulrike Schwinn
Vijayanandan Venkatachalam
Technical Contributors
and Reviewers
David Austin
Ben van Balen
Gerry Batista
Doug Bridges
Sandra Cheevers
Bruce Ernst
Joel Goodman
Scott Gossett
Lex de Haan
Tony Holbrook
Heike Hundt
Christine Jeal
Dominique Jeunot
Thomas Kerepes
Steven King
Pierre Labrousse
Dean Margolese
Jean-Marie Misztela
Tigger Newman
Howard Ostrow
Hans Proetzl
Gary Purcell
Shankar Raman
Donalyn Selinsky
Roger Simon
Jim Spiller
Ramonito Te
Sabine Teuber
Jean-Francois Verrier
Norbert Wittje
Publishers
Stephanie Jones
Kelly Lee
Renee Voss
Oracle8: Database Administration iii
......................................................................................................................................................
......................................................................................................................................................
Contents
Preface
Profile xii
Related Publications xiii
Typographic Conventions xiv
Curriculum Map
Oracle8 Database Administrator Curriculum xviii
Advanced DBA Curriculum xix
Introduction
Course Objectives I-3
Oracle8 Enterprise Edition I-4
Database Administrator Tasks I-5
Course Schedule I-6
Lesson 1: Oracle Architectural Components
Objectives 1-3
Overview 1-4
Connecting to a Database 1-6
Running a Query 1-14
Running a DML Statement 1-19
COMMIT Processing 1-25
Summary 1-27
Lesson 2: Using Administration Tools
Objectives 2-3
Overview 2-4
Using the Server Manager 2-5
Oracle Enterprise Manager 2-9
OEM Components 2-11
Using Oracle Enterprise Manager 2-19
Summary 2-24
Lesson 3: Managing an Oracle Instance
Objectives 3-3
Overview 3-4
Validating Privileged Users 3-5
Creating a Parameter File 3-14
Stages in Startup and Shutdown 3-19
Starting Up 3-22
Shutting Down 3-25
Getting and Setting Parameter Values 3-30
Managing Sessions 3-36
Trace Files and the ALERT File 3-40
Summary 3-43
iv Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Contents
Lesson 4: Creating a Database
Objectives 4-3
Overview 4-4
Preparing the Operating System 4-5
Creating a Database 4-12
Lesson 5: Creating Data Dictionary Views and Standard Packages
Objectives 5-3
Data Dictionary Structure 5-4
Constructing the Data Dictionary 5-11
Using Administrative Scripts 5-13
Administering Stored Procedures and Packages 5-15
Obtaining Information 5-23
Troubleshooting 5-26
Summary 5-27
Lesson 6: Maintaining the Control File
Objectives 6-3
Using the Control File 6-4
The Contents of the Control File 6-5
Obtaining Information About the Control File 6-7
Multiplexing the Control File 6-9
Summary 6-10
Lesson 7: Maintaining Redo Log Files
Objectives 7-3
Overview 7-4
Using Online Redo Files 7-5
LGWR, Log Switches, and Checkpoints 7-7
Archiving Redo Log Files 7-9
Obtaining Log and Archive Information 7-11
Controlling Log Switches and Checkpoints 7-17
Multiplexing and Maintaining Members and Groups 7-19
Relocating Online Redo Log Files 7-22
Dropping Online Redo Log Groups and Members 7-23
Clearing Online Redo Log Files 7-27
Planning Online Redo Logs 7-28
Troubleshooting 7-30
Summary 7-31
Oracle8: Database Administration v
......................................................................................................................................................
......................................................................................................................................................
Contents
Lesson 8: Managing Tablespaces and Data Files
Objectives 8-3
Overview 8-4
Logical Database Structure 8-5
Creating Tablespaces 8-8
Changing the Size of Tablespaces 8-16
Changing the Size of Data Files 8-18
Changing the Storage Settings 8-21
Taking Tablespaces Offline or Online 8-22
Moving Data Files 8-24
Read-Only Tablespaces 8-27
Dropping Tablespaces 8-30
Data Dictionary Information 8-32
Considerations in Creating Tablespaces 8-37
Lesson 9: Storage Structure and Relationships
Objectives 9-3
Overview 9-4
Types of Segments 9-5
Using Block Space Utilization Parameters 9-16
Obtaining Information About Storage Structures 9-21
Planning the Location of Segments 9-25
Summary 9-27
Lesson 10: Managing Rollback Segments
Objectives 10-3
Overview 10-4
Rollback Segments 10-5
Using Rollback Segments with Transactions 10-8
Planning Rollback Segments 10-13
Creating Rollback Segments 10-15
Maintaining Rollback Segments 10-21
Obtaining Rollback Segment Information 10-26
Troubleshooting Rollback Segment Problems 10-32
Summary 10-39
Lesson 11: Managing Temporary Segments
Objectives 11-3
Overview 11-4
Types of Temporary Segments 11-6
Allocating Space for Temporary Segments 11-9
Obtaining Temporary Segment Information 11-11
Summary 11-14
vi Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Contents
Lesson 12: Managing Tables
Objectives 12-3
Overview 12-4
Oracle Data Types 12-7
Creating a Table 12-16
Controlling Space Used by Tables 12-24
Retrieving Table Information 12-42
Summary 12-47
Lesson 13: Managing Indexes
Objectives 13-3
Types of Indexes 13-4
Creating Indexes 13-12
Reorganizing Indexes 13-20
Dropping Indexes 13-26
Obtaining Index Information 13-28
Summary 13-30
Lesson 14: Maintaining Data Integrity
Objectives 14-3
Overview 14-4
Integrity Constraints and Triggers 14-6
Implementing Constraints and Triggers 14-16
Maintaining Constraints and Triggers 14-20
Getting Constraint and Trigger Information 14-31
Summary 14-36
Lesson 15: Using Clusters and Index-Organized Tables
Objectives 15-3
Overview 15-4
Clusters 15-5
Creating Clusters 15-9
Maintaining Clusters 15-17
Retrieving Information About Clusters 15-21
Index-Organized Tables 15-24
Using Index-Organized Tables 15-26
Retrieving Information About Index-Organized Tables 15-31
Summary 15-32
Lesson 16: Loading and Reorganizing Data
Objectives 16-3
Overview 16-4
Loading Data Using Direct-Load Insert 16-6
Loading Data Using SQL*Loader 16-9
Reorganizing Data Using Export and Import 16-29
Summary 16-48
Oracle8: Database Administration vii
......................................................................................................................................................
......................................................................................................................................................
Contents
Lesson 17: Managing Users
Objectives 17-3
Overview 17-4
Creating New Database Users 17-7
Altering and Dropping Database Users 17-14
Dropping Users 17-18
Monitoring Information About Users 17-19
Summary 17-21
Lesson 18: Managing Profiles
Objectives 18-3
Overview 18-4
Controlling Usage of Resources 18-6
Altering and Dropping a Profile 18-14
Viewing Resource Limits 18-18
Administering Passwords 18-20
Viewing Password Information 18-27
Summary 18-29
Lesson 19: Managing Privileges
Objectives 19-3
Overview 19-4
System Privileges 19-5
Granting System Privileges 19-8
Password File Authentication 19-10
Displaying System Privileges 19-13
Revoking System Privileges 19-17
Object Privileges 19-20
Granting Object Privileges 19-21
Displaying Object Privileges 19-23
Revoking Object Privileges 19-25
Summary 19-29
Lesson 20: Managing Roles
Objectives 20-3
Overview 20-4
Creating and Modifying Roles 20-7
Assigning Roles 20-13
Controlling Availability of Roles 20-15
Displaying Role Information 20-27
Summary 20-28
viii Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Contents
Lesson 21: Auditing
Objectives 21-3
Overview 21-4
Using Database Auditing 21-7
Viewing Auditing Results 21-18
Auditing Guidelines 21-20
Summary 21-22
Lesson 22: Using National Language Support
Objectives 22-3
Overview 22-4
Choosing a Database and a National Character Set 22-6
Specifying Language-Dependent Behavior 22-12
NLS Parameters and SQL-Functions 22-20
NLS Parameters in SQL-Functions 22-22
Importing and Loading Data Using NLS 22-26
Obtaining Information About NLS Settings 22-27
Summary 22-32
Appendix A: Practices
Environment A-2
Exercise 1—Oracle Architectural Components A-3
Lab 2—Using Administration Tools A-5
Lab 3—Managing an Oracle Instance A-6
Lab 4—Creating a Database A-8
Lab 5—Creating Data Dictionary Views and Standard Packages A-9
Lab 6—Maintaining the Control File A-10
Lab 7—Maintaining Redo Log Files A-11
Lab 8—Managing Tablespaces and Data Files A-12
Lab 9—Storage Structure and Relationships A-13
Lab 10—Managing Rollback Segments A-14
Lab 11—Managing Temporary Segments A-15
Lab 12—Managing Tables A-16
Lab 13—Managing Indexes A-18
Lab 14—Maintaining Data Integrity A-20
Lab 15—Using Clusters and Index-Organized Tables A-22
Lab 16—Loading and Reorganizing Data A-23
Lab 17—Managing Users A-25
Lab 18—Managing Profiles A-26
Lab 19—Managing Privileges A-27
Lab 20—Managing Roles A-28
Lab 21—Auditing A-29
Lab 22—Using National Language Support A-30
Oracle8: Database Administration ix
......................................................................................................................................................
......................................................................................................................................................
Contents
Appendix B: Hints
Exercise 1—Oracle Architectural Components B-2
Lab 2—Using Administration Tools B-3
Lab 3—Managing an Oracle Instance B-4
Lab 4—Creating a Database B-7
Lab 5—Creating Data Dictionary Views and Standard Packages B-9
Lab 6—Maintaining the Control File B-10
Lab 7—Maintaining Redo Log Files B-11
Lab 8—Managing Tablespaces and Data Files B-13
Lab 9—Storage Structure and Relationships B-15
Lab 10—Managing Rollback Segments B-17
Lab 11—Managing Temporary Segments B-19
Lab 12—Managing Tables B-20
Lab 13—Managing Indexes B-22
Lab 14—Maintaining Data Integrity B-24
Lab 15—Using Clusters and Index-Organized Tables B-26
Lab 16—Loading and Reorganizing Data B-28
Lab 17—Managing Users B-30
Lab 18—Managing Profiles B-31
Lab 19—Managing Privileges B-33
Lab 20—Managing Roles B-34
Lab 21—Auditing B-35
Lab 22—Using National Language Support B-36
Appendix C: Server Manager Solutions
Exercise 1—Oracle Architectural Components C-2
Lab 2—Using Administration Tools C-4
Lab 3—Managing an Oracle Instance C-8
Lab 4—Creating a Database C-16
Lab 5—Creating Data Dictionary Views and Standard Packages C-20
Lab 6—Maintaining the Control File C-25
Lab 7—Maintaining Redo Log Files C-29
Lab 8—Managing Tablespaces and Data Files C-35
Lab 9—Storage Structure and Relationships C-40
Lab 10—Managing Rollback Segments C-47
Lab 11—Managing Temporary Segments C-56
Lab 12—Managing Tables C-60
Lab 13—Managing Indexes C-70
Lab 14—Maintaining Data Integrity C-78
Lab 15—Using Clusters and Index-Organized Tables C-87
Lab 16—Loading and Reorganizing Data C-92
Lab 17—Managing Users C-102
Lab 18—Managing Profiles C-105
Lab 19—Managing Privileges C-112
Lab 20—Managing Roles C-117
x Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Contents
Lab 21—Auditing C-120
Lab 22—Using National Language Support C-121
Appendix D: Oracle Enterprise Manager Solutions
Exercise 1—Oracle Architectural Components D-3
Lab 2—Using Administration Tools D-5
Lab 3—Managing an Oracle Instance D-9
Lab 4—Creating a Database D-18
Lab 5—Creating Data Dictionary Views and Standard Packages D-21
Lab 6—Maintaining the Control File D-25
Lab 7—Maintaining Redo Log Files D-28
Lab 8—Managing Tablespaces and Data Files D-34
Lab 9—Storage Structure and Relationships D-38
Lab 10—Managing Rollback Segments D-46
Lab 11—Managing Temporary Segments D-55
Lab 12—Managing Tables D-59
Lab 13—Managing Indexes D-68
Lab 14—Maintaining Data Integrity D-76
Lab 15—Using Clusters and Index-Organized Tables D-85
Lab 16—Loading and Reorganizing Data D-90
Lab 17—Managing Users D-100
Lab 18—Managing Profiles D-103
Lab 19—Managing Privileges D-110
Lab 20—Managing Roles D-115
Lab 21—Auditing D-118
Lab 22—Using National Language Support D-119
Appendix E: Certification Test: Sample Questions
Oracle Certified Professional (OCP) Program:
Oracle Certified Database Administrator Track E-2
Oracle Database Administration: Sample Test E-3
Oracle Backup and Recovery: Sample Test E-5
Answers E-8
Registering for an OCP Test E-9
................................
Preface
xii Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Preface
Profile
This course is designed to give the Oracle database administrator (DBA) a
firm foundation in basic administrative tasks. The primary goal of this
course is to give the DBA the necessary knowledge and skills to set up,
maintain, and troubleshoot an Oracle database. This course has been
designed for database administrators, technical support analysts, system
administrators, application developers, MIS managers, and other Oracle
users.
This preface covers the following sections:
• Before You Begin This Course
• Prerequisites
• How This Course Is Organized
• How This Book Is Organized
• Related Publications
• Typographic Conventions
Before You Begin This Course
The specific skills you as a participant must have in order to derive the
maximum value from attending this course are:
• Familiarity with relational database concepts
• Thorough knowledge of SQL, SQL*Plus, and PL/SQL
• Basic operating system knowledge
• Working experience with Oracle
Prerequisites
• SQL 1
• PL/SQL Fundamentals
How This Course Is Organized
Oracle8: Database Administration is an instructor-led course featuring
lectures and hands-on exercises. Online demonstrations, animation, and
written practice sessions reinforce the concepts and skills introduced. The
course also uses challenge-level practice labs including scenarios and new
“drill-down” topics for NT users.
In addition, bulletins form Oracle Worldwide Support that address the most
frequently asked questions are used to prepare participants to troubleshoot
“real-world” issues.
This course contains clearly defined objectives designed to support
preparation for the Oracle Certified Professional examination.
Oracle8: Database Administration xiii
......................................................................................................................................................
......................................................................................................................................................
Related Publications
Related Publications
Oracle Publications
Web Sites
Additional Publications
• System release bulletins
• Installation and Configuration Guides
• International Oracle User’s Group (IOUG) articles
• Oracle Magazine
Title Part Number
Oracle8: Server Documentation Kit A54665
Oracle DBA Handbook, 7.3 Edition ISBN: 0-070882285
Oracle8: A Beginner’s Guide ISBN: 0-07-882393
Oracle Troubleshooting ISBN: 0-07-882388
Oracle: The Complete Reference ISBN: 0-07-882097
xiv Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Preface
Typographic Conventions
Typographic Conventions Within Text
The following conventions are used within text:
Convention Object or Term Example
Uppercase Commands,
functions,
column names,
table names,
PL/SQL objects,
schemas
Use the SELECT command to view information
stored in the LAST_NAME column of the EMP
table.
Lowercase, italic Filenames,
syntax variables,
usernames,
passwords
Use the utlxplan.sql
script to create the EXCEPTIONS table.
Initial cap Button names Click Yes.
Italics Books, names of
courses and manuals,
and emphasized
words or phrases
For further information on the subject see:
Oracle8 Server Utilities Manual.
In the simplest configuration known as a ded-
icated server, each server process
services only one user process.
Quotation marks Lessons referenced
within a course
The redo log files are covered in more detail
in the lesson “Managing Redo Log Files.”
Oracle8: Database Administration xv
......................................................................................................................................................
......................................................................................................................................................
Typographic Conventions
Typographic Conventions Within Code
The following conventions are used within code:
Punctuation Within Command Syntax
Convention Object or Term Example
Uppercase Commands,
functions
SELECT tablespace_name, status
FROM dba_tablespaces;
Lowercase,
italic
Syntax variables CREATE ROLE role
Lowercase Column names,
table name,
filenames, PL/SQL
objects
SELECT tablespace_name, status
FROM dba_tablespaces;
Convention Description Example
Vertical bar Separates alternative syntax elements
that may be optional or mandatory
{SYSDBA|SYSOPER}
Brackets One or more optional items (If two
items appear separated by |, enter one of
the items. Do not enter the brackets or |.)
[PARALLEL|SHARED]
Braces A choice of mandatory items (Enter one
of the items separated by |. Do not enter
the braces or |.)
{ONLINE|OFFLINE}
Underlining A default value (If you enter nothing,
SQL*Plus assumes the underlined
value.)
{ONLINE|OFFLINE}
Ellipsis Preceding items may be repeated any
number of times.
filespec }...
xvi Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Preface
................................
Curriculum
Map
xviii Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Curriculum Map
Oracle8 Database Administrator Curriculum
The Oracle8 Database Administrator Curriculum is the industry’s most
comprehensive set of courses for the Oracle DBA. The curriculum is
designed with one objective, to make you successful with Oracle.
Each individual class is based on a set of key DBA tasks, and combines
comprehensive theoretical background and rigorous hands on applications
of the skills learned.
We recommend you take Oracle8: Database Administration and Oracle8:
Backup and Recovery Workshop in quick succession. Put your new skills
into practice for about three months of real-world experience before
sharpening your knowledge in Oracle8: Network Administration and
Oracle8: Performance Tuning Workshop.
Detailed course descriptions and availability are available from
Instructor Note
Note that all classes in the curriculum path support Oracle7 and Oracle8. Likewise
all classes support the tests for the Oracle7 DBA Certified Oracle Professional
(OCP) program. For more information on the OCP program, point students to
Copyright Oracle Corporation, 1998. All rights reserved.
Oracle8 Database Administrator
Curriculum
Introduction to Oracle:
SQL and PL/SQL
SQL1 PL/SQL Fundamentals
Introduction to Oracle
for Experienced SQL Users
Oracle SQL
Specifics
PL/SQL
Fundamentals
or
Oracle8: Backup and Recovery Workshop
Oracle8: DatabaseAdministration
Oracle8: Network Administration
Oracle8: Performance Tuning Workshop
Advanced DBA
Curriculum tracks
Oracle8: Database Administration xix
......................................................................................................................................................
......................................................................................................................................................
Advanced DBA Curriculum
Advanced DBA Curriculum
Following the core DBA track we have a range of courses to support advanced
options and specific operating systems. Course availability varies by region. This
represents a subset of our course offerings.
For a detailed list, course descriptions, and availability check our Web site:
Copyright Oracle Corporation, 1998. All rights reserved.
Advanced DBA
Curriculum
Distributed Database
Oracle8: Distributed Database
Oracle8: Advanced Replication
Parallel Operations
Oracle7/8: Parallel Server Implementation
Oracle7/8: Advanced Replication
Oracle for IBM RS6000/SP
Cartridges
Oracle7/8: Spatial Data Cartridge
Oracle7/8: Context Cartridge
Touchpoint
Manage Oracle7/8 on HP-UX
Manage Oracle7/8 on Solaris
Manage Oracle7/8 on NT 40
Manage Oracle7/8 on MVS
Technology Upgrade
Oracle8: New Features for Administration
Oracle8: New Features for Developers
Oracle8: Oracle Call Interface Extension
Oracle Enterprise Manager
Oracle Rdb
Introduction to Oracle Rdb
Rdb for DBAs
Rdb Performance Tuning
Rdb for NT
xx Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Curriculum Map
................................
I
Introduction
I-2 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Introduction
Instructor Note
Topic Timing
Lecture 15 minutes
Practice 0 minutes
Total 15 minutes
Oracle8: Database Administration I-3
......................................................................................................................................................
......................................................................................................................................................
Course Objectives
Course Objectives
I-2 Copyright Oracle Corporation, 1998. All rights reserved.
Course Objectives
• Starting up and shutting down an Oracle
instance and database
• Creating an operational database
• Managing Oracle database files
• Managing tablespaces, segments,
extents, and blocks
• Managing users, privileges, and resources
• Using National Language Support (NLS)
features
I-4 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Introduction
Oracle8 Enterprise Edition
Oracle8 Enterprise Edition is an object relational database that is scalable
and easily manageable. The administration of the basic enterprise edition is
discussed in this course. However, the following options provide additional
functionality:
• Partitioning: provides facilities for implementation of large, scalable
applications (It enables control over tables and indexes at a lower level
of granularity than is possible with the basic enterprise edition.)
• Oracle Parallel Server: requires special hardware and software, and it
can improve the availability of a database
• Oracle Enterprise Manager Performance Pack: built on top of the Oracle
Enterprise Manager, Oracle Enterprise Manager Performance Pack is an
add-on that provides DBAs with a set of tools for advanced diagnostics,
monitoring, and tuning of Oracle environments.
• Objects: enables users to define user-defined types that are representative
of real world structures, and use them to develop applications.
• Advanced networking: provides client-server, server-server network
security using encryption and data integrity checking, and supports
enhanced user authentication services, using third-party security services
I-3 Copyright Oracle Corporation, 1998. All rights reserved.
Oracle8 Enterprise Edition
Advanced
networking
Parallel Server
Objects
Partitioning
OEM
Performance Pack
Oracle8: Database Administration I-5
......................................................................................................................................................
......................................................................................................................................................
Database Administrator Tasks
Database Administrator Tasks
Scope of the Course
This course is the first in a series of four courses that cover the core database
administrator tasks. The tasks covered in this course are:
• Managing database availability
• Planning and creating databases
• Managing the physical structures
• Managing storage based on the design specifications
• Managing database users, controlling and monitoring their actions
DBA Tasks Covered in Other Courses
The following tasks are discussed in other courses:
• Network administration in Oracle8: Network Administration
• Backup and recovery in Oracle8: Backup and Recovery Workshop
• Database tuning in Oracle8: Performance Tuning Workshop
I-4 Copyright Oracle Corporation, 1998. All rights reserved.
Managing database availability
Planning and creating databases
Managing physical structures
Managing storage based on design
Managing security
Network administration
Backup and recovery
Database tuning
Database Administrator Tasks
I-6 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Introduction
Course Schedule
The following is the recommended lesson schedule for this course:
Day 1
1 Oracle Architectural Components
2 Using Administration Tools
3 Managing an Oracle Instance
4 Creating a Database (lesson only, lab on Day 2)
Day 2
5 Creating Data Dictionary Views and Standard Packages
6 Maintaining the Control File
7 Maintaining Redo Log Files
8 Managing Tablespaces and Data Files
Day 3
9 Storage Structure and Relationships
10 Managing Rollback Segments
11 Managing Temporary Segments
12 Managing Tables
I-5 Copyright Oracle Corporation, 1998. All rights reserved.
Suggested Course Schedule
Start
Lesson 1
Lab 4
Lesson 9
Lesson 13
Lesson 18
Day
1
2
3
4
5
End
Lesson 4
Lab 8
Lab 12
Lab 17
Lesson 22
Oracle8: Database Administration I-7
......................................................................................................................................................
......................................................................................................................................................
Course Schedule
Day 4
13 Managing Indexes
14 Maintaining Data Integrity
15 Using Clusters and Index-Organized Tables
16 Loading and Reorganizing Data
17 Managing Users
Day 5
18 Managing Profiles
19 Managing Privileges
20 Managing Roles
21 Auditing
22 Using National Language Support
I-8 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Introduction
................................
1
Oracle Architectural
Components
1-2 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Lesson 1: Oracle Architectural Components
Instructor Note
Topic Timing
Lecture 60 minutes
Practice 15 minutes
Total 75 minutes
Oracle8: Database Administration 1-3
......................................................................................................................................................
......................................................................................................................................................
Objectives
Objectives
This lesson introduces the data Oracle server architecture by examining the
structures involved in establishing a new database connection and of several
familiar SQL commands.
Instructor Note
The purpose of this lesson is to introduce the processes, memory structures,
and files associated with an Oracle server. It is not intended to be an
extensive description of the Oracle architecture. Each component is
discussed in detail in the lessons throughout the course.
1-2 Copyright Oracle Corporation, 1998. All rights reserved.
Objectives
• Listing the structures involved in
connecting a user to an Oracle server
• Listing the stages in processing a query
• Listing the stages in processing a DML
statement
• Listing the stages in processing
COMMITS
1-4 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Lesson 1: Oracle Architectural Components
Overview
The Oracle server is an object-relational database management system that
provides an open, comprehensive, integrated approach to information
management.
Oracle Database Users
A database user can connect to an Oracle server in any one of the following
ways:
• Logging in directly to the host— that is, the machine running the Oracle
server (For example, a user connecting to a UNIX machine running
Oracle, and using Server Manager to access the database is logging in
directly to the host.)
• Using a two-tiered (client-server) connection, where the machine on
which the user is logged in is connected directly to the machine running
the Oracle server (For example, a user running a Developer/2000
application on a Windows 95 personal computer to access an Oracle
database residing on an NT server is using a two-tiered connection.)
• Using a three-tiered connection, where the user’s machine
communicates to an application or a network server, which in turn is
connected through a network to the machine running the Oracle server
(This may be the case when a user runs a browser on a network computer
to use an application residing on an NT server, that retrieves data from
an Oracle database running on a UNIX host.)
1-3 Copyright Oracle Corporation, 1998. All rights reserved.
The Oracle Server
Server
Application/
network
server
Users
Oracle server
Oracle8: Database Administration 1-5
......................................................................................................................................................
......................................................................................................................................................
Overview
Database users directly issue SQL commands using a tool such as SQL*Plus
or using an application that contains SQL statements. The Oracle server
processes these commands and returns the results to the users.
Oracle Database Administrators
Database administrators are responsible for maintaining the Oracle server so
that the server can process user requests. An understanding of the Oracle
architecture is necessary to effectively maintain it.
This course focuses on creating and maintaining an Oracle server where
users connect either by directly logging in to the machine running the Oracle
server or by using a client-server model.
1-6 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Lesson 1: Oracle Architectural Components
Connecting to a Database
The user who needs to interact with the Oracle server first needs to establish
a database connection. The following steps are carried out to connect to a
database:
• The user starts a tool such as SQL*Plus, or runs an application
developed using a tool such as Developer/2000 Forms, spawning a user
process. In a client-server model, the tool or the application runs on the
client machine.
• In the most basic configuration, when a user logs on to the Oracle server
by specifying a username, password, and a database, a process is created
on the machine that is running the Oracle server. This process is called a
server process. The server process communicates with the Oracle server
on behalf of the user process that runs on the client.
Connection
A connection is a communication pathway between a user process and an
Oracle server. If the user runs the tool or the application on the same
machine as the Oracle server, the communication pathway is established
using the interprocess communication mechanisms available on the
machine. If the user runs the tool on a client machine, network software is
used to communicate between the user and the Oracle server using the
network.
1-4 Copyright Oracle Corporation, 1998. All rights reserved.
Connecting to a Database
User
Client ServerServerprocess
Oracle serverUser
process
Oracle8: Database Administration 1-7
......................................................................................................................................................
......................................................................................................................................................
Connecting to a Database
Sessions
A session is a specific connection of a user to an Oracle server. The session
commences when the user is validated by the Oracle server, and it ends
when the user logs out or when there is an abnormal termination. For a given
database user, many concurrent sessions are possible if the user logs on from
many tools, applications, or terminals at the same time. Except for some
specialized database administration tools, starting a database session
requires that the Oracle server is available for use.
Troubleshooting
The Oracle error message “ORA-01034: Oracle not available” occurs when
a user or an application attempts to connect to an Oracle server that is not
available for use. To resolve this, the database administrator needs to start up
the Oracle server.
1-8 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Lesson 1: Oracle Architectural Components
A user process, also known as the client, has the following properties:
• It is created when a user executes a tool or an application such as
SQL*Plus, Server Manager, or a Developer/2000 application.
• It runs on the client—that is, on the machine where the user is directly
logged on.
• It is started when the tool is started and is terminated when the user exits,
or is forced to discontinue.
• The user process includes the user program interface (UPI).
• The UPI generates calls to the Oracle server whenever the user makes a
request.
1-5 Copyright Oracle Corporation, 1998. All rights reserved.
User Process
• Runs on the client machine
• Is spawned when a tool or an application
is invoked
• Runs the tool or application (SQL*Plus,
Server Manager, Oracle Enterprise
Manager, Developer/2000)
• Includes the User Program Interface (UPI)
• Generates calls to the Oracle server
Oracle8: Database Administration 1-9
......................................................................................................................................................
......................................................................................................................................................
Connecting to a Database
The server process has the following characteristics:
• It runs on the same machine as the Oracle server.
• In the simplest configuration known as a dedicated server, each server
process services only one user process. The server process is created
when the user requests a connection and is generally terminated when
the user disconnects.
• Each server process uses an area of memory called the Program Global
Area (PGA). The PGA is described in a subsequent section in this
lesson.
• The server process uses the Oracle Program Interface (OPI), which is
used to communicate with the Oracle server, at the request of the user
process.
• The server process returns status information and results to the user
process.
Note
If using a multithreaded server (MTS) configuration, it is possible for
multiple user processes to share server processes. MTS is covered in more
detail in the course, Oracle8: Network Administration.
1-6 Copyright Oracle Corporation, 1998. All rights reserved.
Server Process
• Runs on the server machine (host)
• Services a single user process in the
dedicated server configuration
• Uses an exclusive PGA
• Includes the Oracle Program Interface
(OPI)
• Processes calls generated by the client
• Returns results to the client
1-10 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Lesson 1: Oracle Architectural Components
Oracle server consists of an Oracle instance and an Oracle database. An
Oracle instance consists of a memory structure, called the System Global
Area (SGA), and background processes used by an Oracle server to manage
a database. An Oracle instance, which is identified by setting ORACLE_SID
at the operating system, can open and use only one database at any point in
time.
System Global Area
The memory structures of an Oracle instance are contained in a memory
region called the SGA, which contains data and control information for the
Oracle server. The SGA is allocated in the virtual memory of the computer
where the Oracle server resides. The SGA comprises several memory
structures, including:
• Shared pool: used to store information such as the most recently
executed SQL and the most recently used data from the data dictionary
• Database buffer cache: used to store the most recently used data
• Redo log buffer: used to register changes made to the database using the
instance
The purpose of these structures is discussed in detail in a later section.
1-7 Copyright Oracle Corporation, 1998. All rights reserved.
Oracle Instance
Background processes
An Oracle instance:
• Is a means to access an Oracle
database
• Always opens one and only one
database
Instance
SGA
Oracle8: Database Administration 1-11
......................................................................................................................................................
......................................................................................................................................................
Connecting to a Database
Background Processes
The background processes in an instance perform common functions that
are needed to service the requests from several concurrent users, without
compromising the integrity and performance of the whole system. Each
Oracle instance may use several background processes, depending on the
configuration, but every instance comprises these five background processes
by default:
• Database Writer (DBWR): responsible for writing changed data to the
database
• Log Writer (LGWR): records changes registered in the redo log buffer to
the database
• System Monitor (SMON): whose primary function is to check for
consistency and initiate recovery of the database when the database is
opened
• Process Monitor (PMON): cleans up the resources if one of the
processes fails
• Checkpoint process (CKPT): responsible for updating the database
status information whenever changes in the buffer cache are permanently
recorded in the database
The functions of each of these processes are covered in detail later in the
course.
Instructor Note
If you are using a UNIX server, demonstrate the following:
1 Log on to the server and run the following command, and showing the
background processes for the instance:
ps -ef | grep
2 Connect as system/manager using SQL*Plus.
3 Use the host command.
4 Execute ps (without any arguments) to show the user process and the
operating system process ID.
5 Execute the command in step 1 again, identify the server process, and
show that the parent process of this server is the sqlplus process
displayed in step 3.
1-12 Oracle8: Database Administration
......................................................................................................................................................
......................................................................................................................................................
Lesson 1: Oracle Architectural Components
An Oracle database, identified by the database name (DB_NAME),
represents the physical structures and is composed of operating system files.
Although it is possible to use a database name that is different from the
name of the instance, Oracle recommends that the same name be used for
ease of administration.
Database Files
The files constituting a database contain user data and the additional
information that is needed to ensure proper database operation.
An Oracle database consists of the following types of files:
• Data files: store the data dictionary, user objects, and before-images of
data that are modified by current transactions. A database has at least one
data file
• Redo log files: contain a record of changes made to the database to
ensure reconstruction of the data in case of failures (A database requires
at least two redo log files.)
• Control files: contain the information needed to maintain and verify
database integrity (A database needs at least one control file.)
1-8 Copyright Oracle Corporation, 1998. All rights reserved.
Oracle Database
Control
files
Redo log
files
Data filesPassword
file
Parameter
file
Archived
log files
Database
Các file đính kèm theo tài liệu này:
oracle8_database_administration_volume_1_instruction_guide00001_6222.pdf
