Oracle database 11gR2 upgrades: advanced tips and best practices

The Strugatsky brothers have a book where in the army the officer asks the soldiers if they are ready to attack the enemy. One of the men says yes but do we have incense? Why do we need incense, asks the army officer? Well, in case we meet the devil says the soldier.

Do I really need incense when upgrading to 11g? Let us see.

According to the 2011 IOUG Survey on Database Manageability, keeping databases at current patch levels is the primary DBA challenge. Of course, in order to have that current level you need to upgrade/patch the database.

1. Control the size of the SYSAUX tablespace. It grows due to two main reasons: the CBO historical statistics do not get purged automatically and the SQL Plan Baselines consume gigabytes of disk space.

By default the MMON performs the automatic purge that removes all history older than the older of:

* current time – statistics history retention (by default 31 days) and
* time of recent analyze in the system – 1

MMON performs the purge of the optimizer stats history automatically but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes then it’s aborted and the stats are not purged and no trace or alert message is reported.

You will have to manually start the DBMS_STATS.PURGE_STATS procedure or schedule a job to run it on daily basis.

The bigger issue is with bug 13632540. It is not fixed in 11.2.0.3 and SQL Plan Baselines consume more space than historical CBO data. If you do not have much data in the DB, you may witness the unique situation of having a database with more metadata than real application data.

While the automated features make sense, you might wonder doesn’t the urge to use SQL Plan Management contradict with point 1 above? Yes it does, and this makes the 11g upgrade tricky. Verify you can afford some extra space in SYSAUX if you have optimizer_capture_sql_plan_baselines = TRUE. Extra means like 10-50G. It varies from database to databases.

You may create a job that runs on regular basis: delete from sys.col_usage$ c where not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = c.obj#); That is the workaround.

Automatic SQL Tuning is really an underestimated feature in the Oracle database and I am surprised to see so many databases where the feature is not enabled. What I see and read are different blogs and articles on how to enable and disable the feature but almost nothing on real life experience.

3.1 Defragments the data. You can compress the imported data to improve performance.

3.2 Restructures the database. You can create new tablespaces or modify existing tables, tablespaces, or partitions to be populated by imported data.

3.3 Facilitates side-by-side testing of the old and new versions of Oracle Database because an entirely new database is created.

3.4 Enables the copying of specified database objects or users. Importing only the objects, users, and other items you need is useful for establishing a test environment for the new software on only a subset of the production data. Data Pump Export / Import provides flexible data subsetting capabilities.

3.5 Serves as a backup archive – you can use a full database export as an archive of the current database.

3.6 Enables the upgraded database to be established on an operating system or hardware platform that is different from that which is supporting the database being upgraded.Network-based Data Pump Import allows the new Oracle database to be directly loaded across the network from the old database being upgraded. Thus, no intervening dump files are required.

I cannot prove it scientifically but from my experience fresh/new databases are faster and less buggier than manually upgraded databases. It is probably the fragmentation that is a factor, the messed up data dictionary being artificially modified to a higher version, etc.

4. Read the upgrade documents in advance or involve someone who has already read them and has strong experience with database upgrades. Avoid exotic parameters in the init.ora file (unless you are Siebel, EBS, etc.) and gather workload system and fixed table statistics after the upgrade. Check also the “Master Note For Oracle Database Upgrades and Migrations” [ID 1152016.1].

You might think that tip 4 contradicts the title of this post: advanced tips and best practices. What is so advanced with reading the friendly manuals? What indeed 🙂 Note that Oracle list “Read the FRIENDLY manuals!” as Best Practice #1 in the paper above.

Like this:

Related

Excellent info Julian. Thanks for that. It’s been immediately more useful than all the advice I’ve been getitng from Oracle re our 11g upgrade! I wish they’d read more blogs like yours instead of sending out “experts” to client sites, advising mostly nonsense!

Wonderful beat ! I would like to apprentice at the same time as you
amend your web site, how can i subscribe for a weblog
site? The account helped me a applicable deal. I
were tiny bit familiar of this your broadcast offered bright transparent idea

Hey! I just wanted to ask if you ever have
any trouble with hackers? My last blog (wordpress) was hacked and I ended up losing
several weeks of hard work due to no back up. Do you have any methods
to prevent hackers?

Howdy exceptional blog! Does running a blog similar to this take
a massive amount work? I’ve virtually no understanding of coding but I had
been hoping to start my own blog soon. Anyways, should you have any ideas or tips for new blog owners please share.
I understand this is off subject nevertheless I just needed to ask.
Thank you!

I do believe all the ideas you have offered on your post. They are really convincing
and will definitely work. Nonetheless, the
posts are very brief for novices. May just you please prolong them a little from subsequent time?
Thank you for the post.

Julian is the Global Database Lead of Accenture. His primary responsibility is managing and leading the Global Oracle Technology Practice which includes Autonomous Cloud, IaaS, PaaS, Database Services, Engineered Systems, Java, Middleware, Security and all other areas falling under Oracle Technology. He is also the Accenture-Enkitec Group Managing Director for ... Continue reading →