Tutors: Dr.
S.Pahirathan BSc (Hons), MSc, PhD
Mr. Arul Dip, CIW, MBCS
Duration: 1
Year (Full Time)
Fees:
£2000.00
This is a one-year course, which provides a career entry
to work using Information Technology in the field of Database Oracle 10g or SQL
Server 2005. During I year of study time students are prepared
for practical work in computing and information processing using live projects.
Entry Requirements
No previous knowledge of Computing is necessary, but the
students must have passed at least two subjects in GCE (A/L) examination or
similar. Any computing experience would
be advantageous.
Course Outcome
Those who completed this course will be able sit for
Oracle OCP and Microsoft SQL Server examinations
Course
Contents
Introduction
to Database Management System
Database
Database Management System
Logical Database Design
Relational Operators
Physical database Design
Databases & Data files
Database Objects (Tables,
Views, Indexes, Synonyms, Grants, Roles)
Oracle Components
Structured
Query Language (SQL)
SQL Statements: CREATE TABLE, INSERT,
SELECT, UPDATE etc.
SQL *Plus
Data Types
Union
& Join
Queries & Sub queries
SQL Built-In Functions
Programming
an Oracle Database with PL/SQL.
PL/SQL Character Set
Variables
Components of PL/SQL
Control Structures: IF, LOOP, EXIT, FOR-LOOP,WHILE-LOOP, NULL, EXCEPTION
Error handling and Cursors in PL/SQL
Working with Cursors
Program Units: Procedures, Functions, and Packages
Stored procedures and Java Stored Procedures
Integrating XML data with Oracle Database
PL/SQL Tables
User-defined Records
Creating Triggers
Coding & Compilation
Oracle
Developer/2000 Forms 6i
What is Developer/2000
Oracle Form designer
Elements of form designer:
Forms, Menus & Libraries
Oracle Forms
Elements of forms: Object
Navigator, Blocks, Canvas-Views, Windows, Triggers, record Groups, LOVs,
Alerts, Parameters
Building and running
Master-Detail forms
Building Menus and
Multiform Applications
Default Block Facility
Screen Formatting
Oracle Forms Runtime
Elements of Oracle
Graphics
Building Charts
Elements of Procedure
Builder
Oracle
Developer/2000 Reports 6i
Oracle Report Designer
Elements of report: Data
Model, Layout, Parameter Form
Oracle Report Program,
Building Tabular Report
Creating a data Model
Building a Master-Detail
Report
Generating a Runtime
Version of reports
Creating a shortcut to use
a report
Invoking a report from an
Oracle form
OVERVIEW OF ORACLE 10g ADMINISTRATION
Your
responsibilities as an Oracle DBA
Configuring
Oracle 10g databases
Controlling
user access and operations
Monitoring
system usage
Ensuring
database availability
The Oracle
10g architecture
Instances
vs. databases
Oracle
processes and memory usage
Determining
database file structure
Processing
transactions with Oracle 10g
CONSTRUCTING AN ORACLE 10g DATABASE
Creating
and dropping the database
Setting the
initialization parameters
Dynamic and
static parameters
Creating
control files and log files
Reducing
administration with Oracle managed files
Defining
tablespace structure
Supporting
applications that require multiple block sizes
Creating
UNDO and SYSAUX tablespaces
Starting
and stopping the database
Mounting
and opening the database
Text-based
and server parameter files
Connecting
with SYSDBA privilege
PERFORMING SPACE MANAGEMENT
Logical and
physical storage structures
Improving
sort performance with temporary tablespace groups
Bigfile and
locally managed tablespaces
Space
management with blocks & extents
Controlling
storage for database objects
Managing
space with PCTFREE/PCTUSED
Moving and
redefining tables online
Shrinking
tables online to regain space
Resuming
operations after space-related failures
Managing
LOB storage with chunks
AUTOMATING DATABASE MANAGEMENT WITH ORACLE ENTERPRISE MANAGER
(OEM) 10g DATABASE CONTROL
The OEM
architecture
Strengths
and weaknesses of OEM
Navigating
the graphical interface
Administering
with Database Control
Setting
thresholds and generating alerts
Verifying
changes in the data dictionary
MANAGING USERS AND RESOURCES
Creating
user accounts
Implementing
password controls
Allocating space
quotas on tablespaces
Limiting
resource usage with profiles
Maintaining
security
System and
object privileges
Setting up
and working with roles
ENHANCING AVAILABILITY AND ADMINISTRATION WITH PARTITIONS
Building
table partitions and subpartitions
Partitioning
types: range, hash and list
Defining
partition key values
Tailoring
storage for individual partitions
Splitting
and merging partitions
Configuring
index partitions
Deploying
local, global, prefixed and non-prefixed indexes
Rebuilding
unusable indexes
BACKING UP AND RECOVERING THE DATABASE
Safeguarding
the database
Causes of
failure
Role of the
redo log and control file
Ensuring
recovery using archiving
Protecting
and tracing the control file
Selecting a
backup strategy
Implementing
hot and cold backups
Partial
online and offline backups
Restoring
tables with Data Pump
Performing
Oracle 10g recovery
Full
database recovery
Recovering
corrupted tablespaces
Tracking
row history with flashback
Restoring
dropped tables from the recycle bin
FUNDAMENTAL TUNING CONCEPTS
Sizing the
buffer cache
Balancing
disk I/O & memory allocation
Shared pool
sizing
Index
storage considerations
THE ORACLE 10g ARCHITECTURE
Instances
and databases
The System
Global Area (SGA) and background processes
Automated
storage configurations
Optimal
flexible architecture
IMPLEMENTING BACKUP AND RECOVERY STRATEGIES
Protecting
the database
Choosing
appropriate backup strategies
Identifying
points of possible failure
Safeguarding
redo logs and control files
Managing
archive logs
Performing
backups
Generating
full database backups
Implementing
partial online backups
Backing up
and creating control files
Transporting
tablespaces
Establishing
a consistent dataset
Migrating
across hardware platforms
Achieving
full database recovery
Recovering
tablespaces and datafiles
Reconstructing
datafiles without backups
Repairing
online redo logs
Flashing
back the database
DISASTER RECOVERY USING HOT STANDBY DATABASES
Configuring
the standby environment
Creating
and synchronizing the standby database
Maintaining
a read-only standby database
Preserving
the performance of the primary database
Physical
vs. logical standby databases
Moving
operations to the standby
Failing
over and switching to the standby facility
Achieving
No-Data-Loss recovery
AUTOMATING BACKUP AND RECOVERY WITH RECOVERY MANAGER (RMAN)
Setting up
Oracle Recovery Manager
Creating
the Recovery Catalog
Registering
databases for recovery
Configuring
channels and redundancy
Recovery
Manager backups
Full and
incremental backups
Scripting
the backup activity
Maintaining
the catalog
Listing and
reporting on backups
Performing
automated recovery
Restoring
and recovering data from backup sets and image copies
Monitoring
the recovery process
Rolling
forward image copies with incremental backups
Tuning
backup processes with block change tracking
ANALYZING REDO LOG FILES WITH LOGMINER
Configuring
LogMiner
Creating
the LogMiner dictionary
Manually listing
log files to be analyzed
Automatically
choosing log files using continuous mining
Adding
supplemental log data
Interpreting
LogMiner information
Auditing
changes to specific columns
Performing
fine-grained recovery
TUNING THE ORACLE 10g SERVER
Tuning the
SGA
Maximizing
the use of the shared pool
Tuning the
buffer cache with the Buffer Cache Advisor
Reducing
I/O with multiple buffer pools
Monitoring
latch contention
Configuring
Startup with database level triggers
Automating
memory management
Managing
Program Global Area (PGA)
Enabling
dynamic memory allocation
Optimizing
transactions and queries
Determining
block contention
Delayed
block cleanout
Compressing
table data
Monitoring
latch contention and waits
APPLYING DIAGNOSTIC TECHNIQUES
Managing
the workload repository
Identifying
contention with the Automatic Database Diagnostic Monitor (ADDM)
Examining
the alert log
SQL SERVER 2000
INSTALLATION
System
requirements
Choosing
installation options
Managing multiple
instances
Installing on a
cluster
Applying a
service pack
Transact-SQL
Retrieving
data with Transact-SQL
Adding,
Changing and Deleting Information in Tables
Indexes
and Keys
Transactions
and Locking
Rules,
Constraints and Defaults
Stored
Procedures
Flow-Control
Statements
UPGRADING PREVIOUS
VERSIONS
Preparing for the
upgrade
Migrating from
6.5 and 7.0
SETTING UP CLIENTS
Choosing network
libraries
ODBC
OLE DB
Resolving
connectivity problems
Connecting to
named instances
Storage Management
DISK STRUCTURES
Defining files
Creating
databases and transaction logs
SPACE MANAGEMENT
STRATEGIES
Managing dynamic
database growth
Reclaiming unused
space
Backup and Recovery
Transaction log
architecture
Choosing a
recovery model
Performing full,
log and differential backups
Recovering system
and user databases
Scheduling Jobs and Alerts
THE SQL SERVER AGENT
Configuring the
Agent
Setting up SQL
Mail
MULTISTEP JOBS
Defining jobs to
handle routine tasks
Creating alerts
and operators
Associating
alerts with jobs
Managing Security
LOGIN SECURITY
NT authentication
vs. SQL Server authentication
Creating logins
Working with
server roles
Establishing a
secure SQL Server
DATABASE SECURITY
Adding users
Defining new
roles
Taking advantage
of pre-defined roles
Assigning users
to roles
OBJECT SECURITY
Ownership
Granting
permissions
How roles and
permissions interact
MONITORING SQL SERVER
Resolving
contention problems
Studying server
activity with SQL Profiler
Implementing Replication
REPLICATION CONCEPTS
Publish/subscribe
metaphor
Replication types
Replication
agents
MANAGING
TRANSACTIONAL REPLICATION
Configuring the
publisher
Creating
publications
Setting up
subscribers
Converting and Migrating Data
DATA TRANSFORMATION
SERVICES (DTS)
Importing data
using the GUI
Transforming data
with simple scripts
COLLECTING DATA FROM
MULTIPLE SOURCES
Configuring
linked servers
Issuing
distributed queries
DEVELOPING
APPLICATION WITH MS SQL SERVER
Developing
Application to work with SQL Server
Understanding
SQL Server and the Internet
Accessing
SQL Server Databases through Front-End Products
Upsizing
Microsoft Office 97 Applications to SQL Server
The
course is conducted for one years on weekdays minimum 15 hours per week. 50%
time will be spent on theory classes and the rest will be practical sessions.