Oracle Database 11g

Are you using RAT?

Oracle’s Real Application Testing package consisting of Database Replay and the powerful SQL Performance Analyzer (SPA) gives you great possibilities to predict database performance before you’ll upgrade – with very low testing effort.

Just in case you are using RAT or plan to use it in the near future please make sure you’ve applied the following MLRs (Merge Label Request, a merge of several single patches) to your Oracle installation:

  • 11.1.0.7 Database Replay : MLR 8712466 (if either capture or replay side is on 11.1.0.7)
  • 11.1.0.7 SPA : MLR 8756594 (both for 11g SPA system OR if source/destination is on
Continue reading...

Drop SYS.PLAN_TABLE$ prior to upgrading

Dropping a possibly exististing table SYS.PLAN_TABLE$ and public synonym PUBLIC.PLAN_TABLE will avoid having invalid packages and a non-valid Oracle Server component in DBA_REGISTRY.

This will apply to all upgrades to 10.2.0.4, 11.1.0.6 and 11.1.0.7.

SQL> drop table plan_table$;
SQL> drop public synonym plan_table;

This issue has been introduced with the package DBMS_SQLPA for the SQL Performance Analyzer.

If you hit this issue already the following actions will solve it:

SQL> @catplan.sql -- recreate the plan table
SQL> @dbmsxpln.sql -- reload dbms_xplan spec
SQL> @prvtxpln.plb -- reload dbms_xplan implementation
SQL> @prvtspao.plb -- reload dbms_sqlpa
SQL> alter package SYS.DBMS_SUMADVISOR compile;
SQL>

Continue reading...