Oracle

This section includes information about how to perform appropriate settings for the Oracle database.
uim203
This section includes information about how to perform appropriate settings for the Oracle database.
Review the following information in this section:
2
2
CA UIM requires a back-end database to store performance data and events. When CA UIM performance data is stored in an Oracle database, it executes PL/SQL blocks; for example, stored procedures, functions, and triggers. The execution requires DBA privilege be given/granted explicitly to the CA_UIM user. Granting the privilege through a
role
will NOT work because of an Oracle database limitation. For more information about this Oracle database limitation, see How Roles Work in PL/SQL Blocks.
Install Oracle Instant Client
The Oracle Instant Client must be installed on the CA UIM system so it can access the Oracle database.
Follow these steps:
  1. Go to www.oracle.com and download the zip file for the
    Instant Client Package - Basic
    SQL *Plus Package
    Only 12.1 version of Oracle Instant Client is supported; higher versions (for example, 12.2) are not supported.
  2. Install the Instant Client according to the instructions in the Installation guide on the Oracle web site. The UIM installer asks for the location of the Instant Client.
  3. Please make sure that you have downloaded SQL *Plus package and verify if you are able to connect with net service name to Oracle Server to avoid any errors during UIM installation.
  4. Restart the system.
Set the Configuration Parameters
The Oracle administrator must also set the following required configuration parameters before installing CA UIM.
Follow these steps:
  1. As the Oracle database administrator, execute:
    ALTER SYSTEM SET PROCESSES = 300 SCOPE=SPFILE; ALTER SYSTEM SET SESSIONS = 335 SCOPE=SPFILE; -- 1.1 * PROCESSES +5 ALTER SYSTEM SET OPEN_CURSORS = 500 SCOPE=BOTH;
  2. Restart the database.
Grant Permission
As the Oracle database administrator, execute the following command to grant permission to the CA UIM user:
Grant execute on DBMS_CRYPTO TO
<UIM_USER>
;
Configure Settings for Oracle Shared Server
If your Oracle database is configured for shared server use, you can increase the total number of allowed shared server sessions using the
SHARED_SERVER_SESSIONS
parameter. Generally, we recommend increasing the
SHARED_SERVER_SESSIONS
to 300 as a starting point.
The error message
ORA-00018: maximum number of sessions exceeded
during UIM installation indicates that the number of allowed shared server sessions should be increased.
(OC Only) Turn off the Oracle Recycle Bin
If you will install the Operator Console (OC), then the recycle bin must be turned off before you install UIM Server.
Follow these steps:
  1. Use a tool such as SQL Developer to connect to the Oracle database.
  2. Enter the following commands:
    ALTER SYSTEM SET recyclebin = OFF DEFERRED; ALTER SESSION SET recyclebin = off;
  3. Verify that the recycle bin is off using the following command:
    show parameter recyclebin;
We do not recommend turning the Oracle Recycle Bin back on after installing OC.
Verify Linking for Shared Oracle Libraries on Unix Systems
Shared Oracle libraries on Unix-based systems must be linked.
Follow these steps:
  1. Go to the Instant Client.
  2. Execute:
    ldd libociei.so
  3. Verify that there are links for all the libraries and that there are no
    not found
    messages. The output should look similar to the following:
    linux-vdso.so.1 +> (0x00007fff5b0e2000) libclntsh.so.11.1 => /root/instantclient_11_1/libclntsh.so.11.1 (0x00007f36030b3000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f3602eae000) libm.so.6 => /lib64/libm.so.6 (0x00007f3602c57000) libpthread.so.0 +> /lib64/libpthread.so.0 (0x00007f3602a3a000) libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f3602821000) libc.so.6 => /lib64/libc.so.6 (0x00007f36024c1000) libnnz11.so => /root/instantclient_11_1/libnnz11.so (0x00007f3602064000) libaio.so.1 => /lib64.libaio.so.1 (0x00007f3601e61000) /lib64/ld-linux-x36-64.so.2 (0x00007f360a0a0000)
Manual Creation of the Tablespace and User
The procedure for creating a tablespace manually depends on the version of Oracle that you are using.
Oracle 12c or higher
Create a pluggable database using the files of a seed database. See the Oracle documentation for details about the options available when you create a database from a seed.
Follow these steps:
  1. Log in to the Oracle database as the administrator (sys as sysdba).
  2. Connect to the pluggable database using
    sys as sysdba
    The service name for the connection is the pluggable database name where you would like to create your user and tablespace.
  3. Create the tablespace. Execute the following statement, where
    ts_name
    is a tablespace name of your choice (typically,
    CA_UIM)
    :
    create tablespace
    ts_name
    datafile '
    ts_name
    .dbf' size 1000m autoextend on maxsize unlimited;
  4. Create the owner and assign the required privileges. Execute the following statement, where
    db_owner
    is the name of the user to create, and
    ts_name
    is the tablespace:
    create user
    db_owner
    IDENTIFIED BY
    owner_password
    DEFAULT TABLESPACE
    ts_name
    ;
    grant unlimited tablespace to
    db_owner
    ;
    grant administer database trigger to
    db_owner
    ;
    grant create table to
    db_owner
    ;
    grant create view to
    db_owner
    ;
    grant alter any table to
    db_owner
    ;
    grant select any table to
    db_owner
    ;
    grant create sequence to
    db_owner
    ;
    grant create procedure to
    db_owner
    ;
    grant create session to
    db_owner
    ;
    grant create trigger to
    db_owner
    ;
    grant create type to
    db_owner
    ;
    grant select on sys.v_$session to
    db_owner
    ;
    grant execute on sys.dbms_lob to
    db_owner
    ;
    grant execute on dbms_redefinition to
    db_owner
    ;
    grant create any table to
    db_owner
    ;
    grant drop any table to
    db_owner
    ;
    grant lock any table to
    db_owner
    ;
    • The owner and the tablespace commonly have the same name.
    • The
      grant unlimited tablespace
      command sets the quota for all tablespaces to unlimited. To set the quota for only the UIM database, execute the following statement in place of
      grant unlimited tablespace
      . This configuration has not been tested.
      alter user
      db_owner
      quota unlimited on
      ts_name
  5. Start the UIM Server installer. When you are prompted, enter the following information:
    • Service Name
      : Name of the pluggable database instance,
      pdb_name,
      you created
    • Port
      : Port of the Oracle database
    • Username
      : Username for the local user,
      non_admin_user
Your database server is ready.
Oracle Real Application Clusters (Oracle RAC)
You can install CA UIM against Oracle RAC. This is a clustered version of Oracle Database based on a comprehensive high-availability stack that can be used as the foundation of a database cloud system as well as a shared infrastructure, ensuring high availability, scalability, and agility for any application.
Follow these steps:
  1. Install Oracle Instant Client
  2. Set Configuration Parameters
  3. Configure Settings for Oracle Shared Server
  4. Manually create the tablespace and user.
  5. During the CA UIM installation, select "Use Existing Database". Ensure that you keep only one Oracle RAC node alive during the CA UIM installation time. If you have multiple Oracle RAC nodes running during the CA UIM installation, the installation fails. Check the log to review the error messages.
Install Oracle Instant Client
The Oracle Instant Client must be installed on the CA UIM system so it can access the Oracle database.
Follow these steps:
  1. Go to www.oracle.com and click
    Downloads, Instant Client
    .
  2. Click the link for the operating system and hardware of your system.
  3. Download the zip file for the
    Instant Client Package - Basic
    .
  4. Install the Instant Client according to the directions on the web site. The UIM installer asks for the location of the Instant Client.
  5. Restart the system.
Set Configuration Parameters
The Oracle administrator must also set the following required configuration parameters before installing CA UIM.
Follow these steps:
  1. As the Oracle database administrator, execute:
    ALTER SYSTEM SET PROCESSES = 300 SCOPE=SPFILE; ALTER SYSTEM SET SESSIONS = 335 SCOPE=SPFILE; -- 1.1 * PROCESSES +5 ALTER SYSTEM SET OPEN_CURSORS = 500 SCOPE=BOTH;
  2. Restart the database.
Configure Settings for Oracle Shared Server
If your Oracle database is configured for shared server use, you can increase the total number of allowed shared server sessions using the
SHARED_SERVER_SESSIONS
parameter. Generally, we recommend increasing the
SHARED_SERVER_SESSIONS
to 300 as a starting point.
The error message
ORA-00018: maximum number of sessions exceeded
during UIM installation indicates that the number of allowed shared server sessions should be increased.
Manual Creation of the Tablespace and User
The procedure for creating a tablespace manually depends on the version of Oracle that you are using.
Oracle 12c or higher(for Multi-tenant databases)
Create a pluggable database using the files of a seed database. See the Oracle documentation for details about the options available when you create a database from a seed.
Follow these steps:
  1. Log in to the desired Oracle database as the administrator (sys as sysdba).
  2. Create a pluggable database. Execute the following statement, where <pdb_name> is the name of a pluggable database, <ts_name> is a tablespace name of your choice (for example, uim_ts), <dg_name> is an ASM Disk Group Name (for example, +data):
    create pluggable database <pdb_name> admin user <db_owner> identified by <owner_password>
    default tablespace <ts_name>
    datafile '<dg_name>' size 500m autoextend on
    file_name_convert = ('<location_of_db_to_be_cloned>', '<dg_name>');
    alter pluggable database <pdb_name> open;
  3. Preserve PDB Startup State (CDB - Container Database, PDB - Pluggable Database).
    • Prior to 12.1.0.2:
      Create a system trigger on the CDB to start some or all of the PDBs. Use the following command:
      CREATE OR REPLACE TRIGGER open_pdbs
      AFTER STARTUP ON DATABASE
      BEGIN
      EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
      END open_pdbs;
      /
    • 12.1.0.2 onwards:
      Use the following command:
      ALTER PLUGGABLE DATABASE <pdb_name> SAVE STATE;
  4. Connect to the pluggable database using 'sys as sysdba'. The service name for the pluggable database is the <pdb_name> as created in Step 2.
  5. Create a non-administrator user in the CA UIM pluggable database. Execute the following statement, where <non-admin_owner> is the name of the user to be created:
    create user <non_admin_user> identified by <user_password>;
  6. Grant the necessary privileges to the local user <non_admin_user>.
    grant unlimited tablespace to <non_admin_user>;
    grant administer database trigger to <non_admin_user>;
    grant create table to <non_admin_user>;
    grant create any table to <non_admin_user>;
    grant create view to <non_admin_user>;
    grant alter any table to <non_admin_user>;
    grant select any table to <non_admin_user>;
    grant create sequence to <non_admin_user>;
    grant create procedure to <non_admin_user>;
    grant create session to <non_admin_user>;
    grant create trigger to <non_admin_user>;
    grant create type to <non_admin_user>;
    grant drop any table to <non_admin_user>;
    grant lock any table to <non_admin_user>;
    grant select on sys.v_$session to <non_admin_user>;
    grant execute on sys.dbms_lob to <non_admin_user>;
    grant execute on dbms_redefinition to <non_admin_user>;
  7. Start the UIM Server installer. When prompted, enter the following information:
    • Service Name:
      Name of the pluggable database instance <pdb_name> you created
    • Username:
      Username for local user <non_admin_user>
    • Port:
      Port of the Oracle database
    Pluggable database does not always open correctly when hard failover happens (for example, reboot the primary Oracle RAC node). In this case we need to bring it back to open by executing:
    alter pluggable database <pdb_name> open;
    Your database is ready.
Consideration
If you are installing UIM in a Linux environment with Oracle as a database, review the following consideration:
  • Ensure that libnsl.x86_64 and libaio are already installed on the UIM Server before you proceed with the UIM installation.