Oracle and SQL Server DB prerequisites when creating a new Service Management installation
Article ID: 217962
Updated On:
Products
Issue/Introduction
Doing a new Install of Service Management, are there any pre-requisite steps needed to setup the backend database server
Environment
Release : 17.3
Component : SVC DESK UPGRADE
Resolution
When installing a fresh installation of Service Desk, there should not be any new components defined in the backend database. For instance, in Oracle, one should not need to create any tablespaces or users in advance. Any such users and tablespaces will be created by the MDB creation script by virtue of execution by the SYS user. The same also applies for SQL Server as well.
Additional Information
In situations where backend elements such as the tablespaces or the mdbadmin user are created before the SDM install attempt is made, it is not unheard of for the installation attempt to fail with errors such as this in the install_mdb.log
CREATE OR REPLACE FORCE
VIEW TAU_MDB ( MDBMAJORVERSION, MDBMINORVERSION, BUILDNUMBER, RELEASEDATE, INSTALLDATE ) AS
SELECT mdb.mdbmajorversion, mdb.mdbminorversion, mdb.buildnumber, mdb.releasedate, mdb.installdate
FROM mdb
01-01,14:24:14 DEBUG - error occurred during batching: ORA-01031: insufficient privileges
Another example of such errors that can be seen in the install_mdb.log:
01-01,16:32:51 INFO - GRANT SELECT ON TAU_MDB TO public
Update sql failed: GRANT SELECT ON TAU_MDB TO public
01-01,16:32:51 DEBUG - error occurred during batching: ORA-04063: view "MDBADMIN.TAU_MDB" has errors
01-01,16:32:51 INFO - tau_mdb[public].xml is added to the retry queue.
01-01,16:32:51 ERROR - MDBTools_0304E - Possible dependency error processing permission 'tau_mdb[public].xml'; error details follow:
01-01,16:32:51 ERROR - error occurred during batching: ORA-04063: view "MDBADMIN.TAU_MDB" has errors
In a new instance of Service Desk being installed with the backend Oracle DB, the mdbadmin user will be created by the install script.
SQL> CREATE USER mdbadmin PROFILE DEFAULT IDENTIFIED BY "&1" DEFAULT TABLESPACE &2 QUOTA UNLIMITED ON &2 ACCOUNT UNLOCK;
In the above scenario, examining the install_mdb.log shows that the mdbadmin user is already present, despite the installation being a new setup without any prior SDM materials deployed.
new 1: SELECT COUNT(*) FROM SYS.ALL_USERS WHERE USERNAME = UPPER('mdbadmin')
COUNT(*)
----------
1
The mdb script creates the above mdbadmin user within Oracle and also modifies the given user's permissions. What should happen in the install_mdb.log is that a check of the the following entries for the mdbadmin user and the MDB_DATA and MDB_INDEX tablespaces should all come back as 0. :
Checking if mdbadmin exists...
new 1: SELECT COUNT(*) FROM SYS.ALL_USERS WHERE USERNAME = UPPER('mdbadmin')
COUNT(*)
----------
0
Checking if "MDB_DATA" exists...
new 1: SELECT COUNT(*) FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = UPPER('MDB_DATA')
COUNT(*)
----------
0
Checking if "MDB_INDEX" exists...
new 1: SELECT COUNT(*) FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = UPPER('MDB_INDEX')
COUNT(*)
----------
0
As they are not present, the above tablespaces and user are then created by the MDB install script. For the mdbadmin user, the following is a sampling of actions that are taken to create/modify the mdbadmin user by the MDB install script (not all actions are depicted below):
CREATE USER mdbadmin PROFILE DEFAULT IDENTIFIED BY "&1" DEFAULT TABLESPACE &2 QUOTA UNLIMITED ON &2 ACCOUNT UNLOCK;
GRANT CREATE VIEW TO mdbadmin;
GRANT "CONNECT" TO mdbadmin;
GRANT "EXECUTE_CATALOG_ROLE" TO mdbadmin;
ALTER USER MDBADMIN QUOTA UNLIMITED ON MDB_INDEX
Note: It is not recommended to try and create the mdbadmin user in the backend DB Server manually before installing Service Management as there are multiple actions taken to generate the mdbadmin user by the MDB install script, and a manual process to reproduce the actions of the MDB script may be error prone.
Feedback
