For compatibility reasons we had to upgrade the oracle database from one of our customers. They where running a oracle 10g on an old Windows 2003R2 server and have to migrate to 11g on an windows 2012 Server.

Possible attempts:

• Upgrade the OS of the old server and afterwards upgrade the batabse version ... Wenn seems to be possible but since the machines are not virtualized this is somehow risky. Rolling back is difficult.
• Setup another machine with Win2012, install Oracle there and import the data from the oter instance ... Well that sounds good. If something fails then the original system is not tainted. Lets go with this one.

The next few sections are describing what we have done to git it running (and where we failed).

Step 1: Install Oracle. Thats easy. Download it from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html and install it. Dont forget to remember all your settings.

Step 2: Fire up the "Oracle Net Manager" and add another Service. This should point to the old (10g) database instance. You will need it for setting up a remote database - and dont forget to save the settings in the netmgr

OK that was easy. Now the tricky part. You have to setup a database link between the new instance and the old one. This can be done within the "move database" dialog:

The trick is that you use the Net-Servicename that you have created earlier in the process to connect to the old database. The schema and the user / password have to be from the old db too.
If everything works fine than you will end up with the following database-link:

Now ou new database is able to connect to the old one. The next step is to import the data from the old into the new database. There can be some quirks with the db and the os usersettings. One the one hand the db user must be able to connect to the database (and have the right to import the data by adding a new Job).

After adding the new job you may run into the error "ORA-20446: The owner of the job is not registered".  This means basically that your db-user has no permission to add new Jobs to the new database. This can be fixed easily (http://philiphoweoracle.wordpress.com/2009/04/02/ora-20446-the-owner-of-the-job-is-not-registered/):

• Fire up SQL Plus
• Login as sysman (no password required - alt least on my box)
• Enter the statement: (USERID is the oracle-user that wants to add importjobs into the new database)

If you finally manage it to issue a new job against the database then you may run into the problem of operatingsystem permissions because oracle wants to create new files on the filesystem, but it desnt have permissions to do so. (ORA-01119: error in creating database file shows up in the logfile). The solution for this is also simple: The location over which you are trying to create your data file is either incorrect or not writable. Check the path and permissions for the same. (I assume that the user provided in the Host-Id-Data field is the one you have to set the permissions for).

This is a small wlakthrough how i did it with my database:

Common pitfalls:

• Import is only possible if you are logged in as "standard user" (not as sysadmin).Import trys to locate the tablespace on the same location as on the source system. (The tablespace is the location where your database files are located) Either you have to provide the same directory structure or you have to relocate the tablespace. The relocation is only possible if you transfer the schema (and not the database as a whole)
• ORA-31679: Table data object string has long columns, and longs can not be loaded/unloaded using a network link. Solution: You have to export this table to a file and then import the same table from the file.If this occurs with the PLAN_TABLE then you can (according to https://blogs.oracle.com/UPGRADE/entry/drop_sysplan_table_prior_to_up) simply drop / ignore this table. It is internally used by oracles performance analyzer.