Oracle Database 12c: Architecture & Internals

Request more details:

Description:
This course begins with a discussion of the broad systems infrastructure where you find Oracle database installations, and we outline how the database fits with other systems in a multi-tiered architecture, including web servers, application servers and engineered systems such as the Oracle Exadata database computing platform. We then explore the intricacies of a single database installation, including memory, process and storage structures. Periodically we delve into the internals of the database, probing into such areas internal locking mechanisms, kernel module calls and database failures.
This course initially presents the Oracle database architecture from the perspective of a traditional, single-tenant database configuration as it exists within an on-premise systems infrastructure. We then draw comparisons between such a traditional environment and the new Oracle 12c multi-tenant architecture, used in both traditional and cloud-based computing models.
We present this information in a form that goes beyond a discussion of theoretical concepts. In many cases the Enterprise Manager interface is used to explore the components under consideration. Therefore one of the secondary objectives of this course is to acquaint you with the built-in Enterprise Manager Database Express interface and sometimes with its EM Cloud Control (CC) companion. In particular our focus is to discover the capabilities of the Enterprise Manager Database Express interface newly introduced with the Oracle 12 database release.
The architecture of different Oracle database installations are not all uniform, as there is considerable flexibility regarding the way a particular installation is configured. Such configuration options exist largely in the form of database parameter settings. So this course will devote considerable time to identifying these parameters and how these settings can be used to configure a database installation to suit local requirements.
Prerequisites:
These are recommended prerequisites for this course:
- Oracle Database 12c: SQL Fundamentals (Levels I & II)
- Oracle Database 12c: Install & Upgrade Workshop
The successful completion of this course requires that one have access to an Oracle Database 12c Enterprise Edition (EE) installation. The course entitled ORACLE DATABASE 12C: INSTALL & UPGRADE WORKSHOP will assist you in performing such an installation and it is for this reason that it is listed above as a mandatory prerequisite.
Audience:
The target audience for thiscourse is all Oracle professionals. Among the specific groups for whom this textbook will be helpful are:
- Database administrators
- Application designers and developers
- Web server administrators
- System administrators
- Implementation specialists
- Data center support engineers
- Chief Information Officers (CIO) and other information technology (IT) management professionals
ORACLE ARCHITECTURE: THE SYSTEMS INFRASTRUCTURE:
ABOUT ENTERPRISE ARCHITECTURES
The Relational Database
Legacy Computing Models
The Multi-‐Tiered Computing Model
Scaling Up
Cloud-‐Based Deployment
ORACLE INFRASTRUCTURE ECOSYSTEM
USING ORACLE ENTERPRISE MANAGER
More About EM
Using EM Database Express
Using EM Cloud Control
ORACLE ARCHITECTURE: THE DATABASE HOST:
THE DATABASE SERVER STACK
PROCESSOR LAYER
CPU Resources
Memory Resources
I/O & STORAGE PROCESSING
OS LAYER PROCESSING MODES
DATABASE SERVER VIRTUALIZATION
STORAGE VIRTUALIZATION
ORACLE DATABASE SERVER STACK
ORACLE ENGINEERED SYSTEMS
Oracle Exadata Database Platform
Exalogic Cloud Machine
Exalytics BI Machine
ORACLE ARCHITECTURE: PRINCIPLES & TECHNOLOGY CONCEPTS:
GRID COMPUTING PRINCIPLES
Why Grid Computing?
What Is Grid Computing?
PARALLELIZATION PRINCIPLES
Hardware Parallelization
Grid Computing Devices
Clustered Database Servers
CLOUD COMPUTING PRINCIPLES
Multi-‐Tenancy
ORACLE ARCHITECTURE: THE RDBMS INSTALLATION & THE DATABASE INSTANCE:
THE DATABASE SERVER SOFTWARE
Database Versions & Releases
Database Editions
Using PRODUCT_COMPONENT_VERSION View
The Core Database Components
Using V$VERSION View
Understanding The Database Version Number
The COMPATIBLE Database Parameter
DATABASE INSTANCE ELEMENTS
Individual Elements Of A Database Instance
Physical Database Elements
An Operational Database installation
DATABASE INSTANCE CONFIGURATIONS
Single Instance
Parameter Files & Instance Configuration
MAX_STRING_SIZE Parameter Example
Independent Instances
Clustered Instances
The Database Instance In A Multi-‐tenant Configuration
RECONFIGURING A DATABASE INSTANCE
Static Vs. Dynamic Parameters
Dynamic Parameter Setting
Parameter Setting Scope
Parameter Setting Level
Setting Upgrade Related Parameters
DATABASE COMPONENTS
Advanced Data Functionality Components
Security Components
High-‐Performance Components
Administration Components
Database Feature Usage
ORACLE DATABASE INSTANCE: MEMORY ARCHITECTURE:
SHARED & PRIVATE MEMORY
SGA INTERNALS
The Buffer Cache
The Database Smart Flash Cache
The Redo Log Buffer
The Shared Pool
The Large Pool
The Java Pool
Unified Auditing Queues
PGA INTERNALS
What Is Inside The PGA?
Tunable & Non-‐tunable PGA Space
Client-‐Side Cursors
Where Is The PGA Stored?
PGA/UGA In Shared Server Mode
PGA/UGA With Optional Large Pool
LOB OBJECTS & MEMORY HANDLING
LOB Buffer Caching
Shared I/O Pool
LOB Workspace & The PGA
INSTANCE MEMORY MANAGEMENT
About Automatic Memory Management
Default Settings
Configure MEMORY_TARGET Parameter
Configure SGA_TARGET Parameter
Configure PGA_AGGREGATE_TARGET Parameter
PGA_AGGREGATE_LIMIT Parameter
Configure Memory Using EM DE
ORACLE DATABASE INSTANCE: BACKGROUND PROCESS ARCHITECTURE:
FOREGROUND VS. BACKGROUND
ABOUT THE BACKGROUND PROCESSES
The Background Processes
Linux System Processes
The DBWR Process
The LGWR Process
Checkpoints And The CKPT Process
The SMON Process
The PMON Process
The LREG Process
The ARCH Process
The RECO Process
The CJQx Process
The DBRM Process
The Management Framework Processes
Flashback Data Archive (FBDA) Process
Fault Diagnostics
Other Housekeeping Processes
Background Process Performance Monitors
THREADED MODE
About Process Mode
About Threaded Mode
KERNEL ERRORS & EXCEPTIONS
The Error Message
The Error Message Stack
Kernel Errors & Core Dumps
ORA-‐006xx & ORA-‐07445 Errors
Understanding The Kernel Errors
The Kernel Module
Kernel Module Arguments
Diagnostic Modules
The Call Stack Trace
ORA-‐600/ORA-‐7445/ORA-‐700 Error Lookup Tool
ORACLE DATABASE INSTANCE: FOREGROUND PROCESS ARCHITECTURE:
DEDICATED SERVER MODE
Session Details From V$SESSION View
Session Details From EM Database Express
What Is The Impact Of Dedicated Server Mode?
SHARED SERVERS MODE
Processing SQL In Shared Servers Mode
Comparing Dedicated Server & Shared Servers Mode
Dedicated Server Mode Client Connection
Dedicated Server Mode SQL Statement Execution

Shared Servers Mode Client Connection
Shared Servers Mode SQL Statement Execution
Consider Dedicated Server Mode
Consider Shared Servers Mode
Shared Servers Mode Advantages
CHOOSING THE SQL EXECUTION MODE
Instance-‐Level SQL Execution Mode Configuration

Session-‐Level SQL Execution Mode Configuration

PARALLEL SQL EXECUTION
What Is Parallel Execution?
The Impact On SQL Statement Execution
SQL STATEMENT EXECUTION
Parse Phase
Execute Phase
Fetch Phase
SQL OPTIMIZATION & EXECUTION PLANS
Optimization Methods
Rule-‐Based Optimizer
Cost-‐Based Optimizer
Automatic Tuning Optimizer
Adaptive Execution Plans
Adaptive Statistics
ORACLE DATABASE STORAGE ARCHITECTURE: LOGICAL DATABASE OBJECTS:
ABOUT DATABASE OBJECTS
Relational Database Objects List
Database-‐Resident Program Units
Additional Database Objects
Database Objects Illustrated
DATABASE OBJECTS CONTEXT
The Data Dictionary Schema(s)
Making An Object Reference
Explicit Schema Context
Explicit Database Context
Partition Context
EDITIONS CONTEXT & REDEFINITION
About Application Upgrades
About Application Downtime
The Edition Hierarchy
The Editions In Action
ORACLE DATABASE STORAGE ARCHITECTURE: PHYSICAL DATABASE FILES :
ABOUT THE DATABASE FILES
SERVER PARAMETER FILES
CONTROL FILES
REDO LOG FILES
DIAGNOSTIC FILES
What Are The Diagnostic Files?
EM Cloud Control Access
The MAX_DUMP_FILE_SIZE Parameter
The DIAGNOSTIC_DEST Parameter
The Log Files
Text Alert Log Contents
Viewing Text Alert Log Contents
Viewing Alert Log Errors
Maintaining The Alert Log
The Trace Files
Background Process Trace Files
SQL Execution Process (User) Trace Files
Incident Dump Files
Core Dump Files
Trace Files At The OS Level
Sample DIAG Trace File
Monitoring Trace File Space Usage
Maintaining The Trace Directories
FILES IN A MULTI-‐TENANT DATABASE
ORACLE DATABASE STORAGE ARCHITECTURE: TABLESPACES:
TABLESPACES & DATA FILES
Peering Into The Tablespace Storage Hierarchy
More About Clustered Table Storage
More About The RowID
Hybrid Columnar Compression
TEMPORARY SEGMENTS
About Temporary Segments
About Temporary Tablespace Groups
Advantages
INDEX SEGMENTS
B-‐tree Index Segments
Bitmap Index Segments
ORACLE DATABASE INTERNAL MECHANISMS: DATA CONCURRENCY:
SYSTEM VS. USER LOCKS
Internal Locks
Using V$LOCK_TYPE View
Latches
Using V$LATCH View
Mutexes & V$MUTEXT_SLEEP
User Locks
MANAGE & MONITOR SYSTEM LOCKS
About Database Wait Events
Concurrency Wait Events (Mutex)
Concurrency Wait Events (Latch)
Spinning Vs. Sleeping
Using EM Cloud Control
Using AWR: