Oracle9i RAC: Oracle Real Application Clusters Configuration and Internals

OCP Instructors Guide for Oracle DBA
Certification......Page 3
Table of Contents......Page 7
Using the Online Code Depot......Page 16
Conventions Used in this Book......Page 17
Acknowledgements......Page 19
Foreword......Page 20
Book Conclusion......Page 303
About the Author......Page 308
Burleson Oracle Consulting......Page 311
Why Become an Oracle Database Administrator?......Page 23
Oracle Database Administration Responsibilities......Page 25
Oracle Classroom Education......Page 30
Oracle9i Curriculum Changes......Page 32
Oracle9i Oracle Certifications......Page 33
Oracle Certified Database Associate ( OCA)......Page 34
Oracle Certified Database Professional ( OCP)......Page 35
Oracle9i DBA OCP Upgrade Path......Page 36
Other Recommend Classes......Page 38
Preparing for the Oracle Certified Professional Exams......Page 39
Taking the Certification Exams......Page 40
Finding Information Quickly ¨C The Key to Success......Page 41
Reference Manuals......Page 42
Oracle Internal Resources......Page 43
External Resources......Page 44
Book Recommendations......Page 45
Conclusion......Page 47
Oracle Database Architectures......Page 49
Oracle on Microsoft Windows Platforms......Page 50
Oracle on LINUX Platforms......Page 52
Oracle on Unix Platforms......Page 54
Database Architecture Comparison Worksheet......Page 56
Conclusion......Page 58
Oracle Database Design Review Meetings......Page 60
Initial Overview of Proposed System......Page 62
Logical Data Model Review......Page 63
Designing for Performance......Page 64
Setting up a Successful Test System in Oracle......Page 66
Monitoring Performance During Testing......Page 67
Performance Design Reviews......Page 68
Preparation for Production Turnover......Page 70
Post Production Turnover......Page 71
Conclusion......Page 72
Oracle Software Installation......Page 74
Migrating to Newer Oracle Releases......Page 75
Semaphores......Page 78
Shared Memory......Page 81
Relinking Oracle8i and Oracle9i Products......Page 82
Windows Services......Page 83
Oracle Instance Administration......Page 84
Oracle9i Persistent Initialization Parameter Files......Page 85
Remote Startup/ Shutdown......Page 88
Multiple Buffer Pools......Page 90
Specifying Default, Keep and Recycle Bufferpools in Oracle8 and Oracle8i......Page 91
Large Pool......Page 92
Redo log Buffer Performance......Page 93
Buffer Performance is critical to Good Oracle Performance......Page 94
DBWR_ IO_ SLAVES vs DB_ WRITER_ PROCESSES......Page 95
Conclusion......Page 97
What Database Are You Working In?......Page 98
Choosing a Database Block Size......Page 99
Copying Databases Between Servers......Page 101
Temporary Tablespaces......Page 104
Create Tablespace Temporary vs. Create Temporary Tablespace......Page 106
Partitioning......Page 107
Oracle 8 - Range Partitioning......Page 109
Index Partitioning......Page 110
Local Indexes......Page 111
Hash Partitioning......Page 112
List Partitioning......Page 113
Range/ List Composite Partitioning......Page 114
Which Partitioning Technique Do I Choose?......Page 115
Oracle9i Tablespace Changes......Page 117
Locally Managed System Tablespaces......Page 122
Rollback Segments......Page 123
Rollback Segments and Transaction Processing Workloads......Page 124
Batch and On- line Processing......Page 125
Rollback Segments and Users – Who’s Using What?......Page 126
Oracle9i - Database Managed Undo Segments......Page 127
Redo Logs......Page 129
Checkpoint Not Complete......Page 130
Resizing Redo Logs......Page 132
Index- only Tables......Page 134
Single Table Hash Clusters......Page 136
Oracle9i External Tables......Page 138
ALTER TABLE MOVE Statement......Page 141
On- Line Table Reorganizations......Page 142
Merging Tables to Increase Query Performance......Page 146
How Many Indexes Can I Build?......Page 147
Parameters that Impact Index Usage......Page 149
Index- Only Access......Page 150
Index Coalesce vs Index Rebuild......Page 152
Function- Based Indexes......Page 155
Bitmap Indexes......Page 156
Concurrency and Bitmap Indexes......Page 157
When to Use Bitmap Indexes......Page 158
Bitmap vs B- Tree......Page 159
Indexing Hints Tips and Tricks......Page 160
Index Monitoring......Page 161
Conclusion......Page 163
Keep Your Skills Sharp......Page 165
Don¡¯t Be Afraid to Ask Others......Page 166
Instance Recovery and the Oracle Synchronization Process......Page 167
Uncommitted Data on the Data Files......Page 168
Roll Forward Phase......Page 169
V$ RECOVERFILE......Page 170
Data Files and Redo Logs......Page 171
Hot Backup Scripts for Windows......Page 172
Hot Backup Scripts for UNIX......Page 173
Oracle9i ¨C Lazy Checkpointer......Page 176
Recovery Manager......Page 178
Recovery Catalog......Page 179
Backup Sets......Page 180
Multiplexed Backup Sets......Page 181
RMAN Examples......Page 182
Conclusion......Page 184
Be careful with the ANALYZE command......Page 187
Finding Problem Queries......Page 188
Optimizer Plan Stability......Page 189
Pinning Objects in the Shared Pool......Page 191
Clustering Data in Tables......Page 192
Hints......Page 193
Performance Testing......Page 194
Tuning Pack......Page 195
Direct Load Inserts......Page 196
Parallel DML Processing......Page 197
Materialized Views......Page 199
Database Resource Management......Page 200
STATSPACK......Page 201
Segment- Level Performance Statistics Collection......Page 203
Performance Tuning Intelligent Advisories......Page 205
Optimizer Dynamic Sampling......Page 206
Data Segment Compression......Page 208
Using Explain Pan to Determine Access Paths......Page 209
Explain Plan Output......Page 212
SQL* PLUS AUTOTRACE......Page 213
High Water Mark and Table Scans......Page 214
Allocating too Much Memory to the Shared Pool and Data Buffers......Page 215
Conclusion......Page 216
The Foot Rule of Thumb......Page 218
Monitor By Day So You Don¡¯t Get Called at Night......Page 219
Monitoring Space Utilization and Performance Statistics Historically......Page 221
Latches and Enqueues......Page 223
Deadlock Detection......Page 224
Unindexed Foreign Keys and Locking Problems......Page 225
Using PUPBLD......Page 228
Oracle9i - Resumable Space Allocation......Page 229
Oracle9i ¨C Flashback Query......Page 231
Full Database Exports......Page 235
Terminating Oracle Processes ( Threads) in Windows Systems......Page 236
Displaying Time in Milliseconds......Page 237
Don’t’ Write Iterative SQL Statements – Generate SQL with
SQL......Page 238
Conclusion......Page 239
Initial Analysis......Page 242
Determine Impact to the Information Technology Support Infrastructure......Page 243
Analysis Evaluation......Page 244
Obtain Business Unit and IT Management Commitment......Page 245
Create Evaluation Team......Page 246
Initial Elimination......Page 247
Vendor Evaluation......Page 249
Communicate Results......Page 250
Conclusion......Page 251
Good Documentation is Essential......Page 253
Follow OFA Naming Conventions......Page 257
Proceduralize the Change Request Process......Page 258
Repeatable Processes......Page 261
Process......Page 262
Conclusion......Page 263
Protecting Data Requires More than just Protecting the Production Database......Page 265
Identifying Granted Privileges......Page 266
Accounts Created During Database Creation......Page 268
Wrapping PL/ SQL Programs......Page 270
Using OPS$ Accounts......Page 271
Using Security Profiles......Page 272
SYS and SYSTEM Passwords......Page 273
GRANT ANY OBJECT Privilege......Page 274
Administrative User Auditing......Page 275
Moving the AUD$ Table......Page 276
Conclusion......Page 277
Introduction......Page 279
Exam 1Z0- 007 ¨C Introduction to Oracle9i SQL......Page 280
Exam 1Z0- 031 ¨C Oracle Database Fundamentals I......Page 282
Exam 1Z0- 032 ¨C Oracle Database Fundamentals II......Page 289
Exam 1Z0- 033 ¨C Oracle Database Performance Tuning......Page 297
Conclusion......Page 302
Index......Page 305