Oracle DBA Made Simple: Oracle Database Administration Techniques PDF
Preview Oracle DBA Made Simple: Oracle Database Administration Techniques
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.
Description:The list of books you might like
