Oracle 11g Database Administration Curriculum

Oracle 11g Database Administration

Program

For OCA and OCP

Path

Curriculum

Module 1: Introduction to Oracle 11g SQL Chap 1: Introduction to Oracle 11g and SQL

Uniqueness of Oracle Database Suite

Time line of Oracle database and versions

Structure Query Language Overview (On Demand)

Fundamental of DBMS (On Demand)

Use of SQL

Format and Types of SQL and Other SQL

Oracle and SQL Relevance

SQL vs. SQL* Plus vs. SQL Developer

Chap 2: Writing Basic SQL Commands:

List of capabilities of SQL Select Statements Writing First SELECT statement

Selecting all or specific columns in Oracle Tables

Sorting Table Data from SELECT Statement using ORDER BY clause Selecting specific rows using SELECT command using WHERE BY clause Using ampersand (&) substitution variable in SELECT commands

Chap 3: Single Row SQL Functions:

Types of Functions- Single Row, Multiple Row, Character, Number, Date and Conversion Functions

Using Single Row character functions to customized the output

Using various character functions, number functions and date functions Describing types of conversion functions

Using TO_CHAR, TO_NUMBER and TO_DATE conversion functions Applying function based condition in WHERE BY clause in SELECT statement

Chap 4: Aggregate SQL Functions:

Describing various types of group functions and their use

Using GROUP BY clause to group data from SELECT statement

Including or excluding grouped rows using HAVING clause

Chap 5: Displaying Data From Multiple Tables

Describing basics of Oracle and SQL joins

Writing SELECT statement to access the data from more than one tables using SQL equijoins and non-equijoins

Join a table to itself using self join

View the data that generally does not meet a join condition using OUTER JOIN Generating Cartesian product

Chap 6: Using Sub-queries

Define Sub query and identifying the need for sub queries

List the types of sub queries

Writing Single and Multiple Row Sub queries

Chap 7: Using SET operators

Describe SET operators and their types

Use set operators to combine the output of SELECT statements

Control of order of rows returned from SET operator sub queries

Chap 8: Manipulating Data Using DML Commands

Describe each DML statement

Inserting rows into table

Update rows in a table

 Bulk insertion of rows using sub queries inside INSERT statement

Using sub query to update table

Controlling transaction using COMMIT and ROLLBACK

Use of SAVEPOINT

Chap 9: Creating and Managing Tables Using DDL Commands

Listing the main database objects

Reviewing the table structure using DESCRIBE or DESC command List the data types available for columns in Oracle SQL

Creating a simple table

Adding columns to existing table using ALTER command

Removing columns from existing table Renaming column name of table Altering column specification

Overview of constraints and types of constraints

Applying UNIQUE and NOT NULL constraints at the time of creation of table (Column Level)

Applying UNIQUE and NOT NULL constraints at the time of creation of table (Table Level)

Concept of Primary Key

Applying Primary Key constraint at the time of creation of table (Column Level) Applying Primary constraint at the time of creation of table (Table Level) Using CHECK constraint to force domain of data entry (Column Level)

Using CHECK constraint to force domain of data entry (Table Level)

Creating Primary Key and Foreign Key Relationship with referential integrity REFERCES constraints

Applying constraints after the table creation using ALTER command

Chap 10: Other Schema Objects

Introduction to VIEW, SYNONYMS, INDEX and SEQUENCE

Creating Simple and Complex views

Retrieving data from VIEW

Creating, Managing and using SEQUENCES

Creating and Managing INDEXES

Creating public and private SYNONYMS

Renaming object names

 Introduction to Oracle 11g Architecture Understanding concept of Database Server Concept of Instance and Database Connection to Database Server

Ways to connect to database server Establishing the Session

User Process and Server Process Connection

Describing the main players of Oracle 11g Database Server

Understanding the memory structure (Database Buffer Cache, Log Buffer Cache, Shared Pool, Data Dictionary Cache, Java Pool, Large Pool, Stream Pool and etc)

Understanding the Process structure (DBWn, LGWR, CKPT, PMON, SMON, RECO, ARCHn and etc)

Understanding the storage structure Concept of TABLESPACE and DATAFILES Logical view of database

Physical view of database

Chap 2: Preparing Database Environment

Listing and identifying the tools for DBAs

Planning for Oracle Database Suite 11g Release 2

Concept of ORACLE_HOME and ORACLE_BASE directory parameter

Installing Oracle 11g Software using Oracle Universal Installer

Chap 3: Using DBCA

Create a database using Database Configuration Assistance (DBCA)

Creating Database template

Deleting a database

Modifying database settings using DBCA

Chap 4: Configuring Oracle Network Environment

Concept of Listener

Adding Listener

Registering Listener with Database

 Chap 5: Managing Oracle Instance

Starting up the Database Using SQL* PLUS

Stages of Database Startup (Close, No mount, Mount and Open) Concept of Initialization Parameters

SPFILE and PFILE

Creating PFILE from SPFILE and vice versa Use of creating PFILE

Starting up the database with modified PFILE Parameters and their values

Altering Parameter using ALTER SYSTEM command

Viewing Alert Log and Trace Files

Using Data Dictionary and Dynamic Performance Views

Chap 6: Oracle Enterprise Manager

Web Interface of Oracle Manager

Use of OEM or Database Control or DB Console

Using EMCA to install or reinstall OEM (If not installed by default) Starting up and Shutting down the database using OEM

Viewing Alert Log in OEM

Navigating Through OEM

Creating and Managing Database Objects using OEM

Chap 7: Managing Database Storage Architecture:

Overview of TABLESPACE and DATAFILES Creating TABLESPACE

Managing Growth of TABLESPACE Adding Data files in TABLESPACE Space Management in TABLESPACE Defining Default TABLESPACE

Understanding the use of Temporary and UNDO TABLESPACE Creating TEMPORARY and UNDO TABLESPACE

Fundamental of ASM

Chap 8: Administering User Security:

 Changing Password, Locking, Unlocking and other activities with Database User Account Assigning Default TABLESPACE and QUOTA to user

Concept of Privileges- System and Object Privileges Assigning System and Object Privilege To Users

Using WITH ADMIN OPTION and WITH GRANT OPTION clause with privileges Overview and Use of ROLE

Creating ROLES

Assigning privileges to ROLE Granting ROLES to Users Creating and Managing Profile Principle of Database security

Chap 9: Managing Data Concurrency

Managing and Viewing User Sessions

Killing User Sessions

Demonstration of Lock Conflict

Resolving the lock conflict

Chap 10: Managing UNDO Data

Overview of UNDO segment

Use of UNDO Segment

Various properties of UNDO segment

Managing UNDO retention

Chap 11: Database Maintenance

Use and manage optimizer statistics

Use and manage Automatic Workload Repository (AWR)

Use Advisory Framework

Chap 12: Performance Management

Using Automatic Memory Management

Using Automatic Shared Memory Management

Using Memory Advisor

 Module 3: Oracle 11g Database Administration Workshop 1 Chap 1: Introduction to Oracle Backup Procedure

Oracle 11g Architecture Revisit

Importance of REDOLOG, CKPT and ARCHIVEDLOG files Use of Backup

Types of Backup – Hot and Cold Backup, User Managed and Server Manager Backup Oracle Backup Solutions

Types of failures in environment

Chap 2: Setting up Backup Task Activities

Configuring Archived Log Mode for Database

Locating data files, redo log file, archive log files and control file destination Listing important Oracle directories

Configuring FLASH_RECOVERY_AREA

Chap 3: Performing Database Backup in Basic Mode

Performing User Managed database OFFLINE or COLD backup using SQL Plus Performing User Managed database ONLINE or HOT backup using SQL Plus Configuring Database Backup parameters from Oracle Enterprise Manager Performing RMAN Backup Using Oracle Enterprise Manager

Chap 4: Performing RMAN Backup

Create Image file backups
Create Whole Image backup
Create Base backup for Incremental level backup
Create Incremental level 1 backup
Create and use RMAN stored scripts

Chap 5: Performing Recovery

Recovery from a lost TEMP file

Recovery from a lost redo log group

Recovery from a lost password file

Performing User Managed Completed Recovery

Performing User Managed Incomplete Recovery

Backup and Recover Control File