Leave feedback
  • Creating Oracle repositories as SYS or SYSDBA

Write an Article
Wednesday 10 October, 2012
Mike Stopford Mike Stopford OpenText Employee
4 likes 2859 views

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.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lock.htm

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

image

image

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.

image

image

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:

image

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.

image

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

 

Comments (0)

   


Post comment