sdesetup problem: Oracle multiple geodatabases in one oracle instance

[GregKlafki]

I have an existing geodatabse(9.3.1) in Oracle10gR2 and I want to create a user schema geodatabase in the same instance. I have read the PDF paper, set all the user schema permissions, confirmed the permissions, set the service number in the windows service file and the sde service file.

When I try to create the user schema instance using a direct connect I get the message:
Install/Upgrade/Delete has to be performed by instance schema owner

Here is the command I run:

sdesetup -o install -d ORACLE10G -i sde:ORACLE10g:zoes:imagery -u imagery -p password@zoes


Regards

[ShawnThorne]

Hi Greg,

You need to specify a port number when creating a multiple geodatabase - as outlined in the documentation below.

~~~~~~~~

http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=Using_multiple_geodatabases_in_Oracle

You create user geodatabases by running the sdesetup command with the install operation. For the instance (the �??i option), you must specify the port number and the schema. For the user name and password, these must be the user name and password of the user who will own the geodatabase.

Be aware that even if you are using a direct connection to the master SDE geodatabase, you must provide a valid service number with the �??i option. If no service was created for the master SDE geodatabase, one must be created (though it doesn't have to be started) on Windows. This service must be added to the Windows services file. On UNIX or Linux, the service must be added to the services.sde file and the /etc/service file.

NOTE: The user must be given the same privileges you provide for the SDE user while installing or upgrading the SDE geodatabase.

~~~~~~~~


Here's an example :

sdesetup -o install -d ORACLE10G -i 5151:imagery -u imagery -p password@zoes -N


Hope this helps,

-Shawn

[GregKlafki]

Hi Shawn

I tried that option and was getting a database permission error.
ESRI ArcSDE Server Setup Utility Fri Sep 10 08:25:30 2010
----------------------------------------------------------------
Creating ArcSde schema.....
To install your ArcSDE for Oracle
service, you will also have to grant the following
additional permissions to the sde user to accomplish
the install:

CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TRIGGER
CREATE TYPE
CREATE INDEXTYPE
CREATE LIBRARY
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
ADMINISTER DATABASE TRIGGER
CREATE OPERATOR
Error: Insufficient permissions (-25).
Error: SDE release install not completed.
Check SDEHOME\etc\sde_setup.log for more details.


Our DBA has granted all the permissions except ADMINISTER DATABASE TRIGGER as the documentaion states that it is not required to create a user schema geodatabase.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Permissions for user-schema geodatabase owners
To own and administer a user-schema geodatabase, the schema owner must have nearly all the same
permissions as the sde user. Permissions to create, upgrade, and maintain a user-schema geodatabase are
listed in the following sections:
Permissions needed to create a geodatabase
These are the permissions are that required to create a geodatabase in a user's schema. The last seven
permissions can be revoked after the geodatabase is created.
â?¢ CREATE SESSION
â?¢ CREATE TABLE
â?¢ CREATE TRIGGER
â?¢ CREATE SEQUENCE
â?¢ CREATE PROCEDURE
â?¢ CREATE OPERATOR
â?¢ CREATE INDEXTYPE
â?¢ CREATE LIBRARY
â?¢ CREATE PUBLIC SYNONYM
â?¢ DROP PUBLIC SYNONYM
â?¢ CREATE TYPE
â?¢ CREATE VIEW
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have done client side tracing and have seen the sql being sent for permission check - I am not sure if it does a different check for a user schema geodatabase vs the master sde geodatabase. I will work with our DBA to see what SQL is being sent and see if adding the extra permission allows the user schema geodatabase to be created even if the permission is not used.

Thanks for your help

Greg

[GregKlafki]

Problem solved!

I had the DBA add the ADMINISTER DATABASE TRIGGER permission to the schema owner and the install proceeded. I now have the sde tables in the user schema and can do a direct connect though ArcCatalog.

So the documention is misleading - you do need to grant the ADMINISTER DATABASE TRIGGER permission, however, I supect that it is not used by install process. There probably is only one permsision check regardless of master SDE or user schema SDE.

The other possiblity is I have a bad install 😉

A quick check with this query: select instance_name from sde.instances seems to indicate that it has worked.

INSTANCE_NAME
IMAGERY
SDE