Pro Oracle Database 11g Administration

Prelim......Page 1
Contents at a Glance......Page 6
Contents......Page 7
About the Author......Page 31
About the Technical Reviewer......Page 32
Acknowledgments......Page 33
Book Structure......Page 34
Contacting the Author......Page 35
Understanding the Optimal Flexible Architecture......Page 37
Oracle Home Directory......Page 39
Installing Oracle......Page 40
Step 1. Create the Operating System Group and User......Page 41
Step 2. Ensure That the Operating System Is Adequately Configured......Page 42
Step 4. Unzip the Files......Page 43
Oracle Database 10g Scenario......Page 44
Oracle Database 11g Scenario......Page 46
Step 6. Troubleshoot Any Issues......Page 47
Installing with a Copy of an Existing Installation......Page 48
Step 1. Copy the Binaries Using an Operating System Utility......Page 49
Step 2. Attach the Oracle Home......Page 50
Upgrading Oracle Software......Page 51
Reinstalling After Failed Installation......Page 52
Applying Interim Patches......Page 53
Installing Remotely with the Graphical Installer......Page 54
Step 2. Start an X Session on the Local Computer......Page 55
Step 3. Copy the Oracle Installation Media to the Remote Server......Page 56
Step 6. Ensure that the DISPLAY Variable Is Set Correctly on the Remote Computer......Page 57
Step 8. Troubleshoot......Page 58
Summary......Page 59
Setting Operating System Variables......Page 61
Understanding oratab......Page 62
My Approach to Setting OS Variables......Page 63
Step 1. Set the Operating System Variables......Page 65
Step 2: Configure the Initialization File......Page 66
Step 4: Create the Database......Page 68
Step 5. Create a Data Dictionary......Page 72
Configuring and Implementing the Listener......Page 73
Creating a Password File......Page 75
Starting and Stopping the Database......Page 76
Starting the Database......Page 77
Stopping the Database......Page 79
Using a Response File to Create a Database......Page 81
Dropping a Database......Page 82
How Many Databases on One Server?......Page 83
Summary......Page 86
Configuring an Efficient Environment......Page 87
Customizing Your Operating System Command Prompt......Page 88
Customizing Your SQL Prompt......Page 90
Using Aliases......Page 91
Using a Function......Page 92
Rerunning Commands Quickly......Page 93
Listing the Command History......Page 94
Setting the Command Editor......Page 95
dba_setup......Page 96
dba_fcns......Page 97
tbsp_chk.bsh......Page 98
conn.bsh......Page 100
filesp.bsh......Page 101
top.sql......Page 102
users.sql......Page 103
Step 1: Create Directories......Page 104
Step 3: Configure the Startup File......Page 105
Summary......Page 106
Tablespaces and Datafiles......Page 107
Understanding the Need for More......Page 108
Creating Tablespaces......Page 109
Controlling the Generation of Redo......Page 112
Changing a Tablespace’s Write Mode......Page 113
Dropping a Tablespace......Page 114
Using Oracle Managed Files......Page 116
Displaying Tablespace Size......Page 117
Altering Tablespace Size......Page 118
Toggling Datafiles Offline and Online......Page 119
Renaming or Relocating a Datafile......Page 121
Summary......Page 126
Managing Control Files......Page 127
Viewing Control File Names and Locations......Page 129
Spfile Scenario......Page 130
Init.ora Scenario......Page 131
Moving a Control File......Page 132
Removing a Control File......Page 133
Managing Online Redo Logs......Page 134
Displaying Online Redo-Log Information......Page 136
Determining the Optimal Size of Online Redo-Log Groups......Page 138
Determining the Optimal Number of Redo-Log Groups......Page 139
Resizing Online Redo-Log Groups......Page 141
Adding Online Redo-Log Files to a Group......Page 142
Moving or Renaming Redo-Log Files......Page 143
Summary......Page 144
Understanding Schemas vs. Users......Page 147
Managing Default Users......Page 148
Choosing a User Name and Authentication Method......Page 151
Assigning Default Permanent and Temporary Tablespaces......Page 152
Modifying Passwords......Page 154
Enforcing Password Security......Page 155
Logging On as a Different User......Page 156
Dropping Users......Page 158
Enforcing Password Security and Resource Limits......Page 159
Implementing Password Security......Page 160
Limiting Database Resource Usage......Page 162
Assigning Database System Privileges......Page 164
Assigning Database Object Privileges......Page 165
Grouping and Assigning Privileges......Page 166
Summary......Page 167
Understanding Table Types......Page 169
Creating a Heap-Organized Table......Page 171
Implementing Virtual Columns......Page 174
Making Read-Only Tables......Page 176
Understanding Deferred Segment Creation......Page 177
Allowing for Parallel SQL Execution......Page 178
Compressing Table Data......Page 179
Avoiding Redo Creation......Page 180
Modifying a Table......Page 182
Adding a Column......Page 183
Altering a Column......Page 184
Dropping a Column......Page 185
Displaying Table DDL......Page 186
Dropping a Table......Page 187
Undropping a Table......Page 188
Using TRUNCATE......Page 189
Viewing and Adjusting the High-Water Mark......Page 190
Tracing to Detect Space Below the High-Water Mark......Page 191
Using DBMS_SPACE to Detect Space Below the High-Water Mark......Page 192
Shrinking a Table......Page 193
Moving a Table......Page 194
Creating a Temporary Table......Page 195
Managing Constraints......Page 197
Creating Primary-Key Constraints......Page 198
Enforcing Unique Key Values......Page 199
Creating Foreign-key Constraints......Page 200
Checking for Specific Data Conditions......Page 201
Enforcing Not Null Conditions......Page 202
Disabling Constraints......Page 203
Enabling Constraints......Page 204
Summary......Page 206
Deciding When to Create an Index......Page 207
What to Think About......Page 208
Index-Management Guidelines......Page 209
Creating B-tree Indexes......Page 211
Creating Concatenated Indexes......Page 212
Implementing Function-Based Indexes......Page 213
Creating Unique Indexes......Page 214
Using Bitmap Indexes......Page 215
Creating Bitmap Join Indexes......Page 216
Implementing Reverse-Key Indexes......Page 217
Creating Key-Compressed Indexes......Page 218
Implementing Invisible Indexes......Page 219
Guaranteeing Application Behavior Is Unchanged When You Add an Index......Page 220
Using Index-Naming Standards......Page 221
Placing Indexes in Tablespaces Separate from Tables......Page 222
Maintaining Indexes......Page 223
Rebuilding an Index......Page 224
Monitoring Index Usage......Page 226
Summary......Page 227
Creating a View......Page 229
Checking Updates......Page 230
Updatable Join Views......Page 231
Creating an INSTEAD OF Trigger......Page 233
Displaying the SQL Used to Create a View......Page 234
Managing Synonyms......Page 236
Creating Public Synonyms......Page 237
Displaying Synonym Metadata......Page 238
Renaming a Synonym......Page 239
Creating a Sequence......Page 240
Using Sequence Pseudo-columns......Page 241
Autoincrementing Columns......Page 242
Using Multiple Sequences that Generate Unique Values......Page 243
Viewing Sequence Metadata......Page 244
Dropping a Sequence......Page 245
Setting the Current Value of a Sequence to a Higher or Lower Value......Page 246
Resetting a Sequence Back to a Lower Value......Page 247
Summary......Page 248
Static Views......Page 249
Dynamic Performance Views......Page 251
Derivable Documentation......Page 252
Logical and Physical Database Structures......Page 254
Currently Connected User......Page 256
Users Currently Logged In......Page 257
Currently Executing SQL......Page 258
Viewing Table Information......Page 259
Displaying Object Disk-Space Usage......Page 260
Displaying Table Row Counts......Page 261
Displaying Indexes for a Table......Page 263
Showing Foreign-Key Columns Not Indexed......Page 264
Displaying Table Constraints......Page 265
Showing Primary-Key and Foreign-Key Relationships......Page 266
Displaying Granted Roles......Page 268
Displaying System Privileges......Page 269
Displaying Object Privileges......Page 271
Displaying Object Dependencies......Page 272
Displaying Differences in Schemas......Page 274
Summary......Page 277
Describing Current LOB Types......Page 279
Illustrating LOB Locators, Indexes, and Chunks......Page 280
Prerequisites for SecureFiles......Page 282
Creating a BasicFile LOB Column......Page 284
Creating a LOB in a Specific Tablespace......Page 285
Creating a SecureFile LOB Column......Page 286
Creating a Partitioned LOB......Page 287
Removing a LOB Column......Page 289
Caching LOBs......Page 290
Storing LOBs In and Out of Line......Page 291
Compressing LOBs......Page 292
Deduplicating LOBs......Page 293
Encrypting LOBs......Page 294
Moving a Table to a SecureFile Architecture......Page 296
Online Redefinition......Page 297
Viewing LOB Metadata......Page 298
Loading a CLOB......Page 299
Measuring LOB Space Consumed......Page 301
BasicFile Space Used......Page 302
SecureFile Space Used......Page 303
Summary......Page 304
Partitioning: Divide and Conquer......Page 305
What Tables Should Be Partitioned?......Page 306
Creating Partitioned Tables......Page 307
Partitioning by Range......Page 308
Using a NUMBER for the Partition Key Column......Page 309
Using a TIMESTAMP for the Partition Key Column......Page 311
Placing Partitions into Tablespaces......Page 312
Partitioning by List......Page 315
Partitioning by Hash......Page 316
Blending Different Partitioning Methods......Page 317
Creating Partitions on Demand......Page 318
Partitioning to Match a Parent Table......Page 320
Giving an Application Control over Partitioning......Page 322
Viewing Partition Metadata......Page 323
Moving a Partition......Page 324
Automatically Moving Updated Rows......Page 325
Partitioning an Existing Table......Page 326
Adding a Partition......Page 327
Exchanging a Partition with an Existing Table......Page 329
Splitting a Partition......Page 331
Merging Partitions......Page 332
Dropping a Partition......Page 333
Removing Rows from a Partition......Page 334
Manipulating Data within a Partition......Page 335
Partitioning an Index to Follow Its Table......Page 336
Partitioning an Index Differently than Its Table......Page 339
Partition Pruning......Page 340
Summary......Page 341
Data Pump......Page 343
Data Pump Architecture......Page 344
Creating a Database Directory......Page 346
Taking an Export......Page 347
Importing Data......Page 348
Entering Interactive Command Mode......Page 349
Attaching to a Running Job......Page 350
Stopping and Restarting a Job......Page 351
Use a Parameter File......Page 352
Listing the Contents of Dump Files......Page 353
Transferring Data......Page 354
Exporting and Importing Directly Across the Network......Page 355
Copying Datafile(s)......Page 356
Exporting and Importing Tablespaces and Datafiles......Page 357
Changing Segment and Storage Attributes......Page 358
Changing the Size of Datafiles......Page 359
Specifying a Query......Page 360
Excluding Objects from the Export File......Page 361
Exporting Table, Index, Constraint, and Trigger DDL......Page 363
Common Data Pump Tasks......Page 364
Creating a Consistent Export......Page 365
Importing When Objects Already Exist......Page 366
Renaming a Table......Page 367
Remapping Data......Page 368
Suppressing a Log File......Page 369
Using Parallelism......Page 370
Reusing Output File Names......Page 371
Compressing Output......Page 372
Encrypting Data......Page 373
Data-Dictionary Views......Page 374
Status Table......Page 375
Operating-System Utilities......Page 376
Data Pump Mapping to the exp Utility......Page 377
Data Pump Mapping to the imp Utility......Page 379
Summary......Page 380
SQL*Loader vs. External Tables......Page 381
Loading CSV Files into the Database......Page 383
Creating an External Table......Page 384
Loading a Regular Table from the External Table......Page 385
Performing Advanced Transformations......Page 386
Viewing Text Files from SQL......Page 388
Unloading and Loading Data Using an External Table......Page 390
Encrypting a Dump File......Page 392
Preprocessing an External Table......Page 393
Summary......Page 395
Understanding Materialized Views......Page 397
Materialized View Terminology......Page 399
Referencing Useful Views......Page 400
Creating a Complete-Refreshable Materialized View......Page 401
Creating a Fast-Refreshable Materialized View......Page 405
Creating Indexes on MVs......Page 410
Partitioning Materialized Views......Page 411
Encrypting Materialized View Columns......Page 412
Building a Materialized View on a Prebuilt Table......Page 413
Creating a Materialized View Refreshed on Commit......Page 414
Creating a Never-Refreshable Materialized View......Page 415
Creating Materialized Views for Query-Rewrite......Page 416
Creating a Fast-Refreshable MV Based on a Complex Query......Page 417
Viewing Materialized View DDL......Page 419
Dropping a Materialized View......Page 420
Re-creating a Materialized View to Reflect Base-Table Modifications......Page 421
Altering a Materialized View but Preserving the Underlying Table......Page 422
Altering a Materialized View Created on a Prebuilt Table......Page 423
Toggling Redo Logging on a Materialized View......Page 424
Moving a Materialized View......Page 425
Managing Materialized View Logs......Page 426
Creating a Materialized View Log......Page 427
Viewing Space Used by a Materialized View Log......Page 428
Checking the Row Count of a Materialized View Log......Page 429
Moving a Materialized View Log......Page 430
Refreshing Materialized Views......Page 431
Manually Refreshing Materialized Views from SQL*Plus......Page 432
Automating Refreshes Using a Shell Script and Scheduling Utility......Page 433
Creating an MV with a Refresh Interval......Page 434
Handling the ORA-12034 Error......Page 435
Viewing Materialized Views’ Last Refresh Times......Page 436
Monitoring Real-Time Refresh Progress......Page 437
Checking Whether MVs Are Refreshing Within a Time Period......Page 438
Creating Remote Materialized View Refreshes......Page 439
Understanding Remote-Refresh Architectures......Page 440
Viewing Materialized View Base-Table Information......Page 441
Determining How Many MVs Reference a Central MV Log......Page 442
Creating a Materialized View Group......Page 444
DBMS_MVIEW vs. DBMS_REFRESH......Page 445
Adding an MV to a Refresh Group......Page 446
Summary......Page 447
User-Managed Backup and Recovery......Page 449
Making a Cold Backup of a Noarchivelog-Mode Database......Page 450
Step 2: Determine the Locations and Names of the Files to Copy......Page 451
Step 1: Shut Down the Instance......Page 452
Step 2: Copy the Files Back from the Backup......Page 453
Scripting a Cold Backup and Restore......Page 454
Implementing Archivelog Mode......Page 456
Setting the Archive-Redo File Location......Page 457
Setting the Archive Location to a User-Defined Disk Location (non-FRA)......Page 458
Using the FRA for Archive Log Files......Page 460
Thinking Unoraclethodox FRA Thoughts......Page 461
Enabling Archivelog Mode......Page 462
Reacting to a Lack of Disk Space in Your Archive Log Destination......Page 463
Making a Cold Backup of an Archivelog-Mode Database......Page 464
Making a Hot Backup......Page 466
Step 4: Note the Maximum Sequence Number of the Online-Redo Logs......Page 467
Step 7: Alter the Database/Tablespaces Out of Backup Mode......Page 468
Step 10: Back Up Any Archive-Redo Logs Generated During the Backup......Page 469
Scripting Hot Backups......Page 470
Understanding the Split-Block Issue......Page 472
Understanding the Need for Redo Generated During Backup......Page 475
Understanding that Datafiles Are Updated......Page 476
Restoring and Recovering with the Database Offline......Page 477
Step 2: Restore the Datafile from the Backup......Page 478
Step 3: Issue the Appropriate RESTORE Statement......Page 479
Restoring and Recovering with a Database Online......Page 480
Restoring Control Files......Page 481
Restoring When All Control Files Are Damaged......Page 482
Performing an Incomplete Recovery of an Archivelog-Mode Database......Page 485
Flashing Back a Table......Page 487
FLASHBACK TABLE TO BEFORE DROP......Page 488
FLASHBACK TABLE TO RESTORE POINT......Page 489
Flashing Back a Database......Page 490
Summary......Page 492
Configuring RMAN......Page 493
Understanding RMAN......Page 494
Starting RMAN......Page 496
RMAN Architectural Decisions......Page 498
1. Running the RMAN Client Remotely or Locally......Page 500
4. Setting the Archive-Redo Log Destination and File Format......Page 501
5. Configuring the RMAN Backup Location and File Format......Page 502
7. Specifying the Location of the Autobackup of the Control File......Page 504
9. Determining the Location for the Snapshot Control File......Page 505
11. Using a Media Manager......Page 506
13. Configuring RMAN’s Backup-Retention Policy......Page 507
14. Configuring the Archive-Redo Logs’ Deletion Policy......Page 508
15. Setting the Degree of Parallelism......Page 509
16. Using Backup Sets or Image Copies......Page 510
18. Using Incrementally Updated Backups......Page 511
20. Configuring Binary Compression......Page 512
22. Configuring Miscellaneous Settings......Page 513
Segueing from Decisions to Action......Page 514
Creating a Recovery Catalog......Page 518
Backing Up the Recovery Catalog......Page 520
Dropping a Recovery Catalog......Page 521
Summary......Page 522
Preparing to Run RMAN Backup Commands......Page 523
Setting ECHO Setting ECHO......Page 524
Backing up the Entire Database......Page 525
Backup Sets versus Image Copies......Page 526
Backing up the Control File......Page 527
Backing up Archive Redo Logs......Page 528
Excluding Tablespaces from Backups......Page 529
Skipping Offline or Inaccessible Files......Page 530
Adding RMAN Backup Information to the Repository......Page 531
Creating Incremental Backups......Page 532
Taking Incremental Level Backups......Page 533
Making Incrementally Updating Backups......Page 534
Checking for Corruption in Datafiles and Backups......Page 535
Using VALIDATE......Page 536
Redirecting Output to a File......Page 537
Capturing Output with the script Command......Page 538
Querying for Output in the Data Dictionary......Page 539
Using REPORT......Page 540
Using SQL......Page 541
Summary......Page 544
RMAN Restore and Recovery......Page 545
Determining Media Recovery Required......Page 546
How the Process Works......Page 547
Listing Failures......Page 548
Suggesting Corrective Action......Page 549
Repairing Failures......Page 550
Starting Up......Page 551
Previewing Backups Used for Recovery......Page 552
Testing Media Recovery......Page 553
Restoring Entire Database......Page 554
Restoring Tablespaces......Page 555
Restoring Read-Only Tablespaces......Page 556
Restoring Datafile While Database Is Open......Page 557
Restoring Datafiles to Non-Default Locations......Page 558
Performing Block Level Recovery......Page 559
Restoring Archive Redo Log Files......Page 560
Restoring to the Default Location......Page 561
Restoring the Spfile......Page 562
Using a Recovery Catalog......Page 563
Specifying a Filename......Page 564
Incomplete Recovery......Page 565
Determining the Type of Incomplete Recovery......Page 567
Performing Log Sequenced-Based Recovery......Page 568
Restoring to a Restore Point......Page 569
Restoring and Recovering to Different Server......Page 570
Step 1: Create an RMAN Backup on the Originating Database......Page 571
Step 5: Create an init.ora File for the Database to be Restored......Page 572
Step 7: Startup the Database in NOMOUNT Mode......Page 573
Step 10: Make the Control File Aware of the Location of the RMAN Backups......Page 574
Step 11: Rename and restore the datafiles to Reflect New Directory Locations......Page 575
Step 12: Recover the Database......Page 576
Step 13: Set the New Location for the Online Redo Logs......Page 577
Step 15: Add tempfile......Page 578
Summary......Page 579
OSB Editions and Features......Page 581
OSB Administrative Domain and Servers......Page 582
OSB Interfaces......Page 583
OSB Daemons......Page 584
Download and Installation......Page 585
Command-line Access to OSB......Page 587
Configuring Users and Classes......Page 588
Configuring Media Families......Page 590
Configuring Database Backup Storage Selector......Page 591
Database Backup......Page 592
Database Restore......Page 593
File System Backup......Page 594
Creating Dataset Files......Page 595
Configuring Backup Schedules and Triggers......Page 596
Performing On-Demand File-System Backups......Page 597
Performing a Raw Restore......Page 598
Performing an obtar Restore......Page 599
Listing Jobs......Page 600
Showing Job Transcripts......Page 601
Virtual Test Devices......Page 602
OSB Software Upgrades......Page 603
Summary......Page 604
Automating Jobs......Page 605
Creating and Scheduling a Job......Page 606
Viewing Job Details......Page 607
Stopping a Job......Page 608
Running a Job Manually......Page 609
Oracle Scheduler versus cron......Page 610
How cron Works......Page 611
Understanding cron Table Entries......Page 613
Scheduling a Job to Run Automatically......Page 614
Editing the cron Table Directly......Page 615
Loading the cron Table from a File......Page 616
Troubleshooting cron......Page 617
Examples of Automated DBA Jobs......Page 618
Starting and Stopping Database and Listener......Page 619
Checking for Archive Redo Destination Fullness......Page 622
Truncating Large Log Files......Page 624
Checking for Locked Production Accounts......Page 625
Checking for Files over a Certain Age......Page 626
Checking for Too Many Processes......Page 627
Verifying Integrity of RMAN Backups......Page 628
Summary......Page 629
Quickly Triaging......Page 631
Checking Database Availability......Page 632
Locating the Alert Log and Trace Files......Page 633
Removing Files......Page 635
Viewing the Alert Log via OS Tools......Page 636
Viewing the alert.log Using the ADRCI Utility......Page 637
Identifying Bottlenecks via Operating System Utilities......Page 638
Identifying System Bottlenecks......Page 639
Using vmstat......Page 640
Using top......Page 642
Mapping an Operating System Process to a SQL Statement......Page 644
Monitoring Real-Time SQL Execution Statistics......Page 646
Displaying Resource Intensive SQL......Page 647
Running Oracle Diagnostic Utilities......Page 648
Using ADDM......Page 650
Using Statspack......Page 651
Detecting and Resolving Locking Issues......Page 652
Resolving Open Cursor Issues......Page 654
Determining if Undo is Correctly Sized......Page 656
Viewing SQL that is Consuming Undo Space......Page 658
Determining if Temporary Tablespace is Sized Correctly......Page 659
Viewing SQL that is Consuming Temporary Space......Page 660
Auditing......Page 661
Enabling Oracle Standard Auditing......Page 662
Auditing DML Usage......Page 663
Auditing Logon/Logoff Events......Page 664
Viewing Enabled Audit Actions......Page 665
Turning Auditing Off......Page 666
Purging the Audit Table and Files......Page 667
Moving the Audit Table to a Non-System Tablespace......Page 668
Auditing at a Granular Level......Page 669
Summary......Page 670
¦ A......Page 673
¦ B......Page 676
¦ C......Page 678
¦ D......Page 681
¦ E......Page 686
¦......Page 688
¦ G......Page 689
¦ H......Page 690
¦ I......Page 691
¦ K......Page 693
¦ M......Page 695
¦ N......Page 696
¦ O......Page 697
¦......Page 700
¦ R......Page 702
¦ S......Page 706
¦ T......Page 709
¦ U......Page 712
¦ V......Page 713
¦ Z......Page 715