Create new Oracle database

In this article

    Are you going to run SuperOffice on an Oracle database, follow these steps to set up the target database.

    Oracle - Setting up the server

    In this document we will not give a description on how to install the Oracle server. Please refer to the documentation provided by Oracle. We do however have some comments on the server installation.

    If your Oracle server is going to serve other databases than the SuperOffice database, you might want to create a own Oracle instance (SID) for the SuperOffice database, or it will be enough to just place the SuperOffice data into a separate tablespace. What you decide on depends on how your server is going to be maintained, and we don't have any preferences one way or another. We do recommend that you at least create a separate tablespace for the SuperOffice data.

    There are some parameters that you only can set during the installation of the server (or creation of the SID), and these should be considered changed during the installation of your Oracle server. The best examples of this is:

    • National Language Support
    • Code page or Character sets
    • Database Block size

    Preparing the Oracle database

    Because customers are sure to have database policies and opinions that we don’t know about, installation on an Oracle server should be done by the customers’ resident DBA. However, if a SuperOffice consultant does do the installation, it is recommended to use the Database Configuration Assistant (DCA).
    In the DCA, choose the “Custom Database” template to avoid installing numerous options and sample data. Then specify passwords, file storage, and so forth. Turn off Oracle Text, OLAP, Spatial, Data Mining, Ultra Search, and other unused things, unless you specifically need them. These options take up disk space, ram, CPU, and can decrease overall database performance.
    In step 10, 4 tabs, you get to the interesting parts. As for Memory and Sizing, set those to what you think is best.  For character sets, Oracle has two character set concepts: database character set and national character set. When you need to set up Unicode on Oracle, please read this first. The default is Code page 1252 ISO-Latin.
    To support Unicode, select the “Use Unicode (AL32UTF8)” option. Choose “AL16UTF8” as the National character set. If you choose UTF8, you may run into problems when storing long Chinese texts in appointment descriptions. Please also take note how the representation surfaces. VARCHAR is limited to 4000 bytes, which is 2000 characters in UTF16, or anywhere from 4000 to 1300  in UTF8. You could choose the “Custom” option for database character set, but you risk being told (later on) that your choice was illegal.

    Creating the SuperOffice tablespace

    Create a tablespace with a descriptive name etc. SuperOffice_DATA and create a datafile called SUPEROFF.ORA (or some other descriptive name). The size should be 200 Mb, and we recommend that you turn on the autoextend options.

    Creating the CRM7 user*

    You now have to create the user who is going to be the owner of all tables used by SuperOffice. If you delete this user after SuperOffice is installed, all SuperOffice data will be deleted.

    Set the following values on the General-Tab:

    Parameter

    Value

    Name

    CRM7 *

    Profile

    Default

    Password

    YourSecurePassword*

    Default tablespace

    SuperOffice_DATA

    Temporary tablespace

    TEMPORARY_DATA

    Status

    Unlocked

    * Note - you may call the CRM7 user anything you want, this is just an example. Also, you may no longer use CRM7myd or any variation of this as a password for the system user.

    Move to the Roles/Privileges-Tab and grant the following roles:

    CONNECT with Admin Option

    DBA with Admin Option

    Set Open Cursors

    The maximum number of Open Cursors depends on the memory available.
    In order to run CRM 7, Open Cursors must be at least 1000.

    To set the number of open cursors, do the following:


    OPEN_CURSORS = 1000

     

    Tuning Parameters

    There are many opinions on how to tune your Oracle server to perform optimal. Our opinion is that in general the more memory installed and allocated to your Oracle server, the better it will perform.

    In addition we will here list a set of server parameters that we mean are optimal for a normal SuperOffice installation:

    Parameter

    Value

    Open_cursors

    1000

    Shared_pool_size

    9000000

    Db_block_size*

    4096

    Db_block_buffer

    200 * Db_block_size

    Log_buffer

    32768

    Dml_locks

    250

    Sort_area_size

    65536

    Processes

    150

    Db_file_multiblock_readcount

    16

     

    * can only be set when the database is created

    These parameters can be set using the Oracle Enterprise Manager Console

    Table spaces

    Temporary tablespace is hard coded to be : TEMPORARY_DATA
    Default tablespace is not hard coded. Give the name you want.

    Make sure the CRM7 user has this as "Default tablespace".

    All Oracle users created by SuperOffice will have Default and Temporary tablespace set to SYSTEM. The fact that Temporary tablespace is set to system for a huge amount of users, may result in that the Tablespace SYSTEM will eventually go full. To avoid this please update the tablespace settings for each Oracle-user that is created, using the Storage Manager.

    A tablespace with this name has to exist in order to run SuperOffice.
    3. Save and close the file
    4. Restart "Oracle Service" and "Oracle Start" in order to initialise the new settings.
    5. To check the new settings start "Instance Manager" and look into "Initialisation Parameters". 

    All Oracle users created by SuperOffice will have Default and Temporary tablespace set to SYSTEM. The fact that Temporary tablespace is set to system for a huge amount of users, may result in that the Tablespace SYSTEM will eventually go full. To avoid this please update the tablespace settings for each Oracle-user that is created, using the Storage Manager.

    When configuring an Oracle database using DbSetup/ServerSetup:

    The configuration program reads Default and Temp TableSpace names from SUPEROFFICE.INI

    [Oracle]
    DefaultTableSpace=USERS
    TemporaryTableSpace=TEMP

    This is useful if you have a non-standard Oracle database server.

    Users created by the setup program (and the web maintenance client later on) will use these settings to define the Oracle tablespace names that the users are granted access to.

    Oracle 18c compatibility

    SuperOffice does not run on Oracle 18 ODBC drivers.

    You may run Oracle 18 on the database server but you need an older Oracle 12c ODBC driver on the SuperOffice client / server.

    Our existing customers can upgrade their database servers to Oracle 18c, and as long as they do not upgrade the database drivers on the machines running SuperOffice it will continue to work.