12c Database Administration

Enterprise Manager and Other Tools

• Enterprise Manager (EM) Cloud Control home page

• Enterprise Manager Express home page versus Enterprise Manager Database Control

• SQL Developer with new functionalities

• OUI, DBCA

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

• Impacts in Security, Data Guard, Oracle GoldenGate, Oracle Streams, XStream, and Scheduler

Configuring and Creating CDB and PDBs

• Tools: DBCA and SQL*Plus

• Configuration and creation of CDB

• Exploring the Structure (CDB_xxx views and EM)

• Tools used: SQL Developer, DBCA and SQL*Plus and EM Database Express

• 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

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

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”

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

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

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

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

Privileges Enhancements

• Implementing Separation of Duty for Database Administration Tasks

• Using Database Privilege Analysis

• Overview of Database Privilege Analysis

• Granting the CAPTURE_ADMIN role to enable management of privilege captures

• Creating and starting/stopping privilege captures

• Viewing privilege profile data

• Querying DBA_PRIV_CAPTURES

• Explaining the purpose of the default ORA$DEPENDENCY profile

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

General RMAN New Features and FDA Enhancements

• Making Database 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

Monitoring DB Operations

• Overview

• Use cases

• Current Tools

• Define a DB operation

• Monitoring: Bracketing an Operation

• Monitoring the Progress of Operations

• DB Operation Tuning

• DB Operation Active Report

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

ADR and Network Enhancements

• ADR file types

• New File types

• New File locations

• New command for ADRCI

• Improve performance by Compression

• Setup Compression

In-Memory Column Store

• In-Memory Database option goals and benefits

• Row format and columnar format

• New SGA component:

• IMCU synchronization

• Deployment and behaviors

• Compression ratio

• New dictionary tables and added columns

• New statistics: IM %

In-Memory Caching

• Setting up Full Database In-Memory Caching

• Using Full Database In-Memory Caching

• Explaining the two buffer replacement algorithms of Automatic Big Table Caching

• Configuring Automatic Big Table Caching with PARALLEL_DEGREE_POLICY and DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameters

• Using Automatic Big Table Caching

SQL Tuning

• Adaptive Execution Plans

• SQL Plan Directives

• Statistics Gathering Performance Improvements

• Histogram Enhancements

• Enhancements to Extended Statistics

• Adaptive SQL Plan Management

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

Index and Table 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