Creating tablespaces and users on Oracle

You can use the create_oracle_tablespaces_and_users.sql script to create databases and users for the Oracle database. This script is available in the TSSA-DW<version>-external-files.zip file on the  TrueSight Server Automation - Data Warehouse  BMC Electronic Product Distribution (EPD) location.

By default, the script creates the following Oracle tablespace names: 

Oracle tablespaces

By default, the script creates the users shown in the following table. 

Oracle users

To create tablespaces and users

1. Download the product files from the  TrueSight Server Automation - Data Warehouse  BMC Electronic Product Distribution location.
2. Extract the TSSA-DW<version>-external-files.zip file to a directory outside the C:\Program Files directory to avoid any file sharing conflicts.

3. Ensure that the data warehouse schema type aligns with the TrueSight Server Automation database in one of the following ways:

   • • If you have one or more TrueSight Server Automation databases that use the CHAR schema, the data warehouse must use the CHAR schema.

     • If you have one or more TrueSight Server Automation databases that use the NCHAR schema, the data warehouse must use the NCHAR schema.

     • If you have multiple TrueSight Server Automation databases and some use the CHAR schema and others use the NCHAR schema, the data warehouse must use the NCHAR schema. 

        To determine the schema type used by the TrueSight Server Automation database, do the following:

       1. As any user, log in to SQL Plus. For example, system.

       2. Run the following command:

           select data_type from user_tab_columns where table_name = 'SYSTEM_PROPERTY' and column_name like 'NAME' 

          
          For the CHAR type schema, VARCHAR2 is returned as the output. For the  NCHAR type schema, NVARCHAR2 is returned as the output.

        To determine the schema type used by the TrueSight Server Automation - Data Warehouse database, do the following:

       1. As any user, log in to SQL Plus. For example, system.

       2. Run the following command:

          select * from user_tab_columns where table_name = 'BL_SITE' and column_name like 'NAME'

          For the CHAR type schema, VARCHAR2 is returned as the output. For the  NCHAR type schema, NVARCHAR2 is returned as the output.

4. If you are using NCHAR schema, ensure that you use a Unicode value of AL16UTF16.

   NLS_NCHAR_CHARACTERSET=AL16UTF16. 

    To verify that the database is Unicode, do the following:

   1. As any user, log in to SQL Plus. For example, system.

   2. Run the following command:

      select parameter, value from nls_database_parameters where parameter='NLS_CHARACTERSET'

      Verify that a Unicode value is returned (AL32UTF8 or AL16UTF16). If a non-Unicode value is returned, create a new Unicode database and specify a Unicode value of AL32UTF8 or AL16UTF16. For instructions, see the database software documentation.

5. Ensure that the data warehouse uses the same character set as TrueSight Server Automation database or its subset character set. For example, if the TrueSight Server Automation database uses AL32UTF8, the data warehouse must use the same character set or its subset character set. 
6. If you are using non-English databases, such as Asian customers, ensure that you use the NCHAR character set. 
7. Ensure that the setting for the NLS_LENGTH_SEMANTICS parameter for the data warehouse schema is CHAR.

8. Ensure that the global_names parameter for the Oracle instance where TrueSight Server Automation - Data Warehouse is located is set to false. If the global_names parameter is not set to false, the primary site installation fails. After you install the product, you can set this parameter to true if needed.

    To determine the value of the global_names parameter, do the following:

   1. Log in to SQL Plus as sysdba user on the Oracle database instance where the TrueSight Server Automation user is located.

   2. Run the following command:

      SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'global_names';

9. Ensure that the TrueSight Server Automation database user must have Execute permission on the DBMS_JOB and DBMS_LOCK Oracle packages.

    To ensure that the TrueSight Server Automation database user has the Execute permission on these packages, do the following:

   1. Log in to SQL Plus as SYS on the Oracle database instance where the TrueSight Server Automation user is located.

   2. Run the following commands:

      Grant execute on dbms_job to <TrueSightServerAutomationDatabaseUser>

      Grant execute on dbms_lock to <TrueSightServerAutomationDatabaseUser>

      <TrueSightServerAutomationDatabaseUser> is the TrueSight Server Automation database user name.

10. Ensure that the tnsnames.ora file on the data warehouse database server contains the TNS entry of the TrueSight Server Automation database instance and the TNS name matches with the Service name.

    Note: Installing Oracle server on the same server as Oracle client

    An error might occur when the Oracle server is installed on the same server with Oracle client because of an incorrect sequence of the Oracle path in the PATH variable. To avoid this issue, set the client entry before the Oracle server entry in the path variable. For example (Linux), set the PATH variable as follows:

    /data1/oracle/product/11.2.0/client_1/bin;data1/oracle/product/11.2.0/db_1/bin;

11. Navigate to the ../TSSA-DW<version>-external-files/Oracle/create_oracle_tablespaces_and_users.sql script and edit it as follows:

    1. For each user, modify the password as required. Ensure that the passwords:

       • Do not begin with an equal sign (=) character.
       • Do not begin with an integer.
       • Do not contain spaces.

       • Do not use the following special characters:

         • Ampersand (&)
         • Double quotes (" ")
         • Pipe (|)
         • Less than sign (<)
         • Greater than sign (>)
         • Single quotes (' ')
         • Forward slash (/)
         • Semicolon (;)

    2. Modify the Oracle data file paths as required. 

    3. Do not modify the tablespace names. Do not save the following tablespaces on the same physical disk as the BSARA_ETL_STAGE tablespace to prevent an impact on the ETL performance:
       BSARA_DATA, BSARA_INDEX, BSARA_DATA2, BSARA_INDEX2, BSARA_DATA3, and BSARA_INDEX3

    4. Change the value of the MAXSIZE parameter for the following tablespaces according to your sizing requirements. 

       Note: Calculating sizing requirements

       To know the size requirements, see  Sizing tools . The sizing worksheet returns the tablespace size in GB. Before you modify the MAXSIZE parameter in the script, convert it into MB.

    • • BSARA_DATA 
      • BSARA_INDEX
      • BSARA_DATA2 
      • BSARA_INDEX2
      • BSARA_DATA3 
      • BSARA_INDEX3
      • BSARA_ETL_STAGE

      • BSARA_ETL_REPO
        

12. Save the script.

13. Log in to SQL Plus by using a user account with sysdba privileges on the Oracle database instance where you want to create the data warehouse. For example, you can log in using the following commands:

    sqlplus /nolog
    connect sys/<password>@<TNS_ENTRY> as sysdba

14. To create the reports data warehouse tablespaces and users, run the script.

    @create_oracle_tablespaces_and_users.sql;

15. Log out of SQL Plus.

How to video

Creating tablespaces and users on Oracle for TrueSight Server Automation - Data Warehouse (2:31)

 https://youtu.be/eqBi_yzZNWI