Oracle Database 11g: New Features for DBAs and Developers

Oracle Database 11g: New Features for DBAs and Developers......Page 1
Contents at a Glance......Page 6
Contents......Page 8
About the Authors......Page 20
Acknowledgments......Page 22
Enhanced Database Automation......Page 24
Data Guard Enhancements......Page 25
General Database Management......Page 26
Application Improvements......Page 27
High-Availability Enhancements......Page 28
Advanced Compression......Page 29
Installing, Upgrading, and Managing Change......Page 30
Choosing the Datafile and Flash Recovery Area Locations......Page 31
Changes in the Install Options......Page 32
Components......Page 33
Deprecation of the connect Role......Page 34
Installing Oracle Database 11......Page 35
Memory-Related Parameters......Page 37
PL/SQL Native Compilation Parameter......Page 38
New Result Cache–Related Parameters......Page 39
The db_ultra_safe Parameter......Page 40
Optimizer-Related Parameters......Page 41
DBCA Enhancements......Page 42
New Oracle Background Processes......Page 45
Upgrading to Oracle Database 11......Page 46
Upgrade Path to Oracle 11......Page 47
A Manual Upgrade Example......Page 48
Upgrading with the DBUA......Page 53
Downgrading After an Upgrade to 11......Page 54
Oracle Clusterware Upgrade......Page 55
Execute the Pre-update Script......Page 56
Install and Upgrade Using runInstaller......Page 57
Upgrade Remaining Nodes of the RAC Environment......Page 63
Database Replay......Page 64
Capturing the Workload......Page 68
Performing the System Change......Page 70
Replaying the Workload......Page 71
Preparing the Workload Replay......Page 72
Analyzing Workload Capture and Replay......Page 73
The SQL Performance Analyzer......Page 76
Capturing the Production System SQL Workload......Page 78
Creating a SQL Performance Analyzer Task......Page 79
Comparing the SQL Performance......Page 80
New Features in Database Control for Patching......Page 82
Emergency Hot Patching (Online Database Patching)......Page 84
Software and Database Cloning......Page 85
Database Diagnosability and Failure Repair......Page 86
The Fault Diagnosability Infrastructure......Page 87
Problems and Incidents......Page 89
Structure and Location of the ADR......Page 90
Using adrci in the Command-Line Mode......Page 92
Setting the Homepath......Page 93
Viewing the Alert Log......Page 95
Viewing Incidents......Page 97
Packaging Incidents......Page 99
Using the Support Workbench Home Page......Page 102
Using the Support Workbench to Resolve Problems......Page 103
Repairing SQL Failures with the SQL Repair Advisor......Page 107
Database Health Checks......Page 109
Types of Health Monitor Checks......Page 110
Manual Checking Using the DBMS_HM Package......Page 111
Manual Checks Using Database Control......Page 114
Manual Checks with the RMAN Validate Command......Page 115
Data Recovery Advisor......Page 117
Dealing with Failures......Page 118
Managing the DRA with Database Control......Page 124
Improvements in Handling Data Corruption......Page 125
Unknown......Page 0
Lost-Write Detection Using a Physical Standby Database......Page 126
Database Automation......Page 128
Automatic Memory Management......Page 129
Adopting Automatic Memory Management......Page 130
Monitoring Memory Allocation......Page 132
Automated Maintenance Tasks......Page 134
New Maintenance Windows......Page 135
Default Resource Manager Plan......Page 136
Modifying the Automatic Tasks......Page 137
Tracking Job Runs......Page 138
Flashback-Related New Features......Page 139
Total Recall and the Flashback Data Archive......Page 140
What Is the Flashback Data Archive?......Page 141
Benefits of Using a Flashback Data Archive......Page 143
Creating a Flashback Data Archive......Page 144
Altering a Flashback Data Archive......Page 146
Enabling and Disabling Flashback Data Archiving......Page 147
Flashback Data Archive Limitations......Page 148
Using Flashback Data Archives: Examples......Page 149
Flashback Transaction Backout......Page 151
Prerequisites for Flashback Transaction Backout......Page 152
Using the TRANSACTION_BACKOUT Procedure......Page 153
Virtual Columns......Page 154
Adding a Virtual Column to a Table......Page 155
Virtual Columns Limitations......Page 156
Explicit Locking of Tables......Page 157
Creating an Invisible Index......Page 158
Making an Existing Index Invisible......Page 159
How the Optimizer Treats the Invisible Index......Page 160
Checking the Visibility Status of an Index......Page 161
Temporary Tablespace Management......Page 162
Shrinking Temporary Tablespaces and Tempfiles......Page 163
Specifying Temporary Tablespace for Global Temporary Tables......Page 164
Creating an Initialization Parameter File from Memory......Page 165
Creating Restore Points fas off an SCN or a Timestamp......Page 166
Preserving Restore Points......Page 167
Database Resident Connection Pooling......Page 168
How DRCP Works......Page 169
Enabling and Disabling DRCP......Page 170
Configuring DRCP......Page 171
Comparing and Converging Database Objects......Page 172
Comparing Data......Page 173
Converging Data......Page 175
SQL*Plus Error Logging......Page 176
New SQL*Plus Connection Syntax......Page 178
Enhanced Online Index Creation and Rebuild......Page 179
Enhanced Default Column Value Functionality......Page 180
Minimal Invalidation of Dependent Objects......Page 181
Oracle Advanced Table Compression Option......Page 182
Implementing OLTP Table Compression......Page 183
Compressing Data Pump Export Data......Page 184
Enterprise Manager New Features......Page 185
The Server Tab......Page 186
Policy Groups Link......Page 187
LogMiner Support......Page 188
Database Diagnostic Pack and Tuning Pack......Page 189
Oracle Scheduler New Features......Page 190
Lightweight Jobs......Page 191
Installing, Registering, and Managing the Scheduler Agent......Page 192
Creating a Remote External Job......Page 193
Scheduler Support for Data Guard......Page 194
Performance Management......Page 196
Using Real Native Compilation......Page 197
Setting Up a PL/SQL Program Unit for Native Compilation......Page 198
Recompiling a Database for PL/SQL Native Compilation......Page 199
Adaptive Cursor Sharing......Page 201
How Adaptive Cursor Sharing Works......Page 202
Monitoring Adaptive Cursor Sharing......Page 203
SQL Query Result Cache......Page 204
Viewing the Result Cache with EXPLAIN PLAN......Page 208
Examples of Query Result Caching......Page 209
Managing the Query Result Cache......Page 214
PL/SQL Function Result Cache......Page 215
An Example......Page 216
OCI Consistent Client Cache......Page 217
Automatic SQL Tuning......Page 219
Automation of SQL Tuning in Oracle Database 11......Page 220
Implementing the Recommendations......Page 221
Managing Automatic SQL Tuning......Page 222
SQL Access Advisor Enhancements......Page 223
Partitioning Recommendations......Page 224
New DBMS_ADVISOR Procedures......Page 225
Multicolumn Statistics......Page 226
Creating Multicolumn Statistics......Page 228
Gathering Statistics for Column Groups......Page 229
Expression Statistics for Functions and Expressions......Page 230
Setting Preferences......Page 231
Keeping Statistics Pending vs. Publishing Statistics Immediately......Page 232
Making Pending Statistics Available to the Optimizer......Page 233
Collecting Private Statistics......Page 234
Testing, Publishing, and Deleting Pending Statistics......Page 235
SQL Plan Management (SPM)......Page 236
Capturing SQL Plan Baselines......Page 237
SQL Plan Baseline Selection......Page 238
Evolving SQL Plan Baselines......Page 239
Displaying SQL Plan Baselines......Page 240
Managing the SQL Management Base......Page 241
Managing SQL Plan Baselines......Page 242
Running ADDM Over a Specific Time Period......Page 243
Advisor Findings......Page 244
Using the New DBMS_ADDM Package......Page 245
AWR Baselines......Page 246
Moving Window Baselines......Page 247
Generating a Template for a One-Time Baseline......Page 248
Baselines and Template Dictionary Views......Page 249
Adaptive Baseline Metric Thresholds......Page 250
Customized Performance Page......Page 251
ADDM Performance Analysis......Page 252
Enterprise Database Security Concerns......Page 254
Security Out of the Box......Page 255
Delayed Failed Logins......Page 258
Basic Compliance with Industry Security Recommendations......Page 259
Password Versions......Page 260
Change Default User Passwords......Page 261
Database Links and Case Sensitivity......Page 262
Password Management......Page 263
Create Password Verification Function......Page 264
Change Database Account Passwords......Page 266
Tablespace Encryption......Page 268
Creating the Oracle Wallet......Page 269
Wallet Requirements......Page 270
Encrypting a Tablespace......Page 271
Enterprise Manager Integration......Page 273
Tablespace Encryption with Database Console......Page 274
Policy Trend Overview......Page 276
UTL_ Package Network Access Management......Page 277
Fine-Grained Access Control for UTL_* Network Packages......Page 278
Create an Access Control List......Page 279
Add Access Control Entries......Page 280
Assign Hosts......Page 281
Maintain Access Control Lists......Page 282
Query Your Access Control List......Page 283
Order Your Access Control List......Page 285
TDE with LogMiner Support......Page 287
TDE with Hardware Security Module Integration......Page 288
Oracle Advanced Security Features......Page 289
Oracle Call Interface Enhancements......Page 290
Delay or Drop Database Connections......Page 291
Control the Display of Database Release Information......Page 292
Set Up the Banner and Auditing for Unauthorized Access......Page 293
Enhanced Block Media Recovery......Page 294
Preconditions for Using the recover … block Command......Page 295
Recovering Specific Data Blocks......Page 296
Recovering All Corrupt Data Blocks......Page 297
Scripting with RMAN Substitution Variables......Page 299
New RMAN Configuration Parameters......Page 302
Backing Up Large Files in Sections......Page 303
Performing Multisection Backups......Page 304
The backup … keep Command in Oracle Database 10......Page 305
Archival Backups......Page 306
Creating an Archival Backup......Page 307
Restoring an Archival Backup......Page 309
The New Validate Command......Page 310
Validating with the validate Command......Page 311
Speeding Up Validation with the Section Size Clause......Page 312
Validate Command Options......Page 313
Configuring an Archived Redo Log Deletion Policy......Page 314
Archived Redo Log Deletion in a Data Guard Setup......Page 315
Archived Redo Log Failover......Page 316
Active (Network-Based) Database Duplication......Page 317
Prerequisites......Page 318
Necessary Spfile Modifications......Page 319
Performing Active Duplication......Page 321
An Active Database Duplication Example......Page 322
How Network-Enabled Duplication Works......Page 326
Merging Recovery Catalogs......Page 328
Virtual Private Catalogs......Page 331
Creating a Virtual Private Catalog......Page 332
Managing Virtual Private Catalogs......Page 335
Enhanced RMAN Integration with Data Guard......Page 336
Making Configuration Changes for a Site......Page 337
Restoring a Backup Controlfile to a Standby Controlfile......Page 338
Backup File Accessibility Groups......Page 339
Block Change Tracking Support in Standby Databases......Page 340
API Enhancements......Page 342
Compression Enhancements......Page 343
Specifying Encryption......Page 348
Choosing an Encryption Mode......Page 349
Reusing a Dump File......Page 351
Remapping Data......Page 352
Remapping a Table......Page 354
Export and Import of Partitions......Page 355
External Tables Based on Data Pump......Page 358
Stored Procedure to Generate External Table Using a Data Pump......Page 360
Oracle Streams......Page 366
Restrictions on Synchronous Capture......Page 367
Configuring Synchronous Capture......Page 368
DBA Views for Synchronous Capture......Page 370
Requirements for Combined Capture and Apply......Page 371
Combined Capture and Apply Validation......Page 372
Streams Advisor Admin Package......Page 373
Streams Topology......Page 374
Streams Topology Views......Page 375
Supported Database Object Types......Page 377
Comparison......Page 378
Comparison Maintenance......Page 379
Rechecking a Prior Comparison......Page 380
Converging Shared Database Objects (Repair)......Page 381
Merging the Streams......Page 383
Message Tracking......Page 384
Automated Alerts for Clients and Thresholds......Page 385
Background Process Name Changes......Page 386
Support for Transparent Data Encryption......Page 387
Support for Oracle Scheduler......Page 388
Automated Storage Management......Page 390
RMAN and ASM......Page 391
sysasm Privilege and osasm Group......Page 392
Upgrading Manually......Page 395
Upgrading Using DBUA......Page 396
Rolling Upgrades......Page 400
A Step-by-Step Approach to Rolling Upgrades......Page 401
Preferred Read Setup......Page 403
Monitoring Preferred Reads......Page 404
ASM Restricted Mode......Page 405
ASM Diskgroup Maintenance......Page 406
Diskgroup Checks......Page 409
Diskgroup Attributes......Page 410
Variable-Size Extents......Page 414
RDBMS and ASM Compatibility......Page 415
ASM Compatibility......Page 416
Fast Mirror Resync......Page 418
Disk Repair Time......Page 419
Online and Offline Disks......Page 420
lsdg......Page 424
lsdsk......Page 425
cp......Page 427
Backup Diskgroup Using md_backup......Page 429
Restore Diskgroup Using md_restore......Page 430
Metadata Backup and Restore Demonstration......Page 431
Bad Block Recovery with ASMCMD......Page 438
Set Up Direct NFS......Page 439
Validate Direct NFS......Page 441
Direct NFS Performance......Page 442
Data Guard......Page 444
Active Physical Standby Database Duplication......Page 445
Real-Time Query Standby......Page 447
Validate the Real-Time Query Standby Database......Page 448
Snapshot Standby......Page 450
Convert to Snapshot Standby......Page 451
Convert Back to Physical Standby......Page 454
Prepare the Primary Database......Page 455
Convert to Logical: Keep Your Identity......Page 456
Block Change Tracking Support......Page 457
Compressed Redo Traffic......Page 458
Usage of Histograms for the NET_TIMEOUT Attributes......Page 459
Logical Standby: SQL Apply......Page 461
Encrypt Tablespaces......Page 462
Dynamically Set the Data Guard SQL Apply Parameters......Page 463
DBMS_SCHEDULER Support......Page 464
Logical Standby Archivelog Storage......Page 465
Data Guard Broker......Page 466
Data Guard Broker Command-Line Interface......Page 467
Customize Fast-Start Failover Events......Page 469
Initiate Fast-Start Failover from Applications......Page 470
New Data Guard Manager Properties......Page 471
Support of Heterogeneous OS Data Guard Configuration......Page 472
Data Guard Over Storage Vendor Solutions......Page 473
CONTINUE Statement......Page 474
SIMPLE_INTEGER......Page 475
Regular Expression Enhancements......Page 478
Subprogram Inlining......Page 480
pragma INLINE(identifier, mode)......Page 482
Scope and Usage......Page 483
Generalized Invocation......Page 484
Cross-Session PL/SQL Function Result Cache......Page 487
Restrictions and Limitations......Page 491
Bulk In-Bind Table of Records in FORALL......Page 492
Dynamic SQL Symmetry and New Functionality......Page 494
Compound Triggers Type......Page 498
New XMLType......Page 504
XMLIndex Components......Page 510
XMLIndex Path Table......Page 511
EM Database Console to Create XMLIndex......Page 513
Index Maintenance......Page 515
XMLIndex Secondary Indexes......Page 516
Data Dictionary Views for XMLIndex Secondary Indexes......Page 517
Asynchronous Maintenance of XMLIndexes......Page 518
XMLIndex Path Subsetting......Page 521
Native Web Services for Oracle XML DB......Page 522
Setup XML DB Servlet......Page 523
Validate the XML DB Web Service......Page 524
Validate the PL/SQL Web Service......Page 526
In-Place XML Schema Evolution......Page 528
XMLExists......Page 529
XMLCast......Page 530
XMLDiff......Page 531
XMLPatch......Page 532
APEX Configuration......Page 534
APEX Enhancements......Page 535
PHP Support Enhancements......Page 536
JAR Support......Page 537
Improvements to the dropjava Tool......Page 539
Improvements to the ojvmjava Tool......Page 540
The ojvmtc Tool......Page 541
Data Warehousing......Page 544
BasicFiles vs. SecureFiles......Page 545
LOB Performance......Page 546
Deduplication......Page 547
Encryption......Page 551
Compression......Page 552
db_securefile Initialization Parameter......Page 554
Migrate from BasicFiles to SecureFiles......Page 555
Data Warehousing New Features......Page 556
Partition Change Tracking......Page 557
Materialized View Refresh Performance Improvements......Page 558
Materialized View QUERY REWRITE Enhancements......Page 559
Pivot Operators......Page 561
Unpivot Operators......Page 566
Partitioning......Page 567
Partition Advisor......Page 568
Reference Partitions......Page 573
Interval Partitioning......Page 575
Composite List-Hash Partitioning......Page 581
Composite List-Range Partitioning......Page 582
Composite Range-Range Partitioning......Page 583
Virtual Column-Based Partitioning......Page 585
System Partitioning......Page 588
Enhanced DICOM Support......Page 590
Support for SecureFiles......Page 591
Additional Multimedia Enhancements......Page 592
Index......Page 594