Creating tablespaces and user on the Oracle database for Report Engine

After you install the Oracle database, you must create tablespaces and a database user with appropriate rights. You must provide this user name when you install the TrueSight Operations Management Report Engine.

If you plan to use TrueSight Smart Reporting, you must also create tablespaces and a database user for TrueSight Smart Reporting.

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