Siebel Error: ODBC error 08003 in SQLConnect: [DataDirect][ODBC lib] Connection not open (native error 0). Unable to login using specified ODBC parameters.

Sometimes you may get below error while starting Siebel. 

Siebel Error:

ODBC error 08003 in SQLConnect:

[DataDirect][ODBC lib] Connection not open

(native error 0).

Unable to login using specified ODBC parameters.

Resolution :

1) Check ORACLE_HOME is set or not?

2) Check LD_LIBRARY_PATH is set or not?

3) Check TNS_ADMIN is set or not?

4) Check PATH is set in or not?

If all oracle parameters are not then set it.

Example :

bash-3.2$ export ORACLE_HOME=/app/oracle/product/11.2.0/client_32

bash-3.2$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

bash-3.2$ export TNS_ADMIN=/app/oracle/product/11.2.0/client_32

bash-3.2$ export PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin

PX Deq Credit: send blkd wait event in AWR

• PX Deq Credit: send blkd 
  
  
  • The PX Deq Credit: send blkd is an idle event in a parallel execution wait event for RAC. 
  • It may happen that several Object  in schema were defined that "degree of parallelism’ different from 1.
  • Use of Oracle parallel queries may cause optimized to choose ‘full table scan’ and ‘hash join’ instead of index access and ‘nested loops join’.
  • So, parallelism is not usually employed in OLTP systems. You can alter all those objects with NOPARALLEL option.
  
  
  You can check it in any DB with the following queries:
  SELECT TABLE_NAME FROM USER_TABLES WHERE TRIM(DEGREE) <> '1' ORDER BY 1;
  SELECT INDEX_NAME FROM USER_INDEXES WHERE TRIM(DEGREE) <> '1' ORDER BY 1;

How to stop and control .bdb file generation on oracle rac

Step 1. Stop the Cluster Health Monitor resource ora.crf as grid owner
[grid@rac1 ~]$crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'rac1'
CRS-2677: Stop of 'ora.crf' on 'rac1' succeeded

Step 2. Remove the huge CHM(.bdb) files
The file can only be removed by the root user as it is owned by root.

$cd $GI_HOME/crf/db/[nodename]
$rm -rf *.bdb
Example:
[root@rac1 ~]$cd /app/grid/11.2.0.4/crf/db/rac1
[root@rac1]$rm -rf  *.bdb

Step 3. Start the Cluster Health Monitor resource ora.crf
[grid@rac1 ~]$crsctl start res ora.crf -init
CRS-2672: Attempting to start 'ora.crf' on 'rac1'
CRS-2676: Start of 'ora.crf' on 'rac1' succeeded

Step 4. To control .bdb file generation
run below command through grid user.it allowed to change Cluster Health Monitor repository size
$ oclumon manage -repos resize 259200
rac1 --> retention check successful
rac2 --> retention check successful
New retention is 259200 and will use 4524595200 bytes of disk space

ORA-12514 error on RAC environment

Hello

i faced ORA-12514 error on RAC environment.

i am able to tnsping but while connecting to db i got below error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution:

1) Check tnsnames service and node tnsnames.ora service,port should be same.

2) listener and both node should be up and running.

3) user below command from sys or system users.

alter system set remote_listener='<scanname>:<port>';

Example:

alter system set remote_listener='rac-scan.sigmasys.net:1521';

  
 

Error: ora 00604 ora 12663 ora 36961 ora 06512

In alert log i found below error.

Error:

ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 15

Problem: This error occurs due to some trigger.We can see trigger name here in error.Here trigger name is OLAPIHISTORYRETENTION.

Resolution : First we need schema name of that trigger.For that try below query through sys/system user.

SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where TRIGGERING_EVENT like '%STARTUP%';

Or 

SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where TRIGGER_NAME like 'OLAPIHISTORYRETENTION';


In my case Result is like that:
SYS,'AURORA$SERVER$STARTUP'’

SYS,‘OLAPISTARTUPTRIGGER’

Now we need to disable it.

SQL> alter trigger OLAPISTARTUPTRIGGER disable;

ORA-00600: internal error code

Cause: It may happen  the error was in undo corruption.

Resolve:

Step 1:
SQL> SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
-------------------- --------------------
undo_management MANUAL
undo_tablespace UNDO_TBS

Step2:
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like 'UNDO%';

FILE_NAME                         TABLESPACE_NAME
----------------------------------------------------------------
/ebiz/oracle/db/apps_st/data/undotbs_02.dbf     UNDO_TBS
/ebiz/oracle/db/apps_st/data/undotbs_01.dbf     UNDO_TBS

Step 3: Create a new undo tablespace
SQL> create UNDO tablespace UNDOTBS datafile '/ebiz/oracle/db/apps_st/data/undotbs01.dbf' size 1024m REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 1024M;
Tablespace created.

Step 4:
SQL> ALTER SYSTEM SET undo_tablespace = 'UNDOTBS' scope=spfile;
System altered.

Step 5: set old undo tablespace offine mode and drop
SQL> ALTER TABLESPACE UNDO_TBS offline;
Tablespace altered.

SQL> drop tablespace UNDO_TBS including contents and datafiles;
Tablespace dropped.

Step 6:
Rebounced the db services

Step 7: Changed the undo management parameter to AUTO
SQL> alter system set undo_management='AUTO' scope=spfile;
System altered.

SQL> SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
-------------------- --------------------
undo_management AUTO
undo_tablespace UNDOTBS

ORA-01000 and OPEN_CURSOR

What is OPEN_CURSOR?

Ans) OPEN_CURSOR means the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.(Range 0 to 65535)

Error: 

ora-01000: maximum open cursors exceeded

• Monitoring script which provide you full SQLTEXT,USER_NAME,CURSOR_TYPE,SID,Machine, Program information,count

select c.user_name, c.sid,s.SERIAL#, sql.sql_text,s.machine,s.PROGRAM,c.cursor_type

from v$open_cursor c, v$sql sql, v$session s

where c.ADDRESS=sql.address group by c.user_name,c.sid,sql.sql_text,s.machine,s.PROGRAM,c.cursor_type,s.SERIAL#;  

select c.user_name, c.sid,s.SERIAL#, sql.sql_text,count(*) as "OPEN CURSORS VALUE",s.machine,s.PROGRAM,c.cursor_type

from v$open_cursor c, v$sql sql, v$session s

where c.ADDRESS=sql.address group by c.user_name,c.sid,sql.sql_text,s.machine,s.PROGRAM,c.cursor_type,s.SERIAL# order by count(*) desc;

• Monitoring Script which provide you open_cursor value with SID & Serial# value

select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current';

• To know value about current highest open cursor and maximum open cursor

select max(a.value) as highest_open_cur, p.value as max_open_cur

from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# 

and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;

Solution:

1) check application code and check code and check why cursor are staying open? And why its required this much number of open cursor?  

2) increase open_cursor parameter and restart db.(if its required last option)

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;