Part-I: Introduction to SQL and Linux Fundamentals – 40 Hrs.

Managing Oracle on Linux
Important Linux Commands for an Oracle DBA
Installing Oracle Database on Linux Platform
Managing Oracle Database on Linux Platform

Retrieving Data Using theSQL SELECT Statement
List the capabilities of SQL SELECT statements
Execute a basic SELECT statement
Differentiate between SQL statements and iSQL*Plus commands

Restricting and Sorting Data
Limit the rows that are retrieved by a query
Sort the rows that are retrieved by a query
Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Using Single-Row Functions to Customize Output
Describe various types of functions that are available in SQL
Use character, number, and date functions in SELECT statements
Describe the use of conversion functions

Reporting Aggregated Data Using the Group Functions
Identify the available group functions
Describe the use of group functions
Group data by using the GROUP BY clause
Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables
Write SELECT statements to access data from more than one table using
equijoins and non-equijoins
Join a table to itself by using a self-join
View data that generally does not meet a join condition by using outer joins
Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries
Define subqueries
Describe the types of problems that subqueries can solve
List the types of subqueries
Write single-row and multiple-row subqueries

Using the Set Operators
Describe set operators
Use a set operator to combine multiple queries into a single query
Control the order of rows returned

Manipulating Data
Describe each data manipulation language (DML) statement
Insert rows into a table
Update rows in a table
Delete rows from a table
Control transactions

Using DDL Statements to Create and Manage Tables
Categorize the main database objects
Review the table structure
List the data types that are available for columns
Create a simple table
Understand how constraints are created at the time of table creation
Describe how schema objects work

Creating Other Schema Objects
Create simple and complex views
Retrieve data from views
Create, maintain, and use sequences
Create and maintain indexes
Create private and public synonyms

Managing Objects with Data Dictionary Views
Use the data dictionary views to research data on your objects
Query various data dictionary views

Part-II: Basic Databsase Administration – 35 Hrs.

Architecture
Outline the Oracle Architecture and its main Components
Explain the Oracle instance architecture

Installing the Oracle Database Software
Identify common database administrative tools available to a DBA
Use optimal flexible architecture
Install software with Oracle Universal Installer
Identify and configure commonly used environment variables
Use Installer Log

Creating an Oracle Database
Use DBCA to Create a database
Use DBCA to Delete a database
Use DBCA to manage templates

Managing the Oracle Instance
Use Enterprise Manager
Use SQL*Plus and iSQL*Plus to access the Oracle Database
Modify database initialization parameters
Describe the stages of database startup
Describe the database shutdown options
View the database alert log
Use dynamic performance views

Managing Database Storage Structures
Describe how table row data is stored in blocks
Define the purpose of tablespaces and data files
Explain space management in tablespaces
Create tablespaces
Manage tablespaces: alter, drop, take offline, put online, add data files, make read-only or read-write, generate DDL
Obtain tablespace information

Administering User Security
Create and manage database user accounts
Create and manage roles
Grant and revoke privileges
Create and manage profiles

Implementing Oracle Database Security
Apply the principle of least privilege
Audit database activity
Implement Fine-Grained Auditing

Managing Schema Objects
  Create and modify tables
  Define constraints and states of constraints
  Dropping and truncating tables
  Create and use B-Tree and Bitmap indexes
  Create Views
  Create sequences
  Use data dictionary

Managing Data and Concurrency
  INSERT Statements
  UPDATE Statements
  DELETE Statements
  COMMIT,ROLLBACK,SAVEPOINT Statements
  Define levels of locking
  List possible causes of lock conflict
  Monitor and resolve lock conflicts


Managing Undo Data
  Monitor and administer undo
  Configure undo retention
  Describe the relationship between undo and transactions
  Size the undo tablespace


Configuring the Oracle Network Environment
  Use Database Control to Create additional listeners
  Use Database Control to Create Oracle Net service aliases
  Control Oracle Net Listeners
  Identify when to use shared servers versus dedicated servers


Using Globalization Support Objectives
  Customize language-dependent behavior for the database and individual sessions
  Specify different linguistic sorts for queries
  Use datatime datatypes
  Query data using case insensitive and accent insensitive searches
  Obtain Globalization support configuration information


Part-III: Backup & Recovery – 25 Hrs


Backup and Recovery Concepts
  Describe the types of failure that may occur in an Oracle Database
  Identify the importance of checkpoints, redo log files, and archived log files
  Tuning instance recovery
  Configure a database for recoverability
  Configure ARCHIVELOG mode


Performing Database Backup
  Create consistent database backups
  Back up your database without shutting it down
  Create incremental backups
  Automate database backups

  Backup a control file to trace
  Monitor flash recovery area
 
Performing Database Recovery
  Recover from loss of a Control file
  Recover from loss of a Redo log file
  Recover from loss of a system-critical data file
  Recover from loss of a non system-critical data file


Performing Flashback
  Describe flashback database
  Resotore the table contents to a specific point in time
  Recover from a dropped table
  Use Flashback Query to view the contents of the database as of any single  point of time
  View transaction history or row with flashback transaction query
                                                                 
Recovering from User Errors
  Recover a dropped table using Flashback technology
  Perform Flashback table operation
  Manage the recycle bin
  Recover from user errors using Flashback versions query
  Perform transaction level recovery using Flashback Transaction query


Configuring Recovery Manager
  Configure database parameters that affect RMAN operations
  Change RMAN default settings with CONF    IGURE
  Manage RMAN's persistent settings
  Start RMAN utility and allocate channels


Using Recovery Manager
  Use the RMAN BACKUP command to create backup sets and image copies
  Enable block change tracking
  Manage the backups and image copies taken with RMAN with the LIST and REPORT commands


Recovering from Non-Critical Losses
  Recover temporary tablespaces
  Recover a redo log group member
  Recover index tablespaces
  Recover read-only tablespaces
  Recreate the password file

Dealing with Database Corruption
  Detect database corruptions using DBVERIFY
  Repair corruptions using RMAN

Moving Data
  Describe the general architecture of Data Pump
  Use Data Pump export and import to move data between Oracle databases
  Load data with SQL Loader
  Use external tables to move data


Part-IV: Performance Tuning – 20 Hrs.


Proactive Maintenance
  Gather optimizer statistics
  Manage the Automatic Workload Repository
  Use the Automatic Database Diagnostic Monitor (ADDM)
  Set warning and critical alert thresholds
  React to performance issues


Performance Management
  Use enterprise manager to view performance
  Tune SQL by using SQL tuning advisor
  Tune SQL by using SQL access advisor
  Use automatic shared memory management
   Use the memory advisor to size memory buffer


Automatic Database Management
  Use the Database Advisors to gather information about your database
  Use the SQL Tuning Advisor to improve database performance
  Use automatic undo retention tuning


Monitoring and Managing Storage
  Tune redo writing and archiving operations
  Issue statements that can be suspended upon encountering space condition errors
  Reduce space-related error conditions by proactively managing tablespace usage
  Reclaim wasted space from tables and indexes using the segment shrink  functionality
  Estimate the size of new table and indexes
  Use different storage options to improve the performance of queries
  Rebuild indexes online

Monitoring and Managing Memory
  Implement Automatic Shared Memory Management
  Manually configure SGA parameters for various memory components in the SGA
  Use Automatic PGA Memory Management


Automatic Storage Management (ASM)
  Set up initialization parameter files for ASM and database instances
  Execute SQL commands with ASM file names
  Start up and shut down ASM instances
  Administer ASM disk groups
  Use RMAN to migrate your database to ASM


Managing Resources
  Configure the Resource Manager
  Assign users to Resource Manager groups
  Create resource plans within groups
  Specify directives for allocating resources to consumer groups


Automating T asks with the Scheduler
  Simplify management tasks by using the Scheduler
  Create a job, program, schedule, and window
  Reuse Scheduler components for similar tasks
  View information about job executions and job instances