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.

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:

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:

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.

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:

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)"

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:

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.

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.

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.

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.