Configuring Oracle Database

To configure Oracle Database for use with Maximo® Manage, you create table spaces, create a database user, and configure database settings.

Before you begin

For information about installing and deploying Oracle Database, review the Oracle Database product documentation.

For information about supported database versions, generate a Software Product Compatibility Report. For more information, see Software Product Compatibility Report. Search for IBM® Maximo Application Suite and select the suite version to generate the report. Check for the supported database versions on the Supported Software tab of the report.

Configure your database with the following operating system:
  • Linux® or UNIX
  • Microsoft Windows

For more information about system performance, see Best practices for system performance.

Procedure

  1. Log in as the Oracle software user. Typically, this user is named oracle.
  2. To manage requests to connect to the database, create the database listener.
  3. Create a database for use by Maximo Manage.
    For the database initialization parameters, change the values of the following parameters:
    nls_length_semantics
    Change this value to CHAR.
    open_cursors
    Change this value to 1000.
    cursor_sharing
    Set this value to FORCE.
    Note: Ensure that the database character setting is set to the AL32UTF8 character set, which is required for Oracle Database.
  4. In SQL*Plus, create a table space by running the following command. Replace the directory with the path to the database location. 
    Create tablespace maxdata datafile 
'C:\oracle\product\12.1.0.1\db_1\dbs\maxdata.dbf' 
size 1000M autoextend on;

    To create table spaces for indexes, repeat the command and use a similar syntax.

  5. Create a temporary table space by running the following command. Replace the directory with the path to the database location. 
    create temporary tablespace maxtemp tempfile  
'C:\oracle\product\12.1.0.1\db_1\dbs\maxtemp.dbf'
size 1000M autoextend on maxsize unlimited;
  6. To create the Maximo user and grant permissions, run the following command: 
    create user maximo identified by maximo default tablespace maxdata temporary 
tablespace maxtemp;
grant connect to maximo;
grant create job to maximo;
grant create trigger to maximo;
grant create session to maximo;
grant create sequence to maximo;
grant create synonym to maximo;
grant create table to maximo;
grant create view to maximo;
grant create procedure to maximo;
grant alter session to maximo;
grant execute on ctxsys.ctx_ddl to maximo;
alter user maximo quota unlimited on maxdata;

    If you created a separate table space for indexing, you must also grant access to that index table space to the Maximo user.

    For example, if you created a separate table space for indexing that is called TSI_MAM_OWN, then run the following command:
    alter user maximo quota unlimited on TSI_MAM_OWN
  7. Create an Oracle preference arbitrary that is called MAXIMO_STORAGE and store the Oracle text indexes in dedicated table spaces.
    • Store implicit indexes in the MAXINDX table space.
    • Store implicit tables in the MAXDATA table space.
    • Store implicit LOB tables in the LOB table space.
    For example, run the following preference definition to split the implicit objects in a text index across three table spaces: MAXDATA, MAXINDX, and MAXLOBS.
    begin
ctx_ddl.create_preference('MAXIMO_STORAGE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('MAXIMO_STORAGE', 'I_TABLE_CLAUSE',
'tablespace MAXDATA LOB(token_info) store as (tablespace MAXLOBS
enable storage in row)');
ctx_ddl.set_attribute('MAXIMO_STORAGE', 'I_INDEX_CLAUSE',
'tablespace MAXINDX compress 2');
ctx_ddl.set_attribute('MAXIMO_STORAGE', 'K_TABLE_CLAUSE',
'tablespace MAXINDX');
ctx_ddl.set_attribute('MAXIMO_STORAGE', 'R_TABLE_CLAUSE',
'tablespace MAXDATA LOB(data) store as (tablespace MAXLOBS
cache)');
ctx_ddl.set_attribute('MAXIMO_STORAGE', 'N_TABLE_CLAUSE',
'tablespace MAXINDX');
end;

    To create an Oracle text index, the preference definition must be specified in the CREATE INDEX clause as shown in the following example.

    create  index pm_ndx6 on pm (description) indextype is
ctxsys.context parameters ('lexer global_lexer language column
LANGCODE storage MAXIMO_STORAGE');
  8. Set up Oracle text preferences and sublexer definitions.
    1. Use an SQL query tool to log on to the database as the maximo user, which is the schema owner, and run the following set of calls. 
      call ctx_ddl.drop_preference('global_lexer');
call ctx_ddl.drop_preference('default_lexer');
call ctx_ddl.drop_preference('english_lexer');
call ctx_ddl.drop_preference('chinese_lexer');
call ctx_ddl.drop_preference('japanese_lexer');
call ctx_ddl.drop_preference('korean_lexer');
call ctx_ddl.drop_preference('german_lexer');
call ctx_ddl.drop_preference('dutch_lexer');
call ctx_ddl.drop_preference('swedish_lexer');
call ctx_ddl.drop_preference('french_lexer');
call ctx_ddl.drop_preference('italian_lexer');
call ctx_ddl.drop_preference('spanish_lexer');
call ctx_ddl.drop_preference('portu_lexer');
call ctx_ddl.create_preference('default_lexer','basic_lexer');
call ctx_ddl.create_preference('english_lexer','basic_lexer');
call ctx_ddl.create_preference('chinese_lexer','chinese_lexer');
call ctx_ddl.create_preference('japanese_lexer','japanese_lexer');
call ctx_ddl.create_preference('korean_lexer','korean_morph_lexer');
call ctx_ddl.create_preference('german_lexer','basic_lexer');
call ctx_ddl.create_preference('dutch_lexer','basic_lexer');
call ctx_ddl.create_preference('swedish_lexer','basic_lexer');
call ctx_ddl.create_preference('french_lexer','basic_lexer');
call ctx_ddl.create_preference('italian_lexer','basic_lexer');
call ctx_ddl.create_preference('spanish_lexer','basic_lexer');
call ctx_ddl.create_preference('portu_lexer','basic_lexer');
call ctx_ddl.create_preference('global_lexer', 'multi_lexer');
call ctx_ddl.add_sub_lexer('global_lexer','default','default_lexer');
call ctx_ddl.add_sub_lexer('global_lexer','english','english_lexer','en');
call ctx_ddl.add_sub_lexer('global_lexer','simplified chinese','chinese_lexer','zh');
call ctx_ddl.add_sub_lexer('global_lexer','japanese','japanese_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','korean','korean_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','german','german_lexer','de');
call ctx_ddl.add_sub_lexer('global_lexer','dutch','dutch_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','swedish','swedish_lexer','sv');
call ctx_ddl.add_sub_lexer('global_lexer','french','french_lexer','fr');
call ctx_ddl.add_sub_lexer('global_lexer','italian','italian_lexer','it');
call ctx_ddl.add_sub_lexer('global_lexer','spanish','spanish_lexer','es');
call ctx_ddl.add_sub_lexer('global_lexer','portuguese','portu_lexer',null);


commit;

What to do next

Gather the following information for your database:
  • The host and hostname.
  • The port.
  • The database name.
  • The username and password for the database user. These values are created as part of configuring the database.
  • The table space, index space, and schema values. These values are created as part of configuring the database.
If you modified the default role sets assigned to the user ID used to connect to the database, then you must explicitly grant the role sets to the Maximo user. If you restricted the default privileges that are granted to user IDs, you must also explicitly grant the role sets to the Maximo user. For example, if you do not grant a role such as the select_catalog_role role, you must explicitly grant that role to the Maximo user. Make the assignment by running the following SQL*Plus command:
grant select_catalog_role to maximo