Category: Uncategorized

Oracle Database Initialization File

arcsdegeoLeave a comment

Oracle initialization file existence is a “mandatory” pre-requisite before starting up the database, otherwise you won’t be able to startup the database.

The initialization file is a configuration file used for example to adjust memory parameters and control file locations.

There are 2 types of initialization files:

  • Server parameter file (spfile)
  • Init.ora text file

 

The first type (spfile) can be used if you require to change the contents of the file using sql statement (ALTER SYSTEM) command. Also, if you use spfile you can remotely start the database from your client machine.

The second type (init.ora) text file can be edited using OS text editor, and you can add hash comments while performing modifications. I personally prefer using this type of initialization file.

The location of initialization of file is under $ORACLE_HOME/dbs  directory

The init file would like the following:

spfile<SID>.ora       OR    spfile.ora     OR    int<SID>.ora

Where <SID> is the oracle database instance system id

Recommendation:

In 11g and 12c set the memory_target and memory_max_target initialization parameters, this setting will enable automatic memory management.

ORA-01502 Oracle Index in Unusable State

arcsdegeoLeave a comment

if you find out that you have indexes that are in “unusable state”.

first, list all invalid indexes within your Oracle database:

select * from dba_indexes where STATUS <>‘VALID’;

 

after that, rebuild each index, using sql statement:

alter index schema.index_name rebuild;

 

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [90], [46191], [46469], [], [], [], [], [], [], []

arcsdegeo11 Comments

My Oracle database have crashed for some reason, and while trying to start up:

 

SQL> startup
ORACLE instance started.

Total System Global Area 7071333376 bytes
Fixed Size 1675792 bytes
Variable Size 829809390 bytes
Database Buffers 431527320 bytes
Redo Buffers 4405509 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[90], [46191], [46469], [], [], [], [], [], [], []

 

Solution:

in order to resolve this you need t perform recovery, per the following steps:

SQL>Startup mount ;

SQL>Show parameter control_files

SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status=’CURRENT’ ;

remark: write down the name of the redo logs and their paths…

SQL> Shutdown abort ;

remark:Take a OS Level backup of the controlfiles in different directory

SQL> Startup mount ;

SQL> recover database using backup controlfile until cancel ;

Remark: Enter location of redo logs (refer to the query where you have wrote down the location of redologs),then hit ‘Enter’

SQL> Alter database open resetlogs ;

 

 

 

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