Oracle DBA Made Simple: Oracle Database Administration Techniques

350 Pages·2004·2.96 MB·English

by  Mike Ault

Rampant TechPress

Oracle DBA made simple
Oracle database administration
techniques

Mike Ault
ROBO BOOKS MONOGRAPH DATABASE ADMINISTRATION

Notice
While the author makes every effort to ensure the information presented in this
white paper is accurate and without error, Rampant TechPress, its authors and its
affiliates takes no responsibility for the use of the information, tips, techniques or
technologies contained in this white paper. The user of this white paper is solely
responsible for the consequences of the utilization of the information, tips,
techniques or technologies reported herein.
RAMPANT TECHPRESS EBOOK PAGE II

COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.
ROBO BOOKS MONOGRAPH DATABASE ADMINISTRATION

Oracle DBA made simple
Oracle database administration techniques

By Mike Ault

Copyright © 2003 by Rampant TechPress. All rights reserved.

Published by Rampant TechPress, Kittrell, North Carolina, USA

Series Editor: Don Burleson

Production Editor: Teri Wade

Cover Design: Bryan Hoff

Oracle, Oracle7, Oracle8, Oracle8i, and Oracle9i are trademarks of Oracle
Corporation. Oracle In-Focus is a registered Trademark of Rampant TechPress.

Many of the designations used by computer vendors to distinguish their products
are claimed as Trademarks. All names known to Rampant TechPress to be
trademark names appear in this text as initial caps.

The information provided by the authors of this work is believed to be accurate
and reliable, but because of the possibility of human error by our authors and
staff, Rampant TechPress cannot guarantee the accuracy or completeness of
any information included in this work and is not responsible for any errors,
omissions, or inaccurate results obtained from the use of information or scripts in
this work.

Visit www.rampant.cc for information on other Oracle In-Focus books.

ISBN: 0-9740716-5-X

RAMPANT TECHPRESS EBOOK PAGE III

COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.
ROBO BOOKS MONOGRAPH DATABASE ADMINISTRATION

Table Of Contents
Notice.............................................................................................ii
Publication Information ..............................................................iii
Table Of Contents........................................................................iv
Introduction...................................................................................1
System Planning...........................................................................1
Resource and Capacity Planning.........................................................2
Resource Specification for Oracle..................................................2
Optimal Flexible Architecture (OFA).................................................7
Minimum OFA Configuration........................................................8
Oracle Structures and How They Affect Installation........................10
Executables...................................................................................11
Data Files......................................................................................11
Redo Logs.....................................................................................12
Control Files..................................................................................12
Exports..........................................................................................13
Archive Logs.................................................................................13
LOB Storage.................................................................................14
BFILE Storage..............................................................................14
Disk Striping, Shadowing, RAID, and Other Topics........................14
Disk Striping.................................................................................15
Disk Shadowing or Mirroring.......................................................16
RAID—Redundant Arrays of Inexpensive Disks.........................17
New Technologies.............................................................................18
Optical Disk Systems....................................................................18
Tape Systems................................................................................19
RAM Drives (Random Access Memory).....................................19
Backup & recovery ..................................................................... 20
RAMPANT TECHPRESS EBOOK PAGE IV

COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.
ROBO BOOKS MONOGRAPH DATABASE ADMINISTRATION

Backups..............................................................................................21
Cold Backups................................................................................21
Hot Backups..................................................................................23
Example Documentation Procedure for NT Online Backup and
Recovery Scripts...........................................................................48
Imports/Exports.................................................................................51
Limitations on export/import:......................................................51
Exports..........................................................................................51
IMPORT.......................................................................................53
Archive Logs.....................................................................................54
Backup Methodologies......................................................................56
NT or UNIX System Backup........................................................56
Import/Export...............................................................................57
Archive Logging...........................................................................57
Recovery Types.................................................................................57
Oracle7 Enterprise Backup Utility.....................................................59
Oracle8 RECOVERY MANAGER FACILITY................................63
Installing the RMAN Catalog.......................................................66
Incomplete restore scenario..........................................................69
DB_VERIFY UTILITY.....................................................................71
The following example shows how to get on-line help:...............72
The DBMS_REPAIR Utility.............................................................73
DBMS_REPAIR Enumeration Types...........................................74
DBMS_REPAIR Exceptions........................................................74
DBMS_REPAIR Procedures........................................................75
ADMIN_TABLES........................................................................76
CHECK_OBJECT........................................................................81
DUMP_ORPHAN_KEYS............................................................84
FIX_CORRUPT_BLOCKS..........................................................85
REBUILD_FREELISTS...............................................................86
SKIP_CORRUPT_BLOCKS........................................................88
Oracle RDBMS Architecture.............................................................89
Background Processes..................................................................90
RAMPANT TECHPRESS EBOOK PAGE V

COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.
ROBO BOOKS MONOGRAPH DATABASE ADMINISTRATION

Datafiles........................................................................................94
Datafile Sizing...................................................................................96
Rollback Segments......................................................................102
Redo log files..............................................................................104
Control files................................................................................105
Initialization File.........................................................................105
The Undocumented Initialization Parameters (“_”)...................126
The Initialization File Event Settings.........................................145
System Global Area.........................................................................157
SGA.............................................................................................157
Modifying the INIT.ORA...........................................................158
Allocating And Caching Memory...............................................159
Use of the Default Pool...............................................................159
Use of The KEEP Pool...............................................................160
Use of the RECYCLE Pool.........................................................160
Tuning the Three Pools...............................................................161
Shared Pool.................................................................................162
Putting it All In Perspective.............................................................180
What to Pin.................................................................................185
The Shared Pool and MTS..........................................................190
Large Pool Sizing........................................................................191
A Matter Of Hashing..................................................................194
Disk IO and the Shared Pool.......................................................198
Monitoring Library and Data Dictionary Caches.......................201
In Summary.................................................................................204
Managing the Database....................................................................209
Find USER locking others/Kill problem USER.........................209
Methods of Murder..........................................................................213
Killing From the Oracle Side......................................................213
Killing From the Operating System Side....................................215
Creating and starting the database..............................................215
Database Creation............................................................................216
Re-creation of a Database................................................................219
Database Startup and Shutdown......................................................229
RAMPANT TECHPRESS EBOOK PAGE VI

COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.
ROBO BOOKS MONOGRAPH DATABASE ADMINISTRATION

Startup.........................................................................................230
Shutdown....................................................................................232
Tuning Responsibilities...................................................................234
Step 1: Tune the Business Rules.................................................234
Step 2: Tune the Data Design.....................................................234
Step 3: Tune the Application Design..........................................235
Step 4: Tune the Logical Structure of the Database...................235
Step 5: Tune Database Operations..............................................235
Step 6: Tune the Access Paths....................................................236
Step 7: Tune Memory Allocation...............................................236
Step 8: Tune I/O and Physical Structure.....................................237
Step 9: Tune Resource Contention.............................................238
Step 10: Tune the Underlying Platform(s)..................................238
Tuning Summary........................................................................238
Layout & Fragmentation..................................................................239
Tablespace Segments & Free Space...........................................241
Tables & Indexes/Partitioning....................................................242
The V$ views...................................................................................243
How are they used?.....................................................................244
The Optimizers & the Analyze Command......................................248
RULE Based Optimizer..............................................................249
COST Based Optimizer..............................................................250
The Parallel Query Option...............................................................253
Parallel query settings.................................................................253
Problems In Parallel Query Usage...................................................258
Security............................................................................................258
Users...........................................................................................258
Altering Users.............................................................................264
Dropping Users...........................................................................265
Grants...............................................................................................266
System Privileges........................................................................267
Object Privileges.........................................................................276
Column Privileges.......................................................................281
View Grants................................................................................283
RAMPANT TECHPRESS EBOOK PAGE VII

COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.
ROBO BOOKS MONOGRAPH DATABASE ADMINISTRATION

Other Grants................................................................................283
Revoking Grants..............................................................................283
Use Of Roles....................................................................................285
Creating Roles.............................................................................286
Grants To Roles...............................................................................287
Setting Roles....................................................................................289
Special Roles...................................................................................290
OSOPER And OSDBA...............................................................292
CONNECT, RESOURCE, And DBA Roles..............................293
Export/Import Roles....................................................................294
Using PROFILES........................................................................295
Profiles and Resource Limits...........................................................297
Altering Profiles..........................................................................300
Profiles and Passwords...............................................................301
Managing CPU Utilization for in Oracle8i......................................303
Creating a Resource Plan............................................................304
Restricting Access by Rows in Oracle8i.........................................329
Policy Usage...............................................................................335
DBMS_RLS Package..................................................................337
Summary.................................................................................... 340

RAMPANT TECHPRESS EBOOK PAGE VIII

COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.