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
|Advisors Component||Schema/user name||Notes|
|Platform||advisors_platformdb||Required for Advisors implementations.|
|CCAdv/WA||Use the Platform and Metric Graphing schemas.|
|FA||Uses the Platform schema.|
|Metric Graphing||advisors_mgdb||Metric Graphing schema. Required to run the CCAdv/WA dashboards and CCAdv XML Generator.|
|Advisors Genesys Adapter||advisors_gametricsdb||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.|
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:
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:
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)
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.