Ora-01115, Ora-01110, Ora-15081 ORA-15186(alert.log) Error After Connecting To Database with non-oracle Unix User

If you are getting error Ora-01115, Ora-01110, Ora-15081 after you connected to a database with non-oracle user as below.

rep@mydb2:/home/rep/>sqlplus rep_odi/***

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 21 17:01:10 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Data Mining and Real Application Testing options

SQL> select * from dba_indexes;
 select * from dba_indexes
 *
 ERROR at line 1:
 ORA-01115: IO error reading block from file (block # )
 ORA-01110: data file 1: '+DATA/mydb/datafile/system.272.743775337'
 ORA-15081: failed to submit an I/O operation to a disk

Further if you are seeing below lines in alert.log of database:

Mon Nov 21 17:23:23 2011
 Errors in file /u01/app/oracle/diag/rdbms/mydb/MYDB2/trace/MYDB2_ora_13518.trc:
 ORA-15186: ASMLIB error function = [asm_init], error = [18446744073709551611], mesg = [Driver not installed]
 ERROR: error ORA-15186 caught in ASM I/O path

The real cause is you are connecting with OS user which does not belong to OS dba, oper, asmadmin ... etc group.

In order to succesfully do the operation, you must change the ownership of the OS user you are using or conenct to database over tns name, like below:

rep@mydb2:/home/rep/>sqlplus rep_odi/***@REP

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 21 17:01:10 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Data Mining and Real Application Testing options

SQL> select * from dba_indexes;

SQL>...

Thanks to brilliant guy Hakan Dömbekçi for routing me to MOS Note.

Ora-01115, Ora-01110, Ora-15081 When Connecting DB with non-dba Group Users [ID 1372571.1]

How to recover from, 64 bit 32 bit ODBC driver could not be loaded due to system error code 998

Problem

ODBC driver could not be loaded due to system error code 998

Cause

The problem arose because application, Oracle client and Windows does not match properly as 32bit or 64 bit.

I have Windows 2003 64 bit, Oracle 10.2 32 bit Client and 64 bit application.

After googleing around,

I found that there is two versions of odbc datasource administrator:

The default one, is 64 bit which we use Start menu for opening it.

In order to open the 32 bit version I used: %windir%\SysWOW64\odbcad32.exe.

With the help of 32 bit version datasource administrator, I could see my 32 bit odbc driver listed.

I extracted this rule of thumb: If your application is 32 bit, then your data source must use Oracle 32 bit client as well.

Last words, beware of 3 pieces:
1- your application (which uses DSN definition),
2- Oracle client
3-data source administrator

All pieces must have same version, in my case it is 64 bit and therefore I installed 64 bit client.

Further reading:
http://support.microsoft.com/kb/942976

Moving Data and Creating Local Index In Huge, Partitioned Tables

In order to get performance while creating a table and moving data with bulk inserts, it is a common way, first creating the table without index. Performance is gained with the help of not dealing with index manipulation for each row inserted, which means “single insert” operation.

You need to create the index after moving the data with insert /*+APPEND*/clause.

"Creating index" is the heart of the matter here... If you go with traditional approach like in small tables it will not work because of the reasons below:

-          new DML operations must wait for the index creation, which could cause space problems, application problems

-          getting ORA-01555 snaphot too old messages while creating the index

So what will I do in order not to bump into above conditions?

Answer: Apply divide and conquer approach, partitions is for this purpose... First create the index as unusable, and then issue rebuild clause to each partition. This could be done as following:

- Make the index unusable:

ALTER INDEX myindex UNUSABLE;

- Rebuild the index in each partition:

ALTER INDEX myindex REBUILD PARTITION mypartition;

- Check the status of index for each partition from:

SELECT PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
WHERE INDEX_NAME = 'MYINDEX';

Also note that if the index is associated with a constraint such as; primary/unique key constraint, you should better disable or drop the constraint, as for not getting error for DML operations take place after index is unusable.

You could get detailed information with regard to unusable state of indexes and index altering operations in the below links respectively:

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/indexes002.htm#CIHJIDJG

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/indexes004.htm#CIHJCEAJ

A photo from Garipce, Istanbul

How to cancel alter database datafile resize command

If you erroneously give alter database resize command, such as giving 33,884,000M  instead of 3,388,400MMB. Which may cause you to end up with disk space.

You can kill the sql from another session, but also you could give true resize value as well.

Here what happens, in alert log file, from real world scenario:

Thu Mar 03 04:28:12 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 33884000M
Thu Mar 03 04:31:59 2011
Immediate Kill Session#: 600, Serial#: 53111
Immediate Kill Session: sess: 7000007726370c8  OS pid: 52691066
Thu Mar 03 04:32:15 2011
Immediate Kill Session#: 866, Serial#: 41187
Immediate Kill Session: sess: 7000007726ae1c8  OS pid: 34406652
Thu Mar 03 04:34:31 2011
NOTE: deferred map free for map id 116809
Error occured while spawning process O004; error = 601
Thu Mar 03 04:36:27 2011
Immediate Kill Session#: 1133, Serial#: 30931
Immediate Kill Session: sess: 7000007687084b0  OS pid: 63242370
Thu Mar 03 04:39:57 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388400M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388400M
...
Thu Mar 03 04:40:14 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388500M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388500M
...
Thu Mar 03 04:40:31 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3389500M
Thu Mar 03 04:42:35 2011
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb1/trace/mydb1_ora_65994754.trc  (incident=225358):
ORA-00240: control file enqueue held for more than 120 seconds
Incident details in: /u01/app/oracle/diag/rdbms/mydb/mydb1/incident/incdir_225358/mydb1_ora_65994754_i225358.trc
Thu Mar 03 04:42:40 2011
Dumping diagnostic data in directory=[cdmp_20111103044240], requested by (instance=1, osid=65994754), summary=[incident=225358].
Thu Mar 03 04:47:25 2011
minact-scn: useg scan erroring out with error e:12751
Thu Mar 03 04:47:25 2011
Sweep [inc][225358]: completed
Sweep [inc2][225358]: completed
Thu Mar 03 04:49:36 2011
Error occured while spawning process O007; error = 601
Thu Mar 03 04:52:01 2011
Completed: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3389500M