Database Administration

Course Title: Database Administration

Course No: CSIT.415.2

Nature of the Course: Theory + Lab

Semester: 7

Full Marks: 60 + 20 + 20

Pass Marks: 24 + 10 + 10

Credit Hours: 3

Course Description

This course introduces the database administration techniques in Oracle. Most of the DBA's tasks are covered. Topics covered include principles of DBA Roles, Oracle Database Architecture and storage management, Database backup, restoration and recovery, connectivity and user management for database security, Tuning of database and overall DB administration.

Course Objectives

Understand the basic role, task and responsibilities of Database Administrator. Understand the Oracle database architecture and how its components work and interact with one another. Be able to install and configure an Oracle Database. Be able to administer the Oracle Database, create and manage storage structures and Create and manage the users. Be able to perform backup and recovery, tuning the oracle database for the better performance. Be able to create database objects like tables, views, indexes etc. and able to write PL/SQL Procedures.

Course Contents

1. Introduction

6 hrs

1.1. Responsibilities and Role of Database Administrator

1.2. Oracle Database Architecture Overview

1.3. Process Architecture

1.4. Memory structures

1.5. Logical and physical storage structures

1.6. Oracle ASM

1.7. Oracle Database version

1.8. SQL*PLUS Overview

1.9. Oracle Enterprise Manager

1.10. iSQL*PLUS

2. Creating and Managing Oracle Database

4 hrs

2.1. Create and Delete a Database

2.2. Password Management

2.3. Start and stop the Oracle database and components

2.4. Modify database installation parameters

2.5. Describe the stages of database startup

2.6. Describe database shutdown options

2.7. View the alert log

2.8. Access dynamic performance views

3. Understanding Oracle Logical Database Structures

6 hrs

3.1. Creating and managing tables, views, constraints

Creating and managing tables

Creating and managing views

Creating and managing constraints

3.2. Use of DML operations on tables

3.3. Creating index

3.4. Creating users and schema

Creating users

Creating schema

3.5. Creating sequences, synonyms

Creating sequences

Creating synonyms

3.6. Use of PL/SQL Blocks, functions, procedures, packages

PL/SQL Blocks

Functions

Procedures

Packages

3.7. External File Accesses, Database links and remote databases

External File Accesses

Database links and remote databases

4. Managing Database Storage Structures

6 hrs

4.1. Storage Structures

4.2. Tablespace and Datafile management

Tablespace management

Datafile management

4.3. Multiplexing Control files, Redo log files and Archive redo logs

Multiplexing Control files

Multiplexing Redo log files

Multiplexing Archive redo logs

4.4. Configure database in Archive log mode

4.5. Manage FRA (Flash Recovery Area)

4.6. Maintaining and monitoring redo log files, Archive logs

Maintaining and monitoring redo log files

Maintaining and monitoring Archive logs

4.7. Oracle Managed Files (OMF)

5. Administering User Security

5 hrs

5.1. Managing Database User Accounts

5.2. Predefined Administrative Accounts

5.3. Creating Roles and assigning Role

Creating Roles

Assigning Roles

5.4. Predefined Roles

5.5. Implementing Profiles

5.6. Managing privileges

5.7. Database Security and Auditing

Database Security

Auditing

5.8. Virtual Private Database

6. Configuring the Oracle Network Environment

3 hrs

6.1. How Oracle Networking works

6.2. Create and configure the Listener

6.3. Enable Oracle Restart to monitor the listener

6.4. Use tnsping to test Oracle Net connectivity

6.5. Configure and edit the tnsnames.ora and listener.ora files using Oracle NET Manager

Configuring tnsnames.ora using Oracle NET Manager

Configuring listener.ora using Oracle NET Manager

6.6. Identify when to use shared servers and when to use dedicated servers

6.7. The Oracle Client

7. Backup and Recovery concept

6 hrs

7.1. Backup Overview

7.2. Oracle Secure Backup

7.3. User-Managed Backup

7.4. Logical Backup, Physical Backup, Offline Backups, Online Backups

Logical Backup

Physical Backup

Offline Backups

Online Backups

7.5. Data Pump Export and Import

Data Pump Export

Data Pump Import

7.6. SQL Loader

7.7. Types of Database Failure

7.8. Oracle Recovery Process

7.9. Understanding Instance Recovery

7.10. Flashback Techniques and Recovery

7.11. Database Corruption Detection

8. Recovery Manager (RMAN)

4 hrs

8.1. RMAN Features and Configuring RMAN Backup Settings

RMAN Features

Configuring RMAN Backup Settings

8.2. RMAN vs. Traditional Backup Methods

8.3. Overview of RMAN Commands and Options

8.4. Backup Operations: Full Database Backups, tablespace, datafile, control file and spfile backup, Archived Redo Logs, Incremental Backup

Full Database Backups

Tablespace, datafile, control file and spfile backup

Archived Redo Logs backup

Incremental Backup

8.5. Performing Recovery with RMAN

9. Performance Tuning

5 hrs

9.1. Brief overview of Tuning methodology, general tuning concepts

Tuning methodology

General tuning concepts

9.2. Performance Monitoring

9.3. Managing Memory Components

9.4. Enabling Automatic Memory Management (AMM)

9.5. Automatic Shared Memory Advisor

9.6. Dynamic Performance Statistics

9.7. ADDM (Automatic Database Diagnostic Monitor)

9.8. SQL Tuning Advisor

9.9. Automatic Workload Repository (AWR)

Laboratory Works

Student should prepare lab sheet for most of the units in the syllabus. They should practice design database and implementation of database administration activities that demonstrates different concepts discussed is class. However, nature of lab work can be decided by the instructor. The lab work should be practiced for minimum of 3 lab hours per week.

1.DBA Activities Implementation

Text Books

1.Sam R. Alapati, Expert Oracle Database 11g Administration, Apress

Reference Books

1.Bob Bryla, Kevin Loney, Oracle Database 11g DBA Handbook, Oracle Press
2.Introduction to Database Administration, by O'reilly
3.C.J. Date, Database Systems, Addison Wesley, 2000

Notes:

This syllabus follows the official CSIT curriculum of Far Western University. In case of any doubt or revision, the university's published syllabus shall be considered authoritative. https://cdc.fwu.edu.np/faculties.html

Database Administration

Database Administration

Select University

Select Program

TabFlux . Database Administration . FWU . BSc. CSIT