Upgrade scenario: Oracle 8.0.5.0.0 to Oracle 12c?

Question

we have a very old Oracle 8.0.5.0.0 database here that - unfortunately - is still used heavily as every day production system.

I want to start making plans on how to get this DB upgrade to a recent Oracle 12c.

Unfortunately, both DB versions don't seem to be able to talk to each other anymore (*). So to my current knowledge, the only solution is to import daily backups from 8.0.5.0.0 into 12c, which - from the technical point of view - works, but is not feasible because the 8.0.5.0.0 is used in daily business. Beside: The data on the 8.0.5.0.0 server is that large that an import of the backup in the 12c server (on a recent Dell server) takes about 6-8 hours.

So, the question is:

What might be a good approach for a upgrade strategy from 8.0.5.0.0 to 12c?

Thanks for any input.

(*) Of course old Oracle 8.0.5.0.0 clients can talk to the Oracle 8.0.5.0.0 server and recent 12c clients can talk to the 12c server. But there does not seem to be a client solution that can talk to both 8.0.5.0.0 and 12c.

Answer 1

There are multiple solutions to this problem but the driving issues are cost, allowable downtime and complexity.

• Golden Gate could be purchased with a one year license and will do a zero downtime upgrade from Oracle 8i to 12. You still have to upgrade from 8.05 to 8i.
• Your life could be simplified by using VmWare P2V to convert the machine to a virtual machine without any downtime. This method allows you to:
  • get your production database on better hardware which should improve the speed of most operations
  • allows you to make a copy of the machine, put it on a different subnet and start testing various strategies
• I have imported schemas from our Oracle 8i database directly into Oracle 11.2 without issues. Your mileage may vary depending on character sets, licensed features and any unorthodox strategies your programmers have in the PL/SQL code.

One method that could be easily tested:

• buy a new server with some serious hardware like 32GB RAM, install VMWare Esxi
• convert your old machine to a virtual
• create a development copy of the Oracle 8.05
• install another virtual machine with Oracle 12
• install another virtual machine with Oracle 10XE or 11XE
• take snapshots of all the machines with no changes
• take an export of the 8.05 development, import into Oracle 10XE or 11XE, test and verify
• data pump to the 12 database
• verify the data on 12 and offline the 8.05 and oracle 10XE or 11XE

This method needs downtime. A weekend should be more than adequate and only costs you a new server, Oracle 12 licence and some VMWare software licenses. Keep in mind that testing and verification can take more time than importing the data.

Answer 2

This is more an extended comment than a full answer.

There are various ways to do the migration:

1. exp/imp is a good good way if it is possible between versions that differ this much. I think the main reason why export/import is slow is that there is only one job that does the work. You can accelerate the export/import if you group your tables and do the export/import of these groups in parallel. Also, index creation and constraint enabling can be taken away from the import to accelerate it. This needs a lot of preparation but for a 6G this seems a possible way.

2. Transportable tablespace is possible in 8.1, so you can upgrade your database to 8.1 and do the transportable tablespace export/import.

3. Finally, you can do the upgrade from 8.0 to 8.1 to 10.2 to 12.1:

   • Choose an Upgrade Method for Oracle Database (12c)
   • Choose an Upgrade Method (10g)

I think all these methods are possible for your database (6Gb, 12h downtime), and the method you choose should be tested thoroughly.

You should not forget that besides your database your clients must be upgraded and tested, too. Here Oracle describes the major upgrade steps:

• Major Steps in the Upgrade Process for Oracle Database

Answer 3

This is not a full answer, but a complement to the other answers.

Where I work, we had to move data from old 9.2 Oracle database to 11.1 Oracle databases. The old database was on AIX and the new one was on RHEL or Oracle Linux.

To me, there is no "good" answer and it depends on a lot of factors :

• how big is the database ?
• how much down-time is affordable ?
• what kind of objects are used in the database ?
• What budget do you have for purchase ?
• How much time are you given to prepare the operation ?
• What options are installed ?
• etc.

We used (mainly) two different techniques to migrate databases.

The first one was classically to export with "exp" utility and import with "imp" utility with the import done in NOARCHIVELOG as it slows down the migration process. The downtime required for this operation depended on the size of the database. To minimize this downtime (we needed once to migrate a 500 G database in 1 hour...), we spent time and development efforts on making a cold-hot data segmentation, a table/partition transfer duration-based prioritization, a synchronization between source and target servers so that the import started directly once the export ended and finally added parallelism into all of this.

The second one was a home made solution which does the same thing as Golden Gate mentioned in another answer : 1) Replicate structures (tables, views, functions etc.) either using exp/imp tools with ROWS=N 2) Fully transfer the snapshot of a table and start to keep track of modification using triggers on tables, a global sequence for operations and a logging table. 3) Make use of this logging table for incremental loads. Once you want to actually migrate databases, make your incremental loads more often so that you reduce your downtime to the minimum. This method was unsure until 9.2.0.8 version as there were bugs in triggers for LOB objects.

Hope this helps.

Answer 4

I supported 8.0.5 databases at the beginning of my career in the late 90's. I need to point out the transportable table spaces and data pump were added in 9i. You could go through 8i to get to 12c, but it would be better to do it in one shot.

Think if this problem as if you are migrating from a non-Oracle database to Oracle. You should be able to use heterogenous gateway to ODBC to connect from the new database to the old database.

Database Gateway for ODBC User's Guide

Now you can read data directly from 8.0.5 to 12c. Next get the DDL for creating all of your tables and create a script that will create the users and all of the tables. You can probably run the migration of data in parallel by creating several jobs that can run using the 12c scheduler. Each job would simply do insert into the target table without any constraints. If you have any tables that use long, long raw or raw you need to write an external program to migrate those using: c, pro*c, Perl, etc. Once you can migrate the data you need to generate the ddl for enabling the constraints. Finally if you have stored procedures or triggers in the database you should rewrite them.

This seems like an iterative process that will probably run many times over months trying to get the conversion correct and complete, and have it run in the allotted 12 hours. You can do this, but it will take some effort. The good news is that while the conversion will take time, you should not need to lose data or take an exceptionally long down time. So people can keep using the application during that time.