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.

Creating the index after moving the data with insert /*+APPEND*/clause is key for the operation, but it is not finish yet, even the case is beginning now. Your above plan 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

How to increase the speed of RMAN backups

There could be several bottlenecks which makes our backups slower.

Last month we had similar situation and after investigating the issue in the legato side, the number of allocated tape drives seemed us small. Because of that we had increased the number of tape drives devoted for the backup job from 2 to 4.

Interestingly, it does not make any sense.

After than, we increased the number of channels in RMAN script from 8 to 12 and in this way we managed to increase the utilization of tape drives. The real reason behind was the utilization of tape drives are dependent on utilization of disks for reading.

Further, in legato side, while monitoring the backup job, only 2 of allocated 4 tape drives are used. The minimum session limit for each tape drive was 8. That means 12 channels opened only utilizes 2 tape drives. After setting minimum session value per tape drive in legato side to 3, we are happy with the picture we got. 12 channels of backup work is evenly distibuted across the 4 tape drives.

As a result, our full backup job now lasts for 20 hours, which was 50 hours before the optimization.

Find Minimum size of datafiles for resize operations in TOAD

If you had exhausted of space, and tried to find a quick way of resizing the tablespaces. You may want to use TOAD feature:

From Administer ->Tablespaces-> Select one tablespace and press F4. In the opened window click datafiles tab and double click the datafile which you want to resize. In this window there is a button “Minimum Size?”, click it.

After some time of processing, it will find the minimum size of datafile it could be, it could be better than trial-error method for exact sizes.

Tablespace Menu Selection

Datafile Definition Window

Spool SQL in TOAD

If you are using TOAD, and want to get the SQL in order automate tasks or simply wonder what happens behind. You may like to check this below:

In TOAD, from main menu select:

Database -> Spool SQL -> Spool SQL to Screen

Spool SQL Menu Selection

And you will see every action is spooled in the below window.

Spool Output Window

Hope this helps!

TNS Connection Failure Resolution

Many dbas has no single day in a week that not telling  their database  users about how to make TNS connection with the newly created accounts in their hands. I am providing below decision tree, to our users in our company for resolution of their TNS connection failures on their own.

Follow below decision tree for a sample TNS entry in$ORACLE_HOME/network/admin/tnsnames.ora file like below:

MYRAC=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))

1. From cammand line issue: ping mydb.com
   1. If not work, WINS server client configuration problem !
   2. If works: tnsping MYRAC

i.      If works, there must not be any problemJ

ii.      If not work, from command line issue below command:

tnsping  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))

1. If not work:  Oracle client configuration/installation problem !
2. If works: Check $ORACLE_HOME/network/admin/sqlnet.ora  file. Note the NAMES.DEFAULT_DOMAIN parameter value if other than null and place it to your $ORACLE_HOME/network/admin/tnsnames.ora:

MYRAC.<names default domain>=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME= MYSERVICE)))

JDBC connect string

While trying to connect to Oracle via Oracle service name provided to you.

Then you could test the connection by:

tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))

And then used this information int the conenct string as below:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))