ORA-28040: No matching authentication protocol

When connecting to 10g ,11g or 12c databases using a  JDBC thin driver , fails with following errors:

- The Network Adapter could not establish the connection

- ORA-28040: No matching authentication protocol

This happens because you are using a non compatible version of JDBC driver.To resolve this issue, make sure that you are using the latest version of Oracle JDBC driver, if not use JDBC 12c or higher versions. 

Current Interoperability Support Situation

The matrix below summarizes client and server combinations that are supported for the most commonly used product versions. 

ORA-00020: maximum number of processes (5000) exceeded

Symptom

After an ORA-00020 "maximum number of processes (%s) exceeded" occurs ... no new connections (even SYSDBA or SYSOPER) may be made to the instance.

Solution

An ORA-20 "maximum number of processes (%s) exceeded" occurs when the number of OS processes for the instance exceeds the PROCESSES parameter 

There are a couple of 'workarounds' for this issue

WORKAROUNDS:

* UNIX: kill -9 one or more of the client connections 
* WINDOWS : using ORAKILL ... kill one or more of the client connections 
* Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and 
         alter system kill session 'SID, SERAL#';

In my case I don't have any active sessions with sufficient privileges

As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to generate traces.

You can shut abort and then restart your database, instead of rebooting your server.

This is the ultimate chance before reboot. Before doing this, consider disconnecting or killing some user session to get a regular sqlplus / as sysdba.

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)

How to Solve ORA-04031 with Oracle Support Provided Tool???

What is ORA-04031 and why?

ORA-04031 is error message related to lack of available SGA memory component. When a process needs some memory from SGA and memory is not available in this situation ORA-04031 occurs.
ORA-04031 message look like in DB Alert log file

Steps to solve ORA-04031 error with Oracle support tool
Database admins can easily solve this error by re-sizing SGA but the solution would be temporary. To get accurate solution use Oracle support "ORA-4031 Troubleshooting Tool".
How to use ORA-4031 Troubleshooting Tool
1) Login to the Oracle Support (support.oracle.com) and search for "Tools and Training Documents" in Knowledge tab. Click on "Tools and Training Documents"

2) Search for "Diagnostics Tools Catalog"

3) Look for "ORA-4031 Troubleshooting Tools"

4) ORA-4031 Troubleshooting Tools page looks like as below

It has four options

• Troubleshoot a new issue
• Review a troubleshooting report
• Upload new files and re-run a troubleshooting report
• Upload new files and re-run a troubleshooting report

5) Upload trace and alert log files

6) After upload you will get Oracle Recommended Solution. Below is the full report.

7) If recommended solution doesn't work, you can always create a Service Request from this tool itself

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;

ORA-04030: out of process memory when trying to allocate

What is ORA-04030?

• This error indicates that the oracle server process is unable to allocate more memory from the operating system.
• This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.
• For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. 
• In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.
• This ORA-04030 can be caused by a shortage of RAM on a dedicated (non shared server) environment, a too small PGA, and by not setting kernel parameters large enough to allow enough RAM.

What will happen?

• We can't allocate more memory from the operating system.

Query to find out the memory usage for processes from the OS

SELECT SID,NAME,VALUE FROM  v$statname n,v$sesstat s WHERE    n.STATISTIC# = s.STATISTIC# AND NAME LIKE 'session%memory%'  ORDER BY 3 ASC;

How to avoid?

• Get more memory or reduce your need for memory
• Increase pga_aggregate_target
• Decrease sort_area_size and/or hash_area_size

ORA-02030 - Grants on v$ views

Today I have faced an issue while giving GRANT permission on a v$ view. Received 'ORA-02030: can only select from fixed tables/views' error.

SQL> grant select on v$session to TEST_SUH;
grant select on v$session to TEST_SUH
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL>

Solution:

Here the problem is caused because of trying to give select privilege on a synonym, Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and we can't give any privilege on a synonym.

If you want to give permission to a V$ views, Follow below way,,

SQL> grant select on v_$session to TEST_SUH;

Grant succeeded.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout;ORA-29400;KUP-00554;KUP-01005

Drop the table (if exists)
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE EXT_TEST PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;
END;
Verify this does not already exist before running this
Create the "Directory" Object
BEGIN
CREATE OR REPLACE DIRECTORY FULL_SCHEMA_BACKUPS as '/mnt/dbbkup/Databkup/FULL_SCHEMA_BACKUPS'
END;
grant read,write on directory FULL_SCHEMA_BACKUPS to TEST_SUH;
Create the "External" table

SQL> CREATE TABLE EXT_TEST

  2  (
  3     TEST_Client VARCHAR2(30 BYTE)
  4     ,REQUEST_ID VARCHAR2(20 BYTE)
  5     ,Measure VARCHAR2(200 BYTE)
  6     ,FirstDate VARCHAR2(25 BYTE)
  7     ,LastDate VARCHAR2(25 BYTE)
  8     ,CHANGED_FIELD VARCHAR2(10 BYTE)
  9     ,OLD_VALUE VARCHAR2(100 BYTE)
 10     ,NEW_VALUE VARCHAR2(100 BYTE)
 11  )
 12  ORGANIZATION EXTERNAL
 13  (
 14    TYPE ORACLE_LOADER
 15    DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
   16  ACCESS PARAMETERS
  17   (
 18     SKIP 1
 19      FIELDS TERMINATED BY '|'
 20      RECORDS DELIMITED BY '\r\n'
 21      MISSING FIELD VALUES ARE NULL
  22     NOBADFILE
   23    NODISCARDFILE
   24    NOLOGFILE
 25    )
 26    LOCATION
  27   (
 28      'EXT_TEST.txt'
  ) 29
) 30
 31  REJECT LIMIT 0;

Table created.

SQL> select * from EXT_TEST;
select * from EXT_TEST
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "skip": expecting one of: "column, fields,
records"
KUP-01007: at line 1 column 1

Replaced my code as below and it worked. I am able to query from EXT_TEST table.

CREATE TABLE EXT_TEST
(
                HEDIS_Client VARCHAR2(30 BYTE)
                ,REQUEST_ID VARCHAR2(20 BYTE)
                ,Measure VARCHAR2(200 BYTE)
                ,FirstDate VARCHAR2(25 BYTE)
                ,LastDate VARCHAR2(25 BYTE)
                ,CHANGED_FIELD VARCHAR2(10 BYTE)
                ,OLD_VALUE VARCHAR2(100 BYTE)
                ,NEW_VALUE VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL 
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    LOGFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.log'
    BADFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.bad' 
    SKIP 1
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
          )
  LOCATION
  (
    'EXT_TEST.txt'
  )
)

REJECT LIMIT 0;

and removed 
NOBADFILE
NODISCARDFILE
NOLOGFILE

ORA-00959: tablespace 'XXX' does not exist - imp or impdp

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing XXX_2016's objects into XXX_2016
. . importing table                "AAA"       4231 rows imported
. . importing table           "BBB"        429 rows imported
. . importing table            "CCC"         10 rows imported
. . importing table               "DDD"        504 rows imported
. . importing table           "EEE"      12053 rows imported
. . importing table     "FFF"        443 rows imported
. . importing table               "FFF1"        504 rows imported
. . importing table          "FFF2"      23544 rows imported
. . importing table            "FFF3"          1 rows imported
. . importing table            "FFF4"      10984 rows imported
. . importing table             "FFF5"     382879 rows imported
. . importing table                 "FFF6"         12 rows imported
. . importing table             "FFF7"        423 rows imported
. . importing table                     "FFF8"     382879 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "XXX" ("REQUEST_ID" VARCHAR2(20) NOT NULL ENABLE, "CR_ID" NUMBER NOT NULL ENABLE, "GLOBAL__ID" VARCHAR2(50), "CREATION_S"
 "TATUS" CLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL"
 " 786432 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL"
 " DEFAULT) TABLESPACE "TEST_TBL" LOGGING NOCOMPRESS LOB ("CREATION_STATU"
 "S") STORE AS BASICFILE  (TABLESPACE "TEST_TBL" ENABLE STORAGE IN ROW CH"
 "UNK 8192 RETENTION  NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MIN"
 "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TEST_TBL' does not exist
. . importing table               "FFF9"      10787 rows imported
. . importing table            "FFF10"     653765 rows imported
. . importing table                   "FFF11"       2826 rows imported
. . importing table                   "FFF12"       3171 rows imported
. . importing table                   "FFF13"       3268 rows imported
. . importing table                   "FFF14"       1888 rows imported
. . importing table                   "FFF15"       1139 rows imported
. . importing table                   "FFF16"       3530 rows imported
. . importing table              "FFF17"         91 rows imported
. . importing table                    "USAGE"        360 rows imported
IMP-00033: Warning: Table "SAMPLE_1" not found in export file
IMP-00033: Warning: Table "SAMPLE_2" not found in export file
IMP-00033: Warning: Table "SAMPLE_4" not found in export file
IMP-00033: Warning: Table "SAMPLE_5" not found in export file
Import terminated successfully with warnings.

Solution:

impdp or imp will return a ORA-00959 when a table definition specifies multiple tablespaces (i.e. a CLOB column stored in a separate tablespace.  In these cases, the solution is to pre-create the table (punching the DDL with dbms_metadata) and use impdp or imp with ignore=y.

ORA-00020 maximum number of processes exceeded

ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

How to increase PROCESSES initialization parameter:

1.    Login as sysdba
    sqlplus / as sysdba
    
2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
    
        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1
        
4.    These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=100 scope=spfile;
    sql> alter system set sessions=115 scope=spfile;
    sql> alter system set transactions=126 scope=spfile;
    sql> shutdown abort
    sql> startup