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.
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)
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.