Expert Oracle Database 10g Administration

by Alapati, Sam R.

• ISBN13: 9781590594513
• ISBN10: 1590594517
• Format: Paperback
• Copyright: 2005-09-19
• Publisher: SPINGER-VERLAG NEW YORK, INCORPORATED

Summary

This guide to the administration and management of the Oracle database is revised and updated for Oracle 10g, covering all the new 10g management and performance tools and provides essential primers on Unix, Linux and Windows NT administration and on core SQL and PL/SQL programming techniques.

Table of Contents

About the Author	xxxiii
About the Technical Reviewer	xxxiv
Acknowledgments	xxxv
Introduction	xxxvii
PART 1 Background, Data Modeling, and UNIX/Linux
CHAPTER 1 The Oracle DBA's World	3	(16)
The Oracle DBA's Role	3	(5)
Different DBA Job Classifications	8	(1)
Types of Databases	8	(1)
Background and Training	9	(6)
The Daily Routine of a Typical Oracle DBA	15	(1)
Some General Advice	16	(3)
CHAPTER 2 Relational Database Modeling and Database Design	19	(24)
Relational Databases: A Brief Introduction	19	(1)
The Relational Database Model	20	(3)
Relational Database Life Cycle	23	(15)
Reverse-Engineering a Database	38	(1)
Object-Relational and Object Databases	38	(5)
CHAPTER 3 Essential UNIX (and Linux) for the Oracle DBA	43	(56)
Overview of UNIX and Linux Operating Systems	43	(2)
Understanding the UNIX Shell(s)	45	(3)
Overview of Basic UNIX Commands	48	(9)
Navigating Files and Directories in UNIX	57	(6)
Writing and Editing Files with the vi Editor	63	(2)
Extracting and Sorting Text	65	(3)
Shell Scripting	68	(6)
Dealing with UNIX Processes	74	(1)
UNIX System Administration and the Oracle DBA	75	(10)
Disks and Storage in UNIX	85	(3)
RAID Systems	88	(4)
New Storage Technologies	92	(7)
PART 2 Oracle Database 10g Architecture, Schema, and Transaction Management
CHAPTER 4 Introduction to the Oracle Database 10g Architecture	99	(46)
Oracle Database Structures	99	(14)
Oracle Processes	113	(6)
Oracle Memory Structures	119	(11)
A Simple Oracle Database Transaction	130	(1)
Data Consistency and Data Concurrency	131	(2)
Backup and Recovery Architecture	133	(2)
The Oracle Data Dictionary and the Dynamic Performance Views	135	(2)
Talking to the Database	137	(2)
Oracle Utilities	139	(1)
Scheduling and Resource-Management Tools	139	(1)
Automatic Database Management	140	(1)
Common Manageability Infrastructure	141	(2)
Efficient Managing and Monitoring	143	(2)
CHAPTER 5 Schema Management	145	(80)
Types of SQL Statements	145	(3)
Oracle Schemas	148	(1)
Creating and Managing Tablespaces	149	(26)
Oracle Tables	175	(7)
Special Oracle Tables	182	(14)
Oracle Indexes	196	(6)
Managing Database Integrity Constraints	202	(5)
Using Views	207	(2)
Using Materialized Views	209	(5)
Using the SQL Access Advisor	214	(6)
Using Synonyms	220	(2)
Using Sequences	222	(1)
Using Triggers	223	(1)
Viewing Object Information	224	(1)
CHAPTER 6 Oracle Transaction Management	225	(54)
Oracle Transactions	225	(3)
Transaction Properties	228	(1)
Transaction Concurrency Control	229	(2)
Isolation Levels and the ISO Transaction Standard	231	(1)
Oracle's Isolation Levels	232	(3)
Implementing Oracle's Concurrency Control	235	(8)
Using Undo Data to Provide Read Consistency	243	(11)
Flashback Error Correction Using Undo Data	254	(2)
Flashback Using the DBMS_FLASHBACK Package	256	(5)
Flashback Transaction Query	261	(6)
Discrete Transactions	267	(1)
Autonomous Transactions	267	(2)
Resumable Space Allocation	269	(4)
Managing Long Transactions	273	(6)
PART 3 Installing Oracle Database 10g, and Creating and Upgrading Databases
CHAPTER 7 Installing the Oracle Database 10g RDBMS	279	(36)
Installing Oracle	279	(2)
Following the Optimal Flexible Architecture	281	(7)
Performing Preinstallation Tasks	288	(12)
A Final Checklist for the Installation	300	(9)
After the Installation	309	(3)
Uninstalling Oracle	312	(3)
CHAPTER 8 Upgrading to Oracle Database 10g	315	(14)
Routes to Oracle Database 10g	315	(1)
Upgrade Methods and Tools	315	(3)
Upgrading with the DBUA	318	(4)
Upgrading Manually	322	(6)
After the Upgrade	328	(1)
CHAPTER 9 Creating an Oracle Database	329	(62)
Getting Ready to Create the Database	329	(4)
Creating the Parameter File	333	(25)
Creating a New Database	358	(16)
Using a Server Parameter File (SPFILE)	374	(4)
Starting Up and Shutting Down the Database from SQL*Plus	378	(13)
PART 4 Connectivity and User Management
CHAPTER 10 Connectivity and Networking	391	(30)
Oracle Networking and Database Connectivity	391	(2)
Networking Concepts: How Oracle Networking Works	393	(3)
Establishing Oracle Connectivity	396	(1)
The Oracle Client	397	(2)
The Instant Client	399	(1)
The Listener and Connectivity	400	(5)
Naming and Connectivity	405	(11)
Oracle and Java Database Connectivity	416	(5)
CHAPTER 11 User Management and Database Security	421	(70)
Managing Users	422	(9)
The Database Resource Manager	431	(11)
Controlling Access to Data	442	(19)
Auditing Database Usage	461	(10)
Authenticating Users	471	(5)
Enterprise User Security	476	(6)
Database Security Dos and Don'ts	482	(9)
CHAPTER 12 Using SQL*Plus and iSQL*Plus	491	(48)
Starting a SQL*Plus Session	491	(4)
Exiting SQL*Plus	495	(1)
SQL*Plus and SQL Commands	496	(12)
Key SQL*Plus "Working" Commands	508	(4)
Commands for Formatting SQL*Plus Output and Creating Reports	512	(2)
Creating Command Files in SQL*Plus	514	(5)
Editing Within SQL*Plus	519	(5)
Key SQL*Plus Database Administration Commands	524	(1)
Using SQL to Generate SQL	525	(1)
iSQL*Plus	526	(13)
PART 5 Data Loading, Backup, and Recovery
CHAPTER 13 Loading and Transforming Data	539	(50)
An Overview of Extraction, Transformation, and Loading	539	(2)
Using the SQL*Loader Utility	541	(18)
Using External Tables to Load Data	559	(11)
Transforming Data	570	(13)
Using Oracle Streams for Replication and Information Sharing	583	(6)
CHAPTER 14 Using Data Pump Export and Import	589	(42)
Introduction to the Data Pump Technology	589	(9)
Performing Data Pump Exports and Imports	598	(23)
Monitoring a Data Pump Job	621	(1)
Using the Data Pump API	622	(1)
Transportable Tablespaces	623	(8)
CHAPTER 15 Backing Up Databases	631	(68)
Backing Up Oracle Databases	631	(9)
Examining the Flash Recovery Area	640	(8)
The Recovery Manager (RMAN)	648	(31)
Backing Up the Control File	679	(1)
The Oracle Backup Tool	680	(6)
User-Managed Backups	686	(6)
Database Corruption Detection	692	(3)
Enhanced Data Protection for Disaster Recovery	695	(4)
CHAPTER 16 Database Recovery	699	(60)
Types of Database Failures	699	(2)
The Oracle Recovery Process	701	(6)
Performing Recovery with RMAN	707	(4)
Typical Media Recovery Scenarios	711	(15)
Cloning a Database	726	(4)
Techniques for Granular Recovery	730	(6)
Flashback Techniques and Recovery	736	(14)
Using Restore Points	750	(2)
Repairing Data Corruption and Trial Recovery	752	(2)
Troubleshooting Recovery Errors	754	(5)
PART 6 Managing the Operational Oracle Database
CHAPTER 17 Automatic Management and Online Capabilities	759	(64)
The Automatic Database Diagnostic Monitor (ADDM)	759	(15)
Automatic Shared Memory Management	774	(6)
Automatic Optimizer Statistics Collection	780	(3)
Automatic Storage Management	783	(16)
Automatic Space Management	799	(12)
Online Capabilities of Oracle Database 10g	811	(12)
CHAPTER 18 Managing and Monitoring the Operational Database	823	(60)
Types of Oracle Performance Statistics	823	(5)
Server-Generated Alerts	828	(6)
The Automatic Workload Repository (AWR)	834	(11)
Active Session History (ASH)	845	(4)
The Management Advisory Framework	849	(5)
Working with the Undo and the MTTR Advisors	854	(1)
Managing the Online Redo Logs	855	(3)
Managing Database Links	858	(2)
Copying Files with the Database Server	860	(2)
Mapping Oracle Files to Physical Devices	862	(1)
Using the Oracle Scheduler	863	(20)
CHAPTER 19 Using Oracle Enterprise Manager	883	(26)
Oracle Enterprise Manager	883	(3)
OEM Architecture and Components	886	(1)
OEM Database Control	886	(13)
OEM Grid Control	899	(10)
CHAPTER 20 Managing Oracle Databases on Windows and Linux Systems	909	(28)
Oracle Database 10g and Windows	909	(3)
Essential Differences Between Managing Oracle on Windows and UNIX	912	(7)
Installing Oracle Database 10g on a Windows System	919	(1)
The Windows Registry	920	(1)
Managing Oracle on Windows Systems	921	(9)
Uninstalling Oracle on Windows	930	(1)
Oracle and Linux	931	(6)
PART 7 Performance Tuning
CHAPTER 21 Improving Database Performance: SQL Query Optimization	937	(64)
An Approach to Oracle Performance Tuning	937	(2)
Optimizing Oracle Query Processing	939	(4)
Query Optimization and the Oracle Cost-Based Optimizer	943	(14)
Writing Efficient SQL	957	(10)
How the DBA Can Help Improve SQL Processing	967	(7)
SQL Performance Tuning Tools	974	(1)
Using the EXPLAIN PLAN	974	(20)
The SQL Tuning Advisor	994	(5)
A Simple Approach to Tuning SQL Statements	999	(2)
CHAPTER 22 Performance Tuning: Tuning the Instance	1001	(82)
An Introduction to Instance Tuning	1001	(2)
Automatic Performance Tuning vs. Dynamic Performance Views	1003	(1)
Tuning Oracle Memory	1004	(20)
Evaluating System Performance	1024	(6)
Measuring I/O Performance	1030	(2)
Measuring Instance Performance	1032	(34)
A Simple Approach to Instance Tuning	1066	(17)
PART 8 The Data Dictionary, Dynamic Views, and the Oracle-Supplied Packages
CHAPTER 23 The Oracle Data Dictionary and the Dynamic Performance Views	1083	(62)
The Oracle Data Dictionary	1083	(1)
Using the Static Data Dictionary Views	1084	(31)
Using the Dynamic Performance Views	1115	(30)
CHAPTER 24 Using Oracle PL/SQL Packages	1145	(38)
Overview of the Oracle-Supplied PUSQL Packages	1145	(1)
DBMS_FILE_TRANSFER	1146	(2)
DBMS_MONITOR	1148	(1)
UTL_COMPRESS	1148	(1)
UTL_MAIL	1149	(1)
DBMS_TDB	1150	(1)
DBMS_JOB	1151	(2)
DBMS_APPLICATION_INFO	1153	(2)
DBMS_CRYPTO	1155	(1)
DBMS_SESSION	1155	(1)
DBMS_SYSTEM	1156	(3)
DBMS_OUTPUT	1159	(1)
DBMS_REPAIR	1160	(2)
DBMS_OUTLN and DBMS OUTLN EDIT	1162	(1)
DBMS_SPACE	1163	(1)
DBMS_SPACE_ADMIN	1164	(1)
DBMS_PROFILER	1165	(2)
DBMS_ERRLOG	1167	(1)
UTL_FILE	1168	(4)
UTL_SMTP	1172	(1)
DBMS_SHARED_POOL	1173	(1)
DBMS_WM	1174	(1)
DBMS_RLMGR	1175	(1)
Oracle Packages in Earlier Chapters	1175	(8)
APPENDIX A Oracle Database 10g SQL and PL/SQL: A Brief Primer	1183	(34)
The Oracle Database 10g Sample Schemas	1183	(2)
Oracle Data Types	1185	(1)
SQL	1186	(15)
Abstract Data Types	1201	(2)
PL/SOL	1203	(4)
Using Cursors	1207	(2)
Procedures, Functions, and Packages	1209	(1)
Oracle XML DB	1210	(4)
Oracle and Java	1214	(3)
INDEX	1217