Creating an Oracle 11g Database

This page describes how to create a generic Oracle 11g database. Each individual Oracle database 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.

If, due to security restrictions, administrator or security administrator access cannot be granted, the local 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 recommended database names.

Advisors Component	Recommended DB name	Notes
Platform	advisors_platformdb	Required for Advisors implementations.
CCAdv/WA		Uses the Platform and Metric Graphing databases.
FA/AA		Starting in release 8.5.0, the FA/AA database is no longer required. FA database content moves to the Platform database. See Object Migration Utility for information about migrating the FA/AA database data and objects to the Platform database.
Metric Graphing	advisors_mgdb	Metric Graphing database. Required for running CCAdv/WA Dashboards and XML Generator.
Advisors Genesys Adapter	advisors_gametricsdb	Used by AGA to transfer Genesys configuration and statistics values to XML Generator for CCAdv/WA. 35px|link= Starting in release 8.5.0, this database includes a table to support calling list statistics. Only required for CCAdv/WA and WA server installations.
Advisors Cisco Adapter	cisco_adapterdb	Required for Cisco Adapter.

<tabber>

Before You Begin=

You must perform all the steps in the procedure on a machine where you have Oracle client installed. The installation scripts require SQLPlus which is installed as part of Oracle client installation. Please verify that you have your ORACLE_HOME environment variable and tnsnames.ora content set properly. Verify the connectivity to the instance by running the following command line:
tnsping <alias to the oracle instance contained in the local tnsnames.ora file>

It is important to use <alias to 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 instance 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)
    )
  )

To check the connectivity type:

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=

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. Start SQLPlus by entering sqlplus /nolog at the command prompt. You should see the prompt change to SQL>. SQL Command Prompt

5. Using a user account that has DBA privileges (for example, SYSTEM), connect to the Oracle instance by entering: conn {User}/{Password}@<alias to the Oracle instance contained in the local your tnsnames.ora file> at the prompt. SQL Command Prompt 2

6. [+] Show steps if required Tablespaces are already present When prompted, enter the full path to your base data file directory (from Step 2), including the trailing slash. 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 SQLPlus connection, which you will reuse later in this procedure. SQL Command Prompt 4 When prompted, enter the schema password. SQL Command Prompt 7 On the SID prompt, enter the alias to the Oracle instance contained in the local tnsnames.ora. SQL Command Prompt 8 Once the script completes and SQLPlus exits, verify the results by examining the runUsrCre.log file, located in the same directory as your installation scripts. [+] Show steps if required Tablespaces do not yet exist Run the tablespace script by entering @<script name> at the prompt, where <script name> is the name of your tablespace script. For example, @advisors-platform-8.5.0_TBS.sql if you are creating a Platform database (see the following Figure). SQL Command Prompt 3 See for details of script names supplied in the distribution. When prompted, enter the full path to your base data file directory (from Step 2), 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's 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 SQLPlus connection, which you will reuse later in this procedure. SQL Command Prompt 4 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. Alternately, you can run the following query from any Oracle client connected as the system user: SELECT * FROM dba_data_files To create the database schema and objects, and to load initial data, connect as a user with database administrator privileges (such as SYSTEM), and run the schema script by entering @plt-<version>_Schema.sql at the prompt. SQL Command Prompt 5 When prompted, enter your schema name for the database objects. (The following Figures use AdvPlatform as an example.) SQL Command Prompt 6 When prompted, enter the schema password. SQL Command Prompt 7 On the SID prompt, enter the alias to the Oracle instance contained in the local tnsnames.ora. SQL Command Prompt 8 Once the script completes and SQLPlus exits, verify the results by examining the runUsrCre.log file, located in the same directory as your installation scripts.