Month: October 2013

ORA-01031: insufficient privileges while creating MATERIALIZED VIEW

arcsdegeoLeave a comment

One of the things i have found by coincidence in Oracle 11g is when you try to create a materialized view by a non-schema user (even if he has a ‘DBA‘ privilege), you will face the following error:

ERROR at line 1: ORA-01031: insufficient privileges

 

although the user is powerful but he could not create the materialized view under any schema (in the following example the schema name is “sico“).

SQL> CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

you can create a materialized view using the schema owner after granting him the “create table” privilege.

 

check it and see !!!

I personally faced this in Oracle 11gR2.

 

Table was found in GDB_ObjectClasses, but does not exist in the database

arcsdegeoLeave a comment

Table (SICO.T_DUMMY) was found in GDB_ObjectClasses, but does not exist in the database.  Skipping.

DBMS table not found [ORA-04043: object SICO.T_DUMMY does not exist]

This “warning” message is being displayed in the log while running the “pre-requisite” upgrade option in ArcCatalog.

You can still proceed with the upgrade since it’s an “orphan issue” . Remark: warning message are displayed in “Green” color in ArcGIS Desktop.

Solution:

Through SQLPLUS or other database management tools (TOAD, Sql Developer), re-create the table using sql command.

In our example case: CREATE TABLE SICO.T_DUMMY ( OBJECTID INTEGER NOT NULL );

Then connect in ArcGIS Desktop (ArcCatalog) using the schema owner where the table is stored in (in our example schema name is “SICO”).

You will find the table T_DUMMY , then delete it. This will clean SDE metadata.

Reference Links:

http://support.esri.com/en/knowledgebase/techarticles/detail/34050

http://forums.arcgis.com/threads/72827-Clean-up-orphaned-records-in-the-SDE-repository-tables

ORA-02429: cannot drop index used for enforcement of unique/primary key

arcsdegeoLeave a comment

While dropping the tablespace the following error occurred:

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> DROP TABLESPACE TS_XXX_DATA_01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE TS_XXX_DATA_01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02429: cannot drop index used for enforcement of unique/primary key

  Solution:

 

Use the command:

 

DROP TABLESPACE TS_XXX_DATA_01 INCLUDING CONTENTS AND DATAFILES cascade constraints;

 If you still face issues:

Use this query to find out where are the constraint exists within your tablespace:

 select * from dba_segments where tablespace_name= ‘TS_XXX_DATA_01’;

Underlying DBMS error[ORA-00001: unique constraint] in ArcSDE Geodatabase

arcsdegeoLeave a comment

usually you will face the following error: Underlying DBMS error[ORA-00001: unique constraint] while compressing the ArcSDE Geodatabase. to know more information about the constraint index that is causing the issue, check the ArcSDE log file located in your $SDEHOME/etc directory.

once you identify the constraint number and feature class or table, use the ArcSDE gdbrepair utility to fix this issue.

command syntax:

sdegdbrepair -o repair_tables -d <{ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}>
[-r versioned table name | file=<table_list>] [-V {ALL | NONE | <version_name>} | STATE:<state_id>]
[-H <home_directory>] [-u <ArcSDE_admin_user>] [-p <ArcSDE_admin_password>]
[-D <database_name>] [-s <server_name>] [-i <master_geodatabase_service>:<user_schema>] [-N] [-q]

Remarks:

its better to use the flag -V ALL

also, STATE:state id is the constraint number shown in the error.

ESRI Link References:

http://support.esri.com/fr/knowledgebase/techarticles/detail/35298

http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/support_files/serveradmin/sdegdbrepair.htm

 

Deleting user schema geodatabase

arcsdegeoLeave a comment

To delete user schema geodatabase, you can use the following command:

sdesetup -o delete -d ORACLE11G -u oracle_child_geodatabase_user -p password -i 5151:oracle_geodata_user

important remark: you need to specify the port and user schema geodatabase schema.

user schema geodatabase has been configured in Enterprise Geodatabase as a child Geodatabase. The Master Geodatabase is “sde” schema. ESRI is providing this architecture for organizations that require limited number of customers needs, also for data isolation purposes (each child geodatabase will have its own super account similar to “sde” where system tables are stored in it). Not only that, this architecture will also save cost since you will only need one License File for your complete geodatabase.

Oracle Security Patch OCT 2013 ( Applying 11.2.0.3.8 on IBM AIX)

arcsdegeoLeave a comment

Oracle fourth quarter security patch (OCT 2013) has been released.

while applying the oracle security patch on the following current setup environment:

Oracle Database 11g (11.2.0.3.7)

IBM AIX 6.1

the following errors are being thrown while applying the patch:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Yes

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = ‘/oraclex/oradb/product/11.2.0.3‘)

Verifying the update…

There are 1 copy files under ORACLE_HOME that are not patched.

Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.

ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’

Restoring “/oraclex/oradb/product/11.2.0.3” to the state prior to running NApply…

OPatch failed to restore the files from backup area. Not running “make”.

NApply was not able to restore the home.  Please invoke the following scripts:

– restore.[sh,bat]

– make.txt (Unix only)

to restore the ORACLE_HOME.  They are located under

“/oraclex/oradb/product/11.2.0.3/.patch_storage/NApply/2013-10-21_06-54-24AM”

UtilSession failed: ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’Log file location: /oraclex/oradb/product/11.2.0.3/cfgtoollogs/opatch/opatch2013-10-21_06-54-24AM_1.logOPatch failed with error code 73

The Solution of this problem is the following:

before applying the Oracle Security Patch, Run “slibclean” on your Unix IBM AIX server.

Also make sure that no processes are still running in the database such as “listener”.

command used: /usr/sbin/slibclean

Refrence Link for slibclean command: http://publib.boulder.ibm.com/infocenter/pseries/v5r3/index.jsp?topic=/com.ibm.aix.cmds/doc/aixcmds5/slibclean.htm

How to Create ArcSDE Connection – Application Server (ArcGIS 10.1)

arcsdegeoLeave a comment

Geodata Master

starting with ArcGIS 10.1 , the default connection to the geodatabase is (Direct Connection), which means directly connecting to the database without the need to connect to ArcSDE as an application server anymore (3-tier architecture). The ArcSDE libraries are already part of the ArcGIS Desktop software which means you can directly connect from Desktop the Enterprise Geodatabase (2-tier architecture).

I think this why ESRI are gradually shifting the name from ArcSDE Geodatabase, to Enterprise Geodatabase.

as you may know there are 3 types of Geodatabases provided by ESRI: File Geodatabase, Personal Geodatabase, and Enterprise Geodatabase.

To Create an ArcSDE Application Server Connection in ArcGIS Desktop 10.1:

1.in the search tool in ArcCatalog, search the word “ArcSDE“.

2.choose ‘Create ArcSDE Connection File (Data Management)(Tool)‘.

Image

3.you will find a GUI interface that have different fields to be filled.

for example:

ArcSDE Connection File Location:

View original post 71 more words

How to Create ArcSDE Connection – Application Server (ArcGIS 10.1)

arcsdegeo1 Comment

starting with ArcGIS 10.1 , the default connection to the geodatabase is (Direct Connection), which means directly connecting to the database without the need to connect to ArcSDE as an application server anymore (3-tier architecture). The ArcSDE libraries are already part of the ArcGIS Desktop software which means you can directly connect from Desktop the Enterprise Geodatabase (2-tier architecture).

I think this why ESRI are gradually shifting the name from ArcSDE Geodatabase, to Enterprise Geodatabase.

as you may know there are 3 types of Geodatabases provided by ESRI: File Geodatabase, Personal Geodatabase, and Enterprise Geodatabase.

To Create an ArcSDE Application Server Connection in ArcGIS Desktop 10.1:

1.in the search tool in ArcCatalog, search the word “ArcSDE“.

2.choose ‘Create ArcSDE Connection File (Data Management)(Tool)‘.

Image

3.you will find a GUI interface that have different fields to be filled.

for example:

ArcSDE Connection File Location: is the file directory path on your local PC where ArcCatalog 10.1 SDE Connection Files are stored in.

ArcSDE Connection File Name: a name your propose for your geodatabase connection, usually ends with ‘.sde’ extension.

Server: is the server name where your Database is installed on.

Service: is your ArcSDE Application Server Port Number.

Username: is the Oracle Account name (already exists in your DBMS).
Password: password of your account.

Image

 

ArcSDE Performance Troubleshooting (SDEINTERCEPT)

arcsdegeoLeave a comment

if you are facing connection performance issue for your ArcSDE Geodatabase, there is a mechanism to perform troubleshooting using SDEINTERCEPT.

To configure the SDEINTERCEPT either you set it up on the server side if you are using ArcSDE as an application server. If you are using Direct-Connection then use the SDEINTERCEPT on the client machine where ArcGIS Desktop is installed.

the following example, shows you to set the environment variables on UNIX environment (C SHELL), for an ArcSDE application server Architecture (3-tier):

setenv SDEINTERCEPT crwtf
setenv SDEINTERCEPTLOC /apps/arcsde/

For direct connection, you need to add the parameters in your windows registry.

 

For More Detailed Information, use this ESRI reference documentation: http://support.esri.com/en/knowledgebase/techarticles/detail/35704