Migrate Oracle-based content side-by-side

Overview

As part of migrating to v5.0 using a side-by-side DB migration strategy, we transfer data from source Connections 4.0/4.5 databases to target 4.0/4.5 Connections databases

When the data transfer is complete, we validate the new databases, then update them to v5.0

References to 4.5 throughout this topic also include 4.0.

We can continue to use the 4.0 or 4.5 databases until we are ready to move to Connections 5.0. Data generated in the source Connections 4.x databases is not migrated to the new environment.

Run the predbxferxx.sql and postdbxferxx.sql from the 5.0 GA build, and not the 4.5 build.

Depending on which database version we are migrating from, xx might be:

• 4.0
• 4.5
• 4.5CR4

Transfer the databases

1. Using the Connections 4.5 database wizard, create target 4.5 databases on a separate system from the 4.5 source databases.

   The new databases host the data for migration to the 5.0 deployment.

   If the 4.5 database wizard is not on the system hosting the target databases, copy it from the system hosting IBM Connections 4.5.

2. Bring the 4.5 target databases up to the level of the Cumulative Refresh updates made to the 4.5 source server

3. Prepare the target 4.5 databases to accept data from the source 4.5 databases.

   Remove constraints from the target databases by executing the following SQL scripts. Run each script from the directory used to create the target database. Connections uses ojdbc6.jar libraries, which exist on the target database server.

   For each application being migrated, run.....

   sqlplus /NOLOG
   conn system/password@SID
   @SQL_script.sql

   ...where SQL scripts include...

   Application	Directory	Oracle commands
   Activities	/connections.sql/activities/oracle	@predbxferxx.sql
   Blogs	/connections.sql/blogs/oracle	@predbxferxx.sql
   Bookmarks	/connections.sql/dogear/oracle	@predbxferxx.sql
   Communities	/connections.sql/communities/oracle	@predbxferxx.sql @calendar-predbxferxx.sql
   Files	/connections.sql/files/oracle	@predbxferxx.sql
   Forum	/connections.sql/forum/oracle	@predbxferxx.sql
   Home page	/connections.sql/homepage/oracle	@predbxferxx.sql
   Metrics	/connections.sql/metrics/oracle	@predbxferxx.sql
   Mobile	/connections.sql/mobile/oracle	@predbxferxx.sql
   Profiles	/connections.sql/profiles/oracle	@predbxferxx.sql
   Wikis	/connections.sql/wikis/oracle	@predbxferxx.sql

   Run predbxferxx.sql commands in the 5.0 GA build, and not the 4.5 build. xx can be:

   • 4.0
   • 4.5
   • 4.5CR4

4. Use the Connections database transfer tool, transfer data to the target databases:

   1. Create a directory called DBT_HOME on the target database server.

      This directory temporarily stores transferred data.

   2. Use the new version of dbt.jar. Copy it from...
      connections_root/ConfigEngine/lib

      ...to the DBT_HOME directory on the target database server.

   3. Create an XML configuration file under the DBT_HOME directory, and add the following content:

      <dbTransfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <database role="source" 
                    driver="oracle.jdbc.driver.OracleDriver" 
                    url="jdbc:oracle:thin:@host_IP:port:SID" 
                    userId="database_admin
                    schema="application_db_schema_name"
                    dbType="oracle"/>
          <database role="target"
                        driver="oracle.jdbc.driver.OracleDriver"
                        url="jdbc:oracle:thin:@host_IP:port:SID"
                        userId="database_admin" 
                        schema="application_db_schema_name" 
                        dbType="oracle"/>
      </dbTransfer>

      where

      • host_IP is the IP address of the database server.

      • port is the port number of the server.

      • SID is the Oracle System Identifier for Connections

      • application_database_name is one of the following values:

        Activities	OPNACT
        Blogs	BLOGS
        Communities	SNCOMM
        Dogear	DOGEAR
        Files	FILES
        Forum	FORUM
        Home page	HOMEPAGE
        Metrics	METRICS
        Mobile	MOBILE
        Profiles	PEOPLEDB
        Wikis	WIKIS

        database_admin is the user ID of the database administrator.

        application_db_schema_name is one of the following values:

        Activities	ACTIVITIES
        Blogs	BLOGS
        Communities	SNCOMM and CALENDAR
        Dogear	DOGEAR
        Files	FILES
        Forum	FORUM
        Home page	HOMEPAGE
        Metrics	METRICS
        Mobile	MOBILE
        Profiles	EMPINST
        Wikis	WIKIS

        To migrate Communities data, the dbt command needs to be run twice: Once for the SNCOMM schema. Once for for the CALENDAR schema.

      • Prepare the JDBC driver of the target databases:

        • Use the JDBC driver on the target database server.

        • Ensure the Oracle driver on the system has the same version number as the Oracle database server. Connections does not support the Oracle 10.2.0.1 JDBC driver.

      • Perform the data transfer by executing the dbt.jar file:

        • Linux
          "JAVA_HOME/bin/java"
          -cp DBT_HOME/dbt.jar:
          ORACLE_HOME/jdbc/lib/ojdbc6.jar
          com.ibm.wps.config.db.transfer.CmdLineTransfer
          -logDir DBT_HOME/logs
          -xmlfile DBT_HOME/dbt_config_file_name
          -sourcepassword source_db_password
          -targetpassword target_db_password

          ...where...

          • dbt_config_file_name is the name of the XML configuration file created for the dbt.jar file
          • logs is the directory where log files are stored. Create the logs directory before running this file
          • ORACLE_HOME is the path to the Oracle install directory.

        • Windows:
          "JAVA_HOME/bin/java"
          -cp DBT_HOME/dbt.jar;
          ORACLE_HOME/jdbc/lib/ojdbc6.jar
          com.ibm.wps.config.db.transfer.CmdLineTransfer
          -logDir DBT_HOME/logs
          -xmlfile DBT_HOME/dbt_config_file_name
          -sourcepassword source_db_password
          -targetpassword target_db_password

        Remove the lines for the database systems not in the deployment.

        When the transfer is complete, restart the 4.5 deployment. Data generated after restarting the 4.5 environment is not migrated.

5. Reapply constraints to the target databases:

   For each application, change to the directory containing the relevant SQL file and run...

   sqlplus /NOLOG
   conn system/password@SID
   @SQL_script.sql

   ...where...

   Application	Directory	Oracle commands
   Activities	/connections.sql/activities/oracle	@postdbxferxx.sql @clearScheduler.sql
   Blogs	/connections.sql/blogs/oracle	@postdbxferxx.sql
   Bookmarks	/connections.sql/dogear/oracle	@postdbxferxx.sql
   Communities	/connections.sql/communities/oracle	@postdbxferxx.sql @clearScheduler.sql @calendar-postdbxfer45.sql
   Files	/connections.sql/files/oracle	@postdbxferxx.sql @clearScheduler.sql
   Forum	/connections.sql/forum/oracle	@postdbxferxx.sql @clearScheduler.sql
   Home page	/connections.sql/homepage/oracle	@postdbxferxx.sql @clearScheduler.sql
   Metrics	/connections.sql/metrics/oracle	@postdbxferxx.sql @clearScheduler.sql
   Mobile	/connections.sql/mobile/oracle	@postdbxferxx.sql @clearScheduler.sql
   Profiles	/connections.sql/profiles/oracle	@postdbxferxx.sql @clearScheduler.sql
   Wikis	/connections.sql/wikis/oracle	@postdbxferxx.sql @clearScheduler.sql

   Run the postdbxferxx.sql commands in the 5.0 GA build and not the 4.5 build, wherexx might be:

   • 4.0
   • 4.5
   • 4.5CR4
   1. (Profiles only.) Update the database sequence for the Oracle target databases:

      • Runs on the 4.5 source database:

        SELECT EMPINST.EXT_DRAFT_SEQ.NEXTVAL AS EXT_DRAFT_SEQ FROM DUAL;
        SELECT EMPINST.EMP_DRAFT_SEQ.NEXTVAL AS EMP_DRAFT_SEQ FROM DUAL;
        SELECT EMPINST.CHG_EMP_DRAFT_SEQ1.NEXTVAL AS CHG_EMP_DRAFT_SEQ1 FROM DUAL;
        SELECT EMPINST.CHG_EMP_DRAFT_SEQ2.NEXTVAL AS CHG_EMP_DRAFT_SEQ2 FROM DUAL;
        Runs on the 4.5 target database:
        DROP SEQUENCE EMPINST.EXT_DRAFT_SEQ;
        CREATE SEQUENCE EMPINST.EXT_DRAFT_SEQ START WITH query_result;
        DROP SEQUENCE EMPINST.EMP_DRAFT_SEQ;
        CREATE SEQUENCE EMPINST.EMP_DRAFT_SEQ START WITH query_result;
        DROP SEQUENCE EMPINST.CHG_EMP_DRAFT_SEQ1;
        CREATE SEQUENCE EMPINST.CHG_EMP_DRAFT_SEQ1 START WITH query_result;
        DROP SEQUENCE EMPINST.CHG_EMP_DRAFT_SEQ2;
        CREATE SEQUENCE EMPINST.CHG_EMP_DRAFT_SEQ2 START WITH query_result;

        ...where query_result is the result of the corresponding SELECT command that you ran on the 40 database.

   2. (Metrics only.) Update the database sequence for DB2, Oracle, or SQL Server target databases:

      • Run on the 4.5 source database:

        SELECT METRICS.ID_VALUES.NEXTVAL AS ID_VALUES_SEQ FROM DUAL;

        Runs on the 4.5 target database:

        DROP SEQUENCE METRICS.ID_VALUES;
        CREATE SEQUENCE "METRICS"."ID_VALUES" START WITH query_result INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 20;
        GRANT SELECT, ALTER ON "METRICS"."ID_VALUES" TO METRICSUSER_ROLE;

        ...where query_result is the result of the corresponding SELECT command that you ran on the 4.5 database.

What to do next

Check that all the databases are working correctly. If we find errors, resolve the problem and repeat this task.

Update the new databases to IBM Connections v5.0.

Parent topic:
Migrate data side-by-side