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

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: ORACLE DATABASE MULTITENANT ARCHITECTURE OVERVIEW

• Overview of the Oracle Database 19c architecture
• An overview of a Container Database (CDB)
• The benefits of Multitenant Architecture
• The root container and Pluggable Database containers
• The structure of the Root Container
• The structure of a Pluggable Database (PDB)
• CDB_xxx and DBA_xxx Views

Session 2: PREPARE THE SERVER FOR ORACLE DATABASE SOFTWARE INSTALLATION

• Plan an installation
• System requirements and Operating System Prerequisites
• Optimal Flexible Architecture
• Software Pre-installation Tasks
• Carry out Pre-installation Checks
• Set Environment Variables
• Set Permissions to run root Configuration Scripts Automatically

Session 3: INSTALL THE ORACLE DATABASE SOFTWARE

• Interactive Oracle 19c Database Software Installation with the OUI
• The Oracle Inventory
• Silent mode installation
• Create a Response File
• Install with a Response File
• Oracle Database 19c RPM Installation
• 19c Image Based Client Installation
• 19c Automated Installation, Configuration and Patching
• Database Software De-installation

Session 4: CREATE A DATABASE WITH THE DATABASE CONFIGURATION ASSISTANT (DBCA)

• Select a CDB or non-CDB database
• Create a Database with the DBCA
• Create a Database using Silent Mode
• Create a Database using a Response File with DBCA Silent Mode
• Create a Duplicate Database with DBCA Silent Mode from 19c
• Create a 19c Demonstration Database using Silent Mode
• Post Database Creation Tasks
• Delete a database with the DBCA Silent Mode
• Configure Read Only Oracle Homes in Oracle Database 18c/19c

Session 5: PREPARATION FOR UPGRADE TO ORACLE DATABASE

• Upgrade Process Overview
• Direct Upgrade Supported Releases
• Methods used to Perform Indirect Upgrades
• 19c Simplified Upgrade with Autoupgrade
• The Database Upgrade Assistant(dbua)
• Manual Upgrade
• Methods of Data Migration
• Rolling upgrade for Grid Infrastructure
• Create an Upgrade Test Plan
• Carry out Performance Testing
• Run the Pre-Upgrade Information Tool
• Carry out the Recommended Actions
• Check for Compatibility Issues
• Pre-configuration of the ORAchk Utility with Default Values
• Backup the database
• Create the New Oracle Home
• Install the Oracle Database Software
• Oracle 19c Upgrade of Expired Password Accounts

Session 6: PERFORM AN UPGRADE

• The Manual Upgrade of a Database to 19c
• Execute preupgrade.jar
• Upgrade a Database with the Database Upgrade Assistant (DBUA)
• Run the DBUA in Silent Mode
• Use the Oracle Database Autoupgrade Utility
• Post Upgrade Checks
• Manually Start a Failed Upgrade
• Common Installation Errors
• Carry out Post Upgrade Checks
• Check the /etc/oratab File
• Check the Database Version
• Run OPatch Commands
• Enterprise Manager Express Configuration
• Carry Out Post Upgrade Tasks
• Migrate to Unified Auditing
• Deprecated and Desupported Database Parameters
• Plug a Non-Container Database into a 19c Container Database

Session 7: MIGRATE DATA WITH DATA PUMP

• Migrate data with Data Pump
• Transport a database
• Import data using a Data Pump network link

Session 8: ENHANCEMENTS TO DATABASE AUDITING

• Overview of Database Auditing
• Use Mixed Mode Auditing or Migrate to Pure Unified Auditing
• Unified Auditing Roles
• Create Audit Policies
• Audit by Role
• Enhancements to Unified Auditing
• Write Unified Audit Records to SYSLOG
• Changes to Audit Options
• Audit Top Level User Activities
• Purge old Audit Data
• Capture the Predicate Clause when using VPD
• Data Dictionary Enhancements
• Audit a Read-Only Database to the Operating System
• Use Oracle Data Pump to Export and Import the Unified Audit Trail

Session 9: ENHANCEMENTS TO SECURITY PRIVILEGES AND PROFILES

• SYSRAC Administrative Privilege for RAC and Grid Infrastructure
• Administrative User Authentication Enhancements
• The READ Object Privilege
• Automatically Lock Inactive accounts
• Administrative Password Security Enhancements
• Schema Only Accounts from 18c
• Enhanced Security with Password Removal from Schema Only Accounts from 19c
• Grant Administrative Privileges to Schema Only Accounts
• 19c Privilege Analysis Available with an Enterprise Edition License
• Database Privilege Analysis with Database Capture
• Grant the CAPTURE_ADMIN Role to Enable Management of Privilege Captures
• Create, Start and Stop Privilege Captures
• Viewing Privilege Profile Data
• Query the DBA_PRIV_CAPTURES View
• The Default ORA$DEPENDENCY Profile
• Execute the Invokers Rights Privilege with The Inherit Privilege

Session 10: NEW RMAN FEATURES

• Change to the Usage of the SYSBACKUP Privilege
• RMAN Syntax Enhancements
• Execute SQL Statements in RMAN
• Use the RMAN DESCRIBE Command
• The Enhanced SET NEWNAME Command
• Improvements to the Backup and Restore of Very Large Files
• Enhanced Table Point in Time Recovery
• The Enhanced REPAIR FAILURE Command
• Table or Partition Recovery Enhancements
• Recover a Database after Missing Redo Logs Found
• Run the DUPLICATE Operation with the NOOPEN Option
• Simplified Cross Platform Migration

Session 11: MISCELLANEOUS NEW FEATURES

• Automatic Memory Management Restrictions
• Overview of Temporary UNDO
• The Online Rename and Relocation of an Active Data File
• Cancel a SQL Statement in a Session
• Online Redefinition Enhancements from 18c
• Use Private Temporary Tables from 18c
• Miscellaneous Data Masking Enhancements
• Overview of Oracle Data Redaction
• Miscellaneous Data Redaction Enhancements
• INMEMORY_SIZE Parameter Changes and Enhancements

Session 12: SQL ENHANCEMENTS

• SQL*Plus Enhancements
• The sqlcl Utility
• The login.sql File
• SQL*Plus Command Line History
• SQL*Plus Performance Settings Options
• The SQLcl Command Line Interface
• Longer Object Names
• Globalization Support at Table and Column Level
• Configure Extended Data Types
• Markup CSV
• New Approximate Functions
• LISTAGG DISTINCT
• Remove Duplicates from LISTAGG Results using DISTINCT
• Real-Time Materialized View Enhancements
• Approximate Query Processing and Real-Time Materialised Views

Session 13: PL/SQL ENHANCEMENTS

• Using the WITH Clause
• PL/SQL Enhancements
• DBMS_SESSION.SLEEP Replaces DBMS_LOCK.SLEEP
• PL/SCOPE Enhancements
• The Deprecate Pragma for PL/SQL
• Code coverage using DBMS_PLSQL_CODE_COVERAGE
• Control Definer's Rights for Database Links
• Package White Lists Enhancements

Session 14: PERFORMANCE RELATED NEW FEATURES

• Overview of Performance Tuning New Features
• Adaptive Query Optimization in an Oracle Database
• SQL Plan Management (SPM) Overview
• Automatic SQL Plan Capture Enhancements
• Configure the Automatic SPM Evolve Advisor Task to Run Hourly
• Automatic Resolution of SQL Plan Regressions
• The Optimizer Statistics Advisor
  • Rules, Findings, Recommendations and Actions
• The Expression Statistics Store
• Enhancements to the SQL Performance Analyzer
• Monitor the Usage of Indexes

Session 15: BIG DATA AND DATA WAREHOUSE NEW FEATURES

• Reduce Cursor Invalidations for DDLs
• Automatic Indexing with DBMS_AUTO_INDEX
• Real Time Statistics Gathering during DML Operations
• Configure a Lightweight, High Frequency Statistics Gathering Job for Stale Statistics
• Advanced Index Compression
• SQL Statements Terminated by the Resource Manager Quarantined
• SQL Diagnostics and Repair Enhancements
• Bitmap-Based COUNT(DISTINCT) Rollup
• Scalable Sequences
• Polymorphic Tables Enhancements
• HCC Data Compression with an ADO Row-level policy

Session 16: PARTITIONING ENHANCEMENTS

• Oracle Partitioning Overview
• New Oracle Partitioning Strategies prior to 12.1
• Partitioning Strategies Introduced in 12.1
• New Partitioning Strategies from 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 from 18c
• Hybrid Partitioned Tables - Integrate Internal and External Partitions

Session 17: DATA PUMP ENHANCEMENTS

• Oracle Data Pump New Features Introduced from 12c
• Data Pump Parallel Import and Export of Metadata
• The Usage of Wildcards in TRANSPORT_DATAFILES
• New Substitution Variables for Filenames
• Parameter File Contents Written to the Log File
• The REMAP_DIRECTORY Parameter
• DATA_OPTIONS Parameter Changes
• Dynamic Data Pump Tracing
• Miscellaneous New Data Pump Features

Session 18: SQL*LOADER AND EXTERNAL TABLE ENHANCEMENTS

• SQL*Loader Enhancements
• The SDF_PREFIX Parameter
• The HADOOP_TRAILERS Parameter
• Hexadecimal and Multi-byte String Support for SQL*Loader Express
• External Table Enhancements
• InMemory External Tables
• The External MODIFY Clause
• Load XML Documents with the XML TAG Clause

SESSION 19: DATABASE DIAGNOSTIC NEW FEATURES

• Automatic Diagnostic Repository (ADR) New Features
  • Long Date Formats in the alert.log
  • ADR New File Types and Locations
  • The DDL and Debug Logs
  • The ADRCI SHOW LOG Command
  • Set an ADR Retention Target from 12.2
• The UNDO Retention Advisor
• The Trace File Analyzer (TFA) Collector
  • Overview of the TFA
  • Install the TFA Collector Prior to 19c
  • Post Install of the TFA Collector Prior to 19c
  • Download the Latest Version of the TFA for 19c
  • Install the TFA Collector in 19c
  • Automatic Startup of the TFA from the TFA Installation
  • Manage the TFA daemon
  • tfactl Help
  • Start and stop the TFA Collector
  • One Command Service Request Data Collections
  • Quick System and Cluster Summary Reports
• Create a Database Operation
• Quarantined Objects

Session 20: TRANSPARENT SENSITIVE DATA ENCRTPTION (TDE) ENHANCEMENTS

• Overview of New Security Features Support for TDE
• Create, Open and Manage a Keystore
• Table Encryption Enhancements
• Encrypt and Decrypt a Tablespace Online
• Encrypt SYSTEM, SYSAUX and Temporary Tablespaces
• Duplicate a Database that uses TDE
• TSDP Policies for Unified Auditing
• TSDP with Fine-Grained Auditing
• TSDP with TDE Column Encryption
• TDE Column Encryption Encrypt Clause Settings used with TSDP Policies
• TSDP Data Dictionary Views

Session 21: DATA GUARD NEW FEATURES

• Use the DBCA to Create Standby Databases
• Automatic Password File Management
• Synchronous Redo Transport Enhancements
• Simple Repair of NOLOGGING Operations
• Shadow Lost Write Protection
• Perform Data File Block Comparison Between the Primary and a Standby
• Network Based Standby Database Recovery
• Automatic Flashback of a Standby Database
• Flashback a Standby Database when the Primary is Flashed Back
• Automatically Replicate Restore Points from Primary to Standby
• Data Guard Broker New Features
  • New Command Line Interface Properties
  • The V$DATAGUARD_PROCESS View
  • Create Scripts with the Broker DGMGRL Command Line Interface
  • New Broker Commands
  • New VALIDATE Commands
  • Export and Import the Broker Configuration File
  • Configure Multiple Failover Targets
  • Dynamically Change Fast Start Failover Target using the Data Guard Broker
  • Use Observer Only Mode to Test Fast Start Failover
  • Set Multiple Observers

Session 22: ACTIVE DATA GUARD NEW FEATURES

• Connection Preservation During Role Transition
• Keep Buffer Cache at Role Change
• DML Operations on Active Data Guard
• Active Data Guard Redirection
• Network-Based Standby Recovery
• Support for the Diagnostic Pack
• Support for the SQL Tuning Advisor
• Use RMAN and Enterprise Manager to create Far Sync Instances
• Control Login Attempts
• RECOVER STANDBY DATABASE FROM SERVICE
• Create Global Temporary Tables
• Standby NOLOGGING for Load Performance
• Standby NOLOGGING for Data Availability

Session 23: APPLICATION CONTINUITY ENHANCEMENTS

• Application Continuity Overview
• Server Draining Prior to Stopping or Relocating Services
• Oracle Database Recognition of Request Boundaries
• Transparent Application Continuity
• Application Continuity after a Communication Failure

Session A: DEMONSTRATION TABLES

Session B: CONTAINER AND PLUGGABLE DATABASE CRIB SHEET

• Connect to a Container Database as an Administrator
• Connect to a Container Database using a Service Name
• Connect to a Pluggable Database
• Display the Current Container
• Switch between Containers
• Start Up and Shutdown a CDB
• Open and Close a PDB
• Alter a Pluggable Database
• Open and Close All PDBS
• Automatic Start Up of Pluggable Databases
• Preserve or Discard the PDB Open Mode after a CDB Restart
• Change the State of a PDB
• Configure Instance Parameters in a CDB and PDBs

Session C: DATA GUARD QUICK SETUP GUIDE

• Data Guard Physical Standby using the Data Guard Broker
• Primary Server Setup
• Configure Initialization Parameters
• Service Setup
• Standby Server Setup
• Enable the Broker
• Database Switchover
• Database Failover
• Configure the Original Primary as a Standby using Flashback Database
• Configure the Original Primary as a Standby without Flashback
• Read-Only Standby and Active Data Guard
• Snapshot Standby