Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

August 31, 2007

Some Best Practices of Upgrading Oracle Database

Filed under: Oracle 11g New Features,Oracle Best Practices — H.Tonguç Yılmaz @ 1:31 pm

I will be upgrading my experiment mouse 10g Release 2 database(an emotional period for me to get apart :) to 11g Release 1 soon, so I thought remembering, consolidating and sharing some of my past experiences on upgrading an Oracle database can be useful. You may comment on these depending to your own experiences.

If an upgrade is again at my door step(usually within 3 years at most :) –

a. I first read the new features, concepts and upgrade guides of the new release. Also I check to my platform specific release notes for especially special cases.

b. I test the upgrade process carefully, load and test my applications on upgraded environment and of course test the restore/recovery plan for an unexpected situations. Here do not skip to test the third party tools(if you are using) like data extraction(unloader) or performance monitoring tools you purchased extra. Also test your backup and disaster recovery strategies, cronjobs, if there is an active-passive clustering your switchover scripts.

c. If you are also needing and believing in control and performance of the upgrade steps, you may also plan to do it manually like me. For example I plan to;
– take the database temporarily to NOARCHIVELOG mode,
– close the other instances and increase sga and pga for the database to be upgraded,
– create large redo log files,
– spooling each step to a log file during migration and
– carefully monitor os cpu and memory usage for example with top utulity, tail -f alert_SID.log, plan and guarantee sufficient space in your SYSTEM, UNDO and TEMP tablespaces.
Also there is always a well detailed metalink note for this manual upgrade path :)

Of course when I have the tolerance for downtime, prior to migration and after a successful migration a cold(clean closed) backup will always feel good. During migration restricting database connection to users, stopping listener, unix cron and oracle scheduler(jobs) for unwanted change or interrupts is very important.

Since I believe and trust in Cost based optimizer-CBO(hints are always last resorts as Mr.Lewis advices) I re-collect both object and system statistics as a post migration step(still staying in NOARCHIVELOG may be useful at this point). Also setting COMPATIBLE and OPTIMIZER_FEATURES_ENABLE init.ora parameters to enable new optimizer features is important for the quality of your tests. Always one of the last steps is checking and compiling the invalid objects after upgrading, here a report of the invalid objects prior to the migration will be useful for comparison. In fact I create lots of temporary tables with create table as select for the copies of important dictionary views like dba_tables/indexes/tab_partitions/constraints/triggers etc.

d. For a meaningful(best test strategy let me say), I clone my production database onto a machine with similar resources, this means budget of course(I linked a reference guide for cloning a database) . I run my applications under a load similar to its production and monitor the database with OEM, V$ views, events like 10046-10053 and AWR reports. Here CBO enhancements need the primary attention and you will be needing some reports, prior execution plans and reference metrics prior to upgrade for detail comparisons in order to catch a problematic change in CBO behavior.

e. Of course, no matter what the deadline is, I do not accept to upgrade the real production database until I successfully upgrade and finish my planned tests on the cloned test environment..

I will also share some notes from the 11g new features guide I saw which I think you may also be interested –

i. 11g Release 1 deprecated features;
– Oracle Ultra Search,
– Java Development Kit(JDK) 1.4,
– CTXXPATH index

ii. Important initialization parameter changes;
– USER_DUMP_DEST/BACKGROUND_DUMP_DEST/CORE_DUMP_DEST is replaced with DIAGNOSTIC_DEST parameter which defaults to $ORACLE_BASE/diag.
– If there is a null value for the UNDO_MANAGEMENT initialization parameter which means UNDO_MANAGEMENT is not set, after 11g this implies to AUTO mode.
– So to migrate to automatic undo management if you are not already some how :)
1. Set UNDO_MANAGEMENT=MANUAL
2. create a reasonable load compared to your production
3. and execute DBMS_UNDO_ADV.RBU_MIGRATION function to get the advised size for undo tablespace;

DECLARE
   undo_tablespace_MB NUMBER;
BEGIN
   undo_tablespace_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
END;
/

4. create new undo tablespace as adviced sized
5. last step will be setting UNDO_MANAGEMENT=AUTO

And some extra related readings –

Some migration experiences to share
Deffensive upgrade methods, but still no pain no gain
Take the risk and migrate to 10gR2
Cloning a Database by Howard Rogers

9 Comments »

  1. […] Some Best Practices of Upgrading Oracle Database » This Summary is from an article posted at H.Tonguç YILMAZ Oracle Blog on Friday, August 31, 2007 […]

    Pingback by University Update - Linux - Some Best Practices of Upgrading Oracle Database — August 31, 2007 @ 4:06 pm | Reply

  2. Great article. I just hope I’ll remember it when we upgrade to 11g in few years :-)

    Do you have any tips for upgrading the OS on the DB servers?
    We want to upgrade from RHEL 3 to 4 (its about time!), but we are really not sure how to manage the process…

    Comment by prodlife — August 31, 2007 @ 5:06 pm | Reply

  3. […] Yılmaz wrote best practices for upgrades. A must […]

    Pingback by Links of the week - #2 « I’m just a simple DBA on a complex production system — September 1, 2007 @ 1:00 am | Reply

  4. prodlife your question was an interesting topic for me, I did some research for an upgrade guide and even asked to linux forums of Oracle this question – http://forums.oracle.com/forums/thread.jspa?threadID=550270 But still no progress yet.

    Similar to Oracle’s best practices especially if you are using ASM, OCFS and RAC this kind of an os upgrade needs extra attention(testing I mean), I think Oracle’s Unbreakable Linux Network https://linux.oracle.com/pls/apex/f?p=101 may be another important resource for you, $99 per a year is a good price depending on your production system’s availability needs of course.

    Comment by H.Tonguç Yılmaz — September 3, 2007 @ 7:59 pm | Reply

  5. […] here Der Beitrag wurde am Friday, den 31. August 2007 um 08:31 Uhr veröffentlicht und wurde […]

    Pingback by qzchoices » Blog Archiv » Some Best Practices of Upgrading Oracle Database — October 27, 2007 @ 1:19 am | Reply

  6. Metalink Note – Oracle Upgrade Companion
    https://metalink.oracle.com/metalink/plsql/docs/ODG_201.htm

    Comment by H.Tonguç Yılmaz — December 1, 2007 @ 6:01 am | Reply

  7. Oracle Upgrade Companions for 10g and 11g –
    http://blogs.oracle.com/Support/11gAtlanta.pdf

    Comment by H.Tonguç Yılmaz — August 1, 2008 @ 1:01 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: