Oracle Training - United Kingdom

Oracle Database 11g Release 2 DBA - Part I Course Outline

Module 1: Installing and Configuring Oracle Database

• Steps for Installing and Configuring the Oracle Database

Module 2: Administering an Oracle Database

• Introduction
• Initialisation Parameter
• Database Limits
• Security
• Using Trace and Alert Files
• Password Management

Module 3: Understanding Network Configuration

• Understanding Network Configuration
  • Oracle Net Listener Configuration
  • Client Connections

Module 4: Creating and Managing Database Storage Structures

• About Database Storage Structures
  • About Control Files
  • About Online Redo Log Files
  • About Archived Redo Log Files
  • About Data Files
• Viewing Database Storage Structure Information
  • Viewing Control File Information
  • Viewing Online Redo Log File Information
  • Viewing Archived Redo Log File Information
  • Viewing Data File Information
  • Viewing Tablespace Information

Module 5: Managing Redo Log Files and Control Files

• Introduction to Redo Log
  • Redo Threads
  • Redo Log Contents
  • How Oracle Database Writes to the Redo Log
• Managing Redo Log Files and Control Files
  • Creating Redo Log Groups and Members
  • Clearing a Redo Log File
  • Redo Log Data Dictionary Views
  • Introduction to Control Files
  • Creating Initial Control Files
  • Troubleshooting After Creating Control Files
  • Backing Up Control Files

Module 6: Managing Schema Objects

• Creating Multiple Tables and Views in a Single Operation
• Analysing Tables, Indexes, and Clusters
• Truncating Tables and Clusters
• Renaming Schema Objects
• Managing Object Dependencies
• Switching to a Different Schema

Module 7: Managing Users, Profiles, Privileges and Roles

• Managing Privileges and Roles
  • Privilege
  • Roles
  • System Privileges
  • About User Roles
  • Roles Affect the Scope of a User's Privileges
  • Managing Object Privileges

Module 8: Managing Undo Data and Temporary Segments

• Managing Undo Data and Temporary Segments
  • Managing Undo Data
  • Undo Data
  • Undo Tablespace
  • Undo Retention
  • Managing Undo Data
  • Viewing Undo Information

Module 9: Monitoring and Resolving Lock Conflicts

• Preparing for Conflict Resolution
• Monitoring Locks

Module 10: Maintaining Database Security

• About User Security
• Creating a New User Account
  • Specifying a User Name
  • Assigning the User a Password

Module 11: Monitoring General Database State and Workload

• Steps to Monitoring General Database State and Workload

Module 12: Oracle Data Pump

• Overview of Oracle Data Pump
• Data Pump Export
• Data Pump Import

Module 13: Backing Up and Recovering the Database

• Introduction
• Tasks of Backup Administration
• Configuring Database
• Configuring Backup Settings0

Oracle Database 11g Release 2 DBA - Part II Course Outline

Module 1: Backup and Disaster Recovery

• Backup and Recovery
  • Overview
  • Backup Strategies
  • Issues in Backup and Recovery
  • Oracle Backup and Recovery Solutions
  • Oracle Backup and Recovery Strategy
  • Backing Up a Database in Windows
  • Restore the Database on Windows

Module 2: Archivelog and Nonarchivelog

• Archivelog and Nonarchivelog
  • Modes
  • Uses for Archived Redo Logs
  • Running a Database in NOARCHIVELOG Mode
  • Advantages of Archiving Filled Groups
  • Redo Log File Use in Archivelog Mode
  • Changing the Database Archiving Mode

Module 3: Managing Memory

• Managing Memory
  • Memory Management
  • Types of Memory Management
• Memory Architecture
• Memory Structures

Module 4: Recovery Manager

• Recovery Manager (RMAN)
  • RMAN Architecture
  • RMAN Environment Structure
  • Methods of RMAN Backup
  • Restoring and Recovering Database Files
  • Preview a Database Restore and Recovery
  • Recovering the Whole Database

Module 5: Flashback Technology

• Overview
• Features
• Configuring Database for Flashback Transaction
• Configuring Database for Automatic Undo Management
• Configuring Database for Oracle Flashback Transaction Query
• Granting Necessary Privileges

Module 6: Reviewing and Diagnosing Databases

• Database Logs

Module 7: Monitoring and Improving Database Performance

• Database Advisors
  • Overview
  • Performance Advisors
  • What Does an Automatic SQL Tuning Advisor Do?
  • Configuring the Automatic SQL Tuning Advisor

Module 8: Controlling Database Resources

• Overview
• Resource Plan
• Resources Manager Administration Privileges
• Types of Resources Managed by the Resource Manager
• Disabling the Resource Manager
• Using Several Resource Allocation Methods

Module 9: Automating Tasks with Scheduler

• Automate Tasks
  • Overview
  • Maintenance Windows
  • Configuring Automated Maintenance Tasks
  • Configuring Resource Allocations for Automated Maintenance Tasks
  • Predefined Maintenance Windows

Oracle Database 19c Administration Training Course Outline

Module 1: Introduction to Oracle Database

• Overview of the Oracle Database
• Overview of the Oracle Cloud
• Relational Database Concepts
• Database Administration Tasks

Module 2: Oracle Database Architecture

• Overview
• Database and Instance
• Oracle Database Memory Structures
• Process Architecture and Structures
• Server and Client Processes
• Oracle Database Storage Architecture
• Connect to the Oracle Database Instance

Module 3: Administer a Database Instance

• Administrative Tools Available to a DBA
• Use SQL*Plus in Oracle to Manage a Database Instance
• Use SQL Developer to Manage a Database Instance
• Overview of the Enterprise Manager Framework
• Access Enterprise Manager Database Express
• Enterprise Manager Cloud Control
• Initialisation Parameter Files

Module 4: Configure the Oracle Network Environment

• Overview of Network Configuration
• Oracle Net Listener Configuration
• Oracle Net Naming Methods
• Tools for Network Configuration
• Client Connections with Net Manager
• Viewing Listener Configuration
• Starting and Stopping the Listener
• Use TNSPING to Test Oracle Net Connectivity
• Connect to the Database
• Configure Net Services with EM

Module 5: Storage Structures

• Overview of Data Storage
• Database Block
• Overview of Tablespaces and Data Files
• Use Enterprise Manager to View the Storage Structure of the Database
• Create and Alter Tablespace Commands
• Temporary Tablespaces
• Create and Manage Datafiles
• Use OMF
• Drop Tablespaces and Datafiles

Module 6: Manage Users

• Predefined Database Administration Accounts
• User Accounts
• Create a User Account
• Change a User's Password
• Manage and Drop a User Account
• Monitor User Information
• Terminate User Sessions
• Create a Schema Only Account

Module 7: Manage Privileges

• Database Access
• Oracle Supplied Roles
• System and Object Level Privileges
• Grant and Revoke Commands
• Create, Alter, and Drop Roles
• Use Predefined Roles

Module 8: Database Auditing

• Overview of Database Security
• Overview of Database Auditing
• Standard Auditing
• Unified Audit Trail
• Separation of Audit Responsibilities
• Configure the Audit Trail
• Specify Audit Options

Module 9: Data Concurrency and Lock Conflicts

• Data Concurrency and Consistency
• Levels of Locking in Oracle
• Methods Used to Acquire Locks
• Possible Causes of Contention
• DML Locks
• Prevent Locking Problems
• Detect Lock Contention
• Resolve Conflicts

Module 10: Undo Management

• Undo Data Overview
• Monitor and Administer Undo
• Configure Undo Retention
• Switch Undo Tablespaces
• Specify the Retention Period
• Guarantee Undo Retention
• Oracle Flashback Query
• View Undo Space Information
• Use the Undo Advisor
• Alter an Undo Tablespace to a Fixed Size

Module 11: Backup and Recovery Configuration

• Oracle Backup Solutions Program
• Oracle Suggested Backup Strategy
• Overview of Database Backup, Recover
• Flashback Technology
• Types of Failure
• Instance Recovery
• Tune Instance Recovery
• MTTR Advisor
• Media Failure
• Configure a Database for Recoverability

Module 12: Proactive Database Maintenance

• Database Maintenance
• View the Alert Log
• Automatic Workload Repository
• Statistic Levels
• Automatic Database Diagnostic Monitoring
• Monitor an Oracle Database
• Use the Advisors
• Set Up Notification Rules

Module 13: Performance Management

• Tuning Information Sources
• Performance Monitoring
• Performance Planning
• Instance Tuning
• Performance Tuning Methodology
• Performance Tuning Data
• Managing Memory
• Manage Private Temporary Tables

Module 14: SQL Tuning

• Overview
• Cancel a SQL Statement in a Session
• Oracle Optimiser
• SQL Plan Directives
• Adaptive Execution Plans
• SQL Advisors
• Implement Automatic Tuning Recommendations

Module 15: Moving Data

• Create Directory Objects
• Data Pump Architecture
• Data Pump Data Dictionary Views
• Use Data Pump to Export and Import Data
• Overview of SQL Loader
• Command Line Parameters
• Record Filtering
• Control File Keywords
• Data Files
• SQL Loader Data Paths
• External Tables

Module 16: Managing Resources with Resource Manager

• Database Resource Manager
• Use the Resource Manager
• Create Resource Plans
• Create Resource Plan Directives
• Allocate Resources for Resource Plans

Module 17: Automate Tasks with the Scheduler

• Introduction to the Scheduler
• Access Rights
• Scheduler Components and Workflow
• Create a Job
• Job Classes
• Use Time Based, Event-Based Schedules
• Create an Event-Based Schedule

Module 18: Manage Space

• Overview of Space Management
• Block Space Management within Segments
• Segment Types
• Allocate Extents and Space
• Row Chaining and Migration
• Create Tables without Segments

Module 19: Oracle Support

• Enterprise Manager Support Workbench
• Register for Security Updates
• Work with Oracle Support
• Log Service Requests
• Manage Patches
• Apply a Patch

Oracle 18c DBA Fundamentals​ Course Outline

Module 1: Getting Started with Database Administration

• Types of Oracle Database Users
• Tasks of a Database Administrator
• SQL Statements
• Identifying Your Oracle Database Software Release
• About Database Administrator Security and Privileges
• Database Administrator Authentication
• Creating and Maintaining a Database Password File
• Data Utilities

Module 2: Creating and Configuring an Oracle Database

• About Creating an Oracle Database
• Considerations Before Creating the Database
• Creating a Database with DBCA
• Creating a Database with the CREATE DATABASE Statement
• Specifying CREATE DATABASE Statement Clauses
• Specifying Initialisation Parameters
• Managing Initialisation Parameters Using a Server Parameter File
• Managing Application Workloads with Database Services
• Considerations After Creating a Database
• Cloning a Database
• Dropping a Database
• Database Data Dictionary Views
• Database Configuration Assistant Command Reference for Silent Mode

Module 3: Starting Up and Shutting Down

• Starting Up a Database
• Altering Database Availability
• Shutting Down a Database
• Quiescing a Database
• Suspending and Resuming a Database
• Delaying Instance Abort

Module 4: Managing Processes and Memory

• About
  • Dedicated and Shared Server Processes
  • Database and Proxy Resident Connection Pooling
• Configuring
  • Oracle Database for Shared Server
  • Database Resident Connection Pooling
• Managing
  • Prespawned Processes
  • Processes for Parallel SQL Execution
  • Processes for External Procedures
  • Terminating Sessions
• Process and Session Data Dictionary Views
• What is Memory Management and its Architecture?
• Using Automatic Memory Management
• Configuring
  • Memory Manually
  • Database Smart Flash Cache
• Improving Query Performance
  • Memoptimised Rowstore
  • Oracle Database In-Memory

Module 5: Managing Users and Securing the Database

• Importance of Establishing a Security Policy for Your Database
• Managing Users and Resources
• User Privileges and Roles
• Auditing Database Activity
• Predefined User Accounts

Module 6: Monitoring the Database and Managing Diagnostic Data

• Monitoring
  • Errors and Alerts
  • Performance
  • Quarantined Objects
• Oracle Database Fault Diagnosability Infrastructure
• Investigating, Reporting, and Resolving a Problem
• Viewing Problems with the Support Workbench
• Adding Problems Manually to the Automatic Diagnostic Repository
• Viewing the Alert Log
• Finding Trace Files
• Running Health Checks with Health Monitor
• Repairing SQL Failures with the SQL Repair Advisor
• Repairing Data Corruptions with the Data Recovery Advisor
• Creating, Editing, and Uploading Custom Incident Packages

Module 7: Managing Control Files

• What Is a Control File?
• Guidelines for Control Files
• Creating Control Files
• Troubleshooting After Creating Control Files
• Backing Up Control Files
• Recovering a Control File Using a Current Copy
• Dropping Control Files
• Control Files Data Dictionary Views

Module 8: Managing the Redo Log

• What Is the Redo Log?
• Planning the Redo Log
• Creating Redo Log Groups and Members
• Relocating and Renaming Redo Log Members
• Dropping Redo Log Groups and Members
• Forcing Log Switches
• Verifying Blocks in Redo Log Files
• Clearing a Redo Log File
• Precedence of FORCE LOGGING Settings
• Redo Log Data Dictionary Views

Module 9: Managing Archived Redo Log Files

• What Is the Archived Redo Log?
• Choosing Between NOARCHIVELOG and ARCHIVELOG Mode
• Controlling Archiving
• Specifying Archive Destinations
• About Log Transmission Modes
• Managing Archive Destination Failure
• Controlling Trace Output Generated by the Archivelog Process
• Viewing Information About the Archived Redo Log

Module 10: Managing Tablespaces

• Guidelines for Managing Tablespaces
• Creating Tablespaces
• Consider Storing Tablespaces in the In-Memory Column Store
• Specifying Nonstandard Block Sizes for Tablespaces
• Controlling the Writing of Redo Records
• Altering Tablespace Availability
• Using Read-Only Tablespaces
• Altering and Maintaining Tablespaces
• Renaming Tablespaces
• Dropping Tablespaces
• Managing Lost Write Protection with Shadow Tablespaces
• Managing the SYSAUX Tablespace
• Correcting Problems with Locally Managed Tablespaces
• Migrating the SYSTEM Tablespace to a Locally Managed Tablespace
• Viewing Information About Tablespaces

Module 11: Managing Data Files and Temp Files

• Guidelines for Managing Data Files
• Creating Data Files and Adding Data Files to a Tablespace
• Changing Data File Size
• Altering Data File Availability
• Renaming and Relocating Data Files
• Dropping Data Files
• Verifying Data Blocks in Data Files
• Copying Files Using the Database Server
• Mapping Files to Physical Devices
• Data Files Data Dictionary Views

Module 12: Transporting Data and Managing Undo

• About Transporting Data
• Transporting Databases
• Transporting Tablespaces Between Databases
• Transporting Tables, Partitions, or Subpartitions Between Databases
• Converting Data Between Platforms
• Guidelines for Transferring Data Files
• What Is Undo?
• Introduction to Automatic Undo Management
• Managing Undo Tablespaces
• Migrating to Automatic Undo Management
• Managing Temporary Undo
• Undo Space Data Dictionary Views

Module 13: Using Oracle Managed Files

• About Oracle Managed Files
• Enabling the Creation and Use of Oracle Managed Files
• Creating Oracle Managed Files
• Operation of Oracle Managed Files
• Scenarios for Using Oracle Managed Files

Module 14: Managing Views, Sequences, and Synonyms

• Managing Views
• Managing Sequences
• Managing Synonyms
• Views, Synonyms, and Sequences Data Dictionary Views

Module 15: Managing Automated Database Maintenance Tasks

• About Automated Maintenance Tasks and Maintenance Windows
• Configuring
  • Automated Maintenance Tasks
  • Maintenance Windows
  • Resource Allocations for Automated Maintenance Tasks
• Automated Maintenance Tasks Reference

Module 16: Managing a Distributed Database

• Managing Global Names in a Distributed System
• Creating Database Links
• Using Shared Database Links
• Managing Database Links
• Viewing Information About Database Links
• Creating Location Transparency
• Managing Statement Transparency

Oracle SQL Fundamentals Course Outline

Module 1: Relational Database Concepts

• Introduction to Relational Database
• Keys in SQL
• SQL Syntax

Module 2: Using SQL*Plus

• SQL*Plus
• Working with SQL*Plus

Module 3: Using SQL Developer

• SQL Developer Concepts
• SQL Developer User Interface

Module 4: Retrieving Data with the Select Statement

• Using the Select Statement
• SELECT Statement
• SQL Statement

Module 5: Aggregate Functions

• Aggregate Function Overview
• AVG Function
• SUM Function
• MAX Function
• MIN Function

Module 6: Joining Tables

• Joins in SQL
• How to Join Two Tables?

Module 7: Numeric, Character and Date Functions

• Functions in SQL
• ABS
• ACOS
• CEIL
• ASIN ()
• ATAN ()
• Character Functions
• INITCAP
• ASCII
• Date Functions
• CURRENT_DATE
• LAST_DAY
• NEXT_DAY
• MONTHS_BETWEEN
• DBTIMEZONE
• EXTRACT

Module 8: Conversions and Miscellaneous

• Conversion Functions in SQL
• Different Functions

Module 9: SQL Parameters

• Parameter Declaration
• Keyword and Parameter Descriptions

Module 10: Using Subqueries

• Using Subqueries
• Subqueries with the INSERT Statement
• Deleting a Row

Module 11: Managing Data

• Database Management
• Monitoring Databases
• Administering Databases

Module 12: Managing Tables

• Guidelines for Table Management
• Creating Tables
• Creating a Temporary Table
• Loading Tables
• Automatically Collecting Statistics on Tables
• Creating a Table
• Alter Table
• Altering Tables
• Dropping or Truncating Tables
• Managing External Tables
• Creating External Tables
• Altering External Tables
• Dropping External Tables
• Viewing Information About Tables

Module 13: Managing Indexes and Views

• About Indexes
• About Views
• Create a View

Module 14: Managing Sequences and Synonyms

• About Sequences
• Creating Sequences
• Altering Sequences
• Dropping Sequences
• About Synonyms
• Creating Synonyms
• Dropping Synonyms

Module 15: Retrieve Data Using Subqueries

• Types of Subqueries
• IN and NOT IN Subqueries
• Retrieving Data

Module 16: Hierarchical Queries

• Hierarchical Queries Overview
• Hierarchical Query Clause

Module 17: Enhanced grouping features

• Grouping
• Syntax of GROUP

Module 18: Manage Large Data Sets

• Managing Very Large Databases
• Benefits of Partitioning

Module 19: Date, Time, and Time Zone Support

• Introduction to Time Zone Support
• Datetime and Interval Datatypes
• Datetime Datatypes
• Interval Datatypes

Module 20: Regular Expression Support

• Introduction to Regular Expressions
• Implementation of Oracle Database Regular Expressions

Module 21: Manage Objects with the Data Dictionary

• Introduction to Data Dictionary
• Structure of Data Dictionary
• How the Data Dictionary is Used?

Module 22: Manage Schema Objects

• Introduction to Schema Objects
• Schema Object Management Privileges

​Oracle Database 12c R2: New Features for 12c R1 Administrators Ed 1 Course Outline

Module 1: Application Containers and Applications

• What is Application Root and Application PDBs?
• Application Container
  • Create
  • Manage
  • Install Application
  • Patch Application
  • Upgrade Application
• Impacts in
  • Data Guard
  • Oracle Goldengate
  • Oracle Streams
  • Xstream
  • ADO
  • Heat Maps

Module 2: Security in PDBs

• Create Users in Application Containers
  • Commonly
  • Locally
• Grant Privileges in Application Containers
  • Commonly
  • Locally
• Grant Roles in Application Container
  • Commonly
  • Locally
• Differentiate
  • Common and Local Roles in Application Containers
  • Common and Local Objects in Application Containers
  • Common and Local Profiles in Application Containers
• How to Manage PDB Lockdown Profiles?
• How to Create Unified Auditing Policies Operations in Application Containers?

Module 3: PDBs Creation Using New Methods

• How to Create a PDB from CDB Seed Enhancements?
• Unplug into a PDB Archive File
• Plug Using the Archive File
• How to Create Local UNDO Tablespace at PDB Level?
• What is Hot Cloning?
• Near-Zero Downtime PDB Relocation and Create a Remote Proxy PDB

Module 4: PDBs Recovery and Flashback

• Shut Down a PDB When PDB Datafile is Missing
• Flashback at PDB Level and Flashback a PDB To a Clean PDB Restore Point

Module 5: Performance in PDBs

• View
  • CDB / PDB ADDM Analysis
• Manage
  • SGA and PGA at the PDB Level
• Share
  • Resources Between PDBs

Module 6: Upgrade and Other Operations in PDBs

• Upgrade
  • 12.1.0.2 CDB to 12.2
  • 12.1.0.2 PDB to 12.2 By Using Unplug, Plug, and Upgrade
• Use Cross-Platform Non-CDB or PDB Backup sets to Import Into a CDB

Module 7: Privileges and User Profiles

• What is New 12.2 SYSRAC Administrative Privilege?
• Enhancements of Password File
• External Users Grant Administrative Privileges
• How to Migrate the Password File from Pre-12 or 12.1 to 12.2?
• New System Privilege
  • Inherit any Remote Privileges
  • Object Privilege Inherit Remote Privileges
• Security of Administrative Users Enhancement
• How to Pre-Configure a Maximum Number of Days in the User Profile for Which a User Can Remain Inactive?

Module 8: Unified Auditing

• Audit Policies for All Users to Whom the Role is Granted Directly
  • Create Audit Policies
  • Enable Audit Policies
• How to Capture VPD (Virtual Private Database) Generated Predicates?

Module 9: Data Redaction

• EM GUI New Redaction Format Library
• Redaction Policy Expression and New Redaction Functions

Module 10: Data Encryption

• Encrypt, Decrypt, and Rekey Tablespaces Online
• Specific Tablespaces
  • SYSAUX
  • SYSTEM
  • UNDO
  • Temporary
• How to Complete the Operations with Alter Tablespace Encryption Finish Encrypt, Rekey, and Decrypt?

Module 11: Transparent Sensitive Data Protection

• Use TSDP with
  • Unified Auditing
  • Fine-Grained Auditing (FGA)
  • Transparent Data Encryption (TDE)

Module 12: Availability Enhancements

• Non-Auto-Login Support for Duplicate
  • Use Encrypted Backups Whose Decryption Keys are in a Non-Auto-Login Wallet
• Update and Drop the Catalog Without using DROP CATALOG and UPGRADE CATALOG Commands
• Automate the Two Step Manual Process into One Recovery Operation After Missing Logs Found During RECOVER DATABASE
  • Recrepair FAILURE Command
• Transport Data Across Platforms and Steps to Recover a Table
• What is Online Redefinition Supports?

Module 13: Oracle Data Pump, SQL Loader, and External Tables

• Enhancements of
  • Oracle Data Pump
  • SQL Loader
  • External Table

Module 14: In-Memory Column Store

• Dynamic Parameter in 12.2
  • INMEMORY_SIZE
• Caches Results of Virtual Columns and Frequently Evaluated Expressions For Subsequent Reuse
• How to Optimising Joined Columns by Creating Join Groups?
• Interact with Other Products and Features

Module 15: SQL Tuning

• Enhancement of SQL Performance Analyser
• Enhancement of SQL Plan Management
• Statistics Gathering Performance Improvements
  • Optimiser Statistics Advisor
• How to do SQL Performance Analyser (SPA) Enhancements to Make it Resilient to Certain Workloads Which Can Cause Divergence?
• What are the New Ways of SQL Processing?
• What are the Continuous Adaptive Query Plans?

Module 16: Resource Manager and Other Performance Enhancements

• Manage Session PGA Limit and Reduce Cursor Invalidations for DDLs
• Advanced Index Compression (HIGH)
• What are Real-Time Materialised Views with On Query Computation?
• Describe how to Use ON STATEMENT Refresh When a DML Operation Takes Place on Materialised Views Without the Need to Commit the Transaction?
• What is HCC Data Compression with ADO Row-Level Policy

Module 17: Partitioning

• Auto-List Partitioning and Composite Partitioning
• Multicolumn List Partitioning and Read-Only Partition
• Creation of Defer Subpartition Segment
• How to Filter Data During Partition Maintenance Operations?

Module 18: Manageability

• How to Start and Stop a DBOP by Specifying the Session ID and Serial#?

Module 19: Diagnosability

• Ho to Set a Retention Target to ADR for an Automatic Delete of ADR Files?
• How to Use the Retention Advisor?
• Installation of TFA Collector by Default for RDBMS (With RDBMS Root Scripts)
• Start TFA Collector
• Allow Data Pump Tracing Changes Without Stopping and then Restarting the Job
• MV Refresh Statistics

Module 20: Oracle Database Cloud Services

• What is Oracle Database Cloud Services Console?
• How to Create a Database Deployment Under 12.2?
• Procedure of Database Deployment Backup and Recovery Automatic
• Database Deployments Vs On-Premises Databases
• How to Migrate On-Premises Database to Oracle Database Public Cloud Services?

Module 21: SQL Enhancements

• What is New SQLcl Utility?
• Usage of New 128 Bytes Length for Database Objects
• How to Recall SQL*Plus Commands in the Same Session from Memory?
• How to Use the New VALIDATE_CONVERSION Function

Oracle Database 12cR2, 18c and 19c New Features for Administrators​ Course Outline

Module 1: Oracle Database Multitenant Architecture Overview

• Oracle Database 12cR2 Architecture Overview
• An Overview of a Container Database (CDB)
• Benefits of Multitenant Architecture
• Root Container
• Pluggable Database Containers
• Structure of the Root Container
• Structure of a Pluggable Database (PDB)
• CDB_xxx and DBA_xxx Views

Module 2: Install the Oracle 12c or 18c Database Software

• Oracle 12 c Installation
• System Requirements and OS Prerequisites
• Set Environment Variables
• Set Permissions to Run Root Configuration Scripts Automatically
• 19c Automated Installation, Configuration
• Install the Oracle Database Software with the Oracle Universal Installer
• Silent Mode Installation
• 19c Image Based Client Installation
• Database Software Deinstallation

Module 3: Create a Database with the Database Configuration Assistant (DBCA)

• Select a CDB or Non-CDB Database
• Create a Database with the DBCA
• Read Only Oracle Homes in Oracle Database 18c/19c

Module 4: Preparation for Upgrade to Oracle Database 18c/19c

• Methods Used to Perform an Upgrade
• Direct Upgrade Supported Releases
• Upgrade Process Overview
• Rolling Upgrade
• Methods of Data Migration
• 19c Simplified Upgrade with Auto Upgrade
• Backup the Database
• Create an Upgrade Test Plan
• Carry out Performance Testing
• Run the Pre-Upgrade Information Tool

Module 5: Perform Upgrades to Oracle Database 18c/19c

• Upgrading to Oracle Database 18c/19c
• Manual Upgrade of an Oracle 12cR1 Database
• Execute Preupgrade.jar
• Upgrade a Database with the Database Upgrade Assistant (DBUA)
• Post Upgrade Checks
• Deprecated Database Features and Parameters
• Common Installation Errors
• Check the /etc/Oratab File
• Check the Database Version
• Enterprise Manager Express Configuration
• Post Upgrade Tasks After a Manual Upgrade
• Migrate to Unified Auditing

Module 6: Migrate Data with Data Pump

• Introduction to Migrate Data with Data Pump
• Transport a Database
• Import Data Using a Data Pump Network Link

Module 7: Enhancements to Database Auditing

• Use Mixed Mode Auditing or Migrate to Pure Unified Auditing
• Audit Top Level User Activities in 19c
• Use Oracle Data Pump to Export and Import the Unified Audit Trail

Module 8: Enhancements to Privileges and Profiles

• Automatically Lock Inactive Accounts
• Schema Only Accounts from 18c
• 19c Privilege Analysis with an Enterprise Edition License
• Create, Start, and Stop Privilege Captures
• Query the DBA_PRIV_CAPTURES View
• Execute the Invokers Rights Privilege with the Inherit Privilege
• Control Definer's Rights Privileges for Database Links

Module 9: New RMAN Features

• 12.2 RMAN Syntax Enhancements
• Enhanced Table Point in Time Recovery from 12.2
• Enhanced REPAIR FAILURE Command
• Table or Partition Recovery Enhancements from 12.2
• Recover a Database after Missing Redo Logs Found
• Simplified Duplicate a Database using TDE

Module 10: Miscellaneous New Features

• Cancel a SQL Statement in a Session from 18c
• Use Private Temporary Tables from 18c
• Miscellaneous Data Redaction Enhancements
• 12.2 INMEMORY_SIZE Parameter
• 12.2 INMEMORY Enhancements

Module 11: SQL and PL/SQL Enhancements

• SQL*Plus Enhancements
• login.sql File
• SQL*Plus Command Line History
• SQL*Plus Performance Settings Options
• SQLcl Command Line Interface
• Longer Object Names
• Globalisation Support at Table and Column Level
• Markup CSV
• New Approximate Functions
• LISTAGG DISTINCT from Oracle 19c
• Remove Duplicates from LISTAGG Results Using DISTINCT
• DBMS_SESSION.SLEEP Replaces DBMS_LOCK.SLEEP from 18c
• Real Time Materialised View Enhancements
• PL/SQL Enhancements
• PL/SQL Scope Enhancements
• Deprecate Pragma for PL/SQL
• Code Coverage Using DBMS_PLSQL_CODE_COVERAGE
• Package White Lists Enhancements

Module 12: Performance Tuning New Features

• Overview of Performance Tuning New Features
• Adaptive Query Optimisation Enhancements
• Optimiser Statistics Advisor Rules, Findings, Recommendations, and Actions
• Expression Statistics Store
• Enhancements to the SQL Performance Analyser
• Configure the Automate SPM Evolve Advisor Task to Run Hourly
• Automatic Resolution of SQL Plan Regression
• Scalable Sequences

Module 13: Big Data and Data Warehouse New Features

• Reduce Cursor Invalidations for DDLs
• Automatic Indexing in 19c with DBMS_AUTO_INDEX
• Real Time Statistics Gathering During DML Operations
• Advanced Index Compression
• Automatic SQL Plan Management
• SQL Statements Terminated by the Resource Manager Quarantined
• COUNT(DISTINCT) Rollup
• Polymorphic Tables Enhancements

Module 14: Partitioning Enhancements

• New Oracle Partitioning Strategies in 12.2
• Automatic List Partitioning
• Multiple Column List Partitioning
• Multiple Column with Automatic List Partitioning
• Interval Sub-Partitioning
• Filter Data During Partition Maintenance Operations
• Convert a Non-Partitioned Table to a Partitioned Table
• Read Only Partitions
• Defer Segment Creation for Sub-Partitioning
• Online MERGE PARTITION and MERGE SUBPARTITION in 18c
• Hybrid Partitioned Tables - Integrate Internal and External Partitions

Module 15: Data Pump Enhancements

• Data Pump Parallel Import and Export of Metadata
• Usage of Wildcards in TRANSPORT_DATAFILES
• New Substitution Variables for Filenames
• Parameter File Contents Written to the Log File
• REMAP_DIRECTORY Parameter
• DATA_OPTIONS Parameter Changes
• Miscellaneous New Data Pump Features

Module 16: SQL*Loader and External Table Enhancements

• In-Memory External Tables
• External MODIFY Clause
• Load XML Documents with the XML TAG Clause

Module 17: Database Diagnostic New Features

• TFA Collector
• Install the TFA Collector
• Manage the TFA Daemon
• Quarantined Objects

Module 18: Data Guard New Features

• 12.2 Data Guard New Features
• Create Scripts with the Broker DGMGRL Command Line Interface
• Automatic Password File Management
• Simple Repair of No Logging Operations
• Active Data Guard Redirection
• Automatically Replicate Restore Points from Primary to Standby
• New Broker VALIDATE Commands
• 19c Data Guard New Features
• Export the Broker Configuration File
• Observe Only Mode to Test Fast Start Failover
• Dynamically Change Fast Start Failover Target Using the Data Guard Broker
• Enhanced Control of Alternative Destinations
• New Broker Commands
• Use RMAN and Enterprise Manager to Create Far Sync Instances
• Multi-Instance Redo Apply
• Keep Buffer Cache at Role Change
• 18c Data Guard New Features

Module 19: Active Data Guard New Features

• 12.2 Active Data Guard New Features
• Use all Standby Instances to Apply Redo
• Data Guard Broker Support for DBMS_ROLLING Upgrades
• Generating AWR Report from Active Dataguard
• Control Login Attempts
• New RECOVER STANDBY DATABASE FROM SERVICE
• Execute DML
• Create Global Temporary Tables

Module 20: Transparent Sensitive Data Protection

• Use Cases for TSDP Policies
• TSDP with Unified Auditing
• TSDP with Fine-Grained Auditing
• Create a TSDP Policy with TDE Column Encryption
• TDE Column Encryption Encrypt Clause Settings Used with TSDP Policies
• TSDP Data Dictionary Views

Module 21: Application Continuity Enhancements

• Server Draining Prior to Stopping or Relocating Services
• Oracle Database Recognition of Request Boundaries
• Transparent Application Continuity
• Application Continuity for Java

Oracle SQL Advanced Training​ Course Outline

Module 1: Access Control

• System Privileges and Roles
• Create Users
• Alter User Details
• Grant and Revoke Object Privileges

Module 2: Retrieve Data Using Subqueries

• ANY, ALL, or SOME Operators
• Correlated Subqueries
• In-Line Views
• Exists Operator
• WITH Clause
• Multi-Column Subqueries
• Insert and Update Using a Query
• Correlated Update and Delete

Module 3: Hierarchical Queries

• Introduction Hierarchical Data
• START WITH and CONNECT BY Clauses
• LEVEL pseudo-column
• Sequencing the Output
• Eliminating Nodes and Branches

Module 4: Enhanced Grouping Features

• Review of Basic Grouping Concepts
• ROLLUP and CUBE Extensions
• GROUPING SETS Extension
• Using the GROUPING and GROUPING_ID Functions

Module 5: Analytic Queries

• Ranking Functions
• NTILE and WIDTH_BUCKET Functions
• FIRST and LAST Value Functions
• Analytic Aggregate Functions
• LEAD and LAG Functions
• LISTAGG Function

Module 6: Manage Large Data Sets

• Multi-Table Inserts
• Merging Data into a Table

Oracle SQL & PL/SQL Fundamentals Course Outline

Module 1: Introduction

• Oracle SQL & PL/SQL
• RDBMS
• SQL

Module 2: SQL: Data Definition Language

• Introduction to DDL
• Database
• Views
• Indexes
• Synonyms

Module 3: SQL: Data Manipulation Language

• Introduction to Data Manipulation Language
  • INSERT
  • UPDATE
  • DELETE

Module 4: SELECT Statement

• Basic SELECT Statement
• Comparison Conditions
• Using the OR Condition
• Using the LIKE Condition
• Using the IN Condition
• Using the ANY Condition
• Using the ALL Condition
• ORDER by Clause
• Sorting by Multiple Columns
• Aggregation and Grouping

Module 5: Joins

• Joining Table Using Oracle Syntax
• Unions and Subqueries

Module 6: SUB-QUERIES

• Using a Subquery to Solve a Problem
• Types of Subqueries
  • Single Row Subqueries
  • Multiple Rows Subqueries
  • Correlated Subquery

Module 7: SQL Functions

• Introduction
• Single Row Functions
• Aggregate Functions

Module 8: PL/SQL

• About PL/SQL
• Benefits of PL/SQL
• Benefits of Subprograms
• Features of PL/SQL
• Identifiers
• PL/SQL Blocks
• Syntax for Creating Functions
• Advantages of User-Defined functions in SQL Expressions
• Restrictions on SQL Functions
• Removing Functions
• Oracle PL/SQL Package
• Removing Packages
• PL/SQL Trigger
• Guidelines for Designing Triggers
• DML Trigger Components
• SQL and PL/SQL
• Using PL/SQL for Data Manipulation
• Control Structures
• PL/SQL Data Types
• Errors and Exception Handling

Oracle Accounts Payable Training Course Outline

Module 1: Payables Overview

• Payables Workbenches
• Multiple Organisation Access Control

Module 2: Suppliers

• Suppliers Pages Reference
• Reviewing and Adjusting Suppliers

Module 3: Invoices

• Entering Invoices Overview
• Entering Invoices in the Invoice Workbench
• Matched Purchase Orders and Receipts in the Invoice Workbench
• Prepayments
• Credit and Debit Memos
• Entering Foreign Currency Invoices
• Subject to Income Tax Reporting Requirements
• Recurring Invoices
• Quick Invoices
• Payables Open Interface Import
• Approving Invoices
• Taxes on Invoices
• Invoice Notices
• Reviewing Invoices
• Adjusting Invoices
• Assets Integration
• Projects Integration
• Purchasing Integration
• Invoice Window References

Module 4: Expense Reports and Credit Cards

• Expense Reports
• Procurement Card Management
• Credit Card Management
• Managing Credit Card Outstanding Transactions

Module 5: Payments

• Payables and Receivables Netting
• Creating Single Payments
• Refunds
• Creating Foreign Currency Payments
• Bills Payable
• Update Matured Bills Payable Status Program
• Reviewing and Adjusting Payments
• Reconciling Payments with Oracle Cash Management

Module 6: Accounting

• Accounting in Payables
• Posting in Oracle General Ledger
• Reconciling Payables Activity
• How Payables Populates the GL Interface Table

Module 7: Resource Management and Reports

• Purging Records
• Purge Reports
• Submitting Standard Reports, Programs, and Listings
• Supplier Reports
• Income Tax Forms and Reports
• Invoice Reports and Payment Reports
• Receivables and Payables Netting Reports
• Accounting and Reconciliation Reports
• Key Indicators Report
• Notices
• Listings

Module 8: Automatic Interest and Automatic Offsets

• Payment Batches and Quick Payments
• Manual Payments
• Void Payments and Stop Payments
• Automatic Offsets and Invoice Processing
• Automatic Offsets and Payment Processing

Module 9: Invoice, Payment, and Account Default Charts

• Invoice and Invoice Batch Default Hierarchy Chart
• Payment and Payment Batch Default Hierarchy Chart
• Account Default Hierarchy Chart

Oracle Database 18c Administration Training​ Course Outline

The outline is focused on providing complete knowledge of Oracle Database within modern version 18c. Our professionals have arranged the following modules to provide an understanding from the introduction level to the administrative level. Let’s start from:

Module 1: Oracle Database Architecture Overview

• Oracle Database Server Architecture
• Oracle Database Instance Configuration
• Connecting to the Database Instance
• Oracle Database Memory and Process Structures
• Logical and Physical Database Structures
• Multitenant Architecture
• Automatic Storage Management (ASM)

Module 2: Oracle Database Cloud Overview

• Understand Features and Tooling
• Automated Database Overview

Module 3: Oracle Database Access

• Introductions to SQL*Plus
• SQL Developer
• Database Configuration Assistant (DBCA)

Module 4: Managing Database Instances

• Initialisation Parameter Files
• Starting Up Oracle Database Instances
• Shutting Down Oracle Database Instances
• Automatic Diagnostic Repository (ADR)
• Alert Log and Trace Files
• Dynamic Performance Views
• Data Dictionary Views

Module 5: Oracle Net Services

• Introduction of Oracle Net Listener
• Naming Methods
• Oracle Net Services Administration Tools
• Comparing Dedicated and Shared Server Configurations

Module 6: User Security for Administrator

• Managing Oracle Database and Cloud Users, Privileges, and Roles
• User Authentication
• Creating and Assigning Profiles
• Assigning Quotas to Users
• Applying the Principle of Least Privilege

Module 7: Create a PDBs in Oracle

• Creating PDBs from Seed
• Cloning PDBs
• Unplugging and Plugging in PDBs
• Dropping PDBs

Module 8: Creating Master Encryption Keys for PDBs

• Encryption in Oracle Database Cloud Service
• Introduction of Transparent Data Encryption (TDE)
• CDB and PDB Master Encryption Keys
• Creating and Activating an Encryption Key

Module 9: Create and Manage Tablespaces

• Table Data Storage
• Creating, Altering and Dropping Tablespaces
• Viewing Tablespace Information
• Oracle Managed Files
• Tablespace Encryption by Default in DBCS

Module 10: Managing Storage Space

• Block Space Management
• Types of Segments
• Deferred Segment Creation
• Reclaimable Unused Space
• Table and Row Compression
• Shrinkable Segments
• Resumable Space Allocation

Module 11: Undo Data and Moving Data Overview

• Transactions and Undo Data
• Storing Undo Information
• Comparing Undo Data and Redo Data
• Comparing Local Undo Mode and Shared Undo Mode
• Configuring Undo Retention
• Temporary Undo
• Understand Moving Data
• Oracle Data Pump
• SQL*Loader
• External Tables
• Migrating to Oracle Database Cloud Service

Module 12: Backup and Recovery Concepts

• Categories of Failure
• Instance Recovery
• Complete Recovery
• Flashback Technology

Module 13: Configure Backup and Recovery

• Monitoring the Fast Recovery Area
• Multiplexing Control Files
• Multiplexing Redo Log Files
• Archived Redo Log Files
• Setting ARCHIVELOG Mode

Module 14: Creating Database Backups

• Backup Terminology
• Using Recovery Manager (RMAN)
• Automatic Backups on Database Cloud Service (DBCS)

Module 15: Monitoring and Tuning Database Strategies

• Performance Planning Considerations
• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM)
• Advisory Framework
• Managing Metric Thresholds and Alerts
• Monitoring Wait Events, Sessions, and Services
• Managing Memory Components

Module 16: SQL Tuning Overview

• SQL Tuning Process
• Oracle Optimiser
• Optimiser Statistics
• SQL Tuning Advisor
• SQL Access Advisor
• SQL Performance Analyser

Oracle Program Training with PL/SQL Course Outline

Module 1: PL/ SQL and Its Components

• PL/SQL Overview
• PL/SQL Elements
• Variable and Constants
• Data Types
• Initialising Variable and Assigning Values
• Generating Output to SQL or SQL Developer

Module 2: Program Logic Essentials

• Statement for Program
• Loops for Program

Module 3: Cursors in PL/SQL Overview

• Introduction to Cursor
• Implicit and Explicit Cursors
• Cursor Operations
• Declaring, Opening, and Closing Cursors

Module 4: EXCEPTIONS and NESTED Blocks

• Exception Section Overview
• Types of Exception
• Named and Unnamed System-Raised Exceptions
• User-Declared Exceptions and Application Errors
• Propagation of Exceptions

Module 5: PL/SQL Records and Index-By Tables

• Records and Index-By Tables Overview
• Declaring Record Types
• Nested Records
• PL/SQL Table Built-in Functions

Module 6: Triggers Overview

• Introduction to Triggers
• DML Triggers
• CREATE TRIGGER Statement
• INSTEAD OF Triggers
• Calling Procedures from Triggers
• DDL Triggers
• Privileges Required for Triggers

Module 7: Procedures Overview

• Introduction to Procedures
• CREATE PROCEDURE Statement
• Procedure Parameters
• Invoking Procedures
• Named Association Parameter Passing
• Definer's Right and Invoker's Rights
• Autonomous Transactions
• Privileges Required for Procedures
• Dictionary Information Concerning Procedures

Module 8: Functions Overview 

• Introduction to Functions
• Deterministic and Parallel-Enabled Functions
• Function Result Cache
• Privileges Required for PL/SQL Statement and Operations

Module 9: Packages Overview 

• Introduction to Package
• Creating a Package
• Overloading
• Forward Declaration
• Wrapping Packages

Module 10: PL/SQL Records and Tables in Packages

• Associated Arrays
• NOCOPY Hint
• Bulk Collection
• Bulk Binding DML Statements

Module 11: Cursor Variables Overview

• Use Cursor Variables
• Fetch Rows Overview
• Cursor Variable Attributes
• Refcursor Datatype

Module 12: Managing Dependencies

• Dependent and Referenced Objects
• Recompilation
• Invalidation and Recompilation
• Local and Remote Dependencies

Module 13: Native Compilation and Compile-Time Warnings

• Introduction of Native Compilation and Compile-Time Warnings
• Automatic Program Sublining
• Data Dictionary Information
• Compiler Warning Categories
• DBMS_WARNING Package

Module 14: Oracle - Supplied Packages Overview

• Introduction to Oracle-Supplied Packages
• Understand Packages Types

Oracle Database DBA Performance Tuning & Management Training Course Outline

Module 1: Introduction to Oracle Performance Tuning

• Performance Planning
• Understanding Investment Options
• Understanding Scalability
• System Scalability
• System Architecture
• Hardware and Software Components
• Application Design Principles
• Deploying New Applications
• Instance Tuning
• Performance Principles
• Baselines
• Proactive Monitoring
• Bottleneck Elimination
• SQL Tuning
• Query Optimiser and Execution Plans

Module 2: Using Application Tracing Tools

• End-to-End Application Tracing
• Using the trcsess Utility
• Understanding SQL Trace and TKPROF

Module 3: SQL Optimiser

• Optimiser Operations
• Components of the Query Optimiser
• Optimiser Access Paths
• Joins
• Execution Plans

Module 4: Sorts

• Sort Merge Joins

Module 5: Indexes

• Index Characteristics
• Keys and Columns
• Unique and Nonunique Indexes

Module 6: Advanced Indexes

• Types of Indexes
  • Categories of Indexes

Module 7: Joins Operations

• Joins
  • Join Conditions
  • Types of Join

Module 8: Sequences and Views

• Managing Views
  • Creating Views
  • Replacing Views
  • Using Views in Queries
  • Updating a Join View
  • Altering Views
  • Dropping Views
• Managing Sequences
  • Creating Sequences
  • Altering Sequences
  • Dropping Sequences

Module 9: Using Hints

• Overview of Optimiser Hints

Module 10: Miscellaneous

• SQL Tuning Tips
• Array Size
• Virtual Columns
• WITH Clause
• Shared Pool
• Intelligent Cursor Sharing
• Bind Variable Usage
• Result Caching

Module 11: Basic Tuning Diagnostics

• Performance Tuning Diagnostics, Features, and Tools
• DB Time
• Dynamic Performance Views
• Database Statistics
• Wait Events 
• Using ADDM with Oracle Real Application Clusters

Module 12: Reduce the Cost of SQL Operations

• Identify Invalid Objects
• SQL Access Advisor
• Row Migration
• Manage Extents

Module 13: SQL Performance Analyser

• An Overview of the SQL Performance Analyser
• Capture a SQL Workload
• Create a SQL Performance Analyser Task
• Summary of the AWR Compare Periods Report
• About Database Memory Caches and Other Memory Structures
• Data Dictionary Cache Concepts

Module 14: SQL Performance Management

• Optimiser Statistics Collection
• Gathering Statistics for Fixed Objects
• Optimiser Statistics Advisor
• Expression Statistics
• Adaptive Query Optimisation
• Adaptive Query Plans
• Automatic SQL Tuning
• SQL Tuning Advisor
• SQL Access Advisor
• SQL Plan Management

Module 15: Automatic Memory Management

• Overview of Automatic Shared Memory Management
• Dynamic SGA
• Parameters for Sizing the SGA
• Enable and Disable Automatic Shared Memory Management
• Overview of Automatic Memory Management

Module 16: Tune the Shared Pool

• Basic of Shared Pool
• Latches and Mutexes
• Avoiding Hard Parses
• Pin the SQL in the Shared Pool Area
• SHARED_POOL_SIZE and Automatic Storage Management
• Data Dictionary

Module 17: Tune the Buffer Cache

• About Data Base Buffer Cache
• Database Smart Flash Cache
• Full Database Caching

Module 18: Tune PGA and Temporary Space

• Overview of the PGA
• Oracle Memory Usage
• Automatic PGA Memory Mode Configuration
• Histograms
• Temporary Tablespace Management
• Temporary Tablespace Group
• Multiple Temporary Tablespaces
• Temporary Tablespace Shrink
• Data Dictionary Views

Module 19: Create and Use Snapshots and Baselines with the Automatic Workload Repository

• Automatic Workload Repository
• Create Baselines
• Moving Window Baseline
• Baseline Templates
• Managing Baseline
• Compare Periods Report

Module 20: Use Metrics and Alerts

• Overview of Metrics and Alerts
• Server Generated and Event based Alerts
• Set Thresholds

Module 21: Use AWR-Based Tools

• Automatic Maintenance Tasks
• Configuring Maintenance Windows
• Enabling ADDM in a Pluggable Database
• Generate an ASH Report

Module 22: Real Time Database Operation Monitoring

• Overview of Real Time Database Operation Monitoring
• Database Operation Concepts
• Enable the Monitoring of Database Operations
• Database Operation Views
• Oracle Database 12cR2 New Features

Module 23: Monitor Applications

• Create Services
• End-to-End Application Tracing

Module 24: Big Data and Data Warehouse Features

• Online Statistics Gathering for Bulk Loads
• Real Time Statistics Gathering During DML Operations
• Basic and Advanced Table Compression
• Advanced Index Compression
• Automatic Indexing with DBMS_AUTO_INDEX
• Scalable Sequences
• Quarantine of Runaway SQL Statements

Module 25: Tune Database I/O

• Overview of Database I/O Management
• I/O Calibration
• About Data Files
• Manually Distributing I/O
• Sample Configurations
• Asynchronous and Synchronous I/O
• Automatic Storage Management (ASM)

Module 26: Summary of Oracle Performance Tuning

• Potential Impact of Initialisation Parameters on Performance
• Memory Management
• Tablespace Types
• DB_BLOCK_SIZE
• Size the Redo Log Buffer and the Redo Log Files