Online Live Training on: Oracle DBA

Intended Audience: This course is intended for those who want to work as a Database Administrator.

Oracle Database SQL Fundamental
10 classes

Introduction to Oracle SQL

• Discuss The Basic Design Of Relational Database
• Categorize the different types of SQL statements
• Log On to the database using SQL PLUS and SQL Developer

Retrieve Data using the SQL SELECT Statement

• Basic SQL SELECT Statement
• Select All Columns
• Select Specific Columns
• Using Column Heading Defaults
• Using Arithmetic Expressions
• Using Column Alias
• Using Literal Character Strings

Restricting and and Sorting Data

• Write queries that contain a WHERE clause to limit the output retrieved
• List the comparison operators and logical operators that are used in a WHERE clause
• Describe the rules of precedence for comparison and logical operators
• Use character string literals in the WHERE clause
• Write queries that contain an ORDER BY clause to sort the output of a SELECT
  statement
• Sort output in descending and ascending order

Usage of Single-Row Functions to Customize Output

• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC, and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the DATE functions

Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nesting functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

Aggregate Data Using the Group Functions

• Using the aggregation functions
• Divide the retrieved data in groups by using the GROUP BY clause
• Exclude groups of data by using the HAVING clause

Display Data From Multiple Tables Using Joins

• Write SELECT statements to access data from more than one table
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself by using a self join

Use Sub-queries to Solve Queries

• Describe the types of problem that sub-queries can solve
• Define sub-queries
• List the types of sub-queries
• Write single-row and multiple-row sub-queries

The SET Operators

• Describe the SET operators
• Use a SET operator to combine multiple queries into a single query
• Control the order of rows returned

Data Manipulation Statements

• Describe each DML statement
• Insert rows into a table
• Change rows in a table by the UPDATE statement
• Delete rows from a table with the DELETE statement
• Save and discard changes with the COMMIT and ROLLBACK statements
• Explain read consistency

Use of DDL Statements to Create and Manage Tables

• Categorize the main database objects
• Review the table structure
• List the data types available
• Create a simple table
• Add constrains
• Alter table statement
• Drop table statement

Other Schema Objects

• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Control User Access

• Differentiate system privileges from object privileges
• Create Users
• Grant System Privileges
• Create and Grant Privileges to a Role
• Change Your Password
• Grant Object Privileges
• Revoke Object Privileges

Management of Schema Objects

• Add, Modify, and Drop a Column
• Add, Drop, and Defer a Constraint
• How to enable and Disable a Constraint
• Create and Remove Indexes
• Create a Function-Based Index
• Perform Flashback Operations

Manage Objects with Data Dictionary Views

• Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Query the dictionary views for constraint information
• Query the dictionary views for view, sequence, index and synonym information
• Add a comment to a table
• Query the dictionary views for comment information

Manipulate Large Data Sets

• Use Subqueries to Manipulate Data
• Retrieve Data Using a Subquery as Source
• Use Multitable INSERT Statements
• Merge rows in a table

Retrieve Data Using Sub-queries

• Multiple-Column Subqueries
• Scalar Subquery Expressions
• Correlated Subqueries
• The EXISTS and NOT EXISTS operators
• Invoke the WITH clause

Regular Expression Support

• Use the Regular Expressions Functions and Conditions in SQL
• Perform a Basic Search using the REGEXP_LIKE function
• Find patterns using the REGEXP_INSTR function
• Extract Substrings using the REGEXP_SUBSTR function
• Replace Patterns Using the REGEXP_REPLACE function
• Implement the REGEXP_COUNT function

Oracle Database Administration Part-I
10 classes

Oracle Database Architecture

• Introduction To Oracle Database
• Oracle Database Memory Structure
• Oracle Database Process Structure
• Oracle Database Storage Architecture

Installing Oracle database software

• Install Oracle Database Binary GUI Mode
• Install Oracle Database Binary Silent Mode

Creating an Oracle Database Using DBCA

• Create Database Using DBCA GUI Mode
• Create Database Using DBCA Silent Mode
• Delete Database Using DBCA GUI Mode
• Delete Database Using DBCA Silent Mode

Managing Database instance

• Database Initialization Parameter Files
• Changing Initialization Parameter Values
• Database Startup Modes
• Database Shutdown Modes
• Viewing Alert Log
• Dynamic Performance View

Tablespace And Datafile Management

• Create Tablespace
• Rename Tablespace
• Add/Alter Data File
• Add/Alter Temp File
• Rename / Move Data File

User Management

• Create User in Oracle Database
• Alter User Password
• Change User Default Tablespace
• Tablespace Quota for Users
• Define Privilege for Users
• Create Profile for Users
• Alter Profile for Users

Data Concurrency and Undo Management

• Locking Mechanism And Data Concurrency
• DML Locks
• Detecting Locks
• Resolving Lock
• Deadlocks
• Managing Undo Data
• Configuring Undo Retention
• Guaranteeing Undo Retention
• Undo Advisor

Performance Management

• Performance Monitoring – Top Session
• Enabling Automatic Memory Management
• Enabling Automatic Shared Memory Management
• Memory Advisor
• Invalid And Unused Objects

Backup and recovery concept

• Categories of Failure
• Understanding of Instance Recovery
• Configure the Fast Recovery Area
• Multiplexing Control Files
• Redo Log Files and Multiplexing
• Archive Log Files and Archiver Process
• Enabling Archive log Mode

Moving Data Using Data Pump Utility

• Export Schema Using Data Pump
• Import Schema Using Data Pump
• Export Table Using Data Pump
• Import Table Using Data Pump
• Compression Parameter in Data Pump
• Parallel Option in Data Pump
• Query Clause in Data Pump
• Table Exist Action Option In Data Pump
• Par File in Data Pump
• Exclude /Include In Data Pump
• Sqlfile Option In Data pump

Multitenant Architecture

• Oracle Multitenant Overview
• Create Container Database
• Create Pluggable Database
• Create Pdb From PDB$SEED
• Clone Pdb From Another PDB
• Plugging And Unplugging PDB
• Connection To PDB

Oracle Database Administration Part-II
10 classes

Introduction to Backup and Recovery

• Purpose of Backup and Recovery
• Oracle Backup and Recovery Solutions
• Type of RMAN Commands
• Using a Fast Recovery area
• Fast Recovery Area Space Management
• Retention Policy

<Class-02,03 >
Performing Backup with RMAN

• Performing Full Database backup
• Performing Database Image Copies
• Performing Database Incremental backup
• Performing Tablespace Backup
• Performing Data file Backup
• Performing Archive log Backup
• Monitoring RMAN Backup
• Cross Check and Deleting Backup
• Improving Backups

Configuring Backup Setting

• Control File Auto backup
• Specifying Backup Destination
• Configuring and Allocation Channels
• Compressing Backups
• Rman Encrypted Backup

<Class-05,06>
Rman Restore and Recovery

• Restore and Recover Full Database
• Point-in-Time Database Recovery
• Point-in-Time Tablespace Recovery
• Point-in-Time Table Recovery
• Recovery Loss of server Parameter File
• Restore control file from auto backup
• Re-creation Password File
• Recover loss of redo log group
• Recovery from the image copy
• Performing to switch the image copy
• Restore and recover database on a new host

Tuning and Diagnosing

• Tuning RMAN Channel
• Tuning Backup Command
• Block Corruption
• List Failure
• Advise Failure
• Repair Failure

Duplicate database Using RMAN

• Database duplication Techniques
• Duplicating an Active Database with a target Connection
• Duplicating an Active Database without a target Connection

Oracle Flashback Technology

• Enabling Oracle Flashback
• Flashback Retention policy
• Flashback Retention guarantee
• Flashback Table
• Flashback Table to Before Drop
• Flashback Entire Database

<Class-09,10>
Using Rman in Multitenant Database

• Container Database Backup
• Root Container Backup
• Pluggable Database Backup
• Tablespace and Data file Backup
• Archive log Backup
• Container Database Complete Recovery
• Root Container Complete Recovery
• Pluggable Database Complete Recovery
• Tablespace and Data file Complete Recovery
• Container Database Point-in-Time Recovery
• Pluggable database Point-in-Time recovery