Jump to: navigation, search

Creating the Oracle Schema for Advisors

This page describes how to create a generic Oracle schema for Advisors. Each individual Oracle schema in an Advisors implementation has its own creation script in the 8.5 release.

In 8.5.x releases, all Oracle scripts are creation scripts except those that contain the word migrate in the name. Any existing schema with the same name must be dropped prior to running the scripts. Use the migration scripts when upgrading your software version. Always review the "readme" files, if supplied, along with the database scripts. The "readme" files can contain important details, specifics and exceptions related to a particular release and not reflected in the general documentation.

If, due to security restrictions, administrator or security administrator access cannot be granted, the local Database Administrator (DBA) should implement the steps described in the procedure.

The procedure applies to an Oracle user who has permissions to create tablespaces, users, and to grant permissions. Follow your enterprise’s policies in production environments. If necessary, have the DBA create tablespaces, users, and grant permissions. Use scripts relevant to your environment after the DBA completes the work. Refer to the script content description contained in Advisors Software Distribution Contents.

[+] See examples of schema/user names.


<tabber>

Before You Begin=

You must perform all of the steps in the procedure on a machine where you have Oracle client or Oracle instant client installed. The installation scripts require SQL*Plus, which is installed as part of the Oracle client installation or added in addition to the Oracle instant client installation.

Verify that you have your system or session ORACLE_HOME or TNS_ADMIN environment variable and tnsnames.ora content set properly. If you have full Oracle client installed, you can verify the connectivity to the database by running the following command:
tnsping <alias for the oracle instance contained in the local tnsnames.ora file>

It is important to use <alias for the oracle instance contained in the local tnsnames.ora file> as a response on all prompts where the database scripts ask you to <Enter the database alias>.

For example:
Your tnsnames.ora contains the following entry:

wolf =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = inf-wolf.qalab.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.qalab.com)
    )
  )

On the machine with full Oracle client installation, you can check the connectivity by typing the following command:

C:>tnsping wolf

The successful message will look as follows:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = inf-wolf.qaslab.com)(PORT = 1521)) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.qalab.com))) 
OK (0 msec)

|-| Procedure=

Procedure: Creating the Advisors Oracle Schema


Steps

  1. Copy all of your Oracle database scripts to a folder on the machine where you have the Oracle client installed. The path name for this location must not contain spaces.
  2. On the machine where the Oracle client is installed, open a command prompt and change directory to the folder where the database scripts now reside.
  3. Review the "readme" files located in the script directories.
  4. Database scripts are encoded in Windows-1252 format. Before you start SQL*Plus, be sure to set your session to a value with this encoding. See the Oracle NLS_LANG FAQ for more information.
    Set the NLS_LANG variable and start SQL*Plus.
    The figure below shows an example of the commands for Linux and Oracle 11g.
    SQL Command Prompt
  5. Using a user account that has DBA privileges (for example, SYSTEM), enter the following at the prompt to connect to the Oracle instance:
    conn <User>/<Password>@<alias for the Oracle instance contained in your local tnsnames.ora file>
    See the following figure for an example of the command entry.
    SQL Command Prompt 2
  6. If the tablespaces are already present, you can go to Step 7. Otherwise, create tablespaces as described in this Step.
    You can either edit the tablespace script in order to adapt it to your environment, or you can create the tablespaces manually. Genesys recommends that you create at least a dedicated data tablespace and a dedicated temporary default tablespace for each Advisors user/schema.
    1. You, as a privileged user, or your DBA if you do not have privileged user access, must run the tablespace script contained in the installation package (the script name ends with _TBS.sql). To run the tablespace script, enter @<script name> at the SQL*Plus prompt. For example:
      @advisors-platform-8.5.xxx_TBS.sql, if you are creating a Platform schema; or
      @gc-metrics-8.5.xxx_TBS.sql, if you are creating an AGA METRICS schema; or
      @mg-8.5.xxx_TBS.sql, if you are creating a metric graphing schema.

      See the following figure for an example of the command entry. The figure shows an example that uses Linux. The name of the script supplied in the installation package contains the specific release number of Advisors Platform that you will be installing.

      SQL Command Prompt 3
    2. When prompted, enter the full path to your base data file directory including the trailing slash. This is the path on the server where ORACLE is installed; you are indicating where to put the files that will contain the tablespace data. The script will either:
      • Create the tablespaces if they do not yet exist, or
      • Skip the creation if the tablespaces are already present.
      Note that the script will preserve your SQL*Plus connection, which you can reuse later in this procedure.
      The following figure shows an example.
      SQL Command Prompt 4
    3. Verify the results of your script execution:
      1. Using a separate command prompt/terminal session, examine the runTbsCre.log file. You can find this log file in the same directory as your installation scripts.
      2. Browse your data file location to ensure that the files were created. Alternatively, you can run the following query from any Oracle client connected as the system user:
        SELECT * FROM dba_data_files
  7. Starting with Advisors Platform release 8.5.101.17, you must create a job class with the name GenAdvisorsJobClass before the creation of the Platform schema objects. Only a privileged user, either you or your DBA, can create the job class. The privileged user must run the advisors-platform-<version>_DBMS_SCHEDULER.sql script supplied in the installation package. Verify the results as shown in the following figure.
    SQL Command Prompt 5
  8. Create the user/schema and schema objects.

    [+] Show steps to create the user/schema and schema objects separately

    [+] Show steps to create the user/schema and schema objects in one step

    No additional action is required if you create the Platform schema with the scripts supplied in the installation package – that is, using only the advisors-platform-<version>_Schema.sql script (run by a privileged user), or using the advisors-platform-<version>_User.sql script (run by a privileged user) plus the advisors-platform-<version>_Objects<...>.sql script (run by the Platform user), as described above.

    If the user is created in any way other than what is described in this Step, then an additional action is required; see Step 9.

  9. If the user is created in any way other than what is described in Step 8, then a privileged user, either you or your DBA, must ensure that all privileges listed in the advisors-platform-<version>_User.sql script are granted to the Platform user, either directly or through database roles.



35px|link= If you are installing or migrating to Advisors release 8.5.202.09, and have an installation with CISCO ICM, you need to apply the advisors-platform-8.5.202.09_CiscoPostInstall.sql post-installation script that can be found in the \ip\platform-database-sql\oracle\CISCO folder. The script must be applied before you start any of the 8.5.202.09 components for the first time.

If you make a mistake and start the components before you apply this script, stop all components, verify that there are no live sessions from any machine that hosts Advisors components, connect as the Platform schema owner, and issue the following command:

 
DELETE tmpImportCallType;
COMMIT;

Once that is done, re-run the advisors-platform-8.5.202.09_CiscoPostInstall.sql script.

In addition to the preceding action, you can also repeat the verification script described in the Database Recommendations for Oracle Users section of this guide.

There is no negative impact if the advisors-platform-8.5.202.09_CiscoPostInstall.sql script is executed more than once, or executed in installations that do not use CISCO ICM.

This concludes a general Oracle schema/user setup where each created user is the owner of the corresponding schema: Platform, AGA metrics, or metric graphing. You can now specify the user in the relevant Advisors installation wizard screens related to database connectivity. Starting with Advisors release 8.5.202, you have the option to configure database access through runtime users with least privileges, rather than through users who are "schema owners" . The procedure to create runtime users is implemented on top of the general Oracle schema/user setup. See Least Privileges: How to Configure Advisors Database Accounts with Minimal Privileges for more information.

This page was last edited on August 14, 2019, at 13:49.
Comments or questions about this documentation? Contact us for support!