Oracle DBA Training Course Content

Contact Details :

09868422736

dbain60days@gmail.com

http://www.dbain60days.com/

Trainer Profile

 SQL DBA working as a Senior DBA for last 6 years.

 Currently working in a Big MNC.
 Previously worked with Birlasoft and TCS.

Highlights of Training

 Real time Scenarios

 Discounts on groups
 Help in Resume building and Job portal profile creation
 100% Certification success
 Scratch to Advance level topics covered
 All topics covered one can expect up to 3 yrs experience
Architecture & Configuration

1-1: Instance Architecture

• Instance vs. database

• Components of an instance

• Creating the OFA file structure ($DBA, bdump, udump, pfile)

1-2: Oracle Instance Internals

• SGA vs. PGA

• Background processes

• Interfaces with server and disk I/O subsystem

1-3: Using SQL*Plus for DBA management

• Connecting and executing SQL

• Using the “as sysdba” syntax

• Overview of SQL*Plus DBA commands (startup, etc.)

1-4: 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

1-5: 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

Oracle database management

2-1: Overview of instance management

• Parameter files (init.ora, listener.ora, tnsnames.ora)

• Rules for sizing SGA components

• Automated Oracle memory management (AMM)

2-2: Initialization file management

• Creating the init.ora file

• Using spfile

• Displaying init.ora values with v$parameter

2-3: Oracle*Net configuration

• Creating the listener.ora file

• Creating the tnsnames.ora file

2-4: Data buffer configuration & sizing

• Inside the Oracle data buffers

• Using the KEEP pool

• Monitoring buffer effectiveness

• Using multiple blocksizes (multiple buffer pools)

2-5: 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

2-6: Troubleshooting network connectivity

• Verifying network connectivity with ping and tnsping

• Testing database links

Oracle object management

3-1: Oracle tables, views and materialized views

• Types of Oracle tables (regular, IOT, sorted hash clusters, nested tables)

• Oracle Views

• Oracle materialized views

3-2: Oracle indexes

• Types of Oracle indexes (b-tree, bitmap, bitmap join index)

• Creating B*-Tree, bitmap and function-based Indexes

• Index maintenance

3-3: Oracle constraints

• Types of Oracle indexes constraints (check, not null, unique, PK, FK)

• Cascading constraints

3-4: 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)

3-5: 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)

3-6: Oracle DBA Utilities

• Data pump (Imp and exp utilities)

• Flashback

• DataGuard

• Replication (Streams, multimaster, materialized views)

Monitoring Oracle

4-1: Dictionary and v$ views

• The dba_, all_ and user_ structures

4-2: Table & index monitoring

• Monitoring table extents and fragmentation

• Using the dba_tables and dba_segments views

 • Monitoring table extents and fragmentation

• Locating chained rows

• Monitoring table & index growth

• Monitoring index usage

• Monitoring index fragmentation

• Locating un-used indexes

• Reorganizing Indexes with alter index rebuild

• Dropping Indexes

4-4: Instance monitoring

• Monitoring with the AWR and STATSPACK

• Creating a time-series performance report

• Scripts for AWR and STATSPACK

• Plotting performance data (Ion, Excel)

4-5: 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

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.

5-1: Bottleneck performance analysis

• Drill-down into AWR reports

• Top-5 timed events

• External Server Bottlenecks (Network, I/O, RAM, CPU)

• Network troubleshooting
5-2: 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)

5-4: Tracing SQL Execution

• Using EXPLAIN PLAN

• Using “set autotrace”

• Interpreting EXPLAIN PLAN Output

• Using TKPROF / SQL*Trace

5-6: 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

• Collecting table and index statistics (dbms_stats)

6-1: Oracle High Availability tools

• Continuous availability and disaster recovery

• DataGuard

• Oracle Streams

• Real Application Clusters

6-2: Backup & Recovery

• OS-level backups

• Hardware-level backup & recovery

• Block-level change tracking

• Backup & recovery and RAID level

• Oracle-level backups (expdp & RMAN)

• Hot vs. Cold backups