====== Oracle 10g Administration Workshop II ====== ===== Introduction ===== - Course Objectives - How DBAs Spend Their Time - Oracle Database 10g Manageability Goals - Database Management Challenges - Oracle Database 10g Solution: Self-Managing Database - How Oracle Database 10g DBAs Spend Their Time - Student Preface ===== Using Globalization Support ===== - Objectives - Globalization Support Features - Encoding Schemes - Database Character Sets and National Character Sets - Datetimes with Timezones - Configuring the Database Local Timezone - Configuring Datetime Formats - Using Timezones - Specifying Language-Dependent Behavior - Specifying Language-Dependent Behavior for the Server - Language and Territory Dependent Parameters - Other NLS Server Parameters - Specifying Language-Dependent Behavior for the Session - Locale Variants - Using NLS Parameters in SQL Functions - Linguistic Sorting - Using Linguistic Sorting - Sorts That Are Not Case or Accent Sensitive - Linguistic Comparisons - Linguistic Index Support - Customizing Linguistic Sorting - Oracle Locale Builder - Character Set Scanner Utilities - Data Conversion Between Client and Server Character Sets - NLS Data Conversion with Oracle Utilities - NLS Data Conversion with Data Pump - Obtaining Character Set Information - Obtaining NLS Parameter Information - Summary - Practice Overview: Using Globalization Support Features - Practice: Using Globalization Support Features - Practice: Globalization Support ===== Controlling Access to the Oracle Listener ===== - Objectives - Oracle Net Services Review - Listener Password Authentication - Setting Listener Password - Setting Listener Password with Net Manager - Set Password with lsnrctl Utility - Controlling Database Access - Oracle Net Services External Procedures - Overview of the EXTPROC Agent - PL/SQL Calling a C External Procedure - Default Configuration for External Procedure Calls - Modifying the Configuration for External Procedure Calls - Remove Default EXTPROC Entry - Configure a Dedicated Listener for External Procedure Calls - Summary - Practice Overview: Controlling Access to the Listener ===== Configuring Recovery Manager ===== - Objectives - Recovery Manager Features - Recovery Manager Components - Media Management - Using a Flash Recovery Area with RMAN - Setting Parameters for RMAN - RMAN Usage Considerations - Connection Types with RMAN - Starting RMAN - Additional RMAN Command Line Arguments - Configuring Persistent Settings for RMAN - Configuring RMAN Settings Using EM - Control File Autobackups - Retention Policies - Managing Persistent Settings - Channel Allocation - Automatic and Manual Channel Allocation - Channel Control Options - Summary - Practice Overview: Configuring RMAN ===== Using Recovery Manager ===== - Objectives - Issuing Recovery Manager Commands - RMAN Command Overview - RMAN Commands - Job Command: Example - The BACKUP Command - Backup Constraints - Parallelization of Backup Sets - Compressed Backups - Image Copy - Tags for Backups and Image Copies - BACKUP Options - Backing Up Archived Redo Logs - Copying the Whole Database - Making Incremental Backups - Incremental Backup: Example - Block Change Tracking - Enabling Block Change Tracking - Incrementally Updating Backups - LIST Command Operations - The REPORT Command - The REPORT NEED BACKUP Command - REPORT NEED BACKUP: Examples - REPORT OBSOLETE and DELETE OBSOLETE - Managing Backups with EM - RMAN Dynamic Views - Monitoring RMAN Backups - Summary - Practice Overview: Using RMAN ===== Diagnostic Sources ===== - Objectives - Diagnostic Files - The Alert Log - What Is in the alert.log File - Viewing Recent Alert Log Entries - Alert Models Architecture - Server-Generated Alert Types - Viewing Alerts with Enterprise Manager - Alerts Notification - Alert Log Monitoring Configuration - Editing Thresholds - Viewing Initialization Parameters - Trace Files - Specifying the Location of Trace Files - Controlling Trace File Size - Controlling Trace File Writes - Using Enterprise Manager to Enable and View SQL Tracing - System Log Files - Summary - Practice Overview: Diagnosing Problems ===== Recovering from Noncritical Losses ===== - Objectives - Recovery of Noncritical Files - Creating New Temporary Tablespace - Re-creating Redo Log Files - Recovering an Index Tablespace - Re-creating Indexes - Read-only Tablespace Recovery - Read-only Tablespace Recovery Issues - Authentication Methods for Database Administrators - Loss of Password Authentication File - Summary - Practice Overview: Re-creating a Temporary Tablespace - Practice: Re-creating the Temp Tablespace ===== Database Recovery ===== - Objectives - Recovery Steps - Server Managed Recovery: RESTORE and RECOVER Commands - User-Managed Recovery Procedures:RECOVER Command - Recovering a Control File Autobackup - Creating a New Control File - Incomplete Recovery Overview - Situations Requiring Incomplete Recovery - Types of Incomplete Recovery - Incomplete Recovery Best Practices - Using RECOVER for Incomplete Recovery - UNTIL TIME Recovery Example - UNTIL TIME Recovery Steps - Cancel-Based Recovery: Example - Incomplete Recovery and the Alert Log - Incomplete Recovery of a Database Using RMAN - RMAN Incomplete Recovery UNTIL TIME: Example - RMAN Incomplete Recovery UNTIL SEQUENCE: Example - Recovery Using Enterprise Manager - Simplified Recovery Through RESETLOGS - Recovery Through RESETLOGS: Changes - Summary - Practice Overview: Incomplete Recovery ===== Flashback Database ===== - Objectives - Flashback Any Error - Flashback Technology Benefits - When to Use Flashback Technology - Flashback Database Overview - Flashback Database Reduces Restore Time - Flashback Database Architecture - Configuring Flashback Database - Configure Flashback Database with EM - Monitoring Flashback Database - Monitoring Flashback Database with EM - Best Practices for the Database and Flash Recovery Area - Backing Up the Flash Recovery Area - Flash Recovery Area Space Usage - Flashback Database Examples - Flashback Database with EM - Excluding Tablespaces from Flashback Database - Flashback Database Considerations - Summary - Practice Overview: Flashback Database ===== Recovering from User Errors ===== - Objectives - Flashback Time Navigation - Flashback Drop Overview - Recycle Bin - Querying the Recycle Bin - Flashback Dropped Tables Using EM - Restoring Objects from the Recycle Bin - Recycle Bin Automatic Space Reclamation - Recycle Bin Manual Space Reclamation - Bypassing the Recycle Bin - Querying Dropped Tables - Flashback Drop Considerations - Flashback Versions Query Overview - Flashback Versions Query Using EM - Flashback Versions Query Syntax - Flashback Versions Query Example - Flashback Versions Query Considerations - Flashback Transaction Query Overview - Querying FLASHBACK_TRANSACTION_QUERY - Using Flashback Versions Query and Flashback Transaction Query - Flashback Transaction Query Using EM - Flashback Transaction Query Considerations - Flashback Table Overview - Using EM to Flashback Tables - Flashback Table Example - Rolling Back a Flashback Table Operation - Flashback Table Considerations - Guaranteed Undo Retention - SCN and Time Mapping Enhancements - Summary - Practice Overview: Recovering from User Errors ===== Dealing with Database Corruption ===== - Objectives - What Is Block Corruption? - Block Corruption Symptoms: ORA-1578 - DBVERIFY Utility - Interpreting DBVERIFY - The ANALYZE Command - Initialization Parameter DB_BLOCK_CHECKING - How to Handle Corruptions - Using Flashback for Logical Corruption - The DBMS_REPAIR Package - Using DBMS_REPAIR - Block Media Recovery (BMR) - The BLOCKRECOVER Command - RMAN BMR Interface - Examples of BLOCKRECOVER - Which Object Is Corrupted? - Summary - Practice Overview: Dealing with Database Corruption ===== Automatic Management ===== - Objectives - Oracle Wait Events - System Statistics - Displaying Session-Related Statistics - Troubleshooting and Tuning Views - Statistics Collection - Automatic Optimizer Statistics Collection: Overview - Dictionary and Special Views - Statspack - Workload Repository - AWR Snapshot Baselines - Advisory Framework Overview - Database Control and Advisors - Typical Advisor Tuning Session - Manually Invoking ADDM - Application Tuning Challenges - SQL Tuning Advisor Overview - SQL Tuning Advisor Options and Recommendations - Using the SQL Tuning Advisor - Using the SQL Tuning Advisor: Example - Using the SQL Access Advisor - The Undo Management Page - Automatic Undo Retention Tuning - The Undo Advisor Page - Summary - Practice Overview: Optimizing Database Performance ===== Monitoring and Managing Storage ===== - Objectives - Online Redo Log File Configuration - Redo Logfile Sizing Advisor - Increasing the Performance of Archiving - Resumable Statements - Using Resumable Space Allocation - Resuming Suspended Statements - Proactive Tablespace Monitoring Overview - Tablespace Space Usage Monitoring - Edit Tablespace Page - Segment Advisor Overview - Shrinking Segments: Overview - Shrinking Segments: Considerations - Database Control and Segment Shrink - Accessing the Segment Advisor - Segment Advisor - Shrinking Segments Using SQL - Segment Shrink: Execution Considerations - Segment Resource Estimation - Growth Trend Report - Monitoring Index Space - Monitoring Index Space Usage - Deciding Whether to Rebuild or Coalesce an Index - Identifying Unused Indexes - Index-Organized Tables - Index-Organized Tables and Heap Tables - Creating Index-Organized Tables - IOT Row Overflow - Querying DBA_TABLES for IOT Information - Querying DBA_INDEXES and DBA_SEGMENTS for IOT information - Using a Mapping Table - Maintaining a Mapping Table - Clusters - Cluster Types - Situations Where Clusters Are Useful - Sorted Hash Cluster: Example - Summary - Practice Overview: Managing Storage ===== Automatic Storage Management ===== - Objectives 14--2 - What Is Automatic Storage Management? - ASM Key Features and Benefits - ASM Concepts - ASM General Architecture - ASM Instance Functionalities - ASM Instance Creation - ASM Instance Initialization Parameters - Accessing an ASM Instance - Dynamic Performance View Additions - ASM Home Page - ASM Performance Page - ASM Configuration Page - Starting Up an ASM Instance - Shutting Down an ASM Instance - ASM Disk Groups - Failure Group - Disk Group Mirroring - Disk Group Dynamic Rebalancing - ASM Administration Page - Create Disk Group Page - Create or Delete Disk Groups - Adding Disks to Disk Groups - Miscellaneous Alter Commands - Monitoring Long-Running Operations Using V$ASM_OPERATION - ASM Files - ASM Filenames - ASM File Name Syntax - ASM File Name Mapping - ASM File Templates - Template and Alias Examples - Retrieving Aliases - SQL Commands and File Naming - DBCA and Storage Options - Database Instance Parameter Changes - Migrating Your Database to ASM Storage - Summary - Practice Overview: Using ASM ===== Monitoring and Managing Memory ===== - Objectives - Oracle Memory Structures - Oracle Memory Structures Buffer Cache - Using Multiple Buffer Pools - Shared Pool - Large Pool - UGA and Oracle Shared Server - Java Pool - The Redo Log Buffer - Automatic Shared Memory Management: Overview - Benefits of Automatic Shared Memory Management - SGA Tuning Principles - Database Control and Automatic Shared Memory Management - Manual Configuration - Behavior of Auto-Tuned SGA Parameters - Behavior of Manually Tuned SGA Parameters - Using the V$PARAMETER View - Resizing SGA_TARGET - Disabling Automatic Shared Memory Management - Manually Resizing Dynamic SGA Parameters - Program Global Area (PGA) - Automatic PGA Memory Management - PGA Management Resources - Using the Memory Advisor - Summary - Practice Overview: Automatic Shared Memory Management ===== Managing Resources ===== - Objectives - Overview - Database Resource Manager Concepts - Resource Manager Configurations - The Initial Plan: SYSTEM_PLAN - Creating a New Resource Plan - Creating a Simple Plan - Oracle Enterprise Manager: Resource Manager - Active Session Pool Mechanism - Setting the Active Session Pool - Maximum Estimated Execution Time - Setting Idle Timeouts - Switching Back to the Initial Consumer Group at End of Call - Creating Resource Consumer Groups - Assigning Users to Consumer Groups - Automatic Consumer Group Switching - Configuring Consumer Group Switching - Adaptive Consumer Group Mapping - Creating a Mapping Using DBMS_RESOURCE_MANAGER - Assigning Priorities Using DBMS_RESOURCE_MANAGER - Using Sub-Plans to Limit CPU Utilization - Limiting CPU Utilization: Example - Creating a Complex Plan - Resource Allocation Methods for Resource Plans - Comparison of EMPHASIS and RATIO - Resource Allocation Methods for Consumer Groups - Administering the Resource Manager - Assigning Resource Manager Privileges - Setting the Resource Plan for an Instance - Viewing Resource Consumer Groups - Changing a Consumer Group Within a Session - Changing Consumer Groups for Sessions - Database Resource Manager Information - Resource Plan Directives - Monitoring the Resource Manager - Summary - Practice Overview: Using the Resource Manager ===== Automating Tasks with the Scheduler ===== - Objectives - Scheduling Needs - Scheduler Concepts - Privileges for Scheduler Components - Creating a Scheduler Job - Creating a Scheduler Job: Example - Setting the Repeat Interval for a Job - Calendaring Expressions - Using Scheduler Programs - Creating a Program Using EM - Specifying Schedules for a Job - Creating and Using Schedules - Using EM to Create Schedules - Advanced Scheduler Concepts - Creating a Job Class - Job Logging - Creating a Window - Prioritizing Jobs Within a Window - Enabling and Disabling Scheduler Components - Managing Jobs - Managing Programs - Managing Programs with EM - Managing Schedules - Managing Windows - Window Priority - Managing Attributes of Scheduler Components - Managing Attributes of the Scheduler - Viewing Job Execution Details - Viewing Job Logs - Purging Job Logs - Data Dictionary Views - Summary - Practice Overview: Automating Tasks with the Scheduler - Practice: Using the Scheduler ===== Workshop ===== - Objectives - Workshop Methodology - Business Requirements - Database Configuration - Simulated Application - Method for Resolving Database Issues - Summary - Practice Overview: Workshop Setup - Practice Workshop Setup - Workshop Scenario 1