Month: March 2014

ORA-08103: object no longer exists while revoking DBA privilege from user

arcsdegeoLeave a comment

while revoking “DBA” role from an Oracle users, i faced the following error:

SQL> revoke DBA from oracle_user;

ORA-08103: object no longer exists

Image

 

you have 2 methods to resolve this issue:

METHOD I :

  • create a new tablespace with new “data files”.
  • take a backup of the sql definition of the schema user.
  • use the data pump utility (expdp) to export the schema’s data.
  • drop the user schema.
  • drop the tablespace with the datafiles using the sql command:
  • DROP TABLESPACE TS_EMAPLOYEE_DATA INCLUDING CONTENTS AND DATAFILES;
  • re-create the tablespace with “NEW” data file(s) and and create the user schema again.
  • import the schema’s data again.

 

METHOD II:

  • create a new tablespace with new data files.
  • alter user and grant the schema user quota on the new tablespace:
  • alter user emaployee quota unlimited on tablespace ts_employee_data_02;
  • use the “ALTER MOVE” command to move oracle tables and indexes from tablepsace 1 to tablespace 2 (NEW)
  • you may require rebuilding indexes.

 

Important Remark:

This is faced with legacy databases that have been upgraded over the years from 9i……to 11g due data files header issue.

 

 

 

TDE in Oracle 11g

arcsdegeoLeave a comment

What is TDE ?

TDE stands for “transparent data encryption”. TDE is part of Oracle Advanced Security Option for Enterprise Edition. Data at rest are encrypted and ONLY authorized users who have access control privilege will read de-crypted data. So, TDE protects your from direct access to the database host server or backup media that has copies of your data files.

TDE Master Key encryption can be managed through Oracle Wallet Manager.

Example of column data encryption:

The following table will be created with column “salary_information”

SQL> create table EMPLOYEE.EMPLOYEE_INFORMATION(first_name varchar2(20),last_name varchar2(30), EMPLOYEE_ID char(18), salary_information number(10,2) ENCRYPT using ‘AES256’ NO SALT);

 

For Tablespace Encryption:

  • You cannot encrypt an existing tablespace.
  • You can use” Data Pump” method OR “alter table move” to move data to newly encrypted tablespace.

 

Example of creating encrypted tablespace :

CREATE TABLESPACE tablespace_new

DATAFILE  ‘/app/oracle/oradata/tablespace2.ORA’  SIZE 200M

ENCRYPTION USING ‘AES25’ DEFAULT STORAGE (ENCRYPT);

ora-28031 maximum of 148 enabled roles exceeded

arcsdegeo2 Comments

if you face an error like this:

Image

 

Description of the Problem: 

When a new session is initiated Oracle will attempt to enable all the default roles of the account if their number will be less than ( (value of the MAX_ENABLED_ROLES parameter) – 2 ). The default value of MAX_ENABLED_ROLES is 150. If the account is granted (directly or recursively) more than 148 roles then the session creation will fail with ORA-28031.

SQL> show parameter max_enabled_roles 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

max_enabled_roles                    integer     150

Image

To Resolve this error:

You need to remove the role from the user as a “default” one.

So, the only way is to revoke then re-grant the role again without the “default option”. 

REVOKE RL_SELECT_EMP FROM EMPLOYEE;

GRANT RL_ SELECT_EMP TO EMPLOYEE;

 

 

ora-13282 failure on initialization of coordinate transformation

arcsdegeoLeave a comment

before going to the solution part, execute the following verification queries to make sure your Oracle spatial component is valid:

select comp_name,version_status from dba_registry;

select * from dba_objects where status <> ‘VALID’ and owner=’MDSYS’;

============================================================================

Solution Steps:

you need to Correct the WKT strings as follows:

1. Connect as SYSTEM, SYS or MDSYS

2. execute the command:
alter session set nls_numeric_characters = ‘.,’;

3. Regenerate the WKTs for all EPSG coordinate systems using the following command:
execute sdo_cs.update_wkts_for_all_epsg_crs;

4. Now, try to execute the following query:
select SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,41015,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1,3,1,0),MDSYS.SDO_ORDINATE_ARRAY(813483.176346326,83853.5516374355,1,1.42608769437217E-8)),27572) from dual;

if it executes successfully then…..your problem has been resolved 🙂

 

Remark: please note that i didn’t face this error (ora-13282) with Oracle  version 11.2.0.3

ORA-39213: Metadata processing is not available

arcsdegeoLeave a comment

if you face an error like the following while performing “impdp” operations:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit 
Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

 

Solution:

execute the following command:

SQL> exec dbms_metadata_util.load_stylesheets;

if the above query executes sucessfully then try the “impdp” command again.

other wise if you face the below:

SQL> exec dbms_metadata_util.load_stylesheets;

BEGIN dbms_metadata_util.load_stylesheets; END;
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEEXISTs failed
Permission denied
ORA-06512: at “SYS.DBMS_METADATA_UTIL”, line 1807
ORA-06512: at line 1

During dbms_metadata_util.load_stylesheets we read the directory $ORACLE_HOME/rdbms/xml/xsl and load the XSL files using DBMS_LOB package.

If the files cannot be read (most of cases by missing rwx privileges for oracle user), then we raise the errors above.

Re-load the stylesheets using the dbms_metadata_util.load_stylesheets procedure after the permission issue is solved and then retry DataPump import (impdp).

you may need to install Install JVM and XML components.

ORA-28575: unable to open RPC connection to external procedure

arcsdegeoLeave a comment

i have faced the following error while configuring the external procedure for st_gemoetry in oracle database.

ORA-28575: unable to open RPC connection to external procedure

ORA-06512: at “SDE.ST_GEOMETRY_SHAPELIB_PKG”,line 1325

ORA-06512: at “SDE.ST_GEOM_UTIL”, line823

 

Before going to the solution of this error, please review the following crucial points:

  • Other problems such as file corruption could also cause the extproc program to not be executable. Perform the following steps to verify whether the extproc program is valid and executable: 
  • Log in to the system as the applicable user.
  • cd to the $ORACLE_HOME/bin directory.
  • Execute the following command at the prompt: 

$  ./extproc 

 

  • starting form Oracle 11g release, you don’t need to configure the listener for external procedure, this can be directly implemented through “extproc” file.

 

Solution:

under $ORACLE_HOME/hs/admin directory edit the extproc.ora and

either use

SET EXTPROC_DLLS=ANY

(This is not advisable for security reasons)

OR

SET EXTPROC_DLLS=$ORACLE_HOME/libst_shapelib.so

and thats it you are FINISHED !!!!!!

 

However, make sure of the following:

when you perform the following query (access the database as “sde” account user).

SELECT * FROM USER_LIBRARIES;

it should point to the directory where your “.so” file is located, in our example its “$ORACLE_HOME/libst_shapelib.so”

after extensive troubleshooting, i found out that this will occur in one of two situations:

1. the libst_shapelib.so external procedure library is corrupted.

OR

2. the libst_shapelib.so external procedure library is a 32 bit…….to check that use the Unix Command ( filelibst_shapelib.so) the output should give you 64bit

in my situation, the library was corrupted (for some reason)…..when i copied the the library form my local machine (C:\Program Files (x86)\ArcGIS\Desktop10.1\DatabaseSupport) to the database server and used the external procedure method ‘ONLY’ it worked fine and i was able to execute the query: select * from gdb_items_vw;

 

reference links:

http://forums.arcgis.com/threads/73476-ST_GEOMETRY-problem-on-Oracle-RDBMS-11g-R2-11.2.0.3

https://community.oracle.com/thread/3520287