Tài liệu Expert Oracle Database 10g Administration

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