Creating tablespaces and user on the Oracle database for the Report Engine

After you install the Oracle database, you must create tablespaces and user with appropriate rights for the Report Engine and for TrueSight Smart Reporting. This section provides information about creating tablespaces and user for non-pluggable and pluggable databases.

The TrueSight Operations Management Report Engine installation files contain the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script that you can use to create the tablespaces and the database user for the Report Engine. You must have SYSDBA priveleges to run the script. A successful execution of the script creates the required tablespaces and database user with appropriate rights.

The create_TrueSightOperationsManagementReporting_user_tablespaces.sql script creates the following users:

  • Schema Owner: When prompted for the Database user name, the script creates a user with the name that you provide. This user is used to create the database schema while installing TrueSight Operations ManagementReport Engine. 

    The create_TrueSightOperationsManagementReporting_user_tablespaces.sql script provides the following permissions to this user. 

    Privilege Type

    Role

    Object

    Privilege

    Explicit Object Privilege

     

    DBMS_LOCK

    EXECUTE

    Explicit System Privilege

     

     

    CREATE MATERIALIZED VIEW

    Explicit System Privilege

     

     

    CREATE TABLE

    Explicit System Privilege

     

     

    CREATE VIEW

    Explicit System Privilege

     

     

    QUERY REWRITE

    Explicit System Privilege

     

     

    UNLIMITED TABLESPACE

    System Privilege From Role

    CONNECT

     

    CREATE SESSION

    System Privilege From Role

    RESOURCE

     

    CREATE CLUSTER

    System Privilege From Role

    RESOURCE

     

    CREATE INDEXTYPE

    System Privilege From Role

    RESOURCE

     

    CREATE OPERATOR

    System Privilege From Role

    RESOURCE

     

    CREATE PROCEDURE

    System Privilege From Role

    RESOURCE

     

    CREATE SEQUENCE

    System Privilege From Role

    RESOURCE

     

    CREATE TABLE

    System Privilege From Role

    RESOURCE

     

    CREATE TRIGGER

    System Privilege From Role

    RESOURCE

     

    CREATE TYPE

  • Read-only User: The script, by default, creates the read-only user with reuniv name, and the RE#Adm1n password. 

To create Oracle tablespaces and user

Do the following to create tablespaces and database user by using the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script:

  1. On the computer where the Oracle database is installed, navigate to the Util folder.
  2. If you are using Oracle version 18c or 19c, do the following:
    1. Copy the AdminDatabaseScripts folder that is attached to this knowledge article Open link .
    2. Navigate to <TrueSight Report Engine install directory>\Reports\Util\ and replace the AdminDatabaseScripts folder with the folder that you just copied.
  3. If you are using Oracle version 12c, copy the <TrueSight Report Engine install directory>\Reports\Util\AdminDatabaseScripts folder and paste it on the computer where you have installed the Reporting database. 
  4. Log on to the Oracle database with the SYSDBA user.
  5. Run the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script.
    For example, if your script is located in the d:\AdminDatabaseScripts folder, run the following command:
    @d:\AdminDatabaseScripts\create_TrueSightOperationsManagementReporting_user_tablespaces.sql

  6. When prompted, type the following data:
    • User name and password for the Reporting database.

    • The Reporting database size. You can type either small, medium, or large. The default size is small.

    • The path for the datafile.

  7. Create a backup of the spfile file, and then press Enter on the script. The system executes the script and the following message appears:

    Setting the system parameters completed.
    ****************************************************************************
    Please re-start the database instance ? If yes, please press Enter key to continue.
    ****************************************************************************
    Press Enter key only if you have re-started the db instance.
    Press enter once you restart oracle instance
  8. Restart the Oracle instance and press Enter. This creates the required user and tablespaces.

  9. When prompted, enter the user name, password, and the database instance name. The following message appears:

    Please set the values of memory_max_target and memory_target to the maximum memory
    you can allocate for your oracle instance.
    memory_max_target and memory_target should be minimum 50% of Memory Available
    for one Instance of oracle (Assuming available Memory is 4GB)
    e.g. alter system set memory_max_target=2000M scope=spfile
    e.g. alter system set memory_target=2000M scope=spfile
    Set following sga parameter to 0, if ASMM, or AMM set to ON.(By default oracle 11g using ASMM, or AMM set to ON)
    e.g. alter system set sga_target=0 scope=spfile
    e.g alter system set pga_aggregate_target=0 scope=spfile
    Please save the file set_system_parameter_oracle_TrueSightOperationsManagementReporting.log generated.
    ********************************************************************************
    Resize REDO Logs file Maximum to 500M for all redo group (for oracle, Default size is 50M)
    ********************************************************************************
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

To create Oracle tablespaces and user on the pluggable database

If you have installed Oracle 12.1.0.1.0 with the Pluggable option, do the following:

  1. On the computer where the Oracle database is installed, navigate to the Util folder.
  2. Copy the AdminDatabaseScripts folder and paste it on the computer where you have installed the Report Engine database.
  3. Log on to the Oracle 12c Container database as the SYSDBA user.
  4. Run the show pdbs command to display the available pluggable databases and mode of the pluggable databases. 
    Ensure that the pluggable connection has READ WRITE as the open mode. If it has the Mounted option, run the following command to open the connection:

    alter pluggable database <PluggableDatabase> open;

    where  <PluggableDatabase> is the name of the pluggable database that you provided while installing Oracle or while creating the pluggable database.
    For example, run the following command:

    alter pluggable database PDBORCL open;
  5. Run the following command to alter the session to use your pluggable database:

    alter session set container=<PluggableDatabase>;

    where  <PluggableDatabase> is the name of the pluggable database that you provided while installing Oracle or while creating the pluggable database.
    For example, run the following command:

    alter session set container=PDBORCL;
  6. Run the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script.

  7. When prompted, type the following data:
    • User name and password for the Reporting database.

    • The Reporting database size. You can type either small, medium, or large. The default size is small.

    • The path for the datafile.

  8. Create a backup of the spfile file, and then press Enter on the script. The system executes the script and the following message appears:

    Setting the system parameters completed.
    ****************************************************************************
    Please re-start the database instance ? If yes, please press Enter key to continue.
    ****************************************************************************
    Press Enter key only if you have re-started the db instance.
    Press enter once you restart oracle instance
  9. Restart the Oracle instance and press Enter. This creates the required user and tablespaces.

  10. When prompted, type the user name, password, and the database instance name. The following message appears:

    Please set the values of memory_max_target and memory_target to the maximum memory
    you can allocate for your oracle instance.
    memory_max_target and memory_target should be minimum 50% of Memory Available
    for one Instance of oracle (Assuming available Memory is 4GB)
    e.g. alter system set memory_max_target=2000M scope=spfile
    e.g. alter system set memory_target=2000M scope=spfile
    Set following sga parameter to 0, if ASMM, or AMM set to ON.(By default oracle 11g using ASMM, or AMM set to ON)
    e.g. alter system set sga_target=0 scope=spfile
    e.g alter system set pga_aggregate_target=0 scope=spfile
    Please save the file set_system_parameter_oracle_TrueSightOperationsManagementReporting.log generated.
    ********************************************************************************
    Resize REDO Logs file Maximum to 500M for all redo group (for oracle, Default size is 50M)
    ********************************************************************************
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
  11. When you restart the Oracle instance, the pluggable connection changes to the Mounted option. Run the following command to open the connection:

    alter pluggable database <PluggableDatabase> open;

    where  <PluggableDatabase> is the name of the pluggable database that you provided while installing Oracle or while creating the pluggable database.


Note: ORA error

After you run the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script you might get the following error:

ORA-65040: operation not allowed from within a pluggable database

You can ignore this error because the Oracle system parameters cannot be set on a pluggable database. These parameters must be set on the container database.

Was this page helpful? Yes No Submitting... Thank you

Comments