Pro Oracle Database 11g Administration (7 page)

Step 15: Add tempfile ........................................................................................................................ 542

Step 16: Rename the Database .......................................................................................................... 543

Summary ..................................................................................................................... 543

■Chapter 20: Oracle Secure Backup ................................................................... 545

OSB Editions and Features .......................................................................................... 545

OSB Terminology ........................................................................................................ 546

OSB Administrative Domain and Servers ........................................................................................... 546

OSB Interfaces ................................................................................................................................... 547

OSB Users and Classes ...................................................................................................................... 548

OSB Daemons .................................................................................................................................... 548

Download and Installation .......................................................................................... 549

Command-line Access to OSB .................................................................................... 551

OSB Configuration ....................................................................................................... 552

Configuring Users and Classes .......................................................................................................... 552

Configuring Media Families ............................................................................................................... 554

Configuring Database Backup Storage Selector ................................................................................ 555

Database Backup ........................................................................................................ 556

Database Restore ........................................................................................................ 557

File System Backup .................................................................................................... 558

xxv

■ CONTENTS

Creating Dataset Files ........................................................................................................................ 559

Configuring Backup Windows ............................................................................................................ 560

Configuring Backup Schedules and Triggers ..................................................................................... 560

Performing On-Demand File-System Backups .................................................................................. 561

File System Restore .................................................................................................... 562

Performing Catalog-Based Restore .................................................................................................... 562

Performing a Raw Restore ................................................................................................................. 562

Performing an obtar Restore .............................................................................................................. 563

OSB Job Monitoring .................................................................................................... 564

Listing Jobs ........................................................................................................................................ 564

Showing Job Transcripts ................................................................................................................... 565

Virtual Test Devices .................................................................................................... 566

OSB Software Upgrades .............................................................................................. 567

Summary ..................................................................................................................... 568

■Chapter 21: Automating Jobs ............................................................................ 569

Automating Jobs with Oracle Scheduler ..................................................................... 570

Creating and Scheduling a Job .......................................................................................................... 570

Viewing Job Details ............................................................................................................................ 571

Modifying Job Logging History ........................................................................................................... 572

Modifying a Job .................................................................................................................................. 572

Stopping a Job ................................................................................................................................... 572

Disabling a Job .................................................................................................................................. 573

Enabling a Job ................................................................................................................................... 573

Copying a Job .................................................................................................................................... 573

Running a Job Manually ..................................................................................................................... 573

Deleting a Job .................................................................................................................................... 574

Oracle Scheduler versus cron ..................................................................................... 574

Automating Jobs via cron ........................................................................................... 575

How cron Works ................................................................................................................................. 575

Enabling Access to cron ..................................................................................................................... 577

xxvi

■ CONTENTS

Understanding cron Table Entries ...................................................................................................... 577

Scheduling a Job to Run Automatically ............................................................................................. 578

Redirecting cron Output ..................................................................................................................... 581

Troubleshooting cron ......................................................................................................................... 581

Examples of Automated DBA Jobs .............................................................................. 582

Starting and Stopping Database and Listener ................................................................................... 583

Checking for Archive Redo Destination Fullness ............................................................................... 586

Truncating Large Log Files ................................................................................................................. 588

Checking for Locked Production Accounts ........................................................................................ 589

Checking for Files over a Certain Age ................................................................................................ 590

Checking for Too Many Processes ..................................................................................................... 591

Verifying Integrity of RMAN Backups ................................................................................................. 592

Summary ..................................................................................................................... 593

■Chapter 22: Database Troubleshooting ............................................................. 595

Quickly Triaging .......................................................................................................... 595

Checking Database Availability .......................................................................................................... 596

Investigating Disk Fullness ................................................................................................................ 597

Inspecting the Alert Log ..................................................................................................................... 600

Identifying Bottlenecks via Operating System Utilities ............................................... 602

Identifying System Bottlenecks ......................................................................................................... 603

Mapping an Operating System Process to a SQL Statement ............................................................. 608

Finding Resource Intensive SQL Statements .............................................................. 610

Monitoring Real-Time SQL Execution Statistics ................................................................................. 610

Displaying Resource Intensive SQL .................................................................................................... 611

Running Oracle Diagnostic Utilities .................................................................................................... 612

Detecting and Resolving Locking Issues ..................................................................... 616

Resolving Open Cursor Issues .................................................................................... 618

Troubleshooting Undo Tablespace Issues ................................................................... 620

Determining if Undo is Correctly Sized .............................................................................................. 620

Viewing SQL that is Consuming Undo Space ..................................................................................... 622

xxvii

■ CONTENTS

Handling Temporary Tablespace Issues ..................................................................... 623

Determining if Temporary Tablespace is Sized Correctly .................................................................. 623

Viewing SQL that is Consuming Temporary Space ............................................................................ 624

Auditing ....................................................................................................................... 625

Enabling Oracle Standard Auditing .................................................................................................... 626

Auditing DML Usage ........................................................................................................................... 627

Auditing Logon/Logoff Events ............................................................................................................ 628

Viewing Enabled Audit Actions .......................................................................................................... 629

Turning Auditing Off ........................................................................................................................... 630

Purging the Audit Table and Files ...................................................................................................... 631

Moving the Audit Table to a Non-System Tablespace ....................................................................... 632

Auditing at a Granular Level ............................................................................................................... 633

Summary ..................................................................................................................... 634

■Index ................................................................................................................. 637

xxviii