ORACLE DBA  Course Contents   

 

No. Of Hours :  48 Hours.

======================================================

 

• Architecture & Configuration
• 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
• 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 vparameter
• 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 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 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  Introduction DataGuard 
• Creating and Modifying Roles
• Oracle DBA utilities â?? Oracle dbms packages (dbms_redefinition)
• Introduction Replication (Streams, multimaster, materialized views)
• Monitoring Oracle
• This section explores the methods used for monitoring all active components
• of the Oracle database.

 

• 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
• 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 (WISE, 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
• 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 DataGuard
• 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