Oracle

This section includes information about to perform appropriate settings for the Oracle database.
uim902-9-0-2
This section includes information about 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 
    (use version 12.1.0.2.0).
     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 directions on the web site. The UIM installer asks for the location of the Instant Client.
  3. 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. For Oracle 11g servers, we recommended that you define a SQLNET.EXPIRE_TIME in $ORACLE_HOME/network/admin/sqlnet.ora (defined in minutes).  A common configuration is: SQLNET.EXPIRE_TIME=10.  This ensures that connections are not left open indefinitely due to an abnormal client termination.  For more information, see Oracle documentation at: SQLNET.EXPIRE_TIME for Oracle 11g.
  3. 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.
(UMP Only) Turn off the Oracle Recycle Bin
If you will install the Unified Management Portal (UMP), 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 UMP.
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
 
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 11g or Earlier
For Oracle 11g or earlier, follow the steps in this procedure.
 
Follow these steps:
 
  1. Log in as the Oracle administrator. 
  2. 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;
  3. Create the owner and assign required privileges. Execute the following statement, where <
    db_owner
    > is the name of the user to be created and <
    ts_name
    > is the tablespace: 
    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>;
    Note that:
    • The owner and tablespace
       
      commonly have the same name.
    • The 
      grant unlimited tablespace 
      command sets the quota for all tablespaces to unlimited. Although not tested by CA, you can set the quota for only the UIM database by executing the following statement in place of 
      grant unlimited tablespace to <db_owner>
      :
      alter user <db_owner> quota unlimited on <ts_name>;
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 Configuraiton 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.
  6. Manually create the tablespace and user for UR, if you want to install it.
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. For Oracle 11g servers, we recommended that you define a SQLNET.EXPIRE_TIME in $ORACLE_HOME/network/admin/sqlnet.ora (defined in minutes).  A common configuration is: SQLNET.EXPIRE_TIME=10.  This ensures that connections are not left open indefinitely due to an abnormal client termination.  For more information, see Oracle documentation at: SQLNET.EXPIRE_TIME for Oracle 11g.
  3. 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
 
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.
 
Oracle 11g or Earlier
 
For Oracle 11g or earlier, follow the steps in this procedure.
 
Follow these steps:
 
  1. Log in as the Oracle administrator.
  2. Create the tablespace. Execute the following statement, where <ts_name> is a tablespace name of your choice (typically CA_UIM) and <dg_name> is an ASM Disk Group Name (for example, +data):
    create tablespace <ts_name> datafile '<dg_name>' size 1000m autoextend on maxsize unlimited;
  3. Create the owner and assign required privileges. Execute the following statement, where <db_owner> is the name of the user to be created and <ts_name> is the tablespace:
    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 tablespace commonly have the same name.
    • The grant unlimited tablespace command sets the quota for all tablespaces to unlimited. Although not tested by CA, you can set the quota for only the UIM database by executing the following statement in place of grant unlimited tablespace to <db_owner>:
    alter user <db_owner> quota unlimited on <ts_name>;