Solutions to some problems with database integration.Click a query below for the solution.

These instructions are specifically designed for migrating data from an external MySQL database to an Oracle one, when the data set is too large and Confluence's internal backup is not functioning properly. Note that while these instructions have been known to work for us, they have not been rigorously tested, and are thus NOT fully supported.

We highly recommend employing the services of a qualified database administrator for such migrations. Follow these instructions at your own risk - if it breaks, you get to keep both pieces (smile)

Environment

The following instructions were derived from testing in the following software environment:

  • Ubuntu 7.04
  • Oracle XE 10.2.0.1
  • Oracle SQL Developer 1.2.1
  • MySQL 4.1
  • Confluence 2.2.10

Instructions

Please begin by reading the database configuration notes, particularly the Oracle-specific pages. You may also find the notes on connecting Jira to Oracle to be helpful.

Procedures

  1. Take a backup of the MySQL database. Use mysqldump tool for this.
  2. Install Oracle. Make sure it is configured to listen on ports that are not already being used by other servers on the same host.
  3. Shut down Confluence.
  4. It is recommended to create a new installation of Confluence in a TEST environment, to use the new (Oracle) database without affecting your PRODUCTION environment . You will need to select a new location for the data directory.

    It is possible to continue using the current instance of Confluence if you wish, though you will still need to select a new location for the data directory. To do so, edit confluence/WEB-INF/classes/confluence-init.properties to reflect the new location, by commenting out the line that currently sets the value of the confluence.home variable (should be the last line) and adding a new one, setting it to use the new directory. E.g:
    #confluence.home=/var/confluence/confluence-data-mysql
    confluence.home=/var/confluence/confluence-data-oracle

    In the event that the new copy of the site on Oracle fails, you will be able to fail back to the original version on MySQL by shutting down Confluence, reverting confluence.home to its previous value, and restarting Confluence.

  5. Start the new instance of Confluence, following the normal procedure for installing Confluence with Oracle. If you are re-using the current instance, it is now configured to use the Oracle database server.
  6. Shut down the new Confluence instance.
  7. Remove the Confluence database and associated user IDs from the Oracle server. Either remount the database in exclusive mode and use DROP DATABASE, or (simpler and more comprehensive) remove and re-install the Oracle server altogether.
  8. Migrate the data from MySQL to Oracle, using the Oracle SQL Developer tool from the Oracle Migration Workbench website. Full instructions and software downloads are available at that site.

    We have only tested the Quick Migration, which appears to work satisfactorily. Other tools are available for migrating the data, but we have not tested them; if you use a different tool, please tell us about your experience.

  • To connect the Oracle SQL Developer tool to the Oracle server, use the SYSTEM user ID and the password that you set for that user when installing.
  • You may find it necessary to manually create a Repository before the migration process will work properly: from the toolbar at the top of the Oracle SQL Developer tool, select Migration -> Repository Management -> Create Repository.
  1. Edit the copy of confluence.cfg.xml in the new data directory (e.g. /var/confluence/confluence-data-oracle). Change the values of hibernate.connection.password and hibernate.connection.username to confluencedb as follows: 
    <property name="hibernate.connection.username">confluencedb</property>
<property name="hibernate.connection.password">confluencedb</property>
  2. Shut down MySQL, to make sure that Confluence can't accidentally use the old database on startup.
  3. Start the new instance of Confluence and log in. Your instance instance should now be available from the Oracle database containing completely migrated dataset.

Related Resources/Notes

  • mysqldump --compatible=oracle to produce oracle sql output.
    Please note that a new version of MySql is required as per bug that does not produce oracle-compatible output.
  • Or use a raw backup of MySQL and copy all the tables into text files to be then restored in Oracle.