Updating 4.5 and 5.0 Oracle databases manually

Manually update IBM® Connections 4.5 or 5.0 databases to version 5.5 in an existing IBM WebSphere® Application Server and Oracle database environment.

Before you begin

Before applying updates, back up your databases. For more information, see Backing up IBM Connections.

Ensure that you have installed and configured all supporting software for version 5.5.

(Oracle only) Ensure that the Statement cache size for the data sources on WebSphere Application Server is no larger than 50. A higher value could lead to Out Of Memory errors on the application server instance.

(Oracle only) IBM Connections databases use SMALLFILE tablespaces that have a size limitation of 222 blocks. When you use 8 KB blocks, this limit is approximately 32 GB. If you anticipate needing more space, add tablespace files to individual databases. For detailed information, refer to your Oracle documentation.

About this task

This topic describes how to update IBM Connections version 4.5 or 5.0 databases to version 5.5 manually. Use this procedure if you want an alternative to using the database wizard to update your databases.

Notes:
  • This topic applies to all releases of version 4.5 and 5.0
  • Use the Java™ Runtime Environment (JRE) under the Wizards directory in the installation media. Update your PATH variable to point to this JRE, by using the instructions for your operating system. For example, the relative path to the JRE on Microsoft™ Windows™ could be Wizards\jvm\win\jre. For AIX® or Linux™, the relative path could be Wizards/jvm/aix/jre and Wizards/jvm/linux/jre.
  • IBM Connections does not support GNU Java.
  • You need to use a database administrator ID to run the Java migration utilities described in this task.
  • After running each command, examine the output of the command for error messages. If you find errors, resolve them before continuing with the update process.
  • To improve readability, some commands and file paths in this topic are displayed on separate lines. Ignore these formatting conventions when entering the commands.

To update databases manually, complete the following steps:

Procedure

  1. Log in to the WebSphere Application Server Integrated Solutions Console on your Deployment Manager.
  2. Go to Applications > Application types > WebSphere enterprise Applications.
  3. Stop all IBM Connections applications.
  4. Ensure that the Oracle driver on your system has the same version number as your Oracle database server.
  5. Change to the directory that contains the scripts.
  6. For each application, enter the following command and then run the appropriate scripts:

    sqlplus /as sysdba

    Note: To capture the output of each command to a log file, run the following commands before starting this task:

    sql> spool on

    sql> spool output_file

    where output_file is the full path and name of the file where the output is captured.

    When you have completed this task, run the following command: sql> spool off

    To manually create the application database tables, complete the following steps:

    Note: If your database version of IBM Connections components are already at a given schema version, then start running scripts from that schema version level. For example, if the Activities database is schema version 69, enter the commands for schema version 69 and version 72.
  7. Change to the Activities SQL scripts directory Wizards/connections.sql/activities/oracle, and then check for the database schema version as follows:
    select DBSCHEMAVER from ACTIVITIES.OA_SCHEMA
    • If the schema version is 56, then enter the following commands:
      
@upgrade-45-50.sql
    • If schema version is 69, then enter the following commands:
      
@upgrade-50-50CR1.sql
    • If schema version is 72, then enter the following commands:
      
@upgrade-50CR1-55.sql
@appGrants.sql
  8. Change to the Blogs SQL scripts directory Wizards/connections.sql/blogs/oracle, and then check for the database schema version as follows:
    select value from BLOGS.roller_properties where name=
'database.schema.version'
    • If the schema version is 51, then enter the following commands:
      
@upgrade-45-45CR4.sql
    • If schema version is 52, then enter the following commands:
      
@upgrade-45CR4-50.sql
    • If schema version is 69, then enter the following commands:
      
@upgrade-50-50CR1.sql
    • If schema version is 70, then enter the following commands:
        
@upgrade-50CR1-50CR2.sql
    • If schema version is 72, then enter the following commands:
      
@upgrade-50CR2-55.sql   
@appGrants.sql
  9. Change to Bookmarks SQL scripts directory Wizards/connections.sql/dogear/oracle, and then check for the database schema version as follows:
    select DBSCHEMAVER from DOGEAR.DOGEAR_SCHEMA
    • If the schema version is 15, then enter the following commands:
      
@upgrade-45-50.sql
@appGrants.sql
    • If schema version is 22, then enter the following command:
      @upgrade-50-55.sql
@appGrants.sql
  10. Change to the Communities SQL scripts directory Wizards/connections.sql/communities/oracle, and then check the database schema version as follows:
    select DBSCHEMAVER from SNCOMM.SNCOMM_SCHEMA
    • If the schema version is 77, then enter the following commands:
      
@upgrade-45-50.sql
    • If schema version is 93, then enter the following commands:
      
@upgrade-50-50CR2.sql
    • If schema version is 98, then enter the following commands:
      
@upgrade-50CR2-55.sql 
@appGrants.sql
  11. Change to the Calendar SQL scripts directory Wizards/connections.sql/calendar/oracle, and then check the database schema version as follows:
    select DBSCHEMAVER from CALENDAR.CA_SCHEMA
    • If schema version is 15, then enter the following commands:
      @calendar-upgrade-45-50.sql
    • If schema version is 29, then enter the following commands:
      
@calendar-upgrade-50-50CR1.sql
    • If schema version is 30, then enter the following commands:
      
@calendar-upgrade-50CR1-55.sql 
@appGrants.sql
  12. Change to the Files SQL scripts directory Wizards/connections.sql/files/oracle, and then check the database schema version as follows:
    select SCHEMA_VERSION from "FILES"."PRODUCT"
    • If the schema version is 82, then enter the following commands:
      @upgrade-45-45CR3.sql
    • If schema version is 89, then enter the following commands:
      
@upgrade-45CR3-50.sql
    • If schema version is 107, then enter the following commands:
      
@upgrade-50-55.sql
@appGrants.sql
    Note: Do not run the following scripts for the Files application:
    • pns-createDb50.sql
    • pns-dropDb.sql
  13. Create the database schema for Push Notification. Change to the push notification scripts directory Wizards/connections.sql/pushnotification/oracle, and then run the following commands: 
    @application_subdirectory/createDb.sql password
@application_subdirectory/appGrants.sql
  14. Change to the Forum SQL scripts directory Wizards/connections.sql/forum/oracle, and then check the database schema version as follows: 
    select DBSCHEMAVER from FORUM.DF_SCHEMA
    • If the schema version is 27, then enter the following commands:
      
@upgrade-45-50.sql
    • If schema version is 35, then enter the following commands:
      
@upgrade-50-50CR1.sql
    • If schema version is 36, then enter the following commands:
      
@upgrade-50CR1-55.sql
@appGrants.sql
  15. Change to the home page SQL scripts directory Wizards/connections.sql/homepage/oracle, and then check the database schema version as follows: 
    select DBSCHEMAVER from HOMEPAGE.HOMEPAGE_SCHEMA
    • If the schema version is 210, then enter the following commands:
      @upgrade-45-45CR3.sql
    • If schema version is 211, then enter the following command:
      
@upgrade-45CR3-45CR4.sql
    • If schema version is 213, then enter the following commands:
      
@upgrade-45CR4-50.sql

      Next, you need to run a java migration from 4.5 to 5.0.

    • If schema version is 475 , then enter the following commands:
      
@upgrade-50-50CR1.sql
    • If schema version is 477, then enter the following commands:
      
@upgrade-50CR1-50CR2.sql
    • If schema version is 478 , then enter the following commands:
      
@upgrade-50CR2-50CR3.sql
    • If schema version is 479, then enter the following commands:
      
@upgrade-50CR3-55
@appGrants.sql

    Next, you need to run a java migration from 5.0 to 5.5.

  16. Change to the Metrics SQL scripts directory Wizards/connections.sql/metrics/oracle, and then check the database schema as follows:
    select SCHEMA_VERSION from "METRICS"."PRODUCT"
    • If the schema version is 34, then enter the following commands:
      @upgrade-45-45CR2.sql
    • If the schema version is 37, then enter the following commands:
      
@fixup-LO77552.sql 
@upgrade-45CR2-45CR3.sql
@upgrade-45CR4-50.sql
    • If the schema version is 38, then enter the following commands:
      
@upgrade-50-55.sql   
@appGrants.sql
  17. Change to the Mobile SQL scripts directory Wizards/connections.sql/mobile/oracle, and then check the database schema version as follows:
    select VALUE from MOBILE.ROLLER_PROPERTIES where NAME='database.schema.version'
    • If the schema version is not present, then enter the following commands:
      @upgrade-45-45CR4.sql
    • If schema version is 2, then enter the following commands:
      
@upgrade-45CR4-45CR5.sql
    • If schema version is 3, then enter the following commands:
      
@upgrade-45CR5-50.sql

      Disregard any warnings about duplicate foreign keys.

    • If schema version is 7, then enter the following commands:
      
@upgrade-50-55.sql  
@appGrants.sql
  18. Change to the Profiles SQL scripts directory Wizards/connections.sql/profiles/oracle, and then check the database schema version as follows:
    select DBSCHEMAVER from EMPINST.SNPROF_SCHEMA
    • If schema version is 36, then enter the following commands:
      @upgrade-45-50.sql
    • If schema version is 46, then enter the following commands:
       
@upgrade-50-55.sql
@appGrants.sql
@clearScheduler.sql
  19. Change to the Wikis SQL scripts directory Wizards/connections.sql/wikis/oracle, and then check the database schema version as follows:
    select SCHEMA_VERSION from "WIKIS"."PRODUCT"
    • If schema version is 82, then enter the following commands:
      @upgrade-45-45CR3.sql
    • If schema version is 89, then enter the following commands:
      
@upgrade-45CR3-50.sql
    • If schema version is 107, then enter the following commands:
      
@upgrade-50-55.sql 
@appGrants.sql

What to do next

Check that all the databases are working correctly.