Beginning Oracle Database 11g Administration (eBook)

Iggy Fernandez has a rich history of working with Oracle Database in many capacities. He is the author of Beginning Oracle Database 11g Administration (Apress, 2009) and teaches Oracle database administration at University of Washington Professional & Continuing Education. He was the president of the Northern California Oracle Users Group (NoCOUG), the longest running and most active Oracle users group in the world. He writes regularly for the NoCOUG Journal and has been its editor for many years. Iggy's favorite topics are database performance and SQL performance, and he regularly speaks on these topics at Oracle conferences. He has a lot of opinions but is willing to change them when confronted with fresh facts. His favorite quote is "A foolish consistency is the hobgoblin of little minds, adored by little statesmen and philosophers and divines - Speak what you think now in hard words, and to-morrow speak what to-morrow thinks in hard words again, though it contradict every thing you said to-day." - Ralph Waldo Emerson. You can follow him online at http://iggyfernandez.wordpress.com/.

Contents at a Glance 5
Contents 7
Foreword 17
About the Author 18
About the Technical Reviewer 19
Acknowledgments 20
Introduction 21
Who This Book Is For 22
How This Book Is Structured 22
Source Code and Updates 27
Errata 27
Contacting the Author 27
Database Concepts 28
Relational Database Management Systems 29
What Is a Database? 30
What Is a Relational Database? 31
What Is a Database Management System? 40
What Makes a Relational Database Management 44
System Relational? 44
Summary 45
Exercises 45
Further Reading 46
SQL and PL/SQL 47
Railroad Diagrams 49
Types of SQL 51
Embedded SQL 55
SQL*Plus and SQL Developer 56
Criticisms of SQL 59
Introduction to PL/SQL 64
Summary 72
Exercises 74
Further Reading 74
Oracle Architecture 76
Database vs. Instance 78
Database 78
Instance 81
Life Cycle of a Database Session 84
Summary 85
Exercises 87
Further Reading 87
Database Implementation 88
Planning 89
Licensing 89
Architectural Choices 94
Sizing 97
Summary 101
Exercises 102
Further Reading 103
Software Installation 104
Oracle Technology Network 104
The Forgotten Manuals 105
Prerequisites and Preinstallation Requirements 105
Client Installations 106
Server Installations 110
Oracle Database Examples 114
Perl 114
Summary 115
Exercises 116
Further Reading 116
Database Creation 117
Next-Next-Next Click Finish
Configuring a Listener 119
Creating and Configuring a Database 123
Post-Creation Tasks 142
Other Methods of Database Creation 142
Basic Database Administration Tasks 145
Summary 151
Exercises 151
Further Reading 152
Physical Database Design 153
Indexes 154
Partitions 159
Denormalization and Materialized Views 166
Clusters 167
Summary 169
Exercises 170
Further Reading 171
User Management and Data Loading 173
Schemas 173
User Management 180
Data Loading 190
Summary 201
Exercises 202
Further Reading 203
Database Support 204
Taking Control 205
Enterprise Manager 206
SQL Developer 209
Remote Diagnostic Agent 212
Dictionary Tables and Views 214
Third-Party Tools 218
Summary 219
Exercises 219
Further Reading 220
Monitoring 221
Monitoring Database Availability 222
Monitoring Changes 224
Monitoring Security 225
Monitoring Backups 227
Monitoring Growth 227
Monitoring Workload 229
Monitoring Performance 233
Monitoring Capacity 236
Summary 239
Exercises 240
Further Reading 240
Fixing Problems 242
Systematic Five-Step Problem-Solving Method 243
The Book We All Want and Best Practices for 245
Problem Management 245
Real-Life Example—Unresponsive Listener 246
Incident Management vs. Problem Management 250
Internet Resources 251
Working with Oracle Support 255
Error Codes 259
Four Errors 261
Summary 271
Exercises 272
Further Reading 273
Backups 274
Why Do You Need Backups? 275
Horror Stories 276
Types of Backup 277
Practical Demonstration: Physical Backups 280
Practical Demonstration: Logical Backups 286
Common RMAN Commands 288
The “Easy” Button 292
Summary 295
Exercises 295
Further Reading 296
Recovery 298
Horror Stories 298
Types of Recovery 299
Flashback Technology 302
LogMiner 308
Data Recovery Advisor 310
Recovery Using Enterprise Manager 317
Documentation and Testing 320
Summary 320
Exercises 321
Further Reading 322
Database Maintenance 324
The Maintenance Plan 324
Backups 325
Statistics 327
Archiving and Purging 329
Log File Maintenance 332
Auditing 335
User Management 338
Capacity Management 338
Patching 344
Summary 347
Exercises 348
Further Reading 349
The Big Picture and the Ten Deliverables 350
An Instructive Job Interview 351
How I Became a DBA 353
ITIL 355
Start with the End in Mind: The Ten Deliverables 361
The Book You Really Need and the Art of the SOP 363
Summary 371
Exercises 372
Further Reading 373
Database Tuning 374
Instance Tuning 375
Using a Systematic Five-Step Tuning Method 375
Analyzing DB Time 378
Understanding the Oracle Wait Interface 383
Mining the Statspack Repository 384
Using the Statspack Report 393
Summary 394
Exercises 395
Further Reading 398
SQL Tuning 399
Defining Efficiency 399
Identifying Inefficient SQL Statements 400
Understanding the Causes of Inefficient SQL 402
Ways to Improve SQL 403
Tuning by Example 409
Summary 431
Exercises 433
Further Reading 433
Index 435