14 Replies Latest reply: Apr 3, 2012 11:46 AM by user12159859 RSS

    gotcha's of doing a database migration....

    user12159859
      Hello,

      Environment: Oracle 11.2.0.2 database on AIX 6.1.
      No RAC, No DG, No Streams, No MTS, etc.

      Our organization has contracted software maintained by the vendor in which we were recently finally able to upgrade from 9.2.0.4 to 11.2.0.2.
      Unfortunately, the vendor was required to do the work, and the vendor chose to do a database upgrade using the in-place migration method.
      The reason was because our organization could not provide adequate space on the same server to create a new database to import into, and, there was not enough time to allow an export/import (which I disagree because we are only dealing with a 130Gb database).

      Anyway, enuf said about that. My question is what kinds of gotchas should I be looking for?

      Obviously the tablespaces and datafiles were already precreated from the original database and we can not convert them to uniform size extents without completely rebuilding the tablespaces.

      SGA and PGA were not set to use AMM, nor even memory_target.

      I recall from the old days of migrating an Oracle 8 to Oracle 9 database, there were several gotchas, such as with use of rollback segments, dictionary managed tablespaces, default tablespaces set to temp tablespaces, etc., but what about in version 11.2?

      Are there any gotchas that I can check for and resolve?

      I already found things in the init<sid>.ora file such as compatibility set to 10.1, and non-use of Automatic Memory Management.

      What else should I check for?

      Thx...
        • 1. Re: gotcha's of doing a database migration....
          P.Forstmann
          Most of the changes for 10.1, 10.2, 11.1 and 11.2 releases are listed in the relevant Upgrade Guide section "Behavior Changes After Upgrading Oracle Database": http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#I638061
          • 2. Re: gotcha's of doing a database migration....
            user12159859
            Thanks P. Forstmann.

            This is somewhat helpful, but I was hoping someone has had recent experience and can remember some gotchas that they encountered by doing the migration upgrade path as opposed to export/import.

            Best regards.
            • 3. Re: gotcha's of doing a database migration....
              P.Forstmann
              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
              • 4. Re: gotcha's of doing a database migration....
                user12159859
                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.
                • 5. Re: gotcha's of doing a database migration....
                  Srini Chavali-Oracle
                  There are no gotchas of doing an in-place upgrade (via DBUA ?) versus export/import - in fact, it is the recommended method.

                  I would suggest you read thru the post-upgrade steps (http://docs.oracle.com/cd/E11882_01/server.112/e23633/afterup.htm) to determine which of these apply to you.

                  HTH
                  Srini
                  • 6. Re: gotcha's of doing a database migration....
                    user12159859
                    Hi Srini,

                    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.
                    • 7. Re: gotcha's of doing a database migration....
                      user12159859
                      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?

                      Connected to:
                      Oracle Database 11g Enterprise Edition Release 11.2.0.2.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.
                      When I check the database, these views exist, but are not valid.
                      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.
                      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.
                      Example from another 11.2.0.2 database:
                      Connected to:
                      Oracle Database 11g Enterprise Edition Release 11.2.0.2.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
                      • 8. Re: gotcha's of doing a database migration....
                        user12159859
                        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.
                        • 9. Re: gotcha's of doing a database migration....
                          Srini Chavali-Oracle
                          Pl open an SR with Support before deleting any X$ objects. These are internal Oracle structures that could corrupt your database if you mess with them.

                          HTH
                          Srini
                          • 10. Re: gotcha's of doing a database migration....
                            user12159859
                            There is already a note from Oracle that I reference in the previous post that says it is okay to drop these objects. Also note that they are views, such as X_$HB that point to base tables such as X$HB that do not exist in 11g.
                            • 11. Re: gotcha's of doing a database migration....
                              user12159859
                              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.

                              e.g.,

                              audit session;

                              audit ????
                              • 12. Re: gotcha's of doing a database migration....
                                Srini Chavali-Oracle
                                Pl explain what you mean by "default auditing" - AFAIK, no audit actions are turned on by default in any release.

                                Srini
                                • 13. Re: gotcha's of doing a database migration....
                                  user12159859
                                  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.

                                  http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/
                                  • 14. Re: gotcha's of doing a database migration....
                                    user12159859
                                    I found the default auditing options.

                                    Ref. http://www.oracle.com/technetwork/articles/sql/11g-security-100258.html

                                    ----

                                    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:

                                    ALTER SYSTEM
                                    SYSTEM AUDIT
                                    CREATE SESSION
                                    CREATE USER
                                    ALTER USER
                                    DROP USER
                                    ROLE
                                    CREATE ANY TABLE
                                    ALTER ANY TABLE
                                    DROP ANY TABLE
                                    CREATE PUBLIC DATABASE LINK
                                    GRANT ANY ROLE
                                    ALTER DATABASE
                                    CREATE ANY PROCEDURE
                                    ALTER ANY PROCEDURE
                                    DROP ANY PROCEDURE
                                    ALTER PROFILE
                                    DROP PROFILE
                                    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:

                                    http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/