topic Re: Create Enteprise GDB in Oracle 12c in Geodatabase Questions

Create Enteprise GDB in Oracle 12c

DiegoLlamas — Wed, 12 Sep 2018 20:25:53 GMT

Hello,

I am super new using Oracle and I am having some problems creating a database in oracle. I have installed Oracle (with no database created) I also set the path environment variable in windows server and I am able to access other Oracle DBs already created.

I do not have an oracle database created in this new server Do I need to have one so I can use that name to access it? because I do not have any but I am using the IP:port/the name of the database I want to give to it, is this correct?

This tool is going to create the sys user automatically?

I am getting this error

Is there something I need to do?

Thanks,

Diego Llamas

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Wed, 12 Sep 2018 20:34:29 GMT

Yes, create the DB on the Oracle instance before you use that GP tool. You will need the SYS user PW for the EGDB creation.

Then you can use EZConnect for the connection string; i.e ServerName/SID SID = Oracle DB name

Here are the options: Create a geodatabase in Oracle—Help | ArcGIS Desktop

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Tue, 18 Sep 2018 12:45:33 GMT

Hello, George Thompson's Blogt

I was able to create the database in Oracle C with the listener, database but when I run the create EGB tool, it gives me an error for user, so, my question is, do I need to create the SDE user also in Oracle, before running the tool?

Regards,

Diego Llamas

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Tue, 18 Sep 2018 12:49:40 GMT

Hi Diego - You can create the SDE user in advance. Just follow the documentation above and look in the "The Oracle administrator configures the database; the geodatabase administrator creates the geodatabase" Section. It will give you the information to run the correct tools.

Re: Create Enteprise GDB in Oracle 12c

Asrujit_SenGupta — Wed, 19 Sep 2018 04:02:25 GMT

What error do you receive related to the user?

The tool is capable of creating the SDE user and SDE user's tablespace for you. So it is not necessary to create them beforehand.

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Thu, 01 Nov 2018 19:33:32 GMT

Hello George Thompson's Blog After your help I was able to create the database and create roles and user gisadmin, giseditor and gisviewer, the users has their privileges set. So I started to copy paste data sets from an old database to the new one. In the middle of the process of migrating the data I remember that I did not configure the ST_Geometry type to the new database, so I copied the st_shapelib.dll from server to the database server, modify the extproc.ora file as shown in Configure the extproc to access ST_Geometry in Oracle—Help | ArcGIS Desktop run this on the SQL Developer

SELECT file_spec FROM user_libraries WHERE library_name = 'ST_SHAPELIB';
CREATE OR REPLACE LIBRARY ST_SHAPELIB AS 'C:\Oracle_ST_GeometryLIB\st_shapelib.dll'; ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE REUSE SETTINGS;

and run the create spatial type geoprocessing tool.

After I did that, I tried to copy and paste the rest of the data sets and I got this erros

If I add the existed data from the database to arcmap I got this error.

If I try to create a new feature class

If I try to delete something from the database, it erase but once refreshed it come again.

I run one procedure in one of your post and I got this

Any suggestion, What did I do wrong?

Regards,

DLL

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Thu, 01 Nov 2018 19:37:23 GMT

I also checked George Thompson's Blog‌ the privileges for sde user and it has all of them suggested by esri.

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Thu, 01 Nov 2018 19:41:30 GMT

At the beginning you said that you ran the Create/Enable Enterprise Geodatabase tool, correct? If you did that then you do NOT need to run the Create Spatial type tool. That tool is if you want to enable the ST_Geometry objects on a non-geodatabase.

Since this is a Windows Oracle machine, do you have the latest Visual Studio VC++ 2017 installed?

You may need to re-update the library path (double check to make sure it is correct). Then you can recompile the SDE schema.

What did you update the EXTPROC.ora file to; "ANY" or the file path?

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Thu, 01 Nov 2018 20:17:55 GMT

Hello,

Yes, I use Enable Enterprise geodatabase tool, so If I run the create spatial type tool it does not affect my database right, if we already had the ST because I used the create database, create spatial should not affect i assume.

I installed VC_REDSTTx64 you mention. this was a new database, so I did not have any st_shapelib.dll from before, i copied the 10.6 server version file from the server machine to the db machine for the fist time, i did not have one before, so do i needed to use library?

I put the last part.

I will re run the SQL statement after I installed VC++

I do not think I did this FAQ: Can the execute privilege be removed from public on Oracle packages? is this necessary

It this necessary? what user need to have those public packages?

REgards,

DLL

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Fri, 02 Nov 2018 11:50:23 GMT

I will re run the SQL statement after I installed VC++
I do not think I did this FAQ: Can the execute privilege be removed from public on Oracle packages? is this necessary

Nope not necessary.

For the EXTPROC try putting the library in the %ORACLE_HOME%\bin folder and update path?

I am not sure if running the create spatial type tool would cause any issues after the geodatabase is already created, it is possible.

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Fri, 02 Nov 2018 13:52:59 GMT

Hello George,

I got an error in my database server when I run %ORACLE_HOME%\bin so I look for it and I put it here

Do you have the specific path where I need to copy the library.

Regards,

DLL

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Fri, 02 Nov 2018 13:56:16 GMT

That location is ok. Be sure to update the path for the user library and recompile the SDE schema.

Then run the SQL: select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

To see if that fixes it.

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Fri, 02 Nov 2018 14:47:54 GMT

Hello George,

I run the recompile

But after running select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

My situation with the database is serious.

Thanks for your help!

Diego

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Fri, 02 Nov 2018 14:50:16 GMT

Change the EXPTROC to "ANY" then retry.

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Fri, 02 Nov 2018 15:23:19 GMT

Nop, after change to any and recompile, I had the exact same error.

Do you know how can I find out if the database is corrupted or something of I did wrong the ST compatibility.

If I recreate the database and use create new enterprise geodatabase, i just need to modify the extproc file, copy the library to bin folder, and run this command

SELECT file_spec FROM user_libraries WHERE library_name = 'ST_SHAPELIB';
CREATE OR REPLACE LIBRARY ST_SHAPELIB AS 'E:\app\DLLAMAS\product\12.1.0\client_1\bin\st_shapelib.dll';
ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE REUSE SETTINGS;

are those the steps? all this before I add any data or this should work with data in the database?

Regards,

DLL

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Fri, 02 Nov 2018 16:00:59 GMT

I would update SET EXTPROC_DLLS=ANY and that is all.

You may have corrupted the GDB install with the create spatial type step. I would create a new empty Oracle DB and then just run the Create Enterprise GDB. You will need to update the library path for the new GDB and that should take care of it. No data is needed in the GDB for this part.

Re: Create Enteprise GDB in Oracle 12c

DiegoLlamas — Tue, 08 Jan 2019 13:42:03 GMT

Hello George,

Happy new year. I was able to get up and running the new Oracle geodatabase. Now I am re configuring DBTune so I can have the same configuration keywords in the new GDB.

I have seen several changes. For example in version 10.4 I have this: BND_STORAGE "PCTFREE 0 INITRANS 4 STORAGE ( INITIAL 409600) "

and in version 10.6 I have this :BND_STORAGE "PCTFREE 0 INITRANS 4" this in default section.

So what I am doing is copy and paste in the new version so then I can update dbtune in new geodatabase. I am checking this page to confirm that it is compatible with version 10.6 Oracle configuration parameters—Help | ArcGIS Desktop

so my question is, is it ok to add the STORAGE ( INITIAL 409600) for this new version, right?

Regards,

DLL

Re: Create Enteprise GDB in Oracle 12c

George_Thompson — Tue, 08 Jan 2019 15:07:42 GMT

I would say yes. But most people are not modifying the DBTUNE since the RDBMS's are "smarter" and more efficient now than before.

Be sure to review the parameters based on Oracle documentation also for the storage. I would also be sure to have a backup of the DBTUNE before you modify it.

Also note that it will NOT change the storage configuration of the current data, only new data that is loaded via that KEYWORD.