Documentation Center

Upgrading Oracle database schemas

For most versions of WorldServer, you can upgrade Oracle databases on the database server machine only one version at a time. For example, you cannot upgrade directly from version 10.4.3 to version 11.0.1; you have to upgrade from version 10.4.3 to version 10.4.4 first, then you can jump from version 10.4.4 to version 11.0.0, and only then can you upgrade from version 11.0.0 to version 11.0.1. Always take into account the versions of the available upgrade scripts before planning your upgrade.

Before you begin

  • Make sure you have a database connection for the WorldServer version you want to upgrade from. For example, if you want to upgrade from version 10.4.3, make sure you have a 10.4.3 database connection.
  • Make sure that the NLS_LENGTH_SEMANTICS parameter is set to its default value of BYTE.
  • Make sure you have a user with the required roles and system privileges in Oracle SQL Developer. If you do not have such a user, create one.
    • Required roles: connect and resource
    • Required privileges: Create synonym, Create view, and Create table
  • If you want to use the System Monitor tool, connect as sysdba and run the following commands:
    GRANT SELECT ON SYS.V_$SESSION TO <user name>;
    GRANT SELECT ON SYS.V_$LOCKED_OBJECT TO <user name>;
    GRANT SELECT ON SYS.V_$SQLTEXT_WITH_NEWLINES TO <user name>;
    GRANT SELECT ON SYS.V_$SQL TO <user name>;
    GRANT SELECT ON SYS.DBA_OBJECTS TO <user name>;
    COMMIT;
    In this case, <user name> is the name of the database user and is provided as an argument to Oracle SQL Developer.

Procedure

  1. Associate the database connection with the user:
    1. In Oracle SQL Developer, under Connections, right-click the appropriate database connection, and then select Properties.
    2. In the Username box, enter the name of the user with all the roles and system privileges.
    3. In the Password box, enter the password of that user.
    4. Select the SID option and change its value to wsdb.
    5. Select Test, and then, if the result is successful, select Connect.
  2. Copy the appropriate upgrade scripts to a local folder.
    You can find these scripts in the upgrade folder of the WorldServer distribution kit.
    For example, if you want to upgrade from version 10.4.3 to version 11.0.1, go to the upgrade folder of the WorldServer 11.0.1 distribution kit and copy the following upgrade scripts:
    • WS-Upgrade-10.4.3-to-10.4.4.ora.sql
    • WS-Upgrade-10.4.4-to-11.0.ora.sql
    • WS-Upgrade-11.0.0-to-11.0.1.ora.sql
  3. Run the upgrade scripts.

Results

You have now upgraded your Oracle database schema.

What to do next

Upgrade your WorldServer software, and then validate your upgrade. Make sure you have finished running all the database upgrade scripts before you test your upgrade with the testschema script.