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 Operations Management Report Engine.
The BMC TrueSight Operations Management Report Engine installation files contain the create_TrueSightOperationsManagementReporting_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.
The create_TrueSightOperationsManagementReporting_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 Operations Management Report 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 |
Navigate to the Util folder. Copy the AdminDatabaseScripts folder to the computer where you have installed Report Engine database.
Run the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script.
Please Enter the TrueSight Operations Management Reporting Database user name. <Default re>:
Please Enter the password. Example: RE <Default re>:
Do you want to continue. <Default N>
Please Enter the database size. Example: small,medium,large <Default small>:
Type size according to your requirements.
Please Enter the path for datafile. <Default F:\BMCSOFTWARE\DATABASE\ORADATA\REPHASE6\>:
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
Please Enter the sys username:
Please Enter the sys password:
Please Enter the sys db sid:
The following message is displayed:
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
If you have installed Oracle 12.1.0.1.0 with the Pluggable option, run the following steps:
show pdbs
command to display the available pluggable databases and mode of the pluggable databases. alter pluggable database <PluggableDatabase> open;
where <PluggableDatabase> is the name of the pluggable database that you provided during Oracle installation or Pluggable Database creation.alter pluggable database PDBORCL open;
alter session set container=<PluggableDatabase>;
For example, alter session set container=PDBORCL;
Run the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script.
Follow the procedure from Step 4 to 7 in the To create tablespaces and user.
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;
Note
After you run the create_TrueSightOperationsManagementReporting_user_tablespaces.sql script, you may get the ORA-65040 error, see the Oracle database user and tablespaces creation error page, to resolve the error.
1 Comment
Harshal Pandule
Hi Team,
The below permissions are required on the database instance of Oracle for Truesight reporting which are missing in the documentation :
REUNIV user
Query REWRITE
Create Materialized view
Installation fails if above permissions are not provided to REUNIV users, here is the error :
ORA-01950: no privileges on tablespace 'BPPMR_CONFIG'
RE User
GRANT EXECUTE ON "SYS"."DBMS_JOB" TO "RE";
These missing permissions causes warnings and prevents the performance views from creation.
Requesting you to please document these permissions.
Thanks
Harshal