Transcription
Insert Picture Here Bestt PBPracticestiforf UpgradingUdi tot OracleO l DatabaseD t b11g Release 2
AgendaBest PracticesFAQSummary2011 Oracle Corporation2
Best Practice #1 Read the FRIENDLY manuals!2011 Oracle Corporation3
Documentation Note:250.1 Upgrade Advisors2011 Oracle Corporation4
Documentation Note:251.1 Database Upgrades from 10.2 to 11.22011 Oracle Corporation5
Documentation Note:264.1 Database Upgrades from 9.2 to 11.22011 Oracle Corporation6
Documentation Note:785351.1 Upgrade Companion 11g Release 22011 Oracle Corporation7
Documentation Note:785351.1 Upgrade Companion 11g Release 22011 Oracle Corporation8
Documentation Upgrade Guides http://download.oracle.com/docs/cd/E11882 01/server.112/e10819/toc.htm Note:837570.1Complete Checklist for Manual Upgrades to 11g Release 2 Note: 421191.1Complete checklist for manual upgrades from X to Y2011 Oracle Corporation9
OTN Upgrade Page /index.html2011 Oracle Corporation10
OTN Upgrade Page /index.html2011 Oracle Corporation11
OTN Upgrade Page /index.html2011 Oracle Corporation12
OTN Upgrade Forum http://forums.oracle.com/forums/forum.jspa?forumID 583&start 02011 Oracle Corporation13
Upgrade Blog: blogs.oracle.com/UPGRADE2011 Oracle Corporation14
Best Practice #2 Always upgrade Grid Infrastructure (Clusterware andASM) First!2011 Oracle Corporation15
Grid Infrastructure Installation 11.2.0.1 11.1.0.7 ORACLE BASEInventory, ADR etc. ORACLE BASEInventory, ADR etc.GRID HOMEORA CRS HOMEORA CRS HOME1.Oracle Clusterware same owner GridInfrastructureOracle Clusterware ORACLE HOME – for ASM2.Oracle ASM ORACLE HOME – for Database ORACLE HOME3.Oracle Database2011 Oracle CorporationOracle ASM ORACLE HOME – for Database ORACLE HOMEOracle Database16
Grid Infrastructure Upgrade 11g Release 2 Always upgrade Oracle Clusterware first!!! Upgrading to Oracle Clusterware 11g Release 2: Install new software into new Grid Infrastructure homeOut-of-place software upgradeGrid Infrastructure home is owned by 'root‘rootMake sure to check Note:948456.1 for known issuesOn 32-bit32 bit Windows there'llthere ll be no 32-bit32 bit Grid Infrastructure and ASM available!2011 Oracle Corporation17
Grid Infrastructure Upgrade PathsNo ASM, no Oracle ClusterwareR2Clusterware & ASM:10.1.0.5ASM:10 1 0 5Clusterware:Rolling upgradeASM:No rolling upgradeClusterwareR2Clusterware & ASM: 10.2.0.3R2ClusterwareClusterwareClusterware & ASM: rolling upgradeClusterware2011 Oracle Corporation18
Oracle Grid Infrastructure 11.2.0.2 Oracle Grid Infrastructure Patch Set 11.2.0.2: Patch set is not the correct wording: it’s a full releaseIInstallationll i iis out-of-placef lonlyl iinto a separate hhomeTo upgrade from GI 11.2.0.1 to GI 11.2.0.2: Apply PSU 11.2.0.1.2 (or newer) in-place RAC/Grid Infrastructure Upgrade Note: Rolling upgrade ASM issue for 11.2.0.1 11.2.0.2 (bug 9329767)Note:810394.1: RAC Assurance Support Team: RAC and Oracle ClusterwareStarter Kit and Best Practices (Generic)Also see platform-specific notes linked from the generic starter kitVERY IMPORTANT: Follow all instructions in Note:1212703.1Note:1212703 1 Make sure MULTICAST is setup correctly Note:1054902.1 – section DMake sure to check Oracle Database Readme 11g Release 2 Section2.39 - "Open Bugs“Then upgrade GI within OUI2011 Oracle Corporation19
Best Practice #3 Use the Upgrade Planner in My Oracle Support2011 Oracle Corporation20
Upgrade Planner2011 Oracle Corporation21
Upgrade Planner2011 Oracle Corporation22
Best Practice #4 Patch your new ORACLE HOME before you upgrade2011 Oracle Corporation23
Patch Set Installation 11.2.0.2 Download patch set 11.2.0.2 from support.oracle.com:2011 Oracle Corporation24
Patch Set Installation 11.2.0.2 Default: out-of-place patch upgrade!!! It’s a full release!!! If you specify an in-place patch upgrade from 11.2.0.1 to 11.2.0.2:2011 Oracle Corporation25
Patch Set Installation 11.2.0.2 Patch set 11.2.0.2 is a full release Therefore no need anymore to install 11.2.0.1 first!!! OnlyO l way tot dod an in-placei lpatcht h sett installationi t ll ti Backup your /dbs and /network/admin files ./runInstaller –detachHome ORACLE HOME 11.2.0.1-home ./runInstaller -detachHome ORACLE HOME /u01/orahomes/11.2.0Starting Oracle Universal Installer.Checking swap space: must be greater than 500 MB.Actual 10047 MBPassedThe inventory pointer is located at /etc/oraInst.locThe inventory is located at /u01/orabase'DetachHome' was successful. Remove your 1111.2.0.12 0 1 home contentsInstall 11.2.0.2 into the previous 11.2.0.1 homeRestore /dbs and /network/admin filesUpgrade your database with DBUA or catupgrd.sql2011 Oracle Corporation26
Recommended Patches Recommended Database Patches: Note:756671.12011 Oracle Corporation27
Patch Set Update (PSU) Installation Install PSUs Note:854428.1: Introduction to Database Patch Set Updates Note:1227443.1:N t 1227443 1 PPatcht hSSett UUpdatesd t KKnown IIssues Database PSUs include: Fixes for critical issues that may affect a large number of customersand 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 25-100 new bug fixes per PSU – usually cumulative Guaranteed to be RAC rolling installable Will be released 4x per year on the same schedule as CPUs 17-Jan-2011, 19-Apr-2011, 19-Jul-2011 and 17-Oct-2011 PSU check:h k opatch lsinventory -bugs fixed grep -i 'DATABASE PSU'2011 Oracle Corporation28
Important Alerts? Check for important alerts: Note:161818.1‘click’2011 Oracle Corporation29
Upgrade Information / Alerts Known issues in 11.2.0.x? See Note:880782.12011 Oracle Corporation30
Upgrade Information / Alerts Known issues in 11.2.0.2? See Note:1179474.12011 Oracle Corporation31
Recommended OS patches Note:169706.1: OS Installation and Configuration2011 Oracle Corporation32
Best Practice #5 Preserve performance statistics2011 Oracle Corporation33
Preparation - Testing Test the upgrade itself Functional testing – does the upgrade complete successfully? Performance testing – how long will the upgrade take? Test Post-Upgrade Performance Functional testing Will your applicationsli tirun correctly?tl ? Performance testing Gather performance data before you upgrade TimeTiimportantit t queries,ireports,t andd batchb t h jjobsb Tune your init.ora parameters, OS parameters Use real life loads! RealR lAApplicationli ti TTestingti SQL Performance Analyzer Database Replay2011 Oracle Corporation34
Preparation Collecting sufficient performance data priorto the upgrade is of vital importance Sufficient means: Starting at least 4 weeks before the upgrade Gather accurate performance statistics In Oracle 8i/9i: Use STATSPACK Export the PERFSTAT user right before the upgrade Note:466350.1 STATSPACK before/after upgrade In Oracle 10g/11g: Use AWR Take snapshots every 30-60 minutes – retention: 30 days Extract the AWR with: SQL @?/rdbms/admin/awrext.sql For 10.1 only use: DBMS SWRF INTERNAL.EXTRACT AWR Use AWR DIFF reports to compare before & after upgrade performance:DBMS WORKLOAD REPOSITORY.AWR DIFF REPORT HTML2011 Oracle Corporation35
Best Practice #6 Pre-upgrade Sanity operations2011 Oracle Corporation36
Invalid Objects Always check for INVALID objects:SQL SELECT UNIQUE object name, object type, ownerFROM dbadba objectsobjects WHERE status status 'INVALID';INVALID ; There should be no invalid objects in SYS and SYSTEMuser schema Recompile invalid objects with utlrp.sql Compare invalid objects from before and after the upgrade Beginning with 11.1.0.7 the comparison has been automated Find invalid objects in registry sys inv objs andregistry nonsys inv objs Compare before-after: utluiobj.sql The view dba invalid objsdba invalid objs contains a list of invalid objects after theupgrade2011 Oracle Corporation37
Sanity Operations If upgrading from 10g or 11g, purge the recyclebinSQL purge DBA RECYCLEBIN;2011 Oracle Corporation38
Best Practice #7 Always run the pre-upgrade script: Upgrade to Oracle Database 11.2 : utlu112i.sql2011 Oracle Corporation39
Pre-Upgrade Check Run utlu112i.sql in your current environmentOracle Database 11.2 Pre-Upgrade Information Tool09-21-2009 *******-- name:ORCL-- version:10.2.0.3.0-- compatible:10.2.0.3.0-- blocksize:8192-- platform:Linux IA (32-bit)-- timezone file: *************************UpdateUd t PParameters:t[U[Updated t OOraclel DDatabaset b1111.22 init.orai itor spfile]fil *********************WARNING: -- "java pool size" needs to be increased to at least 64 *************************Miscellaneous ****************************WARNING: -- Database is using a timezone file older than version 11. After the release migration, it is recommended that DBMS DST package. be used to upgrade the 10.2.0.3.0 database timezone version. to the latest version which comes with the new releaserelease.2011 Oracle Corporation40
Command Line Upgrade Get the current version of utlunmi.sql Download it now! Note:884522.12011 Oracle Corporation41
Best Practice #8 Remove "old" parameters, underscores and events fromyour init.ora/spfile Examples:pinit.ora: . always semi join offunnest subquery false . optimizerpfeatures enable 9.0.1 . event "10061 trace name context forever, level 10" . 2011 Oracle Corporation42
Sanity Operations – Real World Upgrade of ORDIM component only from 9.2.0.8 to 11.2. These underscore parameters and events were set:complex view merging FALSEmulti join key table lookup FALSElibrary cache advice FALSEindex join enabled FALSEpush join union view FALSEpush join predicatepush join predicate FALSEalways semi join OFFpred move around FALSEunnest subquery FALSEpredicate elimination enabled FALSEeliminate common subexprp FALSEno or expansion FALSEevent '600 trace name systemstate level 10'event '600 trace name errorstack level 10'event '942 trace name errorstack level 10'event '54 trace name systemstate level 10'event '54 trace name errorstack level 10'event '7445 trace name systemstate level 10'event '7445 trace name errorstack level 10'event '10195 trace name context forever, level 1'event '10778 trace name context forever, level 1‘2011 Oracle CorporationUpgrade time:49 minutesUnsetunderscoresand eventsUpgrade time:7 minutes!!43
Best Practice #9 Leave COMPATIBLE at the original value for a weekbefore changing to 11.2.2011 Oracle Corporation44
Parameter COMPATIBLE COMPATIBLE has to be at least 10.1.0 for an 11g database No way back once 11.1.0 has been enabled Supported release downgrade to 1010.1.0.5,1 0 5 10.2.0.2, 10 2 0 2 11.1.0.6 11 1 0 6 Flashback database to 10.2.0.2 No ALTER DATABASE RESET COMPATIBILITY command anymoreCOMPATIBLE 10.0/1/2.0COMPATIBLE 11.0.011 0 02011 Oracle Corporation45
Parameter COMPATIBLE DBUA raises COMPATIBLE only for 9i databases To enable new features after the upgrade: 11.1:11 1: 11.211 2 :SQL alter systemset compatible '11.1.0' scope spfile;SQL alterltsystemtset compatible '11.2.0' scope spfile; Afterwards: restart the database New features will be enabled DatafileD t fil hheadersdwillill bbe adjusteddj t d Redologfiles will be adjusted during first access2011 Oracle Corporation46
Best Practice #10 Test your fallback strategy!2011 Oracle Corporation47
Fallback Strategy In any case: Take a backup!!!Make sure your fallback strategy covers both cases: ProblemsPblencounteredt ddduringi ththe upgradedProblems found days, weeks after the upgradeThen make clear: If anything unforeseen happens and you'll have to step back, willyou 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, OracleGolden Gate2011 Oracle Corporation48
Fallback Strategy - Issues during upgradeALWAYS take a complete ONLINE backup with RMAN 10.2Set all data tablespaces into READONLY mode and take an OFFLINEbackup of SYSTEM, SYSAUX,UNDO, TEMP, redologs controlfilesCreate a guaranteedrestore point forFlashback DatabaseChange COMPATIBLE parameter?YesYesNoRestore andrecoveronline backupShutdown, restore the offlinebackup files and startupin source environmentFlashback tothe guaranteedrestore point2011 Oracle Corporation49
Fallback Strategy – Issues after upgrade Assumption: No data loss allowedAGAIN take a complete ONLINE backup with RMAN after the upgrade 10.1Change COMPATIBLE parameter?YesYesNoexp with SOURCE expand import back into anempty database withsource impUse Oracle Streamsor Oracle Golden Gatefor a capture/applyof data changes back tothe source releaseDowngrade withthe catdwgrd.sqlcatdwgrd sqland the catrelod.sqlscripts back to thesource release2011 Oracle Corporation50
Best Practice #11 After the upgrade .2011 Oracle Corporation51
Post Upgrade Create system statistics during a regular workload period otherwise non-appropriate values for the CBO will be used:SQL exec DBMS STATS.GATHER SYSTEM STATS('start');. – gather statistics while running a typical workloadSQL exec DBMS STATS.GATHER SYSTEM STATS('stop');SQL select pname NAME, pval1 VALUE, pval2 INFOfrom aux stats ;NAMEVALUE INFO-------------------- ---------- 04-03-2009 12:30DSTOP05-03-2009 ED255945.605.2011 Oracle Corporation52
Post Upgrade Example: customer OLTP workload Runtime without system statistics: 2:19h Runtime with system statistics: 2:07h 9% faster2011 Oracle Corporation53
Post Upgrade Create fixed table statistics Directly after catupgrd.sql has been completed This will speed up processing for recompilation with utlrp.sqlSQL exec DBMS STATS.GATHER FIXED OBJECTS STATS; CCreatet fixedfi d ttablebl statisticst ti ti againi afterft a weekk withith regularlproduction workload This task should be done only a few times per year2011 Oracle Corporation54
Post Upgrade - SPFILE Always create an editable init.ora from the current SPFILEafter the upgrade has been finished Prevents rewrite in case of setting wrong parameters orforced 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 DBCASQL create pfile '/tmp/initDB.ora'//from spfile; Now edit init.ora with any editor SQL startup force pfile /tmp/initDB.oraSQL create spfile from pfile;SQL Parameter can be changed by:SQL alter system set PARAMETER VALUEPARAMETER VALUE scopescope both;both;2011 Oracle Corporation55
AgendaBest PracticesFAQSummary2011 Oracle Corporation56
FAQ: Which Version Should I UpgradeTo?todayyR2January 2015August 2015August 2012July 2010 July 2011R2January 2018July 2013January 2012January 2009Sustaining SupportPremier SupportExtended SupportJuly 2007 July 2008R2July 20009200082000720006200052000420003200022011 Oracle Corporation57
Upgrade to Oracle Database 11g Release 2 7.3.49.2.0.8R2 9.2.0.4 8.0.6R2 8.1.7.410.1.0.5 10.2.0.2 9.0.1.4R2"Empty" arrows mean: no specific patch release required2011 Oracle Corporation58
FAQ: How long will the upgrade take?2011 Oracle Corporation59
Upgrade Length How long will the upgrade taketo complete? Independent of: Size of the database Used datatypes Dependent mainly on: The number of installed components and optionsValid and non-stalenon stale data dictionary statisticsNumber of synonyms – they'll get recompiled (upgrade from 9i)Number of objects in XDBAt a very lowlrate,t if COMPATIBLE isi iincreased:d Number of datafiles Size of redo logs2011 Oracle Corporation60
Example: Database Upgrade Time Usually between 30 and 90 minutes Dependent mainly on installed options and components *Actual*A t l titimes may vary ComponentHH:MM:SSOracle Server00:16:17JServer JAVA Virtual Machine00:05:19Oracle Workspace Manager00:01:01Oracle Enterprise Manager00:10:13Oracle XDK00:00:48Oracle Text00:00:58Oracle XML Database00:04:09Oracle Database Java Packages00:00:33Oracle Multimedia00:07:43Oracle Expression Filter00:00:18Oracle Rule Manager00:00:12Gathering Statistics00:04:53Total Upgrade Time: 00:52:312011 Oracle Corporation61
Upgrade Length Speed up your upgrade performance by: Possibly switch off archiving Make sure this will comply with your business rules Do NOT do this if you are using Standby Database or Golden Gate! CCreatingti didictionarytistatisticst ti ti righti ht bbeforefththe upgraded Oracle 9i:SQL exec DBMS STATS.GATHER SCHEMA STATS('SYS‚,options 'GATHER',estimate percent DBMS STATS.AUTO SAMPLE SIZE, method opt 'FORALL COLUMNS SIZE AUTO', cascade TRUE);(Be aware: EXECUTE command does not allow line breaks!!) Oracle 10g/11g:SQL exec DBMS STATS.GATHER DICTIONARY STATS;2011 Oracle Corporation62
FAQ: Which Method Should I Use?UPGRADEExport/ImportNCTAS, COPYYStay on same OS?NSQL ApplyDowntime 30min?YOracle StreamsDBUATransportable TablespacesCLITransportable DatabaseSQL @catupgrdOracle Golden Gate2011 Oracle Corporation63
When to Choose the DBUA Can afford 30 – 90 minutes average downtime Operating system remains the same GUI is preferred ooverer manmanualal command line interface Automatically performs useful pre-upgrade checks Less error-prone / less manual effort Existing database is at least 9.2.0.8 Note: especially useful for RAC databases Consideration: Source and target Oracle Homes must be on the same system Cannot be rere-runrun if an error is encountered mid-upgrademid upgrade2011 Oracle Corporation64
When to Choose Command-Line SQL spool upgrade.logSQL @catupgrd.sqlCan afford 30-90 minutes average downtimeManual command-line interface is preferred over GUIE isting database is at least 9.2.0.8Existing9208Migrating to a new hardware platform with same OS Consideration Cannot upgrade to a system with a different operating systemarchitectureMore manual steps requiredPotential for errors due to typos,ypmissed details2011 Oracle Corporation65
When to Choose an Alternative Method Alternative methods include Original exp/imp or Data Pump expdp/impdp)Oracle Streams or Oracle Golden GateData Guard (SQL Apply)Transportable Tablespaces, Tansportable DatabaseM i dMovingdatat viai CREATE TABLE AS SELECT or otherth techniquest h i Alternative methods must be used when Movingg to a different operatingpg systemypplatform ((32- and 64-bitversions of an OS are considered “the same platform” in this case Upgrading from a release older than 9.2.0.8 Alternative methods may be a good option when Minimal downtime ( 30 minutes) required or desired Re-organizing database storage or schemas2011 Oracle Corporation66
AgendaBest PracticesFAQSummary2011 Oracle Corporation67
Summary Preparation and planning are they keys to a successfulupgradeg The DBUA is the recommended method for simplicity andease-of-use HaveHa ffallbackllb k strategyt tandd ttestt it Oracle Database 11g Release 2 is a stable release withlots of great features, so go for it!2011 Oracle Corporation68
2011 Oracle Corporation69
11g Release 2. Agenda Best Practices FAQ Summary 2011 Oracle Corporation 2. Best Practice #1 Read the FRIENDLY manuals! 2011 Oracle Corporation 3. . If upgrading from 10g or 11g, purge the recyclebin SQL purge DBA_RECYCLEBIN; 2011 Oracle Corporation 38. Best Practice #7 Always run the pre-upgrade script: Upgrade to Oracle .
