Pro Oracle Database 11g Administration

705 Pages·2010·2.45 MB·English

by  Darl Kuhn (auth.)

Pro Oracle Database 11g
Administration

■ ■ ■
Darl Kuhn

i
Pro Oracle Database 11g Administration
Copyright © 2010 by Darl Kuhn
All rights reserved. No part of this work may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying, recording, or by any
information storage or retrieval system, without the prior written permission of the copyright
owner and the publisher.
ISBN 978-1-4302-2970-4
ISBN 978-1-4302-2971-1 (eBook)
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark
symbol with every occurrence of a trademarked name, logo, or image we use the names,
logos, and images only in an editorial fashion and to the benefit of the trademark owner, with
no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even
if they are not identified as such, is not to be taken as an expression of opinion as to whether
or not they are subject to proprietary rights.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewer: Bernard Lopuz
Editorial Board: Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan
Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes,
Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic
Shakeshaft, Matt Wade, Tom Welsh
Coordinating Editor: Anita Castro
Copy Editors: Mary Behr and Tiffany Taylor
Compositor: MacPS, LLC
Indexer: BIM Indexing & Proofreading Services
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233
Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone],
e-mail [email protected], or visit www.springeronline.com.
For information on translations, please e-mail [email protected] or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or
promotional use. eBook versions and licenses are also available for most titles. For more
information, reference our Special Bulk Sales–eBook Licensing web page at
www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although
every precaution has been taken in the preparation of this work, neither the author(s) nor
Apress shall have any liability to any person or entity with respect to any loss or damage
caused or alleged to be caused directly or indirectly by the information contained in this work.
ii
To Heidi, Brandi, and Lisa.
iii
Contents at a Glance
■Contents .......................................................................................................... v(cid:2)
■About the Author ......................................................................................... xxix(cid:2)
■About the Technical Reviewer ...................................................................... xxx(cid:2)
■Acknowledgments ....................................................................................... xxxi(cid:2)
■Introduction ................................................................................................ xxxii
■Chapter 1: Installing the Oracle Binaries ........................................................ 1(cid:2)
■Chapter 2: Implementing a Database ............................................................ 25(cid:2)
■Chapter 3: Configuring an Efficient Environment .......................................... 51(cid:2)
■Chapter 4: Tablespaces and Datafiles ........................................................... 71(cid:2)
■Chapter 5: Managing Control Files and Online Redo Logs ............................. 91(cid:2)
■Chapter 6: Users and Basic Security ........................................................... 111(cid:2)
■Chapter 7: Tables and Constraints .............................................................. 133(cid:2)
■Chapter 8: Indexes ...................................................................................... 171(cid:2)
■Chapter 9: Views, Synonyms, and Sequences ............................................. 193(cid:2)
■Chapter 10: Data Dictionary Basics ............................................................. 213(cid:2)
■Chapter 11: Large Objects ........................................................................... 243(cid:2)
■Chapter 12: Partitioning: Divide and Conquer ............................................. 269(cid:2)
■Chapter 13: Data Pump ............................................................................... 307(cid:2)
■Chapter 14: External Tables ........................................................................ 345(cid:2)
■Chapter 15: Materialized Views .................................................................. 361(cid:2)
■Chapter 16: User-Managed Backup and Recovery ...................................... 413(cid:2)
■Chapter 17: Configuring RMAN .................................................................... 457(cid:2)
■Chapter 18: RMAN Backups and Reporting ................................................. 487(cid:2)
■Chapter 19: RMAN Restore and Recovery ................................................... 509(cid:2)
■Chapter 20: Oracle Secure Backup .............................................................. 545(cid:2)
■Chapter 21: Automating Jobs ...................................................................... 569(cid:2)
■Chapter 22: Database Troubleshooting ....................................................... 595(cid:2)
■Index ........................................................................................................... 637(cid:2)
iv
Contents

■Contents at a Glance ............................................................................................ iv(cid:2)
■About the Author .............................................................................................. xxix(cid:2)
■About the Technical Reviewer ........................................................................... xxx(cid:2)
■Acknowledgments ............................................................................................ xxxi(cid:2)
■Introduction ..................................................................................................... xxxii
■Chapter 1: Installing the Oracle Binaries .............................................................. 1(cid:2)
Understanding the Optimal Flexible Architecture ........................................................... 1(cid:2)
(cid:2)
Oracle Inventory Directory ..................................................................................................................... 3
(cid:2)
Oracle Base Directory ............................................................................................................................ 3
(cid:2)
Oracle Home Directory ........................................................................................................................... 3
(cid:2)
Oracle Network Files Directory .............................................................................................................. 4
(cid:2)
Automatic Diagnostic Repository ........................................................................................................... 4
Installing Oracle .............................................................................................................. 4(cid:2)
(cid:2)
Step 1. Create the Operating System Group and User ........................................................................... 5
(cid:2)
Step 2. Ensure That the Operating System Is Adequately Configured ................................................... 6
(cid:2)
Step 3. Obtain the Oracle Installation Software ..................................................................................... 7
(cid:2)
Step 4. Unzip the Files ........................................................................................................................... 7
(cid:2)
Step 5. Configure the Response File, and Run the Installer ................................................................... 8
(cid:2)
Step 6. Troubleshoot Any Issues .......................................................................................................... 11
Installing with a Copy of an Existing Installation .......................................................... 12(cid:2)
(cid:2)
Step 1. Copy the Binaries Using an Operating System Utility .............................................................. 13
(cid:2)
Step 2. Attach the Oracle Home ........................................................................................................... 14
v
■ CONTENTS
Upgrading Oracle Software ........................................................................................... 15(cid:2)
Reinstalling After Failed Installation ............................................................................. 16(cid:2)
Applying Interim Patches .............................................................................................. 17(cid:2)
Installing Remotely with the Graphical Installer ........................................................... 18(cid:2)
(cid:2)
Step 1. Install X Software and Networking Utilities on the Local PC ................................................... 19
(cid:2)
Step 2. Start an X Session on the Local Computer .............................................................................. 19
(cid:2)
Step 3. Copy the Oracle Installation Media to the Remote Server ....................................................... 20
(cid:2)
Step 4. Run the xhost Command .......................................................................................................... 21
(cid:2)
Step 5. Log In to the Remote Computer from X ................................................................................... 21
(cid:2)
Step 6. Ensure that the DISPLAY Variable Is Set Correctly on the Remote Computer .......................... 21
(cid:2)
Step 7. Execute the runInstaller Utility ................................................................................................. 22
(cid:2)
Step 8. Troubleshoot ............................................................................................................................ 22
Summary ....................................................................................................................... 23(cid:2)
■Chapter 2: Implementing a Database .................................................................. 25(cid:2)
Setting Operating System Variables ............................................................................. 25(cid:2)
(cid:2)
A Manually Intensive Approach ............................................................................................................ 26
(cid:2)
Oracle’s Approach to Setting OS Variables .......................................................................................... 26
(cid:2)
My Approach to Setting OS Variables .................................................................................................. 27
Creating a Database ...................................................................................................... 29(cid:2)
(cid:2)
Step 1. Set the Operating System Variables ........................................................................................ 29
(cid:2)
Step 2: Configure the Initialization File ................................................................................................ 30
(cid:2)
Step 3: Create the Required Directories ............................................................................................... 32
(cid:2)
Step 4: Create the Database ................................................................................................................ 32
(cid:2)
Step 5. Create a Data Dictionary .......................................................................................................... 36
Configuring and Implementing the Listener .................................................................. 37(cid:2)
Creating a Password File .............................................................................................. 39(cid:2)
Starting and Stopping the Database ............................................................................. 40(cid:2)
(cid:2)
Understanding Authentication .............................................................................................................. 41
(cid:2)
Starting the Database .......................................................................................................................... 41
vi
■ CONTENTS
(cid:2)
Stopping the Database ......................................................................................................................... 43
Using a Response File to Create a Database ................................................................ 45(cid:2)
Dropping a Database ..................................................................................................... 46(cid:2)
How Many Databases on One Server? .......................................................................... 47(cid:2)
Summary ....................................................................................................................... 50(cid:2)
■Chapter 3: Configuring an Efficient Environment ................................................ 51(cid:2)
Customizing Your Operating System Command Prompt ............................................... 52(cid:2)
Customizing Your SQL Prompt ...................................................................................... 54(cid:2)
Creating Shortcuts for Frequently Used Commands ..................................................... 55(cid:2)
(cid:2)
Using Aliases ........................................................................................................................................ 55
(cid:2)
Using a Function .................................................................................................................................. 56
Rerunning Commands Quickly ...................................................................................... 57(cid:2)
(cid:2)
Scrolling with the Up and Down Arrow Keys ....................................................................................... 58
(cid:2)
Pressing Ctrl+P and Ctrl+N .................................................................................................................. 58
(cid:2)
Listing the Command History ............................................................................................................... 58
(cid:2)
Searching in Reverse ........................................................................................................................... 59
Setting the Command Editor ......................................................................................... 59(cid:2)
Developing Standard Scripts ........................................................................................ 60(cid:2)
(cid:2)
dba_setup ............................................................................................................................................ 60
(cid:2)
dba_fcns .............................................................................................................................................. 61
(cid:2)
tbsp_chk.bsh ....................................................................................................................................... 62
(cid:2)
conn.bsh .............................................................................................................................................. 64
(cid:2)
filesp.bsh ............................................................................................................................................. 65
(cid:2)
login.sql ................................................................................................................................................ 66
(cid:2)
top.sql .................................................................................................................................................. 66
(cid:2)
lock.sql ................................................................................................................................................. 67
(cid:2)
users.sql .............................................................................................................................................. 67
Organizing Scripts ......................................................................................................... 68(cid:2)
(cid:2)
Step 1: Create Directories .................................................................................................................... 68
vii
■ CONTENTS
(cid:2)
Step 2: Copy Files to Directories .......................................................................................................... 69
(cid:2)
Step 3: Configure the Startup File ........................................................................................................ 69
Summary ....................................................................................................................... 70(cid:2)
■Chapter 4: Tablespaces and Datafiles ................................................................. 71(cid:2)
Understanding the First Five ......................................................................................... 72(cid:2)
Understanding the Need for More ................................................................................. 72(cid:2)
Creating Tablespaces .................................................................................................... 73(cid:2)
Renaming a Tablespace ................................................................................................ 76(cid:2)
Controlling the Generation of Redo ............................................................................... 76(cid:2)
Changing a Tablespace’s Write Mode ........................................................................... 77(cid:2)
Dropping a Tablespace ................................................................................................. 78(cid:2)
Using Oracle Managed Files ......................................................................................... 80(cid:2)
Creating a Bigfile Tablespace ....................................................................................... 81(cid:2)
Displaying Tablespace Size .......................................................................................... 81(cid:2)
Altering Tablespace Size ............................................................................................... 82(cid:2)
Toggling Datafiles Offline and Online ............................................................................ 83(cid:2)
Renaming or Relocating a Datafile ............................................................................... 85(cid:2)
Summary ....................................................................................................................... 90(cid:2)
■Chapter 5: Managing Control Files and Online Redo Logs .................................. 91(cid:2)
Managing Control Files ................................................................................................. 91(cid:2)
(cid:2)
Viewing Control File Names and Locations .......................................................................................... 93
(cid:2)
Adding a Control File ............................................................................................................................ 94
(cid:2)
Moving a Control File ........................................................................................................................... 96
(cid:2)
Removing a Control File ....................................................................................................................... 97
Managing Online Redo Logs ......................................................................................... 98(cid:2)
(cid:2)
Displaying Online Redo-Log Information ............................................................................................ 100
(cid:2)
Determining the Optimal Size of Online Redo-Log Groups ................................................................ 102
(cid:2)
Determining the Optimal Number of Redo-Log Groups ...................................................................... 103
viii
■ CONTENTS
(cid:2)
Adding Online Redo-Log Groups ........................................................................................................ 105
(cid:2)
Resizing Online Redo-Log Groups ...................................................................................................... 105
(cid:2)
Adding Online Redo-Log Files to a Group .......................................................................................... 106
(cid:2)
Removing Online Redo-Log Files from a Group ................................................................................. 107
(cid:2)
Moving or Renaming Redo-Log Files ................................................................................................. 107
Summary ..................................................................................................................... 108(cid:2)
■Chapter 6: Users and Basic Security ................................................................. 111(cid:2)
Understanding Schemas vs. Users ............................................................................. 111(cid:2)
Managing Default Users .............................................................................................. 112(cid:2)
Creating Users ............................................................................................................ 115(cid:2)
(cid:2)
Choosing a User Name and Authentication Method ........................................................................... 115
(cid:2)
Assigning Default Permanent and Temporary Tablespaces .............................................................. 116
Modifying Passwords .................................................................................................. 118(cid:2)
Enforcing Password Security ...................................................................................... 119(cid:2)
Logging On as a Different User ................................................................................... 120(cid:2)
Modifying Users .......................................................................................................... 122(cid:2)
Dropping Users ........................................................................................................... 122(cid:2)
Enforcing Password Security and Resource Limits .................................................... 123(cid:2)
(cid:2)
Implementing Password Security ...................................................................................................... 124
(cid:2)
Limiting Database Resource Usage ................................................................................................... 126
Managing Privileges .................................................................................................... 128(cid:2)
(cid:2)
Assigning Database System Privileges .............................................................................................. 128
(cid:2)
Assigning Database Object Privileges ................................................................................................ 129
Grouping and Assigning Privileges ............................................................................. 130(cid:2)
Summary ..................................................................................................................... 131(cid:2)
■Chapter 7: Tables and Constraints .................................................................... 133(cid:2)
Understanding Table Types ........................................................................................ 133(cid:2)
Creating a Table .......................................................................................................... 135(cid:2)
(cid:2)
Creating a Heap-Organized Table ...................................................................................................... 135
ix