Oracle Database 12c: New Features for Administrators

 

Course ORA12CNF: 5 Day; Instructor-led

Course Description

This course provides a complete, hands-on introduction to the newest release of Oracle Enterprise Edition. Key features of this product are fully discussed and demonstrated.

Target Audience

This course is appropriate for anyone needing to learn key new features of Oracle12c. That would include end users, business analysts, application developers, database administrators and IT management.

Prerequisites

A thorough knowledge of Oracle11g database administration is required.  Full familiarity with Oracle11g PL/SQL is highly desirable.

Course Content 

CHAPTER 1 – SQL ENHANCEMENTS

MAJOR ENHANCEMENTS

·         Top N And Bottom N Queries

·         Using Extended Datatypes

·         Concurrent UNION / UNION ALL Execution

·         Sequences With DEFAULT Values

·         Sequences With Global Temporary Tables

·         IDENTITY Columns

·         New Sequence Functionality For Oracle Replay

·         Invisible Columns

·         Temporal Validity

·         Pattern Matching

MISCELLANEOUS NEW SQL FEATURES

DATA MODEL REVIEW

LAB 1:  SQL ENHANCEMENTS

LAB 1 SOLUTIONS: SQL ENHANCEMENTS

CHAPTER 2 – PL/SQL ENHANCEMENTS

WHITE LISTS

THE WITH CLAUSE FOR FUNCTIONS

PRAGMA UDF

NEW INQUIRY DIRECTIVES

NEW DBMS_SQL FUNCTIONALITY

ENHANCED LANGUAGE INTERFACE FEATURES

MISCELLANEOUS NEW PL/SQL FEATURES FOR ORACLE12C

LAB 2:  PL/SQL ENHANCEMENTS

LAB 2 SOLUTIONS: PL/SQL ENHANCEMENTS

CHAPTER 3 – INSTALLATION / UPGRADING

OVERVIEW OF DATABASE CREATION

THE ORACLE UNIVERSAL INSTALLER

PREREQUISE TASKS

·         Memory Requirements

·         Hardware Requirements

·         Software Requirements

·         Package Requirements

·         What About Windows?

RUNNING THE INSTALLER

POST INSTALLATION

USING THE DATABASE CONFIGURATION ASSISTANT

USING THE DATABASE CONFIGURATION ASSISTANT – SHORT FORM

DATABASE TEMPLATES

UPGRADING THE DATABASE

REMOVING ORACLE SOFTWARE

LAB 3: INSTALLATION / UPGRADING

LAB 3 SOLUTIONS: INSTALLATION / UPGRADING

CHAPTER 4 – ENTERPRISE MANAGER ENHANCEMENTS

DATABASE MANAGEMENT USING EMDE

ENVIRONMENTAL VARIABLES

ACCESSING ENTERPRISE MANAGER DATABASE EXPRESS

·         MODIFIED FUNCTIONALITY WITH EMDE Changing Parameters

·         Modification Of Physical Structures

 

DELETED FUNCTIONALITY IN EMDE

DATABASE ADMINISTRATION WITH OTHER TOOLS

EMDE ACCESS FOR NON-DBA USERS

EMDE ACCESS TO PLUGGABLE DATABASES

LAB 4:  ENTERPRISE MANAGER ENHANCEMENTS

LAB 4 SOLUTIONS: ENTERPRISE MANAGER ENHANCEMENTS

CHAPTER 5 – SQL DEVELOPER / SQL*PLUS

SQL DEVELOPER

MULTITENANT CONNECTION CONSIDERATIONS

USING SQL DEVELOPER

SQL*PLUS FOR ORACLE12C

·         Invisible Columns

·         New SHOW Commands

·         Last Login Time

·         Support For The New Administrative Privileges

·         Support For Pluggable Databases

LAB 5:  SQL DEVELOPER / SQL*PLUS

LAB 5 SOLUTIONS: SQL DEVELOPER / SQL*PLUS

CHAPTER 6 – MULTITENANT DATABASES

THE IMPACT OF MULTITENANT ARCHITECTURE

MAJOR BENEFITS OF MULTITENANT ARCHITECTURE

THE BIRD’S EYE VIEW

WALKING THE DIRECTORIES OF A CONTAINER DATABASE

WHO? WHAT? WHEN? WHERE? HOW?

IMPACTS OF MULTITENANT ARCHITECTURE

TOOLS FOR USE WITH MULTITENANT ARCHITECTURE

·         SQL*Plus

·         DBCA

·         Oracle Enterprise Manager Cloud Control

·         SQL Developer

·         Server Control (SRVCTL)

LAB 6: MULTITENANT DATABASES

LAB 6 SOLUTIONS: MULTITENANT DATABASES

CHAPTER 7 – MULTITENANT BASICS

CREATING CDBS

FEATURES UNSUPPORTED FOR MULTITENANT ARCHITECTURE

THE CREATE DATABASE COMMAND

Seed Data Files

CONFIGURING ENTERPRISE MANAGER DATABASE EXPRESS FOR CDBS

USING SQL DEVELOPER WITH CDBS AS A DBA

LAB 7:  MULTITENANT BASICS

LAB 7 SOLUTIONS: MULTITENANT BASICS

CHAPTER 8 – INTERMEDIATE MULTITENANT

OPTIONS AVAILABLE

THE CREATE PLUGGABLE DATABASE STATEMENT

·         The STORAGE Clause

·         File Locations Clause

·         Temp File Clause

PREPARING TO CREATE PDBS

CREATING PDBS USING THE CDB SEED

·         Specifics Using SQL*Plus

CLONING AN EXISTING LOCAL PDB

·         Specific Cloning Steps

CLONING AN EXISTING REMOTE PDB

·         Specific Cloning Steps

·         Effects Of Cloning A Remote PDB

UNPLUGGING A PDB

CREATING A PDB BY PLUGGING IN AN UNPLUGGED PDB

CREATING A PDB FROM A NON-CDB DATABASE

·         Using DBMS_PDB

NEW TRIGGER TYPES FOR PDBS

·         The BEFORE UNPLUG Trigger

·         The AFTER CLONE Trigger

LAB 8:  INTERMEDIATE MULTITENANT

LAB 8 SOLUTIONS: INTERMEDIATE MULTITENANT

CHAPTER 9 – ADVANCED MULTITENANT

USING DATA PUMP EXPORT AND IMPORT

USING EXP AND IMP

DROPPING PDBS

USING CLOUD CONTROL TO CREATE AND DROP PDBS

ADMINISTRATION DETAILS

·         Special Roles For CDBs

·         Other Administrative Considerations For CDBs

·         Manageability Considerations

·         Objects In CDBs

·         Services

·         Sessions

·         Common Listeners

·         Connecting To PDBs In MOUNT Mode

·         Login Triggers And The SET CONTAINER Clause

·         Package States And Containers

·         EXECUTE IMMEDIATE And SET CONTAINER

EXECUTING ACROSS ALL PLUGGABLE DATABASES USING DBMS_SQL

LOGGING ERRORS FOR MULTIPLE CONTAINERS

ALTER DATABASE IN A CDB

TABLESPACES IN CDBS

USING FORCE ON PDBS

CLOSE VS. CLOSE IMMEDIATE

SPECIFYING PDBS IN ALTER PLUGGABLE DATABASE

LAB 9:  ADVANCED MULTITENANT

LAB 9 SOLUTIONS: ADVANCED MULTITENANT

CHAPTER 10 – ULTIMATE MULTITENANT

THE STARTUP COMMAND WITHIN PDBS

STARTUP PLUGGABLE DATABASE

SHUTDOWN WITHIN PDBS

ALTER SYSTEM SET STATEMENTS IN CDBS

THE CONTAINER CLAUSE IN DDL STATEMENTS

RUNNING SCRIPTS IN ALL CONTAINERS

AUTOMATING TNSNAMES.ORA MAINTENANCE

LAB 10:  ULTIMATE MULTITENANT

LAB 10 SOLUTIONS: ULTIMATE MULTITENANT

CHAPTER 11 – MULTITENANT ROUNDUP

RENAMING A PLUGGABLE DATABASE

MOVING A PDB

BUILDING A UNIFIED PARAMETER QUERY

LAB 11:  MULTITENANT ROUNDUP

LAB 11 SOLUTIONS: MULTITENANT ROUNDUP

CHAPTER 12 – STORAGE AND SECURITY

NEW SUPER USERS

ENHANCED SECURITY FOR CDBS

TEMPORARY TABLESPACE CONSIDERATIONS FOR CDBS

HARDENING A CDB

STORAGE CLAUSES RELEVANT TO PDBS

ALTER SYSTEM IN A PDB

SCOPE = SPFILE IN PDBs

SERVICES AND PDBs

VIEWS IN CDBs

CON_ID DETAILS

OTHER INTERESTING CDB VIEWS

CDB VIEW EXAMPLES

NEW FUNCTIONS FOR CONTAINERS

TRIGGERS IN A CDB

MOVING DATAFILES ONLINE

RAW PARTITIONS

DATABASE SMART FLASH CACHE SUPPORT

LAB 12:  STORAGE AND SECURITY

LAB 12 SOLUTIONS: STORAGE AND SECURITY

CHAPTER 13 – BACKUP AND RECOVERY FOR CDBS

CONTAINER DATABASE CONSIDERATIONS

BACKUP SETUP

BACKING UP THE ENTIRE CDB

BACKING UP JUST THE ROOT CONTAINER

BACKING UP PDBS

BACKING UP TABLESPACES AND DATAFILES FROM WITHIN A PDB

EXECUTING RMAN ACROSS PDBS

REDO LOG, ARCHIVE LOG AND CONTROLFILE BACKUPS IN CDBS

COMPLETE RECOVERY OF A CDB

COMPLETE RECOVERY OF A PDB

COMPLETE RECOVERY OF THE ROOT CONTAINER

INCOMPLETE RECOVERY OF THE ENTIRE CDB

INCOMPLETE RECOVERY OF PDBS

LOSS OF SYSTEM TABLESPACE IN A PDB

RMAN RESTRICTIONS IN PDBS

PRIOR BACKUPS AND ARCHIVELOGS FOR CONVERTED NON-CDBS

FLASHBACK AND CDBS

DATA PUMP AND CDBS

LAB 13:  BACKUP AND RECOVERY FOR CDBS

LAB 13 SOLUTIONS: BACKUP AND RECOVERY FOR CDBS

CHAPTER 14 – AUTOMATIC DATA OPTIMIZATION

STORAGE AND COMPRESSION TIERING

THE HEAT MAP

AUTOMATIC DATA OPTIMIZATION

COMPRESSION LEVELS

CHAPTER 15 – IN-DATABASE ARCHIVING

TO KEEP OR NOT TO KEEP

THE ROW ARCHIVAL CLAUSE

ORA_ARCHIVE_STATE

ROW ARCHIVAL VISIBILITY

LAB 15:  IN-DATABASE ARCHIVING

LAB 15 SOLUTIONS: IN-DATABASE ARCHIVING

CHAPTER 16 – AUDITING ENHANCEMENTS

UNIFIED AUDITING

ENABLING UNIFIED AUDITING

DISABLING UNIFIED AUDITING

HANDLING EARLIER AUDIT RECORDS

CREATING A SEC_ADMIN ACCOUNT

CREATING A UNIFIED AUDIT POLICY

AUTOMATIC AUDITS WITH UNIFIED AUDITING

DISABLING / DROPPING AUDIT POLICIES

IMMEDIATE-WRITE VS. QUEUED-WRITE MODE

AUDIT DATA CLEANUP

LAB 16:  AUDITING ENHANCEMENTS

LAB 17 SOLUTIONS: AUDITING ENHANCEMENTS

CHAPTER 17 – PRIVILEGE ENHANCEMENTS

CONSIDERATIONS REGARDING PASSWORD CASE SENSITIVITY

PROFILES AND PASSWORD POLICIES

ROLES IN ORACLE12C

SEPARATION OF DUTY

INHERIT [ ANY ] PRIVILEGES

PERMISSION CONTROL WITHIN VIEWS

DATABASE PRIVILEGE ANALYSIS

·         Restrictions On Privilege Analysis

·         Potential Benefits Of Privilege Analysis

·         Privilege Analysis In Container Databases

·         Privilege Analysis Policies

·         Disabling A Privilege Analysis Policy

·         Generating A Privilege Analysis Report

·         Dropping A Privilege Analysis Policy

SIGNIFICANT CHANGES TO ROLES AND PRIVILEGES

LAB 17:  PRIVILEGE ENHANCEMENTS

LAB 17 SOLUTIONS: PRIVILEGE ENHANCEMENTS

CHAPTER 18 – DATA REDACTION

OVERVIEW OF DATA REDACTION

EXEMPTION FROM REDACTION POLICIES

HOW AND WHEN REDACTION OCCURS

REDACTION EXAMPLES

FUNCTION_PARAMETERS FOR PARTIAL REDACTION

USING REGULAR EXPRESSIONS

USING RANDOM VALUES IN REDACTION

THE EXPRESSION PARAMETER

ENABLE / DISABLE / DROP REDACTION POLICIES

USING RES FOR PARTIAL VARCHAR2 REDACTIONS

REDACTION_POLICIES / REDACTION_COLUMNS

LAB 18:  DATA REDACTION

LAB 18 SOLUTIONS: DATA REDACTION

CHAPTER 19 – RMAN / FDA ENHANCEMENTS

NEW BACKUP SUPER USER

RMAN CONNECTIONS

SELECT AND DESCRIBE FROM WITHIN RMAN

SIMPLER SQL COMMANDS

DUPLICATE WITH NOOPEN

CROSS-PLATFORM BACKUP AND RESTORE

RECOVERY OVER A NETWORK

STORAGE SNAPSHOT TECHNOLOGY

TABLE AND PARTITION RECOVERY

MULTISECTION BACKUPS ON VERY LARGE FILES

ACTIVE DATABASE DUPLICATION ENHANCEMENTS

TABLESPACE TRANSPORTS ACROSS PLATFORMS AND ENDIANNESS

·         Convert At Source

·         Restore From Converted At Source

·         Convert At Target

·         Restore From Converted At Target

STANDBY DATABASE UPDATING

DATA GUARD ENHANCEMENTS

ORACLE SCHEDULER SCRIPT JOBS

LAB 19:  RMAN / FDA ENHANCEMENTS

LAB 19 SOLUTIONS: RMAN / FDA ENHANCEMENTS

CHAPTER 20 – DB OPERATIONS

DATABASE OPERATIONS

DO MONITORING USE CASES

ENABLING DATABASE OPERATIONS MONITORING

DB OPERATIONS MONITORING ARCHITECTURE OVERVIEW

DO ATTRIBUTES

USING DBMS_SQL_MONITOR

·         BEGIN_OPERATION Function

·         END_OPERATION Procedure

·         REPORT_SQL_MONITOR Function

·         SIMPLE DATABASE OPERATIONS MONITORING EXAMPLE

·         THE ACTIVE REPORT TYPE

NEW OPTIMIZER HINTS

LAB 20:  DB OPERATIONS

LAB 20 SOLUTIONS: DB OPERATIONS

CHAPTER 21 – SCHEMA AND DATA CHANGES

DATABASE CHANGES

CHANGE MANAGEMENT CAPABILITIES

THE DBMS_COMPARISON PACKAGE

THE CREATE COMPARISON PROCEDURE

THE COMPARE FUNCTION

DISCOVERING DIFFERENCES

THE CONVERGE PROCEDURE

THE RECHECK FUNCTION

THE DBA_COMPARISON_COLUMNS VIEW

LAB 21:  SCHEMA AND DATA CHANGES

LAB 21 SOLUTIONS: SCHEMA AND DATA CHANGES

CHAPTER 22 – SQL TUNING

ADAPTIVE EXECUTION PLANS

DBMS_XPLAN AND ADAPTIVE EXECUTION PLANS

SQL PLAN DIRECTIVES / EXTENDED STATISTICS

ENHANCED STATISTICS GATHERING

HISTOGRAM ENHANCEMENTS

LAB 22:  SQL TUNING

LAB 22 SOLUTIONS: SQL TUNING

CHAPTER 23 – REAL-TIME ADDM / PERFORMANCE

THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR

REAL-TIME ADDM

EMERGENCY MONITORING / CURRENT ADDM FINDINGS

MONITORING THE DATABASE WITH EMDE

USING THE PERFORMANCE HUB

·         The Summary Tab

·         The RAC Tab

·         The Activity Tab

·         The Workload Tab

·         The Monitored SQL Tab

·         The ADDM Tab

·         The Current ADDM Findings Tab

·         The Database Time Tab

·         The Resources Tab

·         The System Statistics Tab

·         Workload Repository Reports

·         Modifying Automatic Snapshot Settings

USING OTHER ADVISORS

·         SQL Tuning Advisor Details

·         The Automatic SQL Tuning Advisor

·         SQL Access Advisor

DATABASE REPLAY

WORKLOAD INTELLIGENCE OVERVIEW

WORKLOAD DIMENSIONS

REPORTS

SECUREFILES

PGA_AGGREGATE_LIMIT

REAL APPLICATION TESTING

PATCH INVENTORY PACKAGE

STATISTICS ENHANCEMENTS

·         Automatic Column Group Detection

·         Concurrent Statistics Gathering

·         Incremental Statistics Enhancements

·         Global Temporary Tables Session-Private Statistics

·         Reporting Mode

·         Past Statistics Collection Reports

LAB 23:  REAL-TIME ADDM / PERFORMANCE

LAB 23 SOLUTIONS: REAL-TIME ADDM / PERFORMANCE

CHAPTER 24 – RESOURCE MANAGER FOR CDBS

RESOURCE MANAGER AND CDBS

·         CDB Resource Plans

·         The Initial Default Directive Attributes For PDBs

·         PDB Resource Plans

·         Background And Administrative Tasks

·         Creating The Resource Plan For The CDB

·         Updating The CDB Resource Plan

·         Adding A New Plan Directive For A PDB

·         Deleting A PDB’s Plan Directive

·         Enabling The CDB Resource Plan

·         Disabling The CDB Resource Plan

·         Deleting The Entire CDB Resource Plan

·         Creating / Modifying PDB Resource Plans

·         Enabling / Disabling PDB Resource Plans

·         Resource Manager Views

·         Enabling Resource Manager In PDBs

·         Using Cloud Control With Resource Manager

RUNAWAY QUERY MANAGEMENT

DBMS_SCHEDULER WITH A CDB

LAB 24:  RESOURCE MANAGER FOR CDBS

LAB 24 SOLUTIONS: RESOURCE MANAGER / PERFORMANCE

CHAPTER 25 – TABLE / INDEX ENHANCEMENTS

CREATING MULTIPLE INDEXES ON COLUMNS

ADVANCED ROW COMPRESSION

ONLINE REDEFINITION ENHANCEMENTS

DML_LOCK_TIMEOUT

EDITIONING ENHANCEMENTS

CHAPTER 26 – ADR / NETWORK ENHANCEMENTS

ADR DIRECTORY STRUCTURE

ADR COMMAND INTERPRETER ENHANCEMENTS

ADVANCED NETWORK COMPRESSION

MULTIPROCESS / MULTITHREADED ARCHITECTURE

VERY LARGE NETWORK BUFFERS

CHAPTER 27 – DATA LOADING ENHANCEMENTS

DATA PUMP ENHANCEMENTS

·         Data Pump VIEWS_AS_TABLES

·         Transform Clause Enhancements

·         New Parameters

LOG MINER ENHANCEMENTS

FULL TRANSPORTABLE EXPORT / IMPORT

SQL*LOADER ENHANCEMENTS

·         SQL*Loader Express Mode

·         SQL*Loader Enhancements

·         SQL*Loader / External Table Common Enhancements

CHAPTER 28 – PARTITIONING ENHANCEMENTS

MOVING PARTITIONS ONLINE

INTERVAL-REFERENCE PARTITIONING

ENHANCED CASCADE FUNCTIONALITY

OPERATING ON MULTIPLE PARTITIONS

PARTIAL INDEXES

GLOBAL INDEX MAINTENANCE

CONTROLLING STATISTICS GATHERING FOR PARTITIONS