Oracle DBA (Database Administrator) Course Layout

Overview of Oracle DBA tasks

  • Oracle as a flexible, complex & robust RDBMS
  • The 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

  • The three security methods (VPD, Grant security/role-based security, grant execute)
  • 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

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

Troubleshooting network connectivity

  • Verifying network connectivity with ping and tnsping
  • Testing database links

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 datafile 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
  • DataGuard
  • Oracle DBA utilities – Oracle dbms packages (dbms_redefinition)
  • Replication (Streams, multimaster, materialized views)

Dictionary and 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

Workload & trend monitoring

  • Oracle automated workload tools
  • Using v$bh to monitor buffer activity
  • Using v$sql and v$sql_plan

Instance monitoring

  • Monitoring with the AWR and STATSPACK
  • Creating a time-series performance report
  • Using www.statspackanalyzer.com
  • Scripts for AWR and STATSPACK
  • Plotting performance data (Ion, Excel)
  • Finding performance trends and signatures

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

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
  • DataGuard
  • Oracle Streams
  • 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 (expdp & RMAN)
  • Hot vs. Cold backups