Hide Notes 

Slide 2: Jay Norton Senior Principal Instructor Oracle University Oracle Corporation
Slide 3: Best Practices for Upgrading to Oracle Database 10g
Slide 4: Database 10g Upgrade Enhancements • Analysis of database before upgrade to increase likelihood of successful upgrade • Pre upgrade information tool • Upgrade Performance Enhancement • Parallel recompilation of invalid pl/sql database objects on multiprocessor CPUs • Verification of database upgrade • Post upgrade status tool
Slide 5: Reduced Complexity • Automaticly determines what components (e.g. Java, Spatial…) need to be upgraded and then performs all necessary upgrades in correct dependency order • All components in DBMS_REGISTRY • Prior to Oracle Database 10g, much of the component script invocation was manual
Slide 6: Reduced Complexity • One measure of simplicity: Database Upgrade Guide documentation size is decreasing dramatically: • 8i 512 pages • 9i 484 pages – 111 steps total for RDBMS with all 9 components! • 9iR2 344 pages • 10g 230 pages - 6 steps!
Slide 7: Prepare to Upgrade • Become familiar with the features of the new Oracle Database 10g release • Determine the Upgrade Path to the new release • Choose an upgrade Method • Choose an Oracle Home Directory • Prepare a Backup Strategy • Develop a test plan
Slide 8: 10g Upgrade Paths • Direct upgrade from 9iR2 (9.2.0), 9i (9.0.1), 8i (8.1.7), 8.0.6 • If you are not at one of these versions you need to perform a “double-hop” Upgrade • E.g. 8.1.5 -> 8.1.7 -> 10.1.0 • 7.3.4 -> 9.2.0 -> 10.1.0
Slide 9: Choose an Upgrade Method • Database Upgrade Assistant (DBUA) • The Database Upgrade Assistant is a graphical user interface (GUI) tool that interactively steps the user through the upgrade process and configures the database to run with the new Oracle Database 10g release. • Manual Upgrade • A manual upgrade consists of running SQL scripts and utilities from the command line
Slide 10: Advantages of DBUA • Performs all necessary steps for the upgrade • Interacts with the DBA if decisions need to be made • Provides complete HTML report of the upgrade process • Significantly reduces database downtime
Slide 11: Manual Upgrade • Advantage: Advantages & Disadvantages • Gives the DBA finer control over the upgrade process • Disadvantage: • More work • More error prone
Slide 12: Pre Upgrade Analysis Checks for certain known database, init.ora, etc settings that may cause upgrade to fail, and generates warnings i.e. shared_pool_size init.ora parameter is too low • Utility runs in “old server” & “old database” context • Based on “posterity” database knowledge & needs of Oracle Database 10g • DBUA automatically performs any required actions
Slide 13: Pre Upgrade Analysis • • • • • • • Database version and compatibility Redo logs <4MB Updated Parameters e.g. shared_pool_size Deprecated (renamed) parameters Obsolete parameters Cluster check Account Check
Slide 14: Pre Upgrade Analysis • Names of Components in database e.g. JVM, Spatial, etc • Tablespaces • Increase in size recommendations • New SYSAUX tablespace • Character set incompatibilities • Installed options • Upgrade time estimate
Slide 15: Key DBUA Features • The Assistant automates the upgrade process by performing all the tasks you normally accomplish manually • Invokes the Pre Upgrade Information Utility and automatically makes appropriate adjustments (including tablespace, redo logs, etc) • Checks for adequate resources (disk space, rollback segments) • Creates mandatory tablespace SYSAUX • Optionally, backs up all necessary files. • Creates new configuration files (init.ora, spfile, listener) in the new Oracle home
Slide 16: Key DBUA Features • During the upgrade process, the DBUA: • Runs all necessary scripts (pre-upgrade information utility, “u” script, post upgrade status utility) • Shows upgrade progress • Write detailed trace and logging files
Slide 17: Key DBUA Features • Settings during upgrade • DBUA disables archiving during upgrade phase • Security features • DBUA locks new users in the upgraded database • RAC-DBUA • RAC-DBUA facilitates upgrade of 8.1.7, 9.0.1, 9.2 to 10.1.0 • Upgrade done on the cluster. All database and configuration files are upgraded on all the nodes in the cluster.
Slide 18: Key DBUA Features • Customizable • ISV’s can customize DBUA to run their migration scripts • XML files driving the DBUA engine (created by pre upgrade information utility & post upgrade status utility) • Silent mode for single command upgrade i.e. dbua –silent –sid ora9idb [-backup <location>]
Slide 19: Welcome Screen
Slide 20: Select the database to upgrade
Slide 21: Tablespace
Slide 22: Log Files
Slide 23: Create SYSAUX Tablespace
Slide 24: Parallel Recompilation
Slide 25: Database Backup
Slide 26: Pre-Upgrade Summary Page
Slide 27: Upgrade Progress
Slide 28: Upgrade Results
Slide 29: Manual Upgrade • Install Oracle Database 10g • Analyze the existing database using preupgrade information tool • @?/rdbms/admin/utlu101i.sql • Make necessary adjustments based on feedback     Shutdown database Take a backup Switch to New Oracle Home Startup Database  SQL> startup upgrade
Slide 30: Manual Upgrade • Create SYSAUX tablespace • Invoke the proper upgrade “u” script • SQL> @rdbms/admin/u0801070.sql  Shutdown database  Startup database  Run utlrp  SQL>@rdbms/admin/utlrp  Check upgrade status using post upgrade status tool  SQL>@?/rdbms/admin/utlu101s.sql
Slide 31: Upgrade Validation “STARTUP UPGRADE” mode will suppress certain spurious and unnecessary errors • Catch and handle ORA-00942 on DROP TABLE • Automatically handles setting parameters that can cause problems during upgrade • DBMS_REGISTRY package and dictionary view used to record and manage information about all the component upgrades, for example: SQL> select ... from dba_registry; COMP_ID COMP_NAME VERSION STATUS ------- ---------------------- -----------CATALOG Oracle10g Catalog Views 10.1.0.1.0 VALID
Slide 32: Upgrade Validation • New “Post Upgrade Status Utility” script utlu101s.sql • Queries component REGISTRY to determine upgrade status • Provides information about invalid/incorrect component upgrades • Provides script name to re-run if necessary • DBUA calls this script to display status and then takes corrective action
Slide 33: Manual Upgrade • For further information: • Platform specific documentation on OTN • “The Self-Managing Database: Easy Upgrade” whitepaper from Thomas B. Graves available from OTN • Metalink Note: 263809.1 – “Complete checklist for manual upgrades to 10g”
Slide 34: Rolling Release Upgrade using Data Guard • Rolling Upgrade is a process and not a set of new commands • Data Guard SQL Apply is used as the foundation for the upgrade • No new SQL syntax is required • Information found in “How Oracle Database 10g Revolutionizes Availability and Enables the Grid” whitepaper from Ron Weiss available from OTN and in the Database 10g patchset readme
Slide 35: Rolling Release Upgrade using Data Guard Upgrade Redo A B Clients Patch Set Upgrades Logs Queue A B Version X 1 Version X 2 X X+1 Major Release Upgrades Initial SQL Apply Config Upgrade node B to X+1 Redo Upgrade A B A Redo B Cluster Software & Hardware Upgrades X+1 4 X+1 3 X X+1 Switchover to B, upgrade A Run in mixed mode to test
Slide 36: Rolling Upgrade Pre-requisites • The database that is being upgraded must be 10gR1 patchset 1 or higher • Maximum Performance or Availability mode must be used between the primary database and the standby database (v$database.protection_level) • The compatible parameter must match the version of the Oracle binaries being executed • The Logical Standby destination specified on the primary database must be an OPTIONAL destination
Slide 37: For more info … • Oracle University Related Offering • Oracle Database 10g: New Features for Administrators • Note 263809.1 available on Metalink • “Rolling Patch Update” white paper available on OTN
Slide 38: Reminder – Please complete the Oracle OpenWorld online session survey Thank you.
Slide 39: Q & A QUESTIONS ANSWERS