Migrating Oracle content side-by-side
Transfer Oracle data from your IBM® Connections 5.0 or 5.5 databases to the new 5.0 or 5.5 databases as part of migrating to version 6.0.
About this task
- 50
- 50CRx
- 55
- 55CRx
To update the databases, complete the following steps:
Procedure
-
Using the IBM® Connections 5.0 or 5.5 database wizard,
create target databases on a separate system from your source databases. The new databases host your
data for migration to the 6.0 deployment.
The procedure is described in the IBM® Connections 5.0 Knowledge Center topic: Creating Oracle databases
Or the IBM® Connections 5.5 Knowledge Center: Creating Oracle databases
Note: If the 5.0 or 5.5 database wizard is not on the system that hosts the target databases, copy it from the system that hosts IBM® Connections 5.0 or 5.5.Note: Take the following steps if you are migrating from 5.0 or 5.0 CR only. 5.5 already has the necessary unlimited grants. If you do not modify the createDb.sql script before running createDb.sql, it might not complete successfully and you cannot move data into the empty target databases. -
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:
Creating Oracle databases
Or the IBM® Connections 5.5 Knowledge Center: Updating 4.5 and 5.0 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 6.0 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 6.0 GA build and not the 5.0 or 5.5 build, wherexx might be:
- 50
- 50CRx
- 55
- 55CRx
Table 1. Oracle commands for removing constraints Application and directory Oracle commands Activities: /connections.sql/activities/oracle If you are at: - 5.0 or a 5.0 CR run @predbxfer50.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Blogs: /connections.sql/blogs/oracle If you are at: - 5.0 or a 5.0 CR run @predbxfer50.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Bookmarks: /connections.sql/dogear/oracle If you are at: - 5.0 or a 5.0 CR run @predbxfer50.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Communities: /connections.sql/communities/oracle If you are at: - 5.0 or 5.0 CR1 run @predbxfer50.sql
- 5.0 CR2 or above run @predbxfer50CR2.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Communities - calendar: /connections.sql/communities/oracle If you are at: - 5.0 or a 5.0 CR run @calendar-predbxfer50.sql
- 5.5 or a 5.5 CR run @calendar-predbxfer55.sql
Files: /connections.sql/files/oracle If you are at: - 5.5 or 5.5 CR1 run @predbxfer55.sql
- 5.5 CR2 or above run @predbxfer55CR2.sql
Forums: /connections.sql/forum/oracle If you are at: - 5.0 run @predbxfer50.sql
- 5.0 CR1 or above run @predbxfer50CR1.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Home page: /connections.sql/homepage/oracle If you are at: - 5.0 run @predbxfer50.sql
- 5.0 CR1 run @predbxfer50CR1.sql
- 5.0 CR2 run @predbxfer50CR2.sql
- 5.0 CR3 run @predbxfer50CR3.sql
- 5.0 CR4 run @predbxfer50CR4.sql
- 5.5 run @predbxfer55.sql
- 5.5 CR1 run @predbxfer55CR1.sql
- 5.5 CR2 run @predbxfer55CR2.sql
Metrics: /connections.sql/metrics/oracle If you are at: - 5.0 or a 5.0 CR run @predbxfer50.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Mobile: /connections.sql/mobile/oracle If you are at: - 5.0 or a 5.0 CR run @predbxfer50.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Profiles: /connections.sql/profiles/oracle If you are at: - 5.0 or a 5.0 CR run @predbxfer50.sql
- 5.5 or a 5.5 CR run @predbxfer55.sql
Wikis: /connections.sql/wikis/oracle If you are at: - 5.5 or 5.5 CR1 run @predbxfer55.sql
- 5.5 CR2 or above run @predbxfer55CR2.sql
Push notification: /connections.sql/pushnotification/oracle If you are at 5.5 or a 5.5 CR, run @predbxfer55.sql
- Using the IBM® Connections
database transfer tool, transfer data to the target databases:
- 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 6.0 GA build and not the 5.0 or 5.5 build, wherexx might be:
- 50
- 50CRx
- 55
- 55CRx
Table 2. Oracle commands for reapplying constraints Application and directory Oracle commands Activities: /connections.sql/activities/oracle If you are at: - 5.0 or a 5.0 CR run @postdbxfer50.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
- Run @clearScheduler.sql
Blogs: /connections.sql/blogs/oracle If you are at: - 5.0 or a 5.0 CR run @postdbxfer50.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
Bookmarks: /connections.sql/dogear/oracle If you are at: - 5.0 or a 5.0 CR run @postdbxfer50.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
Communities: /connections.sql/communities/oracle If you are at: - 5.0 or 5.0 CR1 run @postdbxfer50.sql
- 5.0 CR2 or above run @postdbxfer50CR2.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
- Run @clearScheduler.sql
Communities - calendar: /connections.sql/communities/oracle If you are at: - 5.0 or a 5.0 CR run @calendar-postdbxfer50.sql
- 5.5 or a 5.5 CR run @calendar-postdbxfer55.sql
Files: /connections.sql/files/oracle If you are at: - 5.5 or 5.5 CR1 run @postdbxfer55.sql
- 5.5 CR2 or above run @postdbxfer55CR2.sql
- Run @clearScheduler.sql
Forums: /connections.sql/forum/oracle If you are at: - 5.0 run @postdbxfer50.sql
- 5.0 CR1 or above run @postdbxfer50CR1.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
- Run @clearScheduler.sql
Home page: /connections.sql/homepage/oracle If you are at: - 5.0 run @postdbxfer50.sql
- 5.0 CR1 run @postdbxfer50CR1.sql
- 5.0 CR2 run @postdbxfer50CR2.sql
- 5.0 CR3 run @postdbxfer50CR3.sql
- 5.0 CR4 run @postdbxfer50CR4.sql
- 5.5 run @postdbxfer55.sql
- 5.5 CR1 run @postdbxfer55CR1.sql
- 5.5 CR2 run @postdbxfer55CR2.sql
- Run @clearScheduler.sql
Metrics: /connections.sql/metrics/oracleS If you are at: - 5.0 or a 5.0 CR run @postdbxfer50.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
- Run @clearScheduler.sql
Mobile: /connections.sql/mobile/oracle If you are at: - 5.0 or a 5.0 CR run @postdbxfer50.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
Profiles: /connections.sql/profiles/oracle If you are at: - 5.0 or a 5.0 CR run @postdbxfer50.sql
- 5.5 or a 5.5 CR run @postdbxfer55.sql
- Run @clearScheduler.sql
Wikis: /connections.sql/wikis/oracle If you are at: - 5.5 or 5.5 CR1 run @postdbxfer55.sql
- 5.5 CR2 or above run @postdbxfer55CR2.sql
- Run @clearScheduler.sql
Push Notification: /connections.sql/pushnotification/oracle - If you are at 5.5 or a 5.5 CR, run @postdbxfer55.sql
- Run @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.
-
- 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 Migrating 5.0 and 5.5 databases to 6.0 and Updating 5.0 and 5.5 databases with the wizard.
Results
Check that all the databases are working correctly. If you find errors, resolve the problem and repeat this task.