Migrating Oracle content side-by-side
Transfer Oracle data from your IBM® Connections 4.5 or 5.0 databases to the new 4.5 or 5.0 databases as part of migrating to version 5.5.
About this task
- 45
- 45CR4
- 50
To update the databases, complete the following steps:
Procedure
-
Using the IBM Connections 4.5 or 5.0 database wizard,
create target databases on a separate system from your source databases. The new databases host your
data for migration to the 5.5 deployment.
The procedure is described in the IBM Connections 4.5 Knowledge Center topic: Creating databases with the database wizard
Or the IBM Connections 5.0 Knowledge Center: Creating Oracle databases
Note: If the 4.5 or 5.0 database wizard is not on the system that hosts the target databases, copy it from the system that hosts IBM Connections 4.5 or 5.0. - Bring the target Databases up to the latest Cumulative Refresh (CR) level for the code stream as described in the IBM Connections 5.0 Knowledge Center topic: Updating 4.0 and 4.5 Oracle databases manually
-
Prepare the target databases to accept data from the source databases. Remove constraints from
the target databases by running the following SQL scripts from the IBM Connections 5.5 package:
Notes:
- Run these SQL scripts before you transfer data to the target database.
- Run each script from the same directory that you use to create the target database.
- IBM Connections uses the ojdbc6.jar database libraries, which are already on the target database server:
Repeat the following procedures for each application that you are migrating:- For each application, change to the directory that contains the relevant SQL file.
- Enter the following commands:
- sqlplus /NOLOG
- conn system/password@SID
- @SQL_script.sql
Where- password is the password for the user system.
- SID is the Oracle System Identifier for IBM Connections.
- SQL_script refers to a SQL script from the following table.
-
Note: Run the predbxferxx.sql commands that are in the 5.5 GA build and not the 4.5 or 5.0 build, wherexx might be:
- 45
- 45CR4
- 50
Table 1. Oracle commands for removing constraints Application Directory Oracle commands Activities /connections.sql/activities/oracle Run @predbxfer50.sql if you are at 5.0 or a 5.0 CR. Run @predbxfer45.sql if you are at 4.5 or a 4.5 CR.
Blogs /connections.sql/blogs/oracle Run @predbxfer50.sql if you are at 5.0 or 5.0 CR.
Run @predbxfer45.sql if you are at 4.5 or at 4.5 CR.
Bookmarks /connections.sql/dogear/oracle Run @predbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @predbxfer45.sql if you are at 4.5 or a 4.5 CR.
Communities /connections.sql/communities/oracle Run @predbxfer50CR2.sql if you are at 5.0 or a 5.0.
Run @predbxfer45.sql if you are at 4.5 or a 4.5 CR.
Communities - calendar /connections.sql/communities/oracle Run @calendar-predbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @calendar-predbxfer45.sql if you are on 4.5 or a 4.5 CR.
Files /connections.sql/files/oracle Run @predbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @predbxfer45CR3.sql if you are on 4.5 or a 4.5 CR.
Forum /connections.sql/forum/oracle Run @predbxfer50CR1.sql if you are at 5.0 or a 5.0 CR.
Run @predbxfer45.sql if you are on 4.5 or a 4.5 CR.
Home page /connections.sql/homepage/oracle Run @predbxfer50cr4.sql if you are at 5.0 or a 5.0 CR.
Run @predbxfer45CR4.sql if you are at 4.5 or a 4.5 CR.
Metrics /connections.sql/metrics/oracle Run @predbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @predbxfer45CR2.sql if you are on 4.5 or a 4.5 CR.
Mobile /connections.sql/mobile/oracle Run @ predbxfer50.sql if you are at 5.0 or a 5.0 CR
Run @predbxfer45CR4.sql if you are on 4.5 or a 4.5 CR.
Profiles /connections.sql/profiles/oracle Run @predbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @predbxfer45.sql if you are on 4.5 or a 4.5 CR.
Wikis /connections.sql/wikis/oracle Run @predbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @predbxfer45CR3.sql if you are on 4.5 or a 4.5 CR.
- Using the IBM Connections
database transfer tool, transfer data to the target databases:
- Create the directory DBT_HOME on the target database server. This directory temporarily stores transferred data.
- Be sure to use the new version of the dbt.jar file and copy it from the connections_root\ConfigEngine\lib directory to the DBT_HOME directory on the target database server.
- Create an XML configuration file for each component
database to be transferred under the DBT_HOME directory
and add the following content:
<dbTransfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database role="source" driver="JDBC_driver" url="JDBC_url" userId="database_admin schema="application_db_schema_name" dbType="dbType"/> <database role="target" driver="JDBC_driver" url="JDBC_url" userId="database_admin" schema="application_db_schema_name" dbType="dbType"/> <table sourceName="TABLE_TO_SKIP" exclude="true"/> <!-- optional argument used to have dbtransfer skip a table --> </dbTransfer>
Where
JDBC_driver is the following type:- oracle.jdbc.driver.OracleDriver
JDBC_url is the following types- jdbc:oracle:thin:@host_IP:port:SID
Note: 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 IBM 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 CALENDARNote: To migrate Communities data, the dbt command needs to be run twice; the first time for the SNCOMM schema, and the second time for the CALENDAR schema.
- Dogear: DOGEAR
- Files: FILES
- Forum: FORUM
- Home page: HOMEPAGE
- Metrics: METRICS
- Mobile: MOBILE
- Profiles: EMPINSTNote: If you use the TDI-related table EMP_UPDATE_TIMESTAMP, it does not need to be transferred. Add the instruction:
<table sourceName="EMP_UPDATE_TIMESTAMP" exclude="true"/>
. - Wikis: WIKIS
dbType is the following value:- oracle
The entry
<table sourceName="TABLE_TO_SKIP" exclude="true"/>
instructs dbtransfer to skip a table. This instruction is of limited use as most table content is expected to transfer. - Prepare the JDBC driver of the target databases:
- Use the JDBC driver on the target database server.
- Ensure that the Oracle driver on your system has the same version number as your Oracle database server. IBM Connections does not support the Oracle 10.2.0.1 JDBC driver.
-
To transfer the data, run the dbt.jar file. Do not add spaces in a series
of file names:
- 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- JAVA_HOME is the path to the Java™ JDK.
- dbt_config_file_name is the name of the XML configuration file you created for the dbt.jar file
- logs is the directory where log files are stored. Create the logs directory before you run this file
- ORACLE_HOME is the path to the Oracle installation directory.
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 password -targetpassword password
- 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
Run the command on a single line; do not add extra spaces. For example, multiple file names are entered as filename.jar;filename.jar without a space between the file names. The command looks similar to the following when run as a single command (edited to your specific configuration):JAVA_HOME\bin\java -cp c:\DBT_HOME\dbt.jar;ORACLE_HOME\jdbc\lib\ojdbc6.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir c:\DBT_HOME\logs -xmlfile c:\DBT_HOME\dbt_config_file_name -sourcepassword password -targetpassword password
When the transfer is complete, you can restart your 4.5 or 5.0 deployment to minimize service downtime.
Note: Data that is generated after you restart the 4.5 or 5.0 environment is not migrated. - Linux:
- Reapply constraints to the target databases:
- For each application, change to the directory that contains the relevant SQL file.
- Enter the following commands:
- sqlplus /NOLOG
- conn system/password@SID
- @SQL_script.sql
Where
- password is the password for the user system.
- SID is the Oracle System Identifier for IBM Connections.
- SQL_script refers to a SQL script from the following table:
- Note: Run the postdbxferxx.sql commands that are in the 5.5 GA build and not the 4.5 or 5.0 build, wherexx might be:
- 45
- 45CR4
- 50
Table 2. Oracle commands for reapplying constraints Application Directory Oracle commands Activities /connections.sql/activities/oracle Run @postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45.sql if you are on 4.5 or a 4.5 CR.
@clearScheduler.sql
Blogs /connections.sql/blogs/oracle Run @postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45.sql if you are on 4.5 or a 4.5 CR.
Bookmarks /connections.sql/dogear/oracle Run @postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45.sql if you are on 4.5 or a 4.5 CR.
Communities /connections.sql/communities/oracle Run @postdbxfer50CR2.sql if you are at 5.0 or a 5.0.
Run @postdbxfer45.sql if you are at 4.5 or a 4.5 CR.
@clearScheduler.sql
Communities - Calendar /connections.sql/communities/oracle Run @calendar-postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @calendar-postdbxfer45.sql if you are on 4.5 or a 4.5 CR.
@clearScheduler.sql
Files /connections.sql/files/oracle Run @postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45CR3.sql if you are on 4.5 or a 4.5 CR.
@clearScheduler.sql
Forum /connections.sql/forum/oracle Run @postdbxfer50CR1.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45.sql if you are on 4.5 or a 4.5 CR.
@clearScheduler.sql
Home page /connections.sql/homepage/oracle Run @postdbxfer50CR4.sql if you are at 5.0 or a 5.0 CR.
Run @ postdbxfer45CR4.sql if you are at 4.5 or a 4.5 CR.
Metrics /connections.sql/metrics/oracle Run @postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45CR2.sql if you are on 4.5 or a 4.5 CR.
@clearScheduler.sql
Mobile /connections.sql/mobile/oracle Run postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45CR4.sql if you are on 4.5 or a 4.5 CR.
Profiles /connections.sql/profiles/oracle Run @postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45.sql if you are on 4.5 or a 4.5 CR.
@clearScheduler.sql
Wikis /connections.sql/wikis/oracle Run @postdbxfer50.sql if you are at 5.0 or a 5.0 CR.
Run @postdbxfer45CR3.sql if you are on 4.5 or a 4.5 CR.
@clearScheduler.sql
-
(Profiles only) Run the following commands to update the database sequence for the Oracle
target databases:
-
Run the following commands on the 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;
Run the following commands on the 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 source database.
-
- (Metrics only.) Run the following commands to update the
database sequence for DB2®, Oracle,
or SQL Server target databases:
Run the following command on the source database:
SELECT METRICS.ID_VALUES.NEXTVAL AS ID_VALUES_SEQ FROM DUAL;
Run the following commands on the 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 source database.
-
(Files only) Update the existing 4.5/5.0 createdb.sql to insert GRANT
UNLIMITED TABLESPACE TO FILES:
-
Start in the folder you unizipped the 4.5 or 5.0 database wizard package to, navigate to
createDb.sql under connections.sql as follows:
Windows:
\Wizards\connections.sql\files\oracle\
Linux/Unix:
/Wizards/connections.sql/files/oracle/
- Manually edit the Files createDb.sql.
-
Near the beginning of the file, locate the GRANT section and add the "GRANT UNLIMITED
TABLESPACE" line:
GRANT CONNECT, RESOURCE TO FILES; GRANT UNLIMITED TABLESPACE TO FILES; GRANT ALTER TABLESPACE TO FILES;
-
Run createDb.sql as follows:
Windows:
.\Wizards\connections.sql\files\oracle\createDb.sql
Linux/Unix:
./Wizards/connections.sql/files/oracle/createDb.sql
-
Start in the folder you unizipped the 4.5 or 5.0 database wizard package to, navigate to
createDb.sql under connections.sql as follows:
-
(Wikis only) Update the existing 4.5/5.0 createdb.sql to insert GRANT
UNLIMITED TABLESPACE TO WIKIS:
-
Start in the folder you unizipped the 4.5 or 5.0 database wizard package to, navigate to
createDb.sql under connections.sql as follows:
Windows:
\Wizards\connections.sql\wikis\oracle\
Linux/Unix:
/Wizards/connections.sql/wikis/oracle/
- Manually edit the Wikis createDb.sql.
-
Near the beginning of the file, locate the GRANT section and add the "GRANT UNLIMITED
TABLESPACE" line:
GRANT CONNECT, RESOURCE TO WIKIS; GRANT UNLIMITED TABLESPACE TO WIKIS; GRANT ALTER TABLESPACE TO WIKIS;
-
Run createDb.sql as follows:
Windows:
.\Wizards\connections.sql\wikis\oracle\createDb.sql
Linux/Unix:
./Wizards/connections.sql/wikis/oracle/createDb.sql
-
Start in the folder you unizipped the 4.5 or 5.0 database wizard package to, navigate to
createDb.sql under connections.sql as follows:
-
(Metrics only) Update the existing 4.5/5.0 createdb.sql to insert GRANT
UNLIMITED TABLESPACE TO METRICS:
-
Start in the folder you unizipped the 4.5 or 5.0 database wizard package to, navigate to
createDb.sql under connections.sql as follows:
Windows:
\Wizards\connections.sql\metrics\oracle\
Linux/Unix:
/Wizards/connections.sql/metrics/oracle/
- Manually edit the metrics createDb.sql.
-
Near the beginning of the file, locate the GRANT section and add the "GRANT UNLIMITED
TABLESPACE" line:
GRANT CONNECT, RESOURCE TO METRICS; GRANT UNLIMITED TABLESPACE TO METRICS; GRANT ALTER TABLESPACE TO METRICS;
-
Run createDb.sql as follows:
Windows:
.\Wizards\connections.sql\metrics\oracle\createDb.sql
Linux/Unix:
./Wizards/connections.sql/metrics/oracle/createDb.sql
-
Start in the folder you unizipped the 4.5 or 5.0 database wizard package to, navigate to
createDb.sql under connections.sql as follows:
- Manually, or using the DB Wizard, update the new databases to bring them to the IBM Connections version 5.5 level. For more information, see Updating 4.5 and 5.0 databases to 5.5 and Updating 4.5 and 5.0 databases with the wizard.
Results
Check that all the databases are working correctly. If you find errors, resolve the problem and repeat this task.