Oracle Database 10g Insider Solutions

by Kumar, Arun; Kanagaraj, John; Stroupe, Richard

• ISBN13: 9780672327919
• ISBN10: 0672327910
• Edition: 1st
• Format: Paperback
• Copyright: 2006-01-01
• Publisher: Sams

Summary

Insider tips, tricks, and best practices for Oracle 10g - the latest version of Oracle's flagship database.

Table of Contents

Introduction	1	(1)
Who Should Read This Book	1	(1)
What You Need to Use This Book	1	(1)
How This Book Is Organized	2	(1)
About the Book's Sample Code	3	(1)
Conventions Used in This Book	3	(4)
Part I Getting Started with Oracle Database 10g
Exploring Oracle Database 10g Architecture	7	(16)
Overview of New Features	7	(7)
SYSAUX Tablespace	8	(1)
Rename Tablespace Option	8	(1)
Automatic Storage Management	9	(1)
Temporary Tablespace Group	9	(1)
BigFile Tablespace	9	(1)
Cross-Platform Transportable Tablespaces	10	(1)
Performance Management Using AWR	11	(1)
Automatic Database Diagnostic Monitor (ADDM)	12	(1)
DROP DATABASE Command	13	(1)
Data Pump Utilities	13	(1)
Processes Beneath the Hood	14	(2)
Classification of Database Processes	14	(2)
Memory Architecture	16	(2)
System Global Area (SGA)	17	(1)
Program Global Area (PGA)	18	(1)
Memory Manager (MMAN)	18	(1)
Automatic Undo Management	18	(1)
SQL Advisors	19	(1)
Oracle's Suite of Other Advisors	19	(1)
Flash Recovery Area	19	(1)
Flashback Operations	20	(1)
Flashback Database	20	(1)
Improved Oracle Enterprise Manager	20	(1)
Grid Technology Unveiled	21	(1)
Summary	21	(2)
Installing Oracle Database 10g	23	(30)
Installation Decisions	23	(1)
Prerequisites	24	(1)
Overview of Silent Installation	25	(1)
Installing 10g on UNIX/Linux	25	(7)
Installation Steps	27	(1)
Platform-Independent Database Installation Questions	27	(2)
Optimal Flexible Architecture (OFA)	29	(1)
Common Steps for Installation	30	(2)
Components to Access Other Databases and Applications	32	(1)
Oracle Database 10g DBCA Overview	32	(2)
Tracing the Database Configuration Assistant (DBCA)	33	(1)
Installing 10g on Windows	34	(1)
Companion CD Installation and Patches (All Platforms)	35	(1)
Manual Database Installation Steps	36	(1)
Installation Checklists	36	(1)
Database and Binary Cloning	36	(2)
Using Enterprise Manager for Cloning	38	(1)
Migrating a Database from 8i or 9i	39	(1)
Overview of Database Upgrade Assistant	40	(6)
Upgrade Path for Standby Environments	41	(1)
Database Upgrade Assistant in Silent Mode	42	(1)
Using DBUA with Oracle 9i Physical Standby Configuration	42	(2)
Using DBUA with Oracle 9i Logical Standby Configuration	44	(2)
Applying Patches to Database Installations	46	(1)
Uninstalling from a Failed Install	47	(5)
Step 1: Identify All Database Instances	48	(1)
Step 2: Remove the Database(s)	48	(2)
Step 3: Remove the Oracle Software	50	(2)
Summary	52	(1)
Customizing Installation Options	53	(20)
OEM and Automatic Alerts	53	(8)
OEM Preinstallation Requirements	54	(1)
OEM Installation Process	55	(3)
Control for Oracle Management Agents	58	(1)
Automatic Alerts and Management	58	(1)
Metric-Thresholds Management	59	(1)
Mobile Management Using EM2Go	60	(1)
XML DB for XML Data Processing	61	(2)
Installing XML DB	61	(2)
ConText and Other Options	63	(3)
Manual Installation of Oracle Text	64	(1)
Installation of Oracle Spatial	65	(1)
Operations with Oracle UltraSearch	65	(1)
Setting Up RAC	66	(2)
Manual Installation of RAC Instance	66	(2)
Installation of RAC Instance Using Data Guard	68	(1)
Managing Backups with RMAN	68	(4)
Important Points on Rman During Database Upgrade	70	(1)
Unregister a Target Database	71	(1)
Glimpse of Oracle Database 10g Release 2	71	(1)
Summary	72	(1)
Setting Up Automatic Storage Management	73	(20)
Automatic Storage Management (ASM)	73	(1)
Prerequisites for Using ASM	74	(2)
ASM Architecture in a Nutshell	75	(1)
Configuration Options	76	(3)
Initialization Parameters for ASM Instances	78	(1)
StartUp/Shutdown Commands on an ASM Instance	79	(1)
Performing ASM Operations	79	(6)
The Create Disk Group Command	79	(1)
Other Operations on a Disk Group	80	(5)
Avoiding ASM Pitfalls	85	(4)
Unable to Connect to ASM Instance	85	(1)
Non-ASM and ASM Database Migrations	86	(2)
Discovering ASM Instances in a RAC Environment	88	(1)
VIP Timeouts and VIP Failure on RAC	88	(1)
When and Where to Use ASM	89	(1)
ASM Improvements in Oracle Database 10g Release 2	89	(1)
Summary	90	(3)
Part II Quick Wins Using Oracle Database 10g
Using Automatic Memory Management	93	(12)
Introduction to Quick Wins	93	(1)
What Is Automatic Shared Memory Management?	94	(3)
SGA Sizing Considerations	96	(1)
Understanding Memory Advisor	97	(3)
Memory Advisors Through OEM	97	(3)
Automated Maintenance Tasks	100	(1)
Memory Management Tips	100	(3)
Converting a Manual Memory-Managed Database to ASMM	101	(1)
Tuning Sga_Target and Sga_Max_Size	102	(1)
Scaling for Database Expansion	103	(1)
ASMM Improvements in 10g Release 2	104	(1)
Summary	104	(1)
Monitoring with Automatic Statistics Collection	105	(26)
Automatic Statistics Collection Fundamentals	105	(6)
Types of Database Statistics	106	(2)
Database Metrics Revisited	108	(1)
Statistics Levels and AWR Snapshots	108	(3)
Collecting and Analyzing Statistics	111	(14)
Preventing the Time Out of Gather_Stats_Job	112	(1)
Cross-Checking Your Statistics and Ratios	112	(1)
AWR Reports Generation	113	(12)
Metrics Monitoring and Customization	125	(1)
Using Database Alerts with Metrics	126	(1)
Making the Most of Metrics and Statistics Collection	127	(1)
Statistics Collection During Database Upgrades	128	(1)
New Features in Oracle Database 10g Release 2	128	(2)
Summary	130	(1)
Managing Automatic Undo	131	(10)
Using Automatic Undo Management	131	(3)
How Undo Data Helps the Database	132	(1)
The Significance of the Undo_Retention Parameter	133	(1)
Resumable Space Allocation	133	(1)
Undo Management in Real Application Clusters	134	(1)
Redo Log File Sizings	134	(1)
Using Undo Advisor	134	(3)
Undo Management Using OEM	135	(1)
Making the Most of Undo Advisor	136	(1)
Guaranteeing Undo Availability	137	(1)
Undo Tips from the Trenches	138	(1)
Automatic Undo Management Improvements in Release 2	139	(1)
Summary	139	(2)
Leveraging Automatic Segment Management	141	(12)
Segment Management in Oracle Database 10g	141	(2)
Data, Index, and Temporary Segments	142	(1)
Making Segment Management Work for You	143	(1)
Using Segment Advisor	143	(4)
Invoking Segment Advisor from Enterprise Manager	144	(1)
Invoking Segment Advisor from PL/SQL	145	(1)
Reviewing Segment Advisor Results	146	(1)
Intelligent Segment Resource Estimation	147	(1)
Space-Management Tips and Tricks	148	(2)
Limitations for Online Segment-Shrink Operations	150	(1)
For Dictionary-Managed Tablespaces	150	(1)
ASSM Improvements in Release 2	150	(1)
Summary	151	(2)
Implementing Recovery Manager	153	(22)
Rman Basics	153	(8)
Difference Between Rman and Hot Backup	154	(1)
Advantages of Rman over Hot Backup	154	(2)
A Sample Rman Backup Session	156	(4)
Rman Documentation	160	(1)
Accessing Rman	161	(1)
New Features of Rman	161	(7)
Use of Flash Recovery Area	162	(1)
Fast Incremental Backups	163	(1)
Incrementally Updated Backups	164	(2)
Recovery Through RESETLOGS	166	(1)
Rman Tuning	167	(1)
Other Rman Improvements	167	(1)
Recovery Without Hurdles	168	(2)
Backup Strategies	168	(1)
Recovery Scenarios	169	(1)
New Features in Oracle Database 10g Release 2	170	(1)
Summary	171	(4)
Part III Tuning Oracle Database 10g
Adopting a New Approach to Tuning	175	(32)
A New Performance Philosophy	175	(5)
Performance-Management Tools	177	(1)
Statspack Shortfalls	177	(3)
New and Improved Views	180	(11)
V$Metric, V$Metric_History, V$Metricname, and V$Metricgroup	181	(1)
V$Services, V$Servicemetric, and V$Servicemetric_History	182	(1)
V$Service_Stats	182	(1)
V$Service_Event	183	(1)
V$Eventmetric	184	(1)
V$Event_Histogram	184	(2)
V$Filemetric and V$Filemetric_History	186	(1)
V$File_Histogram	186	(2)
V$Temp_Histogram	188	(1)
V$Enqueue_Statistics	188	(1)
V$Osstat	189	(1)
Changes in Existing Views	189	(1)
Changes in Other Views	190	(1)
Time and Wait Model	191	(3)
OEM and Tuning	194	(11)
Navigating OEM Database Control	195	(8)
Alerts and Metrics	203	(2)
Advisors	205	(1)
New Features in Oracle Database 10g Release 2	205	(1)
Summary	206	(1)
Effectively Using the Automatic Workload Repository	207	(32)
Introducing the AWR	207	(8)
AWR Collection	208	(1)
Comparison to STATSPACK	209	(1)
AWR Storage and Reporting	210	(3)
Accessing the AWR Snapshots	213	(2)
Customizing the AWR	215	(5)
AWR Dependencies	215	(1)
Using the OEM to Customize the AWR	216	(1)
Using the SQL Interface to AWR	217	(3)
Performing Baselining	220	(2)
Capturing Baselines	220	(1)
Dropping Baselines	221	(1)
Active Session History	222	(14)
Session-State Information	222	(1)
Using Session-State Information	223	(2)
The Connection to Active Session History	225	(4)
Size of Active Session History Buffers	229	(1)
An Example of Spot Analysis Using ASH	229	(4)
Using OEM to Obtain Active Session History	233	(1)
Dumping ASH Buffer Contents	234	(2)
The Downside of ASH	236	(1)
New Features in Oracle Database 10g Release 2	236	(1)
Summary	237	(2)
Effectively Using the Automatic Database Diagnostic Monitor	239	(24)
Introduction to ADDM	239	(5)
The Goal of ADDM	240	(1)
Problem Areas Handled by ADDM	241	(1)
Using ADDM with ASH	242	(1)
Nonproblematic Areas	242	(1)
Helpful ADDM Views	243	(1)
Interfacing to ADDM	244	(10)
Using OEM for ADDM	244	(6)
Using SQL to Get to ADDM	250	(4)
Making Sense of ADDM Data	254	(8)
Finding 1: SQL Statements Consuming Significant Time	254	(3)
Finding 2: SQL Statements Waiting for Locks	257	(1)
Finding 3: Session Management	258	(1)
Other Findings	259	(2)
Expected I/O Response Time	261	(1)
Cross-Verification from the Advisor Views	261	(1)
New Features in Oracle Database 10g Release 2	262	(1)
Summary	262	(1)
Effectively Using the SQL Advisors	263	(24)
Automating the SQL Advisors	263	(5)
The SQL Tuning Advisor	263	(4)
The SQL Access Advisor	267	(1)
Using the Advisors for Tuning	268	(16)
OEM Interface to the SQL Tuning Advisor	268	(3)
OEM Interface to the SQL Access Advisor	271	(4)
APIs to the SQL Tuning Advisor	275	(9)
Avoiding Advisor Pitfalls	284	(1)
New Features in Oracle Database 10g Release 2	285	(1)
Summary	285	(2)
Influencing the Cost Based Optimizer	287	(26)
New Optimizer Improvements	287	(17)
RBO and CBO: A Comparison	288	(1)
What the Optimizer Does	289	(2)
The Relevance of Object Statistics	291	(2)
The Relevance of System Statistics	293	(2)
The Relevance of Dictionary Statistics	295	(1)
Dynamic Sampling	295	(1)
Table and Index Monitoring	296	(1)
Parameters Influencing the Optimizer	297	(5)
Cardinality, Selectivity, and Column Usage	302	(1)
Tracing the Optimizer	303	(1)
SQL Profiles	304	(3)
Effective Use of Histograms	307	(2)
New Features in Oracle Database 10g Release 2	309	(1)
Summary	310	(3)
Part IV Scaling and Availability with Oracle Database 10g
Utilizing Oracle Database 10g Real Applications Clusters	313	(26)
Oracle 10g RAC Overview	313	(1)
Cluster Ready Services	314	(9)
Cluster Ready Services Architecture	315	(1)
Installing Cluster Ready Services	316	(6)
Troubleshooting the CRS Install	322	(1)
Service Workload Management	323	(1)
10g RAC Installation and Migration Enhancements	324	(2)
Local Versus Shared Install	324	(1)
Installing the 10g Oracle_Home	324	(1)
Adding a Node to or Removing a Node from an Existing Oracle_Home	325	(1)
Migrating from a Previous Release	326	(1)
10g RAC Manageability Enhancements	326	(5)
Oracle Cluster Registry	326	(1)
New Support for 10g Utilities	327	(3)
Managing RAC with 10g Enterprise Manager	330	(1)
Automatic Storage Management for Cluster Management	331	(2)
Managing ASM with 10g RAC	331	(1)
ASM Maximum File Size Limitation with 10g RAC	332	(1)
Third-Party Cluster Management Support	333	(1)
Limited Rolling Upgrade with OPATCH	334	(1)
10g RAC Tips for Better Use	335	(2)
Cluster Interconnect	335	(1)
SGA Components	335	(1)
Tablespaces	336	(1)
Tables and Indexes	336	(1)
Function-Dependent Routing	336	(1)
Object Partitioning	336	(1)
Running 10g RAC with 9i RAC in Same Environment	336	(1)
CRS and ASM	337	(1)
Advanced RAC Features in Oracle Database 10g Release 2	337	(1)
Summary	338	(1)
Utilizing 10g Physical and Logical Standby with Data Guard	339	(30)
Oracle 10g Data Guard Overview	339	(1)
General Improvements with Oracle 10g Data Guard	340	(6)
Real Time Apply	340	(2)
Flashback Database Support	342	(1)
Redo Transmission Authentication and Encryption	343	(1)
Role-Based Destinations	343	(2)
Improved Data Guard Configuration Management	345	(1)
Improved Data Guard Startup Command	345	(1)
Improved Data Guard Archiving	345	(1)
Logical Standby Improvements	346	(5)
Zero Outage for Instantiation	346	(2)
Support for Redo Log Files	348	(1)
The PREPARE TO SWITCHOVER Command	348	(1)
Support for SQL Apply Services	349	(1)
Rolling Database Upgrades with SQL Apply	349	(1)
Enhanced Logical Standby Commands and Views	349	(2)
Data Guard Broker Improvements	351	(10)
Broker Support for RAC Databases	351	(1)
Enhancements to the Enterprise Manager Data Guard GUI	351	(7)
New 10g Broker Commands	358	(3)
Applying Patch Sets with Data Guard in Place	361	(6)
Patching a Physical Database	361	(2)
Patching a Logical Database	363	(4)
New Features in Oracle Database 10g Release 2	367	(1)
Automatic Failover	367	(1)
Faster Redo Apply Failover	367	(1)
Flashback Database Across Switchovers	367	(1)
Asynchronous Redo Transmission	367	(1)
SQL Apply	368	(1)
Summary	368	(1)
Effectively Using Flashback Technologies	369	(44)
Brief Overview of Flashback Technology	369	(1)
Flashback Database	370	(15)
Flashback Database Architecture	371	(1)
Configuring Flashback Database	372	(1)
Managing Flashback Database	373	(2)
Flashback Database in Action	375	(5)
Using Flashback Database with Data Guard	380	(4)
Troubleshooting Flashback Database	384	(1)
Flashback Table	385	(7)
Configuring Flashback Table	385	(1)
Flashback Table in Action	386	(4)
Troubleshooting Flashback Table	390	(2)
Flashback Drop with Recycle Bin	392	(7)
Oracle Recycle Bin	392	(1)
Flashback Drop in Action	393	(5)
Troubleshooting Flashback Drop	398	(1)
Disabling Flashback Drop	399	(1)
Flashback Versions Query	399	(6)
Flashback Versions Query in Action	400	(4)
Troubleshooting Flashback Versions Query	404	(1)
Flashback Transaction Query	405	(4)
Flashback Transaction Query in Action	406	(3)
Advanced Flashback Features in Oracle Database 10g Release 2	409	(2)
Flashback Restore Points	410	(1)
Flashback Across Global Database Changes	410	(1)
Additional 10g R2 Views	411	(1)
Summary	411	(2)
Leveraging Grid Technology Features	413	(12)
What Exactly Is the Grid?	413	(5)
A Familiar Example: The Electricity Grid	414	(1)
Similarities in the Computing Grid	415	(2)
Grid Computing: A Little History	417	(1)
Oracle's Version of the Grid	418	(2)
Managing the Grid	420	(2)
Summary	422	(3)
Part V Using Oracle Database 10g Utilities and Other Advanced Features
Maximizing Data Movement with Oracle Data Pump	425	(22)
Oracle 10g Data Pump Enhancements	425	(18)
Oracle 10g Data Pump Concepts	425	(1)
Data Pump Architecture	426	(3)
Data Pump Process Flow	429	(1)
Data Pump Export and Import Utilities	430	(11)
Diagnosing Data Pump Issues with TRACE	441	(1)
Managing Data Pump Jobs	442	(1)
External Table Enhancements	443	(1)
Advanced Data Pump Features in Oracle Database 10g Release 2	444	(1)
Summary	445	(2)
Using 10g SQL *Plus and iSQL *Plus	447	(8)
10g SQL *Plus Enhancements	447	(3)
DEFINE Enhancements	447	(2)
SPOOL Enhancements	449	(1)
10g iSQL *Plus Enhancements	450	(3)
iSQL *Plus Environment	450	(1)
iSQL *Plus Workspace	450	(3)
Advanced iSQL *Plus and SQL *Plus Features in Oracle Database 10g Release 2	453	(1)
Summary	454	(1)
Making the Most of Oracle Database 10g	455	(14)
A Collection of Useful Features	455	(1)
DML/DDL Features	455	(1)
Network Features	456	(2)
New Features in Oracle Database 10g Release 2	457	(1)
Security Features	458	(1)
Access Control Features	459	(1)
Resource Manager and Scheduler	459	(2)
Job Management Using the Scheduler	460	(1)
Overview of the Resource Manager	460	(1)
New Database Initialization Parameters	461	(1)
Oracle Streams Enhancements	462	(3)
Streams Architecture	462	(1)
Streams Administrator	462	(2)
Streams Downstream Capture	464	(1)
Streams-Enhanced RAC Support	464	(1)
Other Streams Enhancements	465	(1)
MetaLink Integration with Oracle Database 10g	465	(1)
Oracle Database 10g EM Patch Cache	466	(1)
Oracle Applications 11i with Oracle Database 10g	467	(1)
Summary	467	(2)
Index	469

Excerpts

Introduction Introduction Oracle Corporation released its flagship Oracle Database 10g relational database management system in February of 2004. This new version not only arrived with a slew of features, it fundamentally changed the way certain functions and operations are done in the database. When combined with the comprehensive but voluminous documentation set, it is no wonder that first-time users and even experienced Oracle technicians stumble during implementation of this new version. They want to get the best of Oracle Database 10g, but are faced with plowing through the thousands of pages in the 150-odd manuals, searching for nuggets to implement and exploit. If this describes you, a solution to this problem is now in your hands. This book has been written by a trio of experienced Oracle professionals who have faced and overcome the very same issues you face. Having worked in the trenches with previous versions of Oracle products, and now with this new version, they have distilled their combined real-life experiences into this tome. We hope you enjoy reading this book and discovering the nuggets that the authors have laid out for you in the subsequent pages! Who Should Read This Book This book is for anyone who has to work with Oracle Database 10g. Primarily written for database administrators, it caters to database architects as well as developers who would like to know how this new version affects their enterprise and their applications. The chapters are replete with examples and code that the authors themselves have used and tested so you can apply them confidently in a real-life situation. This book, however, is not designed to act as an introductory book or even a comprehensive reference of new features in Oracle Database 10g. Only those features that are useful and have the most bang-for-the-buck in practical terms are listed and discussed. Such topics are covered in depth and, where warranted, additional sources for reading from manuals and MetaLink notes are recommended. What You Need to Use This Book This book covers Oracle Database 10g Release 1 as well as Release 2 of the product, making specific reference to the latter where necessary. Hence, you need to use a supported version of Oracle Database 10g Release 1 at the least. By default, the features and code listed would work for installations that use Oracle Database Release 1 on any operating-system platform unless specifically mentioned otherwise. All SQL code listed is operating-system neutral unless UNIX-style directories are referred to within the code. In certain cases, UNIX-based shell scripts have been used as wrappers around SQL code to demonstrate a few features in principle. These scripts can easily be adapted to Windows and should work without change if they are executed under an emulator such as CygWin or MKS Toolkit. Please note that some of the database features are available only when licensed separately. These are specifically indicated. Throughout the text, we refer to a number of manuals and MetaLink notes. Although the manuals are available both online at http://tahiti.oracle.com and with the software distribution, all readers may not have access to MetaLink. If you do not have a MetaLink account that can be used to access these notes, you can easily create one at http://metalink.oracle.com using your Customer Support Identifier (CSI). This should be available from whomever administers your Oracle Support Contract. How This Book Is Organized This book is divided into five logical parts. Chapters within these sections deal with specific topics related to that area. The parts are as follows: Part I, "Getting Started with Oracle Database 10g."This part provides an introduction to the architectur