Creating the master ArcSDE geodatabase in Oracle (Maritime Charting)

This workflow provides steps for creating the master ArcSDE geodatabase that keeps track of the user-schema geodatabases in Oracle.

Creating the geodatabase

The Create Enterprise Geodatabase geoprocessing tool does the following:

• Creates the geodatabase administrator user and default tablespace (if not created manually)
• Grants the geodatabase administrator privileges to create a geodatabase and drop database connections (if not granted manually)
• Populates geodatabase system tables and stored procedures in the database
• Authorizes ArcSDE using the ArcGIS Server Enterprise license file

To run the Create Enterprise Geodatabase tool, you must install an Oracle client on the computer where ArcGIS for Desktop is installed. You also need a valid ArcGIS for Server authorization file (.ecp).

Steps:

1. Start ArcCatalog or ArcMap.
2. Open the Create Enterprise Geodatabase geoprocessing tool.

   Either search for the tool in the Search window, or open the tool's dialog box directly from the Geodatabase Administration toolset of the Database Management toolbox.

3. Choose Oracle from the Database Platform drop-down list.
4. Provide the information required to connect to the Oracle instance.
   • If you have already configured the Oracle client to use a TNS network alias, type the Net service alias in the Instance text box.
   • If you have not configured the Oracle client's network alias, provide the Oracle Easy Connection string in the Instance text box.

   See Database connections in ArcGIS for Desktop for a list of possible values.

5. Type the sys user's password in the Database Administrator Password text box.
6. Type the name of the geodatabase administrator in the Geodatabase Administrator text box.

   For a master ArcSDE geodatabase, the geodatabase administrator must be named sde. If a user with that name does not already exist in the database, the tool creates it and grants it the privileges necessary to create a geodatabase. If the user already exists, the required privileges are granted to it.

7. Type a password for the geodatabase administrator in the Geodatabase Administrator Password text box.

   If the geodatabase administrator you specified already exists in the database, be sure to type the correct password for the existing user; this tool will not change the password. If you are creating an sde user, the password you type is used when creating the sde user.

8. Specify a tablespace for the geodatabase administrator.
   • If you have a preconfigured tablespace to be used for the geodatabase repository, type its name in the Tablespace Name text box.
   • If you want the tool to create a tablespace for you, type a valid name in the Tablespace Name text box and the tool creates a 400 MB tablespace in the Oracle default location. If you do not type a tablespace name, SDE_TBS tablespace is created and set as the default tablespace for the sde user.
9. To specify an Authorization File, browse to and choose the keycodes file that was created when you authorized ArcGIS for Server Enterprise.

   This file is written to \\Program Files\ESRI\License<release#>\sysgen folder on Windows servers and /arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License<release#>/sysgen on Linux. If you have not already done so, authorize ArcGIS for Server now to create this file.

10. Click OK to run the tool.

Customizing ArcSDE initialization parameters

If users will connect to the enterprise geodatabase using an ArcSDE service, you need to configure the TCPKEEPALIVE ArcSDE initialization parameter to 1 (TRUE) for the master geodatabase and each user-schema geodatabase.

Note:

Changes do not take effect until the ArcSDE service has been restarted.

You may update these parameters directly in the SERVER_CONFIG table. Execute the following commands as the sys user in SQL*Plus.

update sde.server_config set num_prop_value = 1 where prop_name = 'TCPKEEPALIVE';
commit;

Alternately, the following command can be run at the command prompt of a machine where the ArcSDE administration command line tools are installed.

sdeconfig -o alter -v TCPKEEPALIVE=TRUE -i <service> -u sde -p <sde_password>

Validating database objects

It is advisable to verify the SDE schema's objects in your database. Ensure that all the ArcSDE schema's procedures, packages, and triggers are valid and all the related constraints are enabled. Execute the following query as the sys user in SQL*Plus.

select * from dba_objects where status != 'VALID';

If any objects owned by the ArcSDE schema are reported as invalid, you need to recompile them using the following SQL*Plus command as sys user.

Note:

The compile_all => FALSE option compiles only invalid objects, whereas TRUE, which is the default, will compile all objects regardless of status.

exec dbms_utility.compile_schema( 'SDE', compile_all =>  FALSE );

Creating spatial database connection files

Create spatial database connection files for the ArcSDE user (master ArcSDE geodatabase owner).

Learn more about geodatabase connections in ArcGIS for Desktop

Steps:

1. Start ArcCatalog or ArcMap.
2. Double-click the Database Connections folder in the Catalog tree to expand it.
3. Double-click Add Database Connection.

   The Database Connection dialog box opens.

4. Choose Oracle from the Database Platform drop-down list.
5. Provide the appropriate connection information as described in Database connections in ArcGIS for Desktop.

You can enter the Oracle Net service alias if it has been configured, or the Oracle Easy Connection string.

6. Click the Authentication Type drop-down arrow and choose Database authentication.
7. Type the ArcSDE user name and password.
8. Click OK.

For the convenience of all other users, this connection file can be moved to a central location from where it can be accessed.