Oracle ORA-00020: maximum number of processes (xxx) exceeded

If you get following 'ORA-00020' errors in database alert log:
--
ORA-00020: maximum number of processes (xxx) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process m000 submission failed with error = 20
--

Then you probably wont get into the database via sqlplus or any other tool. This is because you got same error when you are trying to connect into the database. Error means that you need to increase processes parameter value or find out what is using too many processes and fix the problem. Best way to handle this kind of situation is to stop application/applications that are using this database and then you can again connect into the database and increase needed parameter values (alter system set processes=500 scope=spfile;) and restart the database.

If you cannot stop the applications to free processes then you can try to stop database with following way:
Run following as oracle user into database server:
export ORACLE_SID=<database_name>
sqlplus -prelim / as sysdba

and then run 'shutdown immediate' or 'shutdown abort' . Then 'startup' or 'startup mount' and do the parameter change (after parameter change you need to do restart the database). But remember that this can damage your applications data (especially if you use abort which just stops the database right away.) So it is better to just stop the application/applications even it means little downtime.

NOTE! When you connect into database via 'sqlplus -prelim' then you can also try to check what is causing these errors with oradebug (hanganalyze). And if you find some problematic sessions/SQLs you can kill (via OS) just those without need to restart the whole database. But this can take some time so more quickly fix use the above instructions. More info about oradebug and hanganalyze can be find from My Oracle Support (MOS) documents:
215858.1
and
310830.1

Oracle 12c Invisible Columns

With Oracle 12c you can use Invisible Columns to hide table columns for your testing or for other purposes.
Column is invisible for following operations (but you can use normal DML operations for invisible columns):
1. SELECT * FROM <table_name>;
2. DESCRIBE  <table_name> (via sqlplus and OCI)
3. %ROWTYPE attribute declarations in PL/SQL

This way you use invisible columns:
For example:
CREATE TABLE test_table(
  test_id NUMBER,
  test_name VARCHAR2(32),
  starting_time TIMESTAMP,
  ending_time TIMESTAMP );


You can change columns to invisible and to visible:
ALTER TABLE test_table MODIFY (test_name INVISIBLE);
ALTER TABLE test_table MODIFY (test_name VISIBLE);

NOTE! When you set column invisible the column order changes (invisible column is removed from column order). And If you set same column back to visible it is placed last in table column order.


You can also add new columns with invisible on:
ALTER TABLE test_table ADD ( tester_id NUMBER INVISIBLE );


You can also create table with invisible columns. Just add INVISIBLE after column datatype:
CREATE TABLE test_table(
  test_id NUMBER INVISIBLE,
  test_name VARCHAR2(32),
  starting_time TIMESTAMP,
  ending_time TIMESTAMP );


You can make normal SQL DML operations for invisible column like this (for previous test table): INSERT INTO test_table (test_id, test_name, starting_time, ending_time) VALUES (1110, 'Test_1', '12-Oct-13', '15-Oct-13');



NOTE! The following types of tables cannot have invisible columns: External tables, Cluster tables, Temporary tables. Also attributes of user-defined types cannot be invisible.

You can find more info about Invisible Columns here:
Understand Invisible Columns

Oracle 12c Temporal Validity time periods in tables.

In Oracle 12c there is new feature called "Temporal Validity". With it you can create time periods between two columns and use these periods for queries.

Example:
-Create table with Temporal Validity period
(you can also add PERIOD FOR in existing table with "ALTER TABLE" clause):
CREATE TABLE test_table(
  test_id NUMBER,
  test_name VARCHAR2(32),
  starting_time TIMESTAMP,
  ending_time TIMESTAMP,
PERIOD FOR testing_time (starting_time, ending_time));

-Inserts are working just like before (PERIOD FOR is not column)
(There can be also NULL values if table constraints accept those.):
INSERT INTO test_table VALUES (1110, 'Test_1', '12-Oct-13', '15-Oct-13');
INSERT INTO test_table VALUES (1110, 'Test_1', '14-Oct-13', null);

- PERIOD FOR gives more variety for your queries (but you can also query table without it):
-This will return all rows that got given date in their time period (first example row):
SELECT * from test_table AS OF PERIOD FOR testing_time TO_TIMESTAMP('13-Oct-13');

-You can also use Period For in "BETWEEN" clause. This will return both example rows.:
SELECT * from test_table VERSIONS PERIOD FOR testing_time BETWEEN
TO_TIMESTAMP('13-Oct-13') AND TO_TIMESTAMP('16-Oct-13');


NOTE: Flashback Query has been extended to support queries on Temporal Validity dimensions. 

You can find more info about Temporal Validity from here:
Oracle 12c New Features
 and here:
Oracle 12c Desing Basics

Oracle ORA-7445 [kkzufst] errors in alertlog.

ORA-7445 [kkzufst] errors in alertlog are result from bugs in materialized views refresh jobs.

If you got these bugs you'll see these kind of errors in your database alertlog
(there is always ORA-07445 and kkzufst in error but other clauses can vary.):
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1000] [PC:0x44BBFCE, kkzufst()+111] [flags: 0x0, count: 1]
Errors in file...
ORA-07445 : exception encountered: core dump [kkzufst()+111] ...
Incident details in:...
--

In incident log you'll see something like this (here you can also see problematic SQL or PL/SQL clause in "Current SQL Statement ..." section)
(there is always ORA-07445 and kkzufst in error but other clauses can vary.):
--
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1000] [PC:0x44BBFCE, kkzufst()+111] [flags: 0x0, count: 1]
.
.
.
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=...

--


Fixes:
Bugs are more accurately 9554075 and 9656434
These both bugs are fixed in Oracle version 11.2.0.2 SPS (Server Patch Set).
And in Oracle version 12 fixes are in following versions: 9554075 -> version Oracle 12.1.0.1 (Base Release). 9656434 -> version Oracle 12.2 (Future release. (Not yet released))


More info about these bugs can be find from MOS (My Oracle Support) document:  
1288415.1

Oracle 12c enhancements for SCAN

There are new features for SCAN in Oracle 12c:

1. SCAN and Oracle Clusterware managed VIPs now support IPv6 based IP addresses.
So now we can use also IPv6 addresses with SCAN. If you check SCAN configs with:
srvctl config scan
it will list you more info: SCAN name, IPv4 address, IPv6 address, SCAN number+VIP



2. SCAN is by default restricted to only accept service registration from nodes in the cluster.
If you want to add nodes (for example for the database that are not using cluster's private interconnect) then you can do it with command:
srvctl modify scan_listener -invitednodes <new_database_name> –update
You can also run this modify before you present real node in the cluster.
 
If you check SCAN-listener configs with:
srvctl config scan_listener
it will list you more info: SCAN-listener name+port, Registration invited nodes, Registration invited subnets



3. SCAN supports multiple subnets in the cluster (one SCAN per subnet).

Before you can do this you must enable multiple subnets for the cluster (This is a post-installation task an cannot be made in Grid Infra installation). 

 


NOTE! Here you can find more info about SCAN and Oracle 12c enhancements :
http://www.oracle.com/technetwork/products/clustering/overview/scan-129069.pdf

Oracle 12c New Features for table Defaults

With Oracle 12c there is improvements in tables default values.

Starting on Oracle 12c you can use sequence nextval straight in column default or you can use "generated as identity" for column default (this will generate new sequence for table and start use it for column default values) This new sequence is linked with table so if you remove the table also the sequence is removed. You can also use "generated by default as identity" when you create new table sequence. This will allow you to override sequence value in inserts. If you use default word as column value or leave column and it's value out of the insert the sequence is used but you can also give new value for this column when "by default" is used.

With Oracle 12c you can also give default values for null columns. So if you are inserting null value into column that have for example this default settings: "default ON NULL 11" then 11 value will be inserted into table instead of null.

Examples:

Create table clause with using existing sequence:
SQL> CREATE TABLE TEST_TABLE ( id int seq_test_id.nextval primary key, name varchar2(32) );


Create table clause with generating new identity sequence:
SQL> CREATE TABLE TEST_TABLE ( id int generated as identity primary key, name varchar2(32) );

Create table clause with generating new identity sequence with sequence init values:
SQL> CREATE TABLE TEST_TABLE( id int generated as identity (start with 10000) primary key, name varchar2(32) );

Override sequence value when table squence is created with "generated by default as identity":
SQL> INSERT INTO TEST_TABLE (id, name) VALUES ( 10, 'test_name');

Using sequence value when table squence is created with "generated by default as identity":
SQL> INSERT INTO TEST_TABLE (id, name) VALUES ( default, 'test_name');
or
SQL> INSERT INTO TEST_TABLE (name) VALUES ( 'test_name');

Oracle 11.2.0.3 ORA-04030: out of process memory when trying to allocate xxxx bytes (kxs-heap-w,KGL Iterator information)

If you get the ORA-04030: ... (kxs-heap-w,KGL Iterator information) error in alert log
and if you see obsoleted parent cursors lying around.
Then you are hitting Bug 12791981 - ORA-4030 due to "KGL Iterator information" . 
Look more from MOS (My Oracle Support) document 12791981.8 .

If you are trying to check those obsolete cursors you are probably hitting the same error ORA-04030 and then you know that there are also those obsoleted cursors in the database.

With this SQL you can check those obsoleted cursors:
select sql_id, address, IS_OBSOLETE, count(*) from v$sql where IS_OBSOLETE='Y' group by sql_id, address, IS_OBSOLETE having count(*)>=10;

Or just:
select count(*) from v$sql where IS_OBSOLETE='Y'; 

You can fix this bug with patch 12791981 .


NOTE! This bug can also be fixed with patch 14799269 (this is more complete fix according to MOS) but it is only available for 11.2.0.3.1 version and newer ones.