Oracle DBA Training Program

In this course students learn the basics of writing Oracle SQL statements. This course provides the SQL skills that allow developers, database administrators and end-users to write queries against single and multiple tables, manipulate data in tables and take advantage of Oracle supplied row functions.

Module 00: Oracle 11g/12c: Introduction to SQL Online Training - Course Introduction

Module 01: Writing SQL SELECT Statements

• Review the basic SQL SELECT statement syntax
• Select all columns using a wildcard notation from a table
• Valid SELECT statement expressions
• Write a query containing the arithmetic operators
• Use aliases to customize column headings
• Create a character expression with the concatenation operator
• Use the CASE Expression
• Lab Exercise

Module 02: Writing SQL SELECT Statements Demo

Module 03: Restricting Data

• Use the WHERE Clause
• WHERE clause operators
• =, <>, >, < operators
• BETWEEN, IN, LIKE, IS NULL operators
• Adding ANY or ALL to an operator
• List the Logical Conditions AND, OR, NOT
• Use Multiple Conditions in the WHERE clause
• Describe the Rules of Precedence
• Lab Exercise

Module 04: Restricting Data Demo

Module 05: Sorting Data

• Discuss default order of result sets
• Sort Rows with the ORDER BY Clause
• ORDER BY valid expressions
• DISTINCT and UNIQUE clauses
• Lab Exercise

Module 06: Sorting Data Demo

Module 07: Displaying Data from Multiple Tables

• Identify Types of Joins
• ANSI standard join operators vs. non-standard join methods
• Using the non-standard join methods
• Inner join vs. Outer join
• Generating a Cartesian Product
• ANSI standard join operators
• NATURAL JOIN, JOIN USING, JOIN ON, LEFT/RIGHT/FULL OUTER joins
• Lab Exercise

Module 08: Displaying Data from Multiple Tables Demo

Module 09: Character Row Functions

• Categories of row functions
• Statement behavior of row functions
• Useful / common character row functions
• Nest Functions to perform multiple tasks in one statement
• Lab Exercise

Module 10: Character Row Functions Demo

Module 11: Numeric Row Functions

• Where numeric row functions can and cannot be used
• Helpful numeric row functions in SQL statements
• Functionalities of common numeric row functions
• Explain Implicit and Explicit conversion
• Lab Exercise

Module 12: Numeric Row Functions Demos

Module 13: Date/Time Row Functions

• Where date/time row functions can and cannot be used
• Helpful date/time row functions in SQL statements
• Functionalities of common date/time row functions
• Lab Exercise

Module 14: Date/Time Row Functions Demo

Module 15: Miscellaneous Row Functions

• Using conversion functions
• Using conversion functions to format date/time data
• Environment and Data specific row functions
• The Decode function versus the Case Option
• Lab  Exercise

Module 16: Miscellaneous Row Functions Demo

Module 17: Aggregating Data Using Group Functions

• Categorize the Types of Group Functions
• Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
• Utilize the DISTINCT Keyword with the Group Functions
• Describe how Nulls are handled with the Group Functions
• Create Groups of Data with the GROUP BY Clause
• Group Data by more than one column
• Exclude Groups of Data with the HAVING Clause
• Differences between the HAVING clause and the WHERE clause
• Lab Exercise

Module 18: Aggregating Data Using Group Functions Demo

Module 19: Extensions to the GROUP BY Clause

• Using the CUBE option
• Using the ROLLUP option
• CUBE vs. ROLLUP output
• The GROUPING BY option
• Lab Exercise

Module 20: Extensions to the GROUP BY Clause Demo

Module 21: Manipulating Data I

• Write INSERT statements to add rows to a table
• Insert Special Values
• Copy Rows from Another Table
• Update Rows in a Table
• Use DELETE statements to remove rows from a table
• Delete Rows Based on Another Table
• The COMMIT and ROLLBACK statements
• Using the SAVEPOINT statement
• Lab Exercise

Module 22: Manipulating Data I Demo

Module 23: Manipulating Data II

• Describe the TRUNCATE Statement
• TRUNCATE vs. DELETE
• Using the Multi-table INSERT statement
• Using the MERGE statement
• Show how Read Consistency works
• Lab Exercise

Module 24: Manipulating Data II Demo

Module 25: 12c New Features

• Top-n Query
• Cascading Truncate Statement
• Native Left Outer Join

You need to have equivalent experience or have taken the following GogoTraining course:

• Introduction to Programming and Coding for Everyone with JavaScript

In this course, students learn how to use the advanced features of SQL in order to query and manipulate data within the database. Advanced querying and reporting techniques including using SET operators, writing hierarchical queries and using the Flashback Query feature are explained. Schema objects such as Tables, Indexes and Constraints are discussed in detail. 

In order to take this course you need to have taken Oracle 11g/12c: Introduction to SQL and have experience in writing Oracle SQL statements.

Module 00: Oracle 11g/12c: Advanced SQL Online Training - Course Introduction

Module 01: Using the Set Operators
•    Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
•    Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
•    Describe and use the INTERSECT operator
•    Explain and use the MINUS operator
•    List the SET operator guidelines
•    Order results when using the UNION operator
•    Using the ORDER BY clause with SET operators
•    Lab Exercise

Module 02: Using the Set Operators Demo

Module 03: Flashback Query
•    Prerequisites for using the Flashback Query feature
•    Uses for the Flashback Query feature
•    Limitations of the Flashback Query feature
•    Statement level vs. session level usage
•    Using statement level Flashback Query
•    The AS OF TIMESTAMP option
•    Using session level Flashback Query
•    The DBMS_FLASHBACK Oracle supplied package
•    Lab Exercise

Module 04: Flashback Query Demo

Module 05: Using Subqueries
•    Use a subquery to solve a problem
•    Identify where subqueries can be placed in a SELECT statement
•    Describe the types of subqueries (single row, multiple row)
•    The single row subquery operators
•    The multiple row subquery operators
•    Using multi-column subqueries
•    Writing a correlated subquery
•    Lab Exercise

Module 06: Using Subqueries Demo

Module 07: Hierarchical Retrieval
•    Hierarchical Queries
•    Ranking Rows with LEVEL
•    Formatting Hierarchical Reports Using LEVEL and LPAD
•    Pruning Branches with the WHERE and CONNECT BY clauses
•    Lab Exercise

Module 08: Hierarchical Retrieval Demo

Module 09: Using the Data Dictionary Views
•    Types of Data Dictionary Views
•    Privileges needed to access the Data Dictionary Views
•    Using the Data Dictionary Views to view database metadata information
•    Helpful Data Dictionary Views
•    Lab Exercise

Module 10: Using the Data Dictionary Views Demo

Module 11: Creating Database Objects I (Views)
•    Creating a view syntax
•    View options
•    Privileges needed to create a view
•    Benefits of views
•    Limitations of views
•    Using views in SQL statements
•    Retrieving View definitions from the Data Dictionary
•    Lab Exercise

Module 12: Creating Database Objects I (Views) Demos

Module 13: Creating Database Objects II (Sequences)
•    Creating a sequence syntax
•    Privileges needed to create a sequence
•    Sequence usage
•    Sequence options
•    Using sequences in SQL statements
•    Retrieving Sequence definitions from the Data Dictionary
•    Lab Exercise

Module 14: Creating Database Objects II (Sequences) Demo

Module 15: Creating Database Objects III (Synonyms)
•    Types of synonyms
•    Private vs. Public synonyms
•    Creating a synonym syntax
•    Privileges needed to create a synonym
•    Synonym usage
•    Using synonyms in SQL statements
•    Retrieving Synonym definitions from the Data Dictionary
•    Lab Exercise

Module 16: Creating Database Objects III (Synonyms) Demo

Module 17: Creating Database Objects IV (Tables)
•    Types of Tables
•    The Basic Heap Table
•    Creating a table syntax
•    Valid column datatypes
•    Privileges needed to create a table
•    Retrieving Table definitions from the Data Dictionary
•    Lab Exercise

Module 18: Creating Database Objects IV (Tables) Demo

Module 19: Creating Database Objects V (Indexes)
•    Types of Indexes
•    Benefits of Indexes
•    The B-Tree Index
•    Creating an index syntax
•    Privileges needed to create a index
•    Retrieving Index definitions from the Data Dictionary
•    Lab Exercise

Module 20: Creating Database Objects V (Indexes) Demo

Module 21: Creating Database Objects VI (Constraints)
•    Types of Constraints
•    Primary Key, Foreign Key, Unique, NOT NULL, Check constraints
•    Review constraint purposes
•    Creating a constraint syntax
•    CREATE vs. ALTER syntax
•    Retrieving Constraint definitions from the Data Dictionary
•    Lab Exercise

Module 22: Creating Database Objects VI (Constraints) Demo

Module 23: Regular Expression Support
•    Regular Expression Support Overview
•    Describing simple patterns for searching and manipulating data
•    Describing complex patterns for searching and manipulating data
•    Lab Exercise

Module 24: Regular Expression Support Demo

Module 25: Advanced Oracle: 12c New Features

•    Row Pattern Matching

•    A Sequence Change

•    Invisible Columns

•    Data Type Changes

•    UNION/UNION All Changes

You need to have equivalent experience or have taken the following GogoTraining courses:

• Introduction to Programming for Non-Programmers
• Introduction to Programming and Coding for Everyone with JavaScript
• Oracle 10g/11g: Introduction to SQL
• Oracle 11g/12c: Introduction to SQL

This Oracle 11g PL/SQL programming Part 1 online training course introduces you to PL/SQL and helps you understand the benefits of Oracle’s proprietary database language. You will learn to create PL/SQL blocks of application code that can be shared by multiple users, forms, reports, and data management applications. In addition you will create anonymous PL/SQL blocks as well as named stored procedures, functions, packages and triggers.  This Oracle course also covers learning to develop, execute, and manage PL\SQL stored program units, subprograms, triggers, declaring identifiers and both user-defined and pre-defined exceptions. You will be introduced to the utilization of some of the more useful Oracle-supplied packages.

Once you have completed Oracle 11g PL/SQL Programming Part 1 the next course in this series is Oracle 11g PL/SQL Programming Part 2.  The Oracle 11g PL/SQL Programming Part 2 course will continue to guide you through more intermediate PL/SQL topics.  You will learn how to handle runtime errors, cursor processing, stored procedures and functions, and design and use PL/SQL packages.  Also, you will learn how to manage dependencies between stored units, manage security, and create automatic triggers to solve business requirement.  Click here to learn more about Oracle 11g PL/SQL Programming Part 2.

Module 00: Oracle 11g PL/SQL programming Part 1 online training - Course Introduction

Module 01: PL/SQL Overview Part 1

• Define PL/SQL
• Identify Advantages of Using PL/SQL
• Basic PL/SQL Commands
• Discuss where PL/SQL is used
• Exercise

Module 02: PL/SQL Overview Part 2

• Understand PL/SQL Block Structure
• List the Four Main Parts of a PL/SQL Block
• State the Purpose of each Part
• Write Anonymous Blocks
• Nested Blocks
• Use DBMS_OUTPUT.PUT_LINE Procedure
• Exercise

Module 03: I/O Within PL/SQL and Overview of Sample Environment

• I/O Within PL/SQL
• Overview of Sample Environment
• Exercise: PL/SQL Overview

Module 04: Datatypes and Scalar Variables

• Datatypes
• Declare Scalar Variables

Module 05: Composite Variables

• Create Composite Variables
• Assign and update Values for Variables
• Reference PL/SQL Table Variables

Module 06: Datatypes and Scalar Variables Exercise and Exercise Review

Module 07: SQL Statements in PL/SQL

• Select Data from the Database into PL/SQL Variables
• The INTO Clause
• Using SQL in PL/SQL: SELECT
• Exercise Review

Module 08: DML Commands and Transaction Processing

• DML Commands
• Using RETURNING Clause
• Transaction Processing Statements: COMMIT, ROLLBACK, and SAVEPOINT

Module 09: DML Commands and Transaction Processing Exercise and Exercise Review

Module 10: IF and CASE Statements

• Use IF..THEN..ELSE Logic in PL/SQL
• Use the CASE Statement
• Exercise: IF and CASE
• Exercise Review

Module 11: Loops and Labels

• Labels and GOTO Statement
• Looping Statements
• Simple Loops, Numeric FOR Loops, WHILE Loops
• Exercise: Loops
• Exercise Review
• What Should I Know about Conditional Logic and Loops?

Module 12: Exception Handling

• Exception Handling
• Define and Code Exceptions
• The OTHERS Exception

Module 13: Pre-Defined Exceptions

• Pre-Defined Exceptions
• Exercise: Pre-Defined Exceptions
• Exercise Review

Module 14: Using SQL code, SQLERRM and User-defined Exceptions

• SQL code
• SQLERRM
• User-defined exceptions

Module 15: User-Defined Exceptions Exercise and Exercise Review

• What Should I Know about Exceptions?

o Identify and code exceptions
o Use pre-defined exceptions
o Declare exceptions
o Write exception handlers
o Implement exception handler for OTHERS exception
o Create and handle user-defined exceptions
o Report error messages - both user and system

Module 16: Exception Propagation and Displaying Errors

• Exception Propagation
• Using Nested Blocks
• Using RAISE_APPLICATION_ERROR
• Display errors with DBMS_UTILITY

Module 17: Exception Propagation and Displaying Errors Exercise and Exercise Review

You need to have equivalent experience or have taken the following GogoTraining courses:

• Oracle 10g/11g: Introduction to SQL
• Oracle 10g/11g: Advanced SQL
• Oracle 11g/12c: Introduction to SQL
• Oracle 11g/12c: Advanced SQL

Oracle 11g PL/SQL Programming Part 2 online training picks up right where Part 1 completes and continues to guide you through more intermediate PL/SQL topics. Through hands-on exercises, you learn to handle all types of runtime errors, use explicit cursor processing including cursor FOR loops, create and use stored procedures and functions, and design and use PL/SQL packages as well as understand the benefits of using packages. This Oracle programming course teaches you to manage dependencies between these PL/SQL stored units, and understand and manage security for them as well. Also covered in this Oracle training is learning how to automate processing by creating triggers to solve business requirements including 11g compound triggers.

In order to take this course you need to have taken Oracle 11g PL/SQL Programming Part 1 and have experience in some high-level programming such as SQL.

Module 00: Oracle 11g PL/SQL Programming Part 2 online training - Course Introduction

Module 01: Cursor Basics

• Learn How Oracle uses Cursors to Process SQL
• Identify when to use an Explicit Cursor
• Describe an Implicit Cursor
• Use Cursor OPEN, FETCH, and CLOSE Statements
• Identify and Use Cursor Attributes
• Use Parameters with Cursors
• Use Cursor FOR Loops

Module 02: Cursor Basics Exercise and Exercise Review

Module 03: Advanced Cursor Topics

• Using %ROWTYPE
• Cursor FOR Loops
• Cursors with Parameters

Module 04: Advanced Curser Topics Part 2

• Using "Where Current of" Option
• Exercise Cursors
• Exercise Review
• What Should I Know about Cursor Processing?

Module 05: Stored Procedures and Functions Basics

• Create Stored Procedures
• Create Stored Functions
• Use a Function in a SQL Statement
• Shared SQL Area
• Pass Parameters to Procedures and Functions
• Locate Source Code in the Database

Module 06: Stored Procedures and Functions Basics Exercise and Exercise Review

Module 07: Passing Parameters to Stored Procedures and Functions

• Using Parameters
• Formal Parameter Modes

Module 08: Passing Parameters to Stored Procedures and Functions Exercise and Exercise Review

Module 09: Dependencies and Security

• Source Code and Dependencies
• security for Stored Program Units
• Implementing Invoker's Rights
• DETERMINISTIC Functions

Module 10: Stored Procedure and Functions Exercise and Exercise Review

• What Should I Know about Stored Procedures and Functions?

Module 11: Trigger Basics

• Triggers
• Define Characteristics of Database Triggers

Module 12: Trigger Basics Part 2

• Row vs. Statement Triggers
• Trigger Types
• Understand the Difference between Statement Level and Row Level Triggers
• Identify Restrictions for Triggers

Module 13: Advanced Trigger Concepts

• Use Trigger Predicates
• Retrieve Trigger Definitions from the Data Dictionary
• Enable and Disable Triggers
• Execution Order of Triggers- Using FOLLOWS clause Compound Triggers (11g)
• Other Events and Triggers

Module 14: Triggers Exercise and Exercise Review

• What Should I Know about Triggers?

Module 15: PL/SQL Packages

• Identify Advantages of Using Packages
• Define a Package Specification
• Define a Package Body

Module 16: PL/SQL Packages Part 2

• Implement Public and Private Elements
• Understand Persistent State of Packaged Objects
• Execute Procedures and Functions in a Package

Module 17: Packages Exercise and Exercise Review

• What Should I Know about Packages?

You need to have equivalent experience and have taken Oracle 11g PL/SQL Programming Part 1 or taken the following GogoTraining courses:

• Oracle 10g/11g: Introduction to SQL
• Oracle 10g/11g: Advanced SQL
• Oracle 11g/12c: Introduction to SQL
• Oracle 11g/12c: Advanced SQL
• Oracle 11g PL/SQL Programming Part 1

In this Oracle database administration course, Part 1 of a two part online training series, you will explore how an Oracle Database applies 'cloud computing' to multiple computers to provide fast, efficient, secure data storage and manipulation that easily scales to thousands of clients. In this Oracle training, students learn how to install, administer and maintain an Oracle 12c database in an effective and efficient manner. Topics such as the interaction among Oracle components, an understanding of Oracle's architecture and user management are covered. This course will assist those preparing to take the Oracle Certified Associate (OCA) certification examination.

Once you have completed Oracle 12c: Database Administration I Part 1 the next course is this series is Oracle 12c: Database Administration I Part 2. The Oracle 12c: Database Administration I Part 2 course will continue to build your knowledge in shared server, database performance monitoring, database maintenance, how to use the undo management feature, how to deal with locking conflicts and finishing up with backups and database recovery. Click here to learn more about Oracle 12c: Database Administration I Part 2.

Module 00: Course Introduction - Oracle 12c: Database Administration I Part 1

Module 01: Database Architecture

• Oracle Architecture
• Memory Components
• Background Processes
• Quiz: Database Architecture

Module 02: Oracle Physical and Logical Structures

• Oracle Logical Storage Structures
• Oracle Physical Storage Structures

Module 03: Oracle and ASM

• ASM Instance
• ASM parameters
• ASM Components
• Quiz: Oracle and ASM

Module 04: Container Databases and Pluggable Databases

• Define Container databases
• Define Pluggable databases

Module 05: Oracle 12c Installing ASM

• Define ASM
• Install ASM
• Lab Exercise: Create and Install ASM 
• Lab Exercise Solution: Create and Install ASM Demo

Module 06: Installing the Database Software

• Define database software requirements
• Install Oracle database Software
• Lab Exercise: Installing the Database Software 
• Lab Exercise Solution: Installing the Database Software Demo

Module 07: Creating a Container Database

• Define Database Requirements
• Create a Container Database using DBCA

Module 08: Creating a Traditional Database 

• Define Database Requirements
• Create a Traditional Database using DBCA
• Lab Exercise: Create a Database 
• Lab Exercise Solution: Create a Database Demo

Module 09: Oracle 12c Database Tools

• Define SQLPLUS
• Use Enterprise Manager Express
• Demo: Use Enterprise Manager Express 

Module 10: Manage your Database with SQL Developer

• Manage Database with SQL Developer
• Understanding SQL Developer
• Demo: Use SQL Developer

Module 11: Manage your Database with Cloud Control

• Manage your database with Cloud Control
• Cloud Control Architecture
• Demo: Use Cloud Control

Module 12: Managing the Database Instance

• Define the components of the SGA
• Identify initialization parameters

Module 13: Start/Stop the Database 

• Start/Stop the database
• Identify the Location of the Alert.log file
• Use Dynamic Performance Views
• Quiz: Managing the Database Instance

Module 14: Maintaining Data Files

• Create tablespaces
• Create Container Databases
• Create Pluggable databases
• Lab Exercise: Create Container Databases, Pluggable Database and Tablespaces 

Module 15: Container Database Architecture

• Identify CDB_* views
• Utilize CDB security

Module 16: Database Security

• Identify Oracle Default accounts
• Create Database Users
• Create database Roles
• Assign database privileges
• Lab Exercise: Create DB Users 

Module 17: Database Profiles

• Identify Database Profiles
• Uses for Database Profiles
• Create Database Profiles

Module 18: Database Undo Components

• Identify Oracle's Undo Segments
• Identify Undo Parameters
• How Undo and Flashback Query Work Together
• Quiz: Database Undo Components

Module 19: Database Networking Components Part 1

• Identify the networking components
• Set up a Listener

Module 20: Database Networking Components Part 2

• Create a tnsnames files
• Use Oracle's EZ Connect

Module 21: Oracle Database Concurrency

• Identify Oracle's Locking Mechanism
• Monitor Row Locks
• Resolve Row Locks

In order to successfully complete Oracle 12c Database Administraion 1 Part 1 you will need to have equivalent experience in or have taken:

• Oracle 10g/11g: Introduction to SQL
• Oracle 10g/11g: Advanced SQL
• Oracle 11g PL/SQL Programming Part 1
• Oracle 11g PL/SQL Programming Part 2

In this Oracle database administration online training course, Part 2 of a two part series, you will explore how an Oracle Database applies 'cloud computing' to multiple computers to provide fast, efficient, secure data storage and manipulation that easily scales to thousands of clients. Students will learn how to install, administer and maintain an Oracle 12c database in an effective and efficient manner. Training topics such as performance monitoring, database security and backup and recovery techniques are covered. This Oracle training will assist those preparing to take the Oracle Certified Associate (OCA) certification examination.

Once you have completed Oracle 12c Database Administration I Part 2 the next course you will want to take is Oracle 12c Database Administration II Part 1. The Oracle 12c Database Administration II is a 2 part series that will enhance your knowledge further with more advance Oracle database topics. Click here to learn more about Oracle 12c Database Administration II Part 1 and Part 2.

Module 00: Course Introduction - Oracle 12c: Database Administration I Part 2

Module 01: Oracle 12c Enterprise Express

• Install Enterprise Express
• Use Enterprise Express
• Lab Exercise: Use Enterprise Manager Express (DEMO)

Module 02: Oracle 12c Cloud Control

• Identify the components of Cloud Control
• Install Cloud Control
• Set up targets within Cloud Control

Module 03: Undo Tablespaces

• Create undo tablespaces
• Manage Undo Tablespaces
• Change Undo Tablespaces
• Lab Exercise: Create Undo Tablespaces
• Lab Exercise: Change Undo Tablespaces

Module 04: Database Auditing

• Define Database Auditing
• Identify Database Auditing Parameters
• Set up database auditing
• Lab Exercise: Setup Database Auditing

Module 05: Database Backups

• Backup concepts
• User managed Backups
• Oracle Managed Backups
• Archivelog Mode and No Archivelog Mode
• Lab Exercise: Place the Database in Archivelog Mode

Module 06: RMAN Concepts

• Identify Backupsets
• Identify Image Copies
• Incremental Backups
• Quiz: RMAN Concepts

Module 07: RMAN Backups

• Create RMAN Backups command Line
• Create RMAN backups with Cloud Control
• Lab Exercise Perform a database backup

Module 08: Database Recovery

• User Managed Complete Recovery
• User Managed Incomplete Recovery
• RMAN complete Recovery
• RMAN incomplete Recovery
• Quiz: Database Recovery

Module 09: Additional Database Recovery

• Definition of non-critical loss
• Definition of Critical Loss
• Using Data Recovery Advisor
• Quiz: Data Recovery Advisor

Module 10: Oracle Data Pump

• Define Oracle's Data Pump Architecture
• Data Pump features
• Perform a data pump export
• Perform a data pump import
• Lab Exercise: perform a database export
• Lab Exercise: perform a database import

Module 11: Oracle Scheduling

• Define the Scheduling components
• Identify the differences between a: (Job, Program, Schedule, Window)
• Identify how Oracle performs resource management with  Job Scheduling
• Create Schedule
• Create a Job Chain

Module 12: Oracle 12 Performance Tuning

• Identify the components of the SGA for tuning
• Identify tuning parameters
• Oracle 12c Tuning Tools

Module 13: Advisory Framework

• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitory (ADDM)
• Real Time ADDM
• Lab Exercise:  Create and run an AWR report.

Module 14: Tuning Framework Tools

• SQL Tuning Advisor
• SQL Access Advisor
• Quiz: Tuning Advisor Framework

Module 15: Plan Management

• Identify SQL Baselines
• Use SQL Baselines
• Identify SQL Plan Management
• Use SQL Plan Management

Module 16: Tune SQL Statements

• Use DBMS_STATS
• Set DBMS_STATS parameters
• Create invisible indexes

In order to successfully complete Oracle 12c Database Administraion 1 Part 2 you will need to have equivalent experience in or have taken:

• Oracle 12c: Database Administration I Part 1
• Oracle 10g/11g: Introduction to SQL
• Oracle 10g/11g: Advanced SQL
• Oracle 11g PL/SQL Programming Part 1
• Oracle 11g PL/SQL Programming Part 2

In this Oracle database administration course, Part 1 of a two part online training series, you will explore how an Oracle Database applies 'cloud computing' to multiple computers to provide fast, efficient, secure data storage and manipulation that easily scales to thousands of clients. In this Oracle training, students learn how to perform complete and incomplete backup and recovery operations, utilize Oracles new FLEX ASM technology, and utilize Oracles new 12 tuning features such as heat map real time ADDM.

Once you have completed Oracle 12c: Database Administration II Part 1 the next course is this series is Oracle 12c: Database Administration II Part 2. The Oracle 12c: Database Administration II Part 2 course will continue to build your knowledge in backup and recovery operations, Oracle’s flashback technology, and advanced resource management.

Module 00: Course Introduction - Oracle 12c Database Administration II Part 1

Module 01: Installing the Database Software and Creating a Database

• Demo: Installing the Database
• Demo: Creating a Database
• Lab Exercise: Installing the Database Software
• Lab Exercise: Create a Database

Module 02: Multitenant Architecture

• Define Oracle’s Multitenant Architecture
• Root Containers
• Container databases/Pluggable databases

Module 03: Managing Container Databases

• Connect to a container/pluggable databases
• Startup/Shutdown a container database
• Preservation of PDB mode upon restart of CDB
• Lab: Start/Stop a container database
• Lab: Preserve Pluggable open mode
• Lab Demo Solution: Start/Stop a container database
• Lab Demo Solution: Preserve Pluggable open mode

Module 04:  Container Users and Roles

• Create container users
• Create Container Roles
• Lab: Create local users
• Lab: Create local roles
• Lab Demo Solution: Create local users
• Lab Demo Solution: Create local roles

Module 05: Container Profiles and Privileges

• Create Container Profiles
• Grant local privileges
• Lab: Grant local privileges
• Lab Demo Solution: Grant local privileges

Module 06: ASM Overview

• ASM Overview
• ASM Privileges
• ASM Components
• ASM Quiz

Module 07: ASM Instance Management

• ASM Instance Administration
• ASM Instance Parameters
• ASM diskgroups
• ASM Startup and Shutdown

Module 08: FLEX ASM

• FLEX ASM Architecture
• FLEX ASM Overview
• Configuring Flex ASM
• Managing Flex ASM
• Quiz Flex ASM

Module 09: ASM Files and Directories

• Using asmcmd
• ASM Directory Structure
• Creating ASM files
• Creating ASM directories
• Lab: Using asmcmd
• Lab Demo Solution: Using asmcmd

Module 10: Oracle Cloud File System

• Cloud File System Overview
• Cloud File System Components
• Using ACFS with Oracle ASM
• Oracle Cloud File System Quiz

Module 11: Fast Recovery Area

• Configure fast recovery area
• Configure flashback database
• Lab: Archivelog and flashback mode
• Lab Demo Solution: Archivelog and flashback mode

Module 12: Recovery Catalog

• Definition of recovery catalog
• Recovery catalog meta data
• Create the recovery catalog

Modules 13: Recovery Catalog Scripts and Databases

• Create recovery catalog scripts
• List target databases
• Synchronizing the recovery catalog

Module 14: Administering the Recovery Catalog

• Backup the recovery catalog
• Configure recovery catalog settings
• Create a virtual private catalog
• Recovery Catalog Quiz

Module 15: RMAN Backups Part 1

• Backup sets
• Image copies
• Non-compressed backups
• Compressed backups
• Incremental backups
• Backup ASM files
• Backup the Recovery Area
• Lab: Database backup

Module 16: Lab Demo Solution: Database Backup

Module 17: RMAN Backups Part 2

• RMAN encrypted backups
• Oracle Transparent Data Encryption
• Password Encryption
• Dual Mode Encryption
• RMAN Backups Quiz

Module 18: RMAN Critical Recovery

• Recover a critical data file
• Incremental recovery
• Image copy recovery
• Recovery to an alternate location
• Oracle’s Data Recovery Advisor
• Oracle Block Corruption

Module 19: Demo: Recover a non-critical file

Module 20: Diagnosing Backup Issues

• Using Oracles ADR
• Using RMAN DEBUG feature
• Reading Backup messages and Output
• Diagnosing Backups Quiz

Module 21: RMAN Recovery Concepts

• Instance Recovery
• Complete Database Recovery
• Incomplete database Recovery
• Recovery through Resetlogs.
• RMAN Recovery Quiz

Module 22: RMAN Recovery

• Recovery in NoArchivelog mode
• Recovery in Archivelog Mode
• Recovery of ASM disk Groups
• Point in Time recovery
• Table recovery using RMAN
• Lab:  Perform Complete Recovery
• Lab Demo Solution: Demo on Complete Recovery

Module 23: Additional Recovery Concepts

• Parameter file recovery
• Control file Recovery
• Non-critical loss recovery
• Disaster Recovery
• Encrypted backup recovery
• Recovery Concepts Quiz

You need to have equivalent Oracle 9i database experience, taken training in 12c: SQL and PL/SQL and have taken the following courses from GogoTraining:

• Oracle 12c: Database Administration I Part 1
• Oracle 12c: Database Administration I Part 2

In this Oracle database administration course, Part 2 of a two part online training series, you will explore how an Oracle Database applies 'cloud computing' to multiple computers to provide fast, efficient, secure data storage and manipulation that easily scales to thousands of clients. In this Oracle training, students learn how to perform Oracle’s Flashback Technology, Recyclebin, Flashback Data Archives, and how to tune backup and recovery operations and utilize Oracle’s tuning tools such as AWR, SQL tuning Advisor and SQL Access Advisor.

Once you have completed Oracle 12c: Database Administration II Part 2 the next course is this series would be Oracle Enterprise Manager 12c Cloud Control and Oracle APEX.

Module 00: Course Introduction - Oracle 12c: Database Administration II Part 2

Module 01: Installing the Database Software and Creating a Database

• Demo: Installing the Database
• Demo: Creating a Database
• Lab Exercise: Installing the Database Software
• Lab Exercise: Create a Database

Module 02: Flashback Technology

• Flashback Overview
• Flashback Terms
• Flashback Table
• Lab: Perform a Flashback Table
• Lab Demo Solution: Perform a flashback Table

Module 03: Flashback Recyclebin and Query Backout

• Recyclebin Concepts
• Retrieve Objects From the recyclebin
• Define Protected Objects in the recyclebin
• Query Backup Concepts
• Transaction Backout
• Lab: Retrieve a table from the recyclebin
• Lab Demo Solution: Retrieve a Table From the recyclebin
• Lab: Perform Transaction Backout
• Lab Demo Solution: Perform Transaction Backup

Module 04: Flashback Data Archives

• Define Flashback Data Archives
• Manage Flashback Data Archives
• Flashback Data Archives Administration
• Flashback Quiz

Module 05: Flashback Database

• Configure Flashback Database
• Monitor Flashback Database
• Perform Flashback Database
• Lab: Configure Flashback Database
• Lab Demo Solution: Configure Flashback Database

Module 06: Database Duplication Part 1

• Define Database Duplication
• Database Duplicate Techniques

Module 07: Database Duplication Part 2

• More on Database Duplicate Techniques
• Using Cloud Control to Perform Database Duplication
• Using RMAN to Perform Database Duplication
• RMAN Duplicate Quiz

Module 08: Tuning RMAN

• Parallel Backups
• RMAN Multiplexing
• RMAN Performance Bottlenecks
• Recovery and Restore Best Practices
• Tuning RMAN Quiz

Module 09: Overview Oracle Database Architecture

• Oracle Database Memory Structures: Overview
• Automatic Shared Memory Management
• Database Storage Architecture, Logical and Physical Database Structures
• Segments, Extents, and Blocks & SYSTEM and SYSAUX Tablespaces

Module 10: Query Optimizer

• SQL Parsing
• Optimizing Terms
• Optimizing Methods
• Query Plan Generation
• Query Plan Control
• Query Optimizer Quiz

Module 11: Oracle 12c Tuning Features

• Identifying and Using Oracle's Heat Map
• 12c Compression Levels and Types

Module 12: Oracle Tuning Tools Part 1

• Monitoring tools overview
• Enterprise Manager
• Dynamic Performance Views
• Automatic Database Diagnostic Monitor

Module 13: Oracle Tuning Tools Part 2

• Automatic Workload Repository
• SQL Tuning Advisor
• Lab: Using SQL Tuning Advisor

Module 14: Lab Demo Solution: SQL Tuning Advisor

Module 15: Using Automatic Workload Repository

• Defining AWR
• AWR Settings
• Creating AWR Snapshots
• Lab Create AWR Report
• Lab Demo Solution: Create AWR Report

Module 16: Metrics, Alerts, and Thresholds

• Defining Metrics
• Setting Alerts
• Setting Corrective Action Thresholds
• Using Defined Metrics
• Dynamic Views Associated to Metrics and Alerts
• Lab Create Metrics and Alerts
• Lab Demo Solution: Create Metrics and Alerts

Module 17: AWR Baselines

• Creating AWR baselines
• Creating AWR Repeating baselines
• Moving Window Baseline
• Lab Create AWR Baseline
• Lab Demo Solution: AWR Baselines

Module 18: Additional AWR Performance Tools

• Automatic Maintenance Tasks
• Segment Advisor
• Statistics Gathering
• Automatic Tuning Optimizer
• Automatic Database Diagnostic Monitor
• Active Session History (ASH)

Module 19: Optimizer Statistics

• Optimizer Statistics Overview
• Table and Index Statistics
• Statistic Preferences and Gathering
• Locking Statistics, Export/Import Statistics
• Pending and Published Statistics
• Optimizer Hints and Paths
• Cost Base Optimization
• Lab: Reading Query Plan
• Lab Demo Solution: Reading Query Plan

Module 20: SQL Tuning Advisor

• SQL Tuning Advisor: Overview
• SQL Tuning Advisor Limited Mode
• SQL Tuning Advisor Comprehensive mode
• SQL Tuning Profiles
• Lab Create  a SQL Tuning Profile

Module 21: Lab Demo Solution: Create a SQL Tuning Profile

Module 22: SQL Access Advisor

• SQL Access Advisor: Overview
• SQL Access Advisor options
• SQL Access Advisor and SQL Tuning Sets
• SQL Access Advisor and AWR
• Results and Implementation
• Lab Use SQL Access Advisor

Module 23: Lab Demo Solution: Use SQL Access Advisor

Module 24: Automatic SQL Tuning

• Automatic SQL Tuning Maintenance Task
• Automatic Tuning Optimization Implementation(ATO)
• Automatic Tuning Optimization Results
• Enable/Disable Automatic Tuning Optimization
• Lab: Enable ATO
• Lab Demo Solution: Enable ATO

Module 25: SQL Plan Management

• SQL Plan Management and Baseline Overview
• Enable SQL Plan Management
• Loading SQL Plan Baselines into the SGA
• Adaptive Plan Management

Module 26: Shared Pool Tuning

• Shared Pool Architecture
• Shared Pool Parameters
• Library Cache
• Dictionary Cache
• Large Pool Considerations and Contents

Module 27: Tuning the Database Buffer Cache Part 1

• Database Buffer Cache Overview
• Database Buffer Cache Parameters
• Oracle and Dirty Reads and Writes
• Automatic Shared Memory Management (ASMM)

Module 28: Tuning the Database Buffer Cache Part 2

• Buffer Cache Goals and Responsibility
• Buffer Cache Pools
• Buffer CacheTuning Quiz

Module 29: Automatic Memory Management (AMM)

• Oracle's Automatic Memory Management Overview
• Database Autotuned Parameters
• Database Non Autotuned Parameters
• Automatic Memory Management Hints and Sizing suggestions
• AMM versus ASMM
• AMM Quiz

Module 30: Tuning Segment Space Utilization (ASSM)

• Overview of Automatic Segment Space Management
• Defining the DB_BLOCK_SIZE
• Defining DB_nk_CACHE_SIZE parameter
• The DB_BLOCK_SIZE Parameter
• Overview of Table Compression, Block Chaining, and Block Migration
• ASSM Quiz

You need to have equivalent Oracle 9i database experience, taken training in 12c: SQL and PL/SQL and have taken the following courses from GogoTraining:

• Oracle 12c: Database Administration I Part 1
• Oracle 12c: Database Administration I Part 2
• Oracle 12c: Database Administration II Part 1