Oracle DBA
Who should Attend This Course:
This course is designed for database administrators and technical support staff who are required to plan and implement database backup and recovery strategies for Oracle10g and 11g databases.It also covers database performance.
Prerequisite Skills:
Introduction of Database:
Introduction to RDBMS
DBMS and RDMS concepts.
12 CODD Rules.
Introduction of Tables, Indexes and Types of Indexes,
Sub queries, Aggregate functions, parsing,Joins and types of joins.
Writing basic SQL Select statements - using Where, Order by, distinct clauses describing the SQL select capabilities, operators
Single Row Functions - Character, Numeric, Date, Conversion and General Functions
Joins - Equi, Non-Equi, Outer, Inner, Self, Cross, Left Outer, Right Outer, Full Outer Joins
Aggregating Functions - Group By & Having clauses
Subqueries, Correlated Subqueries
Creating and Managing Tables
Primary Key, Foreign Key, Unique, Not Null, Check
Database Objects - Creating Views, Sequences, Synonyms and Indexes
DDL, DML, TCL, DCL,
PL/SQL Architecture, Defining Variables, Anonymous Blocks
Control Structures, Cursors, Procedures, Functions, Packages and Triggers
UNIX Duration :
Introduction to UNIX OS Origin, History, Versions, Features.
Structure of UNIX Commands - Architecture, File System, Logging In
Working with UNIX Commands - Password, Directory, File etc.
File Access Permissions - chmod (absolute, symbolic), umask
Wildcard Characters and Redirections - (*,?,[]), (stdin, stdout, stderr)
Editors-vi (advance) - 3 modes: escape, insert, coionx colon.
Simple Filters - displaying lines, word count, translate characters, sort lines, etc.
Searching files using find command by using various options
Communication Tools - using write, mesg, talk, mail and wall commands
Linking Files using hard and soft (symbolic links)
Process Scheduling - background processes, killing a process,session
10g/11g Database:
Oracle Architecture:
What is an Instance?
Logical Structure. (Tablespace, Tables, Segments, Extents, Blocks)
Memory Structure. (SGA and PGA)
Physical Structure. (Control files, Redologfiles, Datafiles, Alert Logfile, Trace File, SP File, Initora File)
Process Structure. (User Process, Server Process, Background Process)
SGA:
Database Buffer Cache, Redo log Buffer cache, Shared pool (library cache & dictionary cache), large pool, Java pool
Back Ground Processes:
PMON, SMON, DBWR, LGWR, CKPT,MMON,REC,ARC
Creating and Managing Initialization files - PFILE and SPFILE,
Types of Database Startup/Shutdown
Monitoring Alert and Trace Files in udump, bdump and cdump.
V$, DBA_views
Oracle Tablespace Management.
Types of Tablespaces, Create,alter,add,resize Tablespace, Managing and Monitoring Tablespaces,Creating and Managing Undo Management,rollback segments
Oracle User Management.
Maintaining and Creating Users, Drop, Grant Roles and Privileges to Users
Oracle Networks.
Networking Concepts in Oracle TCP/IP, TNS, and Listener status.
Maintaining the Control File and Redo Log File
Importance, Role, Sizing, Contents, Multiplexing and Backing up of the Control File
Purpose of and how online redo log files work with their associated Background Processes
Controlling log switches and Checkpoints, Multiplexing and Archiving Online Redo Log Files
Create Database.
Steps for creating Database and Parameters required.
Database Health Checks.
Monitoring, Maintaining and Managing the Database with Daily Checks.
UNIX Commands.
Simple UNIX commands useful for Daily Checks.
Backup and Recovery
Types of Backups and uses,
Logical Backups, Physical Backups, RMAN.
User Managed Backups and Recoveries – Backup and Recovery Operations, Closed and Open DB Backups (Hot and Cold), Complete Recovery, Identifying the situations which require Incomplete Recovery, Recovery from loss of Online Redo Logs, Creating Clone DBs
Oracle Recovery Manager (RMAN) – Features and Components, Repository, Channel Allocation, Configuring RMAN Environment, Backup Commands and Scripts for Backupsets, image copies, Complete and Incomplete Recovery and Full/Incremental
Transporting Data between DBs using Export, Import, SQL*Loader, Transportable Tablespaces
Performance Tuning:
Tuning SGA and Application - Database and Application.
Overview of Tuning Phases, Goals, Methodology and Common Performance problems
Tuning O/S, Virtual and Physical Memory, Paging and Swapping, CPU Tuning guidelines
Diagnosing and Tuning Latch and Lock Contention, Partitioning, Redo Log File Configuration
Enabling Oracle’s Parallel Query Option, Multiple DB Writers, Log Writers, Table Caching
Sizing of SGA – Shared Pool (Reuse Stmts., etc.), Buffer Cache, Redo Log Buffer Cache
Tuning Rollback/Undo Segments – Usage, Configuration, Detecting and Resolving problems
Diagnostic and Tuning Tools – Alert Log File, Trace Files, TKPROF, Explain Plan, Dynamic Performance Views, TIMED_STATISTICS parameter to collect statistics, STATSPACK
Application Tuning – SQL Tuning, Optimizer Modes, Plan Stability, Hints, Detecting and Resolving Row Chaining and Migration, Indexes, Data Storage Structures
Oracle Enterprise Manager (OEM)
Creating OEM Repository, Configuring OEM with Oracle Intelligent Agent and Management Server.
Creating DB Objects (Tablespaces, Rollback Segments, etc., Performing various DBA operations (DB Backup, RMAN Backup), Scheduling Jobs, Events and Event-Notification
Oracle 10g/11g New Features
Automatic Storage Management (ASM)
Oracle Data Pump
Automatic Shared Memory Management using AWR, ASH & ADDM.
Transportable Tablespaces across platforms
Enhanced Flashback features
|
