WIXLIB

Insert Picture Here Move to Oracle Database 11g – The whole StoryRoy Swonger, Carol Tagliaferri, Mike DietrichDatabase Upgrade & UtilitiesORACLE Corporation

Welcome!!!

Upgrade Development GroupAalok Muley, PTS

AgendaPreparationUpgradeNews and Task ListTuningPerformance TestingBest Practices

AgendaPreparationUpgradeNews and Task ListTuningPerformance TestingBest PracticesSupport PolicyPatchesCertificationDocumentationUpgrade pathsInstallationEditions

Lifetime Support PolicytodayAugust 2015August 2012July 2013July 2010R2January 2012January 2009Sustaining SupportPremier SupportExtended SupportJuly 2007R2July 2010July ry/brochure/lifetime-support-technology.pdf

Lifetime Support Policy /lifetimesupport-technology.pdf

Releases – Support-SRs9%

Upgrade to Oracle Database 11g 7.3.47.3.4R2 9.2.0.49.2.0.4 8.0.68.0.6 8.1.7.48.1.7.4R2 9.0.1.49.0.1.4

Recommended Patches

Important Alerts? Note: 161818.1

Upgrade Information / Alerts Note: 454507.1Support Status and Alerts for Oracle 11g Release 1 (11.1.0.X)

Upgrade Information / Alerts Note 738538.1 Known Issues specific to the 11.1.0.7 Patch Set

Timezone Patches Why DST timezone patches? (DST: Daylight Savings Time) The date for moving to DST has been changed for 7 differenttimezones since 2007 HST - EST - MST - EST5EDT - MST7MDT - CST6CDT - PST8PDTCurrent DST definitions are included from within the followingpatch set 10.2.0.4Oracle Database 11g requires timezone version V.4 Oracle 9i: Timezone V1 Oracle 10g: Timezone V2Without timezone V4 no upgrade to Oracle Database11g will be possible!!!

Timezone Patches Summary – 4 important ML notes: Note: 414590.1: FAQ and IssuesNote: 413671.1: Timezone V4 - Checks and Links to the Patches Note: 359145.1: Links to download utltzuv2.sql Note: 396387.1: Explanations Run script utltzuv2.sql to recognize TIMESTAMP WITH TIMEZONEFiles timezone.dat and timezlrg.dat will be applied

Timezone Patches Timezone How To: Download the right patch according to the table in Note 413671.1 Download the script utltzuv2.sql according to Note 359145.1 Run the script utltzuv2.sql in your current source databaseApply files timezone.dat and timezlrg.dat to the source ORACLE HOME/oracore/zoneinfo directory with either Opatchor manually and restart the database

Verify Database Creation Wordsize - 10.2.0.3 Has the database been created originally in a 32-bitenvironment and is now on a 64-bit platform? selectselect ata,'B023'),0,'64bit'64bit Database','32bitDatabase','32bit Database')Database') "DB"DB Creation"Creation"fromfrom kopm ;kopm ; Happens in 10.2.0.3 - see Note:412271.1 Apply patch:5871314 and patch:5892355 if: KOPM .METADATA contains 'B023'Î created in 32-bit env Don't apply patch if: KOPM .METADATA contains 'B047'Î created in 64-bit env Database version is 10.2.0.4

Related to Database Creation Wordsize - 11gR1 Database upgrade to 11.1.0.6: Delete orphan rows from KOTTD Apply patch 6770913 Database upgrade to 11.1.0.7: Delete orphan rows from KOTTD No patches necessaryselectselectnotnot ininsys nc oid sys nc oid fromfrom kottd kottd wherewhere sys nc oid sys nc oid (select(select oid oid fromfrom obj obj wherewhere type# 13);type# 13); If result is not null:deletedelete fromfrom kottd kottd wherewhere sys nc oid sys nc oid notnot inin(select(select oid oid fromfrom obj obj wherewhere type# 13);type# 13);commit;commit; See Note:579523.1 - bug:6770913

Oracle Certification

Recommended OS patches Note: 169706.1 Note: 401705.1 specifically for Linux

Upgrade is easier! The upgrade to Oracle Database 11g is much easierthan any upgrades to earlier Oracle releases Size of Upgrade guides: 8.1.7 9.0.1 9.2.0- 512 pages- 484 pages – 111 steps for an RDBMS with 9 components- 344 pages 10.1.0 10.2.0- 170 pages - only 6 stepsl!!!- 140 pages 11.1.0- 186 pages - now detailed explanations andinformation about new options included

Upgrade Length How long will the upgrade taketo complete? Independent of: Size of the database Used datatypes Dependent on: The number of objects needing upgrading and recompilation Thus: the number of installed options/components The number of synonyms – they‘ll get recompiled Valid and non-stale data dictionary statistics At a very low rate: Number of datafiles Size of redo logs

Documentation Note: 466181.1 Upgrade Companion 10gR2 Note: 601807.1 Upgrade Companion 11g

Documentation Note: 601807.1 Upgrade Companion 11g

Documentation Note: 601807.1 Upgrade Companion 11g

Documentation Upgrade Guide Note:429825.1Complete Checklist for Manual Upgrades to 11gR1 Note: 421191.1Complete checklist for manual upgrades from X to Y

Database.us.oracle.com: Upgrade Page http://database.us.oracle.com

OTN Upgrade Page /oracle11g/upgrade/index.html

OTN Upgrade Forum http://forums.oracle.com/forums/forum.jspa?forumID 583&start 0

External 11g References25-Jan-2009

External 11g References25-Jan-2009

Upgrade PathsUPGRADEExport/ImportExport/ImportYYCTAS,CTAS, COPYCOPYOSOS Change?Change?NNNNSQLSQL ApplyApplyOracleOracle StreamsStreamsYYDBUATransportableTransportable TablespacesTablespacesTransportableTransportable DatabaseDatabaseDowntimeDowntime 45min? 45min?OORRAACCLLEErreeccoommmmeennddeeddCLISQL @catupgrd

Preparation Get INVALID Objects: SQL SQL SELECTSELECT UNIQUEUNIQUE object name,object name, object type,object type, ownerownerFROMFROM dba objectsdba objects WHEREWHERE status 'INVALID';status 'INVALID';Recompile invalid objects in SYS and SYSTEM with utlrp.sql Compare invalid objects from before and after the upgrade Beginning with 11.1.0.7 the comparison has been automatedregistry sys inv objs, registry nonsys inv objs utluiobj.sql Get accurate performance statistics Comparison: before after Time specific queries and batches Test upgrade of the database Functional tests Performance tests with real life loads!!!

Preparation Possibilities for performance analysis: STATSPACK – export Perfstat user!!! Note:394937.1 STATSPACK guide AWR - extract the AWR Do AWR diff reports comparing before/after upgrade Real Application Testing SQL Performance Analyzer Database Replay

Preparation Create dictionary statistics prior to the upgrade - otherwiseit will take significantly longerSQL SQL EXECUTEEXECUTE dbms stats.gather schema statsdbms stats.gather schema stats('SYS',('SYS',optionsoptionsestimate percentestimate percentmethod optmethod optcascadecascade 'GATHER','GATHER', DBMS STATS.AUTO SAMPLE SIZE,DBMS STATS.AUTO SAMPLE SIZE, 'FOR'FOR ALLALL COLUMNSCOLUMNS SIZESIZE AUTO',AUTO', TRUE);TRUE); Detailed scripts can be found here:Oracle Database Upgrade Guide Appendix B Or in/:SQL SQL EXECUTEEXECUTE dbms stats.gather dictionary stats;dbms stats.gather dictionary stats;

Preparation Switch off DATABASE VAULT (if used/installed) Oracle Database Vault Administrator's Guide: Appendix B Unix: (simplified) Relinking without Database Vault[ make -f ins rdbms.mk dv off ] dvcadvca -action-action disabledisable . Windows: Rename oradv10.dll in ORACLE HOME\bin Upon restart: dvcadvca -action-action disabledisable . After upgrading relink with dv on or rename the DLL and enableDatabase Vault again: dvcadvca -action-action enableenable .

Preparation Drop SYS.PLAN TABLE and SYS.PLAN TABLE See Note: 605317.1 and Note:736353.1 Otherwise the component "Oracle Server" can be INVALID afterthe upgrade Introduced with DBMS SQLPA Steps to solve this issue if it has happened: @catplan.sql -- recreate the plan table@dbmsxpln.sql -- reload dbms xplan spec@prvtxpln.plb -- reload dbms xplan implementation@prvtspao.plb -- reload dbms sqlpa

Installation Space / resource requirements Software installation: 150-200 MB in /tmp or \TEMP necessary Example: Linux Seed database: 1,6 GB Seed databases are always prebuilt EE databases with all options!!! RAM: 1GB Swapspace RAM between 513Mb and 2048Mb 1.5x RAM RAM between 2049Mb and 8192Mb 1x RAM RAM more than 8192Mb 0.75x RAM

Installation How to access the software? Download from OTN:http://otn.oracle.com/software/index.html Metalink: http://edelivery.oracle.com/

Installation Download from OTN:

Installation DVD contents? Database Enterprise Edition Standard Edition Personal Edition (Windows) Client Clusterware Extra: Examples (formerly known as companion) Required ProductsOracle Globalization Support DemosOracle Multimedia DemosOracle Precompiler DemosOracle Spatial DemosOracle SQLJ DemosOracle XML DemosOracle JDBC Development DriversOracle Text Knowledge Base

Installation Pre-installation checks: Warning: 500MB swap space needed – also on Windows!!! Ask support before installing with:./runInstaller -ignoreSysPrereqs

Installation - Loopback Adapter for DBcontrol Launch the Windows Add Hardware Wizard Choose Yes, I've already connected the hardware . Scoll down to Add a new hardware device Choose Install the hardware that I manually select . Select Network Adapters:Click on Manufacturer MicrosoftInstall the Loopback AdapterMake the Loopback Adapterthe FIRST network card inTCP/IP properties!!

Installation

Installation

Installation Important:„CUSTOM“ ALWAYS installsEnterprise Edition

Installation Custom installation: 10gR2 doc installation guide 11gR1 doc installation guide

Patch Set Installation Install patch sets and patches before you start the upgrade

Unattended Installation/Configuration Two options: Oracle Universal Installer OUI ./runInstaller -record -destinationFile . ./runInstaller -silent -noconsole -responseFile . But this has to be done for a patch set, too Home Cloning (script or Provisioning Pack) Prepare a fully patched Oracle Home Create an archive consisting of all files Exclude *log, *dbf, tnsnames/listener/sqlnet.ora ./runInstaller -silent -clone -. Listener Configuration ./netca /silent /responseFile .

Installation Overview on differences of database editions and availableoptions by edition Feature differences:Note:465465.1Differences Between Enterprise, Standard and Personal Editions onOracle 10.2 Oracle Database Licensing Information11g Release 1 (11.1)Part Number B28287-01http://download.oracle.com/docs/cd/B28359 01/license.111/b28287/editions.htm

Installation Windows OS - Home Selector :on

Listener Configuration Best Practices:Create a new listener environment with NETCA:

HANDS-ON OS Users: oracle/oracle, root/welcome Install Image 11.1.0.6 and 11.1.0.7 home/oracle/11g image Environment variable files 9.2.0.8: /home/oracle/.orac-92 11g: /home/oracle/.orac-11g Oracle Users sys/oracle, system/oracle Timezone patches have already applied on 9.2.0.8 Don't create an 11g database - there's already an 9i db!

AgendaPreparationUpgradeNews andTuningDatabase Upgrade AssistantCommand Line UpgradeTask ListPost UpgradeAlternativesCRS UpgradeASM UpgradeMigrationPerformance TestingBest Practice

Database Upgrade Assistant (GUI) Features: Graphically led upgrade Lots of important checks RAC aware - inclusion of all nodes for RAC (almost) a must !!! Offline Backup and Restore possibleASM upgradeOracle XE upgradePatch upgrades Logs: ORACLE HOME/cfgtools/dbua Documentation: Oracle Database Upgrade Guide

Database Upgrade Assistant (GUI) Silent mode: dbua –help shows all valid options See doc:http://download.oracle.com/docs/cd/B28359 01/server.111/b28300/upgrade.htm