ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Alert Log details: 

Trace file details:

Cause:

The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:

• - Temporary Datapump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.
• - An OS file for an External table has been removed without clearing up the corresponding data dictionary information. 
• Solution:
• Essentially the solution to this issue is to clean up the orphaned dictionary entries.
• SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;
• or 
• select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
  to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
  ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
  from dba_objects
  where object_name like 'ET$%'
  /
  
  select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
  from dba_external_tables
  order by 1,2
  /
• 
  Drop external temporary table
• 
  Ref. Doc: ID 1274653.1

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: sskgpcreates - When creating new instance

Today I see below errors when trying to create new instance in DEV environment (It has already 3 instance, I was trying to create 4th one).

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: sskgpcreates - When creating new instance

In my case Kernel settings are not sufficient for 4th instance.

Semaphore parameter value caused the issue.

in /etc/sysctl.conf I see kernel.sem = 250 32000 100 128

and changed to kernel.sem = 250 32000 100 256

as a root executed sysctl -p

This fixed my issue and I was able to create 4th instance.

ORA-01000: maximum open cursors exceeded

To solve this issue we can either increase the no. of open_cursors or kill the inactive session which has open the large number of cursors. Now we connect to the database and check the open_cursors limits.
$sqlplus sboda as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 22 22:55:38 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 256
SQL> show parameter open_cursors
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
open_cursors                         integer                          300

Find out the session which is causing ORA-01000: maximum open cursors exceeded  using Below Sql

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' and s.username is not null;

Now one can see which all queries are causing maxing out of open cursors using below Sql:

select  sid ,sql_text, user_name, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (SID) group by sid ,sql_text, user_name

Now check which session opens to many cursors?

SQL>  select sid, status, event, seconds_in_wait state "wait(s)" , blocking_session "blk_sesn", prev_sql_id  "SQL_ID"  from v$session where sid=<sid_number>;

Check the status of the cursor,if it is INACTIVE we can we kill the session by using the below command :
SQL> alter system kill session 'sid,serial#' immediate;
The other alternatives is to increase the no. of the open_cursors parameter as
SQL> alter system set open_cursors=1500 scope=spfile;
In my case i have increased the values of the open_cursors and issue got solved.
Another useful query - total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
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' 
group by s.username, s.machine

order by 1 desc;

HUNG IN AUTO SQL TUNING TASK

Issue-
In 11.2.0.3 database , we get alert in EM related to metric “Generic Operational Error” or "Generic Operational Error Status ". Occasionally when running Automatic SQL Tuning the following messages may appear in the alert log:
Error-

These messages indicate that an auto kill of a "hung"/long running tuning task has taken place.

This is a protective measure purely to avoid the task from over-running its time limit because of a single task and  protects a the system from harm caused by such over-running.

Since this is an expected activity to prevent over-running there is no fix as such.  
Instead as a workaround, you could:
FIX 1 :Give the task more time to complete (the following example would set the per statement timeout to 6 hours (21600 seconds)):
BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 21600);
 END;

/
Note
If you increase the per-statement time limit (LOCAL_TIME_LIMIT) then you need to stay within the bounds of the time limit for the entire task (TIME_LIMIT). The duration of the TIME_LIMIT parameter must be at least equal or greater than the LOCAL_TIME_LIMIT. When the maintenance window closes the SQL Tuning Advisor is stopped.
FIX 2 :
Disable the automatic tuning process and the messages will not appear anymore (though obviously the auto tuning will also no longer occur - you could manually execute the job as desired later). 
To disable the job:
connect / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
To re-enable in future:
connect / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

/
Source : 
How to Avoid or Prevent "Process 0x%p appears to be hung in Auto SQL Tuning task" Messages (Doc ID 1344499.1)