OCP Instructors Guide for Oracle DBA Certification: A Study Guide to Advanced Oracle Certified Professional Database Administration Techniques
An instructor’s guide for teaching Oracle DBA certification candidates, this book is highly technical and written for Oracle Certified Professional (OCP) DBA instructors who want to ensure that their students successfully pass their Oracle OCP certification exam. Covering those OCP topics that students find the most challenging, instructors will find targeted Oracle OCP testing content, test taking tips, and actual OCP test content. Other test topics covered include database architectures,...
Search in google:
An instructor’s guide for teaching Oracle DBA certification candidates, this book is highly technical and written for Oracle Certified Professional (OCP) DBA instructors who want to ensure that their students successfully pass their Oracle OCP certification exam. Covering those OCP topics that students find the most challenging, instructors will find targeted Oracle OCP testing content, test taking tips, and actual OCP test content. Other test topics covered include database architectures, design review meetings, software installation, database objects, backup and recovery, and tuning and performance.
Using the Online Code Depot1Conventions Used in this Book2Acknowledgements4Foreword5Chapter 1Getting Started7Why Become an Oracle Database Administrator?7Oracle Database Administration Responsibilities9Oracle Classroom Education15Oracle9i Curriculum Changes16Oracle9i Oracle Certifications18Oracle Certified Database Associate (OCA)18Oracle Certified Database Professional (OCP)20Oracle Certified Master Database Administrator (OCM)21Oracle9i DBA OCP Upgrade Path21Other Recommend Classes23Preparing for the Oracle Certified Professional Exams25Taking the Certification Exams26Finding Information Quickly--The Key to Success27Reference Manuals28Oracle Internal Resources29External Resources30Book Recommendations31Conclusion33Chapter 2Oracle Database Architectures35Oracle Database Architectures35Oracle on Microsoft Windows Platforms36Oracle on LINUX Platforms38Oracle on Unix Platforms41Database Architecture Comparison Worksheet43Venturing into the Great Unknown--New Database Architectures44Conclusion45Chapter 3Review Meetings47Oracle Database Design Review Meetings47Initial Overview of Proposed System49Logical Data Model Review51Designing for Performance52Setting up a Successful Test System in Oracle53Monitoring Performance During Testing55Performance Design Reviews56Preparation for Production Turnover57Post Production Turnover58Oracle Database Design Review Meetings--Conclusion60Conclusion60Chapter 4Oracle Database Administration62Oracle Software Installation62Migrating to Newer Oracle Releases63UNIX and LINUX Semaphores and Shared Memory67Semaphores67Shared Memory70Relinking Oracle8i and Oracle9i Products71Windows Services72Oracle Instance Administration73Oracle9i Persistent Initialization Parameter Files74Remote Startup/Shutdown78Multiple Buffer Pools79Specifying Default, Keep and Recycle Bufferpools in Oracle8 and Oracle8i81Specifying Default, Keep and Recycle Bufferpools in Oracle9i82Large Pool82Java Pool83Redo log Buffer Performance84Buffer Performance is critical to Good Oracle Performance84DBWR_IO_SLAVES vs DB_WRITER_PROCESSES85Conclusion87Chapter 5Oracle Database Objects89What Database Are You Working In?89Choosing a Database Block Size91Copying Databases Between Servers93Oracle Tablespaces95Temporary Tablespaces96Create Tablespace Temporary vs. Create Temporary Tablespace97Partitioning98Oracle 8 - Range Partitioning101Index Partitioning102Equi-Partitioned Objects103Local Indexes103Global Indexes104Oracle8i--Hash and Range/Hash Composite Partitioning104Hash Partitioning104Combining Range and Hash Partitioning--Range/Hash Composite Partitioning105Oracle9i--List and Range/List Composite Partitioning105List Partitioning106Range/List Composite Partitioning106Which Partitioning Technique Do I Choose?108Oracle9i Tablespace Changes110Locally Managed System Tablespaces115Rollback Segments116When is the System Rollback Segment Used?116Rollback Segments and Transaction Processing Workloads117Batch and On-line Processing118Rollback Segments and Users--Who's Using What?119Oracle9i - Database Managed Undo Segments120Redo Logs123Checkpoint Not Complete123Resizing Redo Logs125Oracle Tables and Indexes127Space Utilization for Parallel Table Creates128Index-only Tables128Single Table Hash Clusters130Oracle9i External Tables132Alter Table Move Statement136Alter Column Rename136On-Line Table Reorganizations137Pctfree and Pctused141Clustering Data in Tables141Merging Tables to Increase Query Performance142How Many Indexes Can I Build?143Parameters that Impact Index Usage144Index-Only Access145Index Rebuilds147Index Coalesce vs Index Rebuild148Function-Based Indexes150Bitmap Indexes151Optimizer and Bitmap Indexes153Concurrency and Bitmap Indexes153When to Use Bitmap Indexes154Bitmap vs B-Tree155Indexing Hints Tips and Tricks155Index Monitoring157Conclusion159Chapter 6Oracle Backup and Recovery162It's the Little Things That Bite You162Keep Your Skills Sharp162Relax and Plan Your Attack163Don't Be Afraid to Ask Others163Instance Recovery and the Oracle Synchronization Process164Uncommitted Data on the Data Files165Committed Data Not On the Data Files166The Synchronization Process166Roll Forward Phase166Roll Backward Phase167Exports and Recovery167V$RECOVERFILE168Watch the NOLOGGING Option168Striped File Systems168Data Files and Redo Logs169Redo Log and Control File Multiplexing169OCOPY for Windows169Hot Backup Scripts for Windows169Hot Backup Scripts for UNIX171Oracle9i--Lazy Checkpointer173Recovery Manager175Recovery Catalog177Recovery Manager Backup Types177Backup Sets178Image Copies178Parallel Backup and Recovery179Multiplexed Backup Sets179Backup/Recovery Reports179Database Recovery180RMAN Examples180db_verify182Conclusion182Chapter 7Tuning and Performance185Be careful with the ANALYZE command185Finding Problem Queries186Optimizer Plan Stability188Pinning Objects in the Shared Pool189PCTFREE and PCTUSED190Caching Tables190Clustering Data in Tables191Merging Tables to Increase Query Performance191Hints192Parallel Hints193Performance Testing193Parallel Query193Tuning Oracle on Windows194Tuning Pack194Direct Load Inserts194Parallel DML Processing196Materialized Views198Database Resource Management199STATSPACK200V$ TABLES vs. Oracle BSTAT/ESTAT and STATSPACK202Segment-Level Performance Statistics Collection202Performance Tuning Intelligent Advisories204Optimizer Dynamic Sampling205Data Segment Compression207Using Explain Pan to Determine Access Paths209Explain Plan Output213SQL*PLUS AUTOTRACE214High Water Mark and Table Scans214Allocating too Much Memory to the Shared Pool and Data Buffers215Conclusion216Chapter 8Miscellaneous Oracle Information219The Foot Rule of Thumb219Monitor By Day So You Don't Get Called at Night220Monitoring Space Utilization and Performance Statistics Historically223Latches and Enqueues225Deadlock Detection226Unindexed Foreign Keys and Locking Problems227Using PUPBLD230Copying databases between servers231Oracle9i - Resumable Space Allocation232Oracle9i--Flashback Query234Full Database Exports238Large Table Imports238Compressing Export Output Files in UNIX239Terminating Oracle Processes (Threads) in Windows Systems239Truncating vs Deleting239Copy Command in SQL*PLUS240Displaying Time in Milliseconds240!, $ and Host Command Differences240Learn Command Line BEFORE Using "Sissy GUI Tools" Like Oracle Enterprise Manager241Don't Write Scripts241Don't' Write Iterative SQL Statements--Generate SQL with SQL241Input Truncated to 9 Characters242Conclusion242Chapter 9Evaluating Third-Party Products245Initial Analysis245Determine Impact to the Information Technology Support Infrastructure246Analysis Evaluation247Obtain Business Unit and IT Management Commitment248Create Evaluation Team249Locate Potential Vendors250Initial Elimination251Vendor Evaluation252Communicate Results254Conclusion255Chapter 10Ease of Administration257Good Documentation is Essential257Follow OFA Naming Conventions261Proceduralize Administrative Support for the Application Development Process262Proceduralize the Change Request Process262Create and Standardize Monitoring and Administration Scripts265Repeatable Processes266Create Service Level Agreements266DBA Report Cards and the 360-Degree Review Process267Corrective Action Reports267Conclusion268Chapter 11Oracle Database Security270Protecting Data Requires More than just Protecting the Production Database270Identifying Granted Privileges272Accounts Created During Database Creation274Wrapping PL/SQL Programs276Using OPS$ Accounts276Using Security Profiles277SYS and SYSTEM Passwords279GRANT ANY OBJECT Privilege280Administrative User Auditing281Moving the AUD$ Table282Conclusion283Chapter 12Certification Test Preparation285Introduction285Oracle Certified Associate Tests286Exam 1Z0-007--Introduction to Oracle9i SQL286Exam 1Z0-031--Oracle Database Fundamentals I288Oracle Certified Professional Tests296Exam 1Z0-032--Oracle Database Fundamentals II296Exam 1Z0-033--Oracle Database Performance Tuning303Conclusion309Book Conclusion311Index313About the Author316
