Home Training Special Training Packages -->> Database Administrator

 

Database Administrator

 

Entrance Requirements: 3 months working experience with MS Windows or Unix or 3 college credit in OS, Database or programming fundamentalI. Oracle DBA

 

Occupational Objectives: Database Administrator (DBA), Database Analyst, Database Coordinator

 

Part 1:  Oracle introduction

·         Write efficient SQL statement

·         Restricting and sorting data

·         Single row, multiple row and group functions

·         Display data from multiple table using join function.

·         Subquery and output with SQLPLUS

·         Manipulating data

·         Create database objects such as table, index, constraint, and view.

·         Controlling user access

·         Oracle 10g/11g date time and set operation.

·         Advance subquery and hierarchical retrieval

·         Extensions and DML and DDL statement

  • Introduction PL/SQL

 

 

Part 3: Oracle database administration

          Workshop II

 

·         Basic oracle network architecture

·         Oracle net server-side configuration

·         Oracle net client-side configuration

·         Usage and configure of Oracle shared server

·         Instance and media recovery structure

·         Configuring DB archive mode

·         Oracle recovery management overview and configure.

·         User managed backup

·         RMAN backup

·         User managed/RMAN completed recovery

·         User managed/RMAN incomplete recovery

·         RMAN recovery catalog creation and maintenance.

·         Transporting data between databases.

·         Loading data into database

Part 2: Oracle database administration Workshop I  

·         Oracle architectural components

·         Operation for database server startup and shutdown.

·         Managing oracle instance and create database

·         Data dictionary contents and usage

·         Maintaining the control file, redo log file, data file and tablespace

·         Storage structure and relationship in system management and local management.

·         Managing undo data and temp data

·         Managing table, indexes, constraints, password security and resources

·         Managing user, privilege and role.

·         Globalization Support.

 

Part 4: Performance tuning Oracle

                database  

 

·         Performance tuning step and tools

·         Database configuration and I/O issues

·         Sizing share pool, buffer cache and other SGA structure

·         Tuning oracle shared server (MTS)

·         Tuning undo segments

·         Tuning SQL statement and application tuning

·         Managing statistics

·         Using materialized views

·         Monitoring and detecting lock contention

·         Using source manage and tuning OS

·         Compare Oracle 10/11g vs MSSQL-2008

 

 

 

II. Advanced Oracle DBA

Module 1:  Oracle installation and migration

  • Unix basic command
  • Oracle Environnant Variable Set-up
  • Install Oracle in Sun-Solaris
  • Applying patches to Oracle
  • Migration oracle database from 9i to 10g
  • Downgrade oracle database from 10g to 9i
  • Setup daily oracle croon job for the backup & other maintenance.

 

 Module2: Clone database & distribute data

  • Change database name
  • Copy database from one server to others using cold / hot backup.
  • Distributed data using oracle tool (snapshot, blink, copy and so on)
  • Oracle Replication / Standby database setup

 

Module3:  Tuning and implement Oracle

  • Capacity planning
  • Physical design RAID (Disk Manager)
  • Tuning I/O & memory
  • Advance SQL tuning

 

Module4:  Database new features.

  • Data transports by tablespaces
  • Choose different block size within singe database
  • Partition table and partition index.The Pros & Cons of Partition
  • Other new features

 

Module 5:  Monitoring of Oracle

  • Oracle daily monitoring ( DDL ,DML change ) using database trigger.
  • Reversing DDL (Undo the changing) using Log Miner
  • Plan for an Effective Load Test & Benchmarking
  • Oracle Data Warehouse
  • Oracle DBA interview tips & techniques with useful administration scripts

 

Module 6-7  10g Grid & Real Application Clusters

  • Introduction to RAC
  • High Performance and Highly Available Databases
  • Database Clusters
  • Real Application Cluster Architecture
  • RAC Server and Disk Technology
  • RAC Installation and Configuration
  • RAC Administration
  • RAC  Backup and Recovery
  • RAC Performance Monitoring and Tuning
  • RAC Parallel Execution
  • RAC Design Consideration
  • Migration to RAC Databases
  • Using RAC Cluster Guard

 

III. MS SQL Server 2008 Administration

Part I  Design and Implement Databases with SQL Server 2005/2008

Chapter 1

"Introduction to Microsoft SQL Server 2005/2008," introduces you to SQL Server and explains what SQL Server is. The chapter provides a cohesive overview of SQL Server so that you can understand how all the pieces fit together.

Chapter 2

 "Using Transact-SQL on a SQL Server Database," introduces you to Transact-SQL and provides details about how to create and execute Transact-SQL statements in order to manage a SQL Server database and its data. The chapter also introduces you to the SQL Server programming tools that allow you to use Transact-SQL to interface with the database.

Chapter 3

"Designing a SQL Server Database," introduces you to the process of creating a SQL Server database. It describes the basic concepts of database design and provides information about planning a database, identifying system requirements, and developing a logical data model.

Chapter 4

"Implementing SQL Server Databases and Tables," explains how to create and manage a SQL Server database. It then discusses data types and how to identify which ones to use when creating a table. The chapter also describes how to create these tables—using the data type information—and how to modify the tables after they have been created.

-Chapter 5

 "Implementing Data Integrity," provides an overview of the various methods that you can use to maintain data integrity and a description of the types of data integrity that you will find in a SQL Server database. The chapter also provides detailed information about the various types of integrity constraints that you can use to enforce data integrity and how to implement them in a database.

-Chapter 6

 "Accessing and Modifying Data," provides detailed information about four Transact-SQL statements (SELECT, INSERT, UPDATE, and DELETE) and describes how each statement is used in Query Analyzer to retrieve and modify data. This chapter also introduces you to other methods for adding, modifying, and deleting data.

Chapter 7

"Managing and Manipulating Data," describes more techniques for managing and manipulating data, including how to import and export data, how to manipulate heterogeneous data, how to use Transact-SQL cursors, and how to extract data in XML format.

 Chapter 8

"Implementing Stored Procedures," introduces you to the types of stored procedures available in SQL Server 2005 and how to create, execute, and alter them. You are also introduced to programming stored procedures.

- Chapter 9

"Implementing Triggers," introduces you to triggers and how to use them to extend data integrity and implement complex business logic. You will learn when it is appropriate to implement triggers and when basic constraints will suffice. You will also learn how to program triggers and how to use system commands and functions commonly used in trigger programming.

-Chapter 10

 "Implementing Views," introduces you to views and the various functionality that they support. You will learn how to use views to insert, update, and modify data.

-   Chapter 11

 "Implementing Indexes," introduces you to the structure and purpose of indexes and the types and characteristics of indexes. You will learn how to determine when an index is appropriate, the type of index to create, and how to create it.

Chapter 12

 "Managing SQL Server Transactions and Locks," introduces you to the fundamentals of transactions and locks and describes how transactions and locks are used to process data modifications.

Chapter 13

"Designing and Administering SQL Server 2005 Security," introduces you to SQL Server security. You will learn how to design SQL Server security to accommodate user requirements and protect the database from unauthorized access.

-Chapter 14

 "SQL Server Monitoring and Tuning," examines how to use SQL Profiler to monitor a database system and explores methods of improving database performance through partitioning and index tuning

 

Part II MS SQL Server 2008 Administration

-Module 1   “Overview of SQL Server 2005/2008” introduces SQL Server 2005/2008. It defines some of the important characteristics of SQL Server 2005/2008and explains the environments in which it is designed to work. You will be intro­duced to the different parts of the product and given some idea as to the role played by these parts.

-Module 2   “Installing SQL Server 2005/2008” explains how to install SQL Server 2005/2008. It lists the hardware and software requirements of the program, and it explains the specific information you have to supply and the decisions you have to make during the installation process. This Module covers using default, named, and multiple instances of SQL Server 2005/2008.

-Module 3   “Preparing to Use SQL Server 2005/2008” reviews the results of install­ing SQL Server 2005/2008. This Module explains starting, stopping, and pausing SQL Server services. The Module also covers working with OSQL, SQL Query Analyzer, and SQL Server Enterprise Manager.

Module 4   “Upgrading to SQL Server 2005/2008” shows you how to perform a ver­sion upgrade of a complete SQL Server 6.5 or 7.0 installation to SQL Server 2005/2008. This Module also explains how to perform an online database upgrade from SQL Server 7.0.

-Module 5   “Understanding System and User Databases” explains SQL Server 2005/2008databases. This includes the structure of a data file and the architecture of the transaction log. This Module concludes with a discussion about system tables, including the querying of system and database catalogs.

Module 6   “Creating and Configuring User Databases” teaches how to create a new user database, set database options for new or existing databases, and manage the size of data and transaction log files. This Module concludes with a discussion about placing database files on multiple disks for recovery, fault tol­erance, and performance.

Module 7   “Populating a Database” discusses the population of user databases with existing data. It shows you how to transfer and transform data. This Module focuses on the use of DTS, Bcp, and the BULK INSERT Transact-SQL statement.

Module 8   “Developing a Data Restoration Strategy” presents an overview of the SQL Server 2005/2008backup and restore processes and discusses the issues that you should consider when planning a backup and restore strategy for a SQL Server 2005/2008installation.

Module 9   “Backing Up and Restoring SQL Server” teaches you how to per­form database backups using disk and tape. It also teaches you how to perform database restorations. This Module explains how to use SQL Server Enterprise Manager and Transact-SQL statements.

Module 10 “Managing Access to SQL Server 2005/2008” discusses controlling access to SQL Server 2005/2008, including the authentication process and the autho­rization process. The Module concludes by showing you how to create and man­age logins.

Module 11 “Managing SQL Server Permissions” shows how to grant data-base-specific permissions. It also discusses the use of application roles. The Module concludes by teaching you how to design an access and permissions strategy.

Module 12 “Performing Administrative Tasks” teach you how to perform a variety of configuration tasks and setup tasks. These include the configuration of the SQL Server service, the SQL Server Agent service, SQL Mail, SQLAgentMail, and XML. The Module concludes with a discussion of the per­formance of periodic maintenance tasks.

Module 13 “Automating Administrative Tasks” shows you how to automate rou­tine tasks using operators, jobs, and alerts. It also teaches the creation of a data­base maintenance plan. It concludes by teaching you to create multiserver jobs.

Module 14 “Monitoring SQL Server Performance and Activity” discusses the development of a performance monitoring methodology. This Module intro­duces each of the monitoring tools and shows you how to use each tool. The Module concludes by teaching you how to perform specific monitoring tasks, such as for memory use or slow-performing queries.

Module 15 “SQL Server Reporting Services (SSRS) & SQL Server Analysis Services (SSAS)

      SSRS: A complete set of tools that you can use to create, manage, and view reports.

      SSAS: Advanced data analysis and interactive reporting &could find many uses for the predictive power of data mining.

Module 16 “Maintaining High Availability” discusses the use of standby serv­ers, including the use of log shipping to automate the process of maintaining a standby server.