Oracle for Administrators Training Overview
Accelebrate's Advanced Oracle for Administrators training teaches students the skills needed to keep a database running reliably and efficiently, including working with multitenant architecture (CDB/PDB), tuning the database, tuning SQL, and performing backup and recovery.
Location and Pricing
Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.
In addition, we offer some courses as live, instructor-led online classes for individuals.
Objectives
- Understand, work with, and administer a multitenant architecture
- Set up a Multitenant environment
- Generate AWR SQL reports
- Install and use Statspack
- Investigate the SGA memory structures
- Understand the purpose of indexes
- Create and maintain indexes
- Use SQL performance investigation tools
- Apply SQL tuning techniques
- Configure the database
- Work with more advanced RMAN capabilities
Prerequisites
Students should have taken Accelebrate's Oracle for Administrators training or have equivalent experience.
Outline
Expand All | Collapse All
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
Conclusion
Training Materials
All students receive comprehensive courseware and a related textbook.
Software Requirements
- Windows or Linux with at least 8GB RAM
- A complete installation of a modern instance of Oracle
- Please contact us for detailed setup instructions if you have purchased this class.
