Move To Oracle Database 11g – The Whole Story

3y ago
81 Views
2 Downloads
5.42 MB
134 Pages
Last View : 1m ago
Last Download : 5m ago
Upload by : Jacoby Zeller
Transcription

Insert Picture Here Move to Oracle Database 11g – The whole StoryMike Dietrich. Carol Tagliaferri, Roy SwongerDatabase Upgrade & UtilitiesORACLE Corporation

Welcome!!!

AgendaPreparationUpgradeNews and Task ListDiagnostics & TuningPerformance TestingBest Practices

AgendaPreparationUpgradeNews and Task ListDiagnostics & TuningPerformance TestingBest PracticesSupport PolicyPatchesCertificationDocumentationUpgrade pathsInstallationEditions

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

Lifetime Support ngSupportMajor Product and Technology Releases999Technical Support999Access to Knowledge Base(MetaLink/Customer Connection/SupportWeb)999Updates, Fixes, Security Alerts and Critical PatchUpdates99PrePre-existing OnlyTax, Legal and Regulatory Updates99NoUpgrade Scripts99NoCertification with existing Third PartyProducts/Versions99NoCertification with New Third PartyProducts/Versions9NoNoCertification with new Oracle Products99No

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

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 Linux x86, x86-64, and s390x Requirements Reference List

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 Thus: the number of installed options/components Valid and non-stale data dictionary statistics Number of synonyms – they‘ll get recompiled (upgrade from 9i) Number of objects in XDB At a very low rate if COMPATIBLE will be increased: 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 http://download.oracle.com/docs/cd/B28359 01/server.111/b28300/toc.htm Note:429825.1Complete Checklist for Manual Upgrades to 11gR1 Note: 421191.1Complete checklist for manual upgrades from X to Y

OTN Upgrade Page /oracle11g/upgrade/index.html

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

40 External 11g References 1/205-May-2009

40 External 11g References 2/205-May-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 automated registry 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 If upgrading from 10g or 11g, purge the recyclebinSQL SQL purgepurge DBA RECYCLEBIN;DBA RECYCLEBIN;

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 Or in/ 'GATHER','GATHER', DBMS STATS.AUTO SAMPLE SIZE,DBMS STATS.AUTO SAMPLE SIZE, 'FOR'FOR ALLALL COLUMNSCOLUMNS SIZESIZE AUTO',AUTO', TRUE);TRUE);: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 Alert-Note:782735.1 and Notes: 605317.1 and 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 Between 2.7 GB (Windows), 3.5 GB (Linux) and 6.9 GB (HP-UX) Seed database: 1,6 GB Seed databases (ORCL) are always prebuilt EE databases with alloptions!!! RAM: 1GB Swapspace RAM up to 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 Microsoft Loopback Adapter is recommended if IP addressis distributed via DHCP Install the MS Loopback Adapter Launch the Add Hardware wizard and install a new networkadapter - manufacturer is Microsoft Adjust this adapter to be the primary network adapter My Network Places Properties TCP/IP IP address: 10.10.10.10 Subnet mask: 255.255.255.0 Edit: \windows\system32\drivers\etc\hosts 10.10.10.10 mycomputer.mydomain.com mycomputer http://download.oracle.com/docs/cd/B28359 01/install.111/b32006/reqs.htm#sthref291

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 guidehttp://download.oracle.com/docs/cd/B28359 01/install.111/b32002/install overview.htm#BABEGJFD

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 OH/inventory/response/netca.rsp

Installation Overview on differences of database editions and availableoptions by edition Feature differences:Note.465465.1Differences Between Enterprise, Standard and Personal Editions on Oracle 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:

AgendaPreparationUpgradeDatabase Upgrade AssistantCommand Line UpgradeNews and Task ListPost UpgradeAlternativesCRS UpgradeDiagnostics & TuningASM 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/cfgtoollogs/dbua Documentation: Oracle Database Upgrade Guidehttp://download.oracle.com/docs/cd/B28359 01/server.111/b28300/toc.htm

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

Database Upgrade Assistant (GUI) dbua [ -silent ] [ -sid SID ] [-oracleHome home name][-oracleBase base name] [-diagnosticDest diagnostic destination][-sysDBAUserName SYSDBA user] [-sysDBAPassword SYSDBA pwd][-upgradeASM] [-autoextendFiles] [-newGlobalDbName db name][-newSid new SID] [-generateMapFile] [-useASM][-commonFileLocation common files] [-omfLocation omf area][-databaseMapFile map file name] [-newRecoveryArea recover area][-newRecoveryAreaSize recover size] [-apexAdminPassword apex pwd][-disableUpgradeScriptLogging ] [-backupLocation directory][-sysauxTablespace -datafileName name -datafileSize size-datafileSizeNext size -datafileSizeMax size][-postUpgradeScripts script [, script ] . ][-initParam parameter value [, parameter value ] . ][-disableArchiveLogMode] [-recompile invalid objects true false][-degree of parallelism cpu number][-emConfiguration {CENTRAL LOCAL ALL NOBACKUP NOEMAIL NONE}-dbsnmpPassword password -sysmanPassword password-asmPassword password -hostUserName hostname-hostUserPassword password -backupSchedule hh:mm[-smtpServer server name -emailAddress address][-centralAgent location] [-localRacSid SID]][-recoveryAreaDestination directory][-h -help]

Database Upgrade Assistant (GUI) Example:dbua -silent -sid dwh-oracleHome /opt/oracle/product/RDBMS10g-diagnosticDest /opt/oracle/diag-sysDBAUserName sys-sysDBAPassword manager-recompile invalid objects true-degree of parallelism 4-emConfiguration LOCAL-dbsnmpPassword manager-sysmanPassword manager

Database Upgrade Assistant (GUI) Before you start DBUA: Run OH 11g/rdbms/admin/utlu111i.sql in your currentenvironment Check especially the components status in DBA REGISTRY To remove (or reinstall) components manually:Note:472937.1 Information On Installed Database ComponentsNote:753041.1 How to diagnose Components with NON VALID status

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)FromFrom 11g11gonalwayson alwaysSECURESECURE

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

Database Upgrade Assistant (GUI)

AgendaPreparationUpgradeDatabase Upgrade AssistantCommand Line UpgradeNews and Task ListPost UpgradeAlternativesCRS UpgradeDiagnostics & TuningASM UpgradeMigrationPerformance TestingBest Practice

Command Line Upgrade Typical scenario: e.g. changing to a new serverServer(OLD)3)4)5)Server(NEW)1)2)Install Oracle11g softwareCopy utlu111i.sql6)7)Apply suggested changesStart the upgrade processRun utlu111i.sqlShutdown databaseCopy all relevant files(dbf, ctl, log, spfile, orapw)

Command Line Upgrade Step-by-step:1. Complete online backup of the database2. Install 11g Oracle software and apply patch set 11.1.0.73. Analyze the DB using utlu111i.sql and follow allrequirements given by the script4. Create a new 11g listener with NETCA5. Switch to the new environment, startup the DB (startupupgrade) and create the SYSAUX tablespace (if source db isan Oracle 9i db)6. Run upgrade script catupgrd.sql7. Recompile with utlrp.sql - compare with utluiobj.sql8. Run catuppst.sql if you are upgrading from 10g9. Check the post upgrade status: utlu111s.sql

Command Line UpgradeInfo Upgrade information script: utlu111i.sql Run in the environment of the source database Checks all init parameters and displays warnings for obsolete anddeprecated parameters Checks Components Tablespace SYSAUX National Characterset Timezone file version check Cluster check

Command Line Upgrade utlu111i.sql: DB info and time zone check? (see also PREPARATION)n DST (Daylight Savings Time) patches for Version V4 have to beapplied as a requirement for the upgrade to Oracle Database 11g REGISTRY DATABASE TZ VERSION 4o

Command Line Upgrade utlu111i.sql: Tablespaces adequate size?p

Command Line Upgrade utlu111i.sql: Init parameter changes?q

Command Line Upgrade utlu111i.sql: Components and options?r Annotation:You'll have to install all options installed for the release you areupgrading from – otherwise some components can’t be upgraded To remove (or reinstall) components manually:Note:472937.1 Information On Installed Database ComponentsNote:753041.1 How to diagnose Components with NON VALID status

Command Line Upgrade - 10gR2 utlu102i.sql: Typical pitfalls Ideally includethe Companion-CD's"Oracle Database Products"in all installations:

Command Line Upgrade Create Dictionary statistics Shutdown the database (IMMEDIATE/NORMAL) Adjust init parameters: COMPATIBLE 10.1.0SGA TARGET 340MB (32-bit) . 640MB (64-bit)PGA AGGREGATE TARGET 25MBLOG ARCHIVE FORMAT must contain %s, %t and %r Move init.ora/SPFILE and PWDsid.ora to their new location Create a new 11g-Listener (use the NETCA) Change environment to point to the new ORACLE HOME

Command Line Upgrade Windows only: Delete the old Service: oradim -DELETE -SID ORCL Create a new Service: oradim -NEW-SID ORCL-SYSPWD passwrd-STARTMODE a-PFILE initfileORADIM creates a logfile in %ORACLE HOME%\database

Command Line UpgradeSQL SQL STARTUPSTARTUP UPGRADE;UPGRADE;ALTER SYSTEM SETAutotune of undoALTER SYSTEM SETALTER SYSTEM SETALTER SYSTEM SETALTER SYSTEM SETResource ManagerALTER SYSTEM SETResource Managersystem trig enabled FALSE SCOPE MEMORY;retention is turned off.undo autotune FALSE SCOPE MEMORY;undo retention 900 SCOPE MEMORY;aq tm processes 0 SCOPE MEMORY;enable ddl logging FALSE SCOPE MEMORY;disabled during database migration: plan '' not setresource manager plan '' SCOPE MEMORY;disabled during database migrationTaken from an example alert.log Supresses unnecessary error messages likeORA-00942: table or view does not exist thus logfiles will be easier to read and check

Command Line Upgrade Create tablespace SYSAUX (only if source is a 9i db) :SQL SQL CREATECREATE TABLESPACETABLESPACE sysauxsysauxDATAFILE'file' SIZESIZE 500M500MDATAFILE 'file'EXTENTEXTENT MANAGEMENTMANAGEMENT LOCALLOCALSEGMENTSEGMENT SPACESPACE MANAGEMENTMANAGEMENT AUTOAUTOONLINE;ONLINE;

Command Line Upgrade One upgrade scripts for all releases and all components:SQL SQL @catupgrd.sql@catupgrd.sql Useful:SQL SQL SPOOLSPOOL c:\temp\upgrade.logc:\temp\upgrade.log

Command Line Upgrade Recompilation: utlrp.sql C

Lifetime Support Policy Feature Premier Support Extended Support Sustaining Support Major Product and Technology Releases 9 9 9 Technical Support 9 9 9 Access to Knowledge Base (MetaLink/Customer Connection/SupportWeb) 9 9 9 Updates, Fixes, Security Alerts and Critical Patch Updates 9 9 Pre-existing Only Tax, Legal and Regulatory Updates 9 9 NNoo

Related Documents:

Oracle Database 11g Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions, Second Edition Dear Reader, Expert Oracle Database Architecture, 2nd Edition is a book that explores and defines the Oracle d

Oracle 11g New Features for Administrators . Summary Sheets . Version. 2.3 . . Oracle Database 11g New Features for DBAs and Developers, by Sam R. Alapati and Charles Kim, Apress, ISBN: 978-1-59059-910-5 . 2 . Book . Oracle Database 11g New Features by Rober G. Freeman, Oracle Press . 3 .

PeopleSoft Oracle JD Edwards Oracle Siebel Oracle Xtra Large Model Payroll E-Business Suite Oracle Middleware Performance Oracle Database JDE Enterprise One 9.1 Oracle VM 2.2 2,000 Users TPC-C Oracle 11g C240 M3 TPC-C Oracle DB 11g & OEL 1,244,550 OPTS/Sec C250 M2 Oracle E-Business Suite M

Oracle TIGHT / Oracle Database 11g & MySQL 5.6 Developer Handbook / Michael McLaughlin / 885-8 Oracle TIGHT / Oracle Database 11g & MySQL 5.6 Developer Handbook / Michael McLaughlin / 885-8 Chapter 1: Architecture 5 The client software component provides an interactive and batch interface that lets you and your programs interact with the database.

viii Related Documentation The platform-specific documentation for Oracle Database 10g products includes the following manuals: Oracle Database - Oracle Database Release Notes for Linux Itanium - Oracle Database Installation Guide for Linux Itanium - Oracle Database Quick Installation Guide for Linux Itanium - Oracle Database Oracle Clusterware and Oracle Real Application Clusters

Oracle Database using Oracle Real Application Clusters (Oracle RAC) and Oracle Resource Management provided the first consolidation platform optimized for Oracle Database and is the MAA best practice for Oracle Database 11g. Oracle RAC enables multiple Oracle databases to be easily consolidated onto a single Oracle RAC cluster.

OBIA 11.1.1.10.1 OBIEE 11g ODI 11g Oracle DB 12.2 Out of Place Migration In Place Upgrade Out of Place Migration WebLogic 11g OAS 5.5 OBIA 11.1.1.10.2 OBIEE 11g ODI 11g DW WebLogic 11g OBIA

Oracle Database 11g Pl a n Ba sel ine Plan History HJ GB 4.Upgrade to 11g 5.Migrate stored outlines into SPM Figure 4 Bulk load the SMB after upgrade using Stored outlines. From the Cursor Cache Starting in Oracle Database 11g it is possible to load plans for statements directly from the cursor cache into the SQL Management Base.File Size: 417KBPage Count: 26