Oracle DBA

Oracle DBA Course Overview

Become a certified database developer by enrolling in SVTechhub Oracle DBA training. Our experienced trainers will help you in learning all fundamental concepts with ease and help you in becoming a database professional. Oracle DBA Course curriculum at SVTechhub is carefully crafted by Industry experts. Enroll now and get certified in the Oracle DBA online training.

About Oracle DBA Course

Oracle database is a proprietary multi-model database created by Oracle Corporation. This database is commonly used for online transaction processing, handles mixed database workloads, and data warehousing. The latest Oracle Database is available on-premises and cloud as well. The database administration (DBAs) use special software for storing and organizing data. The Oracle Database Administrator required to plan, install, configure, database design, troubleshooting, performance monitoring, security as well as backup and recovery. Oracle DBA is solving the data storage needs of companies across the world with its innovative and easy to use features.

Oracle DBA Course Objectives

Q) What are the objectives of Oracle DBA online training?

In our Svtechhub’s Oracle DBA training, you will learn about:

• Learn Oracle Database Architecture
• Learn how to install Oracle database/client binaries
• Query data using SQL and perform DDL, DML and DCL operations
• Configure backups and perform the recovery using the recovery manager
• Work with Oracle database parameters
• Managing Users/Schemas, and profiles
• Learn to work with RDBMS to store and retrieve data

Q) Why should you learn Oracle DBA to grow your career?

• Oracle holds the major share in the relational database segment with 48.35% market share according to the reports of Gartner.
• Increased demand for the relational database made demand for skilled database administrators.
• The average salary of a Database administrator is around $ 112,650 per year.

Q) What will you learn in this Oracle DBA training?

Upon the completion of this course, you will become an expert in the below concepts.

• SQL and PL/SQL concepts
• Core database concepts
• Different structures in Database
• DBA Functionalities
• Configuring & managing oracle network
• Best practices of Database
• Database configuration, and management
• Installation and leveraging cloud storage management

BASIC UNIX COMMANDS:

Getting started to UNIX:

• Different versions and flavours of Unix operating system with their features
• Comparison between windows & Unix

Unix Structures:

• Operating system
• File system
• Unix directories files and inodes

Shell:

• types of shell's in unix
• features of shell

Basic Unix Commands:

• Creation and Deletion of Files with different options
• Working with Directories
• Making/Deleting /Renaming of Directories
• Copying Files & Directories
• Special Directory Names

Listing of Files & Directories:

• Searching of files & Directories using find command
• Wild card characters
• Investigation of the system
• Files access permission'absolute/numeric method
• Symbolic method (both at file level and directory level)

VI-Editor:

• Introduction
• Different modes in Vi(command mode, insert mode,escape mode)
• Vi command reference

Linking Files:

• Soft link
• Hard link
• Removing a link

Creating/Deleting of user accounts:

• Contents of user account
• Password Tips
• Change Password

Types of Filters:

• Simple Filters(more,less,head,tail,sort,paste,wc,tr,diff,cut,comm.)
• Adv. Filters(sed,grep,find & awk)

Process scheduling:

• System process
• Killing the process
• Background process
• Foreground process

Communication Options(write,talk,mesg, wall & mall)

Other usefull commands

Evolution of RDBMS

DB Computing Architecture

Data Definition Language(DDL)

• Create
• Alter
• Drop
• Recreate
• rename

Manipulating Data using DML:

• Insert
• Delete
• Update
• Merge

Transaction Control Language(TCL)

• Commit
• Rollback
• Save point

Data Control Language(DCL):

• Grant
• Revoke

Writing basic SQL select statements:

• Select all columns
• Select specified columns
• Columns alias names
• Column concadination
• Using wherer
• Order by arc
• Order by disc
• Nulls first
• Nulls last
• Distinct clauses
• Describing the SQL select capabilities

Operators

• Arthematical Operators *,-,+,?
• Logical AND, OR & NOT
• Relational <,>,<2,>2,!

Special Operators:

• IN
• NOT IN
• LIKE
• BETWEEN
• NOT BETWEEN
• IS NILL
• IS NOT NILL

FUNCTIONS

• SINGLE ROW FUNCTION
• Character
• Numeric
• Date
• Data conversion
• Multiple row function
• Aggregative function

Special Function

Aggregating Data using Group Functions

• Group by clauses
• Having clauses
• Cube
• Rollup

Subqueries

• Single-Row
• Multiple-Row

Formatting output using

• Sqlplus
• Column format
• Set command
• Spool
• Sqlplus commands(save, insert,append & change)

ORACLE 11g R2 DBA

Level – 1

ORACLE ARCHITECTURAL COMPONENTS:

DATABASE COMPONENTS:

• Storage Device
• Physical structure
• Logical structure
• Memory Structure
• Program Global Area (PGA)
• System Global Area (SGA)
• SQL Statement Execution
• User Session
• Process Structure
• Client Process
• Oracle Server Process
• Server Process
• Background Process
• Understanding about Database instance

INSTALLATION OF ORACLE 11G:

• HARDWARE REQUIREMENTS FOR INSTALLATION
• INSTALLATION OF Oracle 11g on REDHAT LINUX MACHINE

ORACLE 11g ARCHITECTURE:

• Understanding about Oracle database components with Update Statements
• Understanding about all Background process while updating a transaction
• Log Switch Mechanism

CREATING A DATABASE:

• Creating a command line Database with pfile
• Creating a DBCA database with spfile
• Creation of OMF Database
• Creation of ASM Database

MANAGING PFILE & SPFILE:

• Understanding and Modification of PFILE (INIT.ORA)
• Creating & Managing pfile & SPfile
• Difference & adv. Of Pfile & SPFile
• Creation of spfile from pfile

DATABASE STARTUP & SHUTDOWN PROCEDURES:

• STARTUP Nomount, Mount, Mount Restrict Open, Force, Upgrade & Migrade
• Shutdown Normal immediate, Transactional and Abort

DATA DICTIONARY STATIC & DYNAMIC TABLES / VIEWS (VS & DBA)

UNDERSTANDING & MANAGING TABLESPACE AND DATAFILES:

• Types of tablespaces
• Permanent, Temporary & Undo
• Create, Alter and Drop tablespace
• Offline and Online tablespace
• Read write & Read only mode Tablespace
• Resizing a Datafile Manually
• Adding a Datafile in to the existing Tablespace
• Deleting a Datafile
• Creation of Bigfile Tablespace & Default Tablepsace
• How to move a datafile
• How to Renaming a Datafile & Tablepsace
• How to reuse a datafile drop a tablespace
• Managing Autoextend ON and OFF

STORAGE MANAGEMENT:

• Types of Tables
• Dictionary Managed
• Locally Managed (Autoallocate, Uniform)
• Segment Management
• Extent Management

MANAGING SYSAUX TABLESPACE

CREATION OF TEMPORARY TABLESPACES & GROUPS

MANAGING DATABASE USERS WITH PRIVLEGES & ROLES:

• Creating a new user
• Alter and drop existing database users
• Obtaining user information'
• Lock and unlock user accounts
• System privileges
• Granting system privileges
• Revoking system privileges
• Object privileges advantages of Roles
• Creating the roles
• Assigning Roles to users
• Establishing Default Roles
• Removing Roles from users

MANAGING ONLINE REDOLOG FILES AND ARCHIVE MODE:

• Creation of lof members and groups
• Dropping log members and groups
• How to switch log sequence manually
• Purpose of checkpoints

ARCHIVE LOG MANAGEMENT:

• Changing the database from no archive mode to archive mode
• Config. Of multiple archive destinations
• Archive files format & destinations multiple archive process

MANAGING CONTROL FILES:

• Control files contents
• Multiplexing the control files by using PFILE (INIT.ora)

MANAGING UNDO SEGEMENTS & TABLESPACE:

• Creating and maintaining Rollback
• Segemnets & undo segments (online & offline)
• Types of undo segments
• a)system b) Non-system
• Automatic undo management
• Understanding Retetion time policy
• Undo tablespace with Guarantee and Non guarantee
• Deferred roleback segment & pending offline undo segment

FLASHBACK TECHNOLOGIES :

Flashback Query:

• We can recover lost records which done by DML statement But not DDL

Flashback version query:

Flashback versions Query providesa way to audit the rows of a table & retrieve information about the transactions that changed the rows

Flashback Transaction Query:

The flashback Transaction Query is a diagnostic tool to view the changes made to the database at the transaction level.

Flashback Recyclebin:

A table can recover from recyclebin

Flashback Database:

This feature useful to recover droped along with tables & records from flashback directory

BACKUP AND RECOVERY

PHYSICAL BACKUPS:

• Online,offline, hot & cold backups
• Backups, Restore and Recovery scenario's with complete, incomplete, instance and media recovery
• Before you create an oracle database,decide how to protect the database,against potential media failures
• If you do not develop a backup strategy before creating your database, then you may not be able to perform recovery if a disk failure
• damages the datafiles, online redo log

files, or control files

• A database can become unusable because of hardware or software failures or both.
• You may at one time or another, encounter storage problems, power interruptions or applications failures
• Each failures scenario requires a different recovery action
• Protect your data against the possibility of loss by having well recovery strategies.

CASE STUDIES:

• Recovery loss of non system data file
• Recovery loss of system data file
• Recovery loss of undo datafile
• Recovery loss of online redlog files
• Recovery loss of control files by using backup control files
• Recovery loss of datafile without having backup
• Recovery damaged database while online backup

LOGICAL BACKUPS"

Traditional Backups

• Full database level
• User level
• Table level
• Incremental, cumulative & complete backups by using exp & imp tools

ORACLE DATA PUMP:

• Introduction to data pump
• Advantages of data pump
• Logical backups using expdp & impdp tools

TRANSPORTABLE TABLESPACES:

• Moving a tablespace from one database instance to another database instance

CONFIG.OF ORACLE NET PROTOCOL FOR CLIENTS/SERVER & DISTRIBUTED DATABASE ENVIRONMENT:

• Simple network
• Configuration oracle net protocol
• Configuration of TNSNAMES.ORA at clent machine by using NETCA Tool
• Configuration of LISTENER.ORA at server by using NETCA Tool

CONFIGURATION OF ORACLE 11g SITE REPLICATION:

• Complex network
• Creation of database links & materialzed views for distributed databases

CONFIG. OF DISPATCHERS & SHARED SERVER PROCESS (MTS):

CONFIGURATION OF RMAN :

• Configuration of RMAN Tool for remote backups
• Maintaining the database backups using commands and scripts
• Recovery loss of datafiles, Redolog files and control files by using RMAN
• Repository, channels allocation, complete & incomplete recovery
• Compressed backups, incremental backups with block change tracking

ADV.RMAN ENHANCEMENTS:

• Understanding & cinfigutayion of virtual private catalogs
• Data recovery advisor
• Listing the failures
• Determining repair options
• Repairing failures
• Block level recovery
• Parallel backup of same datafiles
• Duplicate DB or DB Refresh(cloning)

CLOINING DATABASE:

• In every oracle development and production environment there is a nedd to transport the entire database from one physical machine to another. This copy may be used for production testing etc.,

ACTIVE STANDBY DATA GARD:

• Providing high data availability and maximum security and performance
• Configuration of Fail over and switch over mechanisms
• Oracle active data guard enables read-only access to a physical standby database for queries, sorting,reporting and web-based access.
• Understanding & config. Of snapshot standby

DATABASE PERFORMANCE & ADV. PERFORMANCE TUNNING

OVERVIEW OF TUNNING:

• O/S level tuning & Database level tuning
• Buffer (PGA & SGA Components)
• Application Tunning

TUNNING ORACLE MEMORIES:

• When to tune memories & how to tune?
• Tuning P.G.A & S.G.A components
• Database buffer b)log buffer
• shared pool size
• Dynamics SGA & Enable Oracles

Paralle Querey Option

• Config. Of multiple DB writers
• TUNING MEMORY_TARGET
• MEMORY_MAX_TARGET
• DB_CACHE_SIZE
• SHARE_POOL_SIZE
• LOG_BUFFER
• PGA BUFFER

ROW CHANGING & MIGRATION:

• How to identify and avoid row migration & row chaining
• Migrated rows affect OLTP systems
• Enabling TIMED_STATISTICS parameters

PARTITIONING TABLES:

• Introduction to Partitioning
• Partitioning methods

OPTIMIZATION MODES AND STATISTICS & EXPLAIN PLAN:

• Optimization methods (RBO & CBO)
• Advantages of CBO
• Why statistics required?
• How to find stats are available or not?
• Gathering table, column, schema, index & system STATS

SEGMENT TUNING:

• How to find out segment fragmentation & how to resolve
• Table caching, Table Reorg, Index Reorg.(index Rebuild)

CONFIGURATION OF ORACLE TRACCING:

• Trace the user session using TKPROF

HOW TO USE OPTIMIZER HINTS EFFICIENTLY:

• What is hint? How it improves query performance
• ALL_ROWS B)FIRST_ROWS C)CHOOSE D)RULE E)FULL F)INDEX G)NOPARALLEL H)PARALLEL I)APPEND

REPORTS:

HOW TO CONFIGURE STATSPACK, AWR,ASH,ADDM,ASMM

HOW TO READ & ANYLYZE THE PROBLEMS

APPLICATION TUNNING:

Indexing:

• What is an index & types of indexes?
• Purpose of indexing
• How to measure index selectivity
• Btree
• Bitmap
• Composite
• How to create index
• Why to create index
• Which column are suitable for indexing?

Enhancements & Additional Features of 11g & 12c

• Table can be converted as read only
• Transportable tablespace for single partition
• Flashback data archive
• Performance tuning results cache
• Oracle patch installation
• Config. Of ASM database with GRID
• Database Migration
• Database upgradation from 10g to 11g
• Dataguard snapshot standby
• RMAN Enhancements
• Online migration of an active data file
• Invisible coloumn
• Multiple indexes on the same coloumn
• DDL Logging
• Temporary undo in-and-outs
• New backup user privlegs
• How to execute SQL Statements in RMAN
• Restricting PGA size
• How to create CDB
• How to Create PDB

MISCELLANEOUS TOPICS

• SQL LOADER
• MULTIPLE BLOCK SIZE
• AT TABLESPACE LEVEL
• RESIZING ONLINEIREDOLOG FILES
• RENAME THE DATABASE
• HOW TO DROP A DATABASE FROM SQL PROMPT
• MONITORING DATABASE ACTIVITIES
• HOW TO CRETAE CONTROL FILES
• BY USING TRACE FILES
• CREATION OF USER PROFILES
• ORACLE MANAGED FILES (OMF)
• DESCRIBING BACKGROUND PROCESS
• DATABASE AUDTING

1) Oracle DBA Certification:

Certification demonstrates your skill and knowledge in SQL development and helps you in overall development. And, certification depicts your level of expertise in handling SQL tasks which include, Oracle database architecture, performance tuning, administration, installation, and troubleshooting.

2) Which companies are recruiting Oracle DBA professionals?

• JPMorgan Chase
• APPS SOLUTIONS INC
• Macaulay-Brown, Inc.
• STRATEGIC IT STAFFING
• Pentagon Service etc.

3) Who can join this course?

Below mentioned job roles can give you the best career opportunities

• Project managers
• Software developers
• Database administrators and Analysts
• SQL  Architects and Programmers
• People whoever wish to start their career in Oracle DBA