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