This content has been marked as final. Show 14 replies
The first 11g gotcha that comes to my mind is the fact that passwords are by default case sensitive.
The first 10g gotchas that come to my mind are :
- default statistics computed by DBMS_STATS job may have in some cases a bad impact on query execution plans (this depends if DBMS_STATS is already used in 9i and how it is used)
- GROUP BY clause does not sort any more data due to algorithm change: ORDER BY must be used.
- CONNECT role change
- AWR, ASH, ADDM requiring Tuning and Diagnostic Pack licenses are installed and enabled by default and can only be disabled with a My Oracle Support script.
Edited by: P. Forstmann on 28 mars 2012 08:54
Yes, I'm aware of the differences in versions, but more specifically I was interested in the 'gotchas' of the difference in doing an in-place migration upgrade verses doing a fresh import into a pre-created 11g database.
The most obvious advantages are things like coalescing space, using uniform extents, rebuilding indexes, etc., but our vendor claimed they did not have the time to precreate a database and then do a full import.
Otherwise, I've already found things like the compatibility was set to 10.1 in the init<sid>.ora, and Automatic Memory Management is not configured, nor are features such as flashback database, etc.
I'm trying to find other things that are not picked up when doing a migration verses a clean import into a freshly created database.
Thanks. That actually was somewhat helpful and I went through all of the recommended post-upgrade checks and tasks.
As for preference, I would always prefer to export/import into an upgraded database (especially with all latest patches allready applied to the software and newly created database). As I mentioned above, the benefits I consider are being able to clean up the entire database, create new tablespaces using uniform extents, clean up chained rows, consolidate datafiles, and coalesce space and putting related table data contigiously when importing, creating all new indexes (saving space), and then setting all of my memory management options, not including the ability to reorganize at the same time. But perhaps that is just me because I strive to gain performance.
Anyway, one thing I noticed while doing checks for tables containing long or long raws, I found that some of the sys and other oracle schemas still use longs and long raws. I thought they were obsolete now? Hmmmmm.....
Thanks again. Meanwhile, I'm still looking for anything else that needs to be checked.
Okay, these are the types of things specifically that I'm trying to catch.
Can anyone help me with these please?
Can I drop these views from my 'migrated' database?
When I check the database, these views exist, but are not valid.
Connected to: Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 1 select owner, object_name, object_type from dba_objects 2 where status = 'INVALID' 3* order by 1,2,3 SQL> / OWNER OBJECT_NAME OBJECT_TYPE -------------- -------------------------------- ------------------- PUBLIC X$BH SYNONYM PUBLIC X$KCBWDS SYNONYM PUBLIC X$KGLLK SYNONYM PUBLIC X$KSLEI SYNONYM SYS X_$BH VIEW SYS X_$KCBWDS VIEW SYS X_$KGLLK VIEW SYS X_$KSLEI VIEW 8 rows selected.
I checked our other 11.2.x databases (all created using DBCA) and these objects do not exist at all in them.
This leads me that these are left over 'trash' from the migration upgrade.
Example from another 18.104.22.168 database:
SQL> select owner, object_name, object_type, status from dba_objects 2 where object_name in ('X_$BH','X_$KCBWDS','X_$KGLLK','X_$KSLEI'); OWNER OBJECT_NAME OBJECT_TYPE STATUS -------------- -------------------------------- ------------------- -------- SYS X_$BH VIEW INVALID SYS X_$KSLEI VIEW INVALID SYS X_$KGLLK VIEW INVALID SYS X_$KCBWDS VIEW INVALID 4 rows selected.
Connected to: Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select owner, object_name, object_type, status from dba_objects 2 where object_name in ('X_$BH','X_$KCBWDS','X_$KGLLK','X_$KSLEI'); no rows selected
Well it looks like persistent research prevails, once again.
My coworker was able to find a link in one of the OTN forums that discusses these views and tables, and references an Oracle note that says they are safe to delete.
Ref: Why the x$bh view does not exist?
This is covered in MOS note# 361757.1. The X$BH view is not a part of 10g or 11g databases. Sometimes they may be there after upgrading from 9i (see note for why). If found in 10g/11g, they can be safely removed per that note (it should be invalid).
This is just one of many, many reasons why I don't like using migration upgrades....
Thanks all. I am closing this posting.
Another 'gotcha' that I caught by the migration method as opposed to the export/import upgrade method is that the default auditing is not turned on using the migration method.
Does anyone know of what commands are necessary to turn on the exact same level of auditing that is default when using DBCA to create a new 11g database?
I already know the options to audit can be see by selecting from audit_actions, but I want to know specifically which options are the options set to audit by default in 11g when creating a new database using DBAC.
I will start a new thread on this subject of default auditing of 11G in the General Database forum.
For now, reference the link below for default auditing in 11g when creating a database using DBCA.
In fact, I'm going to check the default DBCA template and that might answer my own question.
I found the default auditing options.
In Oracle Database 11g, two simple changes have been made to provide an even more powerful auditing solution. First, the database parameter audit_trail is now set to DB by default, not NONE, as it was in previous versions. This allows you to turn on auditing on any object, statement, or privilege without recycling the database.
The second change is more statements have been placed under audit by default. Here is the list:
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
CREATE PUBLIC DATABASE LINK
GRANT ANY ROLE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
GRANT ANY PRIVILEGE
CREATE ANY LIBRARY
EXEMPT ACCESS POLICY
GRANT ANY OBJECT PRIVILEGE
CREATE ANY JOB
CREATE EXTERNAL JOB
Caution: When you upgrade to Oracle Database 11g, auditing is turned on by default for the above-mentioned statements. Thus audit trails will be written to the table AUD$ in the SYSTEM tablespace, which may fill up quickly. Watch this space closely.
Actually, the above statement is not true. When you do a migration upgrade, it does not change any of the auditing settings from the prior release that your database was on.
To deal with the space issues, reference: