Oracle DBA Training Course

For Oracle DBA

Oracle Course Objective

·          The Oracle Database 10g OCA is the first step in the Oracle Database 10g certification track and is the first step in building a successful career as an Oracle professional. Skilled database administrators are in high demand and the OCAs credential is a great way to get started towards a career as a database administrator. Oracle Database 10g DBA OCAs have proven competency in foundational skills required to manage important database administration and configuration functions.

 

Who should do this course?

• Anyone who would like to get to grips with Oracle databases

• Students who like to work in an Oracle environment or become a DBA

• Students wishing to get Oracle certification

 Send an enquiry here

NB:  This course can be tailored for application designers and developers, database administrators, and business users and non-technical end users.

Frequently Asked Questions

• Before enrolling for an Oracle course you should have the following information ready:
  
   

  • Date you wish to attend the course

  • Payment method – how you will be paying

  • An understanding of the costs of the total package
    
     

• You can call us on our office number (see our web site contact page on www.i2ko.com and request a booking telephonically

• You can book by email – simply send us an email with your name, the date and how you would like to pay. We will send you an invoice and upon payment your place is secure

• Request a credit card payment. We do accept credit card payments.

Where do you train the Oracle course?
We train the Oracle course at our Greenpoint training centre in Cape Town, South Africa.
 

What other branches do you have in SA or in the world?
We only train in Cape Town.

What books do I get with this course?
You will receive a course text book which you can take away with you to keep after the course.

How long will it take me to get certified?
We recommend that students take at least one to two weeks for exam preparation for every module they do. It is unusual for students to be able to take the exam in the same week they do the course.

What are the dates for the course?
See our schedule page: http://www.i2ko.com/Schedule_All.html

Does the price include the official exam?
No, the exam is not included in the price. Exam prices vary from centre to centre.

  About the Oracle Exam

 How much does the Oracle course cost?

The Oracle exams are done at any authorised testing centre such as 2KO.  

The exam is a multiple test, on computer at an authorised testy centre. The results are made known immediately. If you fail the test, you must book it again at a future date and will pay again.

 Send an enquiry here

The Oracle course fee is quoted in the currency of your country. Please contact us for the latest prices and special offers. The exam fees are not included in the course fee unless specifically stated.

If you are from out of town, we can arrange accommodation close to our centre. Accommodation rates vary depending on the time of year.

Included in your course fees:

Before doing the Oracle course we recommend that you should be able to use a PC to save files and work with files and folders. You should have a basic knowledge of networking.

Tuition for five days

Breakfast daily at the training centre (where applicable)

Teas and coffees

Lunches daily

Attendance certificate

Oracle Course Outline

Architecture & Configuration

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

1-2: Instance Architecture

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

1-3: Oracle Instance Internals

• SGA vs. PGA
• Background processes
• Interfaces with server and disk I/O subsystem

1-4: Using SQL*Plus for DBA management

• Connecting and executing SQL
• Using the “as sysdba” syntax
• Overview of SQL*Plus DBA commands (startup, etc.)

1-5: 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-6: 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
• Function-based indexes
• Finding indexing opportunities
• Index maintenance

3-3: Oracle constraints

• Costs & benefits of 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)
• SQL*Loader
• LogMiner
• Flashback
• DataGuard
• Oracle DBA utilities – Oracle dbms packages (dbms_redefinition)
• Replication (Streams, multimaster, materialized views)

Monitoring Oracle

This section explores the methods used for monitoring all active components of the Oracle database.

4-1: Dictionary and v$ views

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

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

4-3: workload & trend monitoring

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

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

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

4-6: 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.

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

5-4: Tracing SQL Execution

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

5-5: SQL Execution Internals

• Review of Basic joining methods
• Merge join
• Hash Join
• Nested Loop join
• Advanced SQL operators
• Between operator
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
• Re-writing SQL queries
• Tuning sub-queries

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

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