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