Course Outline:

Introduction to SQL/Fundamental SQL

• Introduction
• Tools used for Develop query: PL/SQL Developer, SQL Developer, Command Line
• Install and Configure Standalone Database in Linux and Windows both.
• Brief Directory Structure of Oracle Database.
• Network Infrastructure
• Retrieving Data using the SQL SELECT Statement
• Restricting and Sorting Data
• Using Single-Row Functions to Customize Output
• Using Conversion Functions and Conditional Expressions
• Reporting Aggregated Data Using the Group Functions
• Displaying Data from Multiple Tables Using Joins
• Using Subqueries to Solve Queries
• Using the SET Operators
• Managing Tables using DML statements
• Introduction to Data Definition Language
• Introduction to Data Dictionary Views
• Creating Sequences, Synonyms, Indexes
• Creating Views
• Managing Schema Objects
• Retrieving Data by Using Subqueries
• Manipulating Data by Using Subqueries
• Controlling User Access
• Manipulating Data
• Managing Data in Different Time Zones

Course Objectives: Oracle 12c Administration Workshop I

By the end of this course students will be able to:

• Installing Oracle Software
• Creating an Oracle Database Using DBCA
• Managing Database instances and ASM instances
• Managing and controlling database network environment
• Define and devise transaction management, concurrency control, crash recovery components
• Managing storage structures
• Controlling user security
• Designing Database backup and recovery procedures
• Take Decisions related with Database Maintenance

Course Outline Short: Oracle 12c Administration Workshop II

• Oracle Database Architecture
• Database installation and creation. (use DBCA)
• Managing Database instances and ASM instances
• Oracle Network environment
• Database storage structures
• User security
• Concurrency control
• Database auditing
• Database maintenance
• Crash Recovery

Detailed Course Outline: Administration I

• Oracle Linux, Environment Variables and Partitions
  • Install Oracle Linux
  • Set Environment Variables
  • Configure Network
  • Multipath Configuration
  • Create user, group and permissions
• Installing Oracle Software
  • Installing Configure Grid
  • Configure ASM Disk
  • Install and Configure Database using ASM Disks

• Creating an Oracle Database Using DBA

• Exploring the Oracle Database Architecture
  • Connecting to a server
  • Oracle Database Server Architecture
  • Instance: Database Configurations
  • Memory Structures – SGA
  • Memory structures – Shared Pool
  • Memory Structures – Buffer Cache
  • Memory Structures – Redo Log Buffer
  • Memory Structures – Large Pool
  • Memory Structures – Java Pool/Streams Pool
  • Memory Structures – PGA
  • Memory Structures – Streams Pool
  • Process Structures
  • Background Processes –  Database Writer Process (DBWn)
  • Background Processes – Log Writer Process (LGR)
  • Background Processes – Checkpoint Process (CKPT)
  • Background Processes – System Monitor Process (SMON)
  • Background Processes – Process Monitor Process (PMON)
  • Background Processes – Recoverer Process
  • Background Processes – Archiver Process (ARCn)
  • Process Startup Sequence
  • Database Storage Architecture
  • Logical and Physical Storage Structures
  • Segments, Extents and Blocks
  • Table Space and Data Files
  • SYSTEM and SYSAUX Table Space
  • ASM storage components
  • Interacting with an Oracle Database

• Managing Database Instance
  • Database initialization parameters modification
  • Stages of database startup
  • Database shutdown modes and options
  • Alert log
  • Using Trace Files
  • Dynamic performance views
  • Data Dictionary views
  • Data dictionary from SQL Expert

• Managing ASM instances
  • Benefits of using ASM
  • ASM instance processes and parameters
  • Interaction between database instances and ASM
  • ASM instance dynamic performance views
  • ASM system privileges
  • ASM disk groups
  • ASM disks
  • Allocation units
  • ASM files
  • Extent Maps
  • Striping granularity
  • Fine-Grained Striping
  • ASM Failure groups
  • Stripe and mirror example
  • Failure example
  • Managing disk groups
  • Adding disk to disk groups
  • Alter commands
  • ASM disk group compatibility
  • Disk Group Attributes
  • ASM Fast Mirror Resync Overview

• Managing and Controlling Database Network Environment
  • Creating additional listeners
  • Creating Oracle Net Service aliases
  • Configuring connect-time failover
  • Controlling the Oracle Net Listener
  • Using tnsping to test Oracle Net connectivity
  • Shared servers versus dedicated servers

• Define and Devise Transaction Management, Concurrency Control, Crash Recovery Components

• Transactions
  • Properties (ACID Rules)
  • Life Cycle
• Concurrency Control
  • Why do we need concurrency control?
  • Types of concurrency control mechanisms
  • Basic samples
• Crash Recovery Components
  • Undo and Redo Operations
  • Examples for different component behaviors
• Deadlocks

• Managing Data Concurrency
  • Locking Mechanism
  • Oracle data concurrency management
  • Enque Mechanism
  • Monitoring and Resolving Locking conflicts Managing Undo Data
  • DML and Undo data generation
  • Monitoring and administer undo data and redo data
  • Configuring undo retention
  • Undo retention guarantee
  • Undo advisor

• Managing Storage Structures
  • Storage of table row data in blocks
  • Oracle-Managed Files (OMF)
  • Enlarging the database

• Controlling User Security
  • Create and Manage database user accounts:
    • Authenticate Users
    • Assign default storage areas (tablespaces)
  • Administer Authentication
  • Grant and revoke privileges (system and object privileges)
  • Create and manage roles
  • Predefined roles
  • Create and manage profiles:
    • Implement standard password security features
    • Control resource usage by users
  • Supplied password verification function
  • Assigning quotas to users
  • Principle of least privilege
  • Protect privileged accounts

• Implementing Oracle Database Auditing
  • DBA responsibilities for security and auditing
  • Standard database auditing
  • DBA responsibilities for security and auditing
  • Standard database auditing
  • Specifying audit options
  • Audit information
  • Value-based auditing
  • Fine-Grained Auditing
  • FGA Guidelines
  • SYSDBA Auditing
  • Maintaining the audit trail
  • Oracle Audit Vault

• Performance Management
  • Use Enterprise Manager to monitor performance
  • Use Automatic Memory Management (AMM)
  • Use the Memory Advisor to size memory buffers
  • View performance-related dynamic views
  • Troubleshoot invalid and unusable objects

• Designing Database Backup and Recovery Procedures
  • Backup and Recovery Concepts
    • Types of failure that can occur in an Oracle database (statement/user process/network/User/Instance failures)
    • Flashback Technology
    • Ways to tune instance recovery (Redo Log files/Log Writer)
    • Using MTTR Advisor
    • Media failure
    • Configuring recoverability
    • Configuring the fast recovery area
    • Checkpoints, redo log files, and archive log files
    • Achiever process
    • Configuring ARCHIVELOG mode
  • Performing Database Backups
    • Consistent database backups
    • Oracle Secure Backup
    • User Managed Backup
    • Recovery Manager (RMAN)
    • Backing Up the Control File to a Trace File
  • Performing Database Recovery
    • Opening a Database
    • Keeping a Database Open
    • Loss of Control file/Redo Log file/data file /noncritical data file /system _critical data file
    • Data failure examples
    • Data recovery advisor
    • Recovery:
      • Control File
      • Redo log File
      • Data File
    • Moving Data
      • Ways to move data
      • Directory Objects
      • Using SQL*Loader to load data from a non-Oracle Database (user files)
      • Using external tables to move data via platform-independent files
      • General architecture of Oracle Data Pump
      • Using Data Pump Export and Import to move data between Oracle Databases
    • Take Decisions Related with Database maintenance
      • Managing optimizer statistics
      • Preferences for Gathering Statistics
      • Managing the Automatic Workload Repository (AWR)
      • Statistic Levels
      • Automatic Database Diagnostic Monitor (ADDM)
      • Advisory framework
      • Automated Maintenance Tasks
      • Server-generated alerts
      • Setting alert thresholds
      • Reacting to alerts
      • Alert types and clearing Alerts
    • Enterprise Manager Support Workbench
      • My Oracle Support
      • Log Service Requests (SR)
      • Manage Patches
        • Apply a patch
        • Stage a patch
      • Oracle Restart

Course Objectives: Administration II

By the end of this course students will be able to:

• Advance RMAN Backup and Recovery
• Oracle Flashback Technologies
• Hands on Advance Performance Tuning
• Oracle High Availability Technologies
• Oracle Supported Application Servers and REST Data Services
• Oracle Replication and Duplication

Course Outline Short: Administration II

• Core Concepts and Tools of the Oracle Database
• Configuring for Recoverability
• Using the RMAN Recovery Catalog
• Configuring Backup Settings
• Creating Backups with RMAN
• Restore and Recovery Task
• Using RMAN to Perform Recovery
• Monitoring and Tuning RMAN
• Diagnosing the Database
• Using Flashback Technology I
• Using Flashback Technology II
• Performing Flashback Database
• Managing Memory
• Managing Database Performance
• Managing Performance by SQL Tuning
• Managing Resources
• Automating Tasks with the Scheduler
• Managing Space in Blocks
• Managing Space in Segments
• Managing Space for the Database
• Duplicating a Database