Oracle Database 10G Dba Handbook

by Loney, Kevin; Bryla, Bob

• ISBN13: 9780072231458
• ISBN10: 0072231459
• Edition: 1st
• Format: Paperback
• Copyright: 2005-04-14
• Publisher: McGraw-Hill Education

Summary

Everything a DBA needs to know in one volume--this is the must-have reference for anyone working with the Oracle database, and it's been fully revised and updated for Oracle Database 10g. Co-author Kevin Loney is the all-time, best-selling Oracle Press author.

Author Biography

Kevin Loney (Wilmington, DE) is a consultant for TUSC and a veteran Oracle developer and DBA. He is among the most renowned members of the Oracle Professional community and is a regular presenter at IOUG-A, Oracle OpenWorld, and regional User Group conferences. Kevin is the coauthor of several of our best-selling titles such as The Complete Reference and the DBA Handbook. Bob Bryla (Plattville, WI) has over 20 years of experience in MIS, ranging from database and data warehouse design to system administration on multiple hardware platforms. Bob is currently lead designer and data analyst for Lands’ End’s online store database and handles the DBA activities on Lands’ End Oracle Internet databases. Bob has also technically reviewed and authored or coauthored several well-reviewed Oracle certification titles for Sybex.

Table of Contents

Acknowledgments	xix
Introduction	xxi
PART I Database Architecture
Getting Started with the Oracle Architecture	3	(70)
An Overview of Databases and Instances	4	(2)
Databases	4	(1)
Instances	5	(1)
Oracle Logical Storage Structures	6	(2)
Tablespaces	6	(1)
Blocks	7	(1)
Extents	7	(1)
Segments	7	(1)
Oracle Logical Database Structures	8	(17)
Tables	8	(7)
Constraints	15	(3)
Indexes	18	(2)
Views	20	(1)
Users and Schemas	21	(1)
Profiles	22	(1)
Sequences	22	(1)
Synonyms	22	(1)
PL/SQL	23	(1)
External File Access	24	(1)
Database Links and Remote Databases	24	(1)
Oracle Physical Storage Structures	25	(5)
Datafiles	25	(1)
Redo Log Files	26	(1)
Control Files	27	(1)
Archived Log Files	27	(1)
Initialization Parameter Files	28	(1)
Alert and Trace Log Files	28	(1)
Backup Files	29	(1)
Oracle Managed Files	29	(1)
Password Files	29	(1)
Multiplexing Database Files	30	(2)
Automatic Storage Management	30	(1)
Manual Multiplexing	30	(2)
Oracle Memory Structures	32	(6)
System Global Area	33	(2)
Program Global Area	35	(1)
Software Code Area	35	(1)
Background Processes	36	(2)
Backup/Recovery Overview	38	(2)
Export/Import	38	(1)
Offline Backups	39	(1)
Online Backups	39	(1)
Rman	39	(1)
Security Capabilities	40	(1)
Privileges and Roles	40	(1)
Auditing	40	(1)
Fine-grained Auditing	41	(1)
Virtual Private Database	41	(1)
Label Security	41	(1)
Real Application Clusters	41	(1)
Oracle Streams	42	(1)
Oracle Enterprise Manager	42	(1)
Oracle Initialization Parameters	43	(4)
Basic Initialization Parameters	43	(4)
Advanced Initialization Parameters	47	(1)
Software Installation	47	(26)
Overview of Licensing and Installation Options	49	(1)
Using OUI to Install the Oracle Software	49	(1)
Using the DBCA to Create a Database	50	(18)
Manually Creating a Database	68	(5)
Upgrading to Oracle Database 10g	73	(10)
Choosing an Upgrade Method	75	(1)
Before Upgrading	76	(1)
Using the Database Upgrade Assistant	76	(1)
Performing a Manual Direct Upgrade	77	(3)
Using Export and Import	80	(1)
Export and Import Versions to Use	80	(1)
Performing the Upgrade	81	(1)
Using the Data-Copying Method	81	(1)
After Upgrading	82	(1)
Planning and Managing Tablespaces	83	(18)
Tablespace Architecture	84	(11)
Tablespace Types	84	(6)
Optimal Flexible Architecture	90	(5)
Oracle Installation Tablespaces	95	(1)
System	95	(1)
Sysaux	95	(1)
Temp	95	(1)
Undotbs1	96	(1)
Users	96	(1)
Example	96	(1)
Segment Segregation	96	(5)
Physical Database Layouts and Storage Management	101	(50)
Traditional Disk Space Storage	102	(27)
Resizing Tablespaces and Datafiles	102	(20)
Moving Datafiles	122	(4)
Moving Online Redo Log Files	126	(2)
Moving Control Files	128	(1)
Automatic Storage Management	129	(22)
ASM Architecture	130	(1)
Creating an ASM Instance	130	(3)
ASM Instance Components	133	(1)
ASM Dynamic Performance Views	134	(1)
ASM Filename Formats	135	(2)
ASM File Types and Templates	137	(1)
Administering ASM Disk Groups	137	(14)
PART II Database Management
Developing and Implementing Applications	151	(44)
Tuning by Design: Best Practices	152	(12)
Do As Little As Possible	152	(3)
Do It As Simply As Possible	155	(2)
Tell the Database What It Needs to Know	157	(1)
Maximize the Throughput in the Environment	157	(2)
Divide and Conquer Your Data	159	(1)
Test Correctly	160	(2)
Standard Deliverables	162	(2)
Resource Management and Stored Outlines	164	(17)
Implementing the Database Resource Manager	165	(4)
Implementing Stored Outlines	169	(3)
Sizing Database Objects	172	(8)
Using Temporary Tables	180	(1)
Supporting Tables Based on Abstract Datatypes	181	(7)
Using Object Views	182	(3)
Security for Abstract Datatypes	185	(2)
Indexing Abstract Datatype Attributes	187	(1)
Quiescing and Suspending the Database	188	(2)
Supporting Iterative Development	190	(2)
Iterative Column Definitions	190	(1)
Forcing Cursor Sharing	191	(1)
Managing Package Development	192	(3)
Generating Diagrams	192	(1)
Space Requirements	192	(1)
Tuning Goals	192	(1)
Security Requirements	192	(1)
Data Requirements	193	(1)
Version Requirements	193	(1)
Execution Plans	193	(1)
Acceptance Test Procedures	194	(1)
The Testing Environment	194	(1)
Monitoring Space Usage	195	(46)
Common Space Management Problems	196	(2)
Running Out of Free Space in a Tablespace	197	(1)
Insufficient Space for Temporary Segments	197	(1)
Too Much or Too Little Undo Space Allocated	197	(1)
Fragmented Tablespaces and Segments	198	(1)
Oracle Segments, Extents, and Blocks	198	(4)
Data Blocks	198	(3)
Extents	201	(1)
Segments	202	(1)
Data Dictionary Views and Dynamic Performance Views	202	(5)
DBA_Tablespaces	203	(1)
DBA_Segments	203	(1)
DBA_Extents	204	(1)
DBA_Free_Space	204	(1)
DBA_LMT_Free_Space	205	(1)
DBA_Thresholds	205	(1)
DBA_Outstanding_Alerts	206	(1)
DBA_Alert_History	206	(1)
V$Alert_Types	206	(1)
V$Undostat	206	(1)
V$Object_Usage	207	(1)
V$Sort_Segment	207	(1)
V$Tempseg_Usage	207	(1)
Space Management Methodologies	207	(7)
Locally Managed Tablespaces	207	(2)
Using OMF to Manage Space	209	(1)
Bigfile Tablespaces	209	(2)
Automatic Storage Management	211	(1)
Undo Management Considerations	212	(2)
SYSAUX Monitoring and Usage	214	(1)
Archived Redo Log File Management	215	(1)
Built-in Space Management Tools	216	(12)
Segment Advisor	216	(3)
Undo Advisor and the Automatic Workload Repository	219	(3)
Index Usage	222	(2)
Space Usage Warning Levels	224	(1)
Resumable Space Allocation	225	(3)
OS Space Management	228	(1)
Space Management Scripts	228	(2)
Segments That Cannot Allocate Additional Extents	228	(1)
Used and Free Space by Tablespace and Datafile	229	(1)
Automating and Streamlining the Notification Process	230	(11)
Using DBMS_Scheduler	230	(1)
OEM Job Control and Monitoring	230	(11)
Managing Transactions with Undo Tablespaces	241	(38)
Transaction Basics	242	(1)
Undo Basics	243	(1)
Rollback	243	(1)
Read Consistency	243	(1)
Database Recovery	244	(1)
Flashback Operations	244	(1)
Managing Undo Tablespaces	244	(16)
Creating Undo Tablespaces	244	(8)
Undo Tablespace Dynamic Performance Views	252	(1)
Undo Tablespace Initialization Parameters	253	(1)
Multiple Undo Tablespaces	254	(3)
Sizing and Monitoring the Undo Tablespace	257	(3)
Read Consistency vs. Successful DML	260	(1)
Flashback Features	260	(18)
Flashback Query	261	(1)
DBMS_Flashback	262	(2)
Flashback Table	264	(7)
Flashback Version Query	271	(4)
Flashback Transaction Query	275	(3)
Migrating to Automatic Undo Management	278	(1)
Database Tuning	279	(32)
Tuning Application Design	280	(4)
Effective Table Design	281	(1)
Distribution of CPU Requirements	281	(2)
Effective Application Design	283	(1)
Tuning SQL	284	(6)
Impact of Order on Load Rates	285	(1)
Additional Indexing Options	286	(2)
Generating Explain Plans	288	(2)
Tuning Memory Usage	290	(4)
Specifying the Size of the SGA	293	(1)
Using the Cost-Based Optimizer	293	(1)
Tuning Data Access	294	(4)
Locally Managed Tablespaces	294	(1)
Identifying Chained Rows	295	(1)
Increasing the Oracle Block Size	296	(1)
Using Index-Organized Tables	297	(1)
Tuning Data Manipulation	298	(5)
Bulk Inserts: Using the SQL*Loader Direct Path Option	298	(2)
Bulk Data Moves---Using External Tables	300	(1)
Bulk Inserts: Common Traps and Successful Tricks	300	(2)
Bulk Deletes: The truncate Command	302	(1)
Using Partitions	303	(1)
Tuning Physical Storage	303	(1)
Using Raw Devices	303	(1)
Reducing Network Traffic	304	(3)
Replication of Data	304	(2)
Using Remote Procedure Calls	306	(1)
Using STATSPACK and the Automatic Workload Repository	307	(2)
Managing Snapshots	308	(1)
Managing Baselines	308	(1)
Generating AWR Reports	309	(1)
Running the Automatic Database Diagnostic Monitor Reports	309	(1)
Tuning Solutions	309	(2)
Using STATSPACK	311	(12)
Installing STATSPACK	312	(2)
Security of the PERFSTAT Account	312	(1)
Post-installation	313	(1)
Gathering Statistics	314	(2)
Running the Statistics Report	316	(5)
Managing the STATSPACK Data	321	(1)
Deinstalling STATSPACK	322	(1)
Database Security and Auditing	323	(66)
Non-Database Security	325	(1)
Database Authentication Methods	325	(13)
Database Authentication	326	(1)
Database Administrator Authentication	326	(3)
Operating System Authentication	329	(1)
Network Authentication	329	(2)
3-Tier Authentication	331	(1)
Client-Side Authentication	332	(1)
Oracle Identity Management	332	(1)
User Accounts	333	(5)
Database Authorization Methods	338	(37)
Profile Management	338	(6)
System Privileges	344	(2)
Object Privileges	346	(5)
Creating, Assigning, and Maintaining Roles	351	(7)
Using a VPD to Implement Application Security Policies	358	(17)
Auditing	375	(10)
Auditing Locations	376	(1)
Statement Auditing	376	(5)
Privilege Auditing	381	(1)
Schema Object Auditing	381	(1)
Fine-Grained Auditing	382	(2)
Auditing-Related Data Dictionary Views	384	(1)
Protecting the Audit Trail	384	(1)
Data Encryption Techniques	385	(4)
PART III High Availability
Real Application Clusters	389	(46)
Overview of Real Application Clusters	390	(3)
Hardware Configuration	391	(1)
Software Configuration	391	(1)
Network Configuration	391	(1)
Disk Storage	392	(1)
Installation and Setup	393	(26)
Operating System Configuration	393	(4)
Software Installation	397	(22)
RAC Database Characteristics	419	(3)
Server Parameter File Characteristics	419	(1)
RAC-Related Initialization Parameters	420	(1)
Dynamic Performance Views	420	(2)
RAC Maintenance	422	(13)
Starting Up a RAC Database	423	(1)
Redo Logs in a RAC Environment	423	(1)
Undo Tablespaces in a RAC Environment	424	(1)
Failover Scenarios and TAF	424	(1)
RAC Node Failure Scenario	425	(7)
Tuning a RAC Node Database	432	(1)
Tablespace Management	433	(2)
Backup and Recovery Options	435	(26)
Capabilities	436	(1)
Logical Backups	436	(1)
The Data Pump Export/Import Process	436	(1)
Physical Backups	437	(2)
Offline Backups	437	(1)
Online Backups	438	(1)
Using Data Pump Export and Import	439	(7)
Creating a Directory	439	(1)
Data Pump Export Options	440	(2)
Starting a Data Pump Export Job	442	(4)
Data Pump Import Options	446	(11)
Starting a Data Pump Import Job	448	(4)
Comparing Data Pump Export/Import to Export/Import	452	(1)
Implementing Offline Backups	453	(1)
Implementing Online Backups	454	(3)
Integration of Backup Procedures	457	(4)
Integration of Logical and Physical Backups	458	(1)
Integration of Database and Operating System Backups	459	(2)
Using Recovery Manager (RMAN)	461	(56)
RMAN Features and Components	462	(5)
RMAN Components	462	(2)
RMAN vs. Traditional Backup Methods	464	(1)
Backup Types	465	(2)
Overview of RMAN Commands and Options	467	(11)
Frequently Used Commands	467	(2)
Setting Up a Repository	469	(1)
Registering a Database	470	(2)
Persisting RMAN Settings	472	(4)
Initialization Parameters	476	(1)
Data Dictionary and Dynamic Performance Views	476	(2)
Backup Operations	478	(20)
Full Database Backups	478	(3)
Tablespace	481	(4)
Datafiles	485	(1)
Image Copies	485	(1)
Control File, SPFILE Backup	486	(1)
Archived Redo Logs	487	(1)
Incremental Backups	487	(3)
Incrementally Updated Backups	490	(1)
Incremental Backup Block Change Tracking	491	(3)
Backup Compression	494	(2)
Using a Flash Recovery Area	496	(1)
Validating Backups	496	(2)
Recovery Operations	498	(13)
Block Media Recovery	498	(1)
Restoring a Control File	499	(1)
Restoring a Tablespace	499	(3)
Restoring a Datafile	502	(2)
Restoring an Entire Database	504	(4)
Validating Restore Operations	508	(2)
Point in Time Recovery	510	(1)
Miscellaneous Operations	511	(6)
Cataloging Other Backups	511	(1)
Catalog Maintenance	511	(3)
Report and List	514	(3)
Oracle Data Guard	517	(22)
Data Guard Architecture	518	(2)
Physical vs. Logical Standby Databases	519	(1)
Data Protection Modes	519	(1)
LOG_ARCHIVE_DEST_n Parameter Attributes	520	(2)
Creating the Standby Database Configuration	522	(7)
Preparing the Primary Database	522	(4)
Creating Logical Standby Databases	526	(3)
Using Real-time Apply	529	(1)
Managing Gaps in Archive Log Sequences	530	(1)
Managing Roles---Switchovers and Failovers	530	(5)
Switchovers	530	(1)
Switchovers to Physical Standby Databases	531	(1)
Switchovers to Logical Standby Databases	532	(1)
Failovers to Physical Standby Databases	533	(1)
Failovers to Logical Standby Databases	534	(1)
Administering the Databases	535	(4)
Startup and Shutdown of Physical Standby Databases	535	(1)
Managing Datafiles in Data Guard Environments	536	(1)
Performing DDL on a Logical Standby Database	537	(2)
Miscellaneous High Availability Features	539	(18)
The flashback table Command	540	(4)
Privileges Required	540	(1)
Recovering Dropped Tables	541	(1)
Flashing Back to SCN or Timestamp	542	(2)
The flashback database Command	544	(2)
Using LogMiner	546	(5)
How LogMiner Works	547	(1)
Extracting the Data Dictionary	547	(1)
Analyzing One or More Redo Log Files	548	(3)
LogMiner Features Introduced in Oracle Database 10g	551	(1)
Online Object Reorganizations	551	(6)
Creating Indexes Online	552	(1)
Rebuilding Indexes Online	552	(1)
Coalescing Indexes Online	552	(1)
Rebuilding Index-Organized Tables Online	552	(1)
Redefining Tables Online	552	(5)
PART IV Networked Oracle
Oracle Net	557	(32)
Overview of Oracle Net	558	(8)
Connect Descriptors	561	(1)
Service Names	562	(1)
Replacing tnsnames.ora with the Oracle Internet Directory	562	(1)
Listeners	563	(3)
Using the Oracle Net Configuration Assistant	566	(6)
Configuring the Listener	567	(5)
Using the Oracle Net Manager	572	(1)
Starting the Listener Server Process	573	(1)
Controlling the Listener Server Process	574	(8)
The Oracle Connection Manager	576	(1)
Using Connection Manager	577	(4)
Directory Naming with Oracle Internet Directory	581	(1)
Using Easy Connect Naming	582	(1)
Using Database Links	583	(2)
Tuning Oracle Net	585	(4)
Limiting Resource Usage	586	(1)
Debugging Connection Problems	586	(3)
Managing Large Databases	589	(54)
Creating Tablespaces in a VLDB Environment	591	(7)
Bigfile Tablespace Basics	591	(1)
Creating and Modifying Bigfile Tablespaces	592	(1)
Bigfile Tablespace ROWID Format	593	(1)
DBMS_ROWID and Bigfile Tablespaces	593	(3)
Using DBVERIFY with Bigfile Tablespaces	596	(1)
Bigfile Tablespace Initialization Parameter Considerations	597	(1)
Bigfile Tablespace Data Dictionary Changes	598	(1)
Advanced Oracle Table Types	598	(36)
Index Organized Tables	599	(1)
Global Temporary Tables	599	(2)
External Tables	601	(2)
Partitioned Tables	603	(31)
Materialized Views	634	(1)
Using Bitmap Indexes	634	(2)
Understanding Bitmap Indexes	634	(1)
Using Bitmap Indexes	635	(1)
Using Bitmap Join Indexes	635	(1)
Oracle Data Pump	636	(7)
Data Pump Export	637	(1)
Data Pump Import	638	(1)
Using Transportable Tablespaces	638	(5)
Managing Distributed Databases	643	(38)
Remote Queries	645	(1)
Remote Data Manipulation: Two-Phase Commit	646	(1)
Dynamic Data Replication	647	(1)
Managing Distributed Data	648	(28)
The Infrastructure: Enforcing Location Transparency	649	(4)
Managing Database Links	653	(1)
Managing Database Triggers	654	(2)
Managing Materialized Views	656	(4)
Using DBMS_MVIEW and DBMS_ADVISOR	660	(11)
What Kind of Refreshes Can Be Performed?	671	(3)
Using Materialized Views to Alter Query Execution Paths	674	(2)
Managing Distributed Transactions	676	(1)
Resolving In-Doubt Transactions	676	(1)
Commit Point Strength	677	(1)
Monitoring Distributed Databases	677	(1)
Tuning Distributed Databases	678	(3)
A Password Verify Function	681	(4)
Index	685