Sumário Itens Encontrados: 1010Part 1 Oracle DBA Fundamentals 36Part 2 Oracle Database Net Services 238Part 3 Oracle Database Backup and Recovery 247Part 4 Oracle Database Security 311Part 5 Oracle Database Performance Tuning 340Part 6 Oracle Automatic Storage Management (ASM) 396Part 7 Oracle Real Application Cluster 410Part 8 Oracle RAC One Node 529Part 9 Oracle Warehousing 551Part 10 Oracle Database Utilities 556Part 11 Miscellaneous Oracle Database Topics 582Part 12 PL/SQL Samples 596Part 13 Appendixes 674Page 4 Oracle DBA Code ExamplesDBA: Best Practices 37DBA: Best Practice Guidelines for Standalone and RAC Databases 37Oracle Database Installation Consideration 38Estimating Disk and Memoery Requirements 38Optimal Flexible Architecture 38Oracle Products Installed with the 11.1 Release 39Installing Oracle 10g R2 on Enterprise Linux 4 40Installation Environment 40Required Software 40Used Hardware 40Installation Plan 401. Preinstallation tasks 402. Oracle Database 10g Software Installation 443. Apply Patchset 3 (10.2.0.4) for Clusterware and Database Software 444. Configure Listeners 455. Create Database 456. Postinstallation Tasks 45Installing Oracle 11g R2 on Enterprise Linux 5 47Installation Environment 47Required Software 47Used Hardware 47Installation Plan 471. Preinstallation tasks 472. Oracle Database 11g Software Installation 533. Apply Patchset 534. Configure Listeners 535. Create Database 536. Postinstallation Tasks 53Page 5 Oracle DBA Code ExamplesInstalling Oracle 11g R2 on Enterprise Linux 5.5 with ASM 54Installation Environment 54Required Software 54Used Hardware 54Installation Plan 541. Preinstallation tasks 552. Oracle Grid Infrastructure installation 603. Oracle Grid Infrastructure Patching 614. Oracle Database 11g R2 Software Installation 615. Oracle Database 11g R2 Software Patching 626. Install EM Agent in cluster nodes (if required) 627. ASM Diskgroups Creation 628. Database Creation 629. Postinstallation tasks 6210. General Useful Postinstallation Tasks in Linux 62Managing Oracle Database Instance 64Product Release Number 64Oracle Database Release Number Format 64Obtaining License Information 64Managing the Instance Architecture 64Obtaining Information about the Instance Processes 64Obtaining Information about the SGA 65Clearing the Buffer Cache 65Database Administration Authentication 65Using Operating System Authentication 65Using Password File Authentication 66Identifying Users SYSDBA or SYSOPER Users 66Data Dictionary and Dynamic Performance Views 66Data Dictionary Creation 66Startup and Shutdown 66Startup Levels 66Shutdown Levels 66Autostart of Database in Windows 66Automatically Starting Databases in Unix 66Page 6 Oracle DBA Code ExamplesQuiescing a Database 71Suspending a Database 71Dropping a Database 71Initialization Files 71Managing Initialization Files 71Managing Parameters in SPFILE 72Alert and Trace Files 72Monitoring Alert and Trace Files 72Managing Oracle Database Physical Structure 74Managing Control Files 74Obtaining Control File information 74Creating Additional Copies, Renaming, and Relocating Control Files 74Creating New Control Files 74Backing Up Control Files 75Manage the Size of Control Files 75Multiplexing the Control File 76Maintaining Online Redo Log Files 76Forcing Log Switches and Checkpoints 76Adding Online Redo Log File Groups 76Adding Online Redo Log File Members 76Dropping Online Redo Log File Groups 76Dropping Online Redo Log File Members 76Relocating and Renaming Redo Log Members 76Verifying Blocks in Redo Log Files 77Clearing a Redo Log File 77Viewing Redo Log Information 77Managing Archived Redo Logs 77Obtaining Information about Archive Log 77Changing the Database Archiving Mode 78Specifying Archive Destinations and their Options 78Specifying the Minimum Number of Successful Destinations 79Controlling Archiving to a Destination 79Controlling Trace Output Generated by the Archivelog Process 79Managing Tablespaces 79Obtaining Tablespace Information 79Page 7 Oracle DBA Code ExamplesCreating a Locally Managed Tablespace 83Specifying Segment Space Management 83Adding Space to Tablespace 83Specifying Nonstandard Block Sizes for Tablespaces 83Using Bigfile Tablespace (BFT) 83Using Temporary Tablespace 84Renaming a Tempfile 84Shrinking Temporary Tablespace 84Using Default Temporary Tablespace 85Using Temporary Tablespace Groups 85Suppressing Redo Generation for a Tablespace 85Controlling Tablespaces Availability 85Using Read-Only Tablespaces 85Renaming Tablespaces 85Default Permanent Tabelspace 85Dropping Tablespaces 86Managing the SYSAUX Tablespace 86Diagnosing and Repairing Locally Managed Tablespace Problems 86Verifying the Integrity of Segments Created in ASSM Tablespaces. 86Checking Consistency of Segment Extent Map with Tablespace File Bitmaps 87Verifying the Integrity of ASSM Tablespaces 88Marking the Segment Corrupt or Valid 89Dropping a Corrupted Segment 89Dumping a Segment Header and Bitmap Blocks 90Marking a DBA Range in Bitmap as Free or Used 90Rebuilding the Appropriate Bitmap 91Rebuilding Quotas for Given Tablespace 91Migrating from a Dictionary-Managed to a Locally Managed Tablespace 91Fixing the State of the Segments in A Tablespace 91Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap) 91Scenario 2: Dropping a Corrupted Segment 92Scenario 3: Fixing Bitmap Where Overlap is Reported 92Scenario 4: Correcting Media Corruption of Bitmap Blocks 92Transporting Tablespaces Between Databases 92Managing Alert Thresholds 94Getting the Current Threshold Setting 94Setting Tablespace Alert Thresholds 95Restoring a Tablespace to Database Default Thresholds 96Page 8 Oracle DBA Code ExamplesModifying Database Default Thresholds 97Viewing Alerts 97Managing Datafiles and Tempfiles 98Creating Datafiles 98Enabling and Disabling Automatic Extension for a Datafile 98Manually Resizing a Datafile 98Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode 98Taking Datafiles Offline in NOARCHIVELOG Mode 98Renaming and Relocating Datafiles in a Single Tablespace 99Dropping Datafiles 99Copying a File on a Local File System 99Transferring a File to a Different Database 100Dumping a Data Block 100Managing Undo Tablespaces 101Obtaining Information on Undo 101Enabling Automatic Undo Management 102Creating an Undo Tablespace 102Setting Startup Undo Tablespace 102Tuning Undo Retention 102Using Undo Advisor 103Setting the Undo Retention Period 103Enabling Retention Guarantee 103Dropping an Undo Tablespace 103To Drop a Corrupt UNDO Tablespace 104Using Oracle Managed Files (OMF) 104Managing Schema Objects 106Chaching Small Tables in Memory 106Creating Virtual Columns 106Creating Partitioned Tables 106Partition Maintenance Operations 111Setting Deferred Segment Creation 112Creating Multiple Tables and Views in a Single Operation 112Collecting Object Statistics 113Collecting Index Statistics 113Collecting Table Statistics 113Page 9 Oracle DBA Code ExamplesCollecting Schema Statistics 114Validating Tables, Indexes, Clusters, and Materialized Views 115Listing Chained and Migrated Rows of Tables and Clusters 115Truncating Tables or Clusters 116Enabling and Disabling Triggers 116Managing Integrity Constraints 116Setting Constraint States and Deferability 116Modifying, Renaming, or Dropping Existing Integrity Constraints 117Reporting Constraint Exceptions 117Obtaining Information on Constraints 118Renaming Schema Objects 118Managing Object Dependencies 118Manually Recompiling Views Procedures and Packages 118Switching to a Different Schema 118Using DBMS METADATA to Display Information About Schema Objects 118Specifying Storage Parameters at Object Creation 118Managing Resumable Space Allocation 119Enabling Resumable Space Allocation 119Detecting Suspended Statements 119Obtaining Information about Suspended Statements 121Reclaiming Wasted Space 121Displaying Information About Space Usage for Schema Objects 121Segment Advisor 124Shrinking Database Segments Online 126Deallocating Unused Space 126Capacity Planning for Database Objects 127Estimating the Space Use of a Table 127Obtaining Object Growth Trends 128Using the SQL Access Advisor 129Estimating the Space Use of a Table 129Managing Tables 134Obtaining Information about Tables 134Creating Tables with some Options 134Page 10 Oracle DBA Code ExamplesDML Error Logging 135Enabling Direct-Path INSERT 135Automatically Collecting Statistics on Tables 136Altering Tables 136Performing Online Redefinition with DBMS REDEFINITION 137Redefining a Table 137Redefining a Single Partition 139Migrating BasicFile LOBs to SecureFiles 140Using Flashback Drop and Managing the Recycle Bin 141Managing Index-Organized Tables 141Managing External Tables 142Managing Indexes 145Using Indexes 145Using Bitmap Join Indexes (BJI) 146Partitioned Indexes 147Managing Materialized Views 148Obtaining Information about Materialized Views 148Monitoring the Progress of a Materialized View Refresh 148Materialized View Typical Refresh Errors 152Using Materialized Views 152Using Query Rewriting 155ReWrite Hints 157Using EXPLAIN MVIEW Procedure: Viewing Materialized View Capabilities 157Using DBMS ADVISOR.TUNE MVIEW 157Registering a User-defined Table as Materialized View 158Managing Clusters and Hash Clusters 159Managing Views, Sequences, and Synonyms 161Managing Transactions 162Implementing Oracleâs Concurrency Control 162Oracle Isolaction Levels 162Oracle Lock Types 162Page 11 Oracle DBA Code ExamplesIdentifying Blocking Sessions 163Using Autonomous Transaction 163Managing Long Transactions with Workspace Manager 164Repairing Corrupted Data 172Options for Repairing Data Block Corruption 172Detecting Corruptions Methods 172Using dbv (DBVerify) Utility 172Setting the Initialization Parameters for Detecting Corruption 172Verifying Block Integrity in Real Time: DB BLOCK CHECKING 172Verifying Block Integrity in Real Time: DB BLOCK CHECKSUM 173Detecting lost write: DB LOST WRITE PROTECT 173Settubg the DB ULTRA SAFE Parameter (In Oracle 11g) 173Using ANALYZE Command 173Using EXP to Detect Corruption 173Using DBMS REPAIR 174DBMS REPAIR Limitations and Restrictions 174Evaluate the Costs and Benefits of Using DBMS REPAIR 174Detect and Report Corruptions using DBMS REPAIR 175Managing Automated Database Maintenance Tasks 177Predefined Automated Maintenance Tasks 177Predefined Maintenance Windows 177Obtaining Information about Predefined Maintenance Tasks 177Enabling and Disabling Maintenance Tasks 178Configuring Maintenance Windows 178Managing Resources 180Obtaining Information on Database Resource Manager 191Monitoring Oracle Database Resource Manager 193Using Oracle Scheduler 195Using Jobs 195Using Programs 202Page 12 Oracle DBA Code ExamplesUsing Schedules 203Using Job Classes 204Using Windows 205Using Window Groups 206Monitoring Job State with Email Notifications 207Using File Watchers 209Using Events Raised by the Scheduler 212Using Events Raised by an Application (Events-Based Jobs) 214Using Chains 217Allocating Resources Among Jobs 221Administering Oracle Scheduler 221Configuring Oracle Scheduler 221Monitoring and Managing the Scheduler 222Enabling, Using and Disabling Remote External Jobs 223Import/Export and the Scheduler 225Scheduler Privileges 225Scheduler Data Dictionary Views 225Using the UTL FILE Package 226Data Loading and Transforming Tools 228Using Database Links 229Managing Diagnostic Data 230Setting the Automatic Diagnostic Repository Directory 230Using adrci Tool 230General usage of adrci 230Using adrci to Package Incidents 231Managing Database Health Monitor 231Managing Data Recovery Advisor 233Using SQL Test Case Builder 233Patching Oracle Products 235Using Oracle Opatch 235Page 13 Oracle DBA Code ExamplesConnectivity Naming Methods 239The Local Naming Method 239The Easy Connect Naming Method 239The External Naming Method 239The Directory Naming Method 240Database Resident Connection Pooling (DRCP) 240Oracle and Java Database Connectivity 242Establishing Database Connectivity 242Miscellaneous Connectivity Options 244Setting the Default Connect String 244Installing the Instant Client 244Setting Listener Options 244Setting Access Controls 244Changing Windows Hostname 245Backup Guidelines 248Causes of Unplanned Down Time 248Causes of Planned Down Time 248Oracleâs Solution to Down Time 249Minimizing Unplanned Downtime Guidelines 249SLA Sample 250Planning a Backup Strategy Guidelines 250Examples of Backup Schedules for a Database 251User-Managed Backups 252Obtaining Database File Information 252Making Whole Closed Database Backups 252Making a Whole Open Backup 252Page 14 Oracle DBA Code ExamplesMaking Tablespace Backups 253Obtaining Backup Status Information 253Checking Datafiles Taken as Backup 253Handling Crash Before User-Manged Backup Ends 253Backing up Control File 254Backing Up Initialization Files 254User-Managed Complete Recovery 255User-Managed Recovery in NOARCHIVELOG Mode 255User-Managed Recovery in NOARCHIVELOG Mode Without Redo Log File 255User-Managed Complete Recovery in ARCHIVELOG Mode 255Re-Creating Lost Datafiles Without Backup 256User-Managed Incomplete Recovery 257Common Situations Requiring Incomplete Recovery 257User-Managed Incomplete Recovery Steps 257Recovering from Lost Control File by Re-Creating the Control File 257Flash Recovery Area 261Obtaining Information on Flash Recovery Area 261Configuring Flash Recovery Area 261Backing Up the Flash Recovery Area 261Moving the Flash Recovery Area 262Recovery Manager (RMAN) 263Using A Media Management Layer (MML) with RMAN 263Obtaining Information about and related to RMAN using Dictionary Views 263Starting RMAN 265Using rlwrap Utility with RMAN in Unix-Based Systems 266Configuring the RMAN Environment 266RMAN Channel Commands 267Duration in days of RMAN information in Control File 268Monitoring RMAN Jobs 268Page 15 Oracle DBA Code ExamplesUsing RMAN BACKUP Command 268Backing Up Control File and SPFile 269Backing Up Archived RedLogs 269Backup in NOARCHIVELOG Mode 270Encrypting RMAN Backups 270Using Compression in RMAN Backups 270Using Multiplexed Backup Sets 270Using Parallelization of Backup Sets 270Using Duplexed Backup Sets (Backupset Copies) 271Making Image Copies 271Validating Backup 272Incremental Backup 272Tags for Backups and Image Copies 272Creating Archival Backups 272Monitoring RMAN Backups 273RMAN Complete Recovery 273Validating Backup Files 273Previewing Backup Files Required by a Restore 274Identifying Datafiles Requiring Recovery 274Performing Complete Recovery 274Restoring whole Database from RMAN Backups On a Different Node 275Restoring whole Database from RMAN Backups from A 32 bit to 64 bit 278RMAN Incomplete Recovery 278Simplified Recovery Through Resetlogs 279Recovering from Lost Control File using RMAN 279Block Media Recovery (BMR) 280Trial Recovery 280Handling Specific Errors During Recovery 280Configuring Instance Crash Recovery Time (MTTR) 282Working with the Data Recovery Advisor in RMAN 282RMAN Maintenance 282Cross Checking Backups and Copies 282Page 16 Oracle DBA Code ExamplesDeleting Backups and Copies 283Changing the Availability of RMAN Backups and Copies 283Exempting a Backup or Copy from the Retention Policy 283The CATALOG Command 284The CHANGE ⦠UNCATALOG Command 284RMAN Catalog 284Creating a Recovery Catalog 284RMAN Catalog Reporting 285Upgrading a Recovery Catalog 285Importing Recovery Catalogs 285Moving a Recovery Catalog 286Dropping a Recovery Catalog 286Virtual Private Catalogs 286Using RMAN Scripts 287Duplicating (Cloning) a Database 289Database Duplication (Cloning) Methods 289Database Duplication Techniques 289Database Duplication Prerequisites 289Duplicating an Active Database using RMAN 289Duplicating a Database without Recovery Catalog or Target Connection 291Manually Duplicating a Database 293Using Oracle Flashback Technology 297Flashback Options 298Preparing Your Database for Flashback 299Using Row Level Flashback Options 300Flashback Query 300Flashback Versions Query 300Flashback Transaction Query 301Flashback Transaction (Backout) 301Using Table Level Flashback Options 303Page 17 Oracle DBA Code ExamplesFlashback Table 303Flashback Drop 303Flashback Data Archive 304Using Flashback Database 308When to use Flashback Database 308Flashback Database Considerations 308Using Flashback Database 308Restore Points 309Oracle Database Security Management 312Security Guidelines 313Managing Users 315Database Authentication 317Managin Passwords 317External (OS) Authentication 317Proxy Authentication 318Logging In As a Different User 318Killing User Sessions from OS 318Controlling Database Access 320System and Object Privileges 320Invoker Rights and Definer Rights 320Roles 320Users, Roles, and Privileges Views 321Fine-Grained Data Access (Virtual Private Database VPD) 322Auditing Database 325Standard Auditing 325Customizing Database Auditing with Triggers 326Page 18 Oracle DBA Code ExamplesAuditing the Database Using System Trigger 328Using Fine Grained Auditing 331Using Data Encryption 334Oracle Transparent Data Encryption (TDE) 334Tablespace Encryption 335Fine-Grained Access Control for UTL * Packages 337Creating ACL 337Access Control Lists Maintenance 338Query Your Access Control List 339Managing Performance Statistics 341Managing OS Statistics 341Managing Database Statistics 345System and Session Statistics 345Time Model Statistics 346Wait Events 347Active Session History (ASH) 349Segment Statistics 351Handling Important Oracle Wait Events 351List of Idle Waits Events 353Using Performance Monitor Tool in Windows 355Using OS Watcher for Windows 355Optimizing Performance in Windows Server 357Optimizing Performance in Windows Server 2003 357Tuning the Database Instance 358Tuning the Shared Pool 358Tuning the Buffer Cache 359Tuning PGA 361Dumping the PGA 361Page 19 Oracle DBA Code ExamplesUsing Server Result Cache 362Obtaining Information about Object Locks 364Handling a Hanging Database 365Accurately Measuring Process Size 365Managing Automatic Workload Repository (AWR) 367Managing Automated Maintenance Tasks 369Using Automatic Database Diagnostic Monitor (ADDM) 369Using Automatic SQL Tuning Advisor 371Implementing Automatic Memory Management 374Configuring DB nK CACHE SIZE 376Managing Optimizer Operations 377Setting the Optimizer Mode 377Defining Access Paths and Joins for the Query Optimizer 377Gathering Optimizer Statistics 381Gathering Object Statistics 381Gathering System Statistics 382Changing Statistics Preferences 382Managing Pending and Published Statistics 383Managing Extended Statistics 384MultiColumn Statistics 384Expression Statistics 385A Simple Approach to Tuning SQL Statements 386Using Application Tracing Tools 387Using the SQL Trace Facility and TKPROF 387Using the Event 10046 to Trace SQL Code 387Tracing End to End Application 388Enabling and Disabling Statistic Gathering for End to End Tracing 389Page 20 Oracle DBA Code ExamplesWriting Efficient SQL 391Improving SQL Processing Techniques 394Using SQL Tuning Advisor 395Managing Oracle ASM 397Obtaining Information about ASM Instance 397Creating an ASM Instance 397Managing Disk Groups and Disks in ASM 398Fundementals of Managing Disk Groups and Disks 398Managing Disk Groups Attributes 399Monitoring Long-Running Operations 401Migrating a Database to ASM 402Moving a Tablespace to ASM 402Accessing an ASM instance from DB Console 403Managing ASM Files 404Using ASMCMD Utility 405Using SYSASM Privilege and OSASM Group 407Manually Upgrading Oracle AS from 10g to 11g 408Verifying Manually ASM Device 408Oracle RAC Possible Installation Configurations 411Installing Oracle 10g R2 RAC on Enterprise Linux 4 412Installation Environment 412Required Software 412Used Hardware 412Installation Plan 4121. Preinstallation tasks 4132. Oracle Clusterware installation 420Page 21 Oracle DBA Code Examples3. Oracle Database 10g Software Installation 4244. Apply Patchset 3 (10.2.0.4) for Clusterware and Database Software 4255. Install EM Agent in cluster nodes (if required) 4266. Configure Listeners 4267. Perform ASM installation 4278. Perform cluster database creation 4279. Postinstallation tasks 42910. Useful Postinstallation Tasks 430Installing Oracle 11g R2 RAC on Enterprise Linux 5 431Main Changes in Oracle 11g Release 2 RAC 431Installation Environment 431Required Software 431Used Hardware 431Installation Plan 4321. Preinstallation tasks 4332. Oracle Grid Infrastructure installation 4403. Oracle Grid Infrastructure Patching 4424. Oracle Database 11g R2 Software Installation 4425. Oracle Database 11g R2 Software Patching 4436. Install EM Agent in cluster nodes (if required) 4437. ASM Diskgroups Creation 4438. RAC Database Creation 4449. Postinstallation tasks 44510. General Useful Postinstallation Tasks in Linux 446Installing Oracle 10g R2 RAC on Windows 447Installation Methods 447Installation Environment 447Required Software 447Used Virtual Hardware 447Installation Plan 4481. Preinstallation tasks 4482. Oracle Clusterware installation 4503. Apply Patch Set 3 (10.2.0.4) on Clusterware software 4524. Oracle ASM 10g Software Installation 453Page 22 Oracle DBA Code Examples5. Apply Patchset 3 (10.2.0.4) on ASM Software 4546. Install EM Agent in cluster nodes (if required) 4547. Configure Listeners 4548. Create ASM Instance 4559. Install Oracle RAC Database Home Software 45610. Apply Patchset 3 (10.2.0.4) on Oracle RAC Software Home 45711. Perform cluster database creation 45712. Useful Postinstallation Steps 459Cleaning Up Clusterware Installation on Windows 460Single Instance to RAC Conversion 462The Tools to Convert a Single Instance DB to RAC 462Conversion Prerequisites for Oracle 10g R2 462Using rconfig Utitlity 462Using DBCA 463Administering RAC Database 465Administering Oracle Clusterware Components 466Managing Cluserware Daemons and Processes 466Displaying Clusterware Processes 466Starting, Stopping, Enabling and Disabling crs Stack 466CSS Parameters 466Administering Voting Disks in RAC 466Multiplexing Voting Disks 466Dynamically Adding and Removing Voting Disks after Installing RAC 467Backing up Voting Disks 467Recovering Voting Disks 467Administering the Oracle Cluster Registry (OCR) 467Replacing the OCR 468Adding and Removing the OCR 468Repairing the OCR 468Making Physical Backups of the OCR 468Recovering the OCR using the Physical Backups 469Making Logical Backups of the OCR (Exporting) 469Making Logical Backups of the OCR (Importing) 469Page 23 Oracle DBA Code ExamplesDiagnosing OCR Problems with the OCRDUMP and OCRCHECK Utilities 470Administering Storage 471Datafile Access in Real Application Clusters 471Redo Log File Storage in Real Application Clusters 471Automatic Undo Management in Real Application Clusters 471Administering ASM Instances with SRVCTL in RAC 472Administering Cluster Databases 473Displaying Current Instance in SQL*Plus Prompt 473Starting and Stopping Instances and RAC Databases 473Starting Up and Shutting Down with SQL*Plus 473Intermittent Windows Shutdown Issue in RAC Environments 473Starting Up and Shutting Down with SRVCTL 473Customizing How Oracle Clusterware Manages RAC Databases 474Switching Between the Database Automatic and Manual Policies 474Customizing Resource Parameters (like AUTO START) 474Handling Initialization Parameter Files in RAC 475Setting Server Parameter File Parameter Values for Real Application Clusters 475Parameters Used in RAC Databases 475Parameters that Must Have Identical Settings on All Instances 475Parameters That Must Have Unique Settings on All Instances 476Parameters that Should Have Identical Settings on All Instances 476ASM Instance Initialization Parameters and RAC 476Dropping a RAC Database 476Workload Management in RAC 477Types of Workload Distribution 477Connection Load Balancing 477Client-Side Load Balancing and Failover 477Server-Side Load Balancing 477Fast Application Notification (FAN) 478Using Fast Application Notification Callouts 478Configuring the Server-Side ONS 480Administering Load Balancing Advisory 480Page 24 Oracle DBA Code ExamplesMonitoring Load Balancing Advisory 481Transparent Application Failover (TAF) 482TAF Basic Configuration without FAN (From Client Side) 482TAF Basic Configuration with FAN (Server-Side) 482TAF Preconnect Configuration 482Verifying TAF Configuration 483Enabling Distributed Transaction Processing for Services 483Administering Services 484Service Attributes 484Administering Services with DBCA 484Administering Services with PL/SQL 484Administering Services with SRVCTL 486Controlling the Preferred and Available Instances 486Using Services with Client Applications 487Services and the Scheduler 487Measuring Performance by Service Using the AWR 488Service Thresholds and Alerts 490Service Performance Views 491Restricted Session and Services 491Configuring Recovery Manager and Archiving 492Backup Possible Distributions in RAC 492RMAN Restore Scenarios for Real Application Clusters 492Cluster File System Restore Scheme 492Non-Cluster File System Restore Scheme 492RMAN and Oracle Net in Real Application Clusters 492Connecting to Specific Node 492Instance Recovery in Real Application Clusters 493Single Node Failure in Real Application Clusters 493Multiple-Node Failures in Real Application Clusters 493Configuring the RMAN Snapshot Control File Location 493Configuring the RMAN Control File and SPFILE Autobackup Feature 493Page 25 Oracle DBA Code ExamplesConfiguring Channels for RMAN in Real Application Clusters 493Configuring Channels to use Automatic Workload Balancing 493Configuring Channels to Use a Specific Instance 493Node Affinity Awareness of Fast Connections 494Archived Redo Log File Conventions in RAC 494Archive Redo Log Configuration Scenarios 494Automatic Storage Management and CFS Archiving Scheme 494Non-Cluster File System Local Archiving Scheme 494Changing the Archiving Mode in Real Application Clusters 495Deleting Archived Redo Logs after a Successful Backup 495Monitoring the Archiver Processes 495Log Archive Dest 1 Set To Default Even When DB Recovery File Dest Is Set(Bug 6373164) 495Media Recovery in Real Application Clusters 497Parallel Recovery in Real Application Clusters 497Using a Flash Recovery Area in RAC 497Managing Backup and Recovery 498Administrative Options 499Using Enterprise Manager Grid Control to Discover Nodes and Instances 499Additional Information About SQL*Plus in RAC 499How SQL*Plus Commands Affect Instances 499Displaying Running Instances 499Displaying Connect Identifier 499Quiescing RAC Databases 500Quiesced State and Cold Backups 500Transparent Data Encryption and Wallets in RAC 500Administering System and Network Interfaces with oifcfg 500Defining Network Interfaces with oifcfg 500Syntax and Commands for the oifcfg Command-Line Tool 500Changing Public or Interconnect IP Subnet Configuration 501Changing VIP Addresses 501Adding Nodes and Instances on UNIX-Based Systems 503Page 26 Oracle DBA Code ExamplesAdding Nodes to a RAC Environment 503Cloning Oracle Clusterware and RAC Software in Grid Environments 503Quick-Start Node and Instance Addition Procedures 504Adding an Oracle Clusterware Home to a New Node 504Adding an Oracle Home with RAC to a New Node 505Detailed Node and Instance Addition Procedure 505Step 1: Connecting New Nodes to the Cluster 505Step 2: Extending Clusterware and Oracle Software to New Nodes 505Step 3: Preparing Storage on New Nodes 505Step 4: Adding Nodes at the Oracle RAC Database Layer 506Step 5: Adding Database Instances to New Nodes 506Deleting Nodes and Instances on UNIX-Based Systems 507Option 1: Quick-Start Node and Instance Deletion Procedures 507Deleting an Oracle Home with RAC from an Existing Node 507Deleting an Oracle Clusterware Home from an Existing Node 508Option 2: Detailed Node and Instance Deletion Procedure 509Step 1: Deleting DB Instances from Real Application Clusters Databases 509Using Enterprise Manager to Delete Database Instances from Existing Nodes 509Using DBCA in Interactive Mode to Delete Database Instances from Existing Nodes 509Using DBCA in Silent Mode to Delete Instance from Existing Nodes 510Step 2: Deleting Nodes from Real Application Clusters Databases 510ASM Instance Clean-Up Procedures for Node Deletion 511Adding and Deleting Nodes and Instances on Windows-BasedSystems 512Cloning Oracle Clusterware and RAC Software in Grid Environments 513Quick-Start Node and Database Instance Addition and Deletion Procedures 513Adding an Oracle Clusterware Home to a New Node 513Adding an Oracle Home with RAC to a New Node 513Deleting an Oracle Home with RAC from an Existing Node 513Deleting an Oracle Clusterware Home from an Existing Node 513Detailed Node and Database Instance Addition and Deletion Procedures 513Overview of Node Addition Procedures 513Page 27 Oracle DBA Code ExamplesStep 1: Connecting New Nodes to the Cluster 513Making Physical Connections 513Installing the Operating System 513Verifying the Installation with the Cluster Verification Utility 513Checking the Installation 513Step 2: Extending Oracle Software to New Nodes at the Oracle Clusterware 513Step 3: Preparing Storage on New Nodes 513Raw Device Storage Preparation for New Nodes 513Step 4: Adding Nodes at the Oracle RAC Database Layer 513Step 5: Adding Database Instances to New Nodes 513Using Enterprise Manager to Add Database Instances to New Nodes 513Using DBCA in Interactive Mode to Add Database Instances to New Nodes 513Using DBCA in Silent Mode to Add Database Instances to New Nodes 513Connecting to iSQL*Plus after Adding a Node 513Adding Nodes that Already Have Clusterware and Oracle Software to a Cluster 514Overview of Node Deletion Procedures 514Step 1: Deleting Instances from Real Application Clusters Databases 514Using Enterprise Manager to Delete Database Instances from Existing Nodes 514Using DBCA in Interactive Mode to Delete Database Instances from Existing Nodes 514Using DBCA in Silent Mode to Delete Instance from Existing Nodes 514Step 2: Deleting Nodes from Real Application Clusters Databases 514Step 3: ASM Instance Clean-Up Procedures for Node Deletion 514Monitoring Performance 515RAC Common Tuning Tips 515Instance Recovery and RAC 515Global Cache Wait Events 515Monitoring Performance in Enterprise Manager 516Using the Cluster Database Performance Page 516Using the Cluster Database Instance Performance Page 516Using the Cluster Performance Page 517Using the Cluster Interconnects Page 517Making Applications Highly Available Using Oracle Clusterware 518Making an Application Highly Available Examples 518Page 28 Oracle DBA Code ExamplesExample1: Making an Application Highly Available 518Example2: Making an Application Highly Available 520Managing Automatic Oracle Clusterware Resource Operations for Action Scripts 522Displaying Clusterware Application and Application Resource Status Information 522Unregistering Applications and Application Resources 523RAC Troubleshooting 524Diagnosing the Oracle Clusterware High Availability Components 524Debugging Recommnedation 524Clusterware Log Files and the Unified Log Directory Structure 524Dynamic Debugging 524Component Level Debugging 524Oracle Clusterware Shutdown and Startup 525Enabling and Disabling Oracle Clusterware Daemons 525Diagnostics Collection Script 525The Oracle Clusterware Alerts 525Resource Debugging 525Checking the Health of the Clusterware 525Troubleshooting the Oracle Cluster Registry 525Troubleshooting Hostname Changes and CSS 525Enabling Additional Tracing for Real Application Clusters High Availability 526Diagnosing Oracle Real Application Clusters Components 526Where to Find Files for Analyzing Errors 526Using Instance-Specific Alert Files in Real Application Clusters 526Enabling Tracing for Java-Based Tools and Utilities in Real Application Clusters 526Resolving Pending Shutdown Issues 526Using the Cluster Verification Utility 526Cluster Verify Locations 526Cluster Verify Stages 526Cluster Verify Components 527CVU Component Verification Examples 527Understanding CVU Commands, Help, Output, and Nodelist Shortcuts 528Performing Various CVU Tests 528Known Issues for the Cluster Verification Utility 528Installing Oracle 11g R2 RAC One Node on Linux 5 530Page 29 Oracle DBA Code ExamplesInstallation Environment 530Required Software 530Used Hardware 530Installation Plan 5311. Preinstallation tasks 5322. Oracle Grid Infrastructure installation 5393. Oracle Grid Infrastructure Patching 5424. Checking Oracle Grid Infrastructure Status 5425. Oracle Database 11g R2 Software Installation 5426. Oracle Database 11g R2 Software Patching 5437. Install EM Agent in cluster nodes (if required) 5448. ASM Diskgroups Creation 5449. RAC Database Creation 54510. Initialize the Database to RAC One Node (11.2.0.1 Only) 54711. Postinstallation tasks 54812. General Useful Postinstallation Tasks in Linux 548Instance Relocation using Omotion (11.2.0.1) 549Instance Relocation using Omotion (11.2.0.2) 550Oracle Warehouse Builder (OWB) 552Oracle Warehouse Builder Architecture 552Starting and Stoping the Service 552Configuring the Repository and Workspaces 552Steps of Using Warehouse Builder 553Mapping Operators 554Using SQL*Plus 557Using SQL*Plus Command-Line Options 557Starting SQL*Plus Session 557Controlling User Privileges in SQL*Plus 557Page 30 Oracle DBA Code ExamplesSetting the SQL*Plus Environment with the SET Command 558Setting SQL*Plus Preferances 558Logging SQL*Plus Errors 559Key SQL*Plus "Working" Commands 559Creating Command Files in SQL*Plus 559Copying Tables with the COPY Command 559Creating Web Pages Using SQL*Plus 560Using SQL to Generate SQL 560Enabling AUTOTRACE for a User 560Using rlwrap Utility with SQL*Plus in Unix-Based Systems 561Escaping Special Characters 561Using SQL*Loader Utility 563Invoking SQL*Loader 563Using SQL*Loader Control File 563Loading Excel File into a Table using SQL*Loader 569Loading Large Fields into a Table 569Using Direct Load Options 570Data Pump Export and Import 571Data Pump Components 571Data Pump Export Interfaces 571Export Modes Parameters 571Required Rrivileges 571Invoking Export Data Pump Examples 571Export Filtering Parameters 572Export Remapping Parameters 572Sampling Export Data 573Export Encryption Parameters 573Export Estimating Parameters 573Export Network Link Parameter 573Export PARALELL Parameter 574Page 31 Oracle DBA Code ExamplesImport Modes Parameters 574File- and Directory-Related Parameters 574Using TABLE EXISTS ACTION Parameter 574Import Filtering Parameters 574Import Remapping Parameters 574Ignoring Nondeferred Constraints 575Import Network Link Parameter 575Import Flashback Parameters 575Import PARALELL Parameter 576Monitoring a Data Pump Jobs 576LogMiner 577Types of Supplemental Logging 577Levels of Supplemental Logging 577Disabling Database-Level Supplemental Logging 577LogMiner Dictionary Options 578Redo Log File Options 578OPTIONS possible values in DBMS LOGMNR.START LOGMNR: 578Obtaining LogMiner Operational Information 578Examples of Using LogMiner 579Without Sepecifying the Redo Files 579Wit Sepecifying the Redo Files 580Managing Oracle Database Control 583Configuring and Using the Database Control 583Implementing EM Database Control Auto Startup 583Dropping and Recreating the Management Repository 584Installing Oracle 10g R5 (10.2) Enterprise Manager Grid Control forLinux x86 585Installation Environment 585Page 32 Oracle DBA Code ExamplesRequired Software 585Used Hardware 585Installation Steps 585Remote Diagnostic Agent (RDA) 593Using Remote Diagnostic Agent (RDA) 593Connect Oracle to SQL Server 594Configuring and Using the Database Control 594PL/SQL Basics 597PL/SQL Data Types 597Controlling Compile-Time Displayed Warnings 598Catching Returned Errors 599Hiding Code 599Controlling Program Flow 599Using Cursors 602Using Records 606Using Table Functions 608Using Collections 610Using VARRAYS 610Using Nested Tables 613Using Associative Arrays 614Using Collection API 616Handling Errors 621Predefined Exceptions 621Using User-Defined Exceptions 621Using RAISE APPLICATION ERROR 622Page 33 Oracle DBA Code ExamplesAutonomous Transactions 624Some Stored Subprobrams Concepts 625Serially Reusable Packages 625Stored Subprograms and Roles 626Invokerâs vs. Definerâs Rights 626Pinning an Programunit in the Shared Pool 627Using Triggers 628Restrictions on Triggers 628Using DML Triggers 628Using Instead-of Triggers 629Using System Triggers 630Handling Mutating Tables in Triggers 634Dropping and Disabling Triggers 635Using Dynamic SQL 636Working with Native Dynamic SQL 636Using DBMS SQL 639Calling Java from PL/SQL 648Configuring Oracle Database to Use External Routines 649Using Large Objects (LOBs) 652Creating LOB 652Using SQL with Internal LOBs 652Using LOBs in PL/SQL 653Performance Considerations 662Using Returning Clause 662Using CONTEXT Index 663Migrating from LONGs to LOBs 664PL/SQL Performance Tuning Tips 665Use PL/SQL Profiler 665Page 34 Oracle DBA Code ExamplesUse BULK COLLECT Clause 666Set PLSQL OPTIMIZE LEVEL and Subprogram Inlining 670Using Bind variables in Dynamic SQL 671Use NOCOPY Keyword 672Use Associative arrays 672Use Server Result Cache 672PL/SQL Miscellaneous Topics 673Accessing V$ Views from PL/SQL 673Program Units and Scripts Used in the Document 675Return Parameter Value for Normal User 675Applying Random Load on Database Sample 1 676Setup 676Using the Load Generator Scripts 682Applying Random Load on Database Sample 2 683Setup 683Using the Load Generator Scripts 691SQL Usage Samples 693Merge Command 693Multitable Inserts 693Parallel Insert 694Deleting Duplicate Rows in a Table 694Adding a Primary Key to a Table and then Filling it 694More DBA Scripts 696Compare Table Sizes in Two Databases 696Using Linux for Oracle 697Verifying the Kernel 697Checking for a Tainted Kernel 697Page 35 Oracle DBA Code ExamplesSupported Hardware 697Using Oracle Relink Utility 697Certified and Supported File Systems 698Enterprise Linux Runlevels 698Using /etc/oratab File and dbstart Utility 698Automating Jobs 699Using cron 699Using anacron 700Using at command 700Using batch command 701Task Scheduler 701Configuring Linux Memory for Oracle 701Using Linux Performance Monitoring Tools 701About Linux Tools 702Using Linux Tools 702Checking Some General Guideline on Truning Oracle in Linux 703Troubleshooting Oracle Database in Linux 704Using OS Watcher (OSW) 704Using OS Watcher Graphs(OSWg) 706Using the On-Board Monitor (LTOM) 706