Reslove ORA-08104 : this index object 75350 is being online built or rebuilt

Error

SQL> alter index SCOTT.SYS_C007740 rebuild online tablespace USERS;
alter index SCOTT.SYS_C007740 rebuild online tablespace USERS
*
ERROR at line 1:
ORA-08104: this index object 75350 is being online built or rebuilt

SQL> conn / as sysdba

SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2563

SQL> declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean :=
DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/

SQL> select obj#,flags from ind$ where obj#=75350;
OBJ# FLAGS
---------- ----------
75350 2051
SQL> alter index SCOTT.SYS_C007740 rebuild online tablespace USERS;

ORA-09968: unable to lock file

Sometimes when you try to startup the database it stops with a message is written in the database alert logfile as follow:


CJQ0 started with pid=8, OS id=21321
MMON started with pid=11, OS id=21325
MMNL started with pid=12, OS id=21329
Wed Feb 29 12:22:45 2012
ALTER DATABASE MOUNT
Wed Feb 29 12:22:45 2012
sculkget: failed to lock
/u01/app/oracle/product/10.2.0/dbs/lkprod exclusive
sculkget: lock held by PID: 4830
Wed Feb 29 12:22:45 2012
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 4925
Wed Feb 29 12:22:45 2012
ORA-1102 signalled during: ALTER DATABASE MOUNT...
Wed Feb 29 12:23:30 2012
alter database open
Wed Feb 29 12:23:30 2012
ORA-1507 signalled during: alter database open...
Wed Feb 29 12:24:10 2012
Shutting down instance (abort)


The Cause:
This error is caused by presence of the lk file in $ORACLE_HOME/dbs , this file is an instance lock file Oracle uses this mechanism to prevent staring of second instance. Normally left due to instance crash i.e not clean shutdown.

The Solution
1. Remove or rename the file and then try to restart the instance.
2. Sometimes, even after the file is deleted, some shadow processes may have open file handle on this file. In this case, we’ll have to kill the process(es) specific to this instance.

ORA-27301: OS failure message: No space left on device

After installing Oracle on Linux and creating a database,I tried to connect to sqlplus and startup the database instance I got this error:

$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 15:53:47 2011

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

Solution:

As root user, edit the /etc/sysctl.conf file and edit the kernel parameters
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128


and then run this command

/sbin/sysctl -p

ORA-08102: index key not found, obj#

You may encounter ORA-08102: index key not found, obj# when updating table

For example:
ORA-08102: index key not found, obj# 176528, file 57, block 7549

Cause Of this error:
Some type of corruption occurred either in the table or index which causes to this mismatch.

Solution:
This error I had resolved by rebuilding the index (ORA-08102 on index objects).

Steps:
1-Check the object causing the problem, if it's index... try to rebuild:

in our example above:

SQL> select object_name, object_type
from dba_objects
where object_id = 176528;

-- 176528 = [obj# in ORA-08102]

OBJECT_NAME OBJECT_TYPE
--------------- -------------------
CPN_MAX_MAN_IDX INDEX

2- And then rebuild it.

SQL> alter index CPN_MAX_MAN_IDX rebuild online;


3- If the index rebuild didn't solve the problem drop the index and recreate it again.

4- If ORA-08102 is on a table then try to analyze the table with validate structure option.

ORA-12518: TNS:listener could not hand off client connection

The "ORA-12518: TNS:listener could not hand off client connection" error indicates the Oracle database can't accept new connections from clients

To solve this error:

Increase the value of PROCESSES parameter in the SPfile to a larger value and then restart the database

SQL> CONNECT / AS SYSDBA

SQL> ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

Fixing the "kkjcre1p: unable to spawn jobq slave process" error

Probelm Description:
I found this error in database alert log file

Process J000 died, see its trace file
Wed Oct 27 12:56:39 2010
kkjcre1p: unable to spawn jobq slave process
Process J000 died, see its trace file
Wed Oct 27 12:56:39 2010
kkjcre1p: unable to spawn jobq slave process
Process J000 died, see its trace file
Wed Oct 27 12:56:39 2010
kkjcre1p: unable to spawn jobq slave process

the database hangs and no further connection are allowed to the database.

Cause:
The failure to spawn error almost always indicates a hardware resource shortage, often RAM or a lack of process memory.

Solution:
1 - This can be from the "processes" parameter being set too low,increase the valuse for this parameter and restart the database to take effect.
2 - check that job_queue_processes parameter it is not set to 0.
3 - Check your server stats, you may have run out of available server RAM resources.