Using Parallelism to Reduce Elapsed Time ........................................................................................ 356
Compressing a Dump File .................................................................................................................. 356
Encrypting a Dump File ...................................................................................................................... 356
Preprocessing an External Table ................................................................................ 357
Summary ..................................................................................................................... 359
■Chapter 15: Materialized Views ........................................................................ 361
Understanding Materialized Views ............................................................................. 361
Materialized View Terminology .......................................................................................................... 363
Referencing Useful Views .................................................................................................................. 364
Creating Basic Materialized Views .............................................................................. 365
Creating a Complete-Refreshable Materialized View ......................................................................... 365
Creating a Fast-Refreshable Materialized View ................................................................................. 369
Going Beyond the Basics ............................................................................................ 374
Creating MVs and Specifying Tablespace for MVs and Indexes ........................................................ 374
Creating Indexes on MVs .................................................................................................................... 374
Partitioning Materialized Views ......................................................................................................... 375
Compressing a Materialized View ...................................................................................................... 376
Encrypting Materialized View Columns .............................................................................................. 376
Building a Materialized View on a Prebuilt Table ............................................................................... 377
xviii
■ CONTENTS
Creating an Unpopulated Materialized View ...................................................................................... 378
Creating a Materialized View Refreshed on Commit .......................................................................... 378
Creating a Never-Refreshable Materialized View .............................................................................. 379
Creating Materialized Views for Query-Rewrite ................................................................................. 380
Creating a Fast-Refreshable MV Based on a Complex Query ............................................................ 381
Viewing Materialized View DDL ......................................................................................................... 383
Dropping a Materialized View ............................................................................................................ 384
Modifying Materialized Views ..................................................................................... 385
Modifying Base-Table DDL and Propagating to Materialized Views .................................................. 385
Toggling Redo Logging on a Materialized View ................................................................................. 388
Altering Parallelism ............................................................................................................................ 389
Moving a Materialized View ............................................................................................................... 389
Managing Materialized View Logs .............................................................................. 390
Creating a Materialized View Log ...................................................................................................... 391
Indexing Materialized View Log Columns .......................................................................................... 392
Viewing Space Used by a Materialized View Log ............................................................................... 392
Shrinking the Space in a Materialized View Log ................................................................................ 393
Checking the Row Count of a Materialized View Log ......................................................................... 393
Moving a Materialized View Log ........................................................................................................ 394
Dropping a Materialized View Log ..................................................................................................... 395
Refreshing Materialized Views ................................................................................... 395
Manually Refreshing Materialized Views from SQL*Plus ................................................................... 396
Automating Refreshes Using a Shell Script and Scheduling Utility .................................................... 397
Creating an MV with a Refresh Interval ............................................................................................. 398
Efficiently Performing a Complete Refresh ........................................................................................ 399
Handling the ORA-12034 Error ........................................................................................................... 399
Monitoring Materialized View Refreshes .................................................................... 400
Viewing Materialized Views’ Last Refresh Times .............................................................................. 400
Determining Whether a Refresh Is in Progress .................................................................................. 401
Monitoring Real-Time Refresh Progress ............................................................................................ 401
xix
■ CONTENTS
Checking Whether MVs Are Refreshing Within a Time Period . .......................................................... 402
Creating Remote Materialized View Refreshes . ......................................................... 403
Understanding Remote-Refresh Architectures ................................................................................... 404
Viewing Materialized View Base-Table Information ........................................................................... 405
Determining How Many MVs Reference a Central MV Log . ............................................................... 406
Managing Materialized Views in Groups . ................................................................... 408
Creating a Materialized View Group . ................................................................................................... 408
Altering a Materialized View Refresh Group . ...................................................................................... 409
Refreshing a Materialized View Group . ............................................................................................... 409
DBMS_MVIEW vs. DBMS_REFRESH . ................................................................................................... 409
Determining Materialized Views in a Group . ....................................................................................... 410
Adding an MV to a Refresh Group . ...................................................................................................... 410
Removing Materialized Views from a Refresh Group . ........................................................................ 411
Dropping a Materialized View Refresh Group ..................................................................................... 411
Summary . ................................................................................................................... 411
■Chapter 16: User-Managed Backup and Recovery ........................................... 413
Implementing a Cold-Backup Strategy for a Noarchivelog-Mode Database .............. 414
Making a Cold Backup of a Noarchivelog-Mode Database ................................................................. 414
Restoring a Cold Backup in Noarchivelog Mode with Online-Redo Logs . .......................................... 416
Restoring a Cold Backup in Noarchivelog Mode Without Online-Redo Logs . ..................................... 417
Scripting a Cold Backup and Restore . ................................................................................................. 418
Implementing Archivelog Mode .................................................................................. 420
Making Architectural Decisions . ......................................................................................................... 421
Setting the Archive-Redo File Location . .............................................................................................. 421
Enabling Archivelog Mode .................................................................................................................... 426
Disabling Archivelog Mode ................................................................................................................... 427
Reacting to a Lack of Disk Space in Your Archive Log Destination . ................................................... 427
Backing Up Archive-Redo Log Files . ................................................................................................... 428
Making a Cold Backup of an Archivelog-Mode Database ................................................................... 428
Implementing a Hot-Backup Strategy . ....................................................................... 430
xx
■ CONTENTS
Making a Hot Backup ......................................................................................................................... 430
Scripting Hot Backups ........................................................................................................................ 434
Understanding the Split-Block Issue .................................................................................................. 436
Understanding the Need for Redo Generated During Backup ............................................................ 439
Understanding that Datafiles Are Updated ......................................................................................... 440
Performing a Complete Recovery of an Archivelog-Mode Database .......................... 441
Restoring and Recovering with the Database Offline ........................................................................ 441
Restoring and Recovering with a Database Online ............................................................................ 444
Restoring Control Files ....................................................................................................................... 445
Performing an Incomplete Recovery of an Archivelog-Mode Database ...................... 449
Flashing Back a Table ................................................................................................. 451
FLASHBACK TABLE TO BEFORE DROP ................................................................................................ 452
Flashing Back a Table to a Previous Point in Time ............................................................................ 453
Flashing Back a Database ........................................................................................... 454
Summary ..................................................................................................................... 456
■Chapter 17: Configuring RMAN ......................................................................... 457
Understanding RMAN .................................................................................................. 458
Starting RMAN ............................................................................................................ 460
RMAN Architectural Decisions .................................................................................... 462
1. Running the RMAN Client Remotely or Locally .............................................................................. 464
2. Specifying the Backup User ........................................................................................................... 465
3. Using Online or Offline Backups ..................................................................................................... 465
4. Setting the Archive-Redo Log Destination and File Format ........................................................... 465
5. Configuring the RMAN Backup Location and File Format .............................................................. 466
6. Setting Autobackup of the Control File .......................................................................................... 468
7. Specifying the Location of the Autobackup of the Control File ...................................................... 468
8. Backing Up Archive-Redo Logs ...................................................................................................... 469
9. Determining the Location for the Snapshot Control File ................................................................ 469