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.html. This 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-112010-090769.html and put it into
extension/jdbc-driver/oracle inside the Sonar installation directory.
Then stop Sonar and edit
#----- 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.