MIT608 · Semester III · Official Syllabus

Database Administration Syllabus

Oracle database architecture, instance configuration, security, PL/SQL, backup and recovery, performance tuning, and task automation.

Unit 1: Introduction to Oracle Database and DBA Responsibilities

Oracle Database architecture, DBA roles and responsibilities, multitenant CDB/PDB model, installation with DBCA, administrative tools (SQL*Plus, SQL Developer, Enterprise Manager), and SQL*Loader file management.

  • Introduction of Oracle Database, Tasks of a Database Administrator
  • Oracle Database Architecture (Memory Structure, Storage Structure, Process Structure)
  • Oracle Multitenant Architecture (CDB, PDB, Benefits)
  • Installing and connecting database (DBCA, Oracle Net)
  • Administrative Tools (SQL*Plus, SQL Developer, Enterprise Manager)
  • SQL Loader Overview (Control file, Log file, Bad file, Discard file)

Unit 2: Oracle Instance Configuration and Storage Management

Parameter file configuration (PFILE vs SPFILE), control file and online redo log multiplexing, database archiving, tablespace and datafile management, startup/shutdown modes, undo management, and Oracle Net configuration.

  • Oracle instance configuration using parameter file (PFILE, SPFILE)
  • Online Redo log file, Control file and multiplexing
  • Database archiving (ARCHIVELOG mode, archive destinations)
  • Tablespaces and datafile management (Create, resize, drop, rename)
  • Starting and shutdown modes (NOMOUNT, MOUNT, OPEN; NORMAL, TRANSACTIONAL, IMMEDIATE, ABORT)
  • Undo Management (Undo tablespace, retention, sizing)
  • Oracle Network Configuration (Listener, tnsnames.ora, TNSPING)

Unit 3: Security and User Management

Database security and auditing, authentication and authorization methods, managing user accounts in CDB and PDB, privilege management (system and object), role management, and profile management for password and resource control.

  • Database Security and Auditing (Standard, Fine-grained, Unified Auditing)
  • Database Authentication and Authorization Methods
  • Managing user accounts in CDB and PDB (Common and local users)
  • Privilege Management (System and object privileges)
  • Role management (Create, assign, modify, drop, predefined roles)
  • Profile Management (Password policies, resource limits)

Unit 4: Managing Database Objects

Tables, constraints, sequences, indexes, partitioning, views, materialized views, synonyms, PL/SQL fundamentals, cursors, records, collections, stored procedures, functions, triggers, and packages.

  • Tables, constraints, sequence and Index (DDL, DML, constraints, indexes)
  • Table Partitioning (Range, List, Hash, Composite)
  • View, Materialized view, Synonyms
  • Introduction of PLSQL, Exception Handling
  • Records, collections, Cursors (Implicit and Explicit)
  • Stored Procedure, Functions, Trigger, Package

Unit 5: Backup, Recovery and High Availability

Oracle backup solutions (RMAN, user-managed), backup terminology and strategies, RPO/RTO, types of failure, Oracle recovery process (crash, instance, media), RMAN backup and recovery, Data Pump export/import, and high availability features (RAC, Data Guard, Flashback).

  • Oracle Backup Solutions (RMAN, User Managed, Terminology)
  • Backup and Recovery Strategy (RPO, RTO, Retention)
  • Types of Failure (Statement, User Process, Network, User Error, Instance, Media)
  • Oracle Recovery Process (Crash/Instance Recovery, Complete/Incomplete Media Recovery)
  • RMAN backup and Recovery, Data Pump (Export/Import)
  • High Availability Features (RAC, Data Guard, Flashback)

Unit 6: Database Tuning

Performance tuning methodology, proactive vs reactive tuning, instance tuning, Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), SQL tuning, execution plans, and SQL Tuning Advisor.

  • Performance Tuning Methodology (Proactive vs Reactive, Instance Tuning)
  • Tuning Activities (Performance Planning, Application Design, Data Access, Network)
  • Automatic Workload Repository (AWR); Automatic Database Diagnostic Monitor (ADDM)
  • Tuning SQL; Execution Plans, SQL Tuning Advisor; Performance Tuning in Multitenant

Unit 7: Task Automation with the Scheduler

Oracle Scheduler architecture, components and workflow, creating and managing jobs, schedules, programs, time-based and event-based schedules, and the DBMS_SCHEDULER package.

  • Introduction to the Scheduler, Access Rights, Scheduler Components
  • DBMS_SCHEDULER workflow (Creating, scheduling, enabling, disabling, dropping jobs)
  • Time Based and Event-Based Schedules