Oracle ORA-12564 TNS:connection refused ( shared server )

If you get "ORA-12564: TNS:connection refused" errors from your database connections.
Reason is typically misspelling in tnsnames.ora file.

But if you are using shared server connections these errors can be seen also if you have not enough dispatchers or your dispatcher max session limit is reached.

Usually with ORA-12564 you can also find these kind of errors in your listener logs:
"TNS-12520: TNS:listener could not find available handler for requested type of server"

This way you can check your connections via listener (this shows both dedicated and dispatcher connections):
lsnrctl services

With following sql you can check your dispatcher settings (these settings can be changed with ALTER SYSTEM SET dispatchers= ... commands):
SQL> select * from V$DISPATCHER;
SQL> select * from V$DISPATCHER_CONFIG;


If you does not get these errors in normal usage but only in occasionally then you also might want to check which users are doing most connections when this problem is on. You can do it with this sql (this uses RAC gv$session view so it shows cluster all instances connections. If you want only one instance connections you can use v$session):
SQL>  select INST_ID, USERNAME, count(SID) from gv$session group by USERNAME, INST_ID order by count(SID);

Oracle "opidcl aborting process unknown ospid (xxx) as a result of ORA-28" error.

If you get following ORA errors in your database alert log:
"opidcl aborting process unknown ospid (xxxx) as a result of ORA-28"

This usually means that some privileged user (dba) has killed sessions from database.

But if you see these errors a lot or all the time then this can be bug. This bug is affected
older (Oracle 11.1.0.6 and 11.1.0.7) versions. With bug error messages look like this:
"ORA-28 : opiodr aborting process unknown ospid (xxxxx_xxxxxxxxxxx) "

NOTE! If some application or user kill several (for example all one user) sessions at the same time there can be several errors in a row in alert log and it is still a normal situation.

Oracle 12c timestamps for datapump output file and console.

Starting on Oracle 12c you can set timestamps on for datapump (expdp and impdp) output file and console messages. With new LOGTIME option you can control timestamps printing. This option can have 4 different values (NONE, STATUS, LOGFILE, ALL).

NONE is default value. With it there is no additional timestamps in the output file or in the console. 

STATUS With this value timestamps are printed in the console but not in the output file.

LOGFILE With this value timestamps are printed in output file but not in the console. 

ALL With this value timestamps are printed in the output file and in the console.

Oracle 12c nologging for impdp

There is very useful new feature in Oracle 12c impdp which you can use to get rid of logging when you are doing import.

For example big bulk imports you might want to take logging of (both table and index) this way archivelog disk is not getting full and you can save some time.  You can do this with impdp "TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y" option.

If you want you can also choose to remove only table or index logging:
only table data logging off during import:
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
only index data logging off during import:
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX 

Default value for this parameter is TRANSFORM=DISABLE_ARCHIVE_LOGGING:N which is doing normal logging during import.

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