Upgrade Guidelines

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:

Known Issues

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:

   1:  Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX [ID 9910484.8]
   2:  Bug 8553944 - SYSAUX tablespace grows [ID 8553944.8]
   3:  Bug 10279045 - Slow Statistics purging (SYSAUX grows) [ID 10279045.8]
   4:  Bug 12958182 - SYSAUX tablespace grows (mon_mods_all$ and col_usage$) [ID 12958182.8]

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.
Handling SM/OPTSTAT component growth in SYSAUX tablespace gives an excellent overview on the problem with the historical CBO statistics.
The bigger issue is with bug 13632540. It is not fixed in 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.

2. Enable Automatic SQL Tuning, Automatic Memory Management (not on Linux of course where HugePages are enabled) and use (carefully) SQL Plan Management.
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.
For SAP users: SAP note 105047 says that “SQL Plan Management” is allowed by SAP.

3. Use DataPump to populate a fresh database, avoid script upgrade if possible. TheOracle 11gR2 Database Upgrade Guide clearly states the 6 major benefits:
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.

5. CURSOR_SHARING = SIMILAR no longer limits the number of child cursors that can be created. This could caused performance problems over time which may not appear during testing if the testing is not run long enough to create a huge number of child cursors. See My Oracle Support Document 1169017.1: ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting for details.

6. Beginning in, mutexes to manage the SGA library cache (vs using latches and pins, etc) was introduced. This can lead to Oracle Database 11g Release 2 post-upgrade performance issues. See My
Oracle Support Document 727400.1: WAITEVENT: “library cache: mutex X” for details on the “library cache: mutex” wait event and a list of bugs.

7. A large SYS.AUD$ table could cause the upgrade to appear to hang. See My Oracle Support Document 979942.1: Database upgrade appears to have halted at SYS.AUD$ Table.

8. Network ACLs – If you get a message similar to “Database contains schemas with objects dependent on network packages”, then please consult the Oracle Database Upgrade Guide, chapter 4 for further information on Network ACLs.

Pre-Checks to perform OR Things to consider during Upgrade

– PSUs, BPs and CPUs Patches
– Kernel Parameters eg. shm, hugepages,
– Pool Sizes
– SYSAUX tablespace
– Compatible Parameter
– Optimizer Features Enable
– Ensure SYSTEM and SYSAUX is optimally sized
– Take Backup

>> Capturing existing Execution plans
 >> Capturing existing Optimizer Statistics
Performing Upgrade Important Steps / Scripts
SQL> startup upgradeSQL> @catupgrd.sql

SQL> startup

SQL> @utlu112s.sql
SQL> @catuppst.sql

SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;

SQL> @utlrp.sql

srvctl upgrade database -d db-unique-name -o oraclehome

ocrconfig -upgrade

–  $ORACLE_HOME/rdbms/admin/utlu102i.sql
What its about : utlu102i.sql – UtiLity Upgrade Information. This script provides information about databases to be upgraded to 10.2.

– $ORACLE_HOME/rdbms/admin/utltzuv2.sql

What its about : utltzuv2.sql – time zone file upgrade to version 2 script. In 10g, the contents of the file timezone.dat and timezlrg.dat  are updated to the version 2 to reflect the transition rule changes   for some time zone region names. The transition rule changes of some time zones might affect the column data of TIMESTAMP WITH TIME ZONE data type.

–  $ORACLE_HOME/rdbms/admin/dbupgdiag.sql
What its about : dbupgdiag.sql – DB Upgrade/Migrate Diagnostic Information.  This script checks the integrity of the source database prior to starting the upgrade. This includes Invalid Database Objects Owned by SYS / SYSTEM, database was created as 32-bit or 64-bit, Duplicate Objects Owned by SYS and SYSTEM and their cleanup, Existence of Java-Based Users and Roles and JVM packages.

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