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.
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, which are 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.
Examples of Schema/User Names
||Required for Advisors implementations.
||Use the Platform and Metric Graphing schemas.
||Uses the Platform schema.
||Metric Graphing schema. Required to run the CCAdv/WA dashboards and CCAdv XML Generator.
|Advisors Genesys Adapter
||AGA metrics schema. Used by AGA to transfer Genesys real-time statistic values to CCAdv/WA. This schema is required for CCAdv and WA server installations only.
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>.
Your tnsnames.ora contains the following entry:
(ADDRESS = (PROTOCOL = TCP)(HOST = inf-wolf.qalab.com)(PORT = 1521))
(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:
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: Creating the Advisors Oracle Schema
- 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.
- 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.
- Review the "readme" files located in the script directories.
- 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.
- 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.
- 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.
- 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.
- 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:
Note that the script will preserve your SQL*Plus connection, which you can reuse later in this procedure.
- Create the tablespaces if they do not yet exist, or
- Skip the creation if the tablespaces are already present.
The following figure shows an example.
- Verify the results of your script execution:
- 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.
- 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
- Starting with Advisors Platform release 188.8.131.52, 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.
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.
- 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.
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.