Creating tablespaces and user on Oracle database

After you install Oracle Database, you must create tablespaces and a user with appropriate rights. You must provide this user name when you install BMC TrueSight Report Engine.

The BMC TrueSight Report Engine installation files contain the create_BPPMR_user_tablespaces.sql script that you use to create the tablespaces and the user. To run the script, you need a user with SYSDBA privileges. A successful execution of the script creates the required tablespaces and a user with appropriate rights.

• • To create tablespaces and user
  • To create tablespaces and user on the pluggable database

The create_BPPMR_user_tablespaces.sql script creates two users:

• Schema Owner: When prompted for the Database user name, the script creates the user with the provided name. This user is used to create schema while installing BMC TrueSight Report Engine. 
  The  create_BPPMR_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 does not prompt for user name, however, by default, creates the user with reuniv name with the password for this user is RE#Adm1n. You can change the password after the successful installation. This user is used by BusinessObjects while creating universe connections. 

To create tablespaces and user

1. Navigate to the Util folder. Copy the AdminDatabaseScripts folder to the computer where you have installed BMC TrueSight Report Engine database. 
2. Log on to Oracle database with the sysdba user.

3. Run the create_BPPMR_user_tablespaces.sql script.

    

   Example

   If your script is located in d:\AdminDatabaseScripts, then run the following command:

   @d:\AdminDatabaseScripts\create_BPPMR_user_tablespaces.sql

4. Enter the required data for the script:
   1. Please Enter the BPPM Reporting Database user name. <Default re>:
      Type the database username for the BMC TrueSight Report Engine database.
   2. Please Enter the password. Example: RE <Default re>:
      Type the password for the BMC TrueSight Report Engine database.
   3. Do you want to continue. <Default N>
      Type Y to continue.
   4. Please Enter the BPPMR dbsize. Example: small,medium,large <Default small>:
      Type size according to your requirements.
   5. Please Enter the path for datafile. <DefaultF:\BMCSOFTWARE\DATABASE\ORADATA\REPHASE6\>:
      Type the path for the datafile.

   6. Press Enter key only if you have backed up the spfile.
      
      

      Create a backup of the spfile, and press Enter once completed.

      The system executes the script and the following prompt 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
      

5. Restart the Oracle instance and press Enter. This creates the required user and tablespaces.
6. Enter the required data for the script:
   1. Please Enter the sys username:
      Type the user name.
   2. Please Enter the sys password:
      Type the password.
   3. Please Enter the sys db  sid:
      Type the database instance name.

7. The following message is displayed:

   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_BPPMR.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, run the following steps:

1. Navigate to the Util folder. Copy the AdminDatabaseScripts folder to the computer where you have installed BMC TrueSight Report Engine database.
2. Log on to Oracle 12c Container database as sysdba.
3. 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, you must 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 during Oracle installation or Pluggable Database creation.
   
   For example, alter pluggable database PDBORCL open; 
4. Run the following command to alter the session to use your pluggable database:
   
   alter session set container=<PluggableDatabase>;
   For example, alter session set container=PDBORCL;
5. Run the create_BPPMR_user_tablespaces.sql script.
6. Follow the procedure from Step 4 to 7 in the To create tablespaces and user.
7. 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;