![Page 1: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/1.jpg)
Enterprise Best Practices SIG
Maria Anderson, I.S.P., ITCP
Suncor Energy
CSI DBA: Anatomy of a Database Upgrade
Session #S312120
![Page 2: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/2.jpg)
Speaker Qualifications
• Database & Middleware Team Lead, Suncor Energy
• Previous role – SAP DBA
• Oracle DBA – 12 years
• OCP designation – Oracle8, 8i, 9i, 10g
• Information Systems Professional (I.S.P.) designation – CIPS
• Various roles – systems analyst, application developer, instructor, technical architect
• Various industries – health care, oil and gas
• Present regularly at the Calgary Oracle Users Group and COLLABORATE
• Professional Development Track Manager, COLLABORATE 10
![Page 3: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/3.jpg)
Session Objectives
To …
• gain an appreciation for developing a solid test plan prior to upgrading a database.
• gain a solid understanding of the database upgrade process.
• learn about the specific steps involved in a database upgrade, as well as troubleshooting.
![Page 4: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/4.jpg)
Session Outline
• Introduction
• Documentation, Planning and Testing
• Installation and Patching
• Step-by-step Database Upgrades
• Troubleshooting an Unsuccessful Upgrade
• Summary
![Page 5: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/5.jpg)
What is not discussed …
• ASM
• RAC
• Data Guard or other stand-by solutions
• Streams or other data replication methods
• Windows
• UNIX platform anomalies
Examples and demos are based on Solaris SPARC
![Page 6: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/6.jpg)
Barriers to Upgrading
• No immediate business value perceived in upgrading – risk outweighs potential benefits
• Risk to the business is high
• Requires considerable time and effort
• Takes business and application support team members away from other projects
• Timing is not right (e.g., year-end financial processes)
• Stability is comforting
• No “surprises” with current release
• Endless patching and upgrading cycles
• More outages and disruption to business
![Page 7: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/7.jpg)
Reasons to Upgrade
• Remain on a supported database release
• Take advantage of new features or increased performance
• Introduce business process improvements
• Compatibility with operating system and hardware
• Resolve bug or issue
• Recommendation by vendor for purchased application
• Become, or remain, compliant with legislation
• E.g., Sarbanes-Oxley (SOX), Payment Card Industry (PCI)
![Page 8: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/8.jpg)
Documentation and Planning
![Page 9: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/9.jpg)
Documentation
• Oracle Database 11g Release 1 (11.1) New Features Guide
• Oracle Database 11g Release 1 (11.1) Upgrade Guide
• My Oracle Support (formerly Metalink)
• Upgrade Companion for 11g – Doc ID# 601807.1
• Upgrade Companion for 10g – Doc ID# 466181.1
• Oracle Upgrade Path Reference List – Doc ID# 730365.1
• Complete Checklist for Manual Upgrades to 11gR1 – Doc ID# 429825.1
• OTN Oracle 11g home page
• IOUG website and Select Journal
• Books written by industry experts
![Page 10: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/10.jpg)
Direct Upgrade Paths to 11g
Source: Oracle Database11g Release 1 (11.1) Upgrade Guide, Oracle Technology Network (http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/toc.htm).
![Page 11: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/11.jpg)
Project Plan: Step 1
• List of candidate databases – rank lowest to highest visibility/criticality
• Review 11g new features
• Research bugs (open and resolved)
• Develop high-level test plan
• Discuss plan with business/clients – determine dates
• Engage change management
• More detailed technical plan should flow from high-level plan
![Page 12: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/12.jpg)
Project Plan: Step 2
• Detailed test plan should include:• Upgrade testing
• Functional testing
• Interface testing
• Performance testing
• Stress/load testing
• Oracle client testing
![Page 13: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/13.jpg)
Real Application Testing
• Introduced with Oracle 11g comprised of two components:• Database replay• SQL Performance Analyzer (SPA)
• Both are now available in release 9.2.0.8 and later to assist with upgrades to Oracle 11g
• Database replay offers the ability to capture a production system’s workload and then replay it back in a test or other non-production environment
• Provides a realistic sampling of the SQL executed in a production system and a good indication of the workload
• After the workload is replayed in a test environment, database replay provides analysis and reporting to highlight potential issues
• SQL Performance Analyzer (SPA) can assist in determining which SQL statements may suffer performance degradation with an upgrade.
• NOTE: this is a separately licensed option!• Doc ID #560977.1 for more information
![Page 14: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/14.jpg)
Real Application Testing
Source: “Oracle Database 11g: The Top New Features for DBAs and Developers”, Arup Nanda, Oracle Technology Network (http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-replay.html)
![Page 15: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/15.jpg)
Project Plan: Step 3
![Page 16: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/16.jpg)
Project Plan: Step 3
![Page 17: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/17.jpg)
Benefits of a Technical Implementation Plan
• Supplements change management documentation
• Audit trail for compliance requirements
• Ensures upgrades are completed the same way
• Crucial steps are not missed
• Serves as working document for next upgrade project
![Page 18: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/18.jpg)
Prepare to Upgrade
• Impact on database management infrastructure
• Modification to scripts
• DIAGNOSTIC_DEST replaces bdump, cdump, udump locations
• Grid Control agents
• RMAN catalog
• CONNECT role changed in 10g
• Passwords are now case-sensitive
• Passwords in database links encrypted
• Oracle 11g uses version 4 time zone files (V$TIMEZONE_FILE)
• Deprecated/obsolete initialization parameters
• Backup strategy
![Page 19: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/19.jpg)
Compatibility Considerations
• Ensure COMPATIBLE parameter set appropriately
• Test thoroughly before setting COMPATIBLE to 11.0.0
• Once set to 11.0.0, cannot downgrade
Source: Oracle Database11g Release 1 (11.1) Upgrade Guide, Oracle Technology Network (http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/toc.htm).
![Page 20: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/20.jpg)
Installation and Patching
![Page 21: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/21.jpg)
Installing Oracle Database 11g
• First time install should not be on a production server
• Certification matrix on My Oracle Support (formerly Metalink) – is your operating system certified for 11g?
• Refer to official documentation prior to attempting an installation
• Readme document and release notes – known compatibility issues, default behaviour changes, etc.
• Backup the oraInventory directory in $ORACLE_BASE
![Page 22: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/22.jpg)
Oracle 11g Installation
![Page 23: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/23.jpg)
Oracle 11g Installation
![Page 24: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/24.jpg)
Operating System Prerequisite Checks
Checking operating system requirements ...
Expected result: One of 5.9,5.10
Actual Result: 5.10
Check complete. The overall result of this check is: Passed
========================================================
Checking for the correct operating system update version...
Check complete. The overall result of this check is: Passed
========================================================
Checking operating system package requirements ...
Checking for SUNWbtool; found CCS tools bundled with SunOS(SUNWbtool). Passed
Checking for SUNWcsl; found Core Solaris, (Shared Libs)(SUNWcsl).Passed
Check complete. The overall result of this check is: Passed
========================================================
Checking kernel parameters
Expected result: BIT_SIZE=64
Actual Result: BIT_SIZE=64
Check complete. The overall result of this check is: Passed
========================================================
Checking physical memory requirements ...
Expected result: 922MB
Actual Result: 32640MB
Check complete. The overall result of this check is: Passed
========================================================
< … snip … >
![Page 25: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/25.jpg)
Oracle 11g Installation
![Page 26: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/26.jpg)
Oracle 11g Installation
![Page 27: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/27.jpg)
Oracle 11g Installation
![Page 28: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/28.jpg)
Oracle 11g Installation
![Page 29: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/29.jpg)
Patching to release 11.1.0.7
• Be sure to download the correct patch set for your platform and release
• Compare the size of the downloaded/transferred file
• Read the README.html file that is included with the patch set – very important information on how to apply the patch!
• Start the OUI from the <patchset_directory>/Disk1, not the current $ORACLE_HOME
• After applying the patch set and upgrading the database, consider applying the latest CPU.
![Page 30: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/30.jpg)
Applying patch set 11.1.0.7
![Page 31: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/31.jpg)
Applying patch set 11.1.0.7
![Page 32: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/32.jpg)
Applying patch set 11.1.0.7
![Page 33: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/33.jpg)
Applying patch set 11.1.0.7
![Page 34: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/34.jpg)
Applying patch set 11.1.0.7
![Page 35: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/35.jpg)
Applying patch set 11.1.0.7
![Page 36: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/36.jpg)
Applying patch set 11.1.0.7
![Page 37: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/37.jpg)
Applying patch set 11.1.0.7
![Page 38: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/38.jpg)
Applying patch set 11.1.0.7
![Page 39: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/39.jpg)
Upgrade Methods
• Direct methods• DBUA
• Manual upgrade – scripts
• Indirect methods
• Export/import or Data Pump
• Table copy
• Transportable tablespaces
![Page 40: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/40.jpg)
Running utlu111i.sql script
• Analyzes database and recommends changes prior to upgrading
• Copy utlu111i.sql from new $ORACLE_HOME/rdbms/admin to new location
• Start SQL*Plus and log into database as SYS (or another user with SYSDBA privileges)
• Spool output and run script
• Review output and make recommended changes priorto attempting an upgrade to 11g
![Page 41: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/41.jpg)
Sample utlu111i.sql output
Oracle Database 11.1 Pre-Upgrade Information Tool 02-19-2008 21:03:45
.
**********************************************************************
Database:
**********************************************************************
--> name: ORATEST
--> version: 10.2.0.3.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Solaris[tm] OE (64-bit)
--> timezone file: V3
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 725 MB
.... AUTOEXTEND additional space required: 245 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 453 MB
.... AUTOEXTEND additional space required: 428 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 423 MB
.... AUTOEXTEND additional space required: 173 MB
< … snip … >
![Page 42: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/42.jpg)
Upgrade using the Database Upgrade Assistant (DBUA)
![Page 43: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/43.jpg)
Using DBUA to Upgrade
• Preferred method to upgrade a database
• Less control over process, but also less prone to error
• On UNIX, start up the DBUA from new $ORACLE_HOME/bin …
./dbua
• Welcome screen will appear …
![Page 44: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/44.jpg)
Using DBUA to Upgrade
![Page 45: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/45.jpg)
Using DBUA to Upgrade
![Page 46: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/46.jpg)
Using DBUA to Upgrade
![Page 47: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/47.jpg)
Using DBUA to Upgrade
![Page 48: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/48.jpg)
Using DBUA to Upgrade
![Page 49: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/49.jpg)
Using DBUA to Upgrade
![Page 50: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/50.jpg)
Using DBUA to Upgrade
![Page 51: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/51.jpg)
Using DBUA to Upgrade
![Page 52: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/52.jpg)
Using DBUA to Upgrade
![Page 53: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/53.jpg)
Using DBUA to Upgrade
![Page 54: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/54.jpg)
Post-upgrade Tasks
• Modify environment variables to reference new $ORACLE_HOME …
• ORACLE_HOME
• PATH
• LD_LIBRARY_PATH
• SHLIB_PATH
• Modify listener.ora and start listener from 11g $ORACLE_HOME
• Reset user passwords to take advantage of case sensitivity
• Take another backup before releasing it to users
• Never start/stop the database with the old software!
![Page 55: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/55.jpg)
Manual Database Upgrade
![Page 56: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/56.jpg)
Manual Database Upgrade
• Backup the database and stop all automated jobs.
• Copy init.ora and password file from old $ORACLE_HOME/dbs to new $ORACLE_HOME/dbs.
• Remove deprecated and obsolete parameters. Set COMPATIBLE parameter.
• Convert rollback segments to automatic UNDO. Create a SYSAUX tablespace if not already there.
• Shut down the database; modify oratab.
• Modify environment variables to point to new $ORACLE_HOME. Navigate to $ORACLE_HOME/rdbms/admin.
• Log into SQL*Plus, start the database with the ‘upgrade’ option …
sqlplus /nolog
connect / as sysdba
startup upgrade;
![Page 57: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/57.jpg)
Manual Database Upgrade
• Run catupgrd.sql from the new $ORACLE_HOME/rdbms/admin. Spool the output.
• The catupgrd.sql script should shut down the database as a last step. If not, shut down the database and restart it.
• Create spfile from the pfile used to start the database; restart the database.
• Run the utlu111s.sql script. All components should be VALID.
• Run the catuppst.sql and utlrp.sql to perform post-upgrade tasks and recompile invalid objects.
![Page 58: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/58.jpg)
Sample output from utlu111s.sql
SQL> @utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 02-23-2008 15:38:35
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 01:15:32
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:34:01
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:03:36
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:02:41
OLAP Catalog
. VALID 11.1.0.6.0 00:02:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:01:11
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:41:45
Oracle XDK
. VALID 11.1.0.6.0 00:02:05
Oracle Text
. VALID 11.1.0.6.0 00:02:50
Oracle XML Database
. VALID 11.1.0.6.0 00:13:25
< … snip … >
![Page 59: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/59.jpg)
Potential Issues with 11g Upgrade
• Re-running catupgrd.sql may not behave as intended• Oracle developers have found bugs in 11.2 which have been
resolved
• Running utlrp.sql may cause “maximum # of sessions exceeded” error message
• Bug #7450096 – work is ongoing to resolve this
![Page 60: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/60.jpg)
Troubleshooting a Database Upgrade
![Page 61: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/61.jpg)
What can possibly go wrong?!
Problem/Issue
• OUI or oraInventory issues – upgrade not started
• Upgrade process hangs or halts with unexpected ORA- error.
• Upgrade fails and downgrade is not possible.
• Upgrade fails, downgrade is not possible, and database cannot be restored.
• Upgrade completes successfully, but performance issues are discovered after the fact.
• Upgrade completes successfully, but unexpected behaviour is discovered with a process (batch, online, etc.) which interrupts business processes.
• Upgrade completes successfully, but interfaces with other systems no longer work.
Mitigated with Testing?
• Yes
• Yes
• Yes
• Yes
• Yes
• Possibly
• Possibly
![Page 62: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/62.jpg)
Logs – Where to find information
• 11g Installation …• $ORACLE_BASE/oraInventory/logs
• DBUA …
• $ORACLE_BASE/cfgtoollogs/dbua/<SID>/
• Manual upgrade …
• Spooled output from running upgrade scripts
• Other logs …
• Alert log and trace files – located in BACKGROUND_DUMP_DEST and USER_DUMP_DEST
• DIAGNOSTIC_DEST location (11g only)
![Page 63: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/63.jpg)
When to Revert Back
• Upgrade will overrun the scheduled maintenance window and/or impact SLAs.
• The upgrade process is suspect, or fails with an error.
• A bug is discovered with the new release that interrupts critical business functions (e.g., month end financials).
• Performance issues impact mission-critical business functions or processes.
![Page 64: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/64.jpg)
Criteria for Database Downgrade
• Can only downgrade to the release upgraded from• E.g., if database was upgraded from 10.1, can downgrade to
10.1 but not 10.2
• COMPATIBLE parameter must not have been set to 11.0.0
• If previous release was 10.1, then must be at 10.1.0.5
• If previous release was 10.2, then must be 10.2.0.3
![Page 65: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/65.jpg)
Downgrading a Database
• Shut down the database and listener. Navigate to $ORACLE_HOME/rdbms/admin.
• Start up the database with the ‘downgrade’ option …
• Run the catdwgrd.sql. Spool the output.
sqlplus /nolog
connect / as sysdba
startup downgrade;
@catdwgrd.sql
• Shut down the database. Copy init.ora and password files back to previous $ORACLE_HOME/dbs.
• Modify environment variables to point to previous $ORACLE_HOME.
![Page 66: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/66.jpg)
Downgrading the Database
• Navigate to $ORACLE_HOME/rdbms/admin.
• Restart the database from the previous $ORACLE_HOME with the upgrade option.
• Run the catrelod.sql and spool the output.sqlplus /nolog
connect / as sysdba
startup upgrade;
@catrelod.sql
• Shut down and restart the database.
• Recompile any invalid objects with the utlrp.sql script.
• If in doubt, restore and recover the database.
![Page 67: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/67.jpg)
If downgrading is not an option …
• Direct, in-place upgrade …• Restore and recover database to a point in time prior to start of
upgrade
• Direct upgrade on copy of database …
• Shut down upgraded database, restart original database
• Indirect upgrade (export/import, DataPump, etc.) …
• Shut down upgraded database, restart original database
![Page 68: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/68.jpg)
Lessons Learned
![Page 69: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/69.jpg)
Keys to a Successful Upgrade
• Initiate database upgrade projects – don’t wait to be pulled into an upgrade project by your business users or clients
• Research the new features of the release you wish to upgrade, as well as bugs/issues.
• Work closely with business users and application support teams to define test plan.
• Utilize a Technical Implementation Plan (TIP) or something similar to manage and document technical details.
• Time spent planning and testing is never wasted!
![Page 70: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/70.jpg)
Questions?
![Page 72: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/72.jpg)
Save the Date!
April 18 – 22, 2010
Mandalay Bay Convention Center
Las Vegas, Nevada
![Page 73: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/73.jpg)
Become a Complete Oracle Technology and Database Professional
Join the IOUG online at www.ioug.org and get immediate access to:
• Member Discounts and Special Offers
• SELECT Journal
• Library of Oracle Knowledge (LoOK)
• Member Directory
• Special Interest Groups
• Discussion Forums:
• Access to Local and Regional Users Groups:
• 5 Minute Briefing: Oracle
• Volunteer Opportunities
![Page 74: CSI DBA: Anatomy of a Database Upgrade Session #S312120gavinsoorma.com/wp-content/uploads/2011/03/anatomy_of-_a... · 2011. 3. 21. · Maria Anderson, I.S.P., ITCP Suncor Energy CSI](https://reader034.vdocuments.us/reader034/viewer/2022051806/60025ae25e1afe4baa525bbe/html5/thumbnails/74.jpg)
Stop by the IOUG Booth
Moscone Center West – 2nd Floor
• Register for Raffles and Giveaways
• Find out more about IOUG
• Fill out a SIG interest card
• Tell them I sent you!!!