Database Administration
0%
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
Course Objectives
Course Contents
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.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.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.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.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.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.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.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
- 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