This discussion is archived
13 Replies Latest reply: Aug 28, 2009 5:45 AM by 491476 RSS

Move/upgrade from 10g 32-bit to 11g 64-bit

user633661 Newbie
Currently Being Moderated
Hi,

I have to perform a database move&upgrade and I wonder what's the easiest/safest method to achieve that. The source system is DB 10.2.0.1 on Windows 2003 32-bit, the target system is DB 11.1.0.6 on Windows 2003 64-bit. Also, the source is single instance and the target is RAC, but for the upgrade process I think that's irrelevant. So there's a change in both the database version and OS, and that's what bothers me.

I thought to perform a RMAN backup on the source, restore it on the target and then run the DBUA (on the target of course). Before performing the backup I'd perform the steps presented in the metalink DocId 556477.1 "Complete Checklist for Upgrades to 11gR1 using DBUA", i.e. upgrade the timezone definition file on the source to version 4 and run the utlu111i.sql script on the source to find out what could be the potential problems. Do you think that approach is feasible?

I know there's also a option of using transportable tablespaces like someone already asked here: http://kr.forums.oracle.com/forums/thread.jspa?messageID=3302687 but what bothers me is that you can't transport the SYSTEM tablespace where all the user PL/SQL code is stored, then there's an additional step of transporting user schemas with expdp/imdp and maybe some additional steps which I'm not aware of. So, that's why I prefer RMAN - because that way I know everything gets transported.

RMAN's Transpotrable Database (CONVERT DATABASE comand) can't be used as far as I know, since the source and the target DB versions are not equal.

As a last resort a full database expdp/impdp could be performed, but I'd prefer not to.

Thanks in advance for any suggestions.

Regards,
Jure
  • 1. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    491476 Expert
    Currently Being Moderated
    You should install the 11g version on the 32-bit server (32-bit version of Oracle) and the 64-bit server (64-bit version of Oracle). Upgrade the database to 11g on the 32-bit server. Once this is upgraded, you can copy the database to the 64-bit server. Create the new Oracle Window's service with oradim and perform the updgrade to 64-bit with the following:
    SQL> startup upgrade
    SQL> @?\rdbms\admin\utlip.sql
    SQL> @?\rdbms\admin\utlrp.sql
  • 2. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    user633661 Newbie
    Currently Being Moderated
    Thank you for the answer, I'll try it and report back.

    Just if someone else is interested, in the meantime I tried the procedure I described above (RMAN backup on 10g and restore on 11g). The restore and recover went without problems, the database also opened in upgrade mode (ALTER DATABASE OPEN RESETLOGS UPGRADE), but when I checked the alert log I found the following message:
    Completed: alter database open resetlogs upgrade
    Tue Aug 18 16:07:30 2009
    Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x28] [PC:0x8EA65C5, priver()+319]
    Errors in file c:\app\administrator\diag\rdbms\upgorcl\upgorcl\trace\upgorcl_ora_912.trc  (incident=24147):
    ORA-07445: exception encountered: core dump [priver()+319] [ACCESS_VIOLATION] [ADDR:0x28] [PC:0x8EA65C5] [UNABLE_TO_READ] []
    Incident details in: c:\app\administrator\diag\rdbms\upgorcl\upgorcl\incident\incdir_24147\upgorcl_ora_912_i24147.trc
    Tue Aug 18 16:07:33 2009
    Sweep Incident[24147]: completed
    Tue Aug 18 16:07:33 2009
    Trace dumping is performing id=[cdmp_20090818160733]
    Tue Aug 18 16:10:42 2009
    Errors in file c:\app\administrator\diag\rdbms\upgorcl\upgorcl\trace\upgorcl_ora_1352.trc  (incident=24148):
    ORA-00600: internal error code, arguments: [kkaegen_get_edition_name_1], [], [], [], [], [], [], []
    Incident details in: c:\app\administrator\diag\rdbms\upgorcl\upgorcl\incident\incdir_24148\upgorcl_ora_1352_i24148.trc
    Tue Aug 18 16:10:44 2009
    Trace dumping is performing id=[cdmp_20090818161044]
    On metalink that ORA-600 is already known as bug no. 6620187.

    Also, when running the utlu111s.sql (which actually isn't necessary to run before running catupgrd.sql on 11g), I got the mesasge "ORA-04023: Object SYS.STANDARD could not be validated or authorized". So either I have done something wrong or that't really a bug.

    Regards,
    Jure
  • 3. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    user633661 Newbie
    Currently Being Moderated
    Hi,

    I actually couldn't perform an upgrade as suggested by ebrian yet, so in the meantime I tried to perform a full Datapump export of the production database (10.2.0.1 32-bit) and a full import on the test database (11.1.0.6 64-bit) and I have a question. Probably it has been answered already many times but I couldn't find any answer on metalink or in the Oracle documentation. If anyone has it, I'll be glad to read it.

    Before starting the full datapump import, I created the tablespaces on the test DB as they exist on the production DB. Then I issued:
    impdp system/****@testDB full=y directory=DATA_PUMP_DIR dumpfile=prod_export.dmp logfile=prod_export.log
    What bothers me is that because of already existing objects in the SYSTEM, SYSMAN, etc. system schemas, I get something like 1264 errors:
    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1264 error(s) at 15:42:21
    The errors are like:
    ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_GENERIC" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_ID" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVORS_S" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVOR_NAME_S" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_REPPROP_KEY" already exists
    ....
    ORA-31684: Object type SYNONYM:"PUBLIC"."MGMT$SOFTWARE_ONEOFF_PATCHES" already exists
    ORA-31684: Object type SYNONYM:"PUBLIC"."MGMT$SOFTWARE_OTHERS" already exists
    ORA-31684: Object type SYNONYM:"PUBLIC"."MGMT$SOFTWARE_PATCHES_IN_HOMES" already exists
    ....
    ORA-39151: Table "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    ORA-39151: Table "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_H" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    ORA-39151: Table "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_G" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    Does anyone know if it's ok to just ignore those messages?

    Someone import/export full=y suggested to skip importing some non-user schemas.

    On the other hand Tom Kyte suggests that there should be no problems (although he's reffering to the "old" exp/imp): http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1463976400346989259#1466510300346628449 :
    "if you start with a 'fresh' database, a full import is 'safe' and has been done by many thousands of people - in particular when they move across different platforms with old releases (before cross platform transports)"

    Any thoughts will ne appreciated very much.
  • 4. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    491476 Expert
    Currently Being Moderated
    Those type of errors are to be expected and can be ignored. Verify with dba_registry and dba_objects that all components/objects are valid.
  • 5. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    user633661 Newbie
    Currently Being Moderated
    Thank you for the quick response ebrian!

    I must be really lucky, since after a full import I get the following in the alert log:
      Replication after-import: trying to enable constraint REPCAT$_SITES_NEW_FK2 for "SYSTEM"."REPCAT$_SITES_NEW"
      Replication after-import: constraint REPCAT$_SITES_NEW_FK2 for "SYSTEM"."REPCAT$_SITES_NEW" is enabled successfully
    Fri Aug 21 17:01:34 2009
    Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE, kgegec()+76]
    Errors in file c:\app\administrator\diag\rdbms\upgorcl\upgorcl\trace\upgorcl_j000_3892.trc  (incident=51362):
    ORA-07445: exception encountered: core dump [kgegec()+76] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE] [UNABLE_TO_READ] []
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06550: line 1, column 96:
    PLS-00201: identifier 'EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS' must be declared
    ORA-06550: line 1, column 96:
    PL/SQL: Statement ignored
    Incident details in: c:\app\administrator\diag\rdbms\upgorcl\upgorcl\incident\incdir_51362\upgorcl_j000_3892_i51362.trc
    Fri Aug 21 17:01:40 2009
    Trace dumping is performing id=[cdmp_20090821170140]
    Fri Aug 21 17:01:42 2009
    Sweep Incident[51362]: completed
    After that I tried to recompile invalid objects with the utlrp.sql script and it went ok. Then I wanted to run first the utlip.sql and then the utlrp.sql script (as you suggested doing after an upgrade from 32 to 64 using DBUA). The result was again an ORA-07445 error:
    Fri Aug 21 17:05:09 2009
    Errors in file c:\app\administrator\diag\rdbms\upgorcl\upgorcl\trace\upgorcl_j000_2240.trc:
    ORA-04045: errors during recompilation/revalidation of TESTUSER.LOGON_TRIGGER
    ORA-01031: insufficient privileges
    Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE, kgegec()+76]
    Errors in file c:\app\administrator\diag\rdbms\upgorcl\upgorcl\trace\upgorcl_j000_2240.trc  (incident=51476):
    ORA-07445: exception encountered: core dump [kgegec()+76] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75520BE] [UNABLE_TO_READ] []
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06550: line 1, column 96:
    PLS-00201: identifier 'EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS' must be declared
    ORA-06550: line 1, column 96:
    PL/SQL: Statement ignored
    Incident details in: c:\app\administrator\diag\rdbms\upgorcl\upgorcl\incident\incdir_51476\upgorcl_j000_2240_i51476.trc
    Fri Aug 21 17:05:11 2009
    Trace dumping is performing id=[cdmp_20090821170511]
    Fri Aug 21 17:05:13 2009
    Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x18] [PC:0x8CB341F, hshget1()+199]
    Errors in file c:\app\administrator\diag\rdbms\upgorcl\upgorcl\trace\upgorcl_ora_1536.trc  (incident=51354):
    ORA-07445: exception encountered: core dump [hshget1()+199] [ACCESS_VIOLATION] [ADDR:0x18] [PC:0x8CB341F] [UNABLE_TO_READ] []
    Incident details in: c:\app\administrator\diag\rdbms\upgorcl\upgorcl\incident\incdir_51354\upgorcl_ora_1536_i51354.trc
    This is similar to bug no. 6927668 or 4149557.

    I don't know, maybe I'm abusing the database somehow....

    Anyway, if I perform a schema level import of user schemas, everything seems to work correctly. I'll only have to check if all objects used by those users get transferred too (public database links, contexts, etc.). Otherwise I'll have to transfer them manually and hopefully not forget any on them.
  • 6. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    491476 Expert
    Currently Being Moderated
    Have you tried to run the following:
    shutdown immediate
    startup upgrade
    @?/rdbms/admin/catupgrd.sql
    startup
    @?/rdbms/admin/utlrp.sql
  • 7. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    user633661 Newbie
    Currently Being Moderated
    Well, no I haven't.... I thought that running catupgrd.sql is required only if upgrading a database which runs against binaries from a higher release in a different home (so that the data dictionary and maybe something else gets upgraded to the higher version). E.g. if the database ran on 10.2.0.1 and then 11.1.0.6 binaries are installed in a new oracle_home, then catupgrd.sql upgrades the data dictionary in the 10.2.0.1 datafiles from 10.2.0.1 to 11.1.0.6.

    But since the 11g database is already in place before invoking the full import, so is the data dictionary which should not need any changes related to upgrading from 10g to 11g (since it's already of version 11.1.0.6). So when preforming datapump full imports I think it's not necessary to run catupgrd.sql. Or am I wrong?

    If I understand correctly, full datapump export dumpfile doesn't even contain SYS owned objects where the data dictionary is stored, so it can't be imported. From Doc ID: 228482.1 (relates to exp):
    Schemas that exclusively contain dictionary objects like SYS, CTXSYS, MDSYS and ORDSYS are never exported nor imported. This is because these schemas are created when you install the related database option or when you create your database.  Thus it is not necessary for export to generate create statements for their objects.

    ....and http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_export.htm#i1006790: A full export does not export system schemas that contain Oracle-managed data and metadata. Examples of system schemas that are not exported include SYS, ORDSYS, and MDSYS

    On the other hand, objects owned by SYSTEM and PUBLIC seem to be imported - at least those that are not already present in the new database, since for the exisiting ones an ORA-31684 is returned. I wonder why there isn't a clear official document on how to treat those errors when performing full imports....

    Maybe I'm wrong about the catupgrd.sql script (or about something else :-)), and if that's the case, please correct me.


    Thank you very much for all the help given so far!
  • 8. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    491476 Expert
    Currently Being Moderated
    Check Note: 566690.1 about the catupgrd.sql. It addresses a similar ORA-07445 that you were encountering. It's not a guarantee that it will resolve the problem, but nevertheless, running catupgrd.sql should not have a detrimental affect.
  • 9. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    user633661 Newbie
    Currently Being Moderated
    Thanks, I'll try in 2 days, and report back.
  • 10. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    user633661 Newbie
    Currently Being Moderated
    I ran catupgrd.sql and utlrp.sql after the import, but the ORA-07445 is still present. The interesting thing is that this happens regardless if I do a full import (full=y) or only a schema level import where I import 5 user schemas. At first I suspected something in the data dictionary gets messed up when doing a full import, but with schema level it shouldn't.

    Anyway, I have a RMAN backup of the "clean" database (so before any imports were performed) and if I restore it, no ORA-07445 are reported in the alert log, so it seems something goes wrong when importing.

    I opened an SR since soon I'll have to perform that on a production database. If we solve the problem, I'll report back.
  • 11. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    tychos Expert
    Currently Being Moderated
    Hi,
    The interesting thing is that this happens regardless if I do a full import (full=y) or only a schema level import where I import 5 user schemas
    Can you list the 5 schemas you are trying to import?
    (Did you exclude system, sysman or any other dictionary related schema?)


    Regards,

    Tycho
  • 12. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    user633661 Newbie
    Currently Being Moderated
    Hi,

    with the help of Oracle support, the problem was resolved by applying the latest patch bundle for the 11.1.0.6 patchset. The full import doesn't cause any ORA-07445 in the alert log.

    Actually I haven't tried yet to perform a manual/DBUA upgrade to see if that way it would also work without any ORA-07445 errors.
  • 13. Re: Move/upgrade from 10g 32-bit to 11g 64-bit
    491476 Expert
    Currently Being Moderated
    Thanks for posting back ! This may help others in the future.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points