Transcript What do I really need to know about upgrading

What do I really need to know when upgrading
Thomas Kyte
http://asktom.oracle.com/
1
So … What Does Oracle Database 11g Mean To Me?
2
Small Change – but think about it…
3
Small Change – but think about it…
ops$tkyte%ORA11GR2> create table t
2
as
3
select substr(object_name, 1, 1 ) str, all_objects.*
4
from all_objects
5
order by dbms_random.random;
Table created.
ops$tkyte%ORA11GR2> create index t_idx on t(str,object_name);
Index created.
ops$tkyte%ORA11GR2> begin
2
dbms_stats.gather_table_stats
3
( user, 'T',
4
method_opt => 'for all indexed columns size 254',
5
estimate_percent=>100 );
6
end;
7
/
PL/SQL procedure successfully completed.
4
Small Change – but think about it…
ops$tkyte%ORA11GR2> select count(subobject_name) from t t1 where str = 'T';
…
-------------------------------------------------------------------------------------| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
19 |
|
1 |
|
|
1 |
19 |
|
2 |
|
292 |
5548 |
296
(0)| 00:00:04 |
|*
3 |
| T_IDX |
292 |
|
4
(0)| 00:00:01 |
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID| T
INDEX RANGE SCAN
296
(0)| 00:00:04 |
|
|
--------------------------------------------------------------------------------------
5
Small Change – but think about it…
ops$tkyte%ORA11GR2> insert into t
2
select 'T', all_objects.*
3
from all_objects
4
where rownum <= 1;
1 row created.
ops$tkyte%ORA11GR2> begin
2
dbms_stats.gather_table_stats
3
( user, 'T',
4
method_opt => 'for all indexed columns size 254',
5
estimate_percent=>100 );
6
end;
7
/
PL/SQL procedure successfully completed.
6
Small Change – but think about it…
ops$tkyte%ORA11GR2> select count(subobject_name) from t t2 where str = 'T';
…
--------------------------------------------------------------------------| Id
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
19 |
|
1 |
|
|
1 |
19 |
|*
2 |
|
293 |
5567 |
SORT AGGREGATE
TABLE ACCESS FULL| T
297
(1)| 00:00:04 |
|
297
|
(1)| 00:00:04 |
---------------------------------------------------------------------------
7
What do you have from the past…
•Online Parameter Changes
•Online Major Memory Changes
•Online Schema Evolution
•Online Index Creates
•Quiesce
•Rolling Upgrades
•Online Disk reconfiguration (ASM)
•Online Cross Platform Tablespace Transport
•Full Database Transports
•And more….
9
What do you need to know?
Test To Scale
The ability to forget
and let it go
SQL Plan
Management
Never
Stopping
Planning
Ahead
10
First – what do we need
to do?
© 2010 Oracle Corporation
11
11
Database Upgrade Process: Steps
1.
2.
3.
4.
5.
6.
7.
Analyze & gather information about environment
Determine the upgrade path and choose upgrade method
Prepare backup / recovery strategy and clone database to test
Establish performance baseline/metrics before upgrade
Develop a test plan for database, applications, and reports
Test upgraded database with applications and reports
Ensure adequate performance by comparing metrics gathered
before and after upgrade
8. Remediate regressions, e.g, tune queries, update database
parameters, call Support, etc.
9. Go Live!
Planning Ahead
Forget and let it go
ASH and AWR
Test Stopping
To Scale
Never
SQL Plan Management
12
ASH/AWR
© 2010 Oracle Corporation
13
13
Automatic Workload Repository (AWR)
Shutdown/startup
T1
T2
T3
T4
You can report on:
T2-T1
T3-T2
T3-T1
T4-T3
T4-T2
T4-T1
select * from
dba_hist_snapshot;
• Every N-Units of time, data is flushed from memory to disk (a
snapshot)
• You can generate reports that cover any range of time (n-units
of time at a time)
• We simply “subtract”
14
Active Session History (ASH)
Point in time:
Short term memory:
Long term memory:
V$SESSION
V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY
V$SESSION_WAIT
SGA Circular
Buffer – sized
Every hour or
2/3rds full in SGA
By CPU_COUNT
•V$ACTIVE_SESSION_HISTORY – about every second of activity
•DBA_HIST_ACTIVE_SESS_HISTORY – every 10 seconds of
activity
•On demand flush
•When ever in memory buffer (V$) is 2/3rds full
•Retained using AWR retention policies
15
SQL
Plan Management
© 2010 Oracle Corporation
16
16
SQL Plan Management
Phase 1 - Capture
• Run applications to create a baseline
– OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
SQL MANAGEMENT BASE
Repeated plans will be added to the
SQL Plan Baseline during this phase
Plan History
Residing in SYSAUX TS.
Will occupy max. 10% of SYSAUX.
Weekly job will delete plans
not used in 53 weeks [default].
GB
Parse
Plan Baseline
HJ
GB
HJ
HJ
HJ
17
SQL Plan Management
Phase 2 - Selection
• New Plans are generated (because something changed)
• But are not trusted
– OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
New plan will be added to the Plan History but it
won't be used unless and until it has been verified
Plan History
GB
NL
GB
GB
Hard Parse
Plan Baseline
NL
NL
NL
NL
NL GB
GB
NL
GB
GB
HJ
HJ
NL
HJ
HJ
HJ
HJ
18
SQL Plan Management
Phase 3 – Evolution
• Plans are verified – by testing the performance of the new
plan in the background
– Automagically or Manually
Equal or better plans can be added to
the SQL Plan Baseline
Plan History
Inefficient plan will
be kept in the
Plan History
GB
NL
Plan Baseline
DBA
GB
GB
GB
NL
HJ
NL
NL
HJ
NL
NL
Automatic Job
19
Upgrade Scenario
• Your 9i application is already in 11g for whatever reason
• You’d like to have ‘query plan stability’
– Coupled with the opportunity to use better plans – do not want to
be frozen
• The steps would be….
20
SQL Plan Management – Parameterize

STS

Repeatable plans will be added
to the Plan Baseline upon 2nd
execution
Plan History
GB
NL
Plan Baseline
NL
GB
GB
GB
NL
HJ
NL
NL
HJ
Now: Different
plans created
with OFE=11
will be added to
the Plan History
for later verification
NL
21
Upgrade Scenario
• Your application is in 9i
• You’d like to have ‘query plan stability’
– Coupled with the opportunity to use better plans – do not want to
be frozen
• You will be changing platforms during the upgrade (not
doing a direct upgrade of the database)
• The steps would be….
22
SQL Plan Management – Outlines

STS
Exp/imp outlines to
New system
SS
exp imp
expdp impdp
DB-Link ...
3

Capture query outlines on the
production system
DBMS_SPM.MIGRATE_STORED_OUTLINE
Plan History
Plan Baseline
GB
GB
GB
NL
HJ
NL
NL
HJ
NL
23
Upgrade Scenario
• Same Scenario but your application is in 10g
• You’d like to have ‘query plan stability’
– Coupled with the opportunity to use better plans – do not want to
be frozen
• You will be changing platforms during the upgrade (not
doing a direct upgrade of the database)
• The steps would be….
24
SQL Plan Management – Tuning Pack


Staging
STS
Table
STS
exp imp
expdp impdp
DB-Link ...
3
10.2 plans will become
the SQL Plan Baseline
Plan History
GB
NL
Plan Baseline
NL
GB
GB
GB
NL
HJ
NL
NL
HJ
NL
25
Upgrade Scenario
• You would like to deploy from development to
production..
• You would like to deploy at a customer site…
• And you want to start with a stable set of plans
– Using better plans only after they have been verified
• The steps would be….
26
SQL Plan Management - New Application


@Vendor
@Customer
DBMS_SPM.CREATE_STGTAB_BASELINE
Staging
Staging
exp imp
expdp impdp
Table
Table
3
DBMS_SPM.UNPACK_STGTAB_BASELINE
DBMS_SPM.PACK_STGTAB_BASELINE
Plan Baseline
Plan Baseline
GB
GB
GB
GB
GB
GB
NL
HJ
NL
NL
HJ
NL
NL
HJ
NL
NL
HJ
NL
27
Test to Scale
© 2010 Oracle Corporation
28
28
Database Replay Overview
• Replay actual production database workload in test environment
• Identify, analyze and fix potential instabilities before making
changes to production
• Capture Workload in Production
– Capture full production workload with real load, timing & concurrency
characteristics (9i, 10g, 11g)
– Move the captured workload to test system (11g)
• Replay Workload in Test
– Make the desired changes in test system
– Replay workload with full production characteristics
– Honor commit ordering
• Analyze & Report
– Errors
– Data divergence
– Performance divergence
Analysis & Reporting
29
Supported Changes
Changes
Unsupported
(there are other
tools for that)
Client
Client
…
Client
Middle Tier
Changes Supported
Recording of
External Client
Requests
•Database Upgrades, Patches
•Schema, Parameters
•RAC nodes, Interconnect
•OS Platforms, OS Upgrades
Storage
•CPU, Memory
•Storage
•Etc.
30
Step 1: Workload Capture
Production System
• All external client requests captured in binary
files
Client
Client
• System background and internal activity
excluded
…
Client
File System
• Minimal overhead
–Avoids function call when possible
–Buffered I/O
Middle Tier
File 1
• Independent of client protocol
File 2
…
• Can capture on 9i, 10g, and 11g and replay on
11g
File n
• Capture load for interesting time period, e.g.,
peak workload, month-end processing, etc.
Storage
31
Step 2: Process Workload Files
•Setup test system
Test System
–Application data should be same as
production system as of capture start time
–Use RMAN, Snapshot Standby, imp/exp,
Data Pump, etc. to create test system
–Make change: upgrade db and/or OS,
change storage, migrate platforms, etc.
•Processing transforms captured data
into replayable format
•Once processed, workload can be
replayed many times
•For RAC copy all capture files to
single location for processing or use
shared file system
File 1
File 1
File 2
File 2
…
…
File n
File n
Capture Files
Metadata
Replay Files
32
Step 3: Replay Workload
•
•
•
•
Replays workload preserving
timing, concurrency and
dependencies of the capture
system
Replay Client is a special
program that consumes
processed workload and sends
requests to the replay system
Clients interpret captured calls
into sequence of OCI calls and
submit to database
For high concurrency
workloads, it may be necessary
to start multiple clients
Test System
Replay Clients
File 1
File 2
…
File n
Metadata
Replay Files
33
Analysis & Reporting
• Error Divergence: For each call error divergence is reported
–New: Error encountered during replay not seen during capture
–Not Found: Error encountered during capture not seen during replay
–Mutated: Different error produced in replay than during capture
• Data Divergence
–Replay: Number of rows returned by each call are compared and
divergences reported
–User: Application level validation scripts
• Performance Reporting
–Capture and Replay Report: Provides high-level performance
information
–ADDM Report: Provides in-depth performance analysis
–AWR, ASH Report: Facilitates comparative or skew analysis
34
SQL Performance Analyzer: Overview
Production
Test
…
Client
Re-execute SQL
Middle Tier
Capture
SQL
Transport
SQL
…
…
Oracle DB
Storage
• If adequate spare cycles available, optionally
execute SQL here
Make Changes / Tuning
Regressions
* No middle & application tier setup required
35
SQL Performance Analyzer: Workflow
Production
Test
Make Change
Steps
(1)
Capture
SQL (STS)
(2)
(3)
Transport
STS
(6)
(4)
Execute SQL
Pre-change
(5)
Execute SQL
Post-change
Compare
Perf.
Reiterate
No
(7)
Production Change /
(7)
Tuning Deployment
Tuned System
Done?
Yes
36
The Ability to forget
And let it go
© 2010 Oracle Corporation
37
37
Flashback for Rapid Recovery from Human Error
Flashback
Query
Flashback
Tables
Flashback
Database
Flashback
Data Archive
and
Transaction
38
Restore Points
•Restore point – specifies a jump label
–Named Restore Point
•Similar to a bookmark
•"Can be" - but no guarantee
•Will be recorded to the control file
SQL> CREATE RESTORE POINT rpt;
SQL> FLASHBACK DATABASE TO RESTORE POINT rpt;
–Guaranteed Restore Point
•Similar to storage snapshots
•Overrides the FLASHBACK_RETENTION_TARGET
•Attention: A guarantee restore point can stop the whole database
SQL> CREATE RESTORE POINT grpt GUARANTEE FLASHBACK DATABASE;
SQL> FLASHBACK DATABASE TO RESTORE POINT grpt;
39
Never Stopping
© 2010 Oracle Corporation
40
40
Rolling Database Upgrades
Upgrade
Clients
Redo
A
Version X
1
B
Logs
Queue
Version X
4
A
X+1
Upgrade node B to X+1
Redo
Upgrade
B
X
2
Initial SQL Apply Config
A
Redo
B
X+1
X+1
Switchover to B, upgrade A
A
3
B
Patch Set
Upgrades
Major
Release
Upgrades
Cluster
Software &
Hardware
Upgrades
X
X+1
Run in mixed mode to test
41
Online Application Upgrade
Edition-based redefinition
•Code changes are installed in the privacy of a new
edition
•Data changes are made safely by writing only to new
columns or new tables not seen by the old edition
•An editioning view exposes a different projection of a
table into each edition to allow each to see just its own
columns
•A crossedition trigger propagates data changes made
by the old edition into the new edition’s columns, or (in
hot-rollover) vice-versa
42
Editions & object visibility
Object_4
Object_3
Object_2
Object_1
Pre-upgrade
edition
43
Editions & object visibility
Object_4
Object_4
(inherited)
Object_3
Object_3
(inherited)
Object_2
Object_2
(inherited)
Object_1
Object_1
(inherited)
Pre-upgrade
edition
is child of
Post-upgrade
edition
44
Editions & object visibility
Object_4
Object_4*
(actual)
Object_3
Object_3*
(actual)
Object_2
Object_2
(inherited)
Object_1
Object_1
(inherited)
Pre-upgrade
edition
is child of
Post-upgrade
edition
45
Planning Ahead
Upgrade Planner
© 2010 Oracle Corporation
46
46
MOS-EM: Unified View, Integrated Information
Between My Oracle Support and Enterprise Manager
My Oracle Support
Knowledge
Management
Problem/SR
Management
Configuration
Management
Oracle
Knowledge
Management
Problem/SR
Management
Configuration
Management
Performance
Management
Problem
Diagnosis
Provisioning
& Patching
Enterprise Manager
Customer
Operating Systems
© 2010 Oracle Corporation
Databases
Middleware
Applications
47
47
‘End-to-End’ Upgrade Lifecycle
Phase
Sub-Phase
Preparation
Upgrade
Plan
My Oracle
Support
Upgrade Plan*
Upgrade
Testing
Upgrade
Rehearsal
Real Application
Testing
Production
Upgrade
Provisioning
Post-Upgrade
Monitor & Maintain
Monitoring
Enterprise Manager – Grid Control
My Oracle Support  EM Grid Control
Integrated solution can be leveraged throughout full lifecycle
*Will be integrated in upcoming release
48
48
<Insert Picture Here>
How to get there
49
Upgrade is easier!
•The upgrade to Oracle Database 11g is much easier than
any upgrades to earlier Oracle releases
•Size of Upgrade guides:
–8.1.7- 512 pages
–9.0.1- 484 pages – 111 steps for an RDBMS with 9 components
–9.2.0- 344 pages
–10.1.0 - 170 pages
–10.2.0 - 140 pages
–11.1.0 - 186 pages
–11.2.0 -178 pages
© 2010 Oracle Corporation
50
50
Documentation
–Note:785351.1 Upgrade Companion 11g Release 2
51
What are my upgrade paths?
Predictable performance post-upgrade
9.2.0.8
10.1.0.5
 10.2.0.2
 11.1.0.6
11.2
SQL Plan Management
Automated SQL tuning
Real Application Testing
52
53