Database patch management Best Practices // IT Management Solutions

Database patch management Best Practices

Oracle database 11gR2 upgrades: advanced tips and best practices

In Database tuning, DBA, Oracle database on

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.

For basic tips and best practices on how to upgrade to 11gR2, read carefully Best Practices for Upgrading to Oracle Database 11g Release 2, the issues I describe below come on top of Oracle’s document and are based on hands-on experience, I am far from advertising how simple the 11g upgrade is:

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.

Have a look at these:

Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX [ID 9910484.8] Bug 8553944 - SYSAUX tablespace grows [ID 8553944.8] Bug 10279045 - Slow Statistics purging (SYSAUX grows) [ID 10279045.8] Bug 12958182 - SYSAUX tablespace grows (mon_mods_all$ and col_usage$) [ID 12958182.8] Bug 13632540 - SQL PLAN BASELINES GENERATES TOO MUCH DATA ON LOB SEGMENT, SYS.SYS_LOBXXX

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 might also like


by marella

• Database Development and Administration. Data Analysis and Reporting
• Computer Training
• IT Support and Maintenance
• Graphic Design and Desktop Publishing
DBA / Data Specialist 1997 – 2001
Promoted to this position quickly due to independence, drive, and initiative. Performed majority of database development and maintenance for this mid-sized package consolidation firm. Directed the design, development, and maintenance of customized client information database. Met with management and direct users to define system and report generation requirements

Reply to curious

by 0AdviceNeeded

Systems Analysis Methods
Management Information Systems
Business Communication Systems
Ethical Issues in Information Systems
Information Systems Logic and Design
Integrating Technology into Business Processes
Technical Writing
Nothing technical such as programming or database modeling (I did the BA instead of the BS degree).
I would like a career that is more specified to things such as interaction with people (HR for example) and solving problems.
I chose Soc as a second major because I liked the flexability it allowed with ideas.
I will get that book. Thx!

Attn: Resume_Pros

by Advice_Please1

To work in a challenging position to use my career developed Customer Service and Administrative skills.
1987, Bachelor of Science, Information Systems Management, University of San Francisco
03/06 – Present Quality Assurance Assistant, Western Forge & Flange Co.
Receive, examine & process metallurgical documents for accuracy, completeness, and conformity to requirements. Resolve unacceptable documents with vendor. Stamp to endorse, certify and file documents. Enter & retrieve information from the manufacturing company’s database

互联网时代的网络自动化运维  — 中关村在线
CMDB(Configuration Management Database) 配置管理数据库(以下简称:CMDB),将IT基础架构的所有组件存储为配置项,维护每个配置项的详细数据,维护各配置项之间的关系数据以及事件、变更历史等管理数据。通过将这些数 ..

Addison-Wesley Database Systems: A Practical Approach to Design, Implementation and Management (5th Edition)
Book (Addison-Wesley)

Related Posts

Copyright © . All Rights Reserved