Oracle DBA Online Training Course Content

Architecture & Configuration

Overview of Oracle DBA tasks

Oracle as a flexible, complex & robust RDBMS
He evolution of hardware and the relation to Oracle
Different DBA job roles(VP of DBA, developer DBA, production DBA, database babysitter)
The changing job role of the Oracle DBA
Environment management (network, CPU, disk and RAM)
Instance management (managing SGA regions)
Oracle table and index management

Instance Architecture
Instance vs database
Components of an instance
Creating the OFA file structure (DBA, bdump, udump, pfile)

Oracle Instance Internals
  SGA vs PGA
Background processes
        Interfaces with server and disk I/O subsystem

Using SQL Plus for DBA management
        Connecting and executing SQL
        Using the 'as sysdba' syntax
Overview of SQL Plus DBA commands (startup, etc.)

Control file, UNDO and REDO management
          Explaining the use of control files
          Listing the Contents of the control File
File locations for control Files
          Obtaining Control File Information
          Listing control file contents
Displaying and Creating Undo segments
          Altering Undo Segments
Determining the Number and Size of Undo segments
Understanding flashback technology
          Troubleshooting Undo – snapshot too old
          Redo log concepts for recovery
          Online redo log (log_buffer) online redo logs and archived redo logs
         Oracle ARCH and LGWR background processes
         Redo log dictionary queries
         Redo log switch frequency and performance
        Multiplexing the Online Redo Log Files
        Archiving the Oracle Redo Logs
Recovery using the redo log files

User and privilege management
Creating New Database Users
          Using pre-spawned Oracle connections
          Auditing User activity
         Identifying System and Object Privileges
         Granting and Revoking Privileges
        Creating and Modifying Roles
Displaying user security Information from the Data Dictionary

Oracle database management

Overview of instance management
Parameter files (init.ora, listener,ora, tnsnames,ora
          Rules for sizing SGA components
        Automated Oracle memory management (AMM)

Initialization file management
         Creating the init.ora file
         Using spfile
Displaying init.ora values with v$parameter

Oracle*Net configuration
Creating the listener.ora file
Creating the tnsnames.ora file

Data buffer configuration & sizing
Inside the Oracle data buffers
Using the KEEP pool
Monitoring buffer effectiveness
Using multiple blocksizes (multiple buffer pools)

Shared pool and PGA configuration & Sizing
              Shared pool concepts and components
             Understanding the library cache
Relieving shared pool contention
Overview of PGA for sorting and hash joins

  Using sort_area_size, hash_area_size and pga_aggregate_target
             Verifying network connectivity with ping and tnsping
Testing database links

Oracle object management

Oracle tables, views and materialized views
Types of Oracle tables (regular, IOT, sorted hash clusters, nested tables)
Oracle Views
Oracle materialized views

Oracle indexes
              Types of Oracle indexes (b-tree, bitmap, bitmap join index)
              Creating B*-Tree, bitmap and function-based Indexes
             Function-based indexes
            Finding indexing opportunities
           Index maintenance

Oracle constraints
             Costs & benefits of constraints
            Types of Oracle indexes constraints (check, not null, unique, PK, FK)
            Cascading constraints

Schema, File & tablespace management
Describing the relationship between data files, tablespaces and table
Understanding Oracle segments
               Creating Tablespaces – using the autoextend option
               Changing the Size of Tablespaces – alter database data file command
              Defining a TEMP tablespace
              Changing the default storage Settings for a tablespace
             Review of the storage parameters in DBA views (ASM, ASSM, pctfree, pctused and freelists).
            Monitoring Chained rows (fetch continued rows)
Monitoring Insert and Update performance (pctused, APPEND)

  Database Maintenance
              Reason for reorgs – chained rows, imbalanced freelists
              Reorganizing Tables using Export and Import
             Using CTAS to reorganize data
             Index rebuilding
Backup & Recovery overview (hot & cold Backups, RMAN, block change tracking)

Oracle DBA Utilities
Data pump (Imp and exp utilities)
             SQL Loader
             LogMiner
             Flashback
             Introduction Data Guard
            Oracle DBA utilities – Oracle dbms packages (dbms_redefinition)
Introduction Replication (Streams, multimaster, materialized views)

Monitoring Oracle

Dictionary and v$ views
            The dba_, all_ and user_ structures
            Querying the tables, indexes, and segments views
Querying the AWR (STATSPACK) tables

Table & index monitoring
Monitoring table extents and fragmentation
              Using the dba_tables and dba_segments views
Monitoring table CBO statistics
Monitoring table extents and fragmentation
Locating chained rows
              Monitoring table & index growth
              Monitoring index usage
Monitoring index fragmentation
              Locating un-used indexes
             Identifying IOT candidates
             Reorganizing Indexes with alter index rebuild
             Dropping Indexes
Getting Index Information from the Data Dictionary

Oracle environment monitoring
             Displaying and managing Oracle sessions (v$session, v$process)
             Using AWR to monitor disk, network and CPU consumption
             Monitoring the alert log
Oracle trace/dump files

STATSPACK and AWR performance management
              Installing STATSPACK
             Running STATSPACK reports
             Interpreting a STATSPACK report
            Getting time series reports with STATSPACK
Finding performance signatures with STATSPACK

Performance Management
This section explores the methods used for performance management in Oracle and shows tips and scripts for monitoring all components of any Oracle database. You will also learn the proper action to take when any area of Oracle becomes a bottleneck.

            Bottleneck performance analysis
            Drill-down into AWR reports
            Top-5 timed events
            External Server Bottlenecks (Network, I/O, RAM, CPU)
            Network troubleshooting

  Instance Tuning
            Changing init.ora optimizer parameters (index_optimizer_cost_adj, optimizer_mode)
            Managing region parameters (shared_pool_size, db_cache_size)
            Understanding instance contention (e.g. Buffer busy waits, library cache contention)

            SQL and CBO behavior
             Introduction to cost-based optimization
            Changing the default optimizer modes
            Optimizer parameters
            Dynamic sampling
            Collecting table and index statistics (dbms_stats)
            Using column histograms and skewonly

 Tracing SQL Execution
             Using EXPLAIN PLAN
             Using “set autotrace”
             Interpreting EXPLAIN PLAN Output
            Using TKPROF / SQL*Trace

            SQL Execution Internals
            Review of Basic joining methods
            Merge join
            Hash Join
            Nested Loop join
            Advanced SQL operators
            Between operator

            SQL Tuning
            Using hints to improve SQL performance
            Using parallel query to improve performance
            SQL reusability within the library cache
            Table high-water mark
            Table striping and table partitions
            Using indexes to improve performance
            Identifying full-table scans
            Re-writing SQL queries
           Tuning sub-queries

Oracle High Availability tools
             Continuous availability and disaster recovery
             Quantifying the cost of unplanned downtime
             Oracle multi-master replication
             Introduction to Data Guard
            Introduction to Oracle Streams
            Introduction to Real Application Clusters

Backup & Recovery
                OS-level backups
                Hardware-level backup & recovery
                Block-level change tracking
                Disk mirroring
                Backup & recovery and RAID level
                Oracle-level backups (exp, expdp & RMAN)
                Hot vs. Cold backups