Oracle Database Administration Workshop Level 2

• Investigate the multitenant architecture
• Describe the container types: root, seed, pluggable
• Understand how Oracle Net establishes sessions
• Query the views that describe the environment
• Describe features relevant to large deployments

Multitenant architecture

• Investigate the multitenant architecture
• Describe the container types: root, seed, pluggable
• Understand how Oracle Net establishes sessions
• Query the views that describe the environment
• Describe some features relevant to large deployments

Set up a Multitenant environment

• Create a container database
• Create pluggable containers
• Convert a non-CDB to a pluggable container
• Relocate, unplug, and drop containers
• Hot clones of PDBs and non-CDBs
• Upgrade a CDB

Multitenant administration

• Startup and shutdown a CDB
• Open and close pluggable containers
• Tablespace management
• Create local and common objects
• Understand use of roles and privileges
• Set instance parameters

Using Statspack and AWR reports

• Understand the automatic snapshot mechanism
• Use the DBMS_WORKLOAD_REPOSITORY package
• Create snapshots
• Create baselines
• Modify settings
• Generate and interpret AWR reports
• Generate AWR SQL reports
• Install and use Statspack

Frequently encountered wait events

• Disc I/O events
• Buffer cache related events
• Shared pool events
• Redo generation events
• Contention events
• Enqueue waits
• Latch and mutex events

Tuning memory structures

• Investigate the SGA memory structures
• Diagnose and fix issues with sizing of the SGA components
• Use automatic shared memory management (ASMM)
• Manage the aggregate PGA 
• Understand the need for PGA memory
• Correctly size the aggregate PGA
• Work with temporary tablespaces
• Enable automatic memory management (AMM)

Indexes

• Understand the purpose of indexes
• Describe the types of index
• B-tree indexes
• Function based indexes
• Reverse key indexes
• Bitmap and bitmap join indexes
• Create and maintain indexes
• The Oracle Text domain index type

The SQL tuning methodology

• Define the purpose of tuning
• Describe the SQL execution cycle
• Understand the concept of cost based optimization
• Learn the steps to tune a SQL statement

SQL performance investigation tools

• AWR reports
• Statspack reports
• Generate and read execution plans
• The EXPLAIN PLAN command
• The SQL*Plus autotrace facility
• The DBMS_XPLAN package
• Consider the use of SQL Trace

The Cost Based Optimizer

• Understand the CBO’s decision making process
• Join order
• Join methods
• Access methods
• Use techniques to test alternative execution plans
• Determine if statistics are inaccurate or inadequate
• Gather object and other statistics

Techniques to tune any SQL statement

• Recognize “red flags” in execution plans
• Look for frequently seen errors in analysis and design
• Some common programming mistakes
• Gather all necessary statistics
• Apply a solution discovery method

Configure the database to prevent data loss

• Understand the importance of archive log mode
• Ensure that files are multiplexed where possible
• Manage the controlfile
• Manage the online redo log
• Consider use of a Fast Recovery Area
• Consider use of Oracle Managed Files
• Enable database flashback
• Data Pump as part of a recovery strategy

The basics of backup, restore, and recovery with RMAN

• Backup the database
• Backup and remove archive logfiles
• Perform complete and incomplete recovery
• Configure defaults

Some more advanced RMAN capabilities

• Decide between backupsets and image copies
• Create incremental backups
• Enable block change tracking
• Use incrementally updated copies
• Enable encryption and compression
• Understand incomplete recovery usage cases

RMAN maintenance, reporting, and tuning

• Understand retention policies
• Generate reports
• Remove obsolete backups
• Update the repository with external changes
• Manage archive logfiles
• Create long term and offsite backups
• Consider performance options