Скачать презентацию Insert Picture Here The Most Common Upgrade Mistakes Скачать презентацию Insert Picture Here The Most Common Upgrade Mistakes

6dab651b58a3caed309fb94137eeac6f.ppt

  • Количество слайдов: 49

<Insert Picture Here> The Most Common Upgrade Mistakes (and How to Avoid Them) Roy 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 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 Upgrade Mistakes 3

Agenda • What Not To Do • How Not To Do It <Insert Picture Agenda • What Not To Do • How Not To Do It 4

Mistake #1: Leaving the Job Half-Finished • Not installing the ‘latest’ patchset or PSU 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, A real life scenario – install but no apply On 8/27/2010 5: 37 AM, 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 <carol. tagliaferri@oracle. A real life scenario – install but no apply From: carol tagliaferri To: Date: 27 -08 -2010 20: 49 Subject: Re: 11. 2. 0. 1 recommended patches ? Hi 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 RTFR – Read The Friendly README Subject: Re: 11. 2. 0. 1 recommended patches ? Date: Mon, 30 Aug 2010 10: 21: 30 +0200 From: To: carol tagliaferri 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 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: 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 4 x per year on the same schedule as CPUs 10

Mistake #2: Being an Imperfect Human Being • Everybody makes mistakes – – Typos 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 7 xxxxx. 992 Upgrade Running Very Slow…. >50 hours ************( Source: ITS-RP )************** SR 7 xxxxx. 992 Owner: CAROL. US @ 600 SRInfo 7 xxxxx. 992 Organisation: CSI 12345678 Contact: DBA at Phone: 408 -518 -xxxx Email: [email protected] com 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 ========== utlu 102 i. sql stated But there is a solution…. CAUSE JUSTIFICATION ========== utlu 102 i. sql stated "shared_pool_size" needs to be increased to at least 250 MB SHARED_POOL_SIZE only set to minimum of 150 mb 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 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 250 m as suggested. regards, Happy Customer 15

Solution #2: Post Upgrade Editable PFILE • Always create an editable init. ora from 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/init. DB. ora' from spfile; << Now edit init. ora with any editor >> SQL> startup force pfile=/tmp/init. DB. 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 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 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: To: 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, Sr. Database Administrator DBA Group - IT Infrastructure 19

It Could Save You Time! From: carol tagliaferri <carol. tagliaferri@oracle. com> To: DBA@<name withheld> It Could Save You Time! From: carol tagliaferri To: [email protected] Date: 08 -09 -2009 9: 42 AM Subject: ORA-600 [22635] - Start with Metalink note 161818. 1 Hello , 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: Pay Attention to Important Alerts! • Note 161818. 1 21

Solution #3: Read the Friendly Documentation – Note: 785351. 1 Upgrade Companion 11 g Solution #3: Read the Friendly Documentation – Note: 785351. 1 Upgrade Companion 11 g Release 2 22

Solution #3: See our famous OTN Upgrade Page – http: //www. oracle. com/technetwork/database/upgrade/index. html 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 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 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: 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 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 utlu 112 i. 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 10 g Solution #4: Properly dispose of any hazardous waste • If upgrading from 10 g or 11 g, purge the recyclebin SQL> purge DBA_RECYCLEBIN; 28

Solution #4: Clean Up! 29 Solution #4: Clean Up! 29

Mistake #5: Pulling the Rug Out from Under Your Database • Patching Your Production 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 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 • 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 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 Old Parameters • Example: customer workload • Conclusion: – It is recommended to remove relics from previous releases and start with the 11 g defaults 34

Solution #6: Look To The Future! • Remove 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 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 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 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 AGUILAR 203 -33 -3234 Oracle Data Masking Irreversible De-Identification Production Non-Production 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, 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 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 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 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 10 g) • 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 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 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 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. 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 exu 9 defpswitches ( 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 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/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 Upgrade Blog: blogs. oracle. com/UPGRADE © 2010 Oracle Corporation 49 49

50 50

51 51