Database Administration

Course Title: Database Administration

Course No: BIT352

Nature of the Course: Theory + Lab

Semester: 6

Full Marks: 60 + 20 + 20

Pass Marks: 24 + 8 + 8

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 which could be useful for administrator in the future.

Course Objectives

To understand the basic role, task and responsibilities of Database Administrator, understand the Oracle database architecture, be able to install and configure an Oracle Database, be able to create database objects like tables, views, indexes etc. and able to write PL/SQL Procedures, be able to administer the Oracle Database, create and manage storage structures, create and manage the users, be able to perform backup and recovery, tuning the oracle database for the better performance.

Course Contents

1. Introduction Administration Basics

7 hrs

1.1. Introduction

Overview of the Oracle Database

Relational Database Concepts

Overview of Oracle Database Architecture

Overview of Oracle Multitenant Architecture

Overview of Oracle Data Guard, Oracle RAC, Oracle ASM, Oracle Cloud

Database Administration Tasks

1.2. Oracle Architecture

Oracle Database Architecture, the Oracle Database Instance

Memory Structures, Process Architecture and Structures

Server and Client Processes

Database Storage Architecture

Connect to the Oracle Database Instance

1.3. Administer Database Instance

Administrative Tools Available to a DBA

Use SQL*Plus in Oracle to Manage a Database Instance

Use SQL Developer to Manage a Database Instance

Administer the Database using Enterprise Manager (EM)

Overview of the Enterprise Manager Framework, Enterprise Manager Cloud Control

Initialization Parameter Files

2. Network Environment and Storage Structures

5 hrs

2.1. Configuring Network Environment

Overview of Network Configuration

Oracle Net Listener Configuration and Management

Oracle Net Naming Methods

Tools for Configuring and Managing the Oracle Network

Using the Net Configuration Assistant

Configure Client Connections with Net Manager

View Listener Configuration, Start and Stop the Oracle Listener

Use TNSPING to Test Oracle Net Connectivity

Connect to the Database

Configure Net Services with Enterprise Manager

2.2. Storage Structures

Overview of Data Storage, the Database Block

Overview of Tablespaces and Datafiles

Use Enterprise Manager to view the Storage Structure of the Database

Create and Alter Tablespace Commands

Temporary Tablespaces

Create and Manage Datafiles, use OMF

Drop Tablespaces and Datafiles

3. Users and Privileges

5 hrs

3.1. Manage Users

Predefined Database Administration Accounts, User Accounts

Create a User Account, User Authentication

Change a User's Password, Manage a User Account, Drop a User Account

Monitor User Information, Terminate User Sessions

Create a Schema Only Account

3.2. Manage Privileges

Database Access, Oracle Supplied Roles

System and Object Level Privileges

The Grant and Revoke Commands

Create, Modify and Drop Roles, Use Predefined Roles

4. Profiles, Resources, and Auditing

4 hrs

4.1. Profiles And Resources

Overview of User Profiles, Profile Resource Parameters

Create Profile Command

Manage Passwords with Profiles

Control Resource Usage with Profiles, Maintain Profiles

4.2. Database Auditing

Overview of Database Security, Overview of Database Auditing

Security Compliance, Standard Auditing, Unified Audit Trail

Separation of Audit Responsibilities with The AUDIT_ADMIN And AUDIT_VIEWER Roles

Configure the Audit Trail, Specify Audit Options

5. Concurrency, Backup and Recovery

7 hrs

5.1. Concurrency

Levels of Locking in Oracle, Methods Used to Acquire Locks

Data Concurrency, Possible Causes of Contention

DML Locks, Prevent Locking Problems

Detect Lock Contention, Resolve Conflicts

5.2. Undo Management

Undo Data Overview, Monitor and Administer Undo

Configure Undo Retention, Switch Undo Tablespaces

Specify the Retention Period, Guarantee Undo Retention

Retention Period for Flashback Queries, View Undo Space Information

Use the Undo Advisor, Size the Undo Tablespace

Alter an Undo Tablespace to a Fixed Size

5.3. Backup And Recovery

Oracle Backup Solutions, Oracle Suggested Backup Strategy

Overview of Database Backup, Restore and Recover

Flashback Technology, Types of Failure

Instance Recovery, Tune Instance Recovery, The MTTR Advisor

Media Failure, Configure a Database for Recoverability

6. Database Maintenance and Performance Management

4 hrs

6.1. Database Maintenance

Overview, View the Alert Log

The Automatic Workload Repository, Statistic Levels

The Automatic Database Diagnostic Monitoring

Monitor an Oracle Database, Use the Advisors

Set Up Notification Rules

6.2. Performance Management

Tuning Information Sources, Performance Monitoring

Tuning Activities, Performance Planning, Instance Tuning

Performance Tuning Methodology, Performance Tuning Data

Monitoring Performance, Managing Memory

Manage Private Temporary Tables

7. Tuning and Moving

5 hrs

7.1. Tuning

SQL Tuning, Cancel a SQL Statement in a Session

The Oracle Optimizer, SQL Plan Directives, Adaptive Execution Plans

SQL Advisors, Automatic SQL Tuning Results

Implement Automatic Tuning Recommendations, SQL Tuning Advisor

7.2. Moving Data

Create Directory Objects

Data Pump Architecture, Data Pump Data Dictionary Views

Data Pump Interactive Mode, Data Pump API

Use Data Pump to Export Data, Use Data Pump to Import Data

Overview of SQL Loader, Command Line Parameters, Record Filtering

Control File Keywords, Data files, SQL Loader Data Paths, External Tables

8. Managing Resources and Scheduling Tasks

4 hrs

8.1. Managing Resources

Overview of the Database Resource Manager, Use the Resource Manager

Create Resource Plans, the Default Maintenance Resource Manager Plan

Create Resource Plan Directives, Allocate Resources for Resource Plans

8.2. Scheduling Tasks

Introduction to The Scheduler, Access Rights

Scheduler Components and Workflow, Create a Job, Job Classes

Use Time Based, Event Based Schedules, Create an Event Based Schedule

9. Space Management and Oracle Support

4 hrs

9.1. Managing Space

Overview of Space Management, Block Space Management within Segments

Segment Types, Allocate Extents, Allocate Space

Row Chaining and Migration, Create Tables without Segments

9.2. Oracle Support

The Enterprise Manager Support Workbench, Register for Security Updates

Work with Oracle Support, My Oracle Support Integration

Log Service Requests, Manage Patches, Apply a Patch

Laboratory Works

Student should prepare lab sheet for most of the units in the syllabus. They should practice, design database and implement database administration activities that demonstrates different concepts discussed is the class.

1.Lab Sheets for All Units

Text Books

1.Oracle Database 19c DBA By Examples: Installation and Administration, Ravindra Gupta, 2021.
2.Pro Oracle Database 18c Administration: Manage and Safeguard Your Organization's Data, Michelle Malcher and Darl Kuhn, Third Edition.
3.Oracle Database 12c DBA Handbook, Manage a Scalable, Secure Oracle Enterprise Database Environment, Bob Bryla.
4.Oracle DBA Mentor: Succeeding as an Oracle Database Administrator, Brian Peasland.

Reference Books

1.https://docs.oracle.com/en/database/oracle/oracle-database/19/administration.html

Notes:

This syllabus follows the official BIT curriculum of Tribhuvan University. In case of any doubt or revision, the university’s published syllabus shall be considered authoritative.

Database Administration

Database Administration

Select University

Select Program

TabFlux . Database Administration . TU . BIT