Oracle DBA 19c & 12c – New Features

Course Duration: 40 Hours

Required Prerequisites

Target Audience

  • Fresher who wants to make career as Oracle Database Administrator.
  • Oracle DBA who wants to upgrade skills from Oracle DBA 11g/12c  to Oracle DBA 19c

Course Highlights

  • Oracle Database 19c / 12c Multitenant Architecture
  • Installation of Oracle Database 19c.
  • Creation of Container Database using DBCA.
  • Creation of PDB using DBCA and SQL Plus.
  • Managing CDB and PDBs
  • Managing Tablespaces, Common and Local Users, Privileges and roles
  • Data Pump , SQL Loader , External Table Enhancement
  • Managing Backup , Recovery and Flashback.
  • Online Datafile Move and Automatic Data Optimization
  • In-Database Archiving
  • Auditing Enhancements
  • Privileges Enhancements
  • Oracle Data Redaction
  • General RMAN New Features
  • ADR and Network Enhancements
  • Resource Manager and Other Performance Enhancements
  • Index  , Table and Partitions Enhancements
  • Upgrade Database from 12c to 19c

Course Content

Section 1: Basics of Multitenant Container Database (CDB)

Benefits of the multitenant architecture

Differences between the root container and pluggable database containers

Structure of the root

Structure of Pluggable Database (PDB)

CDB_xxx and DBA_xxx views

Section 2: Configuring and Creating CDB and PDBs

Tools: DBCA and SQL*Plus

Installation of and creation of CDB on Oracle Enterprise Linux (OEL)

Exploring the Structure (CDB_xxx views)

Tools used: SQL Developer, DBCA and SQL*Plus

Create a new PDB from PDB$SEED

Create a PDB from a non-CDB

Clone a PDB into the same CDB or into another CDB using DB links

Plug in an unplugged PDB

Section 3: Managing CDB and PDBs

Connect to CDB as administrator

Connect to a PDB using a service name

Start up a CDB

Open / Close a PDB (SQL*Plus, srvctl, EM)

Open / Close all PDBs

Shut down a CDB

Preserve or discard the PDBs open mode when the CDB restarts

Change PDB state

Section 4: Managing Tablespaces, Common and Local Users, Privileges and Roles

Create permanent tablespaces in the root and PDBs

Manage common and local schemas and users in the root and PDBs

Manage system and object privileges to common and local grantees granted “commonly“ or “locally“

Manage common and local roles granted “commonly” or “locally”

Section 5: Managing Backup / Recovery / Flashback

Backup a whole CDB

Backup a PDB

Recover CDB from redo log files, control files, undo datafiles loss

Recover PDB from PDB datafiles loss

Flashback at CDB level

Explain the two methods of migration

Section 6: Online Datafile Move and Automatic Data Optimization

Data classification in 12c: tablespace, group, object, row levels

Configure heat map

Automatic movement and compression

Compression levels and types

Policy declaration: simple declarative SQL extension

Customized automated action execution with user-defined function

Execution in scheduled maintenance windows and by MMON

Customized schedule with DBMS_ILM package

Section 7: In-Database Archiving

Challenges of old data in tables and 11g solutions

In-database archiving new solutions

Use ROW ARCHIVAL clause to enable row lifecycle state for applications

Set ROW ARCHIVAL VISIBILITY for session level visibility control

Use predicate on ORA_ARCHIVE_STATE column

Temporal Validity versus Temporal History (Transaction Time of FDA)

New clause of CREATE / ALTER TABLE to set a Temporal Validity: PERIOD FOR

New SQL temporal data type

Section 8: Auditing Enhancements

Review of 11g R2 audit trail implementation

Overview of the Unified Audit Trail

Enabling the Unified Audit Trail

Creating a separate tablespace for the unified audit trail

Granting the AUDIT_ADMIN role to a user for audit trail configuration and management

Configuring the Unified Audit Trail to set a tolerance level for loss of audit records

Creating audit policies

Section 9: Privileges Enhancements

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

Section 10: Oracle Data Redaction

Overview of Oracle Data Redaction

Types of Masking Policies

Administering the EXEMPT REDACTION POLICY system privilege to enable a user to view unmasked values

Managing Masking Policies

Best practices for defining policy expressions

Understanding Masking of Statements Containing Subqueries

Viewing Information About Masking Policies by Querying REDACTION_POLICIES and REDACTION_COLUMNS

Section 11: General RMAN New Features Enhancements

Making Databse Connections with RMAN

Using the SYSBACKUP Privilege

Using SQL, DESCRIBE Command, Duplication Operation with the NOOPEN option

Backing up and Restoring Very Large Files

Creating Multisection Backups

Transporting Data Across Platforms

Prerequisites and Limitations

Transporting Data Processing Steps

Simplified Cross Platform Migration

Table or Partition Recovery Enhancements

Enhanced Table Point in Time Recovery

Recover a Database after Missing Redo Logs Found

Section 12: Real-Time ADDM and Compare Period Advisor

Emergency Monitoring

Real time ADDM

Use cases

Goals

Define Workload dimensions

Reported items

Root Causes

Requirements for a report

Section 13: ADR Enhancements

ADR file types

New File locations

New command for ADRCI

Improve performance by Compression

Setup Compression

The DDL and Debug Logs

The ADRCI SHOW LOG Command

Set an ADR Retention Target from 12.2

Section 14: Resource Manager and Other Performance Enhancements

Manage resources between PDBs

Manage resources within a PDB

Manage resources with CDB and PDBs
plans

Manage runaway queries

Control the in-memory database repopulation resource consumption • Automated maintenance tasks

Current Architecture: Unix multiprocess / one thread, NT/Windows one process / multithread

New Architecture: Multiprocess / MultiThread

Section 15: Index ,Table and Partition Enhancements

Multiple indexes on the same set of columns as long as some characteristic is different

Create table with INVISIBLE columns

Support for invisible columns

Describe online redefinition supports

Explain LOCK timeout during FINISH_REDEF_TABLE

Describe the Advanced Row Compression

Using the following DDL statements in an online manner

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

Section 16: Upgrade Database from 12c to 19c and DB Patching

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

Run the Pre-Upgrade Information Tool

Carry out the Recommended Actions

Check for Compatibility Issues

Backup the database

Create the New Oracle Home

Install the Oracle Database Software

Oracle 19c Upgrade of Expired Password Accounts

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

What is RU and RUR Patch

How to Download Patch

Apply Patch

Section 17: Data Pump , SQL Loader and External Table 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

SQL*Loader Enhancements

External Table Enhancements

InMemory External Tables

The External MODIFY Clause

Section 18: 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

INMEMORY_SIZE Parameter Changes and Enhancements

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

Scalable Sequences