Notable General Essentials
Options Have Major Impact on Timing
The actual upgrade process is dependent on the options
installed on the system and not the amount of data. I have had previous experience in which a
terabyte database took approximately 45 minutes to actually do the conversion
and a 300 megabyte database took approximately 80 minutes to convert. The 300 megabyte database had more options
installed such as JAVA and XML.
CBO Changes Often Impact Performance
The cost base optimizer has substantial differences between
the 10g and 11g versions. This often
will have adverse effects on the sql plans and therefore executions. If possible need to use Database Replay, SQL
Performance Analyzer and SQL Plan Management to mitigate these changes.
Network ACLs
When schemas have objects depending on network packages then
you will need to configure Network ACLs.
This is not straight forward and will take additional effort. Oracle XML
DB must be installed if one or more of the following packages are used:
UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDR NOTE:453786.1. Please see the
Database Upgrade Guide for an example:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17222/upgrade.htm#sthref148
and MOS Note:958129.1 on how to setup Network ACLs
Time Zone File
This took a little effort in previous releases to update but
it seems with 11gR2 they have made it easier with a DBMS_DST package. The databases need to be checked for this and
final upgrade document needs to include the steps if needed.
Recycle Bin
If you don’t purge the DBA_RECYCLEBIN it can cause
problems. In fact we had an upgrade fail
because we were unable to clean this out.
There was an Oracle Bug at the time and it took a rollback of the system
to finally solve the problem.
Stale Statistics
Need to make sure statistics including data dictionary are
updated before the night of the upgrade.
Recompiling Invalid Objects
If there are still connections between databases then this
needs to be pushed out until after all databases have been upgraded and the
listeners are up. Do NOT mark this
within the GUI if there are database links between databases and materialized
views depend on them.
Diagnostic Directory
The location of the trace files change as well as the
format. If there are any monitoring
tools that use these then they will need to change.
Component Installation
Make sure that the components available within the 10g
instance have been installed in the 11g home.
COMPATIBLE parameter
Compatible has to be at least 10.1.0 but there is no way
back to 10g once it has been set to 11.1.0 or greater. Keep it at 10.2.0.x for a week or more.
SQL> alter system set compatible=’11.1.0’ scope=spfile;
A restart of the database will need to occur but New
features will be enabled, datafile headers adjusted and redologfiles will be
adjusted.
Duplicate objects in SYS and SYSTEM
Need to check on duplicate objects within these
schemas. Some are ok but many are not
and need to be fixed. NOTE: 1030426.6
Case sensitive passwords automatically set to true
SQL> select username, password_versions from dba_users;
Need to make sure database links have appropriate passwords. What about users and system passwords?
Materialized Views Refresh
Need to make sure the upgrade is scheduled around
Materialized Views refreshes or they are turned off.
SELECT * FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE
o.obj# = s.obj# AND o.owner# = u.user# AND o.type# = 42 AND bitand(s.mflags, 8)
= 8;
Incremental Stats for Partitioned Tables
Switching on incremental statistics collection for
partitioned tables is highly recommended. INCREMENTAL must be set to TRUE for a
partitioned table. Stats for that table
must be gathered with GRANULARITY parameter set to AUTO: Gather statistics for
touched partition(s) ONLY:
• Documentation:
http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#autoId11
No comments:
Post a Comment