Oracle DBA Training Syllabus

Oracle DBA Training Syllabus – Part 1 Introduction (Database Architecture) •

Describe course objectives

•

!"lore the Oracle 1#g database architecture

Installing the Oracle Database So$t%are •

!"lain core DBA tas&s and tools

•

Plan an Oracle installation

•

'se o"tial e!ible architecture

•

Install so$t%are %ith the Oracle 'niversal Installer (O'I)

*reating an Oracle Database •

*reate a database %ith the Database *on+guration Assistant (DB*A)

•

*reate a database design te"late %ith the DB*A

•

,enerate database creation scri"ts %ith the DB*A

-anaging the Oracle Instance •

Start and sto" the Oracle database and co"onents

•

'se nter"rise -anager (-)

•

Access a database %ith S./0Plus and iS./0Plus

•

-odi$y database initialiation "araeters

•

'nderstand the stages o$ database startu"

•

2ie% the Alert log

•

'se the Data Dictionary

-anaging Database Storage Structures •

Describe table data storage (in bloc&s)

•

De+ne the "ur"ose o$ tables"aces and data +les

•

'nderstand and utilie Oracle -anaged 3iles (O-3)

•

*reate and anage tables"aces

•

Obtain tables"ace in$oration

•

Describe the ain conce"ts and $ unctionality o$ Autoatic Storage -anageent (AS-)

Adinistering 'ser Security •

*reate and anage database user accounts

•

Authenticate users

•

Assign de$ault storage areas (tables"aces)

•

,rant and revo&e "rivileges

•

*reate and anage roles

•

*reate and anage "ro+les

•

I"leent standard "ass%ord security $eatures

•

*ontrol resource usage by users

-anaging Schea Objects •

De+ne schea objects and data ty"es

•

*reate and odi$y tables

•

De+ne constraints

•

2ie% the coluns and contents o$ a table

•

*reate inde!es4 vie%s and se5uences

•

!"lain the use o$ te"orary tables

•

'se the Data Dictionary

•

-anage data through S./

•

-onitor and resolve loc&ing conicts

-anaging 'ndo Data •

!"lain D-/ and undo data generation

•

-onitor and adinister undo

•

Describe the di6erence bet%een undo and redo data

•

*on+gure undo retention

•

,uarantee undo retention

•

'se the undo advisor

I"leenting Oracle Database Security •

Describe DBA res"onsibilities $or security

•

A""ly the "rinci"al o$ least "rivilege

•

nable standard database auditing

•

S"eci$y audit o"tions

•

7evie% audit in$oration

•

-aintain the audit trail

Oracle DBA Training Syllabus – Part 8 *on+guring the Oracle 9et%or& nvironent •

*reate additional listeners

•

*reate 9et Service aliases

•

*on+gure connect:tie $ailover

•

*ontrol the Oracle 9et /istener

•

Test Oracle 9et connectivity

•

Identi$y %hen to use shared versus dedicated servers

Bac&u" and 7ecovery *once"ts •

Identi$y the ty"es o$ $ailure that ay occur in an Oracle Database

•

Describe %ays to tune instance recovery

•

Identi$y the i"ortance o$ chec&"oints4 redo log +les4 and archived log +les

•

*on+gure A7*;I2/O, ode

Per$oring Database Bac&u"s •

*reate consistent database bac&u"s

•

Bac& your database u" %ithout shutting it do%n

•

*reate increental bac&u"s

•

Autoate database bac&u"s

•

-onitor the ash recovery area

Per$oring Database 7ecovery •

7ecover $ro loss o$ a control +le

•

7ecover $ro loss o$ a redo log +le

•

Per$or co"lete recovery $ollo%ing the loss o$ a data +le

Per$oring 3lashbac& •

Describe 3lashbac& database

•

7estore the table content to a s"eci+c "oint in the "ast %ith 3lashbac& Table

•

7ecover $ro a dro""ed table

•

2ie% the contents o$ the database as o$ any single "oint in tie %ith 3lashbac& .uery

•

See versions o$ a ro% over tie %ith 3lashbac& 2ersions .uery

•

2ie% the transaction history o$ a ro% %ith 3lashbac& Transaction .uery

-oving Data •

Describe available %ays $or oving data

•

*reate and use directory objects

•

'se S./0/oader to load data $ro a non:Oracle database (or user +les)

•

!"lain the general architecture o$ Data Pu"

•

'se Data Pu" !"ort and I"ort to ove data bet%een Oracle databases

•

'se e!ternal tables to ove data via "lat$or:inde"endent +les

*on+guring 7ecovery -anager •

7ecovery -anager 3eatures and *o"onents

•

'sing a 3lash 7ecovery Area %ith 7-A9

•

*on+guring 7-A9

•

*ontrol 3ile Autobac&u"s

•

7etention Policies and *hannel Allocation

•

'sing 7ecovery -anager to connect to a target database in de$ault 9O*ATA/O, ode

•

Dis"laying the current 7-A9 con+guration settings

•

Altering the bac&u" retention "olicy $or a database

'sing 7ecovery -anager •

7-A9 *oand Overvie%

•

Paralleliation o$ Bac&u" Sets

•

*o"ressed Bac&u"s

•

Iage *o"y

•


•

/IST and 7PO7T coands

•

nable A7*;I2/O, ode $or the database

•

'se 7ecovery -anager

7ecovering $ro 9on:critical /osses •

7ecovery o$ 9on:*ritical 3iles

•

*reating 9e% Te"orary Tables"ace

•

7ecreating 7edo /og 3iles4 Inde! Tables"aces4 and Inde!es

•

7ead:Only Tables"ace 7ecovery

•

Authentication -ethods $or Database Adinistrators

•

/oss o$ Pass%ord Authentication 3ile

•

*reating a ne% te"orary tables"ace

•

Altering the de$ault te"orary tables"ace $or a database

Inco"lete 7ecovery •

7ecovery Ste"s

•

Server and 'ser -anaged 7ecovery coands

•

7ecovering a *ontrol 3ile Autobac&u"

•

*reating a 9e% *ontrol 3ile

•

Inco"lete 7ecovery Overvie%

•

Inco"lete 7ecovery Best Practices

•

Si"li+ed 7ecovery Through 7ST/O,S

•

Point:in:tie recovery using 7-A9

3lashbac& •

3lashbac& Database Architecture

•

*on+guring and -onitoring 3lashbac& Database

•

Bac&ing '" the 3lash 7ecovery Area

•

'sing 2=3/AS;>7*O27?>A7A>'SA,

•

3lashbac& Database *onsiderations

•

'sing the 3lashbac& Database 7-A9 inter$ace

•

'sing 3lashbac& Database - Inter$ace -anaging and onitoring 3lashbac& Database o"erations

Dealing %ith Database *orru"tion •

Bloc& *orru"tion Sy"tos@ O7A:1C

•

DB27I3? 'tility and the A9A/? coand

•

Initialiation "araeter DB>B/O*E>*;*EI9,

•

Segent -etadata Du" and 2eri+cation

•

'sing 3lashbac& $or /ogical *orru"tion and using DB-S>7PAI7

•

Bloc& -edia 7ecovery

•

7-A9 B-7 Inter$ace

•

Du"ing and 2eri$ying Segent -etadata

-onitoring and -anaging Storage I •

Database Storage Structures

•

S"ace -anageent Overvie%

•

Oracle:-anaged 3iles (O-3)

•

7o% *haining and -igrating

•

Proactive Tables"ace -onitoring

•

-anaging 7esuable S"ace Allocation

•

S?SA'F Tables"ace

•

-onitoring table and inde! s"ace usage

-onitoring and -anaging Storage II •

Autoatic 'ndo -anageent

•

7edo /og 3iles

•

Table Ty"es

•

Partitioned Tables

•

Inde!:Organied Tables (IOT)

•

-anaging inde! s"ace %ith S./

•

*on+gure o"tial redo log +le sie

•

2ie% GAutoatic Tuning o$ 'ndo 7etentionH

2/DB Su""ort •

*reating Big+le Tables"aces

•

Pac&ages and data dictionary changes to su""ort 2/DB

•

*reating and aintaining te"orary tables"ace grou"s (TT,)

•

Partitioning and Partitioned Inde!es

•

S&i""ing unusable inde!es

•

D-/ rror /ogging

•

Inter"reting Big+le 7O
Autoating Tas&s %ith the Scheduler •

Scheduler *once"ts

•

*reating a ob *lass and a
•

-anaging obs4 Progras4 *hains4 vents4 Schedules4 "riority

•

2ie%ing and Purging ob /ogs

•

*reating a "rogra and a schedule

•

*reating a job that uses a "rogra and a schedule

•

Altering the "rogra and schedule $or the job and observing the behavior change o$ the job

•

-onitoring job runs



•

Scenario 1@ Database "er$orance

•

Scenario 8@ 3inding and Tuning IneJcient S./

•

Scenario K@ S,A -anageent – 7DO

•

Scenario L@ 7unning out o$ 'ndo S"ace

•

Scenario @ -issing data+le

•

Scenario M@ -anaging s"ace in a tables"ace – 7DO

•

Scenario @ -issing T-P data +le

INTRODUCTION TO ORACLE DBA

What is DBA ? Why a Company needs a DBA? Roles & Responsibilities of DBA Oracle Architecture Physical and Logical Phase of Database ypes of files!Control file" Redolog file and Datafile# Different types of Datafiles $nstance %A!%ystem lobal Area# and Bac'ground Process

DATABASE CREATION

Different %tartup (odes Different %hutdo)n (odes ypes of Database Creation (anual (ethod of Creation

 TABLESPACE MANAGEMENT

$ntroduction to able space ypes of able space!B$ & %(ALL# emporary able space Altering*Renaming a able space Renaming a data file emporary roups able space +ncryption!,,g#

STORAGE PARAMETERS

What are segments? What are e-tents? What is Bloc's? Oracle Bloc's +stimation of +-tents Dictionary (anaged able space Locally (anaged able space %torage Parameters!$nitial" .e-t" (in +-tents" /tents" Pctincrease#

USER MANAGEMENT

Who are Database 0sers? Why to create a 0ser? Creation of 0sers Assigning Pri1ileges Creating and granting Roles Allocating table space 2uotas to 0sers Assigning default table space for the 0sers %etting Profiles for the 0sers Le1els of Authentication %chema (anagement!,,g# %ecurity!,,g#

UNDO MANAGEMENT

What is 0ndo? Why 0ndo re3uired? Pending offline status Brief idea about Rollbac' segments 4lashbac' 4eatures 4lashbac' able 4lashbac' 3uery 4lashbac' 5ersion 3uery

What is spfle?

Difference bet)een spfile and pfile Ad1antages of spfile What is O(4? Ad1antages of O(4 4iles con1entions of O(4

NETWORKING IN ORACLE

$ntroduction to Oracle .et)or'ing .et)or' default location +diting .et)or' file

 Tnsnames!"a

$ntroduction to Database Lin's ypes of Database Lin's $ntroduction to (ateriali6ed 1ie)s Ad1antages of (ateriali6ed 1ie) Refresh (odes

REDO#LOG $ CONTROL%ILE MANAGEMENT

$ntroduction to Redo log

%tatus of Redo logs (ultiple-ing the Redo log files Re7creation of Control file Renaming the Database (ultiple-ing the Control file

LOGICAL BACKUP

What is a logical bac'up Why it is re3uired +-port*import 0tility Data Pump Difference bet)een Data Pump and raditional +-port*import $mpdp*e-pdp ransportable able spaces

P&'SICAL BACKUP

$ntroduction to Physical bac'up ypes of Physical bac'up Cold & 8ot bac'up Reco1ery scenarios R(A. bac'up R(A. Point in time reco1ery R(A. ,,g ne) features

PE%ORMANCE TUNING

What is Performance uning? Why uning re3uired? ypes of uning Database uning uning %2L uning (emory uning

.et)or' uning Partitioning and $nde-es Partitioning +nhancements AWR*A%8*A%((*ADD( RA!Real Application esting#!,,g# %2L Performance Analyses !,,g# Ro) chaining and Ro) migration Resemble able spaces