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;