The Web Academy

Home | Contact Us | Site Map |

Course Schedule
Course	:	Oracle 9i Database Administrator Fundamentals Course I
Timing	:	TBA
Total Hours	:	64 hours
Start Date	:	Every Fortnight
Tutors Profile	:	8 years Experienced Tutor
Remote Access	:	Yes

Oracle 9i Database Administrator Fundamentals Course I :

Course Description :
This course will build up the key fundamental skills essential to be an Oracle DBA. Delegate will design and create a server using the Optimal Flexible Architecture (OFA), configure logical and physical structures, set up database and user security, add and administer users, monitor and tune main server areas. Delegates will generate a toolkit of administration scripts for database management and tuning by the end. The course will also deal with issues for DBAs having to support Oracle8i and Oracle9i databases.

Eligibility :
Delegates should have prior knowledge of Oracle course or have at least six months working in an Oracle technical environment. An understanding of relational database concepts, SQL and PL/SQL programming skills are required. A good understanding of Oracle schema is recommended.

Duration :
5 weekends or 8 days

Topics :

Understanding the Oracle9i Server Architecture Responsibilities of a DBA Changing DBA Environments What is an Oracle Server? Oracle Versioning Server Architectural Overview Operating System Environment Important Subdirectories in ORACLE_HOME Oracle Product Subdirectories Oracle Instance Components Database, Control, and Redo Log Files Logical Storage Structures Oracle Block Sizes Blocks, Extents, and Segments Different Types of Segments Tablespaces Logical Structural Design System Change Numbers Different Versions of Oracle Oracle8.0 - The Object-Relational Release Oracle Ceilings Additional Database Limits Oracle8i - The Internet Release Oracle9i Release 1- The eBusiness Database Oracle9i Release 2- The Object-Relational OLAP Database Oracle Products and Architectures DBA Management Pack Understanding your Oracle Server Layout	Oracle Memory Structures and Processes Oracle Instance Review Database Buffer Cache The LRU and Dirty Lists Server Processes and the Buffer Cache DBWR and the Buffer Cache MTTR and Checkpoint intervals Checkpoints V$INSTANCE_RECOVERY View MTTR Advisor Minimal I/O Recovery Defining Multiple DB Cache Buffer Pools Defining the Database Buffer Pools (Oracle8i style) Database Buffer Pool Parameters (Oracle8i style) The Buffer Pool Parameter Defining Storage for Buffer Pools Calculating the Hit Ratio for Buffer Pools and Buffer Pool Sizes Latch Contention Caching Tables Calculating Cache Usage of Schema Objects Redo Log Buffer Cache Shared and Large Pool Area Java Pool Size The Oracle JVM Impacting Server Process Memory Background and Required Background Processes Additional Background Processes Listing Processes

Server Processes and Executing SQL Server Processes Program and User Global Areas Dedicated and Shared Server Architecture Deprecated and Shared Server Parameters The Parse Phase Execution Plan Phase Execution and Fetch Phase Processing DML Statements Rule-based and Cost-based Optimizers Tuning Parallel Query Parallel DML	Using the Data Dictionary What is the Oracle Data Dictionary? Base Tables, Static Views, and Dynamic Views in the Data Dictionary ALL_% Static Views DBA_% Static Views USER_% Static Views USER_% Static Views V$ Dynamic Views GV$ Dynamic Views GV$ Dynamic Views Database Data Dictionary Views

Starting and Shutting Down an Oracle Server Database Utilities The Server Manager Tool (Obsolete in Oracle9i) SQL*Plus Features and Commands Additional Commands The Parameter File Oracle Universal Installer The Database Configuration Assistant Setting up a Parameter File Important Parameters for Database Creation Starting an Oracle Server The STARTUP command Startup and Shutdown Privileges The SHUTDOWN Command ALTER DATABASE Command Alert and Trace Files Working with Data Dictionary Views Dynamic versus Static Parameters	Dynamic Memory Management Setting up SQL Work Areas Defining Automatic PGA Memory Management SQL Work Areas versus the PGA Aggregate Target Setting the PGA Target Memory Size Displaying Workarea Information Using a PGA Workarea Dynamic SGA Granules Changing the Database Buffer Cache V$BUFFER_POOL Changing the Shared Pool Size Database Buffer Cache Parameters Multiple Database Buffer Caches Deprecated and Advisory Parameters V$DB_CACHE_ADVICE

Managing Tablespaces and Data Files Review Logical and Physical Structure Relationships The SYSTEM Tablespace CREATE TABLESPACE Locally-Managed Tablespaces Temporary Tablespaces Temporary Segments Database Default Temporary Tablespace Creating a Default Temporary Tablespace Read-only Tablespaces Viewing Active Transactions Coalescing Dictionary Managed Tablespaces Using the ALTER TABLESPACE command Taking Tablespaces ONLINE and OFFLINE Renaming a Data File Adding More Space to a Database Autoextending a Data File DROP TABLESPACE Command Dictionary-Managed Tablespaces Dictionary versus Locally Managed Tablespaces Converting Dictionary and Local Tablespaces Migrating the Tablespace Management Type Data Dictionary Views	Managing Tables Creating Tables Create the SALE_TAB Table ROWIDs Creating Segments in Tablespaces Column Datatypes Global Temporary Tables Clustered Tables Database Block Block Utilization Parameters Row Migration and Chaining Storage Clause Options and Parameters Managing Extent Growth Moving, Renaming, and Redefining Tables Removing Data From Tables Modifying Column Definitions Defining Columns to be Unused Dropping Columns Segment High Water Mark and Segment Header Calculate the HWM of a Segment Online Table Redefinitions DBMS_REDEFINITION Package Validate Structure Online The TRUNCATE Command Data Dictionary Views for Tables

Server Parameter File Definition of the Server Parameter File (SPFILE) Operation of the SPFILE Creating an SPFILE Changing Parameters in the SPFILE Backing up the SPFILE Gathering Information About Parameters SPFILE FAQs	Managing Rollback Segments Managing Before-Image Data Rollback Segments Private versus Public Rollback Segments Second Rollback Segment in SYSTEM Sizing Rollback Segments The OPTIMAL Parameter CREATE ROLLBACK SEGMENT Command ALTER/DROP ROLLBACK SEGMENT Commands Using the SET TRANSACTION Command Data Dictionary Views for Rollback Segments

Tablespace Management Automatic Undo Management Creating an Undo Tablespace Automatic Mode for Managing Undo Space Automatic Undo Initialization Parameters The UNDO_RETENTION Parameter Defining an Undo Tablespace When Creating a Database Determining the Size of the Undo Tablespace Data File Dropping an Undo Tablespace Switching Undo Tablespaces Data Dictionary Views for Undo Information Undo versus Rollback Segments Multiple Block Size Support Setting up Multiple Block Sizes	Segment Management High Throughput Systems Automatic Segment-Space Management Space Management Creating Objects in Automatic Segment-Space Managed Tablespaces Space Deallocation With Automatic Segment-Space Management The DBMS_SPACE Package The SPACE_USAGE Parameters Space Deallocation Statements Viewing Space Usage Information

Managing Indexes Index Types B-tree Indexes Index Attributes Maintaining Indexes Composite Indexes Skip Scans Reverse Key Indexes Bitmap Indexes Cluster (BTree) Indexes Index Organized Tables Function-Based Indexes Domain Indexes Index Maintenance Analyzing, Rebuilding, and Coalescing the Index Online Index Rebuilds Index Organized Table High Availability Features Bitmap Join Indexes Indexes on Primary Keys Export and Import of Indexes	Managing Constraints Data Integrity Inline and Out-of-Line Constraints Constraint Names Primary and Foreign Key Constraints Unique, Not Null, and Check Constraints Viewing Constraint Information Enabling and Disabling Constraints Creating and Setting Deferred Constraints Setting the RELY Flag Explicit Index Definition USING INDEX Syntax Dropping or Disabling Constraints and Indexes Reducing Locking on Foreign Key Tables Caching Primary Key Values

Administration of Control Files Control File Contents Working with Control Files Multiplexing Control Files The CREATE CONTROLFILE Command Log Sequence Numbers Reviewing Checkpoints Instance Recovery Managing Checkpointing Multiple DBWR IO Processes DB_BLOCK_CHECKING DB_BLOCK_CHECKSUM FAST_START_PARALLEL_ROLLBACK	Administration of Redo Log Files Review of Redo Log Files Writing to Redo Log Files Adding and Clearing Online Redo Log Files Dropping and Renaming Online Redo Log Files Archivelog Mode Setting up Archiving Mandatory or Optional Destinations Managing Archive Destinations Dynamic Archive Parameters ARCHIVE LOG LIST Command Data Dictionary Views for Redo Log Files

Oracle Managed Files Why Use Oracle Managed Files? The Mechanism of OMF OMF Data Files and Naming Conventions O9iR2 OMF Naming Conventions DB_CREATE_FILE_DEST DB_CREATE_ONLINE_LOG_DEST_n Using OMF in Database Creation OMF and Online Redo Log Files OMF, Data Files, and Tablespaces Renaming OMF Data Files OMF and Control Files	Managing Users and Profiles Creating Users Assigning Quotas to Users ALTER USER Command Protecting the System Tablespace Creating Profiles Database Resource Limits Security Guidelines

Additional Security Features Enhanced Security Password Management Features Password Resource Limits Password Complexity Setting up Password Management Defining a Profile with Password Limits Defining a Profile for a User Data Encryption Encryption: Random Key Generation	Security Administration System Privileges Roles Viewing System Privileges Privileges and the User Group PUBLIC Revoking System Privileges and Roles Object Privileges Database Authentication Setting up a Role Parent versus Child Roles Working with Default Roles Secure Application Role Enabling and Disabling Roles Guidelines and Standards for Roles

Creating an Oracle Instance and Database Preparation, Methods, and Steps for Creating a Database Requirements for a Minimal Database Setup for UNIX and NT Removing Oracle Servers and Software Things to Consider When Creating a Database Optimal Flexible Architecture Follow OFA Guidelines OFA Directory Layouts File Naming Conventions The CREATE DATABASE Command Creating a Oracle9i Database Script What is Created? Preinstalled Users and Roles After Database Creation Managing PL/SQL Packages Troubleshooting Database Creation	Oracle Utilities and Managing Data Oracle Utilities The Export Utility Export Modes and Parameters Import Utility and Parameters Rebuilding a Database Logical Backups Using Multiple Export Dump Files Using a Query Filter on Export Exporting Outlines Transportable Tablespaces Self Containing Tablespaces New 9i Features in Oracle Utilities Exporting and Importing Statistics New Parameters for Export and Import Tablespace Level Exports Direct-Load Inserts SQL*Loader Conventional versus Direct Path Loads The Control File DBVERIFY Loadjava and Dropjava