In this article I will cover why Streamserve requires SYS (or SYSDBA) to create Streamserve repositories.
When creating repositories on Oracle the Control Center documents state that this must be run as a database administration user or SYSDBA. This applies for the Control Center “Create Database” operation as the SYS user, and when running “load as sys.sql” .
The main reason we advise to run this as sys is because of the GRANT EXECUTE on sys.dbms_lock statement. Because dbms_lock is owned by SYS, no other user can access it unless granted the SYSDBA privilege. Here are some details regarding DBMS_LOCK package.
For Streamserve at a high level this better handles transactional DB locks for resources STRS uses during processing.
So for example, if you grant user opentext sysdba and then try to create the runtime schema via Control Center you will get ORA-01031: insufficient privileges
And via control center if you try to use opentext as sysdba as the user name you will get invalid username as password. Because STRS CC does not interpret the log on string like SQLPLUS does.
Therefore the only option via CC is to create with SYS because SYS connects as sysdba by default therefore has access to DBMS_LOCK package.
If you run the “load_as_sys.sql” as opentext (without sysdba) via SQLPLUS you will get something like the below:
This is because opentext user has no awareness of a package that is not available to it. Therefore you need to connect as opentext as sysdba via SQLPLUS.
As you can see from the above, this is much cleaner and what you should see after repository is created. You should always check the log generated by STRS when running these scripts to confirm there are no errors and repositories were created correctly.
This was tested using Oracle 10g and 11g Sp5