Transcript Slide 1

<Insert Picture Here>
The Most Common Upgrade Mistakes (and How to Avoid Them)
Roy Swonger, Senior Director, Database Upgrade & Utilities
Carol Tagliaferri, Senior Manager, Database Upgrade Development
The following is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions.
The development, release, and timing of any
features or functionality described for Oracle’s
products remains at the sole discretion of Oracle.
2
Upgrade Mistakes
3
Agenda
• What Not To Do
• How Not To Do It
<Insert Picture Here>
4
Mistake #1: Leaving the Job Half-Finished
• Not installing the ‘latest’ patchset or PSU
• Or worse, installing a patch or patchset,
but not actually APPLYING it
5
A real life scenario – install but no apply
On 8/27/2010 5:37 AM, <name withheld> wrote:
Hi Carol
We run into a big issue here.
The problem is that our database replicates a lot of
tables out to other databases. And when we replicate
from our 11.2 database to a 9.2 database, the mview log
(on the 11.2) does not get purged.
But we have applied the one-off patch on the database,
and the issue still persist.
6
A real life scenario – install but no apply
From:
carol tagliaferri
<[email protected]>
To:
<name withheld>
Date:
27-08-2010 20:49
Subject:
Re: 11.2.0.1 recommended patches ?
Hi <name withheld>
The developer was not able to reproduce this problem
would like to know if you executed the files after
installing the patch as shown below?
connect as sysdba
@prvtsnap
@prvtsnps
7
RTFR – Read The Friendly README
Subject:
Re: 11.2.0.1 recommended patches ?
Date:
Mon, 30 Aug 2010 10:21:30 +0200
From:
<name withheld>
To:
carol tagliaferri <[email protected]>
Hi Carol
No, we just installed the patch, we missed that part of
the patch, sorry.
- I have just run the two files, and we will test again
- will inform You asap.
8
Solution #1: Finish What You Started
• Patch the target Oracle Home BEFORE
the upgrade
• For patchset or major release remember
to execute @catupgrd.sql
• For patch or PSU consult README
instructions for proper application
9
Solution #1: Patch Set Update (PSU) Installation
• Install PSUs as well
– Note:854428.1: Introduction to Database Patch Set Updates
– Database PSUs include:
• Fixes for critical issues that may affect a large number of customers
and that are already proven in the field
• Critical Patch Update (CPU) fixes
– Database PSUs do not include:
• Changes that require re-certification
• Fixes that require configuration changes
– Typically 50-100 new bug fixes – usually cumulative
– Guaranteed to be RAC rolling installable
– Will be released 4x per year on the same schedule as CPUs
10
Mistake #2: Being an Imperfect Human Being
• Everybody makes mistakes
–
–
–
–
Typos
Forgetting a step
Misreading a warning message
Corrupt your spfile by editing it
That’s why I
always use
DBUA!
• DBUA helps avoid common errors
“It is the nature of every person to
error, but only the fool perseveres in
error." - Cicero
12
Upgrade Running Very Slow….>50 hours
************************( Source: ITS-RP )***************************
SR 7xxxxx.992
Owner: CAROL.US @ 600 SRInfo 7xxxxx.992
Organisation: <name withheld>
CSI 12345678
Contact:
DBA at <name withheld>
Phone: 408-518-xxxx
Email:
[email protected] Fax:
Severity:
1
Queue: GQOTHER
Status:
HCL
Product:
RDBMS (10.2.0.4)
Rdbms: 10.2.0.4
Platform:
HP-UX Itanium (197) (B.11.23)
Abstract:
PENDING: CATUPGRD.SQL IS RUNNING VERY SLOW
*************************************************************************
13
But there is a solution….
CAUSE JUSTIFICATION
===================
utlu102i.sql stated "shared_pool_size" needs to be increased to at least 250
MB SHARED_POOL_SIZE only set to minimum of 150mb
PROPOSED SOLUTION(S)
====================
increase "shared_pool_size to at least 250 MB
====================
14
One Happy Customer with a successful upgrade
Update from customer:
*** METALINK.US 20-APR-2009 14:08:05 GMT
*** Thanks, Now the catupgrd.sql went fine with in 40 minutes.
All Objects looks good. next time, we will take care the
shared_pool as sugested by the recommendations. i think, in
the second run we incresed the Shared_pool- to 250m as
suggested.
regards, Happy Customer
15
Solution #2: Post Upgrade Editable PFILE
• Always create an editable init.ora from the current SPFILE
after the upgrade has been finished
• Prevents rewrite in case of setting wrong parameters or
forced edit
• Keep in mind:
– The SPFILE is binary file!!! Don't edit it!! Default since Oracle 9.0
– It simply will exist after using DBUA or DBCA
SQL> create pfile='/tmp/initDB.ora' from spfile;
<< Now edit init.ora with any editor >>
SQL> startup force pfile=/tmp/initDB.ora
SQL> create spfile from pfile;
– Parameter can be changed by:
SQL> alter system set PARAMETER=VALUE scope=both;
16
Mistake #3: Driving at night with the headlights off
• AKA - “Not doing your homework”
• Consult Note 161818.1 for the latest gossip
• The Upgrade Companion should be on Oprah’s Book
Club List
• OTN
18
Why Should I Check My Oracle Support?
-------- Original Message -------Subject: Upgrade to RDBMS 10.2.0.4 (from 10.2.0.3)
finally done.
Date: Wed, 19 Aug 2009 15:56:06 -0400
From: <[email protected]>
To: <[email protected]>
Hi Carol,
The question here is:
Why in the upgrade documentation this possible and
costly (probably not for all Oracle customers) bug it is
not referenced, mentioned or included? If it is, my
apologies and please point out where I can find it.
Regards, <name withheld to protect the innocent>
Sr. Database Administrator
DBA Group - IT Infrastructure
19
It Could Save You Time!
From:
carol tagliaferri
<[email protected]>
To:
DBA@<name withheld>
Date:
08-09-2009 9:42 AM
Subject: ORA-600 [22635] - Start with Metalink note
161818.1
Hello <DBA>,
We did a bit more research on the particular problem
you experienced and were able to find it listed in
the ISSUES and ALERTS note.
If you start with Metalink Note: 161818.1 and follow
the link for 10.2(Note 316900.1) and onto issues
specific to 10.2.0.3(Note 401435.1) it is listed
here:
20
Solution #3: Pay Attention to Important Alerts!
• Note 161818.1
21
Solution #3: Read the Friendly Documentation
– Note:785351.1 Upgrade Companion 11g Release 2
22
Solution #3: See our famous OTN Upgrade Page
– http://www.oracle.com/technetwork/database/upgrade/index.html
23
Mistake #4: Transporting Potential Hazardous Waste
•
Garbage In, Garbage Out!
– Invalid components
– Invalid SYS or SYSTEM owned objects
– Junk in the Recycle Bin
•
Any of these could cause your upgrade to fail
24
Solution #4: Run the pre-upgrade tool
• Get the current version of utlunmi.sql
– Download it now!
–Note:884522.1
“Always visually inspect the transport vehicle for leaks or
potential problems.”
- Hazardous Materials Transportation Guide, U.S. DOT
25
But What Is a Customer To Do?
• Solve NON-VALID components before you upgrade:
– To fix up components manually:
• Note: 472937.1 Information On Installed Database
Components/Schemas
• Note: 300056.1 Debug and Validate Invalid Objects
• Note: 753041.1 How to diagnose Components with NON VALID
status
• Note: 733667.1 How to Determine if XDB is Being Used in the
Database?
– If all else fails…call Oracle Support
26
Solution #4: Properly prepare the materials
• Always check for INVALID objects:
SQL> SELECT UNIQUE object_name, object_type, owner
FROM dba_objects WHERE status='INVALID';
• Fix all INVALID objects BEFORE the upgrade!!!!!!
• There should be no invalid objects in SYS and SYSTEM
user schema
– Recompile invalid objects with utlrp.sql before the upgrade
– Compare invalid objects from before and after the upgrade
• Beginning with 11.1.0.7 the comparison has been made easier
• Run the pre-upgrade check script utlu112i.sql
• Afterwards find invalid objects in registry$sys_inv_objs and
registry$nonsys_inv_objs
• Compare after the upgrade with: utluiobj.sql
• The view DBA_INVALID_OBJECTS contains a list of invalid objects after the
upgrade
27
Solution #4: Properly dispose of any hazardous waste
• If upgrading from 10g or 11g, purge the recyclebin
SQL> purge DBA_RECYCLEBIN;
28
Solution #4: Clean Up!
29
Mistake #5: Pulling the Rug Out from Under Your
Database
• Patching Your Production Home is risky!
• ‘Out-of-place’ patchset installation has
always been best practice
• Starting with 11.2.0.2, patchsets will be
full, out-of-place installs by default
• Note 1189783.1,
Important Changes to Oracle Database
Patch Sets Starting With 11.2.0.2
30
Patchset Installation 11.2.0.2
• Recommendation: out-of-place patch upgrade!!
– If you specify an in-place patch upgrade from 11.2.0.1 to 11.2.0.2:
31
Solution #5: Create a new home
• ‘Out-of-place’ upgrade requires much
less downtime
• Safer as there is no need to patch an
existing production home
• Provides faster fallback if needed
• Full patchset eliminates overhead of
installing base release first – one step
32
Mistake #6: Living in the Past
• Clean out the cruft that has built up in your init.ora
over time
– Deprecated parameters
– Trace events
– Undocumented (underscore) parameters from old bug fixes
“Shut out all your past except that which will help
you
weather your tomorrows.”
- Sir William Osler
33
Remove Old Parameters
• Example: customer workload
• Conclusion:
– It is recommended to remove relics from previous releases and
start with the 11g defaults
34
Solution #6: Look To The Future!
• Remove "old" parameters, underscores and events from
your init.ora/spfile
• Examples:
init.ora:
<...>
_always_semi_join=off
_unnest_subquery=false
<...>
optimizer_features_enable=9.0.1
<...>
event = "10061 trace name context forever, level 10"
<...>
35
Mistake #7: Building a Potemkin Village
• Testing with Fake Data
• Using only a subset of the data
• Not enabling the same features in test as in
production
36
What’s the Worst that Could Happen?
A certain unnamed Metal Workers Union…
• Tested with only a subset of data and then went live
• Unfortunately hit an optimizer bug in 10.2.0.1 which
was only reproducible with several thousand rows of
data
• As a result, they couldn't pay out the strike pay to the
workers who went on strike the following week
37
Solution #7: Fill Your Tool Chest
• Real Data
– Data Masking
• Real Workload
– Real Application Testing
• Real Life Features Enabled
– Active Data Guard
38
Oracle Data Masking
Irreversible De-Identification
Production
Non-Production
LAST_NAME
SSN
SALARY
LAST_NAME
SSN
SALARY
AGUILAR
203-33-3234
40,000
ANSKEKSL
111—23-1111
60,000
BENSON
323-22-2943
60,000
BKJHHEIEDK
222-34-1345
40,000
• Remove sensitive data from non-production databases
• Referential integrity preserved so applications continue to
work
• Sensitive data never leaves the database
• Extensible template library and policies for automation
Oracle Confidential
39
Real Application Testing
• 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
– Move the captured workload to test system
• 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
© 2010 Oracle Corporation
40
40
Active Data Guard:
Develop & Test on Standby DB
Production
Database
• Use physical standby database
for development & testing
Developers,
Testers
Standby
Database
– Preserves zero data loss in test/dev mode
• Flashback DB to back-out
changes & use as standby
Eliminates cost of
idle DR system
41
Mistake #8: Whistling Past the Graveyard
• Have you tested your fallback strategy?
42
Solution #8: Create and Test Your Fallback
•
•
In any case: Take a backup!!!
Make sure your fallback strategy covers both cases:
–
–
•
Problems encountered during the upgrade
Problems found days, weeks after the upgrade
Then make clear:
–
If anything unforeseen happens and you'll have to step back, will
you be allowed to lose data
(i.e. changes done to the data in the system after the upgrade):
YES or NO?
•
If YES: restore a backup, flashback (since 10g)
•
If NO: export/import, downgrade, Oracle Streams, Oracle
Golden Gate
© 2010 Oracle Corporation
43
43
Solution #8: Fallback Strategy - Backup
•
Restore a backup
–
–
Complete online backup (RMAN)
Please verify:
•
•
•
Where is your backup located? Tapes, HD, off site...
Does the restore work?
How long will it take?
–
•
© 2010 Oracle Corporation
Check the priority of your restoration jobs especially in Virtual Tape
Drives?
How long will the recovery take?
44
44
Solution #8: Fallback Strategy – Offline Backup
•
No possibility to take a complete backup??
–
–
–
–
Put all data tablespaces into read-only mode
Shutdown the database immediate
Copy SYSTEM, TEMP, UNDO and SYSAUX datafiles and
controlfiles/redologs
Startup database again
–
If something fails during the upgrade:
• Shutdown the database
• Copy SYSTEM, TEMP, UNDO, SYSAUX, controlfile and
redologs from the backup location
• Startup the database in the old environment
–
Advantages:
• Fast and simple
• COMPATIBLE can be changed
© 2010 Oracle Corporation
45
45
Solution #8: Fallback Strategy - Flashback
•
Flashback Database
–
–
–
Considered to be much faster than restore/recover
• Don't change COMPATIBLE
A good solution just in case something happens during upgrade
Works beginning from 10.2
• Create a GUARANTEED RESTORE POINT
• Upgrade your database
• In case of failure flashback to the restore point
• In case of success: DON'T FORGET to delete it
SQL> CREATE RESTORE POINT grpt
GUARANTEE FLASHBACK DATABASE;
SQL> FLASHBACK DATABASE TO RESTORE POINT grpt;
SQL> SELECT * FROM V$RESTORE_POINT;
SQL> DROP RESTORE POINT grpt;
© 2010 Oracle Corporation
46
46
Solution #8: Fallback Strategy - exp/imp
•
Downgrade with exp/imp to 9.2.0.x
–
Note:158845.1
•
•
Prepare an empty database for the import just in case
you'll have to step back
Then:
–
–
–
•
Annotation for 11.1.0.7: See Note:550740.1
–
© 2010 Oracle Corporation
Run the appropiate ?/rdbms/admin/catexp.sql to create
the 9.2 export views in the upgraded database
Use "old" 9.2 exp for the export
Use "old" 9.2 imp for the import
CREATE OR REPLACE VIEW exu9defpswitches (
compflgs, nlslensem ) AS
SELECT a.value, b.value
FROM
sys.v$parameter a, sys.v$parameter b
WHERE
a.name = 'plsql_code_type' AND
b.name = 'nls_length_semantics'
/
47
47
Solution #8: Fallback Strategy: Downgrade
•
Downgrade with catdwgrd.sgl
– Note:443890.1
•
•
Downgrade to the release you've upgraded from
– 10.1.0.5
– 10.2.0.2/3/4/5
– 11.1.0.6/7
Only possible if COMPATIBLE hasn't been raised!!!
–
© 2010 Oracle Corporation
Please note:
A downgrade will only be possible to the release you've upgraded from - so if a
patch set has been applied always apply it before the upgrade starts - otherwise
you'll only be able to downgrade to the release you've patched
48
48
Upgrade Blog: blogs.oracle.com/UPGRADE
© 2010 Oracle Corporation
49
49
50
51