Oracle DBA

A database administrator (DBA) directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing, and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees in database management and use. A DBA is expected to stay abreast of emerging technologies and new design approaches.

A Database administrator’s task is to maintain the oracle server so that the server can process the user requests. Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.

A course in Oracle Database Administration, requires prerequisites such as SQL & PL/SQL, Unix and Shell Scripting.

A database administrator’s responsibilities can include the following tasks:

  • Installing and upgrading the Oracle Database server and application tools
  • To plan and create databases
  • To manage database availability
  • Allocating system storage and planning future storage requirements for the database system
  • Creating primary database storage structures (tablespaces) after application developers have designed an application
  • Creating primary objects (tables, views, indexes) once application developers have designed an application
  • Modifying the database structure, as necessary, from information given by application developers
  • Enrolling users and maintaining system security
  • Ensuring compliance with Oracle license agreements
  • Controlling and monitoring user access to the database
  • Monitoring and optimizing the performance of the database
  • Database tuning
  • Planning for backup and recovery of database information
  • Maintaining archived data on tape
  • Backing up and restoring the database
  • Network administration Contacting Oracle for technical support.

COURSE TOPICS

1.Introduction

  • What Is Dbms?
  • What Is The Need For Dbms?
  • What Is Dba?
  • Dba Roles & Responsibilities
  • Application Developers
  • Database Users
  • Tasks Of A Database Administrator
  • Identifying The Oracle Database Software Release
  • Oracle Products And Services.
  • Oracle 9i Enterprise Edition
  • Oracle Database 10g: “G” Stands For Grid.

2. Oracle Server Architecture

  • Oracle Architectural Components
  • Overview Of Primary Components
  • Oracle Server
  • Oracle Instance
  • Establishing A Connection And Creating A Session
  • Oracle Database
  • Physical Structure
  • Memory Structure
  • System Global Area
  • Shared Pool
  • Library Cache
  • Data Dictionary Cache
  • Database Buffer Cache
  • Redolog Buffer
  • Large Pool
  • Java Pool
  • Program Global Area
  • Process Structure
  • User Process
  • Server Process
  • Background Processes
  • Database Writer (Dbwn)
  • Log Writer (Lgwr)
  • System Monitor (Smon)
  • Process Monitor (Pmon)
  • Checkpoint (Ckpt)
  • Archiver (Arcn)
  • Manageability Monitor
  • Manageability Monitor Light
  • Memory Manager
  • Job Queue Coordination Process
  • The Rebalance Master
  • The Asm Rebalance
  • The Asm Background
  • The Recovery Writer
  • The Changing Tracking Writer
  • Logical Structure
  • Processing SQL Statements
  • Miscellaneous Background Processes
  1. Queue Monitor Coordination (Qmnc)
  2. Recovery (Reco)
  3. Lock (Lckn)

3. Installation (Windows & Linux As 4)

  • Database Administrator Tools
  • Oracle Universal Installer
  • Starting The Universal Installer
  • Non-Interactive Installation Using Response Files
  • Oracle Database Configuration Assistant
  • Database Administrator Users
  • Sql*Plus
  • Oracle Enterprise Manager
  • Oracle Enterprise Manager-Architecture
  • Practice Test

4. Managing An Oracle Instance

  • Initialization Parameter Files
  • Pfile Initsid.Ora
  • Creating A Pfile
  • Pfile Example
  • Spfile Spfilesid.Ora
  • Creating An Spfile
  • Spfile Example
  • Startup Command Behaviour
  • Modifying Parameters In Spfile
  • Starting Up A Database Nomount
  • Starting Up A Database Mount
  • Starting Up A Database Open
  • Startup Command
  • Alter Database Command
  • Opening A Database In Restricted Mode
  • Opening A Database In Read-Only Mode
  • Shutting Down The Database
  • Shutdown Options
  • Monitoring An Instance Using Diagnostics Files*
  • Alert Log File
  • Background Trace Files
  • User Trace File
  • Enabling Or Disabling User Tracing
  • Practice Test.

5. Creating A Database

  • Manually
  • Dbca

6. Managing Table spaces

  • Tablespaces And Datafiles
  • Types Of Tablespaces
  • Creating Tablespaces
  • Space Management In Tablespaces
  • Locally Managed Tablespaces
  • Dictionary-Managed Tablespaces
  • Undo Tablespace
  • Temporary Tablespaces
  • Default Temporary Tablespace
  • Creating A Default Temporary Tablespace
  • Restrictions On Default Temporary Tablespace
  • Read Only Tablespaces
  • Taking A Tablespace Offline
  • Changing Storage Settings
  • Resizing A Tablespace
  • Enabling Automatic Extension Of Datafiles
  • Manually Resizing A Datafile
  • Adding  Datafiles To A Tablespace
  • Methods For Moving Datafiles
  • Dropping Tablespaces
  • Managing Tablespaces Using Omf
  • Obtaining Tablespace Information
  • Practice Test

7. Managing Tables

  • Storing User Data
  • Oracle Built In Data Types
  • Rowid Format
  • Structure Of A Row
  • Creating A Table
  • Creating A Table : Guidelines
  • Creating Temporary Tables
  • Setting Pctfree And Pctused
  • Row Migration And Changing
  • Changing Storage And Block Utilization Parameters
  • Manually Allocating Extents
  • Nonpartitioned Table Reorganisation
  • Truncating A Table
  • Dropping A Table
  • Dropping A Column
  • Using The Unused Option
  • Obtaining Table Information

8. Storage Structure And Relationships

  • Storage And Relationship Structure
  • Types Of Segments
  • Storage Clause Precedence
  • Extent Allocation And Deallocation
  • Used And Free Extents
  • Database Block
  • Multiple Block Size Support
  • Standard Block Size
  • Nonstandard Block Size
  • Creating Nonstandard Block Size Tablespaces
  • Multiple Block Sizing Rules
  • Database Block Contents
  • Block Space Utilization Parameters
  • Data Block Management
  • Automatic Segment –Space Management
  • Configuring Automatic Segment-Space Management
  • Manual Data Block Management
  • Block Space Usage
  • Obtaining Storage Information

9. Managing The Control File

  • Control File
  • Control File Contents
  • Multiplexing The Comtrol File.
  • Multiplexing The Control File When Using Spfile
  • Multiplexing The Control File When Using Pfile.
  • Managing The Control File With Omf
  • Obtaining Control File Information
  • Practice Test

10. Maintaining Redo Log Files

  • Redo Threads
  • Using Redo Log Files
  • Structure Of Redo Log Files.
  • How Redo Log Files Work
  • Log Switches And Log Sequence Numbers.
  • Adding Online Redo Log File Groups
  • Adding Online Redo Log File Members
  • Dropping Online Redo Log File Groups
  • Dropping Online Redo Log File Members
  • Relocating Or Renaming Online Redo Log Files
  • Online Redo Log File Configuration
  • Setting The Size Of Online Redo Log File
  • Choosing The Number Of Online Redo Log Files
  • Forcing Log Switches And Checkpoints
  • Clearing Online Redo Log File
  • Viewing Information Of Redo Logs
  • Practice Test

11. Rollback Segments

  • Transaction And Rollback Segments
  • How Extents Are Deallocated From Rollback Segments
  • The Rollback Segment System
  • Oracle Instance And Rollback Segments
  • Rollback Segment States
  • Partly Available And Needs Recovery Rollback Segments
  • Deferred Rollback Segment
  • High Water Mark
  • Altering Rollback Segments.
  • Changing Rollbacksegments Storage Parameters
  • Shrinking Rollback Segments Manually
  • Changing Online/Offline Status Of Rollback Segments
  • Explicitly Assigning Transactions To A Rollback Segments
  • Dropping A Rollback Segment
  • Viewing Rollback Segment Information
  • Practice Test

12. Undo Management

  • Data Manipulation
  • Undo Data
  • Transactions And Undo Data
  • Storing Undo Information
  • Undo Data Versus Redo Data
  • Monitoring Undo
  • Creating A Undo Tablespace
  • Using Create Undo Tablespace Statement.
  • Sizing The Undo Tablespace
    • Auto-Extensible Tablespaces
    • Sizing Fixed-Sized Undo Tablespaces
  • Alter Undo Tablespace
  • Dropping Undo Tablespace
  • Switching Undo Tablespace
  • Specifying Undo Retention Policy
  • Retention Guarantee
  • Choosing The Retention Period For Flash Back Query
  • Viewing The Information About Undo Tablespace
  • Practice Test

13. Managing Archived Redo Logs

  • Choosing Between Noarchivelog And Archivelog
  • Setting The Initial Database Archive Log Mode
  • Controlling Number Of Archiver Processes
  • Performing Manual Archiving
  • Specifying Archive Destination
    • Understanding Archive Destination Status
    • Managing Archive Destination Failure
    • Re-Archiving To A Failed Destination
  • Views
  • Practice Test

14. User Management

  • Users And Security
  • Database Schema
  • Checklist For Creating Users
  • Creating A New User : Database Authentication
  • Creating A New User : Operating System Authentication
  • Changing User Quota On Tablespaces
  • Dropping A User
  • Obtaining User Information
  • Practice Test

15. Managing Privileges

  • Managing Privileges
  • System Privileges
  • System Privileges : Examples
  • Granting System Privileges
  • Sysdba And Sysoper Privileges
  • System Privilege Restrictions
  • Revoking System Privileges
  • Revoking System Priveleges With The Admin Option
  • Object Privileges
  • Granting Object Privileges
  • Revoking Object Privileges
  • Revoking Object Privileges With Grant Option
  • Obtaining Privileges Information
  • Practice Test

16. Managing Roles

  • Roles
  • Benefits Of Roles
  • Creating Roles
  • Predefined Roles
  • Modifying Roles
  • Assigning Roles
  • Establishing Default Roles
  • Application Roles
  • Enabling And Disabling Roles
  • Revoking Roles From Users
  • Removing Roles
  • Guidelines For Creating Roles
  • Guidelines For Using Passwords And Default Roles
  • Obtaining Role Information
  • Practice Test

17. Indexes

  • Classification Of Indexes
  • B-Tree Index
  • Bitmap Indexes
  • Comparing B-Tree And Bitmap Indexes
  • Creating Normal B-Tree Indexes
  • Creating Indexes : Guidelines
  • Creating Bitmap Indexes
  • Create Indexes After Inserting Table Data
  • Order Index Columns For Performance
  • Limit The Numberof Indexes For Each Table
  • Drop Indexes That Are No Longer Required
  • Specify Index Block Space Use
  • Estimate Index Size And Set Storage Parameters
  • Specify The Tablespace For Each Index
  • Consider Parallelizing Index Creation
  • Consider Creating Indexes With Nologging
  • Consider Costs And Benefits Of Coalescing Or Rebuilding Indexes
  • Consider Cost Before Disabling Or Dropping Constraints
  • Creating Indexes
    • Creating An Index Explicitly
    • Creating A Unique Index Explicitly
    • Creating An Index Associated With A Constraint
  • Specifying Storage Options For An Index Associated With A Constraint
  • Specifying The Index Associated With A Constraint
  • Collecting Incidental Statistics When Creating An Index
  • How Function-Based Indexes Work
  • Altering Indexes
  • Altering Storage Characteristics Of An Index
  • Rebuilding An Existing Index
  • Monitoring Index Usage
  • Dropping Indexes
  • Viewing Index Information
  • Practice Test

18. Implementing Oracle Database Security

  • Industry Security Requirements
  • Seperation Of Responsibilities
  • Database Security
  • Principle Of Least Privilege
  • Applying The Principle Of Least Privilege
  • Monitoring For Suspicious Activity
  • Standard Database Auditing
  • Enabling Auditing
  • Uniform Audit Trails
  • Enterprise Manager Audit Page
  • Specifying Audit Options
  • Using And Maintaining Audit Information
  • Value-Based Auditing
  • Fine-Grained Auditing
  • Fga Policy
  • Audited Dml Statement : Considerations
  • Fga Guidelines
  • Dba Auditing
  • Maintaining The Audit Trail
  • Security Updates
  • Applying Security Patches

19. Advanced Concepts

  • Backup And Recovery  (Cold & Hot Backups)
  • Networking
  • Rman
  • Pt (Performance And Tuning)
  • Import Export & Datapump (Impdp/Expdp)
  • Data Guard
  • Db Cloning
  • Sql Loader
  • Db Links/Materialized Views
  • Flashback Technology
  • Transportable Table Spaces
  • Database Upgradation
  • Asm
  • Rac ( Installation + Overview)

Oracle 12c Dba Topics

  • Day – 1  Oracle 12c Architecture -1, Architecture -2
  • Day – 2  Oracle 12c Architecture -3
  • Day – 3  Introduction To Networking With Oracle 12c (Db Links & Materialized Views (With Mv-Logs)
  • Day – 4 Oracle 12c Multitenant Architecture