Howto migrate SonarQube from MySQL to Oracle

Recently we had the need to move Sonar off our small virtualized MySQL server due to the fact that the Sonar database has begun to grow huge. Really HUGE. We'd like to keep data for about 3 months, and 1 month is already worth several GB of data, and our MySQL server isn't setup for this amount of data.

So we decided to move it to our Oracle database. Thanks to SQL Developer, this was a quite easy process.

Pre-Setup: Create a tablespace and user for Sonar

CREATE SMALLFILE TABLESPACE "TS_SONAR" LOGGING DATAFILE '/database/PHD0/data/ts_sonar.dbf' SIZE 4096M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 31G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER sonar IDENTIFIED BY sonar;
ALTER USER sonar DEFAULT TABLESPACE ts_sonar;
GRANT UNLIMITED TABLESPACE, CONNECT, RESOURCE, CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER TO sonar;

Then I used this perfect guide to migrate the data from MySQL to Oracle: http://www.oracle.com/technetwork/database/migration/mysql-093223.htmlThis took a while.

The mapping didn't work out right. Sequence names were wrong, some columns had wrong data type and so on. But the data was there. We only fixed the data type errors by renaming, recreating and copying the column data. Then we did an expdp of the full sonar database, dropped and recreated the sonar user, and let sonar do a completely fresh install against the empty database.  So next step was to configure Sonar to use Oracle.

First download ojdbc6.jar from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-11201... and put it into extension/jdbc-driver/oracle inside the Sonar installation directory.

Then stop Sonar and edit conf/sonar.properties:

#----- Oracle 10g/11g
# Comment the embedded database and uncomment the following lines to use Oracle
sonar.jdbc.url:                            jdbc:oracle:thin:@phora01/PHD0
sonar.jdbc.driverClassName:                oracle.jdbc.driver.OracleDriver
sonar.jdbc.validationQuery:                select 1 from dual
 

After Sonar finished installing, we shut down Sonar, and used impdp content=DATA_ONLY table_exists_action=TRUNCATE method to import the old data into the newly created, clean schema.

Finally start Sonar and pray it's working. Here it did - mission accomplished.

Comments

Hello Tom,

glad for you, to see, that such a migration scenario has worked for you. Seems to be an alternative for the Sonar DB Copy Tool, which is sadly only available in SonarQube Enterprize.
Im struggling with the migration from sonarqube to oracle, too.
In your blog post, you've mentioned, that the mapping didn't work out right. Sequence names had been wrong and some columns had wrong data types and so on. Can you tell me, which colums and tables wre affected of wrong datatypes?

Thank you in advice,
Greetings
Lukas

Hi Lukas, 

unfortunately no, as I did that migration about 3 years ago. But if I recall correctly, SQLDeveloper complained about it and I simply fixed them one by one.