INTRODUCTION TO ORACLE DATABASE

• List the features of Oracle Database 12c
• Discuss the basic design, theoretical, and physical aspects of a relational database
• Categorize the different types of SQL statements
• Describe the data set used by the course
• Log on to the database using SQL Developer environment
• Save queries to files and use script files in SQL
• Developer

RETRIEVE DATA USING THE SQL SELECT STATEMENT

• List the capabilities of SQL SELECT statements
• Generate a report of data from the output of a basic SELECT statement
• Select All Columns
• Select Specific Columns
• Use Column Heading Defaults
• Use Arithmetic Operators
• Understand Operator Precedence
• Learn the DESCRIBE command to display the table structure

LEARN TO RESTRICT AND SORT DATA

• Write queries that contain a WHERE clause to limit the output retrieved
• List the comparison operators and logical operators that are used in a WHERE clause
• Describe the rules of precedence for comparison and logical operators
• Use character string literals in the WHERE clause
• Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
• Sort output in descending and ascending order

USAGE OF SINGLE-ROW FUNCTIONS TO CUSTOMIZE OUTPUT

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC, and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the DATE functions

INVOKE CONVERSION FUNCTIONS AND CONDITIONAL EXPRESSIONS

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

AGGREGATE DATA USING THE GROUP FUNCTIONS

• Use the aggregation functions to produce meaningful reports
• Divide the retrieved data in groups by using the GROUP BY clause
• Exclude groups of data by using the HAVING clause

DISPLAY DATA FROM MULTIPLE TABLES USING JOINS

• Write SELECT statements to access data from more than one table
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself by using a self-join

USE SUB-QUERIES TO SOLVE QUERIES

• Describe the types of problem that sub-queries can solve
• Define sub-queries
• List the types of sub-queries
• Write single-row and multiple-row sub-queries

THE SET OPERATORS

• Describe the SET operators
• Use a SET operator to combine multiple queries into a single query
• Control the order of rows returned

DATA MANIPULATION STATEMENTS

• Describe each DML statement
• Insert rows into a table
• Change rows in a table by the UPDATE statement
• Delete rows from a table with the DELETE statement
• Save and discard changes with the COMMIT and ROLLBACK statements
• Explain read consistency

USE OF DDL STATEMENTS TO CREATE AND MANAGE TABLES

• Categorize the main database objects
• Review the table structure
• List the data types available for columns
• Create a simple table
• Decipher how constraints can be created at table creation
• Describe how schema objects work

OTHER SCHEMA OBJECTS

• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

CONTROL USER ACCESS

• Differentiate system privileges from object privileges
• Create Users
• Grant System Privileges
• Create and Grant Privileges to a Role
• Change Your Password
• Grant Object Privileges
• How to pass on privileges?
• Revoke Object Privileges

MANAGEMENT OF SCHEMA OBJECTS

• Add, Modify, and Drop a Column
• Add, Drop, and Defer a Constraint
• How to enable and Disable a Constraint?
• Create and Remove Indexes
• Create a Function-Based Index
• Perform Flashback Operations
• Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
• Query External Tables

MANAGE OBJECTS WITH DATA DICTIONARY VIEWS

• Explain the data dictionary
• Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Query the dictionary views for constraint information
• Query the dictionary views for view, sequence, index and synonym information
• Add a comment to a table
• Query the dictionary views for comment information

MANIPULATE LARGE DATA SETS

• Use Subqueries to Manipulate Data
• Retrieve Data Using a Subquery as Source
• Insert Using a Subquery as a Target
• Usage of the WITH CHECK OPTION Keyword on DML Statements
• List the types of Multitable INSERT Statements
• Use Multitable INSERT Statements
• Merge rows in a table
• Track Changes in Data over a period of time

DATA MANAGEMENT IN DIFFERENT TIME ZONES

• Time Zones
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Compare Date and Time in a Session's Time Zone
• DBTIMEZONE and SESSIONTIMEZONE
• Difference between DATE and TIMESTAMP
• INTERVAL Data Types
• Use EXTRACT, TZ_OFFSET and FROM_TZ
• Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

RETRIEVE DATA USING SUB-QUERIES

• Multiple-Column Subqueries
• Pairwise and Non-pairwise Comparison
• Scalar Subquery Expressions
• Solve problems with Correlated Subqueries
• Update and Delete Rows Using Correlated Subqueries
• The EXISTS and NOT EXISTS operators
• Invoke the WITH clause
• The Recursive WITH clause

REGULAR EXPRESSION SUPPORT

• Use the Regular Expressions Functions and Conditions in SQL
• Use Meta Characters with Regular Expressions
• Perform a Basic Search using the REGEXP_LIKE function
• Find patterns using the REGEXP_INSTR function
• Extract Substrings using the REGEXP_SUBSTR function
• Replace Patterns Using the REGEXP_REPLACE function
• Usage of Sub-Expressions with Regular Expression Support
• Implement the REGEXP_COUNT function

ORACLE DATABASE 11G : ADMINISTRATION - 1

• Oracle Database Architecture Overview
• Oracle ASM Architecture Overview
• Process Architecture
• Memory structures
• Logical and physical storage structures
• ASM storage components

INSTALLING YOUR ORACLE SOFTWARE

• Tasks of an Oracle Database Administrator
• Tools Used to Administer an Oracle Database
• Installation: System Requirements
• Oracle Universal Installer (OUI)
• Installing Oracle Grid Infrastructure
• Installing Oracle Database Software
• Silent Install

CREATING AN ORACLE DATABASE

• Planning the Database
• Using the DBCA to Create a Database
• Password Management
• Creating a Database Design Template
• Using the DBCA to Delete a Database

MANAGING THE ORACLE DATABASE INSTANCE

• Start and stop the Oracle database and components
• Use Oracle Enterprise Manager
• Access a database with SQL*Plus
• Modify database installation parameters
• Describe the stages of database startup
• Describe database shutdown options
• View the alert log
• Access dynamic performance views

MANAGE THE ASM INSTANCE

• Set up initialization parameter files for ASM instance
• Start up and shut down ASM instances
• Administer ASM disk groups

CONFIGURING THE ORACLE NETWORK ENVIRONMENT

• Use Enterprise Manager to create and configure the Listener
• Enable Oracle Restart to monitor the listener
• Use tnsping to test Oracle Net connectivity
• Identify when to use shared servers and when to use dedicated servers

MANAGING DATABASE STORAGE STRUCTURES

• Storage Structures
• How Table Data Is Stored
• Anatomy of a Database Block
• Space Management in Tablespaces
• Tablespaces in the Preconfigured Database
• Actions with Tablespaces
• Oracle Managed Files (OMF)

ADMINISTERING USER SECURITY

• Database User Accounts
• Predefined Administrative Accounts
• Benefits of Roles
• Predefined Roles
• Implementing Profiles

MANAGING DATA CONCURRENCYS

• Data Concurrency
• Enqueue Mechanism
• Resolving Lock Conflicts
• Deadlocks

MANAGING UNDO DATA

• Data Manipulation
• Transactions and Undo Data
• Undo Data Versus Redo Data
• Configuring Undo Retention

IMPLEMENTING ORACLE DATABASE AUDITING

• Describe DBA responsibilities for security
• Enable standard database auditing
• Specify audit options
• Review audit information
• Maintain the audit trail

DATABASE MAINTENANCE

• Manage optimizer statistics
• Manage the Automatic Workload Repository (AWR)
• Use the Automatic Database Diagnostic Monitor (ADDM)
• Describe and use the advisory framework
• Set alert thresholds
• Use server-generated alerts
• Use automated tasks

PERFORMANCE MANAGEMENT

• Performance Monitoring
• Managing Memory Components
• Enabling Automatic Memory Management (AMM)
• Automatic Shared Memory Advisor
• Using Memory Advisors
• Dynamic Performance Statistics
• Troubleshooting and Tuning Views
• Invalid and Unusable Objects

BACKUP AND RECOVERY CONCEPTS

• Part of Your Job
• Statement Failure
• User Error
• Understanding Instance Recovery
• Phases of Instance Recovery
• Using the MTTR Advisor
• Media Failure
• Archive Log Files

PERFORMING DATABASE BACKUPS

• Backup Solutions: Overview
• Oracle Secure Backup
• User-Managed Backup
• Terminology
• Recovery Manager (RMAN)
• Configuring Backup Settings
• Backing Up the Control File to a Trace File
• Monitoring the Flash Recovery Area

MOVING DATA

• Describe ways to move data
• Create and use directory objects
• Use SQL*Loader to move data
• Use external tables to move data
• General architecture of Oracle Data Pump
• Use Data Pump export and import to move data

WORKING WITH SUPPORT

• Use the Enterprise Manager Support Workbench
• Work with Oracle Support\
• Log service requests (SR)
• Manage patches
• Use the Enterprise Manager Support Workbench
• Work with Oracle Support\
• Log service requests (SR)
• Manage patches

ORACLE DATABASE 11G : ADMINISTRATION - 2

• The Oracle Database Architecture: Overview
• ASM Storage Concepts
• Connecting to the Database and the ASM Instance
• DBA Tools Overview

CONFIGURING FOR RECOVERABILITY

• Purpose of Backup and Recovery (B&R), Typical Tasks and Terminology
• Using the Recovery Manager (RMAN)
• Configuring your Database for B&R Operations
• Configuring Archivelog Mode
• Configuring Backup Retention
• Configuring and Using a Flash Recovery Area (FRA)

USING THE RMAN RECOVERY CATALOG

• Tracking and Storing Backup Information
• Setting up a Recovery Catalog
• Recording Backups
• Using RMAN Stored Scripts
• Managing the Recovery Catalog (Backup, Export, Import, Upgrade, Drop and Virtual Private Catalog)

CONFIGURING BACKUP SETTINGS

• Configuring and Managing Persistent Settings for RMAN
• Configuring Autobackup of Control File
• Backup optimization
• Advanced Configuration Settings: Compressing Backups
• Configuring Backup and Restore for Very Large Files (Multisection)

CREATING BACKUPS WITH RMAN

• RMAN backup types

RESTORE AND RECOVERY TASK

• Restoring and Recovering
• Causes of File Loss
• Automatic Tempfile Recovery
• Recovering from the Loss of a Redo Log Group
• Recovering from a Lost Index Tablespace
• Re-creating a Password Authentication File
• Complete and Incomplete Recovery
• Other Recovery Operations

USING RMAN TO PERFORM RECOVERY

• Complete Recovery after Loss of a Critical or Noncritical Data File
• Recovering Image Copies and Switching Files
• Restore and Recovery of a Database in NOARCHIVELOG Mode
• Incomplete Recovery
• Performing Recovery with a Backup Control File
• Restoring from Autobackup: Server Parameter File and Control File
• Restoring and Recovering the Database on a New Host

MONITORING AND TUNING RMAN

• Monitoring RMAN Jobs
• Balance Between Speed of Backup Versus Speed of Recovery
• RMAN Multiplexing
• Synchronous and Asynchronous I/O
• Explaining Performance Impact of MAXPIECESIZE, FILESPERSET, MAXOPENFILES and BACKUP DURATION

DIAGNOSING THE DATABASE

• Data Recovery Advisor (DRA)
• Block Corruption
• Automatic Diagnostic Repository (ADR)
• Health Monitor
• The ADR Command-Line Tool, ADRCI

USING FLASHBACK TECHNOLOGY I

• Flashback Technology: Overview and Setup
• Using Flashback Technology to Query Data
• Flashback Table
• Flashback Transaction Query
• Performing Flashback Transaction Backout

USING FLASHBACK TECHNOLOGY II

• Oracle Total Recall
• Flashback Drop and the Recycle Bin

PERFORMING FLASHBACK DATABASE

• Configuring Flashback Database
• Performing Flashback Database Operations
• Monitoring Flashback Database

MANAGING MEMORY

• Oracle Memory Structures
• Oracle Database Memory Parameters
• Using Automatic Memory Management
• Automatic Shared Memory Management
• Using Memory Advisors
• Using Data Dictionary Views

MANAGING DATABASE PERFORMANCE

• Tuning Activities
• Using Statistic Preferences
• Optimizer Statistics Collection
• Monitor the Performance of Sessions and Services
• Automatic Workload Repository (AWR)
• Describing the Benefits of Database Replay

MANAGING PERFORMANCE BY SQL TUNING

• SQL Tuning and SQL Advisors
• Using SQL Tuning Advisor
• SQL Access Advisor
• SQL Performance Analyzer Overview

MANAGING RESOURCES

• Database Resource Manager: Overview and Concepts
• DAccessing and Creating Resource Plans
• DCreating Consumer Group
• DSpecifying Resource Plan Directives, including

AUTOMATING TASKS WITH THE SCHEDULER

• Simplifying Management Tasks
• Creating a Job, Program, and Schedule
• Using Time-Based, Event-Based, and Complex Schedules
• Describing the Use of Windows, Window Groups, Job Classes, and Consumer Groups
• Multi-Destination Jobs

MANAGING SPACE IN BLOCKS

• Free Space Management
• Monitoring Space
• Compressing Data

MANAGING SPACE IN SEGMENTS

• Segment Creation on Demand
• Additional Automatic Space-Saving Functionality
• Shrinking Segments
• Segment Advisor
• Managing Resumable Space Allocation

DUPLICATING A DATABASE

• Purpose and Methods of Cloning a Database
• Using RMAN to Create a Duplicate Database
• Cloning a Database from a Backup
• Duplicate a Database Based on a Running Instance
• Targetless Duplicating a Database