db manageability good ocm
TRANSCRIPT
-
7/28/2019 DB Manageability GOOD OCM
1/291
11g Database Manageability
Memory Management
AWR Baselines
Automatic SQL Tuning
Advisors
Upgrade Considerations
Memory Management in 10g/11g
SGA_TARGET
Key shared memory componentstuned automatically
Single parameter controls allshared memory
10g Automatic Shared MemoryManagement (ASMM)
10g Automatic PGA MemoryManagement
PGA_AGGREGATE_TARGET
Various PGA sort areas tunedautomatically
Single parameter controls all PGAmemory
MEMORY_TARGET
MEMORY_MAX_TARGET
Improves memory utilization
Eases memory management
11g Automatic Memory Management
-
7/28/2019 DB Manageability GOOD OCM
2/292
Automatic Memory Management in 11g
Unifies system (SGA) and process(PGA) memory management
Dynamic parameters for all databasememory:
0(025
-
7/28/2019 DB Manageability GOOD OCM
3/293
11g Database Manageability
AWR Baseline and Adaptive Thresholds
Performance Monitoring and Baselines
Performance Page shows certain metrics against 99th
percentile over selected baseline
Adaptive Thresholds over select system metrics
Enabled by System Moving Window baseline
AWR Compare Period Report
Use Baseline as one of the periods
-
7/28/2019 DB Manageability GOOD OCM
4/294
Other Baselines Features
Baseline Templates enable future Baseline creation
Pre-specify known interesting times, e.g. Thanksgiving
System Moving Window Baseline
Always available
Defined by AWR retention or user specified
Adaptive thresholds computed over this baseline
Creating Single AWR Baseline
-
7/28/2019 DB Manageability GOOD OCM
5/295
Quick Configure Adaptive Thresholds
Adaptive Thresholds
Adaptive threshold valuesfollow baseline values
AWR Baseline
-
7/28/2019 DB Manageability GOOD OCM
6/296
11g Database Manageability
Automatic SQL Tuning
Manual SQL Tuning Challenges
Complex - requires expertise in several domains SQL optimization: adjust the execution plan
Access design: provide fast data access
SQL design: use appropriate SQL constructs
Time consuming Each SQL statement is unique
Potentially large number of statements to tune
Never ending task SQL workload always evolving
Plan regressions can happen
-
7/28/2019 DB Manageability GOOD OCM
7/297
SQL Tuning in Oracle Database 10g
Workload
SQL Tuning Candidates
SQL Tuning
Advisor
ADDM
AWR
one hour
Generate
Recommendations
DBA
InvokeAdvisor
Implement
DBA
Some meaningful automation,but the DBA is still required
Evaluate
Recommendations
DBA
SQL Tuning Automation in 11g
Fully automatic SQL Tuning task
Runs automatically under Autotask framework:
Maintenance Window, CPU resource controlled, on/off switch
Identifies, ranks and tunes candidate SQL
Leverages SQL Tuning Advisor
Candidate SQL automatically chosen
Excluded: parallel queries, DML/DDL, recursive, ad-hoc (infrequent)
Tests and (optionally) implements SQL profiles
Only implements significantly improved plans (3x)
Time budgeted to avoid run-away plans
-
7/28/2019 DB Manageability GOOD OCM
8/298
Its Automatic!
ChooseCandidate
SQLone
week
Automatic SQL Tuning in OracleDatabase 11g
Workload
SQL TuningCandidates
Test SQL ProfilesImplement
SQL Profiles
Generate
Recommendations
AWRDBA
View Reports /ControlProcess
Result Summary
-
7/28/2019 DB Manageability GOOD OCM
9/299
Result Details
Result Recommendations
-
7/28/2019 DB Manageability GOOD OCM
10/2910
11g Database Manageability
SQL Access Advisor
SQL Access Advisor: Partition Advice
Indexes Materialized
views
Materialized
views log
SQL AccessAdvisor
Hypothetical
SQL cache
Filter
Options
STS
CompleteWorkload
Partitioned
objects
Hashpartitions?
Intervalpartitions?
-
7/28/2019 DB Manageability GOOD OCM
11/2911
SQL Access Advisor Results
Access Advisor Recommendations
-
7/28/2019 DB Manageability GOOD OCM
12/2912
Editable implementation script
11g Database Manageability
Fault Diagnostic Automation
-
7/28/2019 DB Manageability GOOD OCM
13/2913
Oracle Database 11g R1
Fault Diagnostic Automation
Realistic Testing
and
Automatic HealthChecks
Automatic
DiagnosticWorkflow
IntelligentResolution
Proactive
Patching
Goal: Reduce Time to Problem Resolution
Prevention Resolution
Diagnostic Solution Delivery
Automatic Diagnostic Workflow
DBA
CriticalError
AutomaticDiagnosticRepository
Known
bug?
DBA
EM Support Workbench:
Apply patch / Data Repair
Yes
DBA
No
Alert DBATargeted health checks
Assisted SR filling
Auto incident creation
First failure capture
EM Support Workbench:
Package incident info
Data Repair
1 2
3
4
-
7/28/2019 DB Manageability GOOD OCM
14/2914
Problems and Incidents
Problems are fundamental code or configurationissues that can cause execution failures
They exist until they are corrected, e.g by patch
They are assigned a signature-based key value
An incident is a single occurrence of a problem
They happen at point(s) in time and thus have timestamps
They induce diagnostic actions like dumps and traces
They are managed by ADR and packaged by IPS
Automatic Diagnostic Repository
diag
rdbms
DBName
SID
ADRBase
$ORACLE_HOME/log
DIAGNOSTIC_DEST
ADRHome
$ORACLE_BASE
ADRCIlog.xml
alert_SID.log
V$DIAG_INFO
BACKGROUND_DUMP_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
alert cdump (others)hmincpkg incident
metadata
incdir_1 incdir_ n
trace
Support Workbench
-
7/28/2019 DB Manageability GOOD OCM
15/2915
Important points about ADR
Location of traces and dumps has changed See V$DIAG_INFO for new locations
Symbolic links can provide work around for scriptcompatibility
Format of alert.log is changed New XML format more parse-able
Old style still available for backward compatibility
Space management Incident flood control minimizes number of traces
ADR auto-purging reduces disk footprint
Incident Packaging Service (IPS)
IPS automates packaging of diagnostic data for given problem
Creates zip files from incident package contents for sending toOracle Support
Intelligently correlates and includes dumps and traces fromrelated incidents Correlation rules based on time proximity, client id, error code
Recommends further diagnostic actions for DBA For example build SQL test case
DBAs can explicitly add/edit or remove files before packaging
Including non-ADR managed files
-
7/28/2019 DB Manageability GOOD OCM
16/2916
EM Support Workbench Overview
Primary customer-facing interface into IPS and ADR
Task-oriented interface over problems and incidents View problem and incident details
Create and manage IPS packages
Run health checks or generate additional diagnostic data
Run advisors to help resolve problems
Linkages to Oracle Support Generate and send incident package files using OCM
Create and track Metalink service requests from problem Close problem SR once resolved
EM Support Workbench
-
7/28/2019 DB Manageability GOOD OCM
17/2917
Package details and IPS in EM
EM Support WorkbenchQuick Package
Simple wizard workflow from problem throughpackage and send to support
Single-problem package
Good for rapid first fault reporting
Limitations
Package zip must be 2GB or less
Cannot scrub traces or customize package
-
7/28/2019 DB Manageability GOOD OCM
18/2918
Prevention: Health Monitoring
Proactive Health Checks
E.g. Dictionary Validation
Reactive Checks
Trigger on error via Intelligent dumping Rules
E.g. Checks blocks around corrupted block
All Checks can be Activated on Demand
Incident Meter for EM
Intelligent Repair Advisors
Data Recovery Advisor
Guided expert data recovery system using diagnostic dataand Health Check output
SQL Test Case Builder
Automatically retrieves exact environment information from
ADR to build SQL test cases and replicate SQL issues
SQL Repair Advisor
Analyzes failing SQL statements to isolate bug
May recommend SQL Patch as work around
-
7/28/2019 DB Manageability GOOD OCM
19/2919
Data Recovery Advisor
Data Recovery Advisor Oracle Database tool that automatically diagnoses data failures,
presents repair options, and executes repairs at the user's request
Determines failures based on symptoms
E.g. an open failed because datafiles f045.dbf and f003.dbf are missing
Failure Information recorded in diagnostic repository (ADR)
Flags problems before user discovers them, via automated health
monitoring
Intelligently determines recovery strategies
Aggregates failures for efficient recovery
Presents only feasible recovery options
Indicates any data loss for each option
Can automatically perform selected recovery steps
Reduces downtime by eliminating confusion
-
7/28/2019 DB Manageability GOOD OCM
20/2920
Data Recovery Advisor
Enterprise Manager Support
SQL Test Case Builder
-
7/28/2019 DB Manageability GOOD OCM
21/2921
SQL Test Case Builder
Business Requirement Bug resolution
Test case required for fast bug resolution
Not always easy to provide a test case
What information should be provided?
How much data is need?
Getting the test case to Oracle can be tricky
Solution
Oracle automatically creates a test case
Collects necessary information relating to a SQL incident
Collected data is packaged to be sent to Oracle
Collected data allows a developer to reproduce the problem
SQL Repair Advisor
-
7/28/2019 DB Manageability GOOD OCM
22/2922
SQL Repair Advisor
Business Requirementy The most common types of SQL problems - exception, performance
regression etc., are hard to diagnose
y A lot of time is spent trying to reproduce the problem
y If a workaround is found it has to be applied to entire system
Solution
Advisor
Investigates the incident locally
Automatically determines the root cause
Provides a workaround (SQL Patch) for just the effected SQL
If not, sends necessary diagnostic information to Oracle
SQL Repair Advisor Flow
DBA
SQLstatement Execute
Statementcrashes
Generateincident in ADRautomatically
DBA run
SQL Repair Advisor
DBA gets
alerted
SQ
LRepairAdvisor
investigates
SQL patchgenerated
DBA accept
SQL patch
Statementexecutes
successfully
again
SQL statementpatched
Execute
Trace files
-
7/28/2019 DB Manageability GOOD OCM
23/2923
DB Management Pack Enhancements
Diagnostic Pack
ADDM for RAC
AWR Baselines and Adaptive Thresholds
Transportable AWR
Tuning Pack
Automatic SQL Tuning Advisor
Partition Advisor
Real-time SQL Monitoring
SQL Plan Management and Plan Evolution
Upgrade Considerations
-
7/28/2019 DB Manageability GOOD OCM
24/2924
New Background Processes in 11g
ASMMaintains disk membership in ASM disk groupsGMON
ALL.The process archives historical rows for tracked tables into flashback data archives and manages the
flashback archives.FBDA
ALL with anyregistered event
activity
(optional non-fatal)
The Event Monitor Coordinator coordinates the event management and notification activity i n the
database which includes Streams Event Notifications, Continuous Query Notifications and FastApplication Notifications.
EMNC / e0xx
ALLPerforms diagnostic dumps and executes global oradebug commands.
DIAG
ALLResponsible for hang detection and deadlock resolution.
DIA0
ALLThe Resource Manager process is responsible for setting Resource Plans and other Resource Manager
related tasks.DBRM
RAC
In an RAC environment, this per-instance process (Atomic Controlfile to Memory Service) is an agentwhich contributes to ensuring a distributed SGA memory update is either globally committed on success
or globally aborted in the event of a f ailure.ACMS
RAC onlyProvides transparent support for XA global transactions in a RAC environment. The database
autotunest he number of these processes based on the workload of XA global transactions.GTX0-j
Description FoundProcess
Name
New Background Processes in 11g
RAC onlyThe RAC Management Processes perform manageability tasks for RAC, e.g. creation of RAC related
resources when new instances are added to the clustersRMSn
RAC onlyIn a RACenvironment, this process manages background slave process creation and communication onremote instances. These background slave processes perform tasks on behalf of a coordinating process
running in another instance.
RMSN
ALLThe Virtual Keeper of TiMe
?is responsible for providing a Wall-Clock time (updated every second) and
Reference-Time Counter (updated every 20ms and available only when running at elevated priority).VKTM
ALL (optional non-fatal)
The space management coordinator process coordinates the execution of various space managementrelated tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave
processes (Wnnn) to implement the task.SMCO / Wnnn
ASM, spawned on
demand.Marks ASM Allocation Units as stale following a mi ssed write to an offline disk.MARK
ASM, spawned ondemand.
Performs proxy I/O to an ASM metafile when a disk becomes offline.KATE
Spawns Oracle processes.
Description
ALLPSP0
FoundProcess
Name
-
7/28/2019 DB Manageability GOOD OCM
25/2925
Oracle Database 11g Release 1Upgrade Paths
Direct upgrade to 11g is supported from 9.2.0.4 or higher,10.1.0.2 or higher, and 10.2.0.1 or higher.
If you are not at one of these versions you need to perform adouble-hop upgrade
For example:
7.3.4 -> 9.2.0.8 -> 11.1
8.1.7.4->9.2.0.8->11.1
Choose an Upgrade Method
Database Upgrade Assistant (DBUA)
Automated GUI tool that interactively steps the user through
the upgrade process and configures the database to run withOracle Database 11g Release 1
Manual Upgrade
Use SQL*Plus to perform any necessary actions to preparefor the upgrade, run the upgrade scripts and analyze theupgrade results
Export-Import Use Data Pump or original Export/Import
CREATE TABLE AS SQL statement
-
7/28/2019 DB Manageability GOOD OCM
26/2926
Oracle Database 11g InstallationChanges
Addition of new products to the install
SQL Developer
Movement of APEX from companion CD to main CD
Warehouse Builder (server-side pieces)
Oracle Configuration Management (OCM)
New Transparent Gateways
Removal of certain products and features from the installation:
OEM Java Console
Oracle Data Mining Scoring Engine
Oracle Workflow
iSQL*Plus
Fine Grained Access Control
for Utl_TCPand its cousinsChallenge
Oracle Database provides packaged APIs for PL/SQL
subprograms to access machines (specified by host and port)using bare TCP/IP and other protocols built on it (SMTP and
HTTP)
Utl_TCP, Utl_SMTP, Utl_HTTP
If you have Executeon the package, you can access ANYhost-port
Solution an Access Control List (ACL) specifies a set of users and roles
you assign an ACL to a host and port range
you may need to explicitly grant this access in 11.1
-
7/28/2019 DB Manageability GOOD OCM
27/2927
Case Sensitive Password
By default:
Default password profile is enabled
Account is locked after 10 failed login attempts
In upgrade:
Passwords are case insensitive until changed
Passwords become case sensitive by ALTER USER
On creation:
Passwords are case sensitive
Review:
Scripts
Database links with stored passwords
Consider backward compatibility parameter,SEC_CASE_SENSITIVE_LOGON
Log files changes
Automatic Diagnostic Repository
$ORACLE_BASE/diag
alert.log
xml format
$ORACLE_BASE/diag/rdbms/orcl/orcl/alert/log.xml
adrci> show alert tail
RP: $ORACLE_BASE/diag/rdbms//trace/alert_SID.log
-
7/28/2019 DB Manageability GOOD OCM
28/2928
Moving On
RBO
Still there, same behavior as 10.2, but even move
tools to ease the move
SQL performance analyzer
SQL plan management
Moving On
Stored Outline SQL Plan Management
Ultrasearch Secure Enterprise Search
CTXPath Index XML Index
SYSDBA for ASM administration
SYSASM
No longer supported
Workflow BPEL
These deprecated features / privileges are stillavailable for backward compatibility but therecommendation is to move on
-
7/28/2019 DB Manageability GOOD OCM
29/29
Oracle Client / Server InteroperabilityServer
Client11.1.0 10.2.0 10.1.0 9.2.0 9.0.1 8.1.7 8.1.6 8.1.5 8.0.6 8.0.5 7.3.4
11.1.0 Yes Yes Yes ES #5 No No No #3 No #3 No #3 No #3 No #3
10.2.0 Yes Yes Yes ES #5 No Was No #3 No #3 No #3 No #3 No #3
10.1.0(#4) Yes Yes Yes ES Was Was #2 No #3 No #3 No #3 No #3 No #3
9.2.0 ES #5 ES #5 ES ES Was Was No No Was No No #1
9.0.1 No No Was Was Was Was Was No Was No Was
8.1.7 No Was Was Was Was Was Was Was Was Was Was
8.1.6 No No No No Was Was Was Was Was Was Was
8.1.5No No No No No Was Was Was Was Was Was
8.0.6 No No No Was Was Was Was Was Was Was Was
8.0.5No No No No No Was Was Was Was Was Was
7.3.4 No No No Was Was Was Was Was Was Was Was
Metalink #207303.1: Client / Server / Interoperability Support Between Different Oracle Versions
Lifetime Support Policy
Metalink #454507.1: ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts
Metalink #454506.1: 11.1.0.6 Base Release - Availability and Known Issues