SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK ORACLE DBA SURVIVAL GUIDE

SUMMARY

488

PART I THE JOB OF THE ORACLE DBA
1 The World of a Database Administrator 3
2 The Database Administrator’s Job Description 13
3 History and Development of Databases and Oracle 43
4 Oracle and Its Environments 55
5 The Tools of the Trade 75
PART II UNDERSTANDING HOW ORACLE WORKS
6 How the Oracle RDBMS Works 97
7 Oracle Memory Structures 109
8 Oracle Files 123
9 Oracle Processes 137
10 Basic Oracle Database Objects 153
11 Oracle System Privileges 171
12 Oracle Object Privileges 191
13 Roles and Grants 205
14 Backup and Recovery 223
PART III INSTALLING AND UPGRADING THE ORACLE SOFTWARE
15 The Life Cycle of an Oracle Database 251
16 Choosing Products and the Environment for
Your Oracle Database 261
17 Planning an Oracle Installation 277
18 Oracle Installations 295
19 Planning an Oracle Upgrade 311
20 Oracle Upgrades 321
PART IV DEVELOPING A DATABASE ADMINISTRATION SCHEME
21 The Database Administration Scheme 329
22 Laying Out a Database 339
23 A Routine Maintenance Schedule 351
24 Developing Scripts to Automate Tasks 363
ORACLE iv DBA SURVIVAL GUIDE
PART V THE DAILY ROUTINE
25 The “Typical” Day 383
26 User Account Maintenance 393
27 Tablespace Maintenance 409
28 Table and Index Maintenance 423
PART VI MONITORING THE DATABASE’S HEALTH
29 The Health of a Database 447
30 Routine Monitoring 457
31 Auditing 479
32 Tuning the Database 495
33 Looking Toward the Future 517
PART VII DEALING WITH PROBLEMS
34 When Problems Occur 531
35 Space Problems 547
36 Instance and Application Crashes 563
37 When the Database Is Too Slow 575
38 Troubleshooting Checklist 585
39 Sound Database Object Design 593
40 Query Optimization 611
41 Keeping Current as a DBA 629
PART IX ADVANCED ORACLE TECHNICAL FEATURES
42 Rollback Segments 641
43 Locks 649
44 Parallel Processing Options 655
45 Packages, Procedures, and Triggers 663
46 Client-Server and Networking 673
47 Where To Next? 681
48 Oracle Workgroup Server and Oracle 7.2 687
APPENDIXES
A SQL Commands 695
B Glossary of Terms 701
C SQL*Plus Features 705
D SQL*DBA Features 711
E SQL*Loaders 715
F Import and Export 719
G Where to Get More Information 725
H Sample System Configuration Analyses 727
Index 737
PART I THE JOB OF THE ORACLE DBA 1
1 The World of a Database Administrator 3
The Concept of a Database Administrator 7
Variety of Responsibilities 8
Summary 11
2 The Database Administrator’s Job Description 13
Types of Database Administrator 15
The Full-Time Database Administrator 18
Developers Acting as Database Administrators. 23
Scientists, Engineers, and Other Users as DBAs 28
Database Administrator Tasks 32
Is the Database Secure Enough? 33
Does the Database Perform Well Enough? 35
Is the Data Accurate? 37
Is the Data Stored in a Logical and Accessible Manner? 37
Interfacing with System Administrators and Other Support Staff 37
Understanding the Application Needs 39
Summary 42
3 History and Development of Databases and Oracle 43
Computerized Data Storage 44
The First Databases 46
Relational Databases 48
Oracle’s History 50
The Current Database Market 51
What Next? 52
Summary 53
4 Oracle and Its Environments 55
What Is Oracle? 56
Alternative Architectures 57
Oracle Development Tools Versus the Database 61
Oracle Utilities 63
Third-Party Products 66
Assembling the Parts into an Architecture 69
Summary 73
5 The Tools of the Trade 75
The Basics: SQL*DBA 78
The Next Generation: Oracle Server Manager 81
Personal Oracle7 for Microsoft Windows 82
The Command-Line Interface: SQL*Plus 88
Import and Export 90
Loading Data from External Systems: SQL*Loader 91
Third-Party Products 93
Locally Developed Tools 93
Summary 93
PART II UNDERSTANDING HOW ORACLE WORKS 95
6 How the Oracle RDBMS Works 97
Overview 98
The Oracle Processes 101
Memory and Speed 102
Disk Storage 104
Multiprocessing and Microsoft Windows Configurations 106
Summary 107
7 Oracle Memory Structures 109
Overview 110
The System Global Area (SGA) 112
The Program Global Area (PGA) 117
User Work Spaces 119
Summary 120
8 Oracle Files 123
File Locations 125
Data Files 127
Redo Log Files 129
Archive Log Files 131
Control Files 133
Initialization Files 133
Log and Trace Files 135
Summary 136
9 Oracle Processes 137
Overview 138
The System Monitor 142
The Process Monitor 144
The Database Writer 144
The Log Writer 147
The Archiver 148
The Recoverer 148
The Lock Writer 148
Dedicated Server Processes 149
Multi-Threaded Server Processes 149
SQL*Net Listeners 150
Parallel Query Processes 150
Oracle 7 for Microsoft Windows. 151
Summary 151
10 Basic Oracle Database Objects 153
Overview of Storage and Access 154
Tables 159
Indexes 162
Views 164
Synonyms 166
Stored Procedures 167
Clusters 168
Sequences 169
Summary 169
11 Oracle System Privileges 171
Overview of Oracle Privileges 172
Overview of System Privileges 176
The User Privileges 178
The Developer Privileges 178
The “Any” Privileges 180
The Database Maintenance Privileges 185
The Monitoring Privilege 187
Typical Privilege Sets. 188
The “Any” Privilege Sets 189
Summary 190
12 Oracle Object Privileges 191
Overview 192
Object Privileges 196
Using Dummy Object Owners 198
A Typical Privilege Scheme 199
Summary 203
13 Roles and Grants 205
Introduction to Grants 206
Introduction to Roles (Version 7 Feature) 209
Grants Without Roles. 212
A Typical Privilege Scheme 212
Use of Scripts to Capture Privilege Sets 220
Summary 221
14 Backup and Recovery 223
The Importance of Backups 224
Overview of Oracle Backup Schemes 225
Archive Log or No Archive Log 228
Cold Backups 229
Warm Backups 236
Exports 238
Which Scheme to Choose 239
Rotating Backup Schemes 242
Automated Backup Schemes 245
What About Mirrored Disks? 247
Summary 248
PART III INSTALLING AND UPGRADING
THE ORACLE SOFTWARE 249
15 The Life Cycle of an Oracle Database 251
Product Selection 254
Planning the Installation 255
Installation 256
When to Upgrade 257
Planning Upgrades 258
Upgrading the Oracle Software 259
Summary 260
16 Choosing Products and the Environment 261
Getting All the Pieces. 262
Host-Based and Server-Based Architectures 268
Client-Server Architectures 271
Dealing with Vendors 274
Summary 276
17 Planning an Oracle Installation 277
Starting with Business Needs 279
The Installation and Configuration Guide 281
The README File 284
Oracle’s Recommended Layout 285
Calculating Data Space 288
Memory Area Planning 289
Process Planning 289
ORACLE x DBA SURVIVAL GUIDE
Developing Your Own Installation Plan 290
Some Good Reviewers 292
Summary 292
18 Oracle Installations 295
Overview 296
Starting with a System Backup. 298
The Oracle Installer 299
Installing the Oracle Application Software 301
UNIX Installations 303
Creating a Database with the Installer 304
Dealing with Installation Problems 305
Manually Creating a Database 307
Summary 309
19 Planning an Oracle Upgrade 311
Overview 312
Storing the New Software 314
The Importance of the README File 314
Changes Needed in the Database 315
Other Factors to Consider 315
The Backout Plan 317
Lining Up Support 318
Summary 319
20 Oracle Upgrades 321
The Oracle Installer: To Use or Not? 323
Another Backup Lecture 324
Loading and Linking the New Software 324
Upgrading the Database 325
When Problems Occur 325
Summary 326
PART IV DEVELOPING A DATABASE ADMINISTRATION SCHEME 327
21 The Administration Scheme 329
What Is a DBA Scheme? 330
Technical Factors to Consider 333
Matching User Requirements 335
Preparing for the Future 336
Summary 338
22 Laying Out a Database 339
Overview 340
Data Files 341
Control Files 344
Online Redo Log Files 345
Archive Log Files 346
The Configuration Process 346
Expansion of the Database 348
Summary 349
23 A Routine Maintenance Schedule 351
Overview 352
Starting with User and System Processing Schedules 355
Types of Activities 357
The Daily Schedule 359
The Long-Term Schedule 360
Summary 361
24 Developing Scripts to Automate Tasks 363
Automation of DBA Tasks 365
SQL Scripts and System Scripts 367
Automatic Job Submission Utilities 373
Developing and Testing Scripts 377
Monitoring the Results 378
Summary 379
PART V THE DAILY ROUTINE 381
25 The “Typical” Day 383
Scheduled Events 386
Monitoring 386
User Support 387
Problems 388
If There Is Any Time Left 390
Summary 391
26 User Account Maintenance 393
User Maintenance and the Security Scheme 394
Using System Logon IDs for Access 396
Adding New Users to the Database 399
Changing User Access Rights 402
Deleting Users from the System 406
Temporarily Disabling Users. 407
Summary 408
27 Tablespace Maintenance 409
Care and Feeding of Tablespaces 410
Monitoring and Planning 416
Typical Problems and Their Solutions 418
Summary 422
28 Table and Index Maintenance 423
Care and Feeding of Tables and Indexes 424
Monitoring Tables and Indexes 434
Typical Problems and Their Solutions 436
Fragmentation 442
Summary 443
PART VI MONITORING THE DATABASE’S HEALTH 445
29 The Health of a Database 447
What Is a “Healthy” Database? 448
Monitoring Programs 451
Auditing 454
Tuning 455
Summary 455
30 Routine Monitoring 457
A Routine Monitoring Program 458
Scripts and Reports 460
Utilization Monitoring 462
Tuning Monitoring 465
Security Monitoring 470
Configuration Monitoring 474
Third-Party Tools 477
Summary 478
31 Auditing 479
Overview 480
Oracle Auditing Events 483
Auditing and Performance 491
Auditing as Part of Security Monitoring 491
Deciding What to Audit 492
Summary 494
32 Tuning the Database 495
What Is Tuning? 496
What Can You Control? 498
Host Computer Indicators 504
Oracle Resource Contention 506
A Tuning Checklist 513
Summary 514
33 Looking Toward the Future 517
Knowing Where the Database Is Going 518
When More Is Needed 522
Proving Your Case 523
Summary 527
PART VII DEALING WITH PROBLEMS 529
34 When Problems Occur 531
Routine Requests, Problems, and Real Problems. 533
Classifying the Problem 536
Resources to Identify the Problems 539
Unknown Problems and Approaches 541
Supporting Resources. 542
The Ten Most Common Problems You’ll Face 543
Service Level Agreements 544
Summary 545
35 Space Problems 547
Identifying the True Problem 548
Cleaning Out Tablespaces 551
Expanding Tablespaces 553
Compressing the Number of Extents 555
Alternatives: Reducing Data Storage 559
Keeping the Data Definition Language (DDL) 560
Summary 560
36 Instance and Application Crashes 563
Tracing the Problem. 564
Log Files Can Help 566
Operating System Conflicts 569
Expanding Oracle Resources for Applications 570
Reducing Oracle Resources for Applications 571
When to Call Oracle 571
Summary 573
37 When the Database Is Too Slow 575
When Is a Database Too Slow? 576
Managing User Expectations 578
Checking Tuning of the Database 579
Application Tuning 580
When Additional Capacity Is Required. 581
Summary 583
38 Troubleshooting Checklist 585
Summary 589
ORACLE xiv DBA SURVIVAL GUIDE
39 Sound Database Object Design 593
Overview 594
Normalization and Table Design. 597
Table Design Modifications for Decision Support 600
When and How to Use Indexes 601
Naming Conventions 604
Summary Tables Versus Views 606
Sizing Tables 606
Sizing Indexes 609
Summary 610
40 Query Optimization 611
Factors Designers Can Control 613
The Rule-Based Optimizer. 616
The Cost-Based Optimizer 617
Execution Plans 619
Hints 622
Indexes 624
General Guidelines 624
The Value of Experimentation 626
Summary 626
41 Keeping Current as a DBA 629
The Changing Environment 631
Training Vendors 632
Books 634
A Test Instance 635
The Internet 635
User Groups 637
Summary 638
PART IX ADVANCED ORACLE TECHNICAL FEATURES 639
42 Rollback Segments 641
Introduction 642
Special Storage Considerations 644
Common and Confusing Error Messages 645
Setting Up Rollback Segments 647
Summary 648
43 Locks 649
Overview 650
Types of Locks Applied. 651
Freeing Locks 653
Determining When an Application is Waiting
for a Lock to Release 653
Summary 654
44 Parallel Processing Options 655
Types of Parallel Processing 657
When to Use Parallel Processing. 658
Distributed Databases Versus Parallel Servers 658
Multi-Threaded Servers 659
Asynchronous Database Writers 660
Parallel Query 661
Parallel Recovery 661
Summary 662
45 Packages, Procedures, and Triggers 663
Software Stored in the Database 664
The Object-Oriented World 666
Trigger Types and Uses 667
Database Procedures 668
Database Packages 670
When to Use Packages, Procedures, and Triggers 671
Summary 671
46 Client-Server and Networking 673
What Is Client-Server?. 674
Typical Client-Server Architectures 678
Tricks to Administering a Client-Server Database 679
Distributed Databases 680
Summary 680
47 Where To Next? 681
The Oracle Environment 682
The DBA’s Job 683
Tips to Make Life Easier 684
The Future 685
48 Oracle Workgroup Server and Oracle 7.2 687
First Impressions of the Workgroup Server Concept 688
Oracle 7.2 690
Rumors and Gossip About Oracle 8 691
Summary 691
APPENDIXES 693
A SQL Commands 695
Object Creation Commands 696
Object Modification Commands 698
Object Deletion Commands 699
B Glossary of Terms 701
C SQL*Plus Features 705
Calling SQL*Plus 706
Output Formatting 706
Working with SQL Files 708
SQL*Plus Versus SQL*DBA 709
D SQL*DBA Features 711
Command Line Versus Screen Mode 712
Menu Interface 712
Monitors 713
Killing User Sessions 713
Oracle Server Manager 713
E SQL*Loaders 715
Using Load Tables and Population Scripts. 716
Fixed Column Versus Delimited Formats 716
An Example Control File 717
Useful Optional Parameters 717
F Import and Export 719
Command Line Versus Interactive 720
Import Parameters 720
Requirements for Running Import 721
Oracle Export 721
An Export and Import Example 721
G Where to Get More Information 725
Conventional Addresses 726
FTP Sites 726
World Wide Web Pages 726
Newsgroups 726
H Sample System Configuration Analyses 727
Index 737