Pro Oracle Database 11g Administration (5 page)

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